こんにちは。Zucks Ad Networkの開発に携わっている@smileeeenです。
最近では所謂ビッグデータを活用している事例も珍しくなくなってきました。
ビッグデータを用いて分析などを行う時に、便利なツールの1つとしてGoogleが提供しているBigQueryがあげられると思います。

弊社内でもBigQueryの活用例が増えてきているので、この機会にどのようなクエリが発行できるのか、お勉強を兼ねてまとめてみました。
ちなみに私は普段MySQLに慣れ親しんでいるので、MySQLではできないような事を中心にまとめてみたいと思います。

それでは、公式サイトのリファレンスに倣って確認していきたいと思います。
Query Reference - Google BigQuery — Google Cloud Platform


■SELECTクエリ概要

https://cloud.google.com/bigquery/query-reference#select-syntax

SELECTクエリのシンタックスは上記のようになっています。
パッと見、MySQLとほぼ同様のクエリを発行する事ができそうですね!
agg_function(expr3) WITHIN expr4 及び FLATTEN(table_name1|(subselect1)) が自分にとっては見慣れないキーワードです。

BigQueryはJSON形式でのデータ読み込みが可能なので、ネストされた構造や繰り返しの構造をサポートしています。
ネストされた構造に対してクエリを発行するために、WITHINFLATTEN が存在するようです。

細かく見ていきましょう。


■SELECT句

WITHIN句 - ネストされたフィールド内の集約

https://cloud.google.com/bigquery/docs/data#within

agg_function(field) WITHIN RECORD
agg_function(field) WITHIN node_name

ネストされたフィールドに対して集約関数を実行する場合に、WITHINを使用します。
深いネストの場合は、親となるノードの名前を指定してあげる必要があります。

最初すんなり理解ができませんでしたが、ネストされたフィールドに対して集約関数を実行する時にはWITHINを指定する、と覚えておけば良さそうです。


■FROM句

https://cloud.google.com/bigquery/query-reference#from

project_name は省略可能です。省略した場合、現在のプロジェクトを対象とします。
プロジェクト名にハイフンが含まれている場合は、テーブル名の指定全体を [] で囲む必要があります。

複数のテーブルを横断してクエリを発行する

BigQueryにはUNION句は存在しません。
その代わりスキーマが同一のテーブルは、FROM句内でカンマで区切って指定するだけで UNION ALL と同等の結果を取得する事ができます。
MySQLでは同じ表記だと INNER JOIN の省略形となり、動作が異なってくるので、注意が必要そうです。

Table wildcard functions - テーブルの指定にワイルドカードを利用

テーブル名にいくつかの種類のワイルドカードを利用する事ができます。
BigQueryでは探索したデータのサイズによって課金されるので、テーブルを日や月で区切って作成しているケースが多いと思います。
そのような場合にテーブルを横断してクエリを発行したい時に便利な機能です。

TABLE_DATE_RANGE - テーブル名の末尾に'YYYYMMDD'

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

テーブル名の末尾に YYYYMMDD 形式の年月日が付いている場合に使えるワイルドカード関数です。
prefix にテーブルの YYYYMMDD 前の部分を指定すると、timestampで指定した範囲のテーブルに対して、一括でクエリ実行する事ができます。

さらに TABLE_DATE_RANGE_STRICT を利用すると、指定した範囲にテーブルが存在しない年月日があるとエラーを返してくれます。
全てのテーブルが存在する事を前提に、クエリを発行したい場合に有用そうです。

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

TABLE_QUERY - 柔軟なテーブルワイルドカード関数

TABLE_QUERY(dataset, expr)

expr部に正規表現など文字列の評価式を入れる事で、任意の条件にマッチするテーブルをクエリ対象にする事ができます。


■FLATTEN句

https://cloud.google.com/bigquery/query-reference#flatten

ネストした構造のフラット化を行います。フラット化したいフィールドを flattenField の部分に指定します。
SELECT句やWHERE句内に複数のネストされたフィールドを指定するとエラーになる事があるので、その際にFLATTENを利用してフラット化してあげる必要があります。

少し試してみたのですが、具体的にどういうケースでエラーになってフラット化する必要があるのか、解説できるまでに至りませんでした…orz


■JOIN句

https://cloud.google.com/bigquery/query-reference#joins

JOIN句では INNER JOIN、LEFT OUTER JOIN、CROSS JOINがサポートされています。
デフォルトでは INNER JOIN となります。

EACH - 大きなサイズのテーブルのJOIN

JOINは通常右側のテーブルに、8MB未満のデータサイズのテーブルしか指定できません。
より大きなサイズのテーブルを指定したい場合には JOIN EACH を使う必要があります。
EACHはCROSS JOINには使用できず、使用する事によって通常のJOINよりもパフォーマンスも悪化するそうです。
8MBというのは割と小さいので、もし大きなデータ同士をJOINしたい場合には、BigQueryにデータを読み込ませる時点で1つのデータにマージする事を検討しても良さそうです。


■GROUP BY句

https://cloud.google.com/bigquery/query-reference#groupby

GROUP BYに指定するフィールドのユニーク数が多い場合、エラーになる事があります。
その場合 EACH を付ける必要があります。
また、float型やdouble型のフィールドは現状GROUP BYに指定する事はできないそうです。


■HAVING句

https://cloud.google.com/bigquery/query-reference#having

集約された値を元に絞り込む場合には、MySQL等と同様にHAVING句を利用できます。
HAVING句で使用するフィールドは、必ずSELECT句に記載されている必要があります。
また、SELECT句で集約されたフィールドのエイリアスは、HAVING句で直接参照が可能となっています。


■集約関数

https://cloud.google.com/bigquery/query-reference#aggfunctions

COUNT(DISTINCT field) - fieldのユニーク数を返す

COUNT(DISTINCT field[, n])

COUNT()内のフィールドに DISTICT を指定した場合、そのフィールドの値のユニーク数を返しますが、統計的近似値で正確ではない可能性があるので注意が必要です。
第二引数の正確な結果が保障される閾値に、大きな値を設定する事でより正確な数を知る事ができますが、クエリの実行時間に影響を及ぼすそうです。
デフォルトの閾値は「1000」になっています。

統計学系

MySQLにも一部実装されていますが、やはり分析が目的で利用されることが多いサービスなので、統計系の関数は充実している印象です。

標準偏差

STDDEV(numeric_expr)
STDDEV_SAMP(numeric_expr)

母標準偏差

STDDEV_POP(numeric_expr)

標準分散

VARIANCE(numeric_expr)
VAR_SAMP(numeric_expr)

母標準分散

VAR_POP(numeric_expr)

相関係数

CORR(numeric_expr, numeric_expr)

母共分散

COVAR_POP(numeric_expr1, numeric_expr2)

標本共分散

COVAR_SAMP(numeric_expr1, numeric_expr2)

文位数

QUANTILES(expr[, buckets])

TOP - 便利なエイリアス

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

GROUP BY ... ORDER BY ... LIMIT ... のエイリアスになります。
またGROUP BY 形式よりも実行時間も速いそうです。
例えば下記の2つのクエリは同じ事をしています。


■IPアドレス用関数

https://cloud.google.com/bigquery/query-reference#ipfunctions

アクセスログの分析など活用できるIPアドレスを人間の読みやすい形式に整形したり、パースしたりすることができます。

人の読みやすい形式のIPv4→in_addr 形式 IPv4

PARSE_IP(readable_ip)

in_addr 形式 IPv4→人の読みやすい形式のIPv4

FORMAT_IP(integer_value)

人の読みやすい形式のIPv4, IPv6→パックされたin_addr 形式 IPv4, IPv6

PARSE_PACKED_IP(packed_ip)

パックされたin_addr 形式 IPv4, IPv6→人の読みやすい形式のIPv4, IPv6

FORMAT_PACKED_IP(packed_ip)

使用例


■JSON用関数

https://cloud.google.com/bigquery/query-reference#jsonfunctions

通常はJSONの構造をスキーマとして反映させますが、JSON文字列を操作する関数も用意されています。

文字列で表現されたJSONから指定したパス部分を抜き出す

JSON_EXTRACT(json, json_path)

使用例


■正規表現

https://cloud.google.com/bigquery/query-reference#regularexpressionfunctions

正規表現にマッチするか判定

REGEXP_MATCH('str','reg_exp')

正規表現にマッチする部分を抜き出す

REGEXP_EXTRACT('str', 'reg_exp')

正規表現でマッチする部分を文字列置換

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

使用例


■文字列操作関数

https://cloud.google.com/bigquery/query-reference#stringfunctions

文字列が含まれるかどうかの判定
単純な文字列を含むかの検索であれば、正規表現の REGEXP_MATCH ではなく、こちらの使用が推奨されています。

expr CONTAINS 'str'

使用例


■URL用関数

https://cloud.google.com/bigquery/query-reference#urlfunctions

ネットサービスには欠かせないURLを操作する関数も用意されています。

URLのホスト部分を取得

HOST('url_str')

URLのドメイン部分を取得

DOMAIN('url_str')

URLのトップレベルドメインを取得

TLD('url_str')

使用例


■Window(分析)関数

https://cloud.google.com/bigquery/query-reference#windowfunctions

OracleやPostgreSQLなどでも利用できるWindow関数が利用できます。 1ブログ記事には収まらないボリュームなので、詳細は公式サイトを参照してくださいm(_ _)m


■感想

SQLの書き方はMySQLなどと大きくは変わらないので、手を付けやすいですね。
ネストされた構造などは今までになかったので、少し慣れが必要そうです。
これから沢山触ってビッグデータと戯れたいと思います!


[PR]

VOYAGE GROUPではビッグデータと戯れたいエンジニアも大募集中です!

どんな会社か気になる方は、是非下記のサイトもご覧ください。