Support formation Microsoft Azure

Pages

DP-203

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 de :

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'

);

⁠

CREATE EXTERNAL FILE FORMAT (Transact-SQL) - SQL Server⁠

⁠

Utilisation de l’instruction CETAS

CREATE EXTERNAL TABLE SpecialOrders

WITH (

-- details for storing results

LOCATION = 'special_orders/',

DATA_SOURCE = files,

FILE_FORMAT = ParquetFormat

)

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

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

)

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