BigQuery でサポートされている疑似列を調べてみた
こんにちは！エノカワです。
今回は、BigQuery でサポートされている疑似列について実際に試してみたクエリとともに、ご紹介します。
疑似列とは
実際のテーブルやデータセットには存在しないが、クエリの中で参照できる特別な列です。
疑似列を使用することで、データの操作や分析がより柔軟かつ効率的に行えます。
BigQuery ではどういった疑似列がサポートされているのでしょうか？
気になって調べてみたところ、以下の疑似列がサポートされていました。
(2023年3月時点）
_FILE_NAME
ソースファイル名の取得_PARTITIONDATE/_PARTITIONTIME
分割テーブルの分割基準の取得_TABLE_SUFFIX
ワイルドカードテーブルでマッチしたテーブルのサフィックス取得
_FILE_NAME
外部テーブルをクエリする際に元のソースファイル名を取得するために使用されます。
データがファイルロードジョブや外部テーブルからインポートされた場合に利用可能です。
外部テーブルを作成して、ファイル名の取得、ファイルごとの集計、ファイル名でフィルタリングを試してみましょう。
【準備】外部テーブル作成
## 公開データセット shakespeare のデータを GCS にエクスポート
# 全データ
EXPORT DATA OPTIONS (
uri = 'gs://cm_enokawa_work/shakespeare_export/all/*.csv',
format = 'CSV'
) AS (
SELECT
*
FROM
`bigquery-public-data.samples.shakespeare`
);
# word_count = 1 のデータ
EXPORT DATA OPTIONS (
uri = 'gs://cm_enokawa_work/shakespeare_export/wc1/*.csv',
format = 'CSV'
) AS (
SELECT
*
FROM
`bigquery-public-data.samples.shakespeare`
WHERE
word_count = 1
);
## 外部テーブルを作成
CREATE EXTERNAL TABLE work.shakespeare_external
(
word STRING,
word_count INT64,
corpus STRING,
corpus_date INT64
)
OPTIONS (
format="CSV",
uris=["gs://cm_enokawa_work/shakespeare_export/*.csv"]
);
ファイル名を取得
SELECT _FILE_NAME AS file_name, word FROM work.shakespeare_external LIMIT 10
ファイルごとの集計
SELECT _FILE_NAME AS file_name, COUNT(*) AS record_count FROM work.shakespeare_external GROUP BY _FILE_NAME ORDER BY _FILE_NAME
ファイル名でフィルタリング
SELECT _FILE_NAME AS file_name, word FROM work.shakespeare_external WHERE _FILE_NAME LIKE '%/wc1/%' LIMIT 10
_FILE_NAME疑似列でデータの出典となるファイルを参照できるので、データ品質の問題や異常値の原因を特定するといったユースケースで利用できそうです。
下記エントリでは、_FILE_NAME疑似列を使った事例が紹介されていますので、こちらも是非ご参照ください！
_PARTITIONDATE / _PARTITIONTIME
分割テーブルの分割基準となる日付または時刻を取得するために使用されます。
これを使って特定の分割範囲に対するクエリを効率化することができます。
分割テーブルを作成して、取り込み時間でフィルタリング、取り込み時間ごとの集計を試してみましょう。
【準備】分割テーブル作成
先ほどGCS にエクスポートしたshakespeareのデータを取り込むテーブルを作成します。
[パーティションとクラスタの設定] セクションの [パーティショニング] で、[取り込み時間により分割] を選択します。
これにより、取り込み時間パーティショニング分割テーブルとして作成されます。
取り込み時間でフィルタリング
SELECT
_PARTITIONTIME AS partition_timestamp,
word
FROM
work.shakespeare_partition
WHERE
_PARTITIONTIME >= TIMESTAMP("2023-03-26")
LIMIT 10
取り込み時間ごとの集計
SELECT _PARTITIONTIME AS partition_timestamp, COUNT(*) AS record_count FROM work.shakespeare_partition GROUP BY partition_timestamp ORDER BY partition_timestamp
_PARTITIONDATE
_PARTITIONDATE疑似列は、日単位のパーティション分割テーブルで選択できます。
[パーティションとクラスタの設定] セクションの [パーティショニング タイプ] で、[1 日ごと] を選択します。
これにより、日単位のパーティション分割テーブルとして作成されます。
SELECT _PARTITIONDATE AS partition_date, _PARTITIONTIME AS partition_timestamp, word FROM work.shakespeare_partition_day WHERE _PARTITIONDATE = '2023-03-26' LIMIT 10
_PARTITIONDATE / _PARTITIONTIME疑似列で取り込み時間を参照できるので、分析対象期間を絞り込んでクエリのパフォーマンスを向上させるといったユースケースで利用できそうです。
下記エントリでは、_PARTITIONTIME疑似列を使った事例が紹介されていますので、こちらも是非ご参照ください！
_TABLE_SUFFIX
ワイルドカードテーブルを使って複数のテーブルをクエリする際に、マッチしたテーブルのサフィックス（接尾辞）を取得するために使用されます。
サフィックスは、プレフィックスと一緒に使用され、テーブル名を一意に識別します。
シャーディングされたテーブルで、テーブルの範囲を制限、テーブルごとの集計を試してみましょう。
【準備】シャーディングされたテーブル
公開データセットのシャーディングされたテーブルnoaa_gsod.gsodを使用します。
テーブルの範囲を制限
SELECT _TABLE_SUFFIX AS table_suffix, year, mo, da FROM `bigquery-public-data.noaa_gsod.gsod*` WHERE _TABLE_SUFFIX = '2023' LIMIT 10
テーブルごとの集計
SELECT _TABLE_SUFFIX AS year, COUNT(*) AS record_count FROM `bigquery-public-data.noaa_gsod.gsod*` WHERE _TABLE_SUFFIX BETWEEN '2010' AND '2020' GROUP BY year ORDER BY year
_TABLE_SUFFIX疑似列で参照するテーブルの範囲を制限できるので、複数のテーブルにまたがる分析を効率的に実行できそうです。
まとめ
以上、BigQuery でサポートされている疑似列をご紹介しました。
疑似列を活用することで、BigQuery でのデータ操作や分析をより柔軟かつ効率的に行うことが可能になります。
BigQuery で使える疑似列には何があるんだろう？ と気になっていたので調べてみました。
誰かのお役に立てれば幸いです。
ちなみに、今回紹介した疑似列に対するクエリは無料です。どんどん活用していきましょう！
そして、今後もサポートする疑似列が増えてくることに期待です。
![[レポート] Google Agentspace の新機能](https://images.ctfassets.net/ct0aopd36mqt/1dD7b8HkT2sbiJzUIewMTD/e5cdc6f33c4fdd9d798f11a4564612ff/eyecatch_developersio_darktone_1200x630.jpg)
