Dotazování složek a několika souborů

V tomto článku se naučíte psát dotaz pomocí bezserverového fondu SQL ve službě Azure Synapse Analytics.

Bezserverový fond SQL podporuje čtení více souborů nebo složek pomocí zástupných znaků, které jsou podobné zástupným znakům používaným ve Windows. Větší flexibilita je ale k dispozici, protože je povoleno více zástupných znaků.

Požadavky

Prvním krokem je vytvoření databáze , ve které můžete spouštět dotazy. Potom inicializovat objekty spuštěním instalačního skriptu v této databázi. Tento instalační skript vytvoří zdroje dat, přihlašovací údaje v oboru databáze a formáty externích souborů, které se používají v těchto ukázkách.

Pomocí složky CSV/taxi postupujte podle ukázkových dotazů. Obsahuje data z NYC Taxi - Yellow Taxi Records od července 2016 do června 2018. Soubory ve formátu CSV/taxi jsou pojmenovány po roce a měsíci pomocí následujícího vzoru:

yellow_tripdata_<year>-<month>.csv*

Čtení všech souborů ve složce

Následující příklad přečte všechny datové soubory NYC Yellow Taxi ze složky CSV/taxi a vrátí celkový počet cestujících a jízd za rok. Zobrazuje také použití agregačních funkcí.

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

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Čtení podmnožina souborů ve složce

Následující příklad načte datové soubory NYC Yellow Taxi ze složky CSV/taxi pomocí zástupného znaku a vrátí celkovou částku jízdného na typ platby.

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;

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Čtení podmnožina souborů ve složce pomocí více cest k souborům

Následující příklad přečte datové soubory NYC Yellow Taxi ze složky CSV/taxi pomocí dvou cest k souborům. První používá úplnou cestu k souboru obsahujícímu data z měsíce leden a druhý používá zástupný znak ke čtení měsíců říjen, listopad a prosinec. Pro každou cestu se vrátí celková částka jízdného na typ platby.

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;

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Čtení složek

Cesta, kterou OPENROWSET zadáte, může být také cesta ke složce. Následující části obsahují tyto typy dotazů.

Čtení všech souborů z konkrétní složky

Všechny soubory ve složce můžete číst pomocí zástupné dokumentace na úrovni souboru, jak je znázorněno ve složce Číst všechny soubory. Existuje ale způsob, jak zadat dotaz na složku a využívat všechny soubory v této složce.

Pokud cesta uvedená v OPENROWSET bodech do složky, všechny soubory v této složce se použijí jako zdroj pro váš dotaz. Následující dotaz načte všechny soubory ve složce csv/taxi .

Poznámka:

Všimněte si existence cesty / na konci cesty v dotazu. Označuje složku. / Pokud tento parametr vynecháte, dotaz místo toho cílí na soubor s názvem 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);

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Čtení všech souborů z více složek

Soubory z více složek je možné číst pomocí zástupné ikony. Následující dotaz načte všechny soubory ze všech složek umístěných ve složce CSV , které mají názvy začínající písmenem t a končí na i.

Poznámka:

Všimněte si existence cesty / na konci cesty v dotazu. Označuje složku. / Pokud tento parametr vynecháte, cílí místo toho na soubory s názvem 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);

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Vzhledem k tomu, že máte jenom jednu složku, která odpovídá kritériím, výsledek dotazu je stejný jako čtení všech souborů ve složce.

Rekurzivní procházení složek

Bezserverový fond SQL může rekurzivně procházet složky, pokud zadáte /** na konci cesty. Následující dotaz načte všechny soubory ze všech složek a podsložek umístěných ve složce 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);

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Použití více zástupných znaků

Na různých úrovních cest můžete použít více zástupných znaků. Můžete například rozšířit předchozí dotaz na čtení souborů pouze s daty 2017 ze všech složek, kde názvy začínají t a končí na i.

Poznámka:

Všimněte si existence cesty / na konci cesty v dotazu. Označuje složku. / Pokud tento parametr vynecháte, cílí místo toho na soubory s názvem t*i. Pro každý dotaz platí maximální limit 10 zástupných znaků.

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

Poznámka:

Všechny soubory, ke kterým se přistupuje pomocí jediného souboru OPENROWSET , musí mít stejnou strukturu (počet sloupců a jejich datových typů).

Vzhledem k tomu, že máte jenom jednu složku, která odpovídá kritériím, výsledek dotazu je stejný jako podmnožina souborů ve složce a číst všechny soubory z konkrétní složky. Složitější scénáře použití zástupných znaků najdete v tématu Dotazování souborů Parquet.

Další krok

Používání souborových metadat v dotazech bezserverového fondu SQL