f:id:vasilyjp:20180927090637j:plain

こんにちは、VASILYバックエンドエンジニアの塩崎です。 VASILYでは様々なログデータの分析にBigQueryを使用しています。インデックスについて何も考えなくても良いのが特に便利です。

さて、そんなBigQueryですが、数か月前にStandard SQLという新しい仕様のSQLがサポートされました。 BigQuery 1.11, now with Standard SQL, IAM, and partitioned tables!

VASILYでも徐々にStandard SQLに移行をしているので、使い勝手や従来のSQLからの移行方法についてまとめておきます。

Standard SQLとは

SQL:2011に準拠しつつ、配列や構造体等の構造化データを扱えるように拡張されたSQLです。 Standard SQLの登場によって、以前からあったSQLはLegacy SQLと呼ばれるようになりました。発表された当初はほとんどの機能がβ版でしたが、最近ではDML機能を除いた全てがリリース版になっています。

すぐに移行する必要があるかどうか

現状ではLegacy SQLもStandard SQLもサポートされています。(2016/11/10現在) そのため、Legacy SQLで書かれたクエリがすぐに動かなくなるということはありません。

以下の公式ドキュメントでは緩やかな移行を推奨しています。

Do I have to migrate to standard SQL?

Migration from legacy SQL to standard SQL is recommended but not required.

https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql

Standard SQLの使い方

Standard SQLでクエリを投げるためには、大きく分けて2つの方法があります。クエリを投げる時のオプションで指定する方法と、SQL文中で指定する方法です。

クエリを投げる時のオプションで指定

Web UI

Web UIからクエリを投げる時には以下のようにして、Standard SQLを指定します。

Show Optionをクリックして、オプション設定を開く。

f:id:vasilyjp:20161109145633p:plain

Use Legacy SQLのチェックを外します。

f:id:vasilyjp:20161109145700p:plain

bqコマンド

bqコマンドを使いコマンドラインからクエリを投げる時には--use_legacy_sql=falseオプションを付与することで、Standard SQLを指定します。

$ bq query --use_legacy_sql=false "SELECT ..."

プログラムから呼ぶ場合

プログラムからクエリを呼ぶ場合は各言語のBigQuery Client Libraryのドキュメントを参考にしてください。 https://cloud.google.com/bigquery/docs/reference/libraries

SQL文中で指定

SQL文の文頭に#standardSQLというコメント行を書くことで、Standard SQLを指定します。この方法はWeb UI、bqコマンド、REST APIの全てで使うことができます。

Standard SQLの利点

その場でビューを作れるようになった

WITH句を使うことで、その場でビューをつくることができるようになりました。

#standardSQL
WITH T AS (
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x
)
SELECT * FROM T

複数個のビューをその場で定義することも、自分よりも上で定義されたビューを参照することもできます。

#standardSQL
WITH T1 AS (
  SELECT 1 AS x UNION ALL
  SELECT 2 AS x UNION ALL
  SELECT 3 AS x
), 
T2 AS (
  SELECT x + 1 FROM T1
)

SELECT * FROM T2

複雑な分析をする時に入れ子になったサブクエリを整理する時に使うことができそうです。ビューとして保存することに比べて、一覧性の向上、ビュー数の削減によるデータセットの整理などの利点が考えられます。もちろん、複数の分析で共通して必要な処理は今まで通り、ビューとして保存することが適しています。 WITH句は1箇所からしか使われない処理をまとめる時に使うのが良いと思います。

また、サンプルデータを定義するためにも使うことができます。これ以降の説明でも、WITH句を利用したサンプルデータの定義を用いていきます。

SELECTとFROMの間にサブクエリが書けるようになった

Legacy SQLと比べてサブクエリを書くことができる場所が増えました。

例えば、以下のクエリで、最高気温が20度以上の日の割合を計算することができます。

#standardSQL
WITH T AS (
  SELECT '2016/11/1' AS date, 16 AS max_temperature UNION ALL
  SELECT '2016/11/2' AS date, 12 AS max_temperature UNION ALL
  SELECT '2016/11/3' AS date, 19 AS max_temperature UNION ALL
  SELECT '2016/11/4' AS date, 19 AS max_temperature UNION ALL
  SELECT '2016/11/5' AS date, 20 AS max_temperature UNION ALL
  SELECT '2016/11/6' AS date, 21 AS max_temperature UNION ALL
  SELECT '2016/11/7' AS date, 14 AS max_temperature
)

SELECT
  COUNTIF(max_temperature >= 20) /
    (SELECT COUNT(1) FROM T) AS warn_days_fraction
FROM T

相関サブクエリが使えるようになった

サブクエリの内側からサブクエリの外側のテーブルを参照できるようになりました。従来ではJOINを利用せざるをえなかった処理をシンプルに書くことができそうです。

以下のクエリでは、都市ごとにその都市の平均気温以上の日を選択するクエリを相関サブクエリを用いて書いています。 where t1.city = t2.cityの部分でサブクエリの外側のテーブルt1を参照しています。

#standardSQL
WITH T AS (
  SELECT '2016/11/1' AS date, 16 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/2' AS date, 12 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/3' AS date, 19 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/4' AS date, 19 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/5' AS date, 20 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/6' AS date, 21 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/7' AS date, 14 AS max_temperature, 'Tyokyo' AS city UNION ALL
  SELECT '2016/11/1' AS date,  4 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/2' AS date,  6 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/3' AS date,  4 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/4' AS date,  4 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/5' AS date,  2 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/6' AS date,  4 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/7' AS date,  4 AS max_temperature, 'Sapporo' AS city UNION ALL
  SELECT '2016/11/1' AS date, 28 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/2' AS date, 24 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/3' AS date, 25 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/4' AS date, 26 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/5' AS date, 28 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/6' AS date, 28 AS max_temperature, 'Naha' AS city UNION ALL
  SELECT '2016/11/7' AS date, 28 AS max_temperature, 'Naha' AS city
)
SELECT * FROM T AS t1
  WHERE max_temperature > (
    SELECT AVG(max_temperature) FROM T AS t2
      WHERE t1.city = t2.city GROUP BY city
  )

配列と構造体が使えるようになった

配列と構造体を使うことで非正規化データを扱うこともできます。それぞれについて見ていきましょう。

配列

配列は要素を[]で囲むことで、宣言することができます。

#standardSQL
SELECT [1, 2, 3, 4, 5] AS numbers

配列の要素にアクセスするためには、0オリジンでインデックスを指定するOFFSET関数、1オリジンでインデックスを指定するORDINAL関数を通す必要があります。 numbers[1]といったアクセス方法は出来ません。

#standardSQL
WITH T AS (
  SELECT [1, 2, 3, 4, 5] AS numbers
)
SELECT numbers[OFFSET(1)], numbers[ORDINAL(1)] FROM T

配列を扱う上での注意点が2点あります。 1点目は配列の要素にNULLを含むことはできない点です。

#standardSQL
SELECT [1, 2, 3, NULL] AS numbers # ERROR!!

2点目は配列の配列を作ることはできない点です。

#standardSQL
SELECT [[1, 2], [3, 4]] # ERROR!!

入れ子になった配列のようなデータ構造を作る場合は、この後に紹介する構造体と組み合わせて、配列を要素としてもつ構造体の配列を作る必要があります。

配列の使い方については以下のドキュメントが詳しくまとまっています。 Working with Arrays

構造体

構造体は要素を()で囲むことで宣言することができます。

#standardSQL
SELECT (1, 1.23, 'str')

また、ASと組み合わせることで、各要素に名前をつけることができます。

#standardSQL
SELECT STRUCT(1 AS int, 1.23 AS float, 'str' AS string)

.を使うことで、構造体中の特定の要素にアクセスすることができます。

#standardSQL
SELECT STRUCT(1 AS int, 1.23 AS float, 'str' AS string).int

COUNT(DISTINCT(x))が正確な数を返すようになった

COUNT(DISTINCT(x))の処理がサンプリングされなくなりました。 Legacy SQLのCOUNT(DISTINCT(x))は、Standard SQLではAPPROX_COUNT_DISTINCT(x)に相当します。

TIMESTAMP型の関数にtime zoneを渡せるようになった

TIMESTAMP型を引数に取る多くの関数にオプションとしてtime zoneを渡すことができるようになりました。

例えば、TIMESTAMP型をSTRING型にする関数であるFORMAT_TIMESTAMP関数に対して、以下のようにtime zoneを渡すと日本標準時での現在時刻が得られます。

#standardSQL
select FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', CURRENT_TIMESTAMP, 'Asia/Tokyo')

time zoneをしていするための文字列は2種類あります。 1つ目はUTCからのオフセットを表す(+|-)H[H][:M[M]]形式の文字列です。 2つ目はtz databaseに登録されている名前です。

日本標準時を指定する時には、time zoneに+9:00もしくはAsia/Tokyoを指定すれば良いです。

なお、time zoneを省略するとUTCとみなされます。

時刻型、日付型の種類が増えた

時刻、日付を表す型として、以下の3つの型が追加されました。

DATE
TIME
DATETIME

DATA型は日付のみを保存する型、TIME型は時刻のみを保存する型、DATETIME型は日付と時刻の両方を保存する型です。

DATETIME型もTIMESTAMP型も日付と時刻を保持する型ですが、どのように使い分けたら良いのでしょうか。 BigQueryのドキュメントには以下のように書かれています。

Unlike Timestamps, a DATETIME object does not refer to an absolute instance in time. Instead, it is the civil time, or the time that a user would see on a watch or calendar.

これだけですと、いまいち分かりづらいので具体例を考えます。例として、ユーザーのアクションをログに残すことを考えます。そのログの中には、いつユーザーがアクションを起こしたかを表す時刻情報を入れます。

さて、この時に日本のユーザーとイギリスのユーザが同時にアクションをしたとします。この時刻情報をTIMESTAMP型で保存する場合は、日本で発生したアクションも、イギリスで発生したアクションも同じ時刻がUTCで記録されるべきです。一方DATETIME型で保存する場合は、それぞれの国の標準時で時刻が記録されるべきです。イギリスの標準時刻はUTC+00:00で、日本標準時はUTC+09:00なので、イベントがイギリスと日本で同時に起こった場合は、日本で起こったイベントの方が9時間進んだ時刻で記録されるべきです。

f:id:vasilyjp:20161109145725p:plain

また、サマータイムを導入している場合には、DATETIME型のみ時刻がずれるべきです。

上記のように使い分けをする意図があるため、DATETIME型とTIMESTAMP型の間で暗黙の変換はできなくなっています。

※ 本節で使用した世界地図のイラストはいらすとやさんの素材を利用させていただきました。

UDFが使いやすくなった

User Define Function(UDF)機能自体はLegacy SQLの時からありましたが、Standard SQLではその仕様がシンプルになり、使いやすくなりました。

Legacy SQLでURIデコードを行うUDFを使う時には、以下のように書く必要がありました。

function urlDecode(row, emit) {
  emit({uri: decodeHelper(row.uri)});
}

function decodeHelper(s) {
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
}

bigquery.defineFunction(
  'urlDecode', 
  ['uri'],
  [{name: 'uri', type: 'string'}],
  urlDecode
);

#lagecySQL
SELECT uri AS decoded
FROM
  urlDecode(SELECT '%e3%83%86%e3%82%b9%e3%83%88' AS uri)

行いたい処理はJavaScriptのdecodeURI関数を呼ぶだけなのに、記述量がかなり多いです。入出力される列名の定義や、エラーハンドリングの処理があるためです。また、UDFに入出力される列が変わった時の修正範囲が広範囲です。

Standard SQLでは同様の処理は以下のように書くことができます。

#standardSQL
CREATE TEMPORARY FUNCTION urlDecode(s STRING)
RETURNS STRING
LANGUAGE js AS """
  try {
    return decodeURI(s);
  } catch (ex) {
    return s;
  }
""";

SELECT urlDecode('%e3%83%86%e3%82%b9%e3%83%88') AS decoded

Legacy SQLのUDFと比べるとすっきりとした見栄えになっています。

エラーハンドリングをしなくても良い場合は、さらにすっきりとします。

#standardSQL
CREATE TEMPORARY FUNCTION urlDecode(s STRING)
RETURNS STRING
LANGUAGE js AS "return decodeURI(s);";

SELECT urlDecode('%e3%83%86%e3%82%b9%e3%83%88') AS decoded

Legacy SQLのUDFと比較して、以下の点が変わっています。

SQLとUDFを同じフォームに入力できるようになった
入出力の単位がテーブルからセルになった
それに伴い、入出力される列名の指定が不要になった
関数のシグネチャはCREATE TEMPORARY FUNCTION文で宣言されるため、JavaScriptのコードでは{}の内側だけを書けば良くなった

また、SQLでUDFを作る機能もサポートされました。

#standardSQL
CREATE TEMPORARY FUNCTION ADD_ONE(i INT64) AS (i + 1);

SELECT ADD_ONE(1)

[2016/11/10現在ではβ版] DML機能が使えるようになった

テーブルに対する、INSERT、UPDATE、DELETE文が実行できるようになりました。ですが、制限が多いのでまだまだ実運用は厳しそうな感じがします。

以下のような制限があります。

Quotaが少ない(1日あたり、48 UPDATE/DELETE文)
REQUIRED型を含むテーブルに対して更新処理を行うことができない
暗黙的にトランザクションのCOMMITがされる(ROLLBACKができない)
streaming insertをしているテーブルに対してのUPDATE/DELETEはバッファーが空になるまでできない

Standard SQLの欠点

以上でStandard SQLの利点を色々と紹介しましたが、欠点もあります。それらも合わせて紹介していきます。

Legacy SQLのビューを参照できない

Legacy SQLで作成したビューに対してクエリを投げることができません。 Standard SQLでクエリを投げるためには、それらのビューをStandard SQLの仕様を満たすように書き換える必要があります。

#standardSQL
SELECT * FROM `mydataset.legacy_sql_view` # ERROR!

Legacy SQLで作られたビュー資産を多く持っているグループでは移行作業が大変になりそうです。

Standard SQLで作ったビューをLegacy SQLから参照できない

前節とは逆方向の操作も行うことができません。 Legacy SQLとStandard SQLを混ぜることはできません。

#legacySQL
SELECT * FROM [mydataset.standard_sql_view] # ERROR!

そのため、Legacy SQLのビューが別のビューを参照している場合には、一連のビューを全てStandard SQLに移行する必要があります。

デフォルトではLegacy SQLが選択されてしまう

Web UIやbqコマンドからStandard SQLを使う時には、Standard SQLを有効にするオプションを明示的に設定する必要があります。そのため、Standard SQLの布教活動がややし辛いです。

プロジェクト単位でStandard SQLとLegacy SQLのどちらをデフォルトにするかの機能があると移行をスムーズに行うことができるかと思います。

UDFをビューに保存できない

UDFをビューに保存しようとすると、以下のようなエラーメッセージが表示されてしまいます。

Failed to save view. No support for CREATE TEMPORARY FUNCTION statements inside views

Standard SQLではUDFが作りやすくなったので、ぜひビューにUDFも保存したいです。

Save Viewはできませんが、Save Queryは問題なく行うことができます。 UDF付きのSQLを他人と共有したいときには、とりあえずは、こちらの機能を使うのがいいでしょう。

日本語の資料が少ない

BigQuery Standard SQLでGoogle検索をしても日本語の資料がほとんど見つかりません。公式ドキュメントも一部の章しか翻訳されていません。

現時点で最もまとまっている日本語資料は@ryok0607さんによる、以下のslideです。

SQLおじさん(自称)がBigQueryのStandard SQLを使ってみた

本記事を書く際にも多くのことを参考にさせていただきました。

Legacy SQLからStandard SQLへの移行方法

さて、Standard SQLの利点、欠点について色々と説明しましたが、ここからはLegacy SQLからStandard SQLに移行する方法について説明します。

FROM句の [] を``に、:を.に入れ替え

テーブル名をエスケープするための文字が、 [ ] から``になりました。また、プロジェクト名とデータセット名の間の区切り文字が:から.になりました。

#LegacySQL
SELECT * FROM [bigquery-public-data:samples.shakespeare] LIMIT 1

#standardSQL
SELECT * FROM `bigquery-public-data.samples.shakespeare` LIMIT 1

REQUIREDの列をNULLABLEにする

REQUIREDの列があるテーブルを対象にして、UPDATE/DELETEを行うことができないため、それらの列をNULLABLEにする必要があります。そのテーブルに対する操作がSELECTしかないのであれば、この作業は不要です。

列の属性はWeb UIのTable Detailsから行うことができます。

f:id:vasilyjp:20161109145751p:plain

型名の変換

いくつかの型はStandard SQLで別の名前に変わっています。その対応表を以下に示します。

Legacy SQL	Standard SQL	備考
BOOL	BOOL
INTEGER	INT64
FLOAT	FLOAT64
STRING	STRING
BYTES	BYTES
RECORD	STRUCT
REPEATED	ARRAY
TIMESTAMP	TIMESTAMP
-	DATE	STRING型を使えば、Legacy SQLでも似た機能を使える
-	TIME	STRING型を使えば、Legacy SQLでも似た機能を使える
-	DATETIME	STRING型を使えば、Legacy SQLでも似た機能を使える

DATE型、TIME型、DATETIME型はStandardSQLで新たに登場した型なので、Lagecy SQLには等価な型はありません。似た機能が欲しい場合はSTRING型などで対応しましょう。

また、型のキャストの方法も変更されています。

#legacySQL
SELECT INTEGER('1234')

#standardSQL
SELECT SAFE_CAST('1234' AS INT64)

SELECT句の最後の,を削除

細かな修正ですが、SELECT句の最終要素の後ろの,が許可されなくなりました。

#legacySQL
SELECT
  word,
  corpus,
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1

FROM句の,をUNION ALLに変換

FROM句の中でテーブルを縦方向に結合するために,区切りでテーブルを並べている場合は、それをUNION ALLに変換する必要があります。

#legacySQL
SELECT
  x, y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

#standardSQL
SELECT
  x, y
FROM
  (SELECT 1 AS x, "foo" AS y) UNION ALL
  (SELECT 2 AS x, "bar" AS y);

なお、Standard SQLではFROM句の中での,はJOINの代わりに使用することができます。

#standardSQL
WITH T1 AS (
  (SELECT 1 AS x, "foo" AS y) UNION ALL
  (SELECT 2 AS x, "bar" AS y)
),
T2 AS(
  (SELECT 1 AS x, "hoge" AS z) UNION ALL
  (SELECT 2 AS x, "fuga" AS z)
)
SELECT
  a.x, a.y, b.z
FROM T1 AS a, T2 AS b
WHERE a.x = b.x

テーブルワイルドカード関数の変換

Standard SQLではTABLE_DATE_RANGE関数、TABLE_QUERY関数などのテーブルワイルドカード関数を使うことができません。

その代わりに、テーブル名中に*を入れ、_TABLE_SUFFIXに対する絞り込み条件をWHERE句に書きます。

#legacySQL
SELECT * FROM TABLE_DATE_RANGE([mydataset.mytable_],
                               TIMESTAMP('2016-11-06'),
                               TIMESTAMP('2016-11-08'))

#standardSQL
SELECT * FROM `mydataset.mytable_*`
  WHERE _TABLE_SUFFIX BETWEEN '20161106' AND '20161108'

これ以上に詳しい情報は以下のドキュメントによくまとまってます。

https://cloud.google.com/bigquery/docs/querying-wildcard-tables#migrating_legacy_sql_table_wildcard_functions

配列に対する関数の変換

Legacy SQLでのREPEATED型はStandard SQLではARRAY型に相当します。しかし、それらに対する操作を行うための関数は大きく変化をしました。配列を多用しているクエリをLegacy SQLからStandard SQLに移植する場合は一苦労ありそうです。

変更点が大きく、それだけで1本のブログ記事が書けそうな分量なので、詳細をここで説明することは割愛します。詳細は以下のドキュメントをご覧ください。

https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#differences_in_repeated_field_handling

関数の変換

それ以外の関数も変換が必要なものもあります。弊社内の分析クエリでよく登場した関数についての対応表を以下に示します。

Legacy SQL	Standard SQL
INTEGER(x)	SAFE_CAST(x AS INT64)
NOW()	CURRENT_TIMESTAMP()
REGEXP_MATCH(str, pattern)	REGEXP_CONTAINS(str, pattern)
IS_NULL(x)	x IS NULL
str CONTAINS "foo"	str LIKE '%foo%'
COUNT(DISTINCT x)	APPROX_COUNT_DISTINCT(x)
EXACT_COUNT_DISTINCT(x)	COUNT(DISTINCT x)
JSON_EXTRACT	UDFで対応

最後のJSON_EXTRACT関数はそれに対応するStandard SQLの関数がないので、UDFで対応しました。

#standardSQL
CREATE TEMPORARY FUNCTION JSON_EXTRACT_HOGE(str STRING)
RETURNS STRING
LANGUAGE js AS "return JSON.parse(str).hoge";

SELECT JSON_EXTRACT_HOGE('{"hoge": "fuga"}')

まとめ

BigQuery Standard SQLは発表されてからまだ数ヶ月の新しいもので、一部の機能はβ版のままです。また、日本語の資料がほとんどないため、移行するためのハードルはやや高いと感じます。しかし、Legacy SQL、Standard SQLという名前の対比を考えるとLegacy SQL側のサポートが徐々になくなっていくことを感じさせられます。今はこれらのSQLの移行期間なので、どちらとも読み書きできるようなエンジニアになるのがいいでしょう。

日本語資料が少なく移行を踏みとどまっている方々にとって、この記事が一助になれば幸いです。

VASILYではファッションに関する膨大なデータを保有しています。これらのデータを活用しユーザーに価値のあるプロダクトを届けられるメンバーを募集しています。

ZOZO TECH BLOG

TECH BLOG

Google BigQueryの新機能 Standard SQLまとめ