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: 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 ;;
}
}