BigQueryのSQL入門 ~データ参照する上で押さえておきたい3つの基本+α~ #BigQuery

ペタバイト級のデータでも数分でスキャンができるBigQueryは広告ツール、解析ツール、GEO情報を扱うツールなどいわゆるビッグデータを扱う時によく使われます。
「うちはビッグデータを使ったサービスは今の所ないからいいかなぁ」と思っていたら、アプリ用のGoogleAnalyticsであるFirebase AnalyticsはクエリAPIがなく、BigQuery経由でデータを取るしかない仕様でして、急に向き合う羽目に。。。
各人それぞれ理由はあると思いますが、突然BigQueryを使わなければいけなくなった人のために概要とデータを参照する上での基本をまとめてみました。

BigQueryの概要

Tomohiro Shindenさんのスライドがよくまとまっていますので、そちらを参照ください
列指向型のデータベースで、コンテナをどかっと立ち上げて分散してぶん回すのでペタバイト級のデータも数分で処理できるのが特徴です。

BigQueryでSQLを書く前に知っておきたいこと

1. 関数がたくさんある

BigQuery公式サイト:クエリリファレンス
 BigQuery公式サイト:関数と演算子
上記2つをみていただければわかりますが、関数がたくさんあります。mysqlなどでもよく使う countなどの他に、数学・分析系の関数やtimestampに関わる関数が充実しており、テーブルワイルドカード関数など独特なものも存在します

2. テーブルが分割されている！テーブルワイルドカード関数が必須

BigQueryのテーブルは1日ごとなどでテーブルが分割（シャーディング）されていることが多いです。これはスキャン容量を抑えるためです。firebase analyticsをexportしたデータも日時で分割されていました。
よって、スキャンするテーブルを複数指定する必要があります。その時に使えるのがテーブルワイルドカード関数です。
例えばTABLE_DATE_RANGE関数を使って、以下のようなクエリを書くと、mydata.people20140325、mydata.people20140326、mydata.people20140327がスキャン対象範囲になります。

SELECT
  name
FROM
  TABLE_DATE_RANGE(mydata.people,
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))

3. legacySQLとstandardSQLがあるので、コピペ注意！

何かやりたいことがあるとき、stackoverflowで見つけたコードをコピペすることもあるかと思いますが、それをするとsyntax errorになることがあります。
これは、現状BigQueryがlegacySQLとstandardSQLという二つの構文をサポートしていることが原因です。
例えば、legacySQLではテーブル名を[]で囲いますが、standardSQLでは`で囲います。
詳しいことは BigQuery公式サイト:標準 SQL への移行に載っていますが、一番わかりやすいのがテーブル指定方法変更だと思うので以下に記載しておきます。もはや別物です。

# legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');
# standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

+α クエリの定期実行はGAS（google apps script）が便利

クエリを定期的に実行したい場合、Google Cloud SDKをセットアップして bq コマンドなどを使って行うかと思いますが、SDKのセットアップが結構めんどくさいです。
最初aws lambdaでやろうとしましたが、sdkのセットアップがめんどくさすぎてやめました。
しかし、BigQueryの定期実行のためだけに、サーバーにsdkいれるのか？という抵抗感もあります。
そこで、提案したいのがGoogle Apps Scriptです。
ヘッダーメニューの　リソース > Googleの拡張サービス からBigQueryを有効にすることですぐに使えるようになります。
GASには定期実行の仕組みもついてるので、日時KPIなど取得するくらいならこれで十分です。

最後に

Googleのサービスを使っているといつかはBigQueryを使う場面に直面すると思うので、先に知っておいた方がいいことをまとめてみました。
これから使い始める方の、少しでもお役に立てれば幸いです。