Query uitvoeren op mappen en meerdere bestanden
In dit artikel leert u hoe u een query schrijft met behulp van een serverloze SQL-pool in Azure Synapse Analytics.
Serverloze SQL-pool ondersteunt het lezen van meerdere bestanden of mappen met behulp van jokertekens, die vergelijkbaar zijn met de jokertekens die worden gebruikt in Windows. Er is echter meer flexibiliteit aanwezig omdat meerdere jokertekens zijn toegestaan.
Vereisten
De eerste stap is het maken van een database waarin u de query's kunt uitvoeren. Initialiseer vervolgens de objecten door een installatiescript op die database uit te voeren. Met dit installatiescript worden de gegevensbronnen, referenties voor databasebereik en externe bestandsindelingen gemaakt die in deze voorbeelden worden gebruikt.
Gebruik de map CSV/taxi om de voorbeeldquery's te volgen. Het bevat NYC Taxi - Yellow Taxi Trip Records gegevens van juli 2016 tot juni 2018. Bestanden in CSV/taxi zijn vernoemd naar jaar en maand met behulp van het volgende patroon:
yellow_tripdata_<year>-<month>.csv*
Alle bestanden in map lezen
In het volgende voorbeeld worden alle NYC Yellow Taxi-gegevensbestanden uit de map CSV/taxi gelezen en wordt vervolgens het totale aantal passagiers en ritten per jaar geretourneerd. Het toont ook het gebruik van statistische functies.
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);
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Subset van bestanden in map lezen
In het volgende voorbeeld worden de gegevensbestanden van NYC Yellow Taxi 2017 uit de csv-/taximap gelezen met behulp van een jokerteken en wordt het totale tariefbedrag per betalingstype geretourneerd.
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;
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Subset van bestanden in map lezen met behulp van meerdere bestandspaden
In het volgende voorbeeld worden de NYC Yellow Taxi-gegevensbestanden van 2017 uit de csv-/taximap gelezen met behulp van twee bestandspaden. De eerste gebruikt het volledige pad naar het bestand met gegevens uit de maand januari en de tweede gebruikt een jokerteken om de maanden oktober, november en december te lezen. Voor elk pad wordt het totale tariefbedrag per betalingstype geretourneerd.
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;
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Mappen lezen
Het pad dat u opgeeft OPENROWSET , kan ook een pad naar een map zijn. De volgende secties bevatten deze querytypen.
Alle bestanden uit een specifieke map lezen
U kunt alle bestanden in een map lezen met behulp van het jokerteken op bestandsniveau, zoals wordt weergegeven in Alle bestanden in de map lezen. Maar er is een manier om een query uit te voeren op een map en alle bestanden in die map te gebruiken.
Als het pad in OPENROWSET verwijst naar een map, worden alle bestanden in die map gebruikt als bron voor uw query. Met de volgende query worden alle bestanden in de map CSV/taxi gelezen.
Notitie
Let op het bestaan van het / pad aan het einde van het pad in de query. Het geeft een map aan. Als de / query wordt weggelaten, richt de query zich in plaats daarvan op een bestand met de naam 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);
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Alle bestanden uit meerdere mappen lezen
Het is mogelijk om bestanden uit meerdere mappen te lezen met behulp van een jokerteken. Met de volgende query worden alle bestanden uit alle mappen in de CSV-map gelezen met namen die beginnen met t en eindigen op i.
Notitie
Let op het bestaan van het / pad aan het einde van het pad in de query. Het geeft een map aan. Als de / query wordt weggelaten, worden de bestanden met de naam t*i in plaats daarvan gericht.
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);
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Omdat u slechts één map hebt die aan de criteria voldoet, is het queryresultaat hetzelfde als alle bestanden in de map lezen.
Mappen recursief doorkruisen
Serverloze SQL-pool kan mappen recursief doorlopen als u aan het einde van het pad opgeeft /** . Met de volgende query worden alle bestanden uit alle mappen en submappen in de map CSV/taxi gelezen.
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);
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Meerdere jokertekens gebruiken
U kunt meerdere jokertekens op verschillende padniveaus gebruiken. U kunt bijvoorbeeld een eerdere query verrijken om bestanden met alleen 2017-gegevens te lezen, vanuit alle mappen waarin namen beginnen met t en eindigen op i.
Notitie
Let op het bestaan van het / pad aan het einde van het pad in de query. Het geeft een map aan. Als de / query wordt weggelaten, worden de bestanden met de naam t*i in plaats daarvan gericht.
Er is een maximumlimiet van 10 jokertekens per 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);
Notitie
Alle bestanden die worden geopend met het ene OPENROWSET bestand, moeten dezelfde structuur hebben (het aantal kolommen en de bijbehorende gegevenstypen).
Omdat u slechts één map hebt die voldoet aan de criteria, is het queryresultaat hetzelfde als de subset Lezen van bestanden in de map en alle bestanden uit een specifieke map lezen. Zie Parquet-bestanden opvragen voor complexere scenario's met jokertekens.