BigQuery のデフォルトタイムゾーンを JST に変更してみた。

#Google BigQuery

#Google Cloud (GCP)

みかみ

2022.12.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

本エントリは、Qiita BigQuery Advent Calendar 2022 の20日目の記事です。

やりたいこと

BigQuery のデフォルトタイムズーンを JST に変更したい
デフォルトタイムゾーン変更後の適用範囲を確認したい
デフォルトタイムゾーンを変更した場合の挙動を確認したい

前提

Google Cloud SDK（gcloud コマンド）の実行環境は準備済みであるものとします。本エントリでは、Cloud Shell を使用しました。

Cloud Shell の使用｜ Cloud Shell ドキュメント

BigQuery や Cloud Storage など、今回使用するサービスの API は有効化済みです。

また、各設定や操作に必要な権限は付与済みです。本エントリでは、プロジェクトオーナーロールを付与したアカウントを使用しています。

デフォルトタイムゾーンを変更

ドキュメントによると、プロジェクトの現在のデフォルト設定値は INFORMATION_SCHEMA の EFFECTIVE_PROJECT_OPTIONS ビュー、または PROJECT_OPTIONS ビューで確認できるとのことです。

まずは以下のクエリを実行して、タイムゾーン変更前の東京リージョンの設定値を確認してみます。

SELECT * FROM region-asia-northeast1.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;

SELECT * FROM region-asia-northeast1.INFORMATION_SCHEMA.PROJECT_OPTIONS;

どちらのクエリも、結果は0件でした。

以下のクエリを実行して、東京リージョンのデフォルトタイムゾーンを Asia/Tokyo に変更してみます。

ALTER PROJECT `cm-da-mikami-yuki-xxxx`
SET OPTIONS (
  `region-asia-northeast1.default_time_zone` = 'Asia/Tokyo'
);

※プロジェクトIDは一部伏せ字に変更しています。

クエリが成功したようなので、再度 INFORMATION_SCHEMA で設定値を確認してみます。

SELECT * FROM region-asia-northeast1.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;

SELECT * FROM region-asia-northeast1.INFORMATION_SCHEMA.PROJECT_OPTIONS;

default_time_zone の設定が追加されました。（余談ですが、クエリエディタで SQL 実行結果のカラムの幅が変更できるようになったアップデート、便利でうれしいです。

今回はタイムゾーンを変更したため option_name が default_time_zone のレコードが追加されました。他にもクエリジョブのタイムアウト時間や暗号化に使用する KMS キーのデフォルト設定も可能とのことです。

デフォルト構成を管理する｜ BigQuery ドキュメント

デフォルト設定の適用範囲を確認

ドキュメントによると、組織レベルまたはプロジェクトレベルで、タイムゾーン、KMS キー、クエリジョブタイムアウト時間のデフォルト値を変更可能とのことです。また、設定変更時にはリージョンを指定する必要があるとのこと。

デフォルトの設定を構成する｜ BigQuery ドキュメント

先ほどは東京リージョンのデフォルトタイムゾーンを変更しましたが、同じプロジェクトでも US リージョンには設定は適用されていないのでしょうか？先ほどと同じ INFORMATION_SCHEMA で、US リージョンを指定して設定値を確認してみます。

SELECT * FROM region-us.INFORMATION_SCHEMA.EFFECTIVE_PROJECT_OPTIONS;
SELECT * FROM region-us.INFORMATION_SCHEMA.PROJECT_OPTIONS;

クエリ結果は0件でした。

本当に別リージョンではデフォルト値変更されていないか、挙動を確認してみます。

デフォルトタイムゾーン変更後の挙動を確認

CURRENT_DATETIME()

以下のクエリで現在時刻を取得してみます。

SELECT CURRENT_DATETIME()

あれ？ UTC のままです。。（画像だとわかりにくいですが、クエリ実行時間 -9h になりました。

クエリ設定からロケーションで東京を指定して、再度実行すると・・・

期待通り、現在日時を JST で取得することができました。

クエリ実行のロケーションを us-central1 に変更すると

UTC のままです。

念のため、実世界のタイムゾーンが東京と同じ、大阪（asia-northeast2）リージョンでも確認してみます。

こちらも UTC のままです。

デフォルトタイムゾーンの設定値は、変更時に指定した asia-northeast1 リージョンのみに適用されていることが確認できました。

CURRENT_TIMESTAMP()

同様に、以下のクエリで CURRENT_TIMESTAMP() の結果も確認してみます。

SELECT CURRENT_TIMESTAMP()

デフォルトタイムゾーン JST に変更したにもかかわらず、UTC の TIMESTAMP が返却されました。

東京リージョンのテーブルに格納済みの TIMESTAMP 型データはどうなるのでしょうか？

以下のクエリで CURRENT_TIMESTAMP() をテーブルに格納してみました。

create table dataset_tokyo.timestamp as (
  select CURRENT_TIMESTAMP() as now
)

テーブルデータを確認してみます。

やはり UTC で格納されています。

ドキュメント確認してみると、タイムスタンプ値にはタイムゾーン変更設定は適用されないようです。

タイムスタンプ関数でのタイムゾーンの仕組み｜ BigQuery ドキュメント

TIMESTAMP 型データロード

先ほどは query ジョブの結果を確認しましたが、load ジョブの挙動も確認してみます。

通常 TIMESTAMP 型データは UTC で格納されます。

BigQuery に日時データをロードする場合のタイムゾーンとフォーマットについて確認してみた｜ DevelopersIO

デフォルトタイムゾーンを JST に変更したら、ロードデータが JST で格納されたりしないでしょうか？

以下の CSV ファイルを用意しました。

col_timestamp,col_timestamp_with_tz
2022-12-18 00:00:00,2022-12-18T00:00:00+09:00

タイムゾーンなし/あり（ISO 8601 フォーマット）２パターンのフォーマットの日時データを、BigQuery にロードしてみます。

$ bq load --autodetect \
  --source_format=CSV \
  dataset_tokyo.table_load \
  gs://test-mikami/data_sample_tz.csv
Waiting on bqjob_r15f4c4daaca2e829_000001852499d4e5_1 ... (1s) Current status: DONE

正常にロードできたようなので、テーブルデータを確認してみます。

$ bq query --nouse_legacy_sql 'SELECT * FROM dataset_tokyo.table_load'
+---------------------+-----------------------+
|    col_timestamp    | col_timestamp_with_tz |
+---------------------+-----------------------+
| 2022-12-18 00:00:00 |   2022-12-17 15:00:00 |
+---------------------+-----------------------+

タイムゾーン付きフォーマットのデータは UTC に変換されているようです。念のため、コンソールからも確認してみます。

ロードジョブでも、やはり TIMESTAMP 型にはデフォルトタイムゾーン変更は適用されませんでした。

_PARTITIONTIME

BigQuery では、ロード時に自動付与されるロード日時（_PARTITIONTIME）でパーティショニングテーブルを定義することができます。

通常この _PARTITIONTIME は UTC で付与されるため、JST のロード日時をパーティション指定してデータ確認する場合、タイムゾーンを考慮する必要があります。

以下の SQL を実行して、テーブルを作成しました。

CREATE TABLE
  dataset_tokyo.table_partition (col_timestamp TIMESTAMP)
PARTITION BY
  _PARTITIONDATE

作成したテーブルにデータをロードした後、_PARTITIONTIME の値を確認してみます。

$ bq query --nouse_legacy_sql 'SELECT _PARTITIONTIME FROM dataset_tokyo.table_partition'
+---------------------+
|   _PARTITIONTIME    |
+---------------------+
| 2022-12-18 00:00:00 |
+---------------------+

コンソールから確認すると UTC 表記がついているのでやはり UTC で付与されていそうですが、念のため、JST の日付変更後（00:30頃）にもう一度データをロードし、_PARTITIONTIME の値を確認してみます。

$ bq query --nouse_legacy_sql 'SELECT _PARTITIONTIME FROM dataset_tokyo.table_partition'
+---------------------+
|   _PARTITIONTIME    |
+---------------------+
| 2022-12-18 00:00:00 |
| 2022-12-18 00:00:00 |
+---------------------+