Eseguire query sui file usando un pool SQL serverless

  • 10 minuti

È possibile utilizzare un pool SQL serverless per eseguire query sui file di dati in vari 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 tabulari dai dati di uno o più file. La query seguente, ad esempio, può essere utilizzata 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 diversi, ad esempio:

  • Schema del set di righe risultante
  • Altre opzioni di formattazione per i file di testo delimitati.

Suggerimento

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

L'output delle funzione OPENROWSET è un set di righe cui deve essere assegnato un alias. Nell'esempio precedente le righe alias vengono utilizzate per assegnare un nome al set di righe risultante.

Il parametro BULK include l'URL completo della posizione nel data lake che contiene 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 da includere. Il parametro FORMAT specifica il tipo di dati su cui eseguire la query. L'esempio precedente legge il testo delimitato da tutti i file con estensione csv nella cartella dei file .

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 con un'identità personalizzata, è necessario creare una credenziale con ambito server.

Come illustrato nell'esempio precedente, è possibile utilizzare i caratteri jolly nel parametro BULK per includere o escludere file nella query. L'elenco seguente mostra alcuni esempi del modo in cui il parametro può essere utilizzato:

  • https://mydatalake.blob.core.windows.net/data/files/file1.csv: include solo file1.csv nella cartella dei file.
  • https://mydatalake.blob.core.windows.net/data/files/file*.csv: tutti i file con estensione csv nella cartella dei file con il nome che inizia con "file".
  • https://mydatalake.blob.core.windows.net/data/files/*: tutti i file nella cartella dei file.
  • https://mydatalake.blob.core.windows.net/data/files/**: tutti i file nella cartella dei file e le relative sottocartelle, in modo ricorsivo.

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

Eseguire query su file di testo delimitati

I file di testo delimitati sono file di formato comune utilizzati in molte aziende. La formattazione specifica utilizzata nei file con valori delimitati può variare, ad esempio:

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

Indipendentemente dal tipo di file con valori delimitati in uso, è possibile leggerne i dati usando la funzione OPENROWSET con il parametro FORMAT csv e altri parametri necessari per gestire i dettagli di formattazione specifici per i dati. Ad 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

PARSER_VERSION consente di determinare il modo in cui la query interpreta la codifica di testo utilizzata nei file. La versione 1.0 è quella 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 consente di ignorare le righe nel file di testo per eliminare il testo di preambolo non strutturato o per ignorare una riga contenente intestazioni di colonna.

Altri parametri che possono essere necessari quando si utilizzano file di testo delimitati includono:

  • FIELDTERMINATOR: carattere utilizzato per separare i valori dei campi in ogni riga. Un file con valori delimitati da tabulazioni, ad esempio, separa i campi con un carattere TAB (\t). Il carattere di terminazione del campo predefinito è la virgola (,).
  • ROWTERMINATOR: carattere utilizzato per indicare la fine di una riga di dati. Un file di testo Windows standard, ad esempio, utilizza una combinazione di ritorno a capo e di avanzamento riga, indicato dal codice \n, mentre i file di testo di tipo UNIX utilizzano un carattere di avanzamento riga singolo, che può essere indicato con il codice 0x0a.
  • FIELDQUOTE: carattere utilizzato per racchiudere valori stringa tra virgolette. Per assicurarsi che la virgola nel valore del campo indirizzo Via Principale 126, app 2 non venga interpretata come un delimitatore di campo, è possibile racchiudere l'intero valore del campo tra virgolette nel modo seguente:"Via Principale 126, app 2". Le virgolette doppie (") rappresentano il carattere di virgoletta del campo predefinito.

Suggerimento

Per dettagli sui parametri aggiuntivi relativi all'uso di file di testo delimitati, vedere la documentazione di Azure Synapse Analytics.

Specifica dello schema dei set di righe

È comune che i file di testo delimitati includano i nomi di colonna nella prima riga. In tal modo, la funzione OPENROWSET può 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 utilizzare la query seguente per estrarre i dati con i nomi di colonna corretti e i tipi di dati di SQL Server dedotti in modo appropriato (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 utilizza parser versione 2.0) indica al motore di query di utilizzare 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 Gadget 3,9900

Si considerino 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 e di conseguenza, anche se i tipi di dati possono ancora essere dedotti, i nomi delle colonne vengono impostati su C1, C2, C3 e così via.

C1 S2 C3
123 Widget 12,9900
124 Gadget 3,9900

Per specificare nomi di colonne e tipi di dati espliciti, è possibile eseguire l'override dei nomi di colonne predefiniti e dei tipi di dati dedotti specificando una definizione di 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

La query restituisce i risultati previsti:

product_id product_name list_price
123 Widget 12,99
124 Gadget 3,99

Suggerimento

Quando si utilizzano file di testo, possono verificarsi alcune incompatibilità con i dati codificati UTF-8 e le regole di confronto utilizzate nel database master per il pool SQL serverless. Per risolvere 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 molto diffuso per le applicazioni Web che si scambino dati tramite interfacce REST o che utilizzano archivi dati NoSQL come Azure Cosmos DB. Di conseguenza, non è insolito mantenere i dati come documenti JSON in file di un data lake per l'analisi.

Un file JSON che definisce un singolo prodotto, ad esempio, può essere simile al seguente:

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

Per restituire i dati di prodotto da una cartella contenente più file JSON in questo formato, è possibile utilizzare 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, quindi è necessario utilizzare il formato csv con FIELDTERMINATOR, FIELDQUOTE e ROWTERMINATOR impostati su 0x0b e uno schema che include una singola colonna NVARCHAR(MAX). Il risultato della query è un set di righe contenente una singola colonna di documenti JSON, come illustrato di seguito:

doc
{"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 file JSON, è possibile utilizzare 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

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

product price
Widget 12,99
Gadget 3,99

Esecuzione di query su file Parquet

Parquet è un formato comunemente utilizzato per l'elaborazione di Big Data nello spazio di archiviazione file distribuito. Si tratta di un formato dati efficiente e ottimizzato per la compressione e l'esecuzione di query analitiche.

Nella maggior parte dei casi, lo schema dei dati è incorporato nel file con estensione csv, quindi è sufficiente specificare il parametro BULK con un percorso ai file da leggere e un parametro FORMAT di Parquet, come illustrato di seguito:

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 dividendoli tra più file in sottocartelle che riflettono i criteri di partizionamento. Ciò consente ai sistemi di elaborazione distribuiti di funzionare in parallelo su più partizioni dei dati o di eliminare facilmente le letture dei dati da cartelle specifiche in base a criteri di filtro. Si supponga, ad esempio, di dover elaborare in modo efficiente i dati degli ordini di vendita e di dover filtrare spesso in base all'anno e al mese in cui sono stati effettuati gli ordini. È possibile partizionare i dati usando le cartelle, come illustrato di seguito:

  • /orders
    • /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 di gennaio e febbraio 2020, è possibile utilizzare 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 filepath numerati nella clausola WHERE fanno riferimento ai caratteri jolly nei nomi delle cartelle nel percorso BULK, quindi il parametro 1 è * nel nome della cartella year=* e il parametro 2 è * nel nome della cartella month=*.