OPENROWSET BULK (Transact-SQL)

Sintassi

Per SQL Server, Azure SQL Database, database SQL in Fabric e 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

Sintassi per 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> per accedere ai file sul disco locale
• \\<network-share\<file path> per accedere ai file nelle condivisioni di rete
• adls://<container>@<storage>.dfs.core.windows.net/<file path> per accedere ad Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> per accedere ad Archiviazione BLOB di Azure
• s3://<ip-address>:<port>/<file path> per accedere all'archiviazione compatibile con s3

A partire da SQL Server 2017 (14.x), il data_file può trovarsi in Archiviazione BLOB di Azure. Per esempi, vedere Esempi di accesso in blocco ai dati in Archiviazione BLOB di Azure.

• https://<storage>.blob.core.windows.net/<container>/<file path> per accedere ad Archiviazione BLOB di Azure o Azure Data Lake Storage
• https://<storage>.dfs.core.windows.net/<container>/<file path> per accedere ad Azure Data Lake Storage
• abfss://<container>@<storage>.dfs.core.windows.net/<file path> per accedere ad Azure Data Lake Storage
• https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/<file path> - per accedere a OneLake in Microsoft Fabric

Puoi usarli OPENROWSET(BULK) per leggere i dati direttamente dai file memorizzati nel OneLake in Microsoft Fabric, in particolare dalla cartella Files di una casa Fabric Lakehouse. In questo modo si elimina la necessità di account di staging esterni, ad esempio ADLS Gen2 o Archiviazione BLOB, e consente l'inserimento nativo dell'area di lavoro gestito dall'area di lavoro usando le autorizzazioni di Infrastruttura. Questa funzionalità supporta:

• Lettura da Files cartelle in Lakehouses
• Carichi da area di lavoro a magazzino all'interno dello stesso tenant
• Imposizione dell'identità nativa con Microsoft Entra ID

Vedere le limitazioni applicabili sia a che COPY INTOa OPENROWSET(BULK) .

Non FORMAT è necessario specificare l'opzione se l'estensione del file nel percorso termina con .csv, .tsv.parquet.parq.jsonl, .ldjson, o ..ndjson Ad esempio, la OPENROWSET(BULK) funzione sa che il formato è parquet basato sull'estensione nell'esempio seguente:

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

Se il percorso del file non termina con una di queste estensioni, è necessario specificare , FORMATad esempio:

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

FORMATFILE

Specifica il percorso completo di un file di formato. SQL Server supporta due tipi di file di formato, ovvero XML e non XML.

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

Un file di formato è necessario per definire i tipi di colonna nel set di risultati. L'unica eccezione è quando SINGLE_CLOB, SINGLE_BLOBo SINGLE_NCLOB è specificato. In tal caso, il file di formato non è obbligatorio.

Per informazioni sui file di formato, vedere Usare un file di formato per l'importazione bulk di dati (SQL Server).For information about format files, see Use a format file to bulk import data (SQL Server).

A partire da SQL Server 2017 (14.x), il format_file_path può trovarsi in Archiviazione BLOB di Azure. Per esempi, vedere Esempi di accesso in blocco ai dati in Archiviazione BLOB di Azure.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE definisce il percorso radice del percorso del file di formato. Consente di usare percorsi relativi nell'opzione 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'
);

L'origine dati del file di formato viene creata con CREATE EXTERNAL DATA SOURCE. Oltre al percorso radice, può definire credenziali personalizzate che possono essere usate per accedere ai file in tale percorso.

PARSER_VERSION = 'parser_version'

Si applica a: Solo Fabric Data Warehouse

Specifica la versione del parser da usare per la lettura dei file. Le versioni del parser attualmente supportate CSV sono 1.0 e 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 versione 1.0 del parser CSV è l'impostazione predefinita ed è completa di tutte le funzionalità, La versione 2.0 è progettata per le prestazioni e non supporta tutte le opzioni e le codifiche.

Specifiche del parser CSV versione 1.0:

• Le opzioni seguenti non sono supportate: HEADER_ROW.
• I caratteri di terminazione predefiniti sono \r\ne \n\r.
• Se si specifica \n (nuova riga) come carattere di terminazione della riga, viene automaticamente preceduto da un \r carattere (ritorno a capo), che genera un terminatore di riga di \r\n.

Specifiche del parser CSV versione 2.0:

• Non sono supportati tutti i tipi di dati.
• La lunghezza massima della colonna di caratteri è 8000.
• Il limite massimo delle dimensioni di riga è 8 MB.
• Le opzioni seguenti non sono supportate: DATA_COMPRESSION.
• La stringa vuota racchiusa tra virgolette ("") viene interpretata come stringa vuota.
• L'opzione DATEFORMAT SET non viene rispettata.
• Formato supportato per il tipo di dati date : YYYY-MM-DD
• Formato supportato per il tipo di dati time : HH:MM:SS[.fractional seconds]
• Formato supportato per il tipo di dati datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
• I caratteri di terminazione predefiniti sono \r\n e \n.

ESCAPE_CHAR = 'char'

Specifica il carattere nel file utilizzato per eseguire l'escape e tutti i valori delimitatori nel file, ad esempio:

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

Se seguito da un valore diverso da se stesso o da uno qualsiasi dei valori dei delimitatori, il carattere di escape viene eliminato durante la lettura del valore.

Il ESCAPECHAR parametro viene applicato indipendentemente dal fatto che FIELDQUOTE sia o non sia abilitato. Non verrà usato per l'escape del carattere virgolette singole. Il carattere di virgolette deve essere impostato come escape con un altro carattere di virgolette. Il carattere di virgolette può essere visualizzato all'interno del valore della colonna solo se il valore è incapsulato con caratteri tra virgolette.

Nell'esempio seguente, la virgola (,) e la barra rovesciata (\) vengono precedute da un carattere di escape e rappresentate come \, e \\:

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

HEADER_ROW = { TRUE | FALSO }

Specifica se un file CSV contiene una riga di intestazione che non deve essere restituita con altre righe di dati. Un esempio di file CSV con un'intestazione è illustrato nell'esempio seguente:

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

Il valore predefinito è FALSE. Supportato in PARSER_VERSION='2.0' Fabric Data Warehouse. Se TRUE, i nomi delle colonne verranno letti dalla prima riga in base all'argomento FIRSTROW . Se TRUE e lo schema viene specificato usando WITH, l'associazione dei nomi di colonna verrà eseguita in base al nome della colonna, non alle posizioni ordinali.

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

ORDER ( { colonna [ ASC | DESC ] } [ ,... n ] [ UNICO ] )

Hint facoltativo che specifica il modo in cui vengono ordinati i dati nel file di dati. Per impostazione predefinita, per l'operazione bulk si presume che il file di dati non sia ordinato. Le prestazioni possono migliorare se Query Optimizer può sfruttare l'ordine per generare un piano di query più efficiente. L'elenco seguente fornisce esempi per quando si specifica un ordinamento può essere utile:

• Inserimento di righe in una tabella con un indice cluster, in cui i dati del set di righe sono ordinati in base alla chiave dell'indice cluster.
• Unione del set di righe con un'altra tabella, in cui le colonne di ordinamento e di join corrispondono.
• Aggregazione dei dati del set di righe tramite le colonne dell'ordinamento.
• Utilizzo del set di righe come tabella di origine nella FROM clausola di una query, in cui le colonne di ordinamento e join corrispondono.

UNIQUE

Specifica che il file di dati non contiene voci duplicate.

Se le righe effettive nel file di dati non vengono ordinate in base all'ordine specificato o se l'hint UNIQUE è specificato e le chiavi duplicate sono presenti, viene restituito un errore.

Quando si utilizzano gli alias di colonna, sono necessari ORDER alias di colonna. L'elenco di alias di colonna deve fare riferimento alla tabella derivata a cui si accede dalla BULK clausola . I nomi di colonna specificati nella ORDER clausola fanno riferimento a questo elenco di alias di colonna. Non è possibile specificare tipi valore di grandi dimensioni (varchar(max), nvarchar(max), varbinary(max)e xml) e tipi loB (large object) (text, ntext e image).

Opzioni contenuto

SINGLE_BLOB

Restituisce il contenuto di data_file come set di righe a riga singola a colonna singola di tipo varbinary(max).

SINGLE_CLOB

Leggendo data_file come ASCII, restituisce il contenuto come set di righe a riga singola e a colonna singola di tipo varchar(max), usando le regole di confronto del database corrente.

SINGLE_NCLOB

Leggendo data_file come Unicode, restituisce il contenuto come set di righe a riga singola a colonna singola di tipo nvarchar(max), usando le regole di confronto del database corrente.

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

CON Schema

Lo schema WITH specifica le colonne che definiscono il set di risultati della funzione OPENROWSET. Include definizioni di colonna per ogni colonna che verrà restituita di conseguenza e delinea le regole di mapping che associano le colonne di file sottostanti alle colonne del set di risultati.

Nell'esempio seguente:

• La country_region colonna ha il tipo varchar(50) e fa riferimento alla colonna sottostante con lo stesso nome
• La date colonna fa riferimento a una colonna CSV/Parquet o a una proprietà JSONL con un nome fisico diverso
• La cases colonna fa riferimento alla terza colonna nel file
• La fatal_cases colonna fa riferimento a una proprietà Parquet annidata o a un oggetto secondario 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>

Nome della colonna che verrà restituita nel set di righe dei risultati. I dati per questa colonna vengono letti dalla colonna di file sottostante con lo stesso nome, a meno che non venga sottoposto a override da <column_path> o <column_ordinal>. Il nome della colonna deve seguire le regole per gli identificatori dei nomi di colonna.

<column_type>

Tipo T-SQL della colonna nel set di risultati. I valori del file sottostante vengono convertiti in questo tipo quando OPENROWSET restituisce i risultati. Per altre informazioni, vedere Tipi di dati in Fabric Warehouse.

<column_path>

Percorso delimitato da punti (ad esempio $.description.___location.lat) usato per fare riferimento a campi annidati in tipi complessi come Parquet.

<column_ordinal>

Numero che rappresenta l'indice fisico della colonna di cui verrà eseguito il mapping alla colonna nella clausola WITH.

• L'uso OPENROWSET(BULK...) di come tabella di origine in un'istruzione INSERT o MERGE importa i dati da un file di dati in una tabella. Per altre informazioni, vedere Usare BULK INSERT o OPENROWSET(BULK...) per importare dati in SQL Server.
• Quando l'opzione OPENROWSET BULK viene usata con un'istruzione INSERT , la BULK clausola supporta hint di tabella. Oltre agli hint di tabella normali, ad esempio TABLOCK, la clausola BULK può accettare gli hint di tabella specializzati seguenti: IGNORE_CONSTRAINTS (ignora solo i vincoli CHECK e FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS e KEEPIDENTITY. Per ulteriori informazioni, vedere Hint di tabella (Transact-SQL).
• Per informazioni su come usare le istruzioni INSERT...SELECT * FROM OPENROWSET(BULK...), vedere Importazione ed esportazione bulk di dati (SQL Server). Per informazioni sui casi in cui le operazioni di inserimento di righe eseguite durante l'importazione in blocco vengono registrate nel log delle transazioni, vedere Prerequisiti per la registrazione minima nell'importazione in blocco.
• Se usato per importare dati con il modello di recupero con registrazione completa, OPENROWSET (BULK ...) non ottimizza la registrazione.

In Microsoft Fabric Data Warehouse, le funzionalità supportate sono riassunte nella tabella:

Importazione bulk di dati SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presuppone che, se non specificato, la lunghezza massima di SQLCHAR, SQLNCHARo SQLBINARY i dati non superino 8.000 byte. Se i dati importati si trovano in un campo dati LOB contenente qualsiasi oggetto varchar(max), nvarchar(max)o varbinary(max) che supera 8.000 byte, è necessario utilizzare un file di formato XML che definisce la lunghezza massima per il campo dati. Per specificare la lunghezza massima, modificare il file di formato dichiarando l'attributo MAX_LENGTH.

Esportazione o importazione in massa di documenti SQLXML

Per l'esportazione o l'importazione bulk di dati SQLXML, utilizzare uno dei tipi di dati seguenti nel file di formato.

A. Usare OPENROWSET per ESEGUIRE BULK INSERT i dati dei file in una colonna varbinary(max)

Si applica a: Solo SQL Server.

Nell'esempio seguente viene creata una tabella di piccole dimensioni a scopo dimostrativo e vengono inseriti dati di file da un file denominato Text1.txt nella C: directory radice in una colonna 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. Usare il provider OPENROWSET BULK con un file di formato per recuperare righe da un file di testo

Si applica a: Solo SQL Server.

Nell'esempio seguente viene utilizzato un file di formato per recuperare le righe da un file di testo delimitato da tabulazioni, values.txt contenente i dati seguenti:

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

Il file di formato, values.fmt, descrive le colonne 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

Questa query recupera i dati:

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

C. Specificare un file di formato e una tabella codici

Si applica a: Solo SQL Server.

Nell'esempio seguente viene illustrato come usare contemporaneamente le opzioni del file di formato e della tabella codici.

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

D. Accedere ai dati da un file CSV con un file di formato

Si applica a: SOLO SQL Server 2017 (14.x) e versioni successive.

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

E. Accedere ai dati da un file CSV senza un file di formato

Si applica a: Solo 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. Accedere ai dati da un file archiviato in Archiviazione BLOB di Azure

Si applica a: SOLO SQL Server 2017 (14.x) e versioni successive.

In SQL Server 2017 (14.x) e versioni successive, nell'esempio seguente viene usata un'origine dati esterna che punta a un contenitore in un account di archiviazione di Azure e a credenziali con ambito database create per una firma di accesso condiviso.

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

Per esempi completiOPENROWSET, inclusa la configurazione delle credenziali e dell'origine dati esterna, vedere Esempi di accesso in blocco ai dati in Archiviazione BLOB di Azure.

G. Importare in una tabella da un file archiviato in Archiviazione BLOB di Azure

L'esempio seguente illustra come usare il OPENROWSET comando per caricare dati da un file CSV in un percorso di archiviazione BLOB di Azure in cui è stata creata la chiave di firma di accesso condiviso. La posizione di Archiviazione BLOB di Azure è configurata come origine dati esterna. Questa operazione richiede credenziali con ambito database che usano una firma di accesso condiviso crittografata con una chiave master nel database utente.

-- 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. Usare un'identità gestita per un'origine esterna

Si applica a: Istanza gestita di SQL di Azure e database SQL di Azure

L'esempio seguente crea una credenziale usando un'identità gestita, crea un'origine esterna e quindi carica i dati da un file CSV ospitato nell'origine esterna.

Creare prima di tutto le credenziali e specificare l'archiviazione BLOB come origine esterna:

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

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

Caricare quindi i dati dal file CSV ospitato nell'archivio BLOB:

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

I. Usare OPENROWSET per accedere a diversi file Parquet usando l'archiviazione oggetti compatibile con S3

Si applica a: SQL Server 2022 (16.x) e versioni successive.

L'esempio seguente usa l'accesso a diversi file Parquet da un percorso diverso, tutti archiviati nell'archivio oggetti compatibile con 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. Usare OPENROWSET per accedere a diverse tabelle Delta da Azure Data Lake Gen2

Si applica a: SQL Server 2022 (16.x) e versioni successive.

In questo esempio il contenitore della tabella dati è denominato Contosoe si trova in un account di archiviazione di 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. Usare OPENROWSET per eseguire query su un set di dati pubblico-anonimo

L'esempio seguente usa il set di dati aperto disponibile pubblicamente per le corse dei taxi gialli di New York.

Creare prima l'origine dati:

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

Eseguire una query su tutti i file con .parquet estensione nelle cartelle che corrispondono al modello di nome:

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

A. Leggere un file parquet da Archiviazione BLOB di Azure

Nell'esempio seguente è possibile vedere come leggere 100 righe da un file Parquet:

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. Leggere un file CSV personalizzato

Nell'esempio seguente è possibile vedere come leggere le righe da un file CSV con una riga di intestazione e i caratteri di terminazione specificati in modo esplicito che separano righe e campi:

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. Specificare lo schema della colonna di file durante la lettura di un file

Nell'esempio seguente è possibile vedere come specificare in modo esplicito lo schema di riga che verrà restituito come risultato della OPENROWSET funzione:

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. Leggere set di dati partizionati

Nell'esempio seguente è possibile vedere come usare la funzione filepath() per leggere le parti dell'URI dal percorso del file corrispondente:

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. Specificare lo schema della colonna di file durante la lettura di un file JSONL

Nell'esempio seguente è possibile vedere come specificare in modo esplicito lo schema di riga che verrà restituito come risultato della OPENROWSET funzione:

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

Se un nome di colonna non corrisponde al nome fisico di una colonna nelle proprietà se il file JSONL, è possibile specificare il nome fisico nel percorso JSON dopo la definizione del tipo. È possibile usare più proprietà. Ad esempio, $.___location.latitude per fare riferimento alle proprietà annidate in tipi complessi parquet o oggetti secondari JSON.

Altri esempi

• Esplorare il contenuto del file usando la funzione OPENROWSET
• Linee guida sulle prestazioni per Il data warehouse di Fabric
• Inserire dati nel warehouse usando Transact-SQL

A. Usa OPENROWSET per leggere un file CSV da un Fabric Lakehouse

In questo esempio, OPENROWSET verrà utilizzato per leggere un file CSV disponibile su Fabric Lakehouse, chiamato customer.csv, memorizzato sotto la Files/Contoso/ cartella. Poiché non vengono fornite credenziali Data Source e Database Scoped, il database SQL Fabric si autentica con il contesto Entra ID dell'utente.

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. Usa OPENROWSET per leggere il file da Fabric Lakehouse e inserirlo in una nuova tabella

In questo esempio, OPENROWSET verrà prima usato per leggere dati da un file parquetstore.parquet denominato. Successivamente, INSERT i dati vengono trasformati in una nuova tabella chiamata Store. Il file parquet si trova in Fabric Lakehouse, poiché non sono forniti DATA_SOURCE e credenziali con ambito di database, il database SQL in Fabric si autentica con il contesto Entra ID dell'utente.

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; 

Altri esempi

Per altri esempi che illustrano l'uso OPENROWSET(BULK...)di , vedere gli articoli seguenti:

• Importazione ed Esportazione Massiva di Dati (SQL Server)
• Esempi di importazione ed esportazione in blocco di documenti XML (SQL Server)
• Mantenere i valori Identity durante l'importazione in blocco dei dati (SQL Server)
• Mantenere i valori Null o i valori predefiniti durante un'importazione in blocco (SQL Server)
• Usare un file di formato per l'importazione in blocco dei dati (SQL Server)
• Usare il formato carattere per importare o esportare dati (SQL Server)
• Usare un file di formato per ignorare una colonna di una tabella (SQL Server)
• Usare un file di formato per escludere un campo dati (SQL Server)
• Usare un file di formato per eseguire il mapping tra le colonne di tabella e i campi del file di dati (SQL Server)
• Eseguire query sulle origini dati usando OPENROWSET in Istanza gestita di SQL di Azure
• Specificare i caratteri di terminazione dei campi e delle righe (SQL Server)