Eseguire query su cartelle e più file
Questo articolo illustra come scrivere una query usando un pool SQL serverless in Azure Synapse Analytics.
Il pool SQL serverless supporta la lettura di più file o cartelle usando caratteri jolly, simili ai caratteri jolly usati in Windows. La flessibilità è tuttavia superiore, perché sono consentiti più caratteri jolly.
Prerequisiti
Il primo passaggio consiste nel creare un database in cui è possibile eseguire le query. Inizializzare quindi gli oggetti eseguendo uno script di installazione in tale database. Questo script di installazione crea le origini dati, le credenziali con ambito database e i formati di file esterni usati in questi esempi.
Usare la cartella csv/taxi per seguire le query di esempio. Contiene i dati nyc Taxi - Yellow Taxi Trip Records da luglio 2016 a giugno 2018. I file in csv/taxi sono denominati dopo anno e mese usando il modello seguente:
yellow_tripdata_<year>-<month>.csv*
Leggere tutti i file nella cartella
L'esempio seguente legge tutti i file di dati nyc Yellow Taxi dalla cartella csv/taxi e quindi restituisce il numero totale di passeggeri e 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi di dati).
Leggere subset di file nella cartella
L'esempio seguente legge i file di dati 2017 NYC Yellow Taxi 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi di dati).
Leggere il subset di file nella cartella usando più percorsi di file
L'esempio seguente legge i file di dati 2017 NYC Yellow Taxi dalla cartella csv/taxi usando due percorsi di file. Il primo usa il percorso completo del file contenente i dati del mese di gennaio e il secondo usa un carattere jolly per leggere i mesi ottobre, novembre e dicembre. Per ogni percorso, viene restituito 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi di dati).
Leggere cartelle
Il percorso fornito da OPENROWSET può anche essere un 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 in una cartella usando il carattere jolly a livello di file, come illustrato in Leggere tutti i file 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 vengono usati come origine per la query. La query seguente legge tutti i file nella cartella csv/taxi .
Nota
Si noti l'esistenza di / alla fine del percorso nella query. Indica che si tratta di una cartella. Se l'oggetto / viene omesso, la query è destinata a 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi di dati).
Leggere tutti i file in più cartelle
È possibile leggere i file in più cartelle usando un carattere jolly. La query seguente legge tutti i file di tutte le cartelle presenti nella cartella csv con nomi che iniziano con t e terminano con i.
Nota
Si noti l'esistenza di / alla fine del percorso nella query. Indica che si tratta di una cartella. Se l'oggetto / viene omesso, la query è destinata ai 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi 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 in modo ricorsivo le cartelle se si specifica /** alla fine del percorso. La query seguente legge tutti i file da 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi di dati).
Usare più caratteri jolly
È possibile usare più caratteri jolly a livelli di percorso diversi. Ad esempio, è possibile arricchire una query precedente per leggere i file solo con dati 2017, da tutte le cartelle in cui i nomi iniziano con t e terminano con i.
Nota
Si noti l'esistenza di / alla fine del percorso nella query. Indica che si tratta di una cartella. Se l'oggetto / viene omesso, la query è destinata ai file denominati t*i .
Esiste 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 il singolo OPENROWSET devono avere la stessa struttura (numero di colonne e relativi tipi 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. Per scenari di utilizzo con caratteri jolly più complessi, vedere Eseguire query su file Parquet.