Préparation de données avec Microsoft Fabric, le SQL

On continu le match avec un vieux compagnon le SQL.

Contexte

#

Afin de découvrir les différents types de traitement proposé par Microsoft Fabric, je vous ai proposé un match entre 3 différentes possibilités : Préparation de données avec Microsoft Fabric, le match !.

Je me propose ici de réalisé les transformations avec du SQL.

Pour rappel, le langage SQL est le langage utilisé pour interagir avec les bases de données. Il permet d’interroger les données contenues dans des tables et permet aussi de réaliser des modifications sur les données.

Création de notre table SQL

#

Dans le cadre de Microsoft Fabric, nous avons 2 possibilités pour utiliser le SQL :

• le point de terminaison SQL du lakehouse permet de faire des requêtes seulement en lecture sur les fichiers managés. C’est les fichiers présentés sous la forme de table.
• le warehouse qui permet de gérer une base de données complète en gérant à la fois les requêtes de lecture que de modification de données.

Pour le match, j’ai choisi d’utiliser le point de terminaison SQL du lakehouse.
La première étape est de transformer notre fichier non managé en table. Une table dans le point de terminaison SQL est un fichier en mode managé sur lequel on peut réaliser des requêtes SQL.

Ouvrez le lakehouse LeMatch de l’espace de travail Fabric - Le match [Silver].

1. Rendez-vous dans le dossier du raccourci Bronze_Le_Match.
2. Sélectionnez les 3 points sur la ligne du fichier.
3. Dans le menu, choisissez Charger dans Tables.

1. Entrez countries dans le nom de la table
2. Appuyez sur Confirmer pour continuer.

1. Sélectionnez les tables du lakehouse.
2. Sélectionnez la table countries.
3. Vous pouvez voir un aperçu du contenu.
4. Passez en mode point de terminaison SQL pour pouvoir écrire une requête SQL sur la table.

Vous pouvez aussi directement ouvrir le point de terminaison SQL depuis l’espace de travail.

Tâches préparatoires à une transformation de données

#

Charger les données

#

Pour charger les données d’une table en SQL on utilise la requête suivante :

SELECT * FROM Schema.Table

Dans notre cas le nom de schéma est le nom par défaut dbo et le nom de la table est countries, la requête SQL est donc :

SELECT * FROM dbo.countries

1. Dans le point de terminaison SQL, sélectionnez Nouvelle requête SQL.
2. Saisir le code de la requête.
3. Appuyez sur Run.
4. Vous pouvez voir le résultat.

Obtenir un échantillonnage des données

#

Souvent lire un échantillon des données, les 100 premières lignes par exemple suffit à ce faire une première idée du jeu de données.
En SQL vous pouvez le faire comme cela :

SELECT TOP 100 * 
FROM dbo.countries

1. Vous pouvez constater que l’on a lu que 100 lignes.

Voir les métadonnées du jeu de données

#

Pour lire les métadonnées de la table, on peut utiliser la requête suivante :

select c.name as column_name
    ,c.column_id
    ,schema_name(t.schema_id) as type_schema
    ,t.name as type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
from sys.columns as c 
join sys.types as t on c.user_type_id=t.user_type_id
where c.object_id = object_id('dbo.countries')
order by c.column_id;

Réaliser les transformations

#

Sélectionner certaines colonnes

#

Pour ne sélectionner que les 3 colonnes name, currencies et capital, on utilise la requête suivante :

SELECT 
    name, 
    currencies,
    capital 
FROM dbo.countries

Filtrer les lignes à conserver

#

Pour ne sélectionner que les lignes ayant pour valeur EUR dans la colonne currencies, on utilise la requête suivante :

SELECT * 
FROM dbo.countries
WHERE currencies = 'EUR'

Modifier le nom des colonnes

#

Pour ne sélectionner que les 3 colonnes name, currencies et capital, et les renommer nom, devise et capitale, on utilise la requête suivante :

SELECT 
    name AS nom, 
    currencies AS devise,
    capital as capitale 
FROM dbo.countries

Modifier le type de données des colonnes

#

Pour convertir la colonne area qui est de type nombre décimal en nombre entier, on utilise la requête suivante :

SELECT 
  convert(int, area) as area
FROM dbo.countries

Gestion des valeurs absentes

#

En SQL les valeurs vides sont appelées valeur null. Pour remplacer les valeurs vides par la valeur -1 de la colonne landlocked et ne conserver en plus que les colonnes name, currencies et capital, on utilise la requête suivante :

SELECT 
    name, 
    currencies,
    capital 
    ,COALESCE(landlocked, -1) as landlocked
FROM dbo.countries

Enlever les doublons

#

Pour obtenir la liste des devises dédoublonnée, on utilise la requête suivante :

SELECT 
    currencies
FROM dbo.countries
GROUP BY 
    currencies

Sauvegarder le résultat dans la couche Silver

#

Le résultat que l’on souhaite conserver dans la couche silver est le résultat de la requête suivante :

SELECT * 
FROM dbo.countries
WHERE currencies = 'EUR'

Pour copier le résultat de la requête SQL réalisé sur la zone bronse dans la zone silver, nous allons utiliser un pipeline de données.
Vous trouverez un exemple de pipeline de données dans mon article Votre premier pipeline d’intégration de données avec Microsoft Fabric.

Le magasin de données de type lakehouse ne gère, dans la preview, que les tables et les fichiers. Il n’est pas possible d’exécuter une requête SQL, pour résoudre ce problème j’ai créé un warehouse dans l’espace de travail nommé MatchSQL. Le warehouse est un moteur de base de données avec plus de fonctionnalités. Il peut notamment exécuter des requêtes sur les tables du lakehouse dans l’activité de copie du pipeline. Pour cela, il suffit d’ajouter le nom du lakehouse dans la requête SQL. Si aucun nom n’est indiqué c’est l’endroit ù on est connecté qui est utilisé.
La requête SQL devient donc :

SELECT * 
FROM LeMatch.dbo.countries
WHERE currencies = 'EUR'

Je créer donc un pipeline dans l’espace de travail Fabric - Le match [Silver].

1. Sélectionnez Data warehouse comme magasin de données.
2. Sélectionnez le warehouse.
3. Indiquez que vous allez utiliser une requête.
4. Entrez le code SQL de la requête.

1. Sélectionnez Lakehouse comme magasin de données.
2. Sélectionnez le lakehouse.
3. Indiquez que vous allez utiliser une table comme destination.
4. Cochez Modifier pour saisir librement le nom de la table.
5. Saisir le nom de la table destination.

Enregistrez et exécutez le pipeline de données, puis rendez-vous dans le lakehouse de l’espace de travail Fabric - Le match [Silver].

1. Dans l’explorateur du lakehouse ouvrez Tables
2. Votre nouvelle table est bien créée.

Merci de votre attention.