Eseguire query sui file Parquet usando il pool SQL serverless in Azure Synapse Analytics

Articolo
10/18/2023

In questo articolo si apprenderà come scrivere una query usando un pool SQL serverless che leggerà i file Parquet.

Esempio di avvio rapido

OPENROWSET la funzione consente di leggere il contenuto del file parquet fornendo l'URL al file.

Leggere file Parquet

Il modo più semplice per visualizzare il contenuto del file consiste nel fornire l'URL del PARQUET file per OPENROWSET funzionare e specificare parquet FORMAT. 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 come 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 chiave di firma di accesso condiviso o identità di Azure personalizzata, è 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 modificare 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 Latin1_General_100_BIN2_UTF8 regole di confronto, si otterrà un miglioramento delle prestazioni aggiuntivo rispetto alle altre regole di confronto. Le Latin1_General_100_BIN2_UTF8 regole di confronto 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 il filtro avviene all'interno del processo SQL. Le Latin1_General_100_BIN2_UTF8 regole di confronto hanno un'ottimizzazione delle prestazioni aggiuntiva 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 OPENROWSET funzione:

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 WITH la clausola :

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 alcune regole di confronto UTF-8 (ad esempio Latin1_General_100_BIN2_UTF8) per tutte le colonne stringa nella WITH clausola o impostare alcune regole di confronto UTF-8 a livello di database. La mancata corrispondenza tra la codifica del testo nelle regole di confronto delle colonne di file e stringhe potrebbe causare errori di conversione imprevisti. È possibile modificare facilmente le 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 regole di confronto, 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 legge il numero di file contemporaneamente, lo schema, i nomi di colonna e i tipi di dati verranno dedotti dal primo servizio file dalla risorsa di archiviazione. Ciò può significare che alcune delle colonne previste vengono omesse, tutte perché il file usato dal servizio per definire lo schema non contiene queste colonne. Per specificare in modo esplicito lo schema, usare la clausola OPENROW edizione Standard T WITH.

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

Nota

Quando si leggono file Parquet, non è necessario specificare colonne nella clausola OPENROWSET WITH. In tal caso, il servizio di query del pool SQL serverless utilizzerà i metadati nel file Parquet e associa le colonne in base al 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 mapping del tipo nativo SQL per Parquet.

Passaggi successivi

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