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

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

2 0 330