BigQueryのSELECT文

この記事はGoogle Cloud Platform Advent Calendar 2014 6日目の記事です。

うちの会社ではGoogleAnalyticsのデータをBigQueryに流しています。どうやら3日目の記事を書いたsatoru_magさんの会社でも導入しているみたいですね！

BigQueryとGoogleAnalytics - Qiita

最近、そのBigQueryを自分のチームで使い始めることになりました。以前、使い始めたので入門部分をまとめて公開しようと思ったらWEB+DB PRESS vol.83に大体書いてありました。。

WEB+DB PRESS Vol.83

BigQueryでデータを集計するのにはSQLのSELECT文を使うのですが、実際さわってみると標準のSELECT文にはない構文を結構使うことになり悩んだりしたので、その辺について書こうと思います。

構造化されたデータに対応する（WITHIN, FLATTEN）

BigQueryではスキーマを定義したテーブルにデータを格納します。データは、csv、もしくはjson形式のファイルから登録することができます。

csvは1行がテーブルの1レコードに対応するのでいいのですが、json形式の場合はプロパティの値が配列とか入れ子のjsonになっていたりするのでどうなるのでしょうか。

このようなデータは正規化することで1対多の関係の複数テーブルに分割できますが、BigQueryの場合は分割せずにそのようなjsonのままテーブルの1レコードとして登録することができます。

これは、テーブルのカラムにREPEATEDなデータ型やRECORD型を定義できるからです。カラムのことをフィールドって呼ぶみたいです。

• REPEATEDなデータ型というのはjsonのプロパティの値が配列になっている場合に対応します。データ型がIntでREPATEDである場合は整数の配列に対応できます
• RECORD型というのはjsonのプロパティの値がjsonになっている場合に対応します。RECORD型でREPEATEDっていうのもできます

1対多のデータを非正規化した状態でテーブルに保存しておくことで、正規化されたテーブルをJOINするより高速に集計できます。

その辺のことが書いてある公式ドキュメントはこちら。

Preparing Data for BigQuery - Google BigQuery — Google Cloud Platform

Google AnaliticsからBigQueryにインポートしたデータが格納されるテーブルは以下のようになっており、RECORD型が使われています。以下のドキュメントからだと分かりませんが、RECORD型でREPEATEDなフィールドもあります。

BigQuery Export スキーマ - アナリティクス ヘルプ

WITHIN

例えば、以下のSQLを実行します。hitsはRECORD型でREPEATEDなフィールドです。

SELECT fullVisitorId
      ,hits.hitNumber
  FROM [schema_name.ga_sessions_20141203]
 LIMIT 4

そうすると以下のような結果になりました。

Row	fullVisitorId	hits_hitNumber
1	1111	1
2	1111	2
3	2222	1
4	2222	2
5	2222	3
6	2222	4
7	2222	5
8	3333	1
9	4444	1

結果が4行以上でてますね。でもレコードとしては4つです。fullVisitorIdが4つでそれに対するREPEATEDなフィールドの値を全て取得しそれぞれ行として表示されてます。

各fullVisitorId毎のhitNumberをCOUNTしてみましょう。 以下のSQLになりますね。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) AS countHitNumber
  FROM [schema_name.ga_sessions_20141203]
 GROUP BY fullVisitorId
 LIMIT 4

これをWITHIN句を使って書き換えることもできます。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) WITHIN RECORD AS countHitNumber
  FROM [schema_name.ga_sessions_20141203]
 LIMIT 4

どちらでも結果はこうなります。

Row	fullVisitorId	countHitNumber
1	1111	2
2	2222	5
3	3333	1
4	4444	1

ここで、ついでに訪れたページのパスも見たいなぁと思いました。GROUP BYを使ってる方を書き換えてみて実行します。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) AS countHitNumber
      ,hits.page.pagePath
  FROM [schema_name.ga_sessions_20141203]
 GROUP BY fullVisitorId, hits.page.pagePath
 LIMIT 4

結果はこうなります。countHitNumberの値がfullVisitorIdとhits.page.pagePath毎のCOUNT値になってます。また結果として表示される行数も4行です。通常のSQLといっしょですね。

Row	fullVisitorId	countHitNumber	hits_page_pagePath
1	1111	2	/products/1000012911
2	2222	2	/my/categories/108895
3	2222	1	/
4	2222	1	/my

WITHIN句を使ってみます。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) WITHIN RECORD AS countHitNumber
      ,hits.page.pagePath
  FROM [schema_name.ga_sessions_20141203]
 LIMIT 4

結果はこうなりました。今度はcountHitNumberの数がfullVisitorIdでGROUP BYしたときのCOUNT値になり、パスに重複があっても複数行表示されます。また結果として表示される行数が4より多いです。

Row	fullVisitorId	countHitNumber	hits_page_pagePath
1	1111	2	/products/1000012911
2	1111	2	/products/1000012911
3	2222	5	/my/categories/108895
4	2222	5	/my/categories/108895
5	2222	5	/
7	2222	5	/my
8	2222	5	/contents/special
9	3333	1	/products/1000018634
10	4444	1	/products/1000027694

GROUP BY句を使った場合とWITHIIN句で結果が変わります。この違いはSQLが、FROM句 -> GROUP BY句 -> LIMIT句 -> SELECT句 の順で処理されると意識すれば腑に落ちるのではないでしょうか。

RECORD型の中にさらにREPATEDな項目があってそれを集計したい場合はどうすればいいのでしょうか。

（実用的な集計ではないですが・・・）hitsというRECORT型の中にhits.customvariablesというRECORD型でREPEATEDな項目があります。hits.customvariablesを、fullVisitorId毎ではなく、hits毎に集計したい場合は以下のように書きます。WITHIN RECORDではなくWITHIN hitsとなっているところが大事です。WITHIN RECORDとした場合fullVisitorId毎の集計になってしまいます。

SELECT fullVisitorId
      ,hits.hitNumber
      ,COUNT(hits.customvariables.customvarname) WITHIN hits AS countCustomvariables
  FROM [schema_name.ga_sessions_20141203]
 LIMIT 4

上のはGoogleのドキュメントでWITHIN node_nameと書かれているやつを使った例でした。

Dealing with Data - Google BigQuery — Google Cloud Platform

FLATTEN

次のは、customdimensionsフィールドでフィルタリングした例です。customdimensionsはRECORD型でREPEATABLEなフィールドです。

SELECT fullVisitorId
  FROM [schema_name.ga_sessions_20141203]
 WHERE customdimensions.index = 1
   AND customdimensions.value = 'hoge'
 LIMIT 4

fullVisitorId毎のヒット数をカウントしてみましょう。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) WITHIN RECORD AS countHitNumber
  FROM [schema_name.ga_sessions_20141203]
 WHERE customdimensions.index = 1
   AND customdimensions.value = 'hoge'
 LIMIT 4

そうするとエラーが起きます。

Error: Cannot query the cross product of repeated fields customDimensions.index and hits.hitNumber.

複数のREPEATEDなフィールドにアクセスすると上のエラーが起きます。今回で言うと、hitsとcustomdimensions。もっと単純にWHERE句ではなくSELECT句に並べた場合でもエラーが起きます。 そんなときはFLATTEN句を使います。

SELECT fullVisitorId
      ,hits.hitNumber
  FROM FLATTEN([schema_name.ga_sessions_20141203], customdimensions)
 WHERE customdimensions.index = 1
   AND customdimensions.value = 'hoge'
 GROUP BY fullVisitorId, hits.hitNumber
 LIMIT 4

FLATTEN句を使うとFROM句が終わった時点で、customdimensionsの数だけの行に展開されるイメージです。

Dealing with Data - Google BigQuery — Google Cloud Platform

UNION句の代わり

BigQueryで使うSQLにUNION句はありません。しかし、UNIONできないわけではありません。 UNIONしたい場合はFROM句にUNION対象とするテーブル名をカンマ区切りで並べます。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) WITHIN RECORD AS countHitNumber
  FROM [schema_name.ga_sessions_20141202]
      ,[schema_name.ga_sessions_20141203]
 LIMIT 4

UNION対象が多い場合や動的に指定したい場合はTable wildcard関数が使えます。例えば3日前から今日までのテーブルをUNIONしたい場合は、TABLE_DATE_RANGE関数を使って以下のように>書けます。

SELECT fullVisitorId
      ,COUNT(hits.hitNumber) WITHIN RECORD AS countHitNumber
  FROM (TABLE_DATE_RANGE([schema_name.ga_sessions_]
                        ,DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY')
                        ,CURRENT_TIMESTAMP()))
 LIMIT 4

他にも正規表現でUNION対象のテーブルを選べるTABLE_QUERY関数もあります。

Query Reference - Google BigQuery — Google Cloud Platform

おしまい

他にも色々便利そうな関数がたくさんあります！

Query Reference - Google BigQuery — Google Cloud Platform

いろいろ試してBigQueryライフを満喫しましょう！