azure
Support formation Microsoft Azure
azure
Support formation Microsoft Azure
DP-203

icon picker
Utiliser des pools SQL serverless Azure Synapse pour transformer des données dans un lac de données

Gonzague Ducos
Utiliser une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS) pour transformer des données
Encapsuler une instruction CETAS dans une procédure stockée.
Inclure une procédure stockée de transformation de données dans un pipeline

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

SQL permet :
Filtrer les lignes et les colonnes.
Renommer les champs
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
CREATE EXTERNAL TABLE AS SELECT (CETAS) conserve 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.

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

Si pool SQL serverless : créer une base de données pour y stocker les objets.

Source de données externe

-- Créer une source de données externe pour un compte de stockage Azure
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
);
Autre approche avec encapsuler des informations d’identification :
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
);

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
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

Utilisation de l’instruction CETAS

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';
DATA_SOURCE doit être précisé dans les deux clauses (emplacement de la table et emplacement des fichiers). Si on veut un emplacement différent :
créer une autre DATA_SOURCE
BULK avec chemin complet (https://mystorage.blob.core.windows.net/data/sales_orders/*.csv)

Suppression de tables externes

DROP EXTERNAL TABLE SpecialOrders;

Encapsuler des transformations de données dans une procédure stockée

Facilite l’opérationnalisation des transformations de données en vous permettant :
de fournir des paramètres,
de récupérer des sorties
d’inclure une logique supplémentaire dans un appel de procédure unique
CREATE PROCEDURE usp_special_orders_by_year @order_year INT
AS
BEGIN
-- Supprime la table si elle existe
IF EXISTS (
SELECT * FROM sys.external_tables
WHERE name = 'SpecialOrders'
)
DROP EXTERNAL TABLE SpecialOrders
-- Crée la table externe avec les commandes spéciales
-- d'une année spécifiée
CREATE EXTERNAL TABLE SpecialOrders
WITH (
LOCATION = 'special_orders/',
DATA_SOURCE = files,
FILE_FORMAT = ParquetFormat
)
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
OPENROWSET(
BULK 'sales_orders/*.csv',
DATA_SOURCE = 'files',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
HEADER_ROW = TRUE
) AS source_data
WHERE OrderType = 'Special Order'
AND YEAR(OrderDate) = @order_year
END
Réduit le trafic réseau entre le client et le serveur
Fournit une limite de sécurité
Facilite la maintenance
Performances améliorées : sont compilées lors de leur première exécution, et le plan d’exécution résultant est conservé dans le cache et réutilisé lors de l’exécution suivante de la même procédure stockée

Inclure une procédure stockée de transformation de données dans un pipeline

Activité Supprimer qui supprime le dossier cible pour les données transformées dans le lac de données, la cas échéant.
Activité Procédure stockée qui se connecte à votre pool SQL serverless et exécute la procédure stockée qui encapsule votre opération CETAS.

Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.