5 - Nettoyage et enrichissement de la données

Dans cette partie nous allons nettoyer la donnée brutes issue des fichiers puis nous allons l’enrichir à l’aide d’une jointure. Pour cela nous allons créer des vues afin de stocker la logique de transformation. Ces vues seront stockés dans un dataset BigQuery.

Une vue est une représentation logique d’une requête utilisateur réalisée sur une ou plusieurs tables. Une vue ne stocke pas de donnée.

Pour saisir une nouvelle requête SQL dans l’éditeur Bigquery, Cliquez sur le bouton Saisir une nouvelle requête de la page d’acceuil de Bigquery.

Nettoyage des données

Création du dataset <PREFIX>_ds_curated

✏ De même que dans la partie 3, cliquez sur les trois points à droite du nom du projet, puis sur "Créer un ensemble de donnée" nommé <PREFIX>_ds_curated.

Vous pouvez aussi utiliser la commande suivante dans cloud shell pour créer le dataset:

Λ\: $ bq --location=europe-west1 mk --dataset ${PROJECT}:${PREFIX}_ds_curated

Création de la vue nettoyée de la table des naissances

Créons une vue naissances_curated afin de nettoyer les données de la table raw tb_naissances. Vous pouvez utiliser une requête dans la console BigQuery pour effectuer le netoyage suivant:

remplacer 1 par Homme, 2 par Femme et sinon Autre dans la colonne sexe
ignorer les lignes dont l’année de naissance est nulle
Convertir en valeur numérique le nombre de naissance et l’année
réaliser une étape de déduplication dans le cas où un fichier aurait été chargé plusieurs fois. On pourra prendre la dernière date de chargement comme référence.

Vous pouvez compléter le code suivant en SQL pour réaliser l’exercice

CREATE VIEW `workshop-data-platform.<PREFIX>_ds_curated.naissances_curated`
AS
  (SELECT
    -- humanize sexe column,
    -- prenom column,
    -- annee_naissance as numeric format,
    -- code_departement,
    -- nombre as numeric format
  FROM
    `workshop-data-platform.<PREFIX>_ds_raw.tb_naissances`
  WHERE
  --  filter out row with null values for annee_naissance
  -- deduplicate identical row in case a file is loaded multiple times
  )

⚠️ SPOILER ALERT: Solution d’implémentation en SQL

CREATE VIEW `workshop-data-platform.<PREFIX>_ds_curated.naissances_curated`
AS
  (SELECT
    CASE SAFE_CAST(sexe AS int)
      WHEN 1 THEN "Homme"
      WHEN 2 THEN "Femme"
    ELSE
    "Autre"
  END
    AS sexe,
    prenom,
    SAFE_CAST(annee_naissance AS int) AS annee_naissance,
    code_departement,
    SAFE_CAST(nombre AS int) AS nombre
  FROM
    `workshop-data-platform.<PREFIX>_ds_raw.tb_naissances`
  WHERE
    SAFE_CAST(annee_naissance AS int) IS NOT NULL
  QUALIFY ROW_NUMBER() OVER (PARTITION BY sexe, prenom, annee_naissance, code_departement) = 1
  )

Création de la vue dénormalisé

Créons une vue dénormalisé dans le dataset curated qui fait la jointure entre les départements et les naissances. Cette vue sera ensuite consommée par un logiciel de visualisation.

La dénormalisation consiste à regrouper plusieurs tables liées par des références, en une seule table, en réalisant les opérations de jointure adéquates. L’objectif de la dénormalisation est d’améliorer les performances en recherche en implémentant les jointures plutôt qu’en les calculant.

Vous pouvez utiliser une requête dans la console BigQuery pour effectuer les opérations suivantes:

Jointure de la vue nettoyée des naissances avec la table des départements sur le code du département
Récupération des champs code_departement, nom_departement et nom_region de la table tb_departement

Vous pouvez compléter le code SQL suivant pour réaliser l’exercice

 CREATE VIEW `workshop-data-platform.<PREFIX>_ds_curated.naissances_denormalise`
  AS
  (
    SELECT
      -- sexe,
      -- prenom,
      -- annee_naissance,
      -- code_departement,
      -- nom_departement,
      -- nom_region,
      -- nombre
    FROM
      `workshop-data-platform.<PREFIX>_ds_curated.naissances_curated` naissances
    -- JOIN with departments table
  )

⚠️ SPOILER ALERT: Solution d’implémentation en SQL

 CREATE VIEW `workshop-data-platform.<PREFIX>_ds_curated.naissances_denormalise`
  AS
  (
    SELECT
      naissances.sexe,
      naissances.prenom,
      naissances.annee_naissance,
      dep.code_departement,
      dep.nom_departement,
      dep.nom_region,
      naissances.nombre
    FROM
      `workshop-data-platform.<PREFIX>_ds_curated.naissances_curated` naissances
    LEFT JOIN `workshop-data-platform.<PREFIX>_ds_raw.tb_departements` dep ON dep.code_departement = naissances.code_departement
  )

Conclusion

Nous venons de créer une vue qui renvoie les données nettoyé et dénormalisés des naissances par departement. Cette vue est prête à être consommé par un logiciel de visualisation comme LookerStudio.

Pour des raison de performances nous pourrions utiliser des procedures stockées qui alimente des tables plutôt que les vues. Les jointures ne seraient alors pas recaclculé à chaque fois mais écrites une seule fois dans la table.