[BigQuery] 外部テーブルでファイル名を取得する方法

はじめに

データアナリティクス事業本部でGoogle Cloudのデータエンジニアをしています、はんざわです。
外部テーブルをGCSから読み込んだ際にどのレコードがどのファイルから読み込まれたか特定したいケースがありました。
今回はその時に調べた方法を記事にまとめようと思います。

結論

外部テーブルから参照する際に_FILE_NAMEをSELECT句で指定するとファイル名を取得することができます。

https://cloud.google.com/bigquery/docs/external-data-cloud-storage?hl=ja#query_the_file_name_pseudo_column

実際に検証してみます。

準備

1. データの準備

データはBigQueryの一般公開データセットを利用します。その中でもサンプルテーブルにあるgithub_nestedを利用します。

https://cloud.google.com/bigquery/public-data?hl=ja#sample_tables

2. 外部テーブル用のGCSを作成

下記コマンドで外部テーブル用のバケットを作成。

gcloud storage buckets create gs://github-nested-external-table \
    --location=asia-northeast1

3. データをGCSに転送する

下記クエリでデータをGCSに転送。

EXPORT DATA
  OPTIONS (
    uri = 'gs://github-nested-external-table/github-nested-*.json',
    format = 'JSON')
AS (
  SELECT
    *
  FROM
    `bigquery-public-data.samples.github_nested`
)

GCSにファイルが転送されていることを確認。

hanzawa_yuya@cloudshell:~ (hanzawa-yuya)$ gcloud storage ls gs://github-nested-external-table/
gs://github-nested-external-table/github-nested-000000000000.json
gs://github-nested-external-table/github-nested-000000000001.json
gs://github-nested-external-table/github-nested-000000000002.json
gs://github-nested-external-table/github-nested-000000000003.json
gs://github-nested-external-table/github-nested-000000000004.json
gs://github-nested-external-table/github-nested-000000000005.json
gs://github-nested-external-table/github-nested-000000000006.json
gs://github-nested-external-table/github-nested-000000000007.json
gs://github-nested-external-table/github-nested-000000000008.json
gs://github-nested-external-table/github-nested-000000000009.json
gs://github-nested-external-table/github-nested-000000000010.json
gs://github-nested-external-table/github-nested-000000000011.json
gs://github-nested-external-table/github-nested-000000000012.json
gs://github-nested-external-table/github-nested-000000000013.json
gs://github-nested-external-table/github-nested-000000000014.json
gs://github-nested-external-table/github-nested-000000000015.json
gs://github-nested-external-table/github-nested-000000000016.json
gs://github-nested-external-table/github-nested-000000000017.json
gs://github-nested-external-table/github-nested-000000000018.json
gs://github-nested-external-table/github-nested-000000000019.json
gs://github-nested-external-table/github-nested-000000000020.json

4. 外部テーブルを作成

下記クエリで外部テーブルを作成。データを読み込めることも確認済み。

CREATE EXTERNAL TABLE `hanzawa-yuya.external_table.github_nested`
(
    repository JSON,
    actor_attributes JSON,
    created_at STRING,
    public BOOLEAN,
    actor STRING,
    payload JSON,
    url STRING,
    type STRING
)
OPTIONS(
    format="NEWLINE_DELIMITED_JSON",
    uris=["gs://github-nested-external-table/*"]
);

5. ファイル名を取得する

前述した通り、ファイル名は_FILE_NAMEで取得することができます。

SELECT
  _FILE_NAME AS file_name,
  COUNT(*) AS cnt
FROM
  `external_table.github_nested`
GROUP BY 1

まとめ

外部テーブルでファイル名を取得する方法を紹介しました。
ファイル名に時間の情報など何らかの情報が含まれている場合、正規表現等で出力し、活用できるデータの幅を広げることができると思います。