Google BigQueryは、分析関数とUDFを活用すると、いい感じの超並列計算機になる #GoogleCloud

最近、仕事でGoogle BogQueryを使っているのですが、そこでBigQueryのすばらしさに改めて気づいてので記事にしました。

「仕事で使っている」といってもお客様向けのプロダクションサービスとかではなく、実験的なプロジェククトのデータ分析基盤として、なので、いろいろ気づいていないデメリットなんかもあるかもしれませんが、そこは悪しからず。

UDFと分析関数を活用

で、何がすごいかというと、Queryが早いというのは皆さんご存知とは思いますが、さらにすごいのが UDF(User Difinition Function) です。UDFは文字通り自由に定義できる関数で、Queryの中で行単位のデータに対して計算されることができます。データ分析なんかしていると統計的な数字を出したかったり、データを変換したかったりすると思いますが、そんなことが自由自在です。現在は対応している言語はJavaScriptだけなのですが、まぁそこは問題にはならないでしょう。さらにもちろん、BigQueryでは分析関数/Analytic Functions が使えますから、行をまたぐような処理はこれを併用していろいろ高度な集計が可能です。

Analytic functionsを使っていい感じに行単位にまとめておき、そこにUDFを使ってBigQuery上で計算をさせれば、BigQueryが超ハイパフォーマンスのデータ加工のための並列コンピューティングとして利用できます。

さらにここの計算リソースはデータの読み込みとは無関係なので、課金周りもまったく気にする必要がありません！(はず)

具体例

具体例として私が活用している処理例を紹介します。

時系列のデータを扱っているので、レコードを並べ変えつつ、前後の値からデータを作りたいので、LEADとかLAGを使って1行にまとめ、

SELECT
  COALESCE(LEAD(A.count, 1, 0) OVER (PARTITION BY mesh ORDER BY time),0) AS rc10_prev1row,
  COALESCE(LAG(A.count, 1, 0) OVER (PARTITION BY mesh ORDER BY time),0) AS rc10_after1row,
  LEAD(Latitude, 1) OVER (PARTITION BY id ORDER BY time ASC) AS next_latitude,
  LEAD(Longitude, 1) OVER (PARTITION BY id ORDER BY time ASC) AS next_longitude

その後、まとめた情報を使って、UDFを使って計算した結果をしまう

さらにすばらしいのは、JavaScriptの処理をGoogleCloudStorageにファイルとしておいておくことができるので、いくら.jsに、ごちゃごちゃ汚い処理のコードがあったとしても(笑)、Query側からは完全に隠蔽でき、すっきりなんです！再利用性含めたメンテナビリティも改善しますね。

CREATE TEMPORARY FUNCTION latlon_to_meshcode(lat FLOAT64, lon FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS """
    mesh = latlon_to_meshcode(lat, lon);
    return mesh;
"""
OPTIONS (
  library="gs://bq-js-lib/convmesh.js"
);

SELECT
  latlon_to_meshcode(next_latitude, next_longitude) AS next_mesh,

こんな感じ。

ね。すっきり。いやぁ、BigQueryすばらしいなぁー