Question

テンプレートフィルターで日付別シャード化テーブルをフィルタ

  • 17 August 2020
  • 0 replies
  • 174 views

Userlevel 1

BigQueryのテーブルが日付別で [prefix]_YYYYMMDD のようにシャード化されており、この日付とテーブル内の日付のカラムがUTCになっている場合に、LookerのExploreやダッシュボードではJSTで指定した期間のデータだけを検索したいケースがあります。


<例>

log_20200728(UTC)テーブルには以下のtimeカラムがあり、データはUTCで入っている。

この場合、JST 7/29で検索したい場合は、シャード化テーブルはog_20200728とlog_20200729を対象とし、timeカラムはJSTに変換したデータを検索したい。


2020-07-28T04:00:00.000+00:00  (JSTは2020-07-28T13:00:00.000+00:00)
2020-07-28T18:00:00.000+00:00 (JSTは2020-07-29T03:00:00.000+00:00)

log_20200729(UTC)テーブルには以下のtimeカラムがあり、データはUTCで入っている


2020-07-29T06:00:00.000+00:00 (JSTは2020-07-29T15:00:00.000+00:00)
2020-07-29T19:00:00.000+00:00 (JSTは2020-07-30T04:00:00.000+00:00)

LookMLでは、以下のようにfilterの中でテンプレートフィルターを使うことで、シャード化したテーブルをJSTで検索しつつ、timestampカラムの値もJST変換して検索することができます。




Viewファイル

view: ga_sessions {
sql_table_name: `test.test.ga_sessions_*`
;;

filter: target_date {
type: date
datatype: date
sql:
({% condition target_date %} ${partition_date} {% endcondition %}
OR
{% condition target_date %} ${partition_date_after} {% endcondition %})
AND
{% condition target_date %} ${time_date} {% endcondition %}
;;
}

dimension_group: partition {
timeframes: [raw,date,week,month,year]
type: time
sql: TIMESTAMP(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'\d\d\d\d\d\d\d\d'))) ;;
}

dimension: partition_date_after {
type: date
datatype: date
sql: DATE_ADD(CAST(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX,r'\d\d\d\d\d\d\d\d')) AS DATE) , INTERVAL 1 DAY) ;;
}

dimension_group: time {
type: time
timeframes: [raw,hour,date,month]
sql: ${TABLE}.time ;;
}
}



0 replies

Be the first to reply!

Reply