Looker勉強中のikumiです。BigQueryではワールドカードテーブルを使って分析するケースが多くあるかと思います。Lookerでワイルドカードテーブルを使用する場合、すべてのテーブルに対してViewを定義する必要があるのか?他にいい方法があるのではないか?と悩んでいたので、実際に試してみました!
ユースケース
BigQueryテーブル
日付でシャーディングされたGoogle Analyticsのデータを想定してサンプルデータを使用します
やりたいこと
上記のテーブルをLookerから接続し一つのViewに定義したうえで、ワイルドカードテーブルに対するクエリを発行する
やってみる
前提として、事前にデータベースへの接続とLookMLプロジェクトは作成済のものとします
Viewの作成
まずはViewの作成を行います。普段通り、Viewファイル上のパラメーターsql_table_name:
でテーブルを指定しますが、いくつかポイントがあります。
- sql_table_name
- テーブル名の接尾辞の部分をワイルドカードで記述する
- dimension_group: partition
- _TABLE_SUFFIXをディメンションで定義することで、WHERE句の検索条件として使用できるようにする
view: ga_session_base {
sql_table_name: `dataset_name.ga_sessions_*` ;;
dimension_group: partition {
type: time
sql: TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'\d\d\d\d\d\d\d\d'))) ;;
}
## 任意のディメンション&メジャーを指定
dimension_group: date {
type: time
timeframes: [date,week,month,year]
sql: PARSE_DATE('%Y%m%d',${TABLE}.date) ;;
}
dimension: visitId {
type: string
sql: ${TABLE}.visitId ;;
}
measure: visit_num {
type: count_distinct
sql: ${visitId} ;;
}
}
Exploreの作成
続いてExploreを定義します。ここで、先ほど作成したpartition
に対してフィルタを適用させました。
explore: ga_session_base {
label: "GA_セッション"
always_filter: {
filters: {
field: partition_date
value: "2017-08-01" ##仮で8/1のデータを指定
}
}
Explore画面で実行する
実行してみると、2017/8/1の訪問数として2509と結果が返されました。
BigQueryで単一テーブルに対してクエリを投げた結果と一致しています。
Exploreで自動的に作成されたSQLの中身を確認してみると、以下の通りとなっています。作成したpartition
がWhere句で適用されて、指定した日付のテーブルの絞込が行われているようです。
SELECT
(DATE(PARSE_DATE('%Y%m%d',ga_session_base.date) )) AS ga_session_base_date_date,
COUNT(DISTINCT ga_session_base.visitId ) AS ga_session_base_visit_num
FROM `dataset_name.ga_sessions_*` AS ga_session_base
WHERE ((( TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'\d\d\d\d\d\d\d\d'))) ) >= ((TIMESTAMP('2017-08-01 00:00:00'))) AND ( TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'\d\d\d\d\d\d\d\d'))) ) < ((TIMESTAMP_ADD(TIMESTAMP('2017-08-01 00:00:00'), INTERVAL 1 DAY)))))
GROUP BY
1
ORDER BY
1 DESC
LIMIT 500
設定はこちらで以上です。
さいごに
いかがでしたでしょうか。初めはやり方が分からず悩んでいましたが、設定自体は簡単だったと思います。
今回は、以下のコミュニティの内容を参考に設定を進めていきましたので、是非こちらも参考にしてみてください!