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

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.

Se si usano le Latin1_General_100_BIN2_UTF8 regole di confronto, si otterrà un aumento delle prestazioni rispetto alle altre regole di confronto. 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 contengono 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'altra 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

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 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.

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.

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.

Passaggio successivo