Eseguire query sui file usando un pool SQL serverless

10 minuti

È possibile usare un pool SQL serverless per eseguire query sui file di dati in diversi formati di file comuni, tra cui:

Testo delimitato, ad esempio file con valori delimitati da virgole (CSV).
File JSON (JavaScript Object Notation).
File Parquet.

La sintassi di base per l'esecuzione di query è la stessa per tutti questi tipi di file ed è basata sulla funzione SQL OPENROWSET; che genera un set di righe tabulare dai dati in uno o più file. Ad esempio, la query seguente può essere usata per estrarre dati da file CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

La funzione OPENROWSET include più parametri che determinano fattori come:

Schema del set di righe risultante
Opzioni di formattazione aggiuntive per i file di testo delimitati.

Suggerimento

La sintassi completa per la funzione OPENROWSET è disponibile nella documentazione di Azure Synapse Analytics.

L'output di OPENROWSET è un set di righe a cui deve essere assegnato un alias. Nell'esempio precedente l'alias righe viene usato per denominare il set di righe risultante.

Il parametro BULK include l'URL completo del percorso nel data lake contenente i file di dati. Può trattarsi di un singolo file o di una cartella con un'espressione con caratteri jolly per filtrare i tipi di file che devono essere inclusi. Il parametro FORMAT consente di specificare il tipo di dati su cui viene eseguito l'esecuzione di query. Nell'esempio precedente viene letto testo delimitato da tutti i file di .csv nella file cartella.

Nota

In questo esempio si presuppone che l'utente abbia accesso ai file nell'archivio sottostante. Se i file sono protetti con una chiave di firma di accesso condiviso o un'identità personalizzata, è necessario creare credenziali con ambito server.

Come illustrato nell'esempio precedente, è possibile usare caratteri jolly nel parametro BULK per includere o escludere file nella query. L'elenco seguente mostra alcuni esempi di come può essere usato:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: includere solo file1.csv nella cartella dei file di.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: tutti i file .csv nei file cartella con nomi che iniziano con "file".
https://mydatalake.blob.core.windows.net/data/files/*: tutti i file nella cartella file.
https://mydatalake.blob.core.windows.net/data/files/**: tutti i file nella cartella e in modo ricorsivo le relative sottocartelle.

È anche possibile specificare più percorsi di file nel parametro BULK, separando ogni percorso con una virgola.

Esecuzione di query su file di testo delimitati

I file di testo delimitati sono un formato di file comune all'interno di molte aziende. La formattazione specifica usata nei file delimitati può variare, ad esempio:

Con e senza una riga di intestazione.
Valori delimitati da virgole e tabulazioni.
Terminazioni di linea di stile Windows e Unix.
Valori non racchiusi tra virgolette e virgolette e caratteri di escape.

Indipendentemente dal tipo di file delimitato in uso, è possibile leggere i dati da essi usando la funzione OPENROWSET con il parametro csv csv FORMAT e altri parametri necessari per gestire i dettagli di formattazione specifici per i dati. Per esempio:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

Il PARSER_VERSION viene usato per determinare come la query interpreta la codifica del testo usata nei file. La versione 1.0 è l'impostazione predefinita e supporta un'ampia gamma di codifiche di file, mentre la versione 2.0 supporta meno codifiche, ma offre prestazioni migliori. Il parametro FIRSTROW viene utilizzato per ignorare le righe nel file di testo, per eliminare qualsiasi testo preambolo non strutturato o ignorare una riga contenente intestazioni di colonna.

I parametri aggiuntivi che potrebbero essere necessari quando si lavora con file di testo delimitati includono:

FIELDTERMINATOR: carattere usato per separare i valori dei campi in ogni riga. Ad esempio, un file delimitato da tabulazioni separa i campi con un carattere TAB (\t). Il carattere di terminazione del campo predefinito è una virgola (,).
ROWTERMINATOR: il carattere usato per indicare la fine di una riga di dati. Ad esempio, un file di testo standard di Windows usa una combinazione di ritorno a capo (CR) e avanzamento riga (LF), indicato dal codice \n; mentre i file di testo in stile UNIX usano un singolo carattere di avanzamento riga, che può essere indicato usando il codice 0x0a.
FIELDQUOTE: carattere usato per racchiudere i valori stringa tra virgolette. Ad esempio, per assicurarsi che la virgola nel valore del campo indirizzo 126 Main St, apt 2 non venga interpretata come delimitatore di campo, è possibile racchiudere l'intero valore del campo tra virgolette come segue: "126 Main St, apt 2". La virgoletta doppia (") è il carattere di virgoletta di campo predefinito.

Suggerimento

Per informazioni dettagliate sui parametri aggiuntivi durante l'uso di file di testo delimitati, vedere la documentazione Azure Synapse Analytics.

Specifica dello schema del set di righe

È comune che i file di testo delimitati includano i nomi delle colonne nella prima riga. La funzione OPENROWSET può usarla per definire lo schema per il set di righe risultante e dedurre automaticamente i tipi di dati delle colonne in base ai valori contenuti. Si consideri ad esempio il testo delimitato seguente:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

I dati sono costituiti dalle tre colonne seguenti:

product_id (numero intero)
product_name (stringa)
list_price (numero decimale)

È possibile usare la query seguente per estrarre i dati con i nomi di colonna corretti e dedurre in modo appropriato i tipi di dati di SQL Server (in questo caso INT, NVARCHAR e DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

Il parametro HEADER_ROW (disponibile solo quando si usa parser versione 2.0) indica al motore di query di usare la prima riga di dati in ogni file come nomi di colonna, come illustrato di seguito:

product_id	product_name	list_price
123	Widget	12.9900
124	Aggeggio	3.9900

Considerare ora i dati seguenti:

123,Widget,12.99
124,Gadget,3.99

Questa volta, il file non contiene i nomi di colonna in una riga di intestazione; pertanto, mentre i tipi di dati possono essere dedotti, i nomi delle colonne verranno impostati su C1, C2, C3e così via.

C1	C2	C3
123	Widget	12.9900
124	Aggeggio	3.9900

Per specificare nomi di colonna e tipi di dati espliciti, è possibile eseguire l'override dei nomi di colonna predefiniti e dei tipi di dati dedotti specificando una definizione dello schema in una clausola WITH, come illustrato di seguito:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Questa query produce i risultati previsti:

product_id	product_name	list_price
123	Widget	12.99
124	Aggeggio	3,99

Suggerimento

Quando si usano file di testo, è possibile riscontrare alcune incompatibilità con i dati con codifica UTF-8 e le regole di confronto usate nel database master per il pool SQL serverless. Per ovviare a questo problema, è possibile specificare regole di confronto compatibili per singole colonne VARCHAR nello schema. Per altre informazioni, vedere le indicazioni per la risoluzione dei problemi.

Esecuzione di query su file JSON

JSON è un formato comune per le applicazioni Web che scambiano dati tramite interfacce REST o usano archivi dati NoSQL, ad esempio Azure Cosmos DB. Non è quindi insolito rendere persistenti i dati come documenti JSON in file in un data lake per l'analisi.

Ad esempio, un file JSON che definisce un singolo prodotto potrebbe essere simile al seguente:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Per restituire i dati del prodotto da una cartella contenente più file JSON in questo formato, è possibile usare la query SQL seguente:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET non ha un formato specifico per i file JSON, pertanto è necessario usare formato csv con FIELDTERMINATOR, FIELDQUOTEe ROWTERMINATOR impostato su 0x0be uno schema che include una singola colonna NVARCHAR(MAX). Il risultato di questa query è un set di righe contenente una singola colonna di documenti JSON, come illustrato di seguito:

dottore
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Per estrarre singoli valori dal codice JSON, è possibile usare la funzione JSON_VALUE nell'istruzione SELECT, come illustrato di seguito:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Questa query restituisce un set di righe simile ai risultati seguenti:

prodotto	prezzo
Widget	12.99
Aggeggio	3,99

Esecuzione di query su file Parquet

Parquet è un formato comunemente usato per l'elaborazione di Big Data nell'archiviazione file distribuita. Si tratta di un formato di dati efficiente ottimizzato per la compressione e l'esecuzione di query analitiche.

Nella maggior parte dei casi, lo schema dei dati viene incorporato all'interno del file Parquet, pertanto è sufficiente specificare il parametro BULK con un percorso ai file da leggere e un parametro format FORMAT di parquet; Così:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Eseguire query su dati partizionati

In un data lake è comune partizionare i dati suddividendo più file in sottocartelle che riflettono i criteri di partizionamento. Ciò consente ai sistemi di elaborazione distribuita di funzionare in parallelo su più partizioni dei dati o di eliminare facilmente le letture dei dati da cartelle specifiche in base ai criteri di filtro. Si supponga, ad esempio, di dover elaborare in modo efficiente i dati degli ordini di vendita e spesso di filtrare in base all'anno e al mese in cui sono stati effettuati gli ordini. È possibile partizionare i dati usando cartelle, come illustrato di seguito:

/Ordini
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Per creare una query che filtra i risultati in modo da includere solo gli ordini per gennaio e febbraio 2020, è possibile usare il codice seguente:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

I parametri dei percorsi file numerati nella clausola WHERE fanno riferimento ai caratteri jolly nei nomi delle cartelle nel percorso BULK -so il parametro 1 è * nel nome della cartella year=* e il parametro 2 è * nel nome della cartella month=*.

Eseguire query sui file usando un pool SQL serverless

Esecuzione di query su file di testo delimitati

Specifica dello schema del set di righe

Esecuzione di query su file JSON

Esecuzione di query su file Parquet

Eseguire query su dati partizionati

Commenti e suggerimenti