Ce TP prend la suite du TP sur la qualité des données. L’objectif ici est de croiser ces données avec des données provenant d’autres jeux de données: les régions et les informations sur les parlementaires. On souhaite à la fin obtenir un schéma en flocon avec les dimensions suivantes:
On essaiera également de d’intégrer les données parlementaires, mais cela ne consituera pas une dimension classique.
Récupérer les informations sur les communes, les départements et les régions au format csv dans le dépôt github d’etalab (référencées depuis data.gouv.fr).
Charger ces données en base, en vous inspirant de ce qui est fait dans le TP sur la qualité des données. On souhaite obtenir une hierarchie d’emplacements géographiques avec les niveaux: commune, département, région, pays (avec une table par niveau).
Penser à définir les contraintes de clés primaire/étrangères sur les tables.
Créer une table issue de votre table des subventions mentionant l’année, le programme, le montant, ainsi que la référence à la commune (qui doit être une clé étrangère bien sûr). On enrichira cette création par la suite avec d’autre attributs pour référencer les autres dimensions, il faut donc garder en tête que la table actuelle sera supprimée puis recréée. Remarque: en pratique cette stratégie ne fonctionne que si le jeu de données est suffisament petit, sinon il faut prendre un échantillon des données lors de la phase de mise au point.
Attention: les références sur les pays ne sont pas inclues dans les données sur les communes, il faut donc les traiter à part en ajoutant des valeurs fictives pour les niveaux intermédiaires.
On peut trouver sur la page de l’INSEE sur la NAF un ensemble de fichiers excel par niveau.
Charger le contenu de chacun de ces fichiers en base, soit en passant par un export vers du CSV depuis Excel, soit en les chargeant depuis Python vers PostgreSQL, via par exemple Pandas.
On veut une table par niveau (de section à sous-classe), le lien avec le niveau supérieur pouvant être fait via le début de l’identifiant actuel. Les sections constituent une exception et il faudra faire le lien avec le fichier des niveaux emboîtés.
Modifier ensuite le script de création de la table de faits pour ajouter une référence vers le NAF.
Attention au format de la référence NAF, qui est légèrement différent dans les données du PLF (qu’il faudra donc adapter pour référencer correctement la dimension).
Comme pour le COG précédement, on prendra soin de définir les clés primaires et étrangères.
On peut constater des incohérences sur la manière dont est saisi le nom des parlementaires.
La base de données pedago contient les données json sur les parementaires, prises à partir dudépôt suivant: Fichier historique des Députés et de leurs mandats.
Afin d’accéder aux tables qui contiennent ces données, on utilisera un foreign data wrapper, comme dans lexemple ci-dessous (pensez à bien passer en revue chaque définition et à changer les logins et les mots de passe):
-- on crée le serveur "distant", ici vers la base 'pedago'
-- on lève ici les droits d'écritures (que les utilisateurs n'ont pas de toute façon)
CREATE SERVER IF NOT EXISTS ro_local_bd_pedago
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'bd-pedago.univ-lyon1.fr', port '5432', dbname 'pedago', updatable 'false');
-- pour annuler
-- DROP SERVER IF EXISTS ro_local_bd_pedago;
-- on dit que l'utilisateur local LoginPostgres accède au distant via le même compte LoginPostgres : il faut préciser le mot de passe
CREATE USER MAPPING IF NOT EXISTS FOR mon_login SERVER ro_local_bd_pedago OPTIONS (user 'mon_login', password 'mon_mot_de_passe_postgres');
-- pour annuler
-- DROP USER MAPPING IF EXISTS FOR mon_login SERVER ro_local_bd_pedago;
-- enfin pour ajouter, ici UNE TABLE DU SCHEMA json
IMPORT FOREIGN SCHEMA json
LIMIT TO (organe,acteur,deport)
FROM SERVER ro_local_bd_pedago INTO public;
-- on teste
SELECT COUNT(*)
FROM organe;
-- résultat: 8529
Attention, en cas d’erreur, il faut bien supprimer les objets créés avant de recommencer.
Une fois les données json accessibles, il faudra les comprendre (typiquement en en ouvrant un exemplaire dans un éditeur ou dans un navigateur qui permet de “plier” certaines sections et/ou en utilisant jq ou yq pour reformater le contenu).
En utilisant les fonctions de manipulation JSON de PostgreSQL, créer des vues matérialisées relationnelles pour représenter, les parlementaires, les mandats et les organes associés. Pour cela, on commencera par écrire des requêtes qui permettent de voir ces informations de manière relationnelle, puis on créera ensuite les vues correspondantes.
On fera ensuite le lien entre subventions et parlementaires. On rappelle qu’il peut y avoir plusieurs parlementaires pour une même subvention. On peut également remarquer que les noms et prénoms ne sont pas toujours indiqués dans le même ordre. Il y a également des cas où c’est un organe et pas un parlementaire qui est référencé dans une subvention. Pour ces cas, on pourra créer un parlementaire fictif ayant un mandat fictif qui référencera l’organe mentionné.
Enfin on placera dans une table à part entière les couples subvention-parlementaire pour lesquel le parlementaire n’a pas pu être retrouvé. Dans la table des subventions, on créera une colonne spéciale pour indiquer que le parlementaire n’a pas été retrouvé afin de distinguer les valeurs nulles pour cause d’absence de parlementaire et celles pour cause de parlementaire non retrouvé.
Si la requête ne renvoie qu’une valeur, utiliser jsonb_path_query_first(donnee_json, string_avec_jsonpath) pour extraire la valeur de la donnée json.
Si la requête JSONPath peut renvoyer plusieurs résultats, utiliser une requête de la forme suivante:
SELECT a.toto, extrait_json as titi
FROM a, LATERAL json_path_query(a.ma_donnee_json, '$.mon.json.path') extrait_json
Si on veut transformer une valeur texte du format jsonb au format varchar, on peut faire mavaleur #>> '{}'.
Un exemple complet:
-----------------------------------------
-- exemples jsonb
-----------------------------------------
-- Données exemples
CREATE TABLE ex_json(id serial primary key, jsondata jsonb);
INSERT INTO ex_json(jsondata) VALUES
('{ "toto": [ "a", "b", "c"], "titi": "d", "tutu": {"foo": "bar"} }'),
('{ "toto": [ "e", "f", "g"], "titi": ["h","i"], "tutu": {"foo": "baz"} }');
-- Valeurs uniques d'abord en jsonb, puis en varchar
SELECT id,
jsonb_path_query_first(jsondata, '$.tutu.foo') as en_jsonb,
jsonb_path_query_first(jsondata, '$.tutu.foo') #>> '{}' as en_varchar
FROM ex_json;
-- Valeurs multiples
SELECT ex_json.id, machin as toto_jsonb, machin #>> '{}' as toto_varchar
FROM ex_json, jsonb_path_query(jsondata, '$.toto[*]') machin;
-- En mélangeant les deux
SELECT ex_json.id,
jsonb_path_query_first(jsondata, '$.tutu.foo') #>> '{}' as foo_varchar,
machin #>> '{}' as toto_varchar
FROM ex_json, jsonb_path_query(jsondata, '$.toto[*]') machin;
-- Distinguer les situations selon le type de noeud json
SELECT id, jsonb_typeof(jsonb_path_query_first(jsondata, '$.titi')) as type_de_titi
FROM ex_json;
-- Extraction différente selon le type json
SELECT id, jsonb_path_query_first(jsondata, '$.titi') as titi
FROM ex_json
WHERE jsonb_typeof(jsonb_path_query_first(jsondata, '$.titi')) = 'string'
UNION ALL
SELECT ex_json.id, un_titi as titi
FROM ex_json, LATERAL jsonb_path_query(jsondata, '$.titi[*]') un_titi -- attention pas de first ici
WHERE jsonb_typeof(jsonb_path_query_first(ex_json.jsondata, '$.titi')) = 'array';
-- La même chose avec une CTE sur factoriser un peu le code
WITH titi_id AS
(SELECT id,
jsonb_typeof(jsonb_path_query_first(jsondata, '$.titi')) as type_de_titi,
jsonb_path_query_first(jsondata, '$.titi') as titi
FROM ex_json)
SELECT id, titi #>> '{}' as titi
FROM titi_id
WHERE type_de_titi = 'string'
UNION ALL
SELECT id, titi_json #>> '{}' as titi
FROM titi_id, LATERAL jsonb_path_query(titi, '$[*]') titi_json
WHERE titi_id.type_de_titi = 'array'
;