BigQueryのタイムゾーンに関するプラクティス

[takegue]

BigQueryにおけるタイムゾーンの取り扱い

BigQueryにおけるタイムゾーンの取り扱いは問題を起こしやすい。
特にDATE型やdatetime型に丸め込まれた場合に問題が発生しやすい。

CURRENT_DATE() や CURRENT_DATETIME() で得られる日付はデフォルトではUTC である*1。
また 日付パーティションもtimestampからの変換を行っている場合はデフォルトでUTCとなる。

特定のタイムゾーンでの日付を得るためには明示的にタイムゾーンを与えなければならない

select 
  CURRENT_DATE('Asia/Tokyo')
  , CURRENT_DATETIME('Asia/Tokyo')
  , timestamp('2022-01-01', 'Asia/Tokyo')
  , timestamp_trunc(current_timestamp(), HOUR, 'Asia/Tokyo')

BigQuery コンソール上の timestampの表記の注意点

注意点としてBigQueryのコンソール画面における timestamp型の表記は
デフォルトではUTCタイムゾーンとして表示される.

JSTといったタイムゾーンの変換を行ったとしてもコンソール上はUTCタイムゾーンを基準に表示される。
先ほどのクエリ結果は、コンソール上では次のように表示される。

BigQuery でのタイムゾーンを含む取り扱いのプラクティス

DWHとのレイヤは timestamp型を保持する

BigQueryでタイムゾーンが型レベルで取り扱えるのは timestamp型 のみである。
データマートの作成などデータソースとしての役割を担うDWHレイヤにおいてはtimestamp型で保持することが望ましい

• タイムゾーン含め日時を表す方の中では 最も情報量が多く明示的な型であるため
• 変換の関数が一番豊富
• 元がDATE型やString型であった場合も含め、timestamp型にパースしておくとよい

利用者向けに ts変換用のUDFを用意しておく

時刻の変換用はミスが出やすいため関数を用意しておくと、利用に便利である

create or replace function `fn.ts_conv`(ts timestamp)
as (
  struct(
    ts as raw
    , struct(
      string(ts, 'UTC') as pretty
      , struct(
          timestamp_trunc(ts, hour, 'UTC') as hour
          , timestamp_trunc(ts, day, 'UTC') as day
          , timestamp_trunc(ts, week, 'UTC') as week
          , timestamp_trunc(ts, month, 'UTC') as month
        ) as trunc
      , struct(
          extract(hour from ts at time zone 'UTC') as hour
          , extract(day from ts at time zone 'UTC') as day
          , extract(week from ts at time zone 'UTC') as week
          , extract(month from ts at time zone 'UTC') as month
        ) as part
    ) as utc
    , struct(
      string(ts, 'Asia/Tokyo') as pretty
      struct(
        timestamp_trunc(ts, hour, 'Asia/Tokyo') as hour
        , timestamp_trunc(ts, day, 'Asia/Tokyo') as day
        , timestamp_trunc(ts, week, 'Asia/Tokyo') as week
        , timestamp_trunc(ts, month, 'Asia/Tokyo') as month
      ) as trunc
      , struct(
          extract(hour from ts at time zone 'Asia/Tokyo') as hour
          , extract(day from ts at time zone 'Asia/Tokyo') as day
          , extract(week from ts at time zone 'Asia/Tokyo' ) as week
          , extract(month from ts  at time zone 'Asia/Tokyo') as month
        ) as part
    ) as jst
  )
);

-- 構造体のアクセス宣言のみで時間の変換が可能になる
select ts_conv(current_timestamp()).jst.trunc.hour
;

@@time_zone によるデフォルトのタイムゾーンの変換

BigQuery Scriptの機能を利用し、 デフォルトのタイムゾーンの変換が可能である (Ref. https://cloud.google.com/bigquery/docs/reference/system-variables)

set @@time_zone = 'Asia/Tokyo'

select current_date()
--> 'Asia/Tokyo'での日時となる

ただし、上記の設定はテーブル作成の際のパーティション計算には及ばない点は注意である
@@time_zoneを変更したとしても日付パーティションの作成の際に、timestampをDATEに変換したとしても
DATEはUTCを基準に計算される.

set @@time_zone = 'Asia/Tokyo';
create or replace table `sandbox.test_partition`
-- DATE(d) の日付はUTCを基準に計算される
partition by DATE(d) 
as
  select d from unnest(
    generate_timestamp_array(
      timestamp('2022-08-24', 'UTC')
      , timestamp('2022-08-25', 'UTC')
      , interval 3 hour
    )
  ) as d

Orgnization/Projectごとのデフォルトタイムゾーンの設定 (from twitter:@yutah_3)

BigQueryではタイムゾーン情報は、プロジェクトもしくは組織レベルでのデフォルト値を設定することができる。
この場合ユーザによるBigQuery Script毎の設定は不要となる。

-- 引用: https://cloud.google.com/bigquery/docs/default-configuration#default_configurations

-- Orgnization-wide configuration
ALTER ORGANIZATION
SET OPTIONS (
  `region-us.default_time_zone `= 'Asia/Tokyo'
);

-- Project-wide configuration
ALTER PROJECT project_id
SET OPTIONS (
  `region-us.default_time_zone` = 'Asia/Tokyo'
);

この現在の設定は INFORMATION_SCHEMAから確認できる

-- 現在のプロジェクトで有効な設定の状態
SELECT * FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;

-- Organization-wide Configuration 
SELECT * FROM region-us.INFORMATION_SCHEMA.ORGANIZATION_OPTIONS;

詳細は公式ドキュメントを参照するとよい。
https://cloud.google.com/bigquery/docs/default-configuration#default_configurations

References

*1. Timestamp Function | How time zones work with timestamp functions

[takegue]

こちらのFBを反映した
https://twitter.com/yutah_3/status/1562232074974953475?s=21&t=zypNUOa7JZDTqoPFUEPlYw