クエリ フォルダーと複数のファイル

Tip

Microsoft Fabric Data Warehouse は、将来のアーキテクチャ、組み込みの AI、および新機能を備えた、Data Lake 基盤上のエンタープライズ 規模のリレーショナル ウェアハウスです。 データ ウェアハウスを初めて使用する場合は、Fabric Data Warehouseから始めます。 既存の dedicated SQL プール ワークロードは、Fabric にアップグレードして、データ サイエンス、リアルタイム分析、レポートの新機能にアクセスできます。

この記事では、Azure Synapse Analytics のサーバーレス SQL プールを使用してクエリを作成する方法について説明します。

サーバーレス SQL プールでは、Windows で使用されるワイルドカードとよく似たワイルドカードを使用して複数のファイルまたはフォルダーを読み取ることができます。 しかし、複数のワイルドカードを使用できるため、柔軟性がより高くなります。

前提条件

最初の手順として、クエリを実行できるデータベースを作成します。 次に、そのデータベースでセットアップ スクリプトを実行して、オブジェクトを初期化します。 このセットアップ スクリプトにより、これらのサンプルで使用されるデータ ソース、データベース スコープ資格情報、外部ファイル形式が作成されます。

フォルダー csv/taxi を使用してサンプル クエリを実行します。 これには、2016 年 7 月から 2018 年 6 月までの NYC のタクシー - イエロー タクシーの運行記録が含まれています。 csv/taxi のファイルには、次のパターンを使用して、年月に従って名前が付けられます。

yellow_tripdata_<year>-<month>.csv*

フォルダー内のすべてのファイルを読み取る

次の例では、csv/taxi フォルダーから NYC イエロー タクシーのすべてのデータ ファイルを読み取り、年間の合計乗客数および乗車回数を返します。 また、集計関数の使用方法も示します。

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

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

フォルダー内のファイルのサブセットを読み取る

次の例では、ワイルドカードを使用して、csv/taxi フォルダーから NYC イエロー タクシーの 2017 年のデータ ファイルを読み取り、支払いの種類ごとの合計料金を返します。

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;

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

複数のファイルパスを使用してフォルダー内のファイルのサブセットを読み取る

次の例では、2 つのファイル パスを使用して、csv/taxi フォルダーから NYC イエロー タクシーの 2017 年のデータ ファイルを読み取ります。 1 つ目は 1 月のデータを含むファイルへの完全なパスを使用し、2 つ目はワイルドカードを使用して 10 月、11 月、12 月を読み取ります。 パスごとに、支払いタイプごとの合計料金が返されます。

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;

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

フォルダーを読み取る

OPENROWSET 指定するパスは、フォルダーへのパスにすることもできます。 以下のセクションでは、このクエリの種類について説明します。

特定のフォルダーからすべてのファイルを読み取る

「フォルダー内のすべてのファイルを読み取る」で示したように、ファイル レベルのワイルドカードを使用して、フォルダー内のすべてのファイルを読み取ることができます。 しかし、フォルダーに対してクエリを実行し、そのフォルダー内のすべてのファイルを使用する方法があります。

OPENROWSET で指定するパスがフォルダーを指す場合、そのフォルダー内のすべてのファイルが、クエリのソースとして使用されます。 次のクエリは、csv/taxi フォルダー内のすべてのファイルを読み取ります。

注意

クエリで、パスの最後に / があることに注意してください。 これはフォルダーを表します。 / を省略すると、クエリは代わりに 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);

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

複数のフォルダーからすべてのファイルを読み取る

ワイルドカードを使用して複数のフォルダーからファイルを読み取ることができます。 次のクエリでは、csv フォルダー内にある、t で始まり、i で終わる名前を持つすべてのフォルダーからすべてのファイルを読み取ります。

注意

クエリで、パスの最後に / があることに注意してください。 これはフォルダーを表します。 / を省略した場合、クエリは代わりに 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);

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

条件に一致するフォルダーは 1 つだけであるため、クエリの結果は、「フォルダー内のすべてのファイルを読み取る」場合と同じです。

フォルダーを再帰的にスキャンする

パスの末尾に /** を指定すると、サーバーレス SQL プールでフォルダーを再帰的に走査できます。 次のクエリでは、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);

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

複数のワイルドカードを使用する

複数のワイルドカードを異なるパス レベルで使用できます。 たとえば、前述のクエリをエンリッチして、名前が t で始まり、i で終わるすべてのフォルダーから、2017 年のデータを含むファイルのみを読み取るようにすることができます。

注意

クエリで、パスの最後に / があることに注意してください。 これはフォルダーを表します。 / を省略した場合、クエリは代わりに t*i という名前のファイルを対象とします。 クエリあたりのワイルドカードの上限数は 10 個です。

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

注意

1 回の OPENROWSET でアクセスされるファイルはすべて同じ構造 (列数とデータ型) である必要があります。

条件に一致するフォルダーは 1 つだけであるため、クエリの結果は、「フォルダー内のファイルのサブセットを読み取る」場合および「特定のフォルダーからすべてのファイルを読み取る」場合と同じです。 より複雑なワイルドカードの使用シナリオについては、「Parquet ファイルのクエリ」を参照してください。

次のステップ

サーバーレス SQL プール クエリでファイルのメタデータを使用します

  • Last updated on