LoginSignup
0
0

More than 1 year has passed since last update.

@suzutatsu

BigQueryの関数向けにデフォルトのタイムゾーン設定がやってきた

Posted at

BigQueryの新機能

デフォルト構成という機能が登場しました。

できること

以下の3つがプロジェクトまたは組織の単位で指定できるようになります。

  • default_time_zone
  • default_kms_key_name
  • default_query_job_timeout_ms

どれも興味深いのですが、特にデフォルトのタイムゾーンを指定できるdefault_time_zoneに期待が高まります。
これにより、BigQueryのデフォルトのタイムゾーンがUTCではなくAsia/Tokyoであるかのように関数を使えます。

できないこと

あくまでdefault_time_zoneは
タイムゾーンが引数として指定されていない場合に、タイムゾーンに依存する SQL 関数で使用するデフォルトのタイムゾーン
を指定するだけなので、データ取り込み時の挙動は変わりません。
タイムスタンプ型でデータを取り込んだらBigQuery内でずっとAsia/Tokyo扱いされるということはありません。

やってみた

デフォルト値の確認

まずはデフォルト値を確認します。

SELECT 'us' as region, * FROM region-us.INFORMATION_SCHEMA.PROJECT_OPTIONS;
SELECT 'us' as region, * FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS

SELECT 'tokyo' as region, * FROM region-asia-northeast1.INFORMATION_SCHEMA.PROJECT_OPTIONS;
SELECT 'tokyo' as region, * FROM region-asia-northeast1.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS

SELECT 'osaka' as region, * FROM region-asia-northeast2.INFORMATION_SCHEMA.PROJECT_OPTIONS;
SELECT 'osaka' as region, * FROM region-asia-northeast2.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS

いずれも0件でした。

default_time_zoneの設定

プロジェクトレベルでusとtokyoにdefault_time_zoneを設定してみます。

ALTER PROJECT `sandbox-suzutatsu-bq-jst`
SET OPTIONS (
  `region-us.default_time_zone` = 'Asia/Tokyo',
  `region-asia-northeast1.default_time_zone` = 'Asia/Tokyo');

Duplicate option 'default_time_zone'
一気に設定はできませんでした。
個別に実行してみます。

ALTER PROJECT `sandbox-suzutatsu-bq-jst`
SET OPTIONS (
  `region-us.default_time_zone` = 'Asia/Tokyo');

ALTER PROJECT succeeded. Please make sure no existing queries depend on the old defaults (such as the default time zone) or else these queries will be broken.
成功しました。
tokyoも設定します。

ALTER PROJECT `sandbox-suzutatsu-bq-jst`
SET OPTIONS (
  `region-asia-northeast1.default_time_zone` = 'Asia/Tokyo');

設定後のデフォルト値の確認

設定後のデフォルト値を確認します。option_descriptionは結果の文字列が長いので省略しました。

us
bq query --location=US --nouse_legacy_sql \
'
SELECT "us" as region, * EXCEPT(option_description) FROM region-us.INFORMATION_SCHEMA.PROJECT_OPTIONS;
SELECT "us" as region, * EXCEPT(option_description) FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS
'

SELECT "us" as region, * EXCEPT(option_description) FROM region-us.INFORMATION_SCHEMA.PROJECT_OPTIONS; -- at [2:1]
+--------+----------------+--------------------------+-------------------+-------------+--------------+
| region | project_number |        project_id        |    option_name    | option_type | option_value |
+--------+----------------+--------------------------+-------------------+-------------+--------------+
| us     |    34294867256 | sandbox-suzutatsu-bq-jst | default_time_zone | STRING      | Asia/Tokyo   |
+--------+----------------+--------------------------+-------------------+-------------+--------------+
SELECT "us" as region, * EXCEPT(option_description) FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS; -- at [3:1]
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+
| region | project_number |        project_id        |    option_name    | option_type | option_value | option_set_level |     option_set_on_id     |
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+
| us     |    34294867256 | sandbox-suzutatsu-bq-jst | default_time_zone | STRING      | Asia/Tokyo   | projects         | sandbox-suzutatsu-bq-jst |
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+
tokyo
bq query --location=asia-northeast1 --nouse_legacy_sql \
'
SELECT "tokyo" as region, * EXCEPT(option_description) FROM region-asia-northeast1.INFORMATION_SCHEMA.PROJECT_OPTIONS;
SELECT "tokyo" as region, * EXCEPT(option_description) FROM region-asia-northeast1.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS
'

SELECT "tokyo" as region, * EXCEPT(option_description) FROM region-asia-northeast1.INFORMATION_SCHEMA.PROJECT_OPTIONS; -- at [2:1]
+--------+----------------+--------------------------+-------------------+-------------+--------------+
| region | project_number |        project_id        |    option_name    | option_type | option_value |
+--------+----------------+--------------------------+-------------------+-------------+--------------+
| tokyo  |    34294867256 | sandbox-suzutatsu-bq-jst | default_time_zone | STRING      | Asia/Tokyo   |
+--------+----------------+--------------------------+-------------------+-------------+--------------+
SELECT "tokyo" as region, * EXCEPT(option_description) FROM region-asia-northeast1.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS; -- at [3:1]
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+
| region | project_number |        project_id        |    option_name    | option_type | option_value | option_set_level |     option_set_on_id     |
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+
| tokyo  |    34294867256 | sandbox-suzutatsu-bq-jst | default_time_zone | STRING      | Asia/Tokyo   | projects         | sandbox-suzutatsu-bq-jst |
+--------+----------------+--------------------------+-------------------+-------------+--------------+------------------+--------------------------+

option_valueにAsia/Tokyoを設定できました。

クエリの挙動の確認

それではタイムゾーンに影響するクエリを実行してみます。

us
bq query --location=US --nouse_legacy_sql \
'
SELECT
  CURRENT_TIMESTAMP() AS ts,

  CURRENT_DATETIME() AS dt_default,
  CURRENT_DATETIME("UTC") AS dt_utc,
  CURRENT_DATETIME("Asia/Tokyo") AS dt_jst,

  CURRENT_DATE() AS d_default,
  CURRENT_DATE("UTC") AS d_utc,
  CURRENT_DATE("Asia/Tokyo") AS d_jst,

  CURRENT_TIME() AS t_default,
  CURRENT_TIME("UTC") AS t_utc,
  CURRENT_TIME("Asia/Tokyo") AS t_jst,
'

+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+
|         ts          |         dt_default         |           dt_utc           |           dt_jst           | d_default  |   d_utc    |   d_jst    |    t_default    |      t_utc      |      t_jst      |
+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+
| 2022-08-17 15:04:44 | 2022-08-18T00:04:44.341428 | 2022-08-17T15:04:44.341428 | 2022-08-18T00:04:44.341428 | 2022-08-18 | 2022-08-17 | 2022-08-18 | 00:04:44.341428 | 15:04:44.341428 | 00:04:44.341428 |
+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+

タイムスタンプは、タイムゾーンに関係なく、絶対的な時刻を表します。参考:タイムスタンプ関数でのタイムゾーンの仕組み
そのため、CURRENT_TIMESTAMP()は引数にタイムゾーンを取らず、default_time_zoneの影響も受けません。

dt_default、d_default、t_defaultの結果はAsia/Tokyoを指定したものと一致しており、良さそうです。
省略しますが、tokyoでクエリを実行しても同じ結果でした。

確認のため、default_time_zoneを指定していないosakaで実行してみます。

osaka
bq query --location=asia-northeast2 --nouse_legacy_sql \
'
SELECT
  CURRENT_TIMESTAMP() AS ts,

  CURRENT_DATETIME() AS dt_default,
  CURRENT_DATETIME("UTC") AS dt_utc,
  CURRENT_DATETIME("Asia/Tokyo") AS dt_jst,

  CURRENT_DATE() AS d_default,
  CURRENT_DATE("UTC") AS d_utc,
  CURRENT_DATE("Asia/Tokyo") AS d_jst,

  CURRENT_TIME() AS t_default,
  CURRENT_TIME("UTC") AS t_utc,
  CURRENT_TIME("Asia/Tokyo") AS t_jst,
'

+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+
|         ts          |         dt_default         |           dt_utc           |           dt_jst           | d_default  |   d_utc    |   d_jst    |    t_default    |      t_utc      |      t_jst      |
+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+
| 2022-08-17 15:06:26 | 2022-08-17T15:06:26.505344 | 2022-08-17T15:06:26.505344 | 2022-08-18T00:06:26.505344 | 2022-08-17 | 2022-08-17 | 2022-08-18 | 15:06:26.505344 | 15:06:26.505344 | 00:06:26.505344 |
+---------------------+----------------------------+----------------------------+----------------------------+------------+------------+------------+-----------------+-----------------+-----------------+

dt_default、d_default、t_defaultの結果はUTCを指定したものと一致しており、元々の挙動どおりです。

参考

タイムゾーンを使う他の関数も少し試してみました。 
bq query --location=asia-northeast1 --nouse_legacy_sql \
'
SELECT
  TIMESTAMP("2022-01-01 00:00:00+00") AS ts1,
  TIMESTAMP("2022-01-01 00:00:00+09") AS ts2,
  TIMESTAMP("2022-01-01 00:00:00") AS ts3,

  DATETIME(TIMESTAMP "2022-01-01 00:00:00+00") AS dt1,
  DATETIME(TIMESTAMP "2022-01-01 00:00:00+09") AS dt2,
  DATETIME(TIMESTAMP "2022-01-01 00:00:00") AS dt3,

  DATE(TIMESTAMP "2022-01-01 00:00:00+00") AS d1,
  DATE(TIMESTAMP "2022-01-01 00:00:00+09") AS d2,
  DATE(TIMESTAMP "2022-01-01 00:00:00") AS d3,
  DATE(DATETIME "2022-01-01 00:00:00") AS d4
'

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------+------------+
|         ts1         |         ts2         |         ts3         |         dt1         |         dt2         |         dt3         |     d1     |     d2     |     d3     |     d4     |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------+------------+
| 2022-01-01 00:00:00 | 2021-12-31 15:00:00 | 2021-12-31 15:00:00 | 2022-01-01T09:00:00 | 2022-01-01T00:00:00 | 2022-01-01T00:00:00 | 2022-01-01 | 2022-01-01 | 2022-01-01 | 2022-01-01 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------+------------+------------+------------+

TIMESTAMPの引数の文字列でタイムゾーンが指定されている場合も、default_time_zoneより優先されます。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0