OPENROWSET BULK (Transact-SQL)

Sintassi

Per SQL Server, database SQL di Azure, database SQL in Fabric e Istanza gestita di SQL di Azure:

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

Quando accedi ai dati memorizzati in Azure Data Lake Storage Gen2, usa i abfss://<container>@<storage>.dfs.core.windows.net/<file path> formati URI di or https://<storage>.dfs.core.windows.net/<container>/<file path> invece dell'endpoint blob. Entrambi forniscono pieno supporto per il namespace gerarchico (HNS), che consente la semantica delle directory, operazioni ottimizzate nei file e liste di controllo accessi (ACL) in stile POSIX.

Al contrario, l'endpoint blob non espone le caratteristiche gerarchiche dello spazio dei nomi e tratta tutti i percorsi come chiavi oggetto piatte. Ciò può portare a prestazioni ridotte, comportamento limitato delle directory e incompatibilità con motori che prevedono la semantica del file system Azure Data Lake Storage Gen2.

Puoi usare OPENROWSET(BULK) la lettura dei dati direttamente dai file memorizzati nel OneLake in Microsoft Fabric, in particolare dalla cartella Files di un Fabric Lakehouse. Questa capacità elimina la necessità di account staging esterni (come ADLS Gen2 o gestione rete virtuale di Azure) e consente l'ingestione SaaS-native governata dall'area di lavoro utilizzando i permessi Fabric. 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 i limiti che si applicano sia COPY INTO a che OPENROWSET(BULK)a .

Non è necessario specificare l'opzione FORMAT 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 nel seguente esempio:

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

Serve un file di formato per definire i tipi di colonne nel set di risultati. L'unica eccezione è quando specifichi SINGLE_CLOB, SINGLE_BLOB, o SINGLE_NCLOB; in questo caso, non hai bisogno di un file format.

Per maggiori informazioni sui file di format, vedi Usa un file di formattazione per importare dati in massa (SQL Server).

A partire da SQL Server 2017 (14.x), possono format_file_path essere 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. Utilizzando questa fonte di dati, puoi utilizzare i 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'
);

Crea la fonte dati del file di formattazione con CREATE EXTERNAL DATA SOURCE. Oltre alla posizione radice, può definire una credenziale personalizzata per accedere ai file in quella posizione.

VERSIONE_PARSER

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 2.0 del parser CSV è l'implementazione predefinita ottimizzata per le prestazioni, ma non supporta tutte le opzioni e codifiche legacy disponibili nella versione 1.0. Quando si utilizza OPENROWSET, Fabric Data Warehouse torna automaticamente alla versione 1.0 se si utilizzano le opzioni supportate solo in quella versione, anche quando la versione non è esplicitamente specificata. In alcuni casi, potrebbe essere necessario specificare esplicitamente la versione 1.0 per risolvere errori causati da funzionalità non supportate segnalate dalla versione 2.0 del parser.

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.
• DATEFORMAT SET L'opzione 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

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

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 dati non sono ordinate secondo l'ordine specificato, o se specifichi l'hint UNIQUE e sono presenti chiavi duplicate, viene restituito un errore.

Gli alias a colonna sono richiesti quando si usa ORDER. La lista degli alias della colonna deve fare riferimento alla tabella derivata a cui la BULK clausola accede. I nomi delle colonne che specifichi nella ORDER clausola si riferiscono a questa lista di alias. Non puoi specificare colonne di tipo di grande valore (varchar(max),nvarchar(max), varbinary(max) e xml) e di tipo di oggetto grande (LOB) (testo, ntext e immagine).

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

Si legge data_file come ASCII e restituisce il contenuto come un set di righe singole e colonna singola di tipo varchar(max), utilizzando la collazione del database corrente.

SINGLE_NCLOB

Legge data_file come Unicode e restituisce il contenuto come un set di righe singole e colonna singola di tipo nvarchar(max), utilizzando la collation 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 colonne per ogni colonna che OPENROWSET restituisce e delinea le regole di mappatura che legano le colonne del file sottostante 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 o Parquet o a una proprietà JSONL con un nome fisico diverso.
• La cases colonna fa riferimento alla terza colonna del file.
• La fatal_cases colonna fa riferimento a una proprietà Parquet annidata o a un sotto-oggetto 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>

Il nome della colonna che OPENROWSET restituisce nel set di righe del risultato. OPENROWSET legge i dati di questa colonna dalla colonna del file sottostante con lo stesso nome, a meno che non si sovrascriva usando <column_path> o <column_ordinal>. Il nome della colonna deve seguire le regole per gli identificatori del nome delle colonne.

<column_type>

Tipo T-SQL della colonna nel set di risultati. OPENROWSET converte i valori dal file sottostante a questo tipo quando 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>

Un numero che rappresenta l'indice fisico della colonna che si mappa alla colonna della WITH clausola.

• Usando OPENROWSET(BULK...) come tabella sorgente in un'istruzione INSERT o MERGE , importi in massa dati da un file dati in una tabella. Per altre informazioni, vedere Usare BULK INSERT o OPENROWSET(BULK...) per importare dati in SQL Server.
• Quando usi l'opzione OPENROWSET BULK con un'istruzione INSERT , la BULK clausola supporta suggerimenti per la 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 su quando le operazioni di inserimento righe effettuate da un'importazione in blocco vengono registrate nel registro delle transazioni, vedi Prerequisiti per un logging minimo nell'importazione in massa.
• Quando si usano OPENROWSET (BULK ...) per importare dati con il modello di recupero completo, non si ottimizza la registrazione.

In Microsoft Fabric Data Warehouse, la seguente tabella riassume le funzionalità supportate:

Importazione bulk di dati SQLCHAR, SQLNCHAR o SQLBINARY

OPENROWSET(BULK...) presuppone che, se non specifichi altrimenti, la lunghezza massima di SQLCHAR, SQLNCHAR, o SQLBINARY i dati non superi gli 8.000 byte. Se importi dati in un campo dati LOB che contiene oggetti varchar(max), nvarchar(max) o varbinary(max) che superano gli 8.000 byte, devi usare un file in formato XML che definisce la lunghezza massima del campo dati. Per specificare la lunghezza massima, modifica il file di formato e dichiara 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. Usa OPENROWSET per BULK INSERT archiviare i dati 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 mostra come usare il OPENROWSET comando per caricare dati da un file CSV in una posizione di archiviazione Azure Blob dove hai creato la chiave SAS. Configuri la posizione di archiviazione Azure Blob come sorgente dati esterna. Questo processo richiede una credenziale con ambito di database che utilizza una firma di accesso condiviso (SAS) criptata tramite una chiave principale 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

Il seguente esempio crea una credenziale utilizzando un'identità gestita, crea una sorgente esterna e poi carica i dati da un CSV ospitato sulla sorgente 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.

Il seguente esempio accede a diversi file Parquet da diverse località, tutti memorizzati su oggetti compatibili 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

Nel seguente esempio puoi vedere come leggere righe da un file CSV con una riga di header e caratteri terminator esplicitamente specificati 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

Nel seguente esempio, specifichi lo schema della riga che la OPENROWSET funzione restituisce:

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

Nel seguente esempio, usi la filepath() funzione per leggere le parti dell'URI dal percorso 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, si legge OPENROWSET un file CSV su una Fabric Lakehouse. Il file è nominato customer.csv e memorizzato nella Files/Contoso/ cartella. Poiché non fornisci una fonte di dati o credenziali con scopo di database, il database Fabric SQL utilizza il contesto Entra ID per autenticarsi.

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 un file da un Fabric Lakehouse e inserire dati in una nuova tabella

In questo esempio, si leggono OPENROWSET dati da un file Parquet chiamato store.parquet. Poi, si aggiungono INSERT i dati in una nuova tabella chiamata Store. Il file Parquet si trova in una casa Fabric Lakehouse. Poiché non fornisci una fonte di dati o credenziali con scopo di database, il database SQL in Fabric utilizza il contesto Entra ID per autenticarsi.

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)