Standard SQLを使い込む

Legacy SQLをやめて、Standard SQLを使いましょう。いくつかの理由があります。

with句が可読性を上げる
SQLのユーザ定義関数が便利
配列の扱いが「Standard」
- cross join unnest(tbl.array_col)
日付を扱う関数の見通しがよい
- {format,parse}_{timestamp,date,datetime}(format, value)

with句

以下、よく使うものを集めたテンプレートです。

#standardsql

with config as (
select
  '20171201' as start_yyyymmdd
, '20171225' as end_yyyymmdd
-- その他、変更されそうなパラメータ
)

, accesses as (
select
  user_id
, access_at
, url
from
  `service.access_log*` -- 実際のテーブルとは異なります
  cross join config
where
  _table_suffix between start_yyyymmdd and end_yyyymmdd
)

/*
  その他色々...
*/

, main (
select
  ...
)
select * from main

/*
-- 出力したデータの受入テスト
, check as (
select
  count(*) as num_row
, sum(case when ... then 1 end) as num_xxx_expected_zero
from
  main
)
select * from check
*/

「その他色々」のところに任意個の, name as (select ...)ステートメントが入ります。with句のブロックも前カンマにしておくと、追加が楽になるのが最近の発見です。

クエリが長いとメンテがつらくなるものですが、with句により可読性がかなり改善されます(名前が先にくるのが読みやすい。サブクエリだと後ろに名前がつく)。集計期間のようなよく変わりそうなところをいちいち「別言語で文字列置換」などのサポートしなくてもよいのも便利で、複数箇所でcross join configして使います。

コメントアウトしていますが、最終的に出力する結果のデータをテストするクエリを書いておくと、その後の変更で壊してしまう心配も減ります。以下のような集計を用意しておき、checkクエリの出力を確認するようにするとよいです。

出力行数。期待から外れて多かったり少なかったりしないか？
- count(*) as num_row
ユニークであるカラム(例えば集計月あたり１行になる、など)のcount(distinct ...)が重複した出力を行なっていないか？
- count(distinct ...) = count(*) as is_column_unique
値が期待するレンジに含まれているか？(例えば全体に対するシェアなど、0〜1の範囲に収まるはずの値)
- sum(case when 0 <= x and x <= 1 then 1 end) = count(*) as is_all_x_in_range

派生で、configを前処理してから使うパターンです。

#standardsql

with raw_config as (
select
  '20171225' as end_yyyymmdd
, 7 as num_date
)

, config as (
select
  *
, format_date('%Y%m%d', date_add(parse_date('%Y%m%d', end_yyyymmdd), interval - num_date day)) as start_yyyymmdd
from
  raw_config
)

select * from config

ユーザ定義関数、配列、日付

一定の集計期間でデータを集約するタスクが発生します。このとき、集計の軸が注文によってコロコロ変わったりするのを吸収するレイヤが欲しくなり、ユーザ定義関数がその役割を果たしてくれます。式として書ける範囲で、複雑なのもかけます。

#standardsql

create temporary function round_unit(t date) -- 集計単位
  returns string
  as (
    -- format_date('%u_%a', t) -- 単純な曜日丸め
    case -- 曜日丸め。但し土曜と日曜は「週末」として集約。ソートできるようにprefixをつける
      when format_date('%a', t) in ('Sat', 'Sun') then '9_Weekend'
      else format_date('%u_%a', t)
    end
  );

select
  round_unit(day) as unit
, count(*) as num_day
from
  (select generate_date_array('2017-12-01', '2017-12-25') as days) as d
  cross join unnest(d.days) as day
group by
  unit
order by
  unit

データ型として配列を利用することは、アプリケーション開発だとあまり使わない印象があります(正規化されて消える)が、データ分析の文脈だと割と発生します。Legacy SQLだと REPEATED モードのカラムの扱い方に独自構文が用意されてましたが、Standard SQLだと上記の通り(Prestoなどでも使える)unnestによる展開ができます。

Web UIを使い倒す

データを貯めてると、いつのまにかデータセットやテーブルや過去に書いたクエリが増えてて、使いたいものがどこにあるのかわからなくなることがままあります。Web UIの便利機能を使うと色々捗ります。

dataset一覧で検索

「userなんちゃらテーブル見ればわかるって言われたけど、どのテーブル...」

datasetの検索窓から文字列検索しましょう(Job Historyの下)。複数のデータセットからテーブル名で検索ができます。

Query Editorからテーブル定義へ飛ぶ

「このクエリで使われてるテーブルにxxxカラムあったっけ？」

(Mac) Commandキーを押しながらテーブル名をクリックしましょう。テーブル定義がすぐ開きます。

Query Historyで検索

「"session"に関するクエリを以前書いたんだけど、どのプロジェクトで書いたんだっけ」

Query Historyを開いて、文字列検索しましょう。右の▽をクリックすると、より詳細な検索オプションも指定できます(正規表現マッチとか)。

その他

コストチェックがクエリでできて便利(コストに心配が必要になるけど)とか、BigQueryに繋げられる分析ツールが増えてきて便利とか、クエリによる負荷を心配しなくて良いとか。改めてBigQueryは便利と思いました(小並感)。

BigQueryを使い込んで便利だと思ったこと