Traduction des schmas JSON en scripts SQL

[GT-CNIG-DDU]

Bonjour @Pierlou,

Lors du dernier SG ZAE, @Mavialle a posé la question de savoir si les schemas JSON peuvent être traduits en script SQL afin de créer la structure d'une base de données. Peux-tu nous répondre sur la faisabilité. D'ailleurs peut-être un outil existe-t-il déjà ?

à bientôt, @GT-CNIG-DDU

PS : Je pourrai dupliquer cette issue sur le Github schema.data.gouv si tu le juges utile.

[Pierlou]

Bonjour Arnauld (?), (réponse : oui !)

Cela ne devrait techniquement pas poser problème, il suffira de convertir les champs du schéma en colonnes avec le type associé. En faisant un petit tour d'horizon, je suis tombé sur cette librairie python qui semble faire exactement cela.

[GT-CNIG-DDU]

Vu et effectivement cela semble correspondre exactement au besoin ! @Mavialle suit nos échanges. Merci Pierlou !

[Doctor-Who]

Merci pour cette ressource, pourrait-on avoir un exemple de commande pour générer le standard dans une base PostGreSQL à partir du schema.json (pour ceux qui ne sont pas familiers avec le Python) ?

[Pierlou]

Bonjour, une solution possible serait :

import json
from tableschema_sql.mapper import Mapper

with open('schema.json', 'r', encoding='utf8') as f:
    schema = json.load(f)
    f.close()

mapper = Mapper(prefix='')
query = "CREATE TABLE {schema['name']} (\n"
for col in mapper.convert_descriptor(bucket='', descriptor=schema)[0]:
    query += f'{col} {col.type},\n'
query += ')'
print(query)

Par exemple avec ce schéma :

CREATE TABLE schema-sites-economiques (
pole_id TEXT,
pole_nom TEXT,
pole_vocadomi TEXT,
pole_identif_date DATE,
pole_actu_date DATE,
pole_nb_site FLOAT,
pole_nb_etab FLOAT,
pole_nb_emploi FLOAT,
pole_surface FLOAT,
pole_geompoint TEXT,
pole_rayonnement TEXT,
)

[Doctor-Who]

Merci je vais tester.

[Doctor-Who]

J'ai finalement pu tester et il est à noter que la requête générée est plus ambiguë et moins bien formatée que le standard tel qu'il est décrit dans le schema.json. De plus il ne sait pas interpréter la géométrie. Je vais creuser la question et fournir à la communauté un template SQL plus précis.

CREATE TABLE site_eco (
site_id TEXT,
pole_id TEXT,
site_nom TEXT,
site_type TEXT,
site_vocadomi TEXT,
site_etat TEXT,
site_surf_brute FLOAT,
site_surf_utile FLOAT,
site_creation_date DATE,
site_identif_date DATE,
site_actu_date DATE,
source_producteur TEXT,
site_description TEXT,
site_quali_territoire TEXT,
site_evol_urba TEXT,
site_url TEXT,
site_epci_nom TEXT,
site_epci_siren TEXT,
site_comm_nom TEXT,
site_comm_insee TEXT,
site_moa_type TEXT,
site_moa_nom TEXT,
site_moa_amngt TEXT,
site_moa_comm TEXT,
site_uf_nbre FLOAT,
site_uf_vacant_nbre FLOAT,
site_taux_vacance FLOAT,
site_uf_bati_nbre FLOAT,
site_nb_etab FLOAT,
site_nb_emploi FLOAT,
site_geomsurf TEXT,
site_geompoint TEXT,
site_media TEXT,
site_surf_comm_dispo FLOAT,
site_surf_projet FLOAT,
site_voca_industrielle TEXT,
site_voca_commerciale TEXT,
site_voca_tertiaire TEXT,
site_voca_artisanale TEXT,
site_voca_touristique TEXT,
site_voca_portuaire TEXT,
site_voca_aeroportuaire TEXT,
reseau_eau_pluviale TEXT,
reseau_eau_potable TEXT,
reseau_assainissement TEXT,
reseau_gaz TEXT,
reseau_electrique TEXT,
reseau_fibre_optique TEXT,
reseau_fret_ferroviaire TEXT,
reseau_fluvial TEXT,
desserte_tc TEXT,
desserte_route_nom TEXT,
autoroute_nom TEXT,
autoroute_echang_dist FLOAT,
gare_nom TEXT,
gare_dist FLOAT,
gare_fret_nom TEXT,
gare_fret_dist FLOAT,
aeroport_nom TEXT,
aeroport_dist FLOAT,
port_nom TEXT,
port_dist FLOAT,
)