LookerでBigQueryのワイルドカードテーブルを1つのViewファイルに定義する #Looker

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

設定はこちらで以上です。

さいごに

いかがでしたでしょうか。初めはやり方が分からず悩んでいましたが、設定自体は簡単だったと思います。

今回は、以下のコミュニティの内容を参考に設定を進めていきましたので、是非こちらも参考にしてみてください！

BigQuery-のワイルドカードテーブルを1つのViewを通して検索する方法