サーバーレス SQL プール クエリでファイルのメタデータを使用します
この記事では、メタデータを使用して特定のファイルまたはフォルダーに対してクエリを実行する方法について説明します。 サーバーレス SQL プールは、複数のファイルとフォルダーに対応できます。 詳細については、「フォルダーと複数のファイルに対してクエリを実行する」を参照してください。
場合によっては、どのファイルまたはフォルダーのソースが、結果セット内の特定の行に関連付けられているかを知る必要がある可能性があります。
filepath 関数と filename 関数を使用して、結果セット内のファイル名またはパスを返したり、ファイル名またはフォルダー パスに基づいてデータをフィルター処理したりできます。 これらの関数については、「filename 関数」と「filepath 関数」で説明されています。
次のセクションでは、簡単な説明とコード サンプルを提供します。
前提条件
最初の手順は、ストレージ アカウントを参照するデータ ソースを使用してデータベースを作成することです。 次に、そのデータベースでセットアップ スクリプトを実行して、オブジェクトを初期化します。 このセットアップ スクリプトにより、この記事のサンプルで使用されるデータ ソース、データベース スコープの資格情報、外部ファイル形式が作成されます。
関数
Filename
filename 関数からは、行の生成元のファイル名が返されます。
次のサンプルでは、2017 年 9 月の NYC イエロー タクシーのデータ ファイルを読み取り、ファイルごとの乗車数を返します。 クエリの OPENROWSET 部分では、読み取るファイルを指定します。
SELECT
nyc.filename() AS [filename]
,COUNT_BIG(*) AS [rows]
FROM
OPENROWSET(
BULK 'parquet/taxi/year=2017/month=9/*.parquet',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT='PARQUET'
) nyc
GROUP BY nyc.filename();
次の例では、WHERE 句で filename() を使用して、読み取るファイルをフィルター処理する方法を示します。 クエリの OPENROWSET 部分のフォルダー全体にアクセスし、WHERE 句内のファイルをフィルター処理します。
結果は前の例と同じになります。
SELECT
r.filename() AS [filename]
,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
FIRSTROW = 2)
WITH (C1 varchar(200) ) AS [r]
WHERE
r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
r.filename()
ORDER BY
[filename];
Filepath
filepath 関数からは、完全なパスまたは部分的なパスが返されます。
- パラメーターを指定せずに呼び出した場合、行の生成元である完全なファイル パスが返されます。
OPENROWSETでDATA_SOURCEが使用されると、DATA_SOURCEに対する相対パスが返されます。 - パラメーターを指定して呼び出すと、パラメーターで指定した位置にあるワイルドカードと一致するパスの一部が返されます。 たとえば、パラメーター値 1 の場合は、最初のワイルドカードと一致するパスの一部が返されます。
次のサンプルでは、2017 年の過去 3 か月間について、NYC イエロー タクシーのデータ ファイルが読み取られます。 ファイル パスごとの乗車数が返されます。 クエリの OPENROWSET 部分では、読み取るファイルを指定します。
SELECT
r.filepath() AS filepath
,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id INT
) AS [r]
GROUP BY
r.filepath()
ORDER BY
filepath;
次の例では、WHERE 句で filepath() を使用して、読み取るファイルをフィルター処理する方法を示します。
クエリの OPENROWSET 部分にワイルドカードを使用し、WHERE 句内のファイルをフィルター処理することができます。 結果は前の例と同じになります。
SELECT
r.filepath() AS filepath
,r.filepath(1) AS [year]
,r.filepath(2) AS [month]
,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
BULK 'csv/taxi/yellow_tripdata_*-*.csv',
DATA_SOURCE = 'SqlOnDemandDemo',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
FIRSTROW = 2
)
WITH (
vendor_id INT
) AS [r]
WHERE
r.filepath(1) IN ('2017')
AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
r.filepath()
,r.filepath(1)
,r.filepath(2)
ORDER BY
filepath;