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

  • [アーティクル]

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

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

前提条件

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

フォルダー 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/タクシー フォルダーから 2017 NYC の黄色のタクシー データファイルを読み取ります。1 つ目は、1 月のデータを含むファイルへの完全パスを使用し、2 番目のファイル パスには、月と月を示すワイルドカードを格納します。

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 フォルダー内のすべてのファイルを読み取ります。

Note

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

Note

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

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

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

Note

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

Note

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 年のデータを含むファイルのみを読み取るようにすることができます。

Note

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

次のステップ

詳細については、特定のファイルに対するクエリに関する記事を参照してください。