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/mappen met behulp van jokertekens, die vergelijkbaar zijn met de jokertekens die worden gebruikt in het Windows-besturingssysteem. Er is echter meer flexibiliteit omdat meerdere jokertekens zijn toegestaan.

Vereisten

De eerste stap bestaat uit het maken van een database waarin u de query's gaat uitvoeren. Initialiseer vervolgens de objecten door een installatiescript uit te voeren op die database. Met dit installatiescript worden de gegevensbronnen, referenties voor databasebereik en externe bestandsindelingen gemaakt die in deze voorbeelden worden gebruikt.

U gebruikt 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 hebben de naam jaar en maand met het volgende patroon: yellow_tripdata_<jaarmaand><>.csv

Alle bestanden in map lezen

In het onderstaande voorbeeld worden alle NYC Yellow Taxi-gegevensbestanden uit de csv-/taximap gelezen en wordt het totale aantal passagiers en ritten per jaar geretourneerd. Ook wordt het gebruik van statistische functies weergegeven.

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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen het aantal kolommen en de bijbehorende gegevenstypen).

Subset van bestanden in map lezen

In het onderstaande voorbeeld worden de 2017 NYC Yellow Taxi-gegevensbestanden uit de csv-/taxi-map gelezen met behulp van een jokerteken en wordt het totale tarief 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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen het aantal kolommen en de bijbehorende gegevenstypen).

Subset van bestanden in map lezen met behulp van meerdere bestandspaden

In het onderstaande voorbeeld worden de 2017 NYC Yellow Taxi-gegevensbestanden uit de csv-/taxi-map gelezen met 2 bestandspaden eerst met het volledige pad naar het bestand met gegevens van de maand januari en de tweede met een jokerteken in de maanden november en december die het totale tarief per betalingstype retourneren.

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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen het aantal kolommen en de bijbehorende gegevenstypen).

Mappen lezen

Het pad dat u naar OPENROWSET opgeeft, 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 map lezen. Er is echter een manier om een query uit te voeren op een map en alle bestanden in die map te gebruiken.

Als het pad in OPENROWSET naar een map verwijst, worden alle bestanden in die map gebruikt als bron voor uw query. Met de volgende query worden alle bestanden in de csv-/taxi-map gelezen.

Notitie

Let op het bestaan van de / aan het einde van het pad in de onderstaande query. Het geeft een map aan. Als de / wordt weggelaten, is de query in plaats daarvan gericht 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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen 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 die namen hebben die beginnen met t en eindigen op i.

Notitie

Let op het bestaan van de / aan het einde van het pad in de onderstaande query. Het geeft een map aan. Als de / wordt weggelaten, richt de query zich in plaats daarvan op bestanden met de naam 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);

Notitie

Alle bestanden die met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen 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 map lezen.

Recursief mappen doorlopen

Serverloze SQL-pool kan recursief mappen 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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen het aantal kolommen en de bijbehorende gegevenstypen).

Meerdere jokertekens

U kunt meerdere jokertekens gebruiken op verschillende padniveaus. U kunt bijvoorbeeld de vorige query verrijken om bestanden te lezen met alleen 2017-gegevens, uit alle mappen waarvan de namen beginnen met t en eindigen op i.

Notitie

Let op het bestaan van de / aan het einde van het pad in de onderstaande query. Het geeft een map aan. Als de / wordt weggelaten, richt de query zich in plaats daarvan op bestanden met de naam t*i . 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 met één OPENROWSET worden geopend, moeten dezelfde structuur hebben (dat wil zeggen het aantal kolommen en de bijbehorende gegevenstypen).

Omdat u slechts één map hebt die voldoet aan de criteria, is het queryresultaat hetzelfde als Subset van bestanden in map lezen en Alle bestanden uit een specifieke map lezen. Complexere gebruiksscenario's met jokertekens worden behandeld in Query Parquet-bestanden.

Volgende stappen

Meer informatie vindt u in het artikel Query-specifieke bestanden .