唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryのクエリで定数を定義して複数箇所で使いたい

BigQuery

BigQueryでクエリを書くときに、同じ日付や倍率、円周率などの定数をクエリの複数箇所で使いたい時があります
単純に複数箇所に書くと修正するときに大変なので、共通のものを複数箇所で使いまわす方法を調べてみました

UDF(ユーザー定義関数)による方法

定数を返す関数を定義して使う方法が一番簡単です

Standard SQLのUDFは以下のような形式で書けます
CREATE TEMPORARY FUNCTION 関数名(引数名 型, ...) AS (使いたいSQL);
これを使うと以下のように定数として使うことができて、修正したいときは関数の定義だけを変えればよくなります

#StandardSQL
CREATE TEMPORARY FUNCTION RATE1() AS (0.01);
CREATE TEMPORARY FUNCTION PI() AS (ACOS(-1));

SELECT
  i, i * RATE1(), PI()
FROM UNNEST(GENERATE_ARRAY(1, 100)) AS i

WITHで適当なテーブルを作ってCROSS JOINする方法

こっちの方法だとCROSS JOINをしないといけないので少しコードがわかりづらくなります

#StandardSQL
WITH const AS (
  SELECT
    0.01 AS RATE1
    , ACOS(-1) AS PI
  )

SELECT
  i, i * RATE1, PI
FROM const, UNNEST(GENERATE_ARRAY(1, 100)) AS i

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

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

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

以下目次

個人的に便利になったと思うのは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()みたいなこともできるようになってます
更にサブクエリが外側の値に依存している相関サブクエリも書けるようになりました

日付の型

Legacy SQLではTIMESTAMP型しかなかったのですが、Standard SQLではTIME型、DATETIME型、DATE型が加わりました
TIMESTAMP型はUTCの標準時の日時を表していて、その他はローカルの日時を表しています

タイムゾーン

Standard SQLではタイムゾーンが使えるようになったので以下のように書けるようになりました(Legacy SQLだと日本時間との間の変換で9時間ずらすことを考えないといけなかった)

  • TIMESTAMP('2017-06-24 00:00:00', 'Asia/Tokyo')
  • TIMESTAMP('2017-06-24 00:00:00+09')

日時などを取り出すときも以下のようにタイムゾーンを指定すれば9時間ずらすことをあまり考えずに日時を扱えます

  • EXTRACT(MONTH FROM timestamp AT TIME ZONE 'Asia/Tokyo')
  • DATE(timestamp, 'Asia/Tokyo')
  • STRING(timestamp, 'Asia/Tokyo')

キャスト

Legacy SQLでは以下のような感じでした

  • FROAT('2.0')
  • INTEGER(1.5)

Standard SQLではCAST関数などを使います

  • CAST('2.0' AS FLOAT64)
  • CAST(1.5 AS INT64)

ちなみにStandard SQLの浮動小数のキャストCAST(1.5 AS INT64)は切り捨てではなく四捨五入されるので注意(-1.5の場合は-2)

UDF

このブログで何回か触れていますが、JavaScriptやSQLで関数を定義して呼べるので便利です
Legacy SQLでもStandard SQLでも使えますが、Standard SQLになってちょっと使いやすくなりました
BigQueryで配列に添字をつける(複数の配列をzipする) - 唯物是真 @Scaled_Wurm
BigQueryで単語分割がしたい - 唯物是真 @Scaled_Wurm
BigQueryのWebコンソールでJSONを整形して出力(pretty print) - 唯物是真 @Scaled_Wurm