Transformer des fichiers de données avec l’instruction CREATE EXTERNAL TABLE AS SELECT

5 minutes

Le langage SQL inclut de nombreuses fonctionnalités et fonctions qui vous permettent de manipuler des données. Par exemple, vous pouvez utiliser SQL pour :

Filtrer les lignes et les colonnes dans un jeu de données.
Renommer les champs de données et convertir entre types de données.
Calculer les champs de données dérivés.
Manipuler les valeurs de chaîne.
Grouper et agréger les données.

Les pools SQL serverless Azure Synapse peuvent être utilisés pour exécuter des instructions SQL qui transforment les données et conservent les résultats en tant que fichier dans un lac de données pour les traiter et les interroger par la suite. Si vous êtes familiarisé avec la syntaxe Transact-SQL, vous pouvez concevoir une instruction SELECT qui applique la transformation spécifique qui vous intéresse, puis stocker les résultats de l’instruction SELECT dans un format de fichier sélectionné avec un schéma de table de métadonnées qui peut être interrogé avec SQL.

Vous pouvez utiliser une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS) dans un pool SQL dédié ou un pool SQL serverless pour conserver les résultats d’une requête dans une table externe, qui stocke ses données dans un fichier dans le lac de données.

L’instruction CETAS inclut une instruction SELECT qui interroge et manipule les données de toute source de données valide (qui peut être une table ou une vue existante dans une base de données, ou une fonction OPENROWSET qui lit les données basées sur fichiers du lac de données). Les résultats de l’instruction SELECT sont ensuite conservés dans une table externe, qui est un objet de métadonnées dans une base de données qui fournit une abstraction relationnelle sur les données stockées dans les fichiers. Le schéma suivant illustre le concept visuellement :

Schéma montrant une instruction CREATE EXTERNAL TABLE AS SELECT qui enregistre les résultats de requête sous la forme d’un fichier.

En appliquant cette technique, vous pouvez utiliser SQL pour extraire et transformer les données de fichiers ou de tables, et stocker les résultats transformés pour les traiter et les analyser en aval. Les opérations suivantes sur les données transformées peuvent être effectuées sur la table relationnelle dans la base de données du pool SQL ou directement sur les fichiers de données sous-jacents.

Création d’objets de base de données externes pour prendre en charge CETAS

Pour utiliser des expressions CETAS, vous devez créer les types d’objet suivants dans une base de données d’un pool SQL serverless ou dédié. Lors de l’utilisation d’un pool SQL serverless, créez ces objets dans une base de données personnalisée (créée avec l’instruction CREATE DATABASE), pas dans la base de données intégrée.

Source de données externe

Une source de données externe encapsule une connexion à un emplacement de système de fichiers dans un lac de données. Vous pouvez ensuite utiliser cette connexion pour spécifier un chemin relatif dans lequel les fichiers de données de la table externe créés par l’instruction CETAS sont enregistrés.

Si les données sources de l’instruction CETAS se trouvent dans des fichiers avec le même chemin de lac de données, vous pouvez utiliser la même source de données externe dans la fonction OPENROWSET utilisée pour l’interroger. Vous pouvez également créer une source de données externe distincte pour les fichiers sources ou utiliser un chemin de fichier complet dans la fonction OPENROWSET.

Pour créer une source de données externe, utilisez l’instruction CREATE EXTERNAL DATA SOURCE, comme illustré dans cet exemple :

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

L’exemple précédent suppose que les utilisateurs exécutant des requêtes qui utilisent la source de données externe ont des autorisations suffisantes pour accéder aux fichiers. Une autre approche consiste à encapsuler des informations d’identification dans la source de données externe afin de pouvoir les utiliser pour accéder aux données de fichier sans accorder à tous les utilisateurs des autorisations pour les lire directement :

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Conseil

En plus de l’authentification SAS, vous pouvez définir des informations d’identification qui utilisent l’identité managée (l’identité Microsoft Entra utilisée par votre espace de travail Azure Synapse), un principal Microsoft Entra spécifique ou une authentification directe basée sur l’identité de l’utilisateur exécutant la requête (il s’agit du type d’authentification par défaut). Pour en savoir plus sur l’utilisation des informations d’identification dans un pool SQL serverless, consultez la documentation Contrôler l’accès au compte de stockage pour le pool SQL serverless dans Azure Synapse Analytics dans la documentation Azure Synapse Analytics.

Format de fichier externe

L’instruction CETAS crée une table avec ses données stockées dans des fichiers. Vous devez spécifier le format des fichiers que vous souhaitez créer en tant que format de fichier externe.

Pour créer un format de fichier externe, utilisez l’instruction CREATE EXTERNAL FILE FORMAT, comme illustré dans cet exemple :

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Conseil

Dans cet exemple, les fichiers sont enregistrés au format Parquet. Vous pouvez également créer des formats de fichiers externes pour d’autres types de fichier. Pour plus d’informations, consultez CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Utilisation de l’instruction CETAS

Après avoir créé une source de données externe et un format de fichier externe, vous pouvez utiliser l’instruction CETAS pour transformer des données et stocker les résultats dans une table externe.

Par exemple, supposons que les données sources que vous souhaitez transformer se composent de commandes d’articles dans des fichiers texte délimités par des virgules qui sont stockés dans un dossier dans un lac de données. Vous souhaitez filtrer les données pour inclure uniquement les commandes marquées « commande spéciale » et enregistrer les données transformées en tant que fichiers Parquet dans un autre dossier du même lac de données. Vous pouvez utiliser la même source de données externe pour les dossiers sources et de destination, comme illustré dans cet exemple :

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Les paramètres LOCATION et BULK de l’exemple précédent sont des chemins relatifs pour les résultats et les fichiers sources respectivement. Les chemins sont relatifs à l’emplacement du système de fichiers référencé par la source de données externe des fichiers.

Un point important à comprendre est que vous devez utiliser une source de données externe pour spécifier l’emplacement où les données transformées pour la table externe doivent être enregistrées. Lorsque les données sources basées sur des fichiers sont stockées dans la même hiérarchie de dossiers, vous pouvez utiliser la même source de données externe. Sinon, vous pouvez utiliser une deuxième source de données pour définir une connexion aux données sources ou utiliser le chemin complet, comme illustré dans cet exemple :

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Suppression de tables externes

Si vous n’avez plus besoin de la table externe contenant les données transformées, vous pouvez la supprimer de la base de données en utilisant l’instruction DROP EXTERNAL TABLE, comme illustré ici :

DROP EXTERNAL TABLE SpecialOrders;