BigQueryで日付ごとに作られたテーブルを検索する Legacy SQL を Standard SQL に変換する

BigQueryで日付ごとに解析したいデータを入れるときの第一オプションは日付によるテーブルパーティショニングだと思います。しかし、テーブルパーティショニングの日付として扱えるフィールドがデータの中にない場合や、テーブルパーティショニングが公開される以前は table_20181206 のように日付の入ったテーブルを作成することがよく行われていました。

BigQueryではこのようなテーブルを作成すると、従来のコンソール画面では¹UI上で日付のプルダウンが作成され、スキーマとプレフィックスが一致していて日付だけが違うテーブルはあたかも同一テーブルであるかのように扱えます。

しかし、これはあくまでUI上の話で実際には別テーブルなので複数テーブルを扱うクエリを書く必要があります。以前関わっていた仕事では長らく Legacy SQL を使って日付ごとに分割されたテーブルにクエリを書いていましたが、現在では Standard SQL が使えるのでこれを書き換えてみたいと思います。

Legacy SQL と Standard SQL

Legacy SQL

SELECT
  spam
FROM
  TABLE_DATE_RANGE([project-999:dataset.table_], TIMESTAMP('2018-12-01'), TIMESTAMP('2018-12-25'))
WHERE
  egg >= 1

Legacy SQL では TABLE_DATE_RANGE を使って project-999:dataset.table_ とプレフィックスのついたテーブルを検索しています。[] で囲っているのはこれがなければ : がシンタックスエラーになるためです。

Standard SQL

SELECT
  spam
FROM
  `project-999.dataset.table_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20181201' AND '20181225'
AND
  egg >= 1

Standard SQL では TABLE_DATE_RANGE が使えないので _TABLE_SUFFIX を使っています。プロジェクト名・データセット名・テーブル名は . で区切り [] ではなくバッククォートで囲います。

手作業で変換すべきなのか？

未だ Legacy SQL で書かれているクエリは数多く存在していると思うのでそれがすぐに使えなくなる未来は想像したくありませんが、 "Legacy" と名前がついているので今後は Standard SQL を使ったほうが良いのかと思います。どこかのイベントで Google の中の人に「Legacy SQL を Standard SQL に変換するツールを作らないのか」と質問が投げられていましたが、「Legacy SQL は"スタンダードなSQL" には無い機能があり、単純に変換するのは簡単ではなくできたとしても100%ではない」というニュアンスのことを言っていた気がします。なのでそのようなツールがあったとしてもある程度自分で変換する作業が必要かもしれません。

1. 新しいコンソール画面に慣れていないので今どうなっているかわかりません。 ↩