この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、みかみです。
本エントリは、Qiita BigQuery Advent Calendar 2022 の20日目の記事です。
やりたいこと
- BigQuery のデフォルトタイムズーンを JST に変更したい
- デフォルトタイムゾーン変更後の適用範囲を確認したい
- デフォルトタイムゾーンを変更した場合の挙動を確認したい
前提
Google Cloud SDK（gcloud コマンド）の実行環境は準備済みであるものとします。
本エントリでは、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 キーのデフォルト設定も可能とのことです。
デフォルト設定の適用範囲を確認
ドキュメントによると、組織レベルまたはプロジェクトレベルで、タイムゾーン、KMS キー、クエリジョブタイムアウト時間のデフォルト値を変更可能とのことです。 また、設定変更時にはリージョンを指定する必要があるとのこと。
先ほどは東京リージョンのデフォルトタイムゾーンを変更しましたが、同じプロジェクトでも 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 で格納されています。
ドキュメント確認してみると、タイムスタンプ値にはタイムゾーン変更設定は適用されないようです。
TIMESTAMP 型データロード
先ほどは query ジョブの結果を確認しましたが、load ジョブの挙動も確認してみます。
通常 TIMESTAMP 型データは UTC で格納されます。
デフォルトタイムゾーンを 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）でパーティショニングテーブルを定義することができます。
- 取り込み時間パーティションパーティション分割テーブルを作成する ｜ BigQuery ドキュメント
- BigQuery のコストを削減するために。パーティショニングテーブルの使い方を調べてみた ｜ DevelopersIO
通常この _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 |
+---------------------+
_PARTITIONTIME にも、デフォルトタイムゾーンの変更は適用されないことが確認できました。
まとめ（所感）
- デフォルトタイムゾーンは組織レベルまたはプロジェクトレベルで変更可能
- 変更は指定したリージョンにのみ適用される
- 変更したデフォルト値は CURRENT_DATETIME() などの DATETIME 関数に適用される
- TIMESTAMP 値には変更は反映されない
占有ディスクから成るシェアードナッシングな他の DWH と違って、リソースを共有する BigQuery のアーキテクチャを連想させる挙動でした。
デフォルトタイムゾーンが変更できるようになったとはいえ適用範囲は限定的なので、オンプレミスや他のクラウドのデータベースのような感覚で使用すると、想定とは違う挙動になるケースもあるのではないかと思います。 変更を適用する場合には、十分な検証が必要だと思いました。
7
