Ingérer des données dans votre entrepôt à l’aide de Transact-SQL

S’applique à :✅ Entrepôt dans Microsoft Fabric

Le langage Transact-SQL offre des options que vous pouvez utiliser pour charger des données à grande échelle à partir de tables existantes de votre lakehouse et de votre entrepôt dans de nouvelles tables de votre entrepôt. Ces options sont pratiques si vous devez créer des versions d’une table avec des données agrégées, des versions de tables avec un sous-ensemble des lignes ou créer une table à partir d’une requête complexe. Observons quelques exemples.

Créer une table avec le résultat d’une requête

L’entrepôt dans Microsoft Fabric vous permet de créer facilement une table basée sur un résultat de requête T-SQL, à l’aide des instructions T-SQL suivantes :

  • CREATE TABLE AS SELECT Instruction CTAS qui vous permet de créer une table dans votre entrepôt à partir de la sortie d’une SELECT instruction.
  • SELECT INTO clause de requête qui vous permet de sélectionner des résultats à partir de n’importe quelle source de table et de rediriger les résultats vers une nouvelle table. Il s’agit d’une fonctionnalité standard dans le langage T-SQL.

Ces deux instructions sont similaires, de sorte que les exemples suivants se concentrent sur l’instruction CTAS.

L’instruction CTAS exécute l’opération d’ingestion dans la nouvelle table en parallèle, ce qui le rend très efficace pour la transformation des données et la création de nouvelles tables dans votre espace de travail.

Vous pouvez utiliser les options suivantes pour la partie SELECT de l’instruction CTAS :

  • Lecture d’une table d’entrepôt, telle qu’une table intermédiaire.
  • Lecture d’un dossier Lakehouse Delta Lake à l’aide d’une table générée automatiquement dans le point de terminaison d’analytique SQL pour Lakehouse.
  • Lecture de fichiers CSV, Parquet ou JSONL directement à partir d’Azure Data Lake ou du stockage Blob Azure avec la fonction OPENROWSET.

Pour charger un exemple de jeu de données, suivez les étapes d’ingestion de données dans votre entrepôt à l’aide de l’instruction COPY pour créer les exemples de données dans votre entrepôt.

Créer une table à partir de la table Warehouse

Le premier exemple montre comment créer une table qui est une copie de la table existante dbo.TaxiTrips , mais filtrée pour inclure uniquement les données de l’année 2023 :

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Créer une table à partir du dossier Delta Lake

Les dossiers Delta Lake conservés dans OneLake sont automatiquement représentés sous forme de tables s’ils sont stockés dans le dossier /Tables dans un lakehouse. Le code suivant crée une nouvelle table TaxiTrips_2023 à partir du dossier Delta Lake /Tables/TaxiTrips dans le lakehouse MyLakehouse :

CREATE TABLE dbo.TaxiTrips_2023
AS
SELECT * 
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Vous pouvez référencer le dossier Delta Lake à l’aide de la notation en trois parties qui fait référence au lakehouse où sont stockés les fichiers. Tous les exemples présentés dans la section précédente s’appliquent aux dossiers Delta Lake.

Créer une table à partir d’un fichier CSV/Parquet/JSONL

Vous pouvez également créer une table directement à partir d’un fichier externe à l’aide de la OPENROWSET fonction. Par exemple, l’exemple T-SQL suivant utilise des paramètres fictifs pour illustrer comment vous pouvez importer un fichier Parquet public.

CREATE TABLE dbo.<table_name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.parquet') AS data;

Vous pouvez créer une table en transformant des données à partir d’un fichier CSV externe et publiquement disponible :

CREATE TABLE dbo.<table name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.csv') AS data;

Vous pouvez également créer une table en transformant des données à partir d’un fichier JSONL externe disponible publiquement :

CREATE TABLE dbo.<table name>
AS
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>.jsonl') AS data;

Ingestion de données dans des tables existantes avec des requêtes T-SQL

Les exemples précédents créent des tables basées sur le résultat d’une requête. Pour répliquer les exemples mais sur des tables existantes, le INSERT ... SELECT modèle peut être utilisé.

Ingérer des données à partir de la table Warehouse

Le code suivant ingère de nouvelles données d’une table d’entrepôt dans une table existante :

INSERT INTO dbo.TaxiTrips_2023
SELECT *
FROM dbo.TaxiTrips
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Les critères de requête de l’instruction SELECT peuvent être n’importe quelle requête valide, à condition que les types de colonnes de requête résultants s’alignent sur les colonnes de la table de destination. Si les noms de colonnes sont spécifiés et incluent uniquement un sous-ensemble des colonnes de la table de destination, toutes les autres colonnes sont chargées en tant que NULL. Pour plus d’informations, consultez Utilisation d’INSERT INTO...SELECT pour importer en bloc des données avec une journalisation et un parallélisme minimaux.

Ingérer des données à partir du dossier Delta Lake

Les dossiers Delta Lake conservés dans OneLake sont automatiquement représentés sous forme de tables s’ils sont stockés dans /Tables un dossier dans un lakehouse.

Le code suivant ingère de nouvelles données depuis la section du dossier Delta Lake /Tables/TaxiTrips dans le lakehouse MyLakehouse*.

INSERT INTO dbo.TaxiTrips_2023
SELECT *
FROM MyLakehouse.dbo.TaxiTrips 
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Ingérer des données à partir du fichier CSV/Parquet/JSONL

Vous pouvez utiliser la OPENROWSET fonction comme source pour ingérer des fichiers Parquet, CSV ou JSON à partir du stockage :

INSERT INTO dbo.<table name>
SELECT *
FROM OPENROWSET(BULK 'https://<storage account>.blob.core.windows.net/public/<subfolder>/<file name>') AS data
WHERE DATEPART(YEAR, tpep_pickup_datetime) = '2023';

Vous pouvez lire plusieurs fichiers à l’aide de caractères génériques tels que *.parquet, ou en ciblant des répertoires partitionnés tels que /year=*/month=*. Pour optimiser les performances, appliquez des filtres dans la clause WHERE pour éliminer les lignes et partitions inutiles pendant l’exécution de la requête.

Cet exemple est similaire à celui utilisé dans l’ingestion avec COPY INTO. La commande COPY INTO est plus facile à utiliser, en particulier pour les chargements simples de données source à destination. Toutefois, si vous devez transformer des données sources (telles que la conversion de valeurs ou la jointure avec d’autres tables), l’utilisation INSERT ... SELECT vous offre la possibilité d’effectuer des transformations pendant l’ingestion.

Ingérer des données à partir de OneLake

Vous pouvez utiliser la OPENROWSET fonction comme source pour ingérer des données à partir du stockage Fabric OneLake. Remplacez {workspaceId} et {lakehouseId} avec les GUID d’espace de travail et lakehouse correspondants dans l’exemple suivant :

INSERT INTO dbo.TaxiTrips_2023
SELECT *
FROM OPENROWSET(BULK 'https://onelake.dfs.fabric.microsoft.com/{workspaceId}/{lakehouseId}/Files/year=*/month=*/*.parquet') AS data
WHERE data.filepath(1) = '2023'

Cet exemple s’appuie sur le précédent qui lit les données d’Azure Data Lake Storage. Utilisez cette approche lorsque vous devez transformer des données sources, par exemple en convertissant des valeurs, en joignant d’autres tables ou en lisant des partitions spécifiques. Dans ce cas, l’utilisation INSERT ... SELECT offre la possibilité d’appliquer des transformations pendant l’ingestion des données.

Importation de données depuis des tables sur différents entrepôts et lakehouses

Pour les deux CREATE TABLE AS SELECT et INSERT ... SELECT, l’instruction SELECT peut également référencer des tables sur des entrepôts qui sont différents de l’entrepôt où votre table de destination est stockée, à l’aide de requêtes inter-entrepôts. Pour ce faire, utilisez la convention de nommage en trois parties [warehouse_or_lakehouse_name.][schema_name.]table_name. Prenons l’exemple des ressources d’espace de travail suivantes :

  • Une maison de données appelée taxi_lakehouse contenant les données les plus récentes.
  • Un entrepôt nommé reference_warehouse avec les tables utilisées pour les données de référence.
  • Un entrepôt nommé research_warehouse où la table de destination est créée.

Vous pouvez créer une table qui utilise un nommage en trois parties pour combiner les données des tables sur ces ressources d’espace de travail :

CREATE TABLE research_warehouse.dbo.taxi_trips
AS
SELECT *
FROM taxi_lakehouse.dbo.TaxiTrips AS latest
INNER JOIN reference_warehouse.dbo.TaxiTrips AS reference
ON latest.vendorId_lpep = reference.vendorId_lpep;

Pour en savoir plus sur les requêtes entre entrepôts, consultez Écrire une requête SQL inter-bases de données.

Auditer et surveiller l’ingestion T-SQL

Les opérations CTAS exécutées INSERT ... SELECT via T-SQL s’affichent dans l’historique/l’activité des requêtes de l’entrepôt et peuvent être surveillées en même temps que d’autres opérations d’entrepôt.

Options d’ingestion des données

Voici d’autres façons d’ingérer des données dans votre entrepôt :

Contenu connexe

  • Last updated on