2021-11-26

BigQuery を使って分析する際の tips (part3)

TL;DR

part3 は STRUCT/ARRAY 型の理解、便利機能、preview 機能について書く
BigQuery は分析に便利な機能がたくさんあって楽しい
BigQuery で分析する際の tips シリーズは一旦これで終了

part3 では part2 よりももっと具体的なクエリを対象として tips をまとめる。

具体的なクエリになるほど公式リファレンスを見れば分かるというものになりがちだが、その中でも個人的にこの辺を押さえておくとよさそうというものについて書くことにする。

tips7: `STRUCT` 型と `ARRAY` 型を使いこなす

part2 のスカラー関数・集計関数・分析関数のところでも触れたが、BigQuery で分析する際に STRUCT 型や ARRAY 型はよく使うので、これらに関してはスムースに読み書きできるようにしておくと役に立つ。

と言っても STRUCT 型の場合にはそれほど気を付けることはないが、NULL の判定は注意が必要。 STRUCT 自体が NULL なのかその中の field が NULL なのかは明確に区別する必要があり、前者は単に struct_column IS NULL のように比較すればよいが、後者は以下の例で分かるように STRUCT リテラルでは比較できないので field アクセスして NULL 比較せねばならない。

SELECT IF((1,"a") = (1,"a"),1,0) AS result
UNION ALL SELECT IF((1,NULL) = (1,NULL),1,0)
UNION ALL SELECT IF((NULL,NULL) = (NULL,NULL),1,0)
UNION ALL SELECT IF(STRUCT(NULL AS c1,NULL AS c2).c1 IS NULL,1,0)

行	result
1	1
2	0
3	0
4	1

ARRAY 型の場合も ARRAY 自体が NULL なのか中身が空の配列なのかを区別する必要があり、前者は単に array_column IS NULL のように比較すればよいが、後者は ARRAY_LENGTH(array_column) = 0 のように指定する必要がある。これらの違いは BigQuery console 上で見ても違いが分からないので、ちゃんと認識しておかないと想定外のミスをしてしまう可能性がある。ちなみに ARRAY 型は配列の要素に NULL を持つことは禁止されている。また、イコールで比較する機能は提供されていない。

WITH
  test AS (
  SELECT [1,2,3] AS array_column
  UNION ALL SELECT []
  UNION ALL SELECT NULL)

SELECT
  array_column,
  array_column IS NULL AS is_null_array_column,
  ARRAY_LENGTH(array_column) = 0 AS is_zero_length_array_column
FROM
  test

行	array_column	is_null_array_column	is_zero_length_array_column
1	1	false	false
	2
	3
2		false	true
3		true	null

次に ARRAY 型を扱う上で最も重要になる UNNEST について。 UNNEST を最もよく使うのは ARRAY を解いて元のソーステーブルに JOIN して使うケースだろう。 UNNEST を使う場合 correlated join について一度は公式リファレンス https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#correlated_join に目を通しておくのがよいと思う。その上で以下のように具体的な例で振る舞いを把握しておくのがよい。

WITH
  sequences AS (
  SELECT
    1 AS id,
    [1,2,3] AS some_numbers
  UNION ALL SELECT
    2 AS id,
    [2,4] AS some_numbers
  UNION ALL SELECT
    3 AS id,
    [3] AS some_numbers
  UNION ALL SELECT
    4 AS id,
    [] AS some_numbers)

SELECT
  id,
  flattened_numbers
FROM
  sequences,UNNEST(sequences.some_numbers) AS flattened_numbers
----- 以下全部同じ振る舞い
--   sequences CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers
--   sequences, sequences.some_numbers AS flattened_numbers
--   sequences INNER JOIN UNNEST(sequences.some_numbers) AS flattened_numbers
----- UNNEST する ARRAY において NULL ARRAY や要素が空のものも残す場合は LEFT JOIN
--   sequences LEFT JOIN UNNEST(sequences.some_numbers) AS flattened_numbers

行	id	flattened_numbers
1	1	1
2	1	2
3	1	3
4	2	2
5	2	4
6	3	3

UNNEST は IN UNNEST(foo) などでも使えてこれは単に配列をバラしてスキャンするだけだが、この correlated join の場合は単に配列をバラしてるだけではなくて特別な振る舞いをするものなので慣れが必要である。これはもうあれこれ考えるのではなくこういうもんだとして慣れて、それを前提知識とするのが一番いいかなと思っている（脳筋だが、それくらいよく使うものなので）。

ちょっと脱線だが、取り出したい要素が一行に対して一つの時はスカラーサブクエリの中で UNNEST を使うこともできる。例えば先ほどの例で ARRAY の中身の MAX だけを取り出したければ以下のように書けるけど、UNNEST とスカラーサブクエリが重なって複雑になって読みにくいので個人的にはあまり好きではない。素直に correlated join して GROUP BY とかする方が好み。

WITH
  sequences AS (
  SELECT
    1 AS id,
    [1,2,3] AS some_numbers
  UNION ALL SELECT
    2 AS id,
    [2,4] AS some_numbers
  UNION ALL SELECT
    3 AS id,
    [3] AS some_numbers
  UNION ALL SELECT
    4 AS id,
    null AS some_numbers)

SELECT
  id,
  (SELECT MAX(flattened_numbers) FROM UNNEST(sequences.some_numbers) AS flattened_numbers) AS max_some_numbers
FROM
  sequences

行	id	max_some_numbers
1	1	3
2	2	4
3	3	3
4	4	null

ARRAY は順序付きリストでかつ OFFSET を使って要素にアクセスしてスカラー関数のように使うこともできるので、特定の位置の要素のみに興味がある場合は UNNEST を使わずに望むクエリを書くこともできる。例えば ARRAY に入っている最初の要素だけ取り出せればいいというのであれば、以下のように書けばよい。ただしこれは対象としている ARRAY が何かしらのルールでちゃんと sort されていることに依存しているので、それが保証されてない限りはちゃんと UNNEST して ORDER BY するなりしてクエリを書くべき。ここでの話とは直接関係しないが、UNNEST すると順序が保証されなくなる https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays ので注意。

WITH
  sequences AS (
  SELECT
    1 AS id,
    [1,2,3] AS some_numbers
  UNION ALL SELECT
    2 AS id,
    [2,4] AS some_numbers
  UNION ALL SELECT
    3 AS id,
    [3] AS some_numbers
  UNION ALL SELECT
    4 AS id,
    null AS some_numbers)

SELECT
  id,
  some_numbers[OFFSET(0)] AS first_elem_some_numbers
FROM
  sequences

行	id	first_elem_some_numbers
1	1	1
2	2	2
3	3	3
4	4	null

最後に GENERATE_ARRAY 系に触れておく。 BigQuery には連続した要素を持つ ARRAY を生成する便利な関数が存在する。公式リファレンスは https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_array この辺り。

例えば、ログを集計して日毎のエラー数を出す時に、エラーがない日があると日付が歯抜けになってしまうのでダッシュボードなどで使う時にちょっと不便だったりすることがあり得る。そういう場合には例えば以下のように GENERATE_DATE_ARRAY 関数を使ってテーブルを生成し、これに対して LEFT JOIN するなどすればよい。

WITH
  base_date AS (
  SELECT
    generated_date
  FROM
    UNNEST(GENERATE_DATE_ARRAY(DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY), CURRENT_DATE())) AS generated_date )

...

ARRAY を使いこなすと分析でだいぶ自由が効くようになるので、バンバン使っていきましょう。

tips8: いくつかの便利機能紹介

雑多な感じにはなるが、クエリを書くときの便利機能をいくつか紹介する。

UDF

公式リファレンスは https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions

UDF は JavaScript が使えるので、分析を BigQuery だけで完結させたいという場合に有効である。そんなに面白い例ではないが、例えば以下のようにフィボナッチ数を計算することができたりする。

CREATE TEMPORARY FUNCTION
  fibonacchi( num INT64 )
  RETURNS INT64
  LANGUAGE js AS "function fibo(n) {return n < 2 ? n : fibo(n - 2) + fibo(n - 1);} return fibo(num);";

WITH num_table AS (
  SELECT num FROM UNNEST(GENERATE_ARRAY(1,10,1)) AS num
)

SELECT
  num,
  fibonacchi(num) AS result
FROM
  num_table

行	num	result
1	1	1
2	2	1
3	3	2
4	4	3
5	5	5
6	6	8
7	7	13
8	8	21
9	9	34
10	10	55

SQL だけだと表現しにくい数理的な演算やちょっとした自然言語処理をしたい場合に JavaScript UDF は便利。 JavaScript のコードが長くなるという場合には GCS にファイルを置いて読み込む https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions#including-javascript-libraries こともできる。（今回のシリーズでは気にしないと言ったが）パフォーマンスは犠牲になる https://cloud.google.com/bigquery/docs/best-practices-performance-compute#avoid_javascript_user-defined_functions ので注意。

現在は JavaScript のみ対応してるが、最近の Google Cloud Next で他の言語への拡張が話題に挙がっていたので期待大。 https://www.youtube.com/watch?v=MY2vBrjA_xg&t=367s

チームで同じような処理を使い回す場合にも UDF は便利だが、その場合は適当に作りまくると管理が大変になりがちなので注意。会社だと Terraform で管理してるが、今回の主題からは離れているので割愛。

JSON functions

BigQuery は JSON データを扱うための便利な関数も提供してくれている。公式リファレンスは https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions

ここで一番言いたいのは、JSON_EXTRACT を使っている人は JSON_QUERY や JSON_VALUE を使うようにしようということだ（公式が推奨してるので）。実際のところこれらの違いは大きくはないが、JSON_EXTRACT では JSONPath で invalid な文字が出てきた時に single quote と [] を使ってエスケープする必要がある。JSON_QUERY などの場合は [] を使わずに double quote を使うことができる。

SELECT JSON_QUERY('{"a.b": {"c": "hello world"}}', '$."a.b"') AS test
UNION ALL SELECT JSON_VALUE('{"a.b": {"c": "hello world"}}', '$."a.b"')
UNION ALL SELECT JSON_QUERY('{"a.b": {"c": "hello world"}}', '$."a.b".c')
UNION ALL SELECT JSON_VALUE('{"a.b": {"c": "hello world"}}', '$."a.b".c')

-- UNION ALL SELECT JSON_EXTRACT('{"a.b": {"c": "hello world"}}', '$."a.b".c') AS test  -- これはエラー
-- UNION ALL SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "hello world"}}', '$."a.b".c') AS test  -- これはエラー

UNION ALL SELECT JSON_EXTRACT('{"a.b": {"c": "hello world"}}', "$['a.b']")
UNION ALL SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "hello world"}}', "$['a.b']")
UNION ALL SELECT JSON_EXTRACT('{"a.b": {"c": "hello world"}}', "$['a.b'].c")
UNION ALL SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "hello world"}}', "$['a.b'].c")

行	test
1	{"c":"hello world"}
2	null
3	"hello world"
4	hello world
5	{"c":"hello world"}
6	null
7	"hello world"
8	hello world

他にも JSON-formatted string から ARRAY を抜き出す JSON_QUERY_ARRAY や JSON_VALUE_ARRAY が存在する。例えば、{"a": [1,2,3,"abc"]} という STRING から ARRAY を抜き出し（各要素は STRING となる）、その各要素を使って concat して一つの STRING にするみたいなのは以下のようにスッと書ける。

SELECT
  ARRAY_TO_STRING(arr, "-") AS test
FROM (SELECT JSON_VALUE_ARRAY('{"a": [1,2,3,"abc"]}','$.a') AS arr)

行	test
1	1-2-3-abc

JSON データを分析する場合は JSON functions の公式リファレンスを一通り眺めてからやるとだいぶ見通しがよくなるだろう。

Scripting statements

Scripting は一度のリクエストで複数個のステートメントを実行できる機能である。公式リファレンスは https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting

その機能からして分析で使いたいものはあまりないのだけど、自分は対象を変えながら使い回しをするクエリ DECLARE を使ったりする。例えば、以下のようなクエリを手元に持っておいて、必要に応じて target_corpus の要素だけを書き換えて実行する。

DECLARE target_corpus ARRAY<STRING> DEFAULT ["sonnets","various","1kinghenryvi","2kinghenryvi","3kinghenryvi"];

SELECT
  word,
  COUNT(1) as cnt
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE 
  corpus IN UNNEST(target_corpus)
GROUP BY 
  word
ORDER BY
  cnt desc, word

行	word	cnt
1	'	5
2	'tis	5
3	A	5
4	Ah	5
5	Alas	5
...	...	...

これは TEMPORARY FUNCTION を使っても同じようなことができるけど、文法的に変数であることが明確なので自分はこっちの方が好み。ただし、複数個のステートメントの実行は BigQuery コンソールじゃないと対応できないことが多い（20211127 時点で DataGrip ではまだサポートされてない）ので、泣く泣く WITH 句を使って書いたりすることもあり、他の環境でもサポートされるといいなぁと常々思っている。

Scripting は自分は使ってるものがあまりないけど、公式リファレンスを眺めると色々できることがあるというので面白い。

tips9: preview 機能を楽しむ

最後は 20211127 時点で preview である機能の中で面白そうなものを眺めてみることにする。

まずは QUALIFY 句。公式リファレンスは https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause

これは分析関数のフィルタリングができるというとても便利な機能で、例えば以下のように使用できる。

SELECT
  word,
  word_count,
  LENGTH(word) / MAX(LENGTH(word)) OVER () AS ratio_word_length
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE 
  TRUE 
QUALIFY 
  ratio_word_length > 0.6
ORDER BY
  ratio_word_length DESC, word

行	word	word_count	ratio_word_length
1	honorificabilitudinitatibus	1	1.0
2	Anthropophaginian	1	0.6296296296296297
3	indistinguishable	1	0.6296296296296297
4	undistinguishable	2	0.6296296296296297

これまでは WITH 句を使って多段で処理したりする必要があったが、それがすっきりと書けるのでこれはぜひ GA になって欲しい。と言いたいところだが、上のクエリには無意味な WHERE TRUE が入ってることにお気づきだろうか。 QUALIFY を使うには WHERE もしくは GROUP BY もしくは HAVING の少なくとも一つが含まれている必要があり、望むクエリを書く際にこれらを使う必要がない場合は、上記のように WHERE TRUE のような虚無を挿入しなければならない、という制約があるためである。そしてこれは an implementation limitation ですと説明されている…悲しい… それでも GA にはなって欲しいが。

次は table sampling で、これは大きい（1 GB 以上）テーブルのランダムなサブセットを取得することができる機能である。公式リファレンスは https://cloud.google.com/bigquery/docs/table-sampling

BigQuery は LIMIT で件数を区切ってもデータスキャン量は変わらないが、以下のように書くことでデータスキャン量が変わっていることが確認できる。デカいテーブルに対して色々クエリを投げながら分析する時に、クエリが固まるまではサンプリングして試せば無駄な課金を減らしたり実行時間を早くしたりできる。

SELECT
  *
FROM
  `bigquery-public-data.samples.wikipedia` TABLESAMPLE SYSTEM (1 PERCENT)
LIMIT 10

（結果は deterministic ではなくキャッシュもされないので実行ごとに変わり得る）

新しい機能好きな人はちょくちょく Release notes https://cloud.google.com/bigquery/docs/release-notes などをチェックしておくと楽しいかもしれない。

まとめ

BigQuery を使って分析する際の tips part1~3 を書いた。part3 は公式リファレンスが一番、みたいな話になってしまった…

part1: 開発環境やデータ連携 https://yoheikikuta.github.io/blog/2021-11-13-bigquery_tips_part1
part2: クエリを書く際に押さえておくとよいこと https://yoheikikuta.github.io/blog/2021-11-21-bigquery_tips_part2
part3: 具体的なクエリの tips （このエントリ）https://yoheikikuta.github.io/blog/2021-11-26-bigquery_tips_part3