OPENROWSET BULK (Transact-SQL)

Syntax

För SQL Server, Azure SQL Database, SQL database in Fabric och 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 att komma åt filer på en lokal disk
• \\<network-share\<file path> för att komma åt filer på nätverksresurser
• adls://<container>@<storage>.dfs.core.windows.net/<file path> för att få åtkomst till Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> för att få åtkomst till Azure Blob Storage
• s3://<ip-address>:<port>/<file path> för att få åtkomst till s3-kompatibel lagring

Från och med SQL Server 2017 (14.x) kan data_file finnas i Azure Blob Storage. Exempel finns i Exempel på massåtkomst till data i Azure Blob Storage.

• https://<storage>.blob.core.windows.net/<container>/<file path> för att få åtkomst till Azure Blob Storage eller Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> för att få åtkomst till Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> för att få åtkomst till Azure Data Lake Storage
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - för att komma åt OneLake i Microsoft Fabric

När du får åtkomst till data lagrad i Azure Data Lake Storage Gen2, använd URI-formatet abfss://<container>@<storage>.dfs.core.windows.net/<file path> istället https://<storage>.dfs.core.windows.net/<container>/<file path> för blob-endpointen. Båda ger fullt stöd för det hierarkiska namnrymden (HNS), vilket möjliggör katalogsemantik, optimerade filoperationer och POSIX-liknande åtkomstkontrolllistor (ACL).

I kontrast exponerar inte ändpunkten blob hierarkiska namnrymdsfunktioner och behandlar alla vägar som platta objektnycklar. Detta kan leda till minskad prestanda, begränsat katalogbeteende och inkompatibilitet med motorer som förväntar sig Azure Data Lake Storage Gen2-filsystemsemantik.

Du kan använda den OPENROWSET(BULK) för att läsa data direkt från filer som lagras i OneLake i Microsoft Fabric, specifikt från mappen Files i en Fabric Lakehouse. Denna funktion eliminerar behovet av externa staging-konton (såsom ADLS Gen2 eller Blob Storage) och möjliggör arbetsytstyrd, SaaS-native inmatning genom att använda Fabric-behörigheter. Den här funktionen stöder:

• Läsa från Files mappar i Lakehouses
• Arbetsyte-till-lager-inläsningar inom samma klientorganisation
• Intern identitetsframtvingande med Hjälp av Microsoft Entra-ID

Se begränsningarna som gäller för både COPY INTO och OPENROWSET(BULK).

Du behöver inte specificera FORMAT alternativet om filfiländelsen i sökvägen slutar på .csv, .tsv, .parquet, .parq, .jsonl, , .ldjson, eller .ndjson. Till exempel OPENROWSET(BULK) vet funktionen att formatet är parquet baserat på tillägget i följande exempel:

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

Om filsökvägen inte slutar med något av dessa tillägg måste du ange ett FORMAT, till exempel:

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

FORMATFILE

Anger den fullständiga sökvägen för en formatfil. SQL Server stöder två typer av formatfiler: XML och icke-XML.

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

Du behöver en formatfil för att definiera kolumntyper i resultatuppsättningen. Det enda undantaget är när du anger SINGLE_CLOB, SINGLE_BLOB, eller SINGLE_NCLOB; i detta fall behöver du ingen formatfil.

För mer information om formatfiler, se Använd en formatfil för att bulkimportera data (SQL Server).

Från och med SQL Server 2017 (14.x) kan de format_file_path vara i Azure Blob Storage. Exempel finns i Exempel på massåtkomst till data i Azure Blob Storage.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definierar rotplatsen för formatfilens sökväg. Genom att använda denna datakälla kan du använda relativa vägar i alternativet 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'
);

Skapa formatet fildatakälla med CREATE EXTERNAL DATA SOURCE. Utöver root-platsen kan den definiera en anpassad inloggningsbehörighet för att komma åt filerna på den platsen.

PARSER_VERSION

Gäller för: Endast Fabric Data Warehouse

Anger den parserversion som ska användas vid läsning av filer. Parserversioner som stöds CSV för närvarande är 1.0 och 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 är standardimplementeringen optimerad för prestanda, men den stöder inte alla äldre alternativ och kodningar som finns tillgängliga i version 1.0. När du använder OPENROWSET faller Fabric Data Warehouse automatiskt tillbaka till version 1.0 om du använder de alternativ som endast stöds i den versionen, även om versionen inte är uttryckligen specificerad. I vissa fall kan du behöva specificera version 1.0 uttryckligen för att åtgärda fel orsakade av icke-stödda funktioner rapporterade av parser version 2.0.

CSV-parser version 1.0- detaljer:

• Följande alternativ stöds inte: HEADER_ROW.
• Standardavgränsare är \r\n, \n och \r.
• Om du anger \n (ny rad) som radavgränsare prefixet automatiskt med ett \r (vagnretur) tecken, vilket resulterar i en radavslutare av \r\n.

CSV-parser version 2.0- detaljer:

• Alla datatyper stöds inte.
• Maximal kolumnlängd är 8 000.
• Maximal radstorleksgräns är 8 MB.
• Följande alternativ stöds inte: DATA_COMPRESSION.
• Den citerade tomma strängen ("") tolkas som en tom sträng.
• DATEFORMAT SET Alternativet respekteras inte.
• Format som stöds för datumdatatyp : YYYY-MM-DD
• Format som stöds för tidsdatatyp : HH:MM:SS[.fractional seconds]
• Format som stöds för datatypen datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• Standardavgränsare är \r\n och \n.

ESCAPE_CHAR

Anger tecknet i filen som används för att undkomma sig själv och alla avgränsarvärden i filen, till exempel:

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

Om escape-tecknet följs av ett annat värde än sig självt, eller något av avgränsarvärdena, tas escape-tecknet bort när värdet läss.

Parametern ESCAPECHAR tillämpas oavsett om FIELDQUOTE är eller inte är aktiverat. Den kommer inte att användas för att undkomma citattecknet. Citattecknet måste undanröjas med ett annat citattecken. Citattecknet kan endast visas inom kolumnvärdet om värdet kapslas in med citattecken.

I följande exempel är kommatecken (,) och omvänt snedstreck (\) undantagna och representeras som \, och \\:

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

HEADER_ROW

Anger om en CSV-fil innehåller rubrikrad som inte ska returneras med andra datarader. Ett exempel på CSV-fil med en rubrik visas i följande exempel:

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

Standard är FALSE. Stöds i PARSER_VERSION='2.0' Fabric Data Warehouse. Om TRUEkommer kolumnnamnen att läsas från den första raden enligt FIRSTROW argumentet. Om TRUE och schemat anges med hjälp WITHav utförs bindningen av kolumnnamn efter kolumnnamn, inte ordningstalspositioner.

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

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

Ett valfritt tips som anger hur data i datafilen sorteras. Som standard förutsätter massåtgärden att datafilen är osorterad. Prestanda kan förbättras om frågeoptimeraren kan utnyttja ordningen för att generera en effektivare frågeplan. Följande lista innehåller exempel på när du anger en sortering kan vara fördelaktigt:

• Infoga rader i en tabell som har ett grupperat index, där raduppsättningsdata sorteras på den klustrade indexnyckeln.
• Koppla raduppsättningen till en annan tabell, där sorterings- och kopplingskolumnerna matchar.
• Aggregera raduppsättningsdata efter sorteringskolumnerna.
• Använda raduppsättningen som en källtabell i FROM-satsen för en fråga, där sorterings- och kopplingskolumnerna matchar.

UNIQUE

Anger att datafilen inte har duplicerade poster.

Om de faktiska raderna i datafilen inte är sorterade enligt den ordning du anger, eller om du anger UNIQUE att hint- och dubblettnycklar finns, returneras ett felmeddelande.

Kolumnaliaser krävs när du använder ORDER. Kolumnaliaslistan måste referera till den härledda tabellen som klausulen BULK kommer åt. De kolumnnamn du anger i klausulen ORDER syftar på denna kolumnalias lista. Du kan inte specificera stora värdetyper (varchar(max),nvarchar(max),varbinary(max) och xml) och stora objekttyper (LOB) (text, ntext och bild).

Innehållsalternativ

SINGLE_BLOB

Returnerar innehållet i data_file som en radradsraduppsättning med en enda kolumn av typen varbinary(max).

SINGLE_CLOB

Läser data_file som ASCII och returnerar innehållet som en enkelrad, enkelkolumns radmängd av typen varchar(max), med hjälp av sorteringen av den aktuella databasen.

SINGLE_NCLOB

Läser data_file som Unicode och returnerar innehållet som en radmängd med en rad och en kolumn av typen nvarchar(max), med hjälp av sorteringen av den aktuella databasen.

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

WITH-schemat

Schemat WITH anger de kolumner som definierar resultatuppsättningen för funktionen OPENROWSET. Den innehåller kolumndefinitioner för varje kolumn som OPENROWSET returnerar och beskriver mappningsreglerna som binder de underliggande filkolumnerna till kolumnerna i resultatuppsättningen.

I följande exempel:

• Kolumnen country_region har varchar(50)- typen och refererar till den underliggande kolumnen med samma namn.
• Kolumnen date refererar till en CSV- eller Parquet-kolumn eller JSONL-egenskap med ett annat fysiskt namn.
• Kolumnen cases refererar till den tredje kolumnen i filen.
• Kolumnen fatal_cases refererar till en nästlad Parquet-egenskap eller JSONL-delobjekt.

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>

Namnet på kolumnen som OPENROWSET returneras i resultatraduppsättningen. OPENROWSET läser data för denna kolumn från den underliggande filkolumnen med samma namn, om du inte åsidosätter den genom att använda <column_path> eller <column_ordinal>. Kolumnnamnet måste följa reglerna för kolumnnamnsidentifierare.

<column_type>

T-SQL-typen för kolumnen i resultatuppsättningen. OPENROWSET konverterar värden från den underliggande filen till denna typ när resultaten returneras. Mer information finns i Datatyper i Infrastrukturlager.

<column_path>

En punktavgränsad sökväg (till exempel $.description.location.lat) som används för att referera till kapslade fält i komplexa typer som Parquet.

<column_ordinal>

Ett tal som representerar det fysiska indexet för kolumnen som avbildas till kolumnen i satsen WITH .

• Genom att använda OPENROWSET(BULK...) som källtabell i en INSERT eller sats MERGE importerar du data från en datafil i bulk till en tabell. Mer information finns i Använd BULK INSERT eller OPENROWSET(BULK...) för att importera data till SQL Server.
• När du använder OPENROWSET BULK alternativet med en INSERT sats BULK stöder klausulen tabellledtrådar. Förutom vanliga tabelltips, till exempel TABLOCK, kan BULK-satsen acceptera följande specialiserade tabelltips: IGNORE_CONSTRAINTS (ignorerar endast begränsningarna CHECK och FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTSoch KEEPIDENTITY. Mer information finns i Tabelltips (Transact-SQL).
• Information om hur du använder INSERT...SELECT * FROM OPENROWSET(BULK...)-instruktioner finns i massimport och export av data (SQL Server). För information om när radinsättningsoperationer som bulkimport utför loggas i transaktionsloggen, se Förutsättningar för minimal loggning vid bulkimport.
• När du importerar OPENROWSET (BULK ...) data med hela återställningsmodellen optimeras inte loggningen.

I Microsoft Fabric Data Warehouse sammanfattar följande tabell stödda funktioner:

Massimport av SQLCHAR-, SQLNCHAR- eller SQLBINARY-data

OPENROWSET(BULK...) antar att, om du inte specificerar något annat, den maximala längden på SQLCHAR, SQLNCHAR, eller SQLBINARY data inte överstiger 8 000 byte. Om du importerar data i ett LOB-datafält som innehåller varchar(max), nvarchar(max) eller varbinär(max) objekt som överstiger 8 000 byte, måste du använda en XML-fil som definierar maximal längd för datafältet. För att ange maximal längd, redigera formatfilen och deklarera attributet MAX_LENGTH .

Massexportera eller importera SQLXML-dokument

Om du vill massexportera eller importera SQLXML-data använder du någon av följande datatyper i formatfilen.

A. Använd OPENROWSET för att BULK INSERT arkivera data i en varbinär(max)-kolumn

Gäller för: ENDAST SQL Server.

I följande exempel skapas en liten tabell i demonstrationssyfte och fildata infogas från en fil med namnet Text1.txt i rotkatalogen C: i en kolumn med 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. Använd OPENROWSET BULK-providern med en formatfil för att hämta rader från en textfil

Gäller för: ENDAST SQL Server.

I följande exempel används en formatfil för att hämta rader från en flikavgränsad textfil, values.txt som innehåller följande data:

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

Formatfilen, values.fmt, beskriver kolumnerna i 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

Den här frågan hämtar dessa data:

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

C. Ange en formatfil och kodsida

Gäller för: ENDAST SQL Server.

I följande exempel visas hur du använder alternativ för både formatfilen och kodsidan samtidigt.

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

D. Få åtkomst till data från en CSV-fil med en formatfil

Gäller för: ENDAST SQL Server 2017 (14.x) och senare versioner.

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

E. Komma åt data från en CSV-fil utan en formatfil

Gäller för: ENDAST 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. Komma åt data från en fil som lagras i Azure Blob Storage

Gäller för: ENDAST SQL Server 2017 (14.x) och senare versioner.

I SQL Server 2017 (14.x) och senare versioner använder följande exempel en extern datakälla som pekar på en container i ett Azure-lagringskonto och en databasomfattande autentiseringsuppgift som skapats för en signatur för delad åtkomst.

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

Fullständiga OPENROWSET exempel, inklusive konfiguration av autentiseringsuppgifter och extern datakälla, finns i Exempel på massåtkomst till data i Azure Blob Storage.

G. Importera till en tabell från en fil som lagras i Azure Blob Storage

Följande exempel visar hur man använder kommandot OPENROWSET för att ladda data från en CSV-fil i en Azure Blob-lagringsplats där du skapade SAS-nyckeln. Du konfigurerar Azure Blob-lagringsplatsen som en extern datakälla. Denna process kräver en databasbegränsad legitimation som använder en shared access-signatur (SAS) krypterad med en huvudnyckel i användardatabasen.

-- 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. Använda en hanterad identitet för en extern källa

Gäller för: Azure SQL Managed Instance och Azure SQL Database

Följande exempel skapar en legitimation genom att använda en hanterad identitet, skapar en extern källa och laddar sedan data från en CSV som är värd på den externa källan.

Skapa först autentiseringsuppgifterna och ange bloblagring som extern källa:

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

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

Läs sedan in data från CSV-filen som finns på Blob Storage:

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

I. Använda OPENROWSET för att komma åt flera Parquet-filer med S3-kompatibel objektlagring

gäller för: SQL Server 2022 (16.x) och senare versioner.

Följande exempel åtkomst till flera Parquet-filer från olika platser, alla lagrade på S3-kompatibel objektlagring:

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. Använd OPENROWSET för att få åtkomst till flera Delta-tabeller från Azure Data Lake Gen2

gäller för: SQL Server 2022 (16.x) och senare versioner.

I det här exemplet heter datatabellcontainern Contosooch finns på ett Azure Data Lake Gen2-lagringskonto.

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. Använda OPENROWSET för att fråga offentlig-anonym datamängd

I följande exempel används den offentligt tillgängliga nyc yellow taxi trip-posterna open data set.

Skapa datakällan först:

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

Fråga alla filer med .parquet tillägg i mappmatchningsnamnsmönster:

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

A. Läsa en parquet-fil från Azure Blob Storage

I följande exempel kan du se hur du läser 100 rader från en Parquet-fil:

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. Läsa en anpassad CSV-fil

I följande exempel kan du se hur man läser rader från en CSV-fil med en header-rad och uttryckligen specificerade terminatortecken som separerar rader och fält:

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. Ange filkolumnschemat när du läser en fil

I följande exempel specificerar du schemat för raden som OPENROWSET funktionen returnerar:

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. Läsa partitionerade datauppsättningar

I följande exempel använder filepath() du funktionen för att läsa delarna av URI:n från den matchade filvägen:

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. Ange filkolumnschemat när du läser en JSONL-fil

I följande exempel kan du se hur du uttryckligen anger schemat för raden som ska returneras som ett resultat av OPENROWSET funktionen:

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

Om ett kolumnnamn inte matchar det fysiska namnet på en kolumn i egenskaperna om JSONL-filen, kan du ange det fysiska namnet i JSON-sökvägen efter typdefinitionen. Du kan använda flera egenskaper. Om du till exempel $.location.latitude vill referera till kapslade egenskaper i parquet-komplexa typer eller JSON-underobjekt.

Fler exempel

• Bläddra bland filinnehåll med funktionen OPENROWSET
• Prestandariktlinjer för Infrastrukturdatalager
• Mata in data i ditt lager med Hjälp av Transact-SQL

A. Använd OPENROWSET för att läsa en CSV-fil från en Fabric Lakehouse

I det här exemplet använder OPENROWSET du det för att läsa en CSV-fil på en Fabric Lakehouse. Filen är namngiven customer.csv och lagras i mappen Files/Contoso/ . Eftersom du inte tillhandahåller någon datakälla eller databasbegränsade inloggningsuppgifter, använder Fabric SQL-databasen din Entra ID-kontext för autentisering.

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. Använd OPENROWSET för att läsa en fil från en Fabric Lakehouse och infoga data i en ny tabell

I det här exemplet använder OPENROWSET du det för att läsa data från en Parquet-fil som heter store.parquet. Sedan använder INSERT du det för att lägga till data i en ny tabell som heter Store. Parquet-filen finns i ett Fabric Lakehouse. Eftersom du inte tillhandahåller någon datakälla eller databasbegränsade inloggningsuppgifter, använder SQL-databasen i Fabric din Entra ID-kontext för autentisering.

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; 

Fler exempel

Fler exempel som visar hur du använder OPENROWSET(BULK...)finns i följande artiklar:

• massimport och export av data (SQL Server)
• Exempel på massimport och export av XML-dokument (SQL Server)
• Behåll identitetsvärden vid massimport av data (SQL Server)
• Behåll null-värden eller standardvärden under massimport (SQL Server)
• Använd en formatfil för att massimportera data (SQL Server)
• Använd teckenformat för att importera eller exportera data (SQL Server)
• Använd en formatfil för att hoppa över en tabellkolumn (SQL Server)
• Använd en formatfil för att hoppa över ett datafält (SQL Server)
• Använd en formatfil för att mappa tabellkolumner till datafilfält (SQL Server)
• Fråga datakällor med OPENROWSET i Azure SQL Managed Instances
• Ange fält- och radavgränsare (SQL Server)