Eseguire query su file Parquet usando un pool SQL serverless in Azure Synapse Analytics

Articolo
04/29/2024

In questo articolo verrà illustrato come scrivere una query con un pool SQL serverless che leggerà i file Parquet.

Esempio di avvio rapido

La funzione OPENROWSET consente di leggere il contenuto di un file Parquet fornendo l'URL al file.

Leggere file Parquet

Il modo più semplice per visualizzare il contenuto del file PARQUET consiste nel fornire l'URL del file alla funzione OPENROWSET e specificare FORMAT Parquet. Se il file è disponibile pubblicamente o se l'identità di Microsoft Entra può accedere a questo file, dovrebbe essere possibile visualizzare il contenuto del file usando la query simile a quella illustrata nell'esempio seguente:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Verificare che sia possibile accedere a questo file. Se il file è protetto con una chiave di firma di accesso condiviso o con un'identità di Azure, sarà necessario configurare le credenziali a livello di server per l'account di accesso SQL.

Importante

Assicurarsi di usare regole di confronto del database UTF-8 (ad esempio Latin1_General_100_BIN2_UTF8) perché i valori stringa nei file PARQUET vengono codificati usando la codifica UTF-8. Una mancata corrispondenza tra la codifica del testo nel file PARQUET e le regole di confronto può causare errori di conversione imprevisti. È possibile cambiare facilmente le regole di confronto predefinite del database corrente usando l'istruzione T-SQL seguente: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; Per altre informazioni sulle regole di confronto, vedere Tipi di regole di confronto supportati per Synapse SQL.

Se si usano le regole di confronto Latin1_General_100_BIN2_UTF8, si otterrà un ulteriore miglioramento delle prestazioni rispetto alle altre. Le regole di confronto Latin1_General_100_BIN2_UTF8 sono compatibili con le regole di ordinamento delle stringhe Parquet. Il pool SQL è in grado di eliminare alcune parti dei file Parquet che non conterranno i dati necessari nelle query (eliminazione di file/segmento di colonna). Se si usano altre regole di confronto, tutti i dati dei file Parquet verranno caricati in Synapse SQL e verranno applicati filtri all'interno del processo SQL. Le regole di confronto Latin1_General_100_BIN2_UTF8 prevedono un'ulteriore ottimizzazione delle prestazioni che funziona solo per Parquet e Cosmos DB. Lo svantaggio è che si perdono regole di confronto con granularità fine come la distinzione tra maiuscole e minuscole.

Utilizzo dell'origine dati

Nell'esempio precedente viene usato il percorso completo del file. In alternativa, è possibile creare un'origine dati esterna con il percorso che punta alla cartella radice dell'archiviazione e usare tale origine dati e il percorso relativo del file nella funzione OPENROWSET:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

Se un'origine dati è protetta con una chiave di firma di accesso condiviso o un'identità personalizzata, è possibile configurare l'origine dati con credenziali con ambito database.

Specificare in modo esplicito lo schema

OPENROWSET consente di specificare in modo esplicito le colonne da leggere dal file usando la clausola WITH:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Importante

Assicurarsi di specificare in modo esplicito una regola di confronto UTF-8 (ad esempio Latin1_General_100_BIN2_UTF8) per tutte le colonne stringa nella clausola WITH o impostare una regola di confronto UTF-8 a livello di database. Una mancata corrispondenza tra la codifica del testo nelle regole di confronto delle colonne di file e stringa potrebbe causare errori di conversione imprevisti. È possibile cambiare facilmente regole di confronto predefinite del database corrente usando l'istruzione T-SQL seguente: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8; È possibile impostare facilmente le regole di confronto sui tipi di colonna, ad esempio: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Per altre informazioni sulle regole di confronto, vedere Tipi di regole di confronto supportati per Synapse SQL.

Nelle sezioni seguenti è possibile vedere come eseguire query su vari tipi di file PARQUET.

Prerequisiti

Il primo passaggio consiste nel creare un database con un'origine dati che faccia riferimento all'account di archiviazione NYC Yellow Taxi. Inizializzare quindi gli oggetti eseguendo uno script di installazione su tale database. Questo script di installazione creerà le origini dati, le credenziali con ambito database e i formati di file esterni usati in questi esempi.

Set di dati

In questo esempio viene usato il set di dati di NYC Yellow Taxi. È possibile eseguire query sui file Parquet nello stesso modo in cui si leggono i file CSV. L'unica differenza è che il parametro FILEFORMAT deve essere impostato su PARQUET. Gli esempi in questo articolo illustrano le specifiche relative alla lettura di file Parquet.

Set di query di file Parquet

Quando si eseguono query su file Parquet, è possibile specificare solo le colonne di interesse.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Inferenza automatica dello schema

Quando si leggono file Parquet, non è necessario usare la clausola OPENROWSET WITH. I nomi di colonna e i tipi di dati vengono letti automaticamente dai file Parquet.

Tenere presente che se si leggono numerosi file contemporaneamente, lo schema, i nomi di colonna e i tipi di file verranno dedotti dal primo file che il servizio ottiene dall'archiviazione. Ciò può significare che alcune colonne previste vengono omesse, perché non sono contenute nel file usato dal servizio per definire lo schema. Per specificare esplicitamente lo schema, usare la clausola OPENROWSET WITH.

L'esempio seguente mostra le funzionalità di inferenza automatica dello schema per i file Parquet. Restituisce il numero di righe per settembre 2018 senza specificare uno schema.

Nota

Quando si leggono file Parquet, non è necessario specificare colonne nella clausola OPENROWSET WITH. In questo caso, il servizio query del pool SQL serverless utilizzerà i metadati del file Parquet e assocerà le colonne per nome.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Eseguire query su dati partizionati

Il set di dati usato in questo esempio è diviso (partizionato) in sottocartelle separate. È possibile indicare come destinazione partizioni specifiche usando la funzione filepath. Questo esempio illustra gli importi delle tariffe per anno, mese e payment_type per i primi tre mesi del 2017.

Nota

Le query del pool SQL serverless sono compatibili con lo schema di partizione Hive/Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Mapping dei tipi

Per il mapping dei tipi Parquet al tipo nativo SQL, consultare Mapping dei tipi per Parquet.

Passaggi successivi

Per informazioni su come eseguire query su tipi nidificati Parquet, passare al prossimo articolo.