Condividi tramite

Eseguire query su cartelle e più file

  • Articolo

Questo articolo spiega come scrivere una query con il pool SQL serverless in Azure Synapse Analytics.

Il pool SQL serverless supporta la lettura di più file/cartelle con caratteri jolly, che sono simili ai caratteri jolly usati nel sistema operativo Windows. La flessibilità è tuttavia superiore, perché sono consentiti più caratteri jolly.

Prerequisiti

Il primo passaggio consiste nel creare un database in cui verranno eseguite le query. 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.

Per seguire le query di esempio, si userà la cartella csv/taxi. La cartella contiene record relativi alle corse dei taxi gialli di New York da luglio 2016 a giugno 2018. Il nome dei file in csv/taxi include anno e mese, come nel modello seguente: yellow_tripdata_<anno>-<mese>.csv

Leggere tutti i file nella cartella

L'esempio seguente consente di leggere tutti i file di dati relativi ai taxi gialli di NYC dalla cartella csv/taxi e restituisce il numero totale di passeggeri e di corse all'anno. Illustra inoltre l'uso delle funzioni di aggregazione.

SELECT 
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        pickup_datetime DATETIME2 2, 
        passenger_count INT 4
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Leggere subset di file nella cartella

L'esempio seguente consente di leggere i file di dati relativi ai taxi gialli di NYC nel 2017 dalla cartella csv/taxi usando un carattere jolly e restituisce l'importo totale della tariffa per tipo di pagamento.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Leggere il subset di file nella cartella usando più percorsi di file

L'esempio seguente consente di leggere i file di dati relativi ai taxi gialli di NYC nel 2017 dalla cartella csv/taxi usando due percorsi di file, in cui il primo include il percorso completo del file che contiene i dati del mese di gennaio e il secondo che contiene un carattere jolly per leggere i mesi di novembre e di dicembre e che restituisce l'importo totale della tariffa per tipo di pagamento.

SELECT 
    payment_type,  
    SUM(fare_amount) AS fare_total
FROM OPENROWSET(
        BULK (
            'csv/taxi/yellow_tripdata_2017-01.csv',
            'csv/taxi/yellow_tripdata_2017-1*.csv'
        ),
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        payment_type INT 10,
        fare_amount FLOAT 11
    ) AS nyc
GROUP BY payment_type
ORDER BY payment_type;

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Leggere cartelle

Il percorso specificato in OPENROWSET può anche essere il percorso di una cartella. Le sezioni seguenti includono questi tipi di query.

Leggere tutti i file di una cartella specifica

È possibile leggere tutti i file presenti in una cartella usando il carattere jolly a livello di file, come illustrato in Leggere tutti i file presenti nella cartella. Esiste tuttavia un modo per eseguire query su una cartella e utilizzare tutti i file all'interno di tale cartella.

Se il percorso specificato in OPENROWSET punta a una cartella, tutti i file in tale cartella verranno usati come origine per la query. La query seguente leggerà tutti i file presenti nella cartella csv/taxi.

Nota

Si noti l'uso di / alla fine del percorso nella query seguente. Indica che si tratta di una cartella. Se si omette la barra (/), la destinazione della query sarà un file denominato taxi.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Leggere tutti i file in più cartelle

È possibile leggere i file in più cartelle usando un carattere jolly. La query seguente leggerà tutti i file di tutte le cartelle presenti nella cartella csv i cui nomi iniziano con t e terminano con i.

Nota

Si noti l'uso di / alla fine del percorso nella query seguente. Indica che si tratta di una cartella. Se si omette la barra (/), la destinazione della query saranno i file denominati t*i.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Poiché è presente una sola cartella che corrisponde ai criteri, il risultato della query è uguale a quello ottenuto in Leggere tutti i file presenti nella cartella.

Attraversare le cartelle in modo ricorsivo

Il pool SQL serverless può attraversare le cartelle in modo ricorsivo se si specifica /** alla fine del percorso. La query seguente leggerà tutti i file di tutte le cartelle e le sottocartelle presenti nella cartella csv/taxi.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/taxi/**', 
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Più caratteri jolly

È possibile usare più caratteri jolly a livelli di percorso diversi. Ad esempio, è possibile arricchire la query precedente per leggere solo i file con dati del 2017 da tutte le cartelle che iniziano con t e terminano con i.

Nota

Si noti l'uso di / alla fine del percorso nella query seguente. Indica che si tratta di una cartella. Se si omette la barra (/), la destinazione della query saranno i file denominati t*i. È previsto un limite massimo di 10 caratteri jolly per ogni query.

SELECT
    YEAR(pickup_datetime) as [year],
    SUM(passenger_count) AS passengers_total,
    COUNT(*) AS [rides_total]
FROM OPENROWSET(
        BULK 'csv/t*i/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'sqlondemanddemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIRSTROW = 2
    )
    WITH (
        vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
        pickup_datetime DATETIME2, 
        dropoff_datetime DATETIME2,
        passenger_count INT,
        trip_distance FLOAT,
        rate_code INT,
        store_and_fwd_flag VARCHAR(100) COLLATE Latin1_General_BIN2,
        pickup_location_id INT,
        dropoff_location_id INT,
        payment_type INT,
        fare_amount FLOAT,
        extra FLOAT,
        mta_tax FLOAT,
        tip_amount FLOAT,
        tolls_amount FLOAT,
        improvement_surcharge FLOAT,
        total_amount FLOAT
    ) AS nyc
GROUP BY
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime);

Nota

Tutti i file a cui si accede con l'unica funzione OPENROWSET devono avere la stessa struttura, ovvero lo stesso numero di colonne con lo stesso tipo di dati.

Poiché è presente una sola cartella che corrisponde ai criteri, il risultato della query è uguale a Leggere il subset di file presente nella cartella e Leggere tutti i file di una cartella specifica. Gli scenari in cui vengono usati caratteri jolly più complessi sono trattati in Eseguire query su file Parquet.

Passaggi successivi

Per altre informazioni, vedere l'articolo Eseguire query su file specifici.