OPENROWSET BULK (Transact-SQL)

Syntaxis voor SQL Server, Azure SQL Database en 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

Syntaxis voor Fabric Data Warehouse

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> toegang tot bestanden op lokale schijf
• \\<network-share\<file path> toegang krijgen tot bestanden op netwerkshares
• adls://<container>@<storage>.dfs.core.windows.net/<file path> toegang tot Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> toegang tot Azure Blob Storage
• s3://<ip-address>:<port>/<file path> toegang tot s3-compatibele opslag

Vanaf SQL Server 2017 (14.x) kan de data_file zich in Azure Blob Storage bevinden. Zie Voorbeelden van bulktoegang tot gegevens in Azure Blob Storagevoor voorbeelden.

• https://<storage>.blob.core.windows.net/<container>/<file path> toegang tot Azure Blob Storage of Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> toegang tot Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> toegang tot Azure Data Lake Storage
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - voor toegang tot Fabric OneLake (momenteel in preview)

U kunt OPENROWSET(BULK) gegevens rechtstreeks lezen uit bestanden die zijn opgeslagen in de Fabric OneLake, met name vanuit de map Bestanden van een Fabric Lakehouse. Dit elimineert de noodzaak voor externe faseringsaccounts (zoals ADLS Gen2 of Blob Storage) en maakt werkruimtebeheerde SaaS-systeemeigen opname met infrastructuurmachtigingen mogelijk. Deze functionaliteit ondersteunt:

• Lezen vanuit Files mappen in Lakehouses
• Werkruimte-naar-magazijnbelastingen binnen dezelfde tenant
• Systeemeigen identiteits afdwinging met Behulp van Microsoft Entra-id

De FORMAT optie hoeft niet te worden opgegeven als de bestandsextensie in het pad eindigt op .csv, .tsv, .parquet, .parq, , .jsonlof .ldjson.ndjson. De functie weet bijvoorbeeld OPENROWSET(BULK) dat de indeling parquet is gebaseerd op de extensie in het volgende voorbeeld:

SELECT *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

Als het bestandspad niet eindigt op een van deze extensies, moet u bijvoorbeeld een FORMAT, bijvoorbeeld:

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

FORMATFILE

Hiermee specificeert u het volledige pad van een indelingsbestand. SQL Server ondersteunt twee typen indelingsbestanden: XML en niet-XML.

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

Een indelingsbestand is vereist voor het definiëren van kolomtypen in de resultatenset. De enige uitzondering is wanneer SINGLE_CLOB, SINGLE_BLOBof SINGLE_NCLOB is opgegeven; In dat geval is het indelingsbestand niet vereist.

Zie Een indelingsbestand gebruiken om gegevens (SQL Server) bulksgewijs te importerenvoor meer informatie over het opmaken van bestanden.

Vanaf SQL Server 2017 (14.x) kan de format_file_path zich in Azure Blob Storage bevinden. Zie Voorbeelden van bulktoegang tot gegevens in Azure Blob Storagevoor voorbeelden.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definieert de hoofdlocatie van het bestandspad voor de indeling. Hiermee kunt u relatieve paden gebruiken in de optie 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'
);

De indelingsbestandsgegevensbron wordt gemaakt met CREATE EXTERNAL DATA SOURCE. Naast de hoofdlocatie kan er aangepaste referenties worden gedefinieerd die kunnen worden gebruikt voor toegang tot de bestanden op die locatie.

PARSER_VERSION = 'parser_version'

Van toepassing op: Fabric Data Warehouse

Hiermee geeft u parserversie moet worden gebruikt bij het lezen van bestanden. Momenteel ondersteunde CSV parserversies zijn 1.0 en 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'
)

CSV-parserversie 1.0 is standaard en bevat uitgebreide functies. Versie 2.0 is gebouwd voor prestaties en biedt geen ondersteuning voor alle opties en coderingen.

Details van CSV-parserversie 1.0:

• De volgende opties worden niet ondersteund: HEADER_ROW.
• Standaardeindtekens zijn \r\n, \n en \r.
• Als u (nieuwe regel) opgeeft \n als het rijeindteken, wordt deze automatisch voorafgegaan door een \r teken (regelterugloop), wat resulteert in een rijeindteken van \r\n.

Details van CSV-parserversie 2.0:

• Niet alle gegevenstypen worden ondersteund.
• De maximale lengte van de tekenkolom is 8000.
• Maximale maximale rijgrootte is 8 MB.
• De volgende opties worden niet ondersteund: DATA_COMPRESSION.
• Lege tekenreeks tussen aantekening ("") wordt geïnterpreteerd als een lege tekenreeks.
• De optie DATEFORMAT SET wordt niet gehonoreerd.
• Ondersteunde indeling voor datumgegevenstype : YYYY-MM-DD
• Ondersteunde indeling voor tijdgegevenstype : HH:MM:SS[.fractional seconds]
• Ondersteunde indeling voor het gegevenstype datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• Standaardeindtekens zijn \r\n en \n.

ESCAPE_CHAR = 'char'

Hiermee geeft u het teken in het bestand dat wordt gebruikt om te ontsnappen aan zichzelf en alle scheidingstekens in het bestand, bijvoorbeeld:

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

Als het escape-teken wordt gevolgd door een andere waarde dan het teken zelf, of een van de scheidingstekens, wordt het escape-teken genegeerd bij het lezen van de waarde.

De ESCAPECHAR parameter wordt toegepast, ongeacht of de FIELDQUOTE parameter wel of niet is ingeschakeld. Het wordt niet gebruikt om het aanhalingsteken te ontsnappen. Het aanhalingsteken moet worden voorafgegaan door een ander aanhalingsteken. Het aanhalingsteken kan alleen in de kolomwaarde worden weergegeven als de waarde is ingekapseld met aanhalingstekens.

In het volgende voorbeeld worden komma's (,) en backslash (\) ontsnapt en weergegeven als \, en \\:

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

HEADER_ROW = { TRUE | ONWAAR }

Hiermee geeft u op of een CSV-bestand veldnamenrij bevat die niet moet worden geretourneerd met andere gegevensrijen. Een voorbeeld van een CSV-bestand met een koptekst wordt weergegeven in het volgende voorbeeld:

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

De standaardinstelling is FALSE. Ondersteund in PARSER_VERSION='2.0'. Als TRUE, de kolomnamen worden gelezen uit de eerste rij volgens FIRSTROW argument. Als TRUE en schema wordt opgegeven met behulp WITHvan, wordt de binding van kolomnamen uitgevoerd op kolomnaam, niet op rangtelposities.

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

ORDER ( { kolom [ ASC | DESC ] } [ ,... n ] [ UNIEK ] )

Een optionele hint die aangeeft hoe de gegevens in het gegevensbestand worden gesorteerd. Standaard wordt bij de bulkbewerking ervan uitgegaan dat het gegevensbestand niet is gerangschikt. De prestaties kunnen worden verbeterd als de queryoptimalisatie de volgorde kan benutten om een efficiënter queryplan te genereren. De volgende lijst bevat voorbeelden voor wanneer u een sorteerbewerking opgeeft, kan nuttig zijn:

• Rijen invoegen in een tabel met een geclusterde index, waarbij de rijensetgegevens worden gesorteerd op de geclusterde indexsleutel.
• De rijenset samenvoegen met een andere tabel, waarbij de kolommen sorteren en samenvoegen overeenkomen.
• De rijensetgegevens samenvoegen op basis van de sorteerkolommen.
• Gebruik de rijenset als een brontabel in de FROM component van een query, waarbij de kolommen sorteren en samenvoegen overeenkomen.

UNIQUE

Hiermee geeft u op dat het gegevensbestand geen dubbele vermeldingen bevat.

Als de werkelijke rijen in het gegevensbestand niet worden gesorteerd op basis van de opgegeven volgorde of als de UNIQUE hint is opgegeven en er dubbele sleutels aanwezig zijn, wordt een fout geretourneerd.

Kolomaliassen zijn vereist wanneer ORDER wordt gebruikt. De lijst met kolomaliassen moet verwijzen naar de afgeleide tabel die wordt geopend door de BULK-component. De kolomnamen die zijn opgegeven in de ORDER component verwijzen naar deze kolomaliaslijst. Grote waardetypen (varchar(max), nvarchar(max), varbinary(max), en XML- en LOB-typen (large object) (tekst, ntext en afbeelding) kunnen niet worden opgegeven.

Inhoudsopties

SINGLE_BLOB

Retourneert de inhoud van data_file als een rijset met één rij van het type varbinary(max).

SINGLE_CLOB

Als u data_file als ASCII leest, wordt de inhoud geretourneerd als een rijset met één rij, één kolom van het type varchar(max), met behulp van de sortering van de huidige database.

SINGLE_NCLOB

Als u data_file als Unicode leest, wordt de inhoud geretourneerd als een rijset met één rij met één kolom van het type nvarchar(max), met behulp van de sortering van de huidige database.

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

MET schema

Het WITH schema geeft de kolommen op die de resultatenset van de OPENROWSET-functie definiëren. Het bevat kolomdefinities voor elke kolom die als resultaat wordt geretourneerd en geeft een overzicht van de toewijzingsregels die de onderliggende bestandskolommen binden aan de kolommen in de resultatenset.

In het volgende voorbeeld:

• De country_region kolom heeft het type varchar(50) en verwijst naar de onderliggende kolom met dezelfde naam
• De date kolom verwijst naar een CSV-/Parquet-kolom of JSONL-eigenschap met een andere fysieke naam
• De cases kolom verwijst naar de derde kolom in het bestand
• De fatal_cases kolom verwijst naar een geneste Parquet-eigenschap of JSONL-subobject

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>

De naam van de kolom die wordt geretourneerd in de resultatenrijset. De gegevens voor deze kolom worden gelezen uit de onderliggende bestandskolom met dezelfde naam, tenzij deze worden overschreven door <column_path> of <column_ordinal>. De naam van de kolom moet voldoen aan de regels voor kolomnaam-id's.

<column_type>

Het T-SQL-type van de kolom in de resultatenset. De waarden uit het onderliggende bestand worden geconverteerd naar dit type wanneer OPENROWSET de resultaten retourneert. Zie Gegevenstypen in Fabric Warehouse voor meer informatie.

<column_path>

Een door punt gescheiden pad (bijvoorbeeld $.description.location.lat) dat wordt gebruikt om te verwijzen naar geneste velden in complexe typen zoals Parquet.

<column_ordinal>

Een getal dat de fysieke index vertegenwoordigt van de kolom die wordt toegewezen aan de kolom in de WITH component.

• Als OPENROWSET(BULK...) brontabel in een INSERT of MERGE instructie worden gegevens bulksgewijs geïmporteerd uit een gegevensbestand in een tabel. Zie BULK INSERT of OPENROWSET(BULK...) gebruiken om gegevens te importeren in SQL Servervoor meer informatie.
• Wanneer de optie OPENROWSET BULK wordt gebruikt met een INSERT-instructie, ondersteunt de BULK-component tabelhints. Naast de normale tabelhints, zoals TABLOCK, kan de BULK-component de volgende gespecialiseerde tabelhints accepteren: IGNORE_CONSTRAINTS (negeert alleen de beperkingen voor CHECK en FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSen KEEPIDENTITY. Zie tabelhints (Transact-SQL)voor meer informatie.
• Zie INSERT...SELECT * FROM OPENROWSET(BULK...)voor meer informatie over het gebruik van instructies. Zie Vereisten voor minimale logboekregistratie bij bulkimportvoor informatie over wanneer rij-invoegbewerkingen die worden uitgevoerd door bulkimport in het transactielogboek worden geregistreerd.
• Wanneer u gegevens importeert met het volledige herstelmodel, optimaliseert OPENROWSET (BULK ...) logboekregistratie niet.

In Microsoft Fabric worden de ondersteunde functies samengevat in de tabel:

SQLCHAR-, SQLNCHAR- of SQLBINARY-gegevens bulksgewijs importeren

OPENROWSET(BULK...) ervan uitgaat dat, indien niet opgegeven, de maximale lengte van SQLCHAR, SQLNCHARof SQLBINARY gegevens niet groter is dan 8000 bytes. Als de geïmporteerde gegevens zich in een LOB-gegevensveld bevinden dat varchar(max), nvarchar(max)- of varbinary(max) -objecten bevat die groter zijn dan 8.000 bytes, moet u een XML-indelingsbestand gebruiken dat de maximale lengte voor het gegevensveld definieert. Als u de maximale lengte wilt opgeven, bewerkt u het indelingsbestand en declareert u het kenmerk MAX_LENGTH.

SQLXML-documenten bulksgewijs exporteren of importeren

Als u SQLXML-gegevens bulksgewijs wilt exporteren of importeren, gebruikt u een van de volgende gegevenstypen in uw indelingsbestand.

A. OPENROWSET gebruiken om bestandsgegevens BULKSGEWIJS IN TE VOEGEN in een varbinary(max)-kolom

Van toepassing op: Alleen SQL Server.

In het volgende voorbeeld wordt een kleine tabel gemaakt voor demonstratiedoeleinden en worden bestandsgegevens ingevoegd uit een bestand met de naam Text1.txt in de C: hoofdmap in een varbinary(max) -kolom.

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. De OPENROWSET BULK-provider gebruiken met een indelingsbestand om rijen op te halen uit een tekstbestand

Van toepassing op: Alleen SQL Server.

In het volgende voorbeeld wordt een indelingsbestand gebruikt om rijen op te halen uit een tekstbestand met tabscheidingstekens, values.txt met de volgende gegevens:

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

Het indelingsbestand, values.fmt, beschrijft de kolommen in 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

Met deze query worden die gegevens opgehaald:

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

C. Een indelingsbestand en codepagina opgeven

Van toepassing op: Alleen SQL Server.

In het volgende voorbeeld ziet u hoe u zowel de indelingsbestands- als codepaginaopties tegelijk gebruikt.

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

D. Toegang tot gegevens uit een CSV-bestand met een indelingsbestand

Van toepassing op: ALLEEN SQL Server 2017 (14.x) en latere versies.

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

E. Toegang tot gegevens uit een CSV-bestand zonder een indelingsbestand

Van toepassing op: Alleen SQL Server.

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. Toegang krijgen tot gegevens uit een bestand dat is opgeslagen in Azure Blob Storage

Van toepassing op: ALLEEN SQL Server 2017 (14.x) en latere versies.

In SQL Server 2017 (14.x) en latere versies wordt in het volgende voorbeeld een externe gegevensbron gebruikt die verwijst naar een container in een Azure-opslagaccount en een databasereferentie die is gemaakt voor een handtekening voor gedeelde toegang.

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

Zie OPENROWSETvoor volledige voorbeelden, waaronder het configureren van de referentie en externe gegevensbron.

G. Importeren in een tabel vanuit een bestand dat is opgeslagen in Azure Blob Storage

In het volgende voorbeeld ziet u hoe u de opdracht OPENROWSET gebruikt om gegevens uit een CSV-bestand te laden in een Azure Blob Storage-locatie waarop u de SAS-sleutel hebt gemaakt. De Azure Blob Storage-locatie is geconfigureerd als een externe gegevensbron. Hiervoor is een database-gerichte referentie vereist met behulp van een gedeelde toegangssignatuur die is versleuteld met een hoofdsleutel in de gebruikersdatabase.

-- 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. Een beheerde identiteit gebruiken voor een externe bron

Van toepassing op: Azure SQL Managed Instance en Azure SQL Database

In het volgende voorbeeld wordt een referentie gemaakt met behulp van een beheerde identiteit, een externe bron gemaakt en vervolgens gegevens uit een CSV geladen die wordt gehost op de externe bron.

Maak eerst de referentie en geef blobopslag op als de externe bron:

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

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

Laad vervolgens gegevens uit het CSV-bestand dat wordt gehost in blobopslag:

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

I. OPENROWSET gebruiken voor toegang tot verschillende Parquet-bestanden met behulp van S3-compatibele objectopslag

Van toepassing op: SQL Server 2022 (16.x) en latere versies.

In het volgende voorbeeld wordt gebruikgemaakt van toegang tot verschillende Parquet-bestanden vanaf verschillende locaties, die allemaal zijn opgeslagen op S3-compatibele objectopslag:

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. OPENROWSET gebruiken voor toegang tot verschillende Delta-tabellen vanuit Azure Data Lake Gen2

Van toepassing op: SQL Server 2022 (16.x) en latere versies.

In dit voorbeeld heet de gegevenstabelcontainer Contosoen bevindt zich in een Azure Data Lake Gen2-opslagaccount.

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. OPENROWSET gebruiken om een query uit te voeren op een openbare anonieme gegevensset

In het volgende voorbeeld wordt gebruikgemaakt van de openbaar beschikbare gegevensset voor gele taxiritën in NYC.

Maak eerst de gegevensbron:

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

Voer een query uit voor alle bestanden met .parquet extensie in mappen die overeenkomen met het naampatroon:

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

A. Een Parquet-bestand lezen uit Azure Blob Storage

In het volgende voorbeeld ziet u hoe u 100 rijen uit een Parquet-bestand kunt lezen:

SELECT TOP 100 * 
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet'
);

B. Een aangepast CSV-bestand lezen

In het volgende voorbeeld ziet u hoe u rijen uit een CSV-bestand kunt lezen met een veldnamenrij en expliciet opgegeven afsluittekens die rijen en velden scheiden:

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',');

C. Het schema van de bestandskolom opgeven tijdens het lezen van een bestand

In het volgende voorbeeld ziet u hoe u expliciet het schema van de rij opgeeft dat als resultaat van de OPENROWSET functie wordt geretourneerd:

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/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. Gepartitioneerde gegevenssets lezen

In het volgende voorbeeld ziet u hoe u de functie filepath() kunt gebruiken om de onderdelen van de URI te lezen vanuit het overeenkomende bestandspad:

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. Het schema van de bestandskolom opgeven tijdens het lezen van een JSONL-bestand

In het volgende voorbeeld ziet u hoe u expliciet het schema van de rij opgeeft dat als resultaat van de OPENROWSET functie wordt geretourneerd:

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.dfs.core.windows.net/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'
     );

Als een kolomnaam niet overeenkomt met de fysieke naam van een kolom in de eigenschappen als het JSONL-bestand, kunt u de fysieke naam in het JSON-pad opgeven na de typedefinitie. U kunt meerdere eigenschappen gebruiken. Als u bijvoorbeeld $.location.latitude wilt verwijzen naar de geneste eigenschappen in parquet complexe typen of JSON-subobjecten.

Meer voorbeelden

• Door bestandsinhoud bladeren met de functie OPENROWSET
• Prestatierichtlijnen voor Fabric Data Warehouse
• Gegevens opnemen in uw warehouse met Behulp van Transact-SQL

Meer voorbeelden

Zie de volgende artikelen voor meer voorbeelden die het gebruik van OPENROWSET(BULK...)weergeven:

• bulksgewijs importeren en exporteren van gegevens (SQL Server)
• Voorbeelden van bulksgewijs importeren en exporteren van XML-documenten (SQL Server)
• Identiteitswaarden behouden bij het bulksgewijs importeren van gegevens (SQL Server)
• Null-waarden of standaardwaarden behouden tijdens het bulksgewijs importeren (SQL Server)
• Een indelingsbestand gebruiken om gegevens bulksgewijs te importeren (SQL Server)
• Tekenindeling gebruiken om gegevens (SQL Server) te importeren of te exporteren
• Een indelingsbestand gebruiken om een tabelkolom (SQL Server) over te slaan
• Een indelingsbestand gebruiken om een gegevensveld (SQL Server) over te slaan
• Een indelingsbestand gebruiken om tabelkolommen toe te wijzen aan gegevensbestandsvelden (SQL Server)
• query's uitvoeren op gegevensbronnen met behulp van OPENROWSET in Azure SQL Managed Instances
• veld- en rijeindtekens (SQL Server) opgeven