BigQueryのStandard SQLの便利な機能とLegacy SQLからの変更点をいくつか紹介

勉強会で発表したので内容を適当にまとめてブログ記事にしておく

いつの間にかリファレンスが日本語化されているので、全部一読したい人はそちらを読んだほうがよいかも
SQL Reference | BigQuery Documentation | Google Cloud Platform
ちなみに日本語版のリファレンスだと最近の変更点が載っていない場合があるので、最新の情報を知りたい場合は英語版を見た方がよいです(URLに?hl=enを足してアクセスすればよい(？))

以下目次

BigQueryで使える2つのSQL
Standard SQLを使うべき？
便利な機能や変更点

個人的に便利になったと思うのはJOINの条件制限の緩和、WITH、サブクエリを使える場所の制限の緩和、タイムゾーンが使えるようになったことです

BigQueryで使える2つのSQL

BigQueryでテーブルのデータを取得するときには以下の2つのSQLが使えます

Legacy SQL: 元々BigQueryで使えたSQLで、Legacyという名前ですが今でもデフォルトはこれです
Standard SQL: SQL標準の仕様に沿っているらしい新しく使えるようになったSQLです。Standard SQLを使いたいときはオプションを指定したり、Webのコンソールの場合は1行目に#standardSQL的な行を書けばよいです

f:id:sucrose:20170625235506p:plain

Standard SQLを使うべき？

Standard SQLはLegacy SQLと比べるとこんな人に向いています

サブクエリやJOINを駆使した複雑なクエリを書く
他のDBでSQLに触ったことがあるがBigQueryのLegacy SQLには触ったことがない
ドキュメントが比較的詳細な方を使いたい(Legacy SQLではLIKEやOFFSETが使えますがドキュメントには説明を見つけられませんでした)
新しい機能を使うのが好き

また以下のQiitaの記事によると「Standard SQLの方が高速で、新機能も追加されていく」ということらしいです
qiita.com

便利な機能や変更点

以下適当に列挙していきます
スライドからコピペしてきたので、ものによっては動かないかも(？)

テーブル名の参照

普通のSQLっぽくなりました
[]や``は省略できるので囲まずにプロジェクト名も省略すれば両方に対応できます

Legacy SQL	Standard SQL
`[プロジェクト名:データセット名.テーブル名]`	`プロジェクト名.データセット名.テーブル名`

`COUNT(DISTINCT value)`

Legacy SQL	Standard SQL
`EXACT_COUNT_DISTINCT(value)`	`COUNT(DISTINCT value`

Legacy SQLのCOUNT(DISTINCT value)は統計的な予測値を返すので、正確な値が欲しいときはEXACT_COUNT_DISTINCT(value)を使わないといけないという紛らわしさがあったのですがStandard SQLで解消されました

`SELECT テーブル名.*`

JOINしたときにSELECT テーブル名.*で特定のテーブル名のカラムだけ取ってこれます

`SELECT * EXCEPT (カラム名)`

SELECT * EXCEPT (カラム名)で特定のカラムだけ除外してその他のカラムを取得できます

複数のREPEATED型のカラムを持つテーブルの`SELECT *`

またSELECT *をするテーブルがREPEATED型(ARRAY型)のカラムを複数持っているとLegacy SQLではエラーになってクエリを実行できなかったのですが、Standard SQLでは実行できるようになりました

`SELECT`のカラム名などの後`FROM`の前の`,`の禁止

使いづらくなった点としてはStandard SQLではSELECTのカラム名などの後FROMの前に,があるとエラーになるようになってしまいました

`JOIN`したテーブルのカラムを`SELECT`した時に出力されるカラム名の変更

またSELECT テーブル名.dateのようなカラムをSELECTした場合、Legacy SQLでは結果としてテーブル名_dateというカラム名に解釈されていたのですが、Standard SQLではdateと解釈されてしまうようになったので、同名のカラムを複数SELECTする場合には明示的に別のエイリアスを指定しないといけなくなりました

`JOIN`の条件にだいたいなんでも書けるようになった

Legacy SQLだと以下のようにカラム名の一致の条件しか書けなかった

ON テーブルの別名1.id = テーブルの別名2.id

Standard SQLだとだいたいなんでも書けるようになっています

ON テーブルの別名1.id = テーブルの別名2.id
ON テーブル名1.id = テーブル名2.id
USING (id)
ON テーブルの別名1.id < テーブルの別名2.id
ON テーブル名1.id + 100 = テーブル名2.id

複数のテーブルを`UNION ALL`するときの書き方

Legacy SQLだとテーブル名を,でつなげるだけで書けます

FROM テーブル1, テーブル2

Standard SQLだとちゃんとUNION ALLを書かないといけなくなってしまいました

FROM (SELECT * FROM テーブル1 UNION ALL SELECT * FROM テーブル2)

日付ごとに分けられているテーブルを日付の範囲を指定して取ってくる

BigQueryではテーブルを日付ごとに分けるのがよく行われている。テーブル名は「テーブル名の先頭部分+日付」の形式にするのが多い
Legacy SQLだとTABLE_DATE_RANGE関数などを使う
FROM TABLE_DATE_RANGE([テーブル名の先頭部分], TIMESTAMP('2017-06-23'),TIMESTAMP('2017-06-24'))
Standard SQLだと*で指定した部分が_TABLE_SUFFIXに入るのでWHEREで条件を指定できる
FROM `テーブル名のプレフィクス*` WHERE _TABLE_SUFFIX BETWEEN '20170623' AND '20170624'

`WITH`

Standard SQLではWITHを使ってサブクエリに名前を付けて使いまわすことができます
これを使うことで複雑なクエリの場合に、何層もサブクエリを使っても多少わかりやすくなったり、共通部分をWITHでまとめたりできます
ちなみにBigQueryのWITHでは引数を与えたり再帰はできないです

↓あまり意味のないWITHを使ったクエリの例

#standardSQL
WITH data AS (SELECT 1 AS x, 2 AS y UNION ALL SELECT 3 AS x, 4 AS y)

SELECT
  SUM(x) AS sum_x,
  SUM(y) AS sum_y
FROM data

サブクエリ

Legacy SQLではFROMの中にしかサブクエリが書けませんでしたが、Standard SQLではいろいろなところに書けるようになりました
そのおかげでORDER BY RAND()みたいなこともできるようになってます
更にサブクエリが外側の値に依存している相関サブクエリも書けるようになりました