こんにちは。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形式でのデータ読み込みが可能なので、ネストされた構造や繰り返しの構造をサポートしています。
ネストされた構造に対してクエリを発行するために、WITHIN や FLATTEN が存在するようです。
細かく見ていきましょう。
■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ではビッグデータと戯れたいエンジニアも大募集中です！
どんな会社か気になる方は、是非下記のサイトもご覧ください。
