Google BigQuery の SQL文tips

Google BigQueryとはおおざっぱに言うと、大量のテーブルデータに対して高速にSQLを実行できるフルマネージドサービスである。主にアクセスログや、テーブルデータのように、十分に大きいデータソースを持っている企業にとっては安価で、すぐにSQLを実行することができるため、導入している企業も多い*1。そのため、企業のログデータ解析をするとなると、まず選択肢に上がるのはこれか、Treasure Data などになるのではないだろか。

BigQueryを実行するにあたっては、SQL文を記述する必要がある。単純にテーブルデータを呼び出すだけの利用目的であれば、RDBMS バックエンドのjsonサーバーを実装するときに書くような、Web エンジニアが普段書くSELECT文で十分なケースも多い。しかし実際には、テーブルを跨いだ集計が必要だったりすると、クエリは長くなってしまい、平気で数百行の SQLになってしまうこともしばしばあある。こうしたSQL文は、初見では読み解くのが難しかったり、フルスクラッチで一から組み立てるのが難しかったりする。

ここでは、StandardSQL の文法のもとで、筆者がBigQueryでSQL文を書く中で気づいた、お役立ちtipsをいくつか紹介したい。

具体例

1. with句を利用してサブクエリをシーケンシャルに記述する

例えば、2つのテーブルA,Bがあって、それぞれに対して絞り込んだものを最終的にJOINしたい、というような場合では、

テーブルAを絞り込んでできたA'
テーブルBを絞り込んでできたB'
A'とB'をジョインしてできたC

のような手順でクエリを構成したい。こうしたものを記述するときに、よくあるのはジョインしたいテーブルの情報を、サブクエリで書き下す場合である。

SELECT * FROM `log` INNER JOIN ( SELECT * FROM `users` WHERE users.id < 1000 ) AS master_users ON master_users.id = log.user_id ;

だが、これが複雑に入り組んでくると、クエリの全貌をとらえるのが難しくなる。そこで、サブクエリの変種であるWITH句を利用して、

WITH master AS (
  SELECT * FROM `log` 
  WHERE log.id < 1000
), master_users AS (
  SELECT * FROM `users` 
  WHERE users.id < 1000
) 
SELECT * FROM `log` 
INNER JOIN master_users ON master_users.id = log.user_id ;

と書くと、WITH句によってフローをシーケンシャルに記述することができるので、上から順に読んでいくとどういう流れで処理しようとしているのか、より分かりやすく表現することができる（はず）。

2. 何度も使い回したい数値を関数として使い回したい

SQL文内で関数を定義することもできる。

CREATE TEMPORARY FUNCTION max_user_count() AS ('1000');

これは例えば、先ほどのwith句を利用するような長大なクエリの中で、共通の条件で絞り込みたいといった場合に応用ができる。

3. 1ずらしの連続値が入っているカラムを、joinによってeach_consする

たとえばこんなテーブルを考えてみよう。

id	group_id	seq_id
1	1	1
2	1	2
3	1	3
4	2	1
5	2	2

このテーブルでは、全体に対してidが付与されているほか、複数のレコードにわたって共通のgroup_idが付与されており、そのgroup_id内でシーケンシャルに増加するidが割り振られている。このとき、同じgroup_idの中で、連続するseq_idのあいだで、どのような変化が起きているか、という1次のマルコフ性を考慮した解析を行いたいということがある。ruby でいうところの、each_cons に相当するような処理を、BigQueryで記述したいということである。

このとき、seq_idを1ずらしたテーブルを作って、seq_idでjoinすれば良い。具体的には、join前のテーブルをwith句で取ってくる時に、seq_idをデクリメント（ないしインクリメント）して取ってくる。そして、その後にseq_idと、group_idが一致するものをマージするというSQL文になる。

WITH master_1 AS (
  SELECT
    id,
    group_id
    seq_id
    target
  FROM
    `sample.log`
, master_2 AS (
  SELECT
    id,
    group_id
    seq_id - 1 as seq_id, 
    target
  FROM
    `sample.log`
) SELECT DISTINCT 
   master_1.group_id
   master_1.seq_id
   master_1.target AS target_1
   master_2.target AS target_2
  FROM
    master_1
  LEFT JOIN
    master_2
  ON
    master_1.group_id = master_2.group_id AND master_1.seq_id = master2_.seq_id

たとえば上記のようにすることで、target カラムが、seq_idの前後でどう変化したかをみることができるようになる。

4. case whenを利用して一致したら1、不一致なら0を返す

SELECT CASE WHEN type = 'User' THEN 1 ELSE 0 FROM `users`

これを応用して、一致する要素が何件あるかを集計することも可能だ。

SELECT SUM(CASE WHEN type = 'User' THEN 1 ELSE 0)  FROM `users`

5. "&=" で結合されたパラメータを別々の行に分ける

URLをカラムに直接格納するとき、しばしば、/query?q=param1&q=param2 のように、アンドで結んだパラメータが入っていることがある。これを、それぞれのクエリ文字列に分けて取り出して解析をしたいというとき、

WITH log AS (
  SELECT * ,
  REGEXP_EXTRACT_ALL(parameters, r'(?:^|&)q=(\d+)') as params
  FROM `log`),
log_param AS (SELECT
  *
FROM  log
CROSS JOIN
  UNNEST(log.params) AS param)

上記のように書くことで、param というカラムにもともと複数入っていた複数のパラメータが展開されて、それぞれ1つずつパラメータが別々のレコードに格納されるようになる。これを下流の解析で用いることで、パラメータの文字列ごとの集計などが可能になる。

*1:学生の頃には大金でとても払えないし、そもそもBigQueryが必要とされるほどのデータを集めること自体が難しいので、全く使うことはなかったが。

備忘録 blog

Docker/Machine Learning/Linux