Efterfråga mappar och flera filer

  • Artikel

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/mappar med jokertecken, som liknar de jokertecken som används i Windows OS. Det finns dock större flexibilitet eftersom flera jokertecken tillåts.

Förutsättningar

Det första steget är att skapa en databas där du kör frågorna. Initiera sedan objekten genom att köra installationsskriptet på databasen. Det här installationsskriptet skapar datakällor, databasbegränsade autentiseringsuppgifter och externa filformat som används i dessa exempel.

Du använder 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 följande mönster: yellow_tripdata_<årsmånad><>.csv

Läsa alla filer i mappen

Exemplet nedan läser alla datafiler för NYC Yellow Taxi från mappen csv/taxi och returnerar det totala antalet passagerare och resor per år. Den visar också användningen av mängdfunktioner.

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);

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Läsa delmängd av filer i mappen

Exemplet nedan läser datafilerna för New York Yellow Taxi 2017 från mappen csv/taxi med ett jokertecken och returnerar det totala biljettbeloppet 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;

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Läsa delmängd av filer i mappen med hjälp av flera filsökvägar

Exemplet nedan läser 2017 NYC Yellow Taxi-datafilerna från mappen csv/taxi med hjälp av 2 filsökvägar först en med fullständig sökväg till filen som innehåller data från januari månad och sekund med jokerteckenläsningsmånaderna november och december som returnerar det totala biljettbeloppet 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;

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Läsa mappar

Sökvägen 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 jokertecken på filnivå som du ser i Läsa 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 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 .

Anteckning

Observera förekomsten av / i slutet av sökvägen i frågan nedan. Den anger en mapp. Om / utelämnas riktas frågan mot 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);

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet 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 i csv-mappen som har namn som börjar med t och slutar med i.

Anteckning

Observera förekomsten av / i slutet av sökvägen i frågan nedan. Den anger en mapp. Om /utelämnas kommer frågan att rikta 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);

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Eftersom du bara har en mapp som matchar kriterierna är frågeresultatet detsamma som Läsa alla filer i mappen.

Bläddra igenom mappar rekursivt

Serverlös SQL-pool kan rekursivt bläddra i 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);

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Flera jokertecken

Du kan använda flera jokertecken på olika sökvägsnivåer. Du kan till exempel utöka föregående fråga för att läsa filer med endast 2017-data, från alla mappar som namn börjar med t och slutar med i.

Anteckning

Observera förekomsten av / i slutet av sökvägen i frågan nedan. Den anger en mapp. Om /utelämnas kommer frågan att rikta 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);

Anteckning

Alla filer som nås med en enda OPENROWSET måste ha samma struktur (t.ex. antalet kolumner och deras datatyper).

Eftersom du bara har en mapp som matchar kriterierna är frågeresultatet detsamma som underuppsättningen Läsa filer i mappen och Läsa alla filer från en specifik mapp. Mer komplexa användningsscenarier med jokertecken beskrivs i Query Parquet-filer.

Nästa steg

Mer information finns i artikeln Frågespecifika filer .