GROUPエンジニアブログです。>2015年02月16日15:55カテゴリBigQueryで使えるクエリをまとめてみたこんにちは。Networkの開発に携わっている@smileeeenです。最近では所謂ビッグデータを活用している事例も珍しくなくなってきました。ビッグデータを用いて分析などを行う時に、便利なツールの1つとしてGoogleが提供しているBigQueryがあげられると思います。弊社内でもBigQueryの活用例が増えてきているので、この機会にどのようなクエリが発行できるのか、お勉強を兼ねてまとめてみました。ちなみに私は普段MySQLに慣れ親しんでいるので、MySQLではできないような事を中心にまとめてみたいと思います。それでは、公式サイトのリファレンスに倣って確認していきたいと思います。Platform■SELECTクエリ概要https://cloud.google.com/bigquery/query-reference#select-syntaxSELECTクエリのシンタックスは上記のようになっています。自分にとっては見慣れないキーワードです。BigQueryはJSON形式でのデータ読み込みが可能なので、ネストされた構造や繰り返しの構造をサポートしています。細かく見ていきましょう。node_nameネストされたフィールドに対して集約関数を実行する場合に、WITHINを使用します。深いネストの場合は、親となるノードの名前を指定してあげる必要があります。最初すんなり理解ができませんでしたが、ネストされたフィールドに対して集約関数を実行する時にはWITHINを指定する、と覚えておけば良さそうです。省略した場合、現在のプロジェクトを対象とします。複数のテーブルを横断してクエリを発行するBigQueryにはUNION句は存在しません。同等の結果を取得する事ができます。省略形となり、動作が異なってくるので、注意が必要そうです。テーブルの指定にワイルドカードを利用テーブル名にいくつかの種類のワイルドカードを利用する事ができます。BigQueryでは探索したデータのサイズによって課金されるので、テーブルを日や月で区切って作成しているケースが多いと思います。そのような場合にテーブルを横断してクエリを発行したい時に便利な機能です。形式の年月日が付いている場合に使えるワイルドカード関数です。前の部分を指定すると、timestampで指定した範囲のテーブルに対して、一括でクエリ実行する事ができます。利用すると、指定した範囲にテーブルが存在しない年月日があるとエラーを返してくれます。全てのテーブルが存在する事を前提に、クエリを発行したい場合に有用そうです。expr)expr部に正規表現など文字列の評価式を入れる事で、任意の条件にマッチするテーブルをクエリ対象にする事ができます。■FLATTEN句https://cloud.google.com/bigquery/query-reference#flattenネストした構造のフラット化を行います。SELECT句やWHERE句内に複数のネストされたフィールドを指定するとエラーになる事があるので、その際にFLATTENを利用してフラット化してあげる必要があります。JOINがサポートされています。大きなサイズのテーブルのJOINJOINは通常右側のテーブルに、8MB未満のデータサイズのテーブルしか指定できません。JOINには使用できず、使用する事によって通常のJOINよりもパフォーマンスも悪化するそうです。8MBというのは割と小さいので、もし大きなデータ同士をJOINしたい場合には、BigQueryにデータを読み込ませる時点で1つのデータにマージする事を検討しても良さそうです。BYに指定するフィールドのユニーク数が多い場合、エラーになる事があります。付ける必要があります。BYに指定する事はできないそうです。■HAVING句https://cloud.google.com/bigquery/query-reference#having集約された値を元に絞り込む場合には、MySQL等と同様にHAVING句を利用できます。HAVING句で使用するフィールドは、必ずSELECT句に記載されている必要があります。SELECT句で集約されたフィールドのエイリアスは、HAVING句で直接参照が可能となっています。指定した場合、そのフィールドの値のユニーク数を返しますが、統計的近似値で正確ではない可能性があるので注意が必要です。第二引数の正確な結果が保障される閾値に、大きな値を設定する事でより正確な数を知る事ができますが、クエリの実行時間に影響を及ぼすそうです。デフォルトの閾値は「1000」になっています。統計学系MySQLにも一部実装されていますが、やはり分析が目的で利用されることが多いサービスなので、統計系の関数は充実している印象です。エイリアスになります。形式よりも実行時間も速いそうです。下記の2つのクエリは同じ事をしています。■IPアドレス用関数https://cloud.google.com/bigquery/query-reference#ipfunctionsアクセスログの分析など活用できるIPアドレスを人間の読みやすい形式に整形したり、パースしたりすることができます。■JSON用関数https://cloud.google.com/bigquery/query-reference#jsonfunctions通常はJSONの構造をスキーマとして反映させますが、JSON文字列を操作する関数も用意されています。なく、こちらの使用が推奨されています。■URL用関数https://cloud.google.com/bigquery/query-reference#urlfunctionsネットサービスには欠かせないURLを操作する関数も用意されています。■Window(分析)関数https://cloud.google.com/bigquery/query-reference#windowfunctionsOracleやPostgreSQLなどでも利用できるWindow関数が利用できます。_)m■感想SQLの書き方はMySQLなどと大きくは変わらないので、手を付けやすいですね。ネストされた構造などは今までになかったので、少し慣れが必要そうです。採用どんな会社か気になる方は、是非下記のサイトもご覧ください。