Efterfråga mappar och flera filer
I den här artikeln får du lära dig hur du skriver en fråga med hjälp av en serverlös SQL-pool i Azure Synapse Analytics.
Serverlös SQL-pool stöder läsning av flera filer eller mappar med jokertecken, som liknar de jokertecken som används i Windows. Det finns dock större flexibilitet eftersom flera jokertecken tillåts.
Förutsättningar
Ditt första steg är att skapa en databas där du kan köra frågorna. Initiera sedan objekten genom att köra ett installationsskript på databasen. Det här installationsskriptet skapar de datakällor, databasomfattningsautentiseringsuppgifter och externa filformat som används i dessa exempel.
Använd mappen csv/taxi för att följa exempelfrågorna. Den innehåller data om NYC Taxi – Yellow Taxi Trip Records från juli 2016 till juni 2018. Filer i csv/taxi namnges efter år och månad med hjälp av följande mönster:
yellow_tripdata_<year>-<month>.csv*
Läsa alla filer i mappen
I följande exempel läss alla NYC Yellow Taxi-datafiler från mappen csv/taxi och returnerar sedan det totala antalet passagerare och resor per år. Den visar också användningen av aggregerade funktioner.
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);
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Läsa delmängd av filer i mappen
I följande exempel läss datafilerna för 2017 NYC Yellow Taxi från mappen csv/taxi med ett jokertecken och returnerar det totala prisbeloppet per betalningstyp.
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;
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Läsa delmängd av filer i mappen med hjälp av flera filsökvägar
I följande exempel läss datafilerna för 2017 NYC Yellow Taxi från mappen csv/taxi med hjälp av två filsökvägar. Den första använder den fullständiga sökvägen till filen som innehåller data från januari månad, och den andra använder ett jokertecken för att läsa månaderna oktober, november och december. För varje sökväg returneras det totala prisbeloppet per betalningstyp.
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;
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Läs mappar
Sökvägen som du anger till OPENROWSET kan också vara en sökväg till en mapp. Följande avsnitt innehåller dessa frågetyper.
Läsa alla filer från en specifik mapp
Du kan läsa alla filer i en mapp med hjälp av jokertecknet på filnivå enligt läs alla filer i mappen. Men det finns ett sätt att köra frågor mot en mapp och använda alla filer i mappen.
Om sökvägen som anges i OPENROWSET pekar på en mapp används alla filer i mappen som källa för din fråga. Följande fråga läser alla filer i mappen csv/taxi .
Kommentar
Observera förekomsten av / i slutet av sökvägen i frågan. Det anger en mapp. Om utelämnas / riktar frågan in sig på en fil med namnet taxi i stället.
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);
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Läsa alla filer från flera mappar
Det går att läsa filer från flera mappar med hjälp av ett jokertecken. Följande fråga läser alla filer från alla mappar som finns i csv-mappen med namn som börjar med t och slutar med i.
Kommentar
Observera förekomsten av / i slutet av sökvägen i frågan. Det anger en mapp. Om utelämnas / riktar frågan in sig på filer med namnet t*i i stället.
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);
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Eftersom du bara har en mapp som matchar kriterierna är frågeresultatet detsamma som Läs alla filer i mappen.
Bläddra igenom mappar rekursivt
Serverlös SQL-pool kan rekursivt passera mappar om du anger /** i slutet av sökvägen. Följande fråga läser alla filer från alla mappar och undermappar som finns i mappen 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);
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Använda flera jokertecken
Du kan använda flera jokertecken på olika sökvägsnivåer. Du kan till exempel utöka en tidigare fråga för att läsa filer med endast 2017-data, från alla mappar där namn börjar med t och slutar med i.
Kommentar
Observera förekomsten av / i slutet av sökvägen i frågan. Det anger en mapp. Om utelämnas / riktar frågan in sig på filer med namnet t*i i stället.
Det finns en maxgräns på 10 jokertecken per fråga.
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);
Kommentar
Alla filer som nås med den enskilda OPENROWSET måste ha samma struktur (antal kolumner och deras datatyper).
Eftersom du bara har en mapp som matchar kriterierna är frågeresultatet detsamma som Läs delmängd av filer i mappen och Läsa alla filer från en specifik mapp. Mer komplexa scenarier för jokertecken finns i Fråga Parquet-filer.