OPENROWSET BULK (Transact-SQL)

Syntax

Für SQL Server, Azure SQL-Datenbank, SQL Database in Fabric und 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

Syntax für 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> für den Zugriff auf Dateien auf einem lokalen Datenträger
• \\<network-share\<file path> für den Zugriff auf Dateien auf Netzwerkfreigaben
• adls://<container>@<storage>.dfs.core.windows.net/<file path> zugriff auf Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> für den Zugriff auf Azure Blob Storage
• s3://<ip-address>:<port>/<file path> für den Zugriff auf s3-kompatiblen Speicher

Ab SQL Server 2017 (14.x) kann sich die data_file in Azure Blob Storage befinden. Beispiele finden Sie unter Beispiele für den Massenzugriff auf Daten in Azure Blob Storage.

• https://<storage>.blob.core.windows.net/<container>/<file path> für den Zugriff auf Azure Blob Storage oder Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> zugriff auf Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> zugriff auf Azure Data Lake Storage
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - um auf OneLake in Microsoft Fabric zuzugreifen

Wenn Sie auf Daten zugreifen, die in Azure Data Lake Storage Gen2 gespeichert sind, verwenden Sie das abfss://<container>@<storage>.dfs.core.windows.net/<file path> oder https://<storage>.dfs.core.windows.net/<container>/<file path> das URI-Format anstelle des Blob-Endpunkts. Beide bieten vollständige Unterstützung für den hierarchischen Namensraum (HNS), der Verzeichnissemantik, optimierte Dateioperationen und POSIX-ähnliche Zugriffskontrolllisten (ACLs) ermöglicht.

Im Gegensatz dazu stellt der blob Endpunkt keine hierarchischen Namensraummerkmale offen und behandelt alle Pfade als flache Objektschlüssel. Dies kann zu geringerer Leistung, eingeschränktem Verzeichnisverhalten und Inkompatibilität mit Engines führen, die die Semantik des Azure Data Lake Storage Gen2-Dateisystems erwarten.

Sie können OPENROWSET(BULK) Daten direkt aus Dateien lesen, die im OneLake in Microsoft Fabric gespeichert sind, speziell aus dem Dateiordner eines Fabric Lakehouse. Diese Funktion beseitigt externe Staging-Konten (wie ADLS Gen2 oder Blob Storage) und ermöglicht workspace-gesteuerte, SaaS-native Ingestion mittels Fabric-Berechtigungen. Diese Funktionalität unterstützt Folgendes:

• Lesen aus Files Ordnern in Lakehouses
• Arbeitsbereich-zu-Lager-Lasten innerhalb desselben Mandanten
• Erzwingung nativer Identitäten mithilfe der Microsoft Entra-ID

Siehe die Einschränkungen, die sowohl für als OPENROWSET(BULK)auch COPY INTO gelten.

Du musst die FORMAT Option nicht angeben, wenn die Dateiendung im Pfad mit .csv, .tsv, .parquet, .parq, .jsonl, , .ldjsonoder .ndjsonendet. Zum Beispiel weiß die OPENROWSET(BULK) Funktion, dass das Format Parquet ist, basierend auf der Erweiterung im folgenden Beispiel:

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

Wenn der Dateipfad nicht mit einer dieser Erweiterungen endet, müssen Sie beispielsweise folgendes FORMATangeben:

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

FORMATFILE

Gibt den vollständigen Pfad einer Formatdatei an. SQL Server unterstützt zwei Typen von Formatdateien: XML- und Nicht-XML-Formatdateien.

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

Du brauchst eine Formatdatei, um Spaltentypen im Ergebnisset zu definieren. Die einzige Ausnahme ist, wenn Sie , SINGLE_BLOB, oder SINGLE_NCLOBangebenSINGLE_CLOB; in diesem Fall benötigen Sie keine Formatdatei.

Für weitere Informationen zum Formatieren von Dateien siehe Verwenden Sie eine Formatdatei zum Massenimport von Daten (SQL Server).

Ab SQL Server 2017 (14.x) kann es format_file_path in Azure Blob Storage sein. Beispiele finden Sie unter Beispiele für den Massenzugriff auf Daten in Azure Blob Storage.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definiert den Stammspeicherort des Formatdateipfads. Mit dieser Datenquelle können Sie relative Pfade in der FORMATFILE Option verwenden.

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'
);

Erstelle das Datei-Daten-Format mit CREATE EXTERNAL DATA SOURCE. Zusätzlich zum Root-Standort kann es eine benutzerdefinierte Zugangsberechtigung definieren, um auf die Dateien an diesem Speicherort zuzugreifen.

PARSER_VERSION

Gilt für: Nur Fabric Data Warehouse

Gibt die beim Lesen von Dateien zu verwendende Parserversion an. Derzeit unterstützte CSV Parserversionen sind 1.0 und 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-Parser-Version 2.0 ist die Standardimplementierung, die auf Leistung optimiert ist, unterstützt jedoch nicht alle Altoptionen und Codierungen, die in Version 1.0 verfügbar sind. Bei Verwendung von OPENROWSET fällt das Fabric Data Warehouse automatisch auf Version 1.0 zurück, wenn man die Optionen nur in dieser Version verwendet, selbst wenn die Version nicht explizit angegeben ist. In manchen Fällen muss man die Version 1.0 explizit angeben, um Fehler zu beheben, die durch nicht unterstützte Funktionen verursacht werden, die vom Parser Version 2.0 gemeldet werden.

Einzelheiten zu CSV-Parserversion 1.0:

• Die folgenden Optionen werden nicht unterstützt: HEADER_ROW.
• Standardterminatoren sind \r\n\n und \r.
• Wenn Sie (Zeilenumbruch) als Zeilenendpunkt angeben \n , wird es automatisch einem \r Zeichen (Wagenrücklauf) vorangestellt, das zu einem Zeilenendpunkt führt \r\n.

Einzelheiten zu CSV-Parserversion 2.0:

• Nicht alle Datentypen werden unterstützt.
• Die maximal zulässige Zeichenlänge für Spalten beträgt 8.000.
• Die maximale Zeilengröße beträgt 8 MB.
• Folgende Optionen werden nicht unterstützt: DATA_COMPRESSION.
• Eine leere Zeichenfolge in Anführungszeichen ("") wird als leere Zeichenfolge interpretiert.
• DATEFORMAT SET Die Option wird nicht anerkannt.
• Unterstütztes Format für datumsdatentyp : YYYY-MM-DD
• Unterstütztes Format für den Zeitdatentyp : HH:MM:SS[.fractional seconds]
• Unterstütztes Format für datetime2-Datentyp : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• Standardterminatoren sind \r\n und \n.

ESCAPE_CHAR

Gibt das Zeichen in der Datei an, das verwendet wird, um sich selbst und alle Trennzeichenwerte in der Datei zu escapen, z. B.:

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

Wenn auf das Escapezeichen ein Wert folgt, bei dem es sich nicht um das Escapezeichen selbst oder um einen der Trennzeichenwerte handelt, wird das Escapezeichen beim Lesen des Werts gelöscht.

Der ESCAPECHAR Parameter wird unabhängig davon angewendet, ob der FIELDQUOTE Parameter aktiviert ist oder nicht aktiviert ist. Er fungiert nicht als Escapezeichen für das Zitierzeichen. Das Anführungszeichen muss mit einem weiteren Anführungszeichen als Escapezeichen versehen werden. Das Anzeichen kann nur innerhalb des Spaltenwerts angezeigt werden, wenn der Wert mit an zitierten Zeichen gekapselt ist.

Im folgenden Beispiel werden Kommas (,) und umgekehrter Schrägstrich (\) als Escapezeichen und dargestellt als\,:\\

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

HEADER_ROW

Gibt an, ob eine CSV-Datei Kopfzeilen enthält, die nicht mit anderen Datenzeilen zurückgegeben werden sollen. Ein Beispiel für eine CSV-Datei mit einer Kopfzeile wird im folgenden Beispiel gezeigt:

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

Der Standardwert ist FALSE. Unterstützt im PARSER_VERSION='2.0' In-Fabric Data Warehouse. Wenn TRUEdie Spaltennamen gemäß Argument aus der ersten Zeile FIRSTROW gelesen werden. Wenn TRUE und Schema mithilfe WITHangegeben wird, erfolgt die Bindung von Spaltennamen durch Spaltennamen, nicht durch Ordnungspositionen.

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

ORDER ( { Spalte [ ASC | DESC ] } [ ,... n ] [ EINDEUTIG ] )

Ein optionaler Hinweis, der angibt, wie die Daten in der Datendatei sortiert sind. Standardmäßig geht der Massenvorgang davon aus, dass die Datendatei nicht sortiert ist. Die Leistung kann verbessert werden, wenn der Abfrageoptimierer die Reihenfolge ausnutzen kann, um einen effizienteren Abfrageplan zu generieren. Die folgende Liste enthält Beispiele für die Angabe einer Sortierung, die von Vorteil sein kann:

• Einfügen von Zeilen in eine Tabelle mit einem gruppierten Index, in der die Rowsetdaten nach dem Schlüssel des gruppierten Index sortiert sind.
• Verknüpfen des Rowsets mit einer anderen Tabelle, in der die Sortierungs- und Joinspalten übereinstimmen.
• Aggregieren der Rowsetdaten nach den Sortierspalten.
• Verwenden des Rowsets als Quelltabelle in der FROM Klausel einer Abfrage, wobei die Sortier- und Verknüpfungsspalten übereinstimmen.

UNIQUE

Gibt an, dass die Datendatei keine doppelten Einträge enthält.

Wenn die tatsächlichen Zeilen in der Datendatei nicht nach der von dir angegebenen Reihenfolge sortiert sind oder wenn du angibst, dass Hinweis UNIQUE und doppelte Schlüssel vorhanden sind, wird ein Fehler zurückgegeben.

Spaltenaliase sind erforderlich, wenn du ORDER. Die Spaltenaliasliste muss auf die abgeleitete Tabelle verweisen, auf die die Klausel BULK zugriff. Die Spaltennamen, die Sie in der Klausel angeben, ORDER beziehen sich auf diese Spaltenaliasliste. Man kann keine großen Werttypen (varchar(max),nvarchar(max),varbinary(max) und xml) und große Objekttypen (LOB) (Text, ntext und Bild) angeben.

Inhaltsoptionen

SINGLE_BLOB

Gibt den Inhalt data_file als einspaltiges Zeilenzeilen-Rowset vom Typ varbinary(max)zurück.

SINGLE_CLOB

Liest data_file als ASCII und gibt den Inhalt als einzeilige, einspaltige Zeilenmenge vom Typ varchar(max) zurück, wobei die Sortierung der aktuellen Datenbank verwendet wird.

SINGLE_NCLOB

Liest data_file als Unicode und gibt den Inhalt als einzeilige, einspaltige Zeilenmenge vom Typ nvarchar(max) zurück, wobei die Sortierung der aktuellen Datenbank verwendet wird.

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

WITH-Schema

Das WITH Schema gibt die Spalten an, die das Resultset der OPENROWSET-Funktion definieren. Sie enthält Spaltendefinitionen für jede Spalte, die OPENROWSET die Abbildungsregeln zurückgibt und umreißt, die die zugrundeliegenden Dateispalten an die Spalten im Ergebnisset binden.

Im folgenden Beispiel:

• Die Spalte country_region hat den Typ varchar(50) und bezieht sich auf die zugrundeliegende Spalte mit demselben Namen.
• Die Spalte date bezieht sich auf eine CSV- oder Parquet-Spalte oder JSONL-Eigenschaft mit einem anderen physischen Namen.
• Die Spalte cases verweist auf die dritte Spalte in der Datei.
• Die Spalte fatal_cases verweist auf eine verschachtelte Parquet-Eigenschaft oder JSONL-Unterobjekt.

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>

Der Name der Spalte, die OPENROWSET im Ergebnis-Zeilensatz zurückgegeben wird. OPENROWSET liest Daten für diese Spalte aus der zugrundeliegenden Dateispalte mit demselben Namen, es sei denn, Sie überschreiben sie mit <column_path> oder <column_ordinal>. Der Spaltenname muss den Regeln für Spaltennamen-Identifikatoren folgen.

<column_type>

Der T-SQL-Typ der Spalte im Resultset. OPENROWSET wandelt Werte aus der zugrundeliegenden Datei in diesen Typ um, wenn die Ergebnisse zurückgegeben werden. Weitere Informationen finden Sie unter Datentypen in Fabric Warehouse.

<column_path>

Ein punkttrennter Pfad (z. B. $.description.location.lat), der verwendet wird, um geschachtelte Felder in komplexen Typen wie Parkett zu referenzieren.

<column_ordinal>

Eine Zahl, die den physikalischen Index der Spalte repräsentiert, die auf die Spalte in der Klausel WITH abgebildet ist.

• Indem Sie sie als Quelltabelle in einer INSERT Or-Anweisung MERGE verwendenOPENROWSET(BULK...), importieren Sie Daten aus einer Datendatei in Massen in eine Tabelle. Weitere Informationen finden Sie unter Verwenden BULK INSERT oder OPENROWSET(BULK...), um Daten in SQL Server zu importieren.
• Wenn du die OPENROWSET BULK Option mit einer INSERT Anweisung verwendest, unterstützt die Klausel BULK Tabellenhinweise. Zusätzlich zu den regulären Tabellenhinweisen wie TABLOCK, kann die BULK-Klausel die folgenden spezialisierten Tabellenhinweise akzeptieren: IGNORE_CONSTRAINTS (ignoriert nur die CHECK- and FOREIGN KEY-Einschränkungen), IGNORE_TRIGGERS, KEEPDEFAULTS und KEEPIDENTITY. Weitere Informationen finden Sie unter Tabellenhinweise (Transact-SQL).
• Informationen zur Verwendung von INSERT...SELECT * FROM OPENROWSET(BULK...)-Anweisungen finden Sie unter Massenimport und -export von Daten (SQL Server). Für Informationen darüber, wann Zeilen-Einfügungsoperationen, die durch den Massenimport durchgeführt werden, im Transaktionsprotokoll protokolliert werden, siehe Voraussetzungen für minimale Protokollierung beim Massenimport.
• Wenn du OPENROWSET (BULK ...) Daten mit dem vollständigen Wiederherstellungsmodell importierst, optimiert das Logging nicht.

In Microsoft Fabric Data Warehouse fasst die folgende Tabelle die unterstützten Funktionen zusammen:

Massenimport von SQLCHAR-, SQLNCHAR- oder SQLBINARY-Daten

OPENROWSET(BULK...) nimmt an, dass, wenn man nicht anders angibt, die maximale Länge von SQLCHAR, SQLNCHAR, oder SQLBINARY Daten nicht 8.000 Bytes überschreitet. Wenn Sie Daten in ein LOB-Datenfeld importieren, das beliebige Objekte von varchar(max),nvarchar(max) oder varbinary(max) enthält, die 8.000 Bytes überschreiten, müssen Sie eine XML-Datei verwenden, die die maximale Länge für das Datenfeld definiert. Um die maximale Länge anzugeben, bearbeite die Formatdatei und deklariere das MAX_LENGTH Attribut.

Massenexport und -import von SQLXML-Dokumenten

Verwenden Sie in der Formatdatei einen der folgenden Datentypen für den Massenexport oder -import von SQLXML-Daten.

A. Verwenden Sie OPENROWSET, um BULK INSERT Daten in einer varbinary(max)-Spalte abzulegen.

Gilt für: Nur SQL Server.

Im folgenden Beispiel wird eine kleine Tabelle für Demonstrationszwecke erstellt und Dateidaten aus einer Datei Text1.txt eingefügt, die C: sich im Stammverzeichnis befindet, in eine varbinary(max) -Spalte.

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. Verwenden des OPENROWSET BULK-Anbieters mit einer Formatdatei zum Abrufen von Zeilen aus einer Textdatei

Gilt für: Nur SQL Server.

Im folgenden Beispiel werden mithilfe einer Formatdatei Zeilen aus der durch Tabstopps getrennten Textdatei values.txt abgerufen, die die folgenden Daten enthält:

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

Die Formatdatei values.fmt beschreibt die Spalten 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

Diese Abfrage ruft diese Daten ab:

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

C. Angeben einer Formatdatei und codepage

Gilt für: Nur SQL Server.

Im folgenden Beispiel wird gezeigt, wie Sie sowohl die Optionen für die Formatdatei als auch die Codeseiten gleichzeitig verwenden.

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

D. Zugreifen auf Daten aus einer CSV-Datei mit einer Formatdatei

Gilt für: Nur SQL Server 2017 (14.x) und höhere Versionen.

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

E. Zugreifen auf Daten aus einer CSV-Datei ohne Formatdatei

Gilt für: Nur 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. Zugreifen auf Daten aus einer Datei, die in Azure Blob Storage gespeichert ist

Gilt für: Nur SQL Server 2017 (14.x) und höhere Versionen.

In SQL Server 2017 (14.x) und höheren Versionen verwendet das folgende Beispiel eine externe Datenquelle, die auf einen Container in einem Azure-Speicherkonto und auf eine datenbankbezogene Anmeldeinformationen verweist, die für eine freigegebene Zugriffssignatur erstellt wurden.

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

Vollständige OPENROWSET Beispiele, einschließlich der Konfiguration der Anmeldeinformationen und der externen Datenquelle, finden Sie unter Beispiele für den Massenzugriff auf Daten in Azure Blob Storage.

G. Importieren in eine Tabelle aus einer Datei, die in Azure Blob Storage gespeichert ist

Das folgende Beispiel zeigt, wie man den OPENROWSET Befehl verwendet, um Daten aus einer CSV-Datei in einem Azure Blob-Speicherort zu laden, an dem der SAS-Schlüssel erstellt wurde. Sie konfigurieren den Azure Blob-Speicherstandort als externe Datenquelle. Dieser Prozess erfordert eine datenbankbezogene Zugangsdaten, die eine Shared-Access-Signatur (SAS) verwendet, die durch einen Masterschlüssel in der Benutzerdatenbank verschlüsselt wird.

-- 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. Verwenden einer verwalteten Identität für eine externe Quelle

Gilt für: Verwaltete Azure SQL-Instanz und Azure SQL-Datenbank

Das folgende Beispiel erstellt eine Zugangsdaten mit einer verwalteten Identität, erstellt eine externe Quelle und lädt dann Daten aus einer CSV, die auf der externen Quelle gehostet wird.

Erstellen Sie zuerst die Anmeldeinformation, und geben Sie den Blobspeicher als externe Quelle an:

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

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

Laden Sie als Nächstes Daten aus der CSV-Datei, die im Blobspeicher gehostet wird:

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

I. Verwenden von OPENROWSET für den Zugriff auf mehrere Parkettdateien mit S3-kompatiblem Objektspeicher

Gilt für: SQL Server 2022 (16.x) und höhere Versionen.

Das folgende Beispiel greift von verschiedenen Orten auf mehrere Parquet-Dateien zu, die alle auf S3-kompatiblen Objektspeicher gespeichert sind:

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. Verwenden von OPENROWSET für den Zugriff auf mehrere Delta-Tabellen aus Azure Data Lake Gen2

Gilt für: SQL Server 2022 (16.x) und höhere Versionen.

In diesem Beispiel heißt der Datentabellencontainer Contoso und befindet sich auf einem Azure Data Lake Gen2-Speicherkonto.

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. Verwenden von OPENROWSET zum Abfragen von öffentlich-anonymen Datasets

Im folgenden Beispiel wird das öffentlich verfügbare gelbe NYC-Taxireisedatensatz verwendet.

Erstellen Sie zuerst die Datenquelle:

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

Fragen Sie alle Dateien mit .parquet der Erweiterung in Ordnern ab, die mit dem Namensmuster übereinstimmen:

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

A. Lesen einer Parkettdatei aus Azure Blob Storage

Im folgenden Beispiel können Sie sehen, wie Sie 100 Zeilen aus einer Parkettdatei lesen:

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. Lesen einer benutzerdefinierten CSV-Datei

Im folgenden Beispiel sehen Sie, wie man Zeilen aus einer CSV-Datei mit einer Header-Zeile und explizit angegebenen Terminatorzeichen liest, die Zeilen und Felder trennen:

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. Angeben des Dateispaltenschemas beim Lesen einer Datei

Im folgenden Beispiel spezifizieren Sie das Schema der Zeile, die die OPENROWSET Funktion zurückgibt:

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. Partitionierte Datensätze lesen

Im folgenden Beispiel verwendet man die filepath() Funktion, um die Teile der URI aus dem abgestimmten Dateipfad auszulesen:

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. Angeben des Dateispaltenschemas beim Lesen einer JSONL-Datei

Im folgenden Beispiel können Sie sehen, wie Sie das Schema der Zeile explizit angeben, das als Ergebnis der OPENROWSET Funktion zurückgegeben wird:

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'
     );

Wenn ein Spaltenname nicht mit dem physischen Namen einer Spalte in den Eigenschaften übereinstimmt, wenn die JSONL-Datei vorhanden ist, können Sie den physischen Namen im JSON-Pfad nach der Typdefinition angeben. Sie können mehrere Eigenschaften verwenden. Um z $.location.latitude . B. auf die geschachtelten Eigenschaften in Komplexen Typen von Parkett oder JSON-Unterobjekten zu verweisen.

Weitere Beispiele

• Durchsuchen von Dateiinhalten mithilfe der OPENROWSET-Funktion
• Leistungsrichtlinien für Fabric Data Warehouse
• Erfassen von Daten in Ihrem Warehouse mithilfe von Transact-SQL

A. Verwenden Sie OPENROWSET, um eine CSV-Datei aus einem Fabric Lakehouse auszulesen

In diesem Beispiel liest OPENROWSET du eine CSV-Datei auf einem Fabric Lakehouse. Die Datei ist benannt customer.csv und wird im Ordner Files/Contoso/ gespeichert. Da du keine Datenquelle oder datenbankbezogene Zugangsdaten angibst, verwendet die Fabric-SQL-Datenbank deinen Entra ID-Kontext zur Authentifizierung.

SELECT * FROM OPENROWSET 
( BULK ' abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/customer.csv' 
, FORMAT = 'CSV' 
, FIRST_ROW = 2 
) WITH 
(  
    CustomerKey INT,  
    GeoAreaKey INT,  
    StartDT DATETIME2,  
    EndDT DATETIME2,  
    Continent NVARCHAR(50),  
    Gender NVARCHAR(10),  
    Title NVARCHAR(10),  
    GivenName NVARCHAR(100),  
    MiddleInitial VARCHAR(2),  
    Surname NVARCHAR(100),  
    StreetAddress NVARCHAR(200),  
    City NVARCHAR(100),  
    State NVARCHAR(100),  
    StateFull NVARCHAR(100),  
    ZipCode NVARCHAR(20),  
    Country_Region NCHAR(2),  
    CountryFull NVARCHAR(100),  
    Birthday DATETIME2,  
    Age INT,  
    Occupation NVARCHAR(100),  
    Company NVARCHAR(100),  
    Vehicle NVARCHAR(100),  
    Latitude DECIMAL(10,6),  
    Longitude DECIMAL(10,6) ) AS DATA 

B. Verwenden Sie OPENROWSET, um eine Datei aus einem Fabric Lakehouse zu lesen und Daten in eine neue Tabelle einzufügen

In diesem Beispiel liest OPENROWSET du Daten aus einer Parquet-Datei namens store.parquet. Dann fügen INSERT Sie die Daten in eine neue Tabelle namens Storehinzu. Die Parquet-Akte befindet sich in einem Fabric Lakehouse. Da Sie keine Datenquelle oder datenbankbezogene Anmeldedaten bereitstellen, verwendet die SQL-Datenbank in Fabric Ihren Entra ID-Kontext zur Authentifizierung.

SELECT * 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' )
 AS dataset; 

-- insert into new table 
SELECT * 
INTO Store 
FROM OPENROWSET 
(BULK 'abfss://<workspace id>@<tenant>.dfs.fabric.microsoft.com/<lakehouseid>/Files/Contoso/store.parquet' 
, FORMAT = 'parquet' ) 
 AS STORE; 

Weitere Beispiele

Weitere Beispiele für die Verwendung OPENROWSET(BULK...)finden Sie in den folgenden Artikeln:

• Massenimport und -export von Daten (SQL Server)
• Beispiele für den Massenimport und -export von XML-Dokumenten (SQL Server)
• Beibehalten von Identitätswerten beim Massenimport von Daten (SQL Server)
• Beibehalten von NULL-Werten oder Standardwerten während des Massenimports (SQL Server)
• Massenimport von Daten mithilfe einer Formatdatei (SQL Server)
• Verwenden des Zeichenformats zum Importieren oder Exportieren von Daten (SQL Server)
• Überspringen einer Tabellenspalte mithilfe einer Formatdatei (SQL Server)
• Auslassen eines Datenfelds mithilfe einer Formatdatei (SQL Server)
• Verwenden einer Formatdatei zum Zuordnen von Tabellenspalten zu Datendateifeldern (SQL Server)
• Abfragen von Datenquellen mit OPENROWSET in Azure SQL verwaltete Instanz s
• Angeben von Feld- und Zeilenendzeichen (SQL Server)