Skip to content
Utiliser une instruction CREATE EXTERNAL TABLE AS SELECT (CETAS) pour transformer des donnéesEncapsuler une instruction CETAS dans une procédure stockée.Inclure une procédure stockée de transformation de données dans un pipeline
Filtrer les lignes et les colonnes.Renommer les champsConvertir 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
créer une autre DATA_SOURCEBULK avec chemin complet (https://mystorage.blob.core.windows.net/data/sales_orders/*.csv)
de fournir des paramètres,de récupérer des sortiesd’inclure une logique supplémentaire dans un appel de procédure uniqueRéduit le trafic réseau entre le client et le serveurFournit une limite de sécuritéFacilite la maintenancePerformances 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
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.
Support formation Microsoft Azure
- Pages
Utiliser des pools SQL serverless Azure Synapse pour transformer des données dans un lac de données
Gonzague Ducos
Transformer des fichiers de données avec l’instruction CREATE EXTERNAL TABLE AS SELECT
SQL permet de :
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 :
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 :
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
Inclure une procédure stockée de transformation de données dans un pipeline
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.