コード日進月歩

しんくうの技術的な小話、メモ、つれづれ、など

BigQueryで_TABLE_SUFFIXを適用して複数のテーブルを取る

BigQuery

別のことを書きたかったが前提の話題としてまずは切り出して書く。

前提条件

  • STANDARD SQLです（記載タイミングでレガシーのほうを使っているほうが稀かなと思いますが念の為）

使い所とやり方

BigQueryには同じテーブルでもテーブル名の末尾に日付をつけて保存をするという手法（シャーディング）がある。

誰でも見れるサンプルデータで例えると、 bigquery-public-dataプロジェクトの google_analytics_sample　データセットに ga_sessions_ というテーブルがあるが、これは日付ごとに保存されていて同じカラム構成だが ga_sessions_20160801 , ga_sessions_20160802 , ga_sessions_20160803 ... のように連々と日付別のテーブルが作られている。

ログ系のテーブルでこの手法が使われるのだが、例えば「2016年の8月にfullVisitorIdが0001363886612345162だったログのdateがほしい」といったときに

SELECT date FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
WHERE fullVisitorId = "0001363886612345162"

のようなクエリ ga_sessions_20160801 の部分を変えて8月の日付分実行する必要が出てくるのでかなり大変。

そこで利用するのが _TABLE_SUFFIX で、 * を使って複数のテーブルにまたいでクエリを実行できる。以下例

SELECT date FROM `bigquery-public-data.google_analytics_sample.ga_sessions_2016*`
WHERE fullVisitorId = "0001363886612345162"
AND _TABLE_SUFFIX BETWEEN "0801" AND "0831"

上記の例のようにテーブル名で可変に指定したい部分に * を指定して、そこに当てはめたい文字をWHERE句を使って設定する。上記の場合は BETWEEN を使って指定している。もちろん _TABLE_SUFFIX = "0803" のような書き方も可能。

注意点

BigQueryではこのような日付別テーブルでデータを分ける方法の他にMySQLなどにもあるパーティションテーブルの方法も用意されており、パフォーマンス観点ではパーティションテーブルのほうが上なので、使い所には気をつける。

参考URL