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

  • Článek

V tomto článku se dozvíte, jak napsat dotaz pomocí bezserverového fondu SQL v Azure Synapse Analytics.

Bezserverový fond SQL podporuje čtení více souborů nebo složek pomocí zástupných znaků, které se podobají zástupným znakům používaným v operačním systému Windows. Existuje však větší flexibilita, protože je povoleno více zástupných znaků.

Požadavky

Prvním krokem je vytvoření databáze , ve které budete spouštět dotazy. Pak inicializujte 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.

Ke sledování ukázkových dotazů použijete složku csv/taxi . Obsahuje data NYC Taxi - Yellow Taxi Trip Records od července 2016 do června 2018. Soubory v csv/taxi jsou pojmenovány po roce a měsíci podle následujícího vzoru: yellow_tripdata_<rok-měsíc><>.csv

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

Následující příklad čte všechny datové soubory NYC Yellow Taxi ze složky csv/taxi a vrací 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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

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

Následující příklad přečte datové soubory 2017 NYC Yellow Taxi ze složky csv/taxi pomocí zástupného znaku a vrátí celkovou částku jízdného podle typu 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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

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

Následující příklad čte datové soubory 2017 NYC Yellow Taxi ze složky csv/taxi pomocí dvou cest k souborům první s úplnou cestou k souboru, který obsahuje data z měsíce leden a druhý se zástupnými znakůýmimi měsíci listopad a prosinec, který vrací celkovou částku jízdného podle typu 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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

Čtení složek

Cesta, kterou zadáte pro OPENROWSET, 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

Ke čtení všech souborů ve složce můžete použít zástupný znak na úrovni souboru, jak je znázorněno v části Čtení všech souborů ve složce. Existuje ale způsob, jak zadávat dotazy na složku a využívat všechny soubory v této složce.

Pokud cesta zadaná v openrowset odkazuje na složku, všechny soubory v této složce se použijí jako zdroj pro váš dotaz. Následující dotaz přečte všechny soubory ve složce csv/taxi .

Poznámka

Všimněte si existence / na konci cesty v následujícím dotazu. Označuje složku. Pokud parametr / vynecháte, bude dotaz místo toho cílit 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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

Č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 přečte všechny soubory ze všech složek umístěných ve složce CSV , které mají názvy začínající t a končící na i.

Poznámka

Všimněte si existence / na konci cesty v následujícím dotazu. Označuje složku. Pokud parametr / vynecháte, dotaz místo toho bude cílit 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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

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

Rekurzivní procházení složek

Bezserverový fond SQL může rekurzivně procházet složky, pokud na konci cesty zadáte /**. Následující dotaz přeč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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

Více zástupných znaků

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

Poznámka

Všimněte si existence / na konci cesty v následujícím dotazu. Označuje složku. Pokud parametr / vynecháte, dotaz místo toho bude cílit na soubory s názvem t*i . Maximální limit je 10 zástupných znaků na dotaz.

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 objektu OPENROWSET, musí mít stejnou strukturu (tj. počet sloupců a jejich datové typy).

Vzhledem k tomu, že máte pouze jednu složku, která odpovídá kritériím, je výsledek dotazu stejný jako Čtení podmnožina souborů ve složce a Čtení všech souborů z konkrétní složky. Složitější scénáře použití zástupných znaků jsou popsané v části Query Parquet files.

Další kroky

Další informace najdete v článku Věnovaném dotazům na soubory .