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

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

#Google BigQuery
#Google Cloud (GCP)
みかみ

みかみ

facebook logohatena logotwitter logo
Clock Icon2020.07.23

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

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

やりたいこと

  • BigQuery に JST の日時データをロードしたい
  • BigQuery に日時データをロードする場合、どんなフォーマットが許容されるのか確認したい

前提

GCS に配置してある日時データを、Python クライアントライブラリで BigQuery にロードします。

Python クライアントライブラリおよび gcloud コマンド実行環境は準備済みです。

検証時には Cloud Shell を使用しました。

BigQuery に日時データをロード

BigQuery に日時データをロードする場合、日時データのフォーマットに関して、ドキュメントに以下の記載がありました。

JSON または CSV データを読み込む場合、TIMESTAMP 列のタイムスタンプ値の日付部分の区切りにはダッシュ（-）を使用し、日付は YYYY-MM-DD（年-月-日）の形式にする必要があります。タイムスタンプの時間部分 hh:mm:ss（時-分-秒）には、区切りとしてコロン（:）を使用します。

まずはドキュメント記載の通り、YYYY-MM-DD hh:mm:ss フォーマットの以下の日時データを、テーブルスキーマ自動検出モード（ autodetect ）でロードします。

col_1
2020-01-01 00:00:00
2020-06-15 09:00:00
2020-12-31 23:59:59

from google.cloud import bigquery
import argparse

parser = argparse.ArgumentParser(description='load timestamp')
parser.add_argument('file', help='data file name')
args = parser.parse_args()
uri = "gs://test-mikami/data_timestamp/{}".format(args.file)
table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp'

client = bigquery.Client()
job_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_TRUNCATE",
    autodetect=True
)
table = bigquery.Table(table_id)

job = client.load_table_from_uri(uri, table, job_config=job_config)
print("\tStarting job {}".format(job.job_id))
job.result()
print("table: {} Loaded from uri.".format(table.table_id))

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_norm.csv
        Starting job 4562c083-f457-4d66-a31d-a5b9d4b66a1f
table: table_load_timestamp Loaded from uri.

実行結果を確認してみます。

データ型は TIMESTAMP を自動検出してくれています。

データもロードされましたが、タイムゾーンが UTC になってしまいました。

タイムゾーンが明示的に指定されていない場合は、デフォルトのタイムゾーンである UTC が使用されます。

とのことで、BigQuery の TIMESTAMP 型データをロードする場合、タイムゾーンの指定が必要なようです。

なお、Cloud Shell から bq query コマンドで SELECT したところ、取得したデータにタイムゾーンは確認できませんでした。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT * FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp` ORDER BY col_1'
Waiting on bqjob_r24c7a64fe0b6f35_000001737635037d_1 ... (0s) Current status: DONE   
+---------------------+
|        col_1        |
+---------------------+
| 2020-01-01 00:00:00 |
| 2020-06-15 09:00:00 |
| 2020-12-31 23:59:59 |
+---------------------+

JST データのみを管理コンソールを通さずに扱うのであればあまり支障はないかもしれませんが、意図せず UTC として扱われるのは潜在的な不具合につながりかねませんし、BI など他のツールを通してデータを扱う場合には、タイムゾーンは UTC とみなされる可能性が高いのではないかと思います。

BigQuery にタイムゾーン付きの日時データをロード

タイムゾーンに JST を指定したデータであれば、BigQuery にも JST で格納できるのでしょうか？

タイムゾーンを付与した以下の ISO 8601 フォーマットの日時データをロードしてみます。

col_1
2020-01-01T00:00:00+09:00
2020-06-15T09:00:00+09:00
2020-12-31T23:59:59+09:00

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_iso.csv
        Starting job e00a2a14-efbf-45e1-b076-b60c6516416c
table: table_load_timestamp Loaded from uri.

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

UTC に変換された日時データが格納されていました。

bq コマンドで SELECT してみると、

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT * FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp` ORDER BY col_1'
Waiting on bqjob_r6e41d1e422cd7086_00000173763bc004_1 ... (0s) Current status: DONE
+---------------------+
|        col_1        |
+---------------------+
| 2019-12-31 15:00:00 |
| 2020-06-15 00:00:00 |
| 2020-12-31 14:59:59 |
+---------------------+

変換後の UTC 日時のままになってしまいます。

JST で取得する場合は、FORMAT_TIMESTAMP 関数でタイムゾーンを指定する必要があります。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", col_1, "Asia/Tokyo") FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp` ORDER BY col_1'
Waiting on bqjob_rc25f4fba8fb5d13_000001737642a6f3_1 ... (0s) Current status: DONE   
+---------------------+
|         f0_         |
+---------------------+
| 2020-01-01 00:00:00 |
| 2020-06-15 09:00:00 |
| 2020-12-31 23:59:59 |
+---------------------+

では、タイムゾーン付きの他のフォーマットの日時データも正常にロードできるかどうか試してみます。

まずは、Python の datetime.strftime で '%Y-%m-%d %H:%M:%S %z' フォーマットを指定して変換しました。

ISO 8601 フォーマットではありませんが、+0900 が付与された以下の日時データをロードしてみます。

col_1
2020-01-01 00:00:00 +0900
2020-06-15 09:00:00 +0900
2020-12-31 23:59:59 +0900

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_p9.csv
        Starting job e780a6fe-1ffe-4311-86dc-be0890091f84
table: table_load_timestamp Loaded from uri.

UTC に変換されたデータが正常にロードできました。

さらに、datetime.strftime で '%Y-%m-%d %H:%M:%S %Z' フォーマット指定で変換した、JST 付きの以下のデータをロードしてみます。

col_1
2020-01-01 00:00:00 JST
2020-06-15 09:00:00 JST
2020-12-31 23:59:59 JST

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_jst.csv
        Starting job 1c5e8381-d184-4fa5-a412-cf08e9fc5a29
Traceback (most recent call last):
  File "load_timestamp.py", line 19, in <module>
    job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[
] collection for more details.

ロードエラーになりました。。 ジョブ実行結果も確認してみます。

Invalid time zone: JST; Could not parse '2020-01-01 00:00:00 JST' as TIMESTAMP とのことで、フォーマットエラーです。。

ドキュメントによると、tz database で規定されているタイムゾーン名を付与する必要があるようです。

先ほどのデータの JST 部分を Asia/Tokyo に書き換えた以下のデータをロードしてみます。

col_1
2020-01-01 00:00:00 Asia/Tokyo
2020-06-15 09:00:00 Asia/Tokyo
2020-12-31 23:59:59 Asia/Tokyo

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_tokyo.csv
        Starting job 52aceafb-024c-4b9e-a8b4-3fcadfe174ac
table: table_load_timestamp Loaded from uri.

今度は正常にロードできました。

BigQuery に TIMESTAMP 型でロード可能な日時データフォーマットを確認

日本では日時データがスラッシュ区切りで扱われることも多いと思います。ドキュメントにはハイフン区切りにする必要がある旨の記載がありましたが、他のフォーマットだとロードできないのでしょうか？

以下のデータをロードしてみます。

col_1
2020/1/1 0:00:00
2020/6/15 9:00:00
2020/12/31 23:59:59

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp.py data_timestamp_sl.csv
        Starting job b5ce8dba-19ce-4fe3-a1e0-b8207e94cce6
table: table_load_timestamp Loaded from uri.

タイムゾーン指定がないため UTC で格納されてしまっていますが、エラーになることなく TIMESTAMP 型でロードできました。

ただ、ロード時に autodetect 指定なしで、テーブルスキーマで TIMESTAMP 型を指定してロードしようとすると、フォーマットエラーが発生します。

（省略）
client = bigquery.Client()
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("col_1", bigquery.enums.SqlTypeNames.TIMESTAMP),
    ],
    write_disposition="WRITE_TRUNCATE",
    skip_leading_rows=1
)
table = bigquery.Table(table_id)

job = client.load_table_from_uri(uri, table, job_config=job_config)
（省略）

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp_with_schema.py data_timestamp_sl.csv
        Starting job fe2c23ee-b981-4218-a545-a1e025428987
Traceback (most recent call last):
  File "load_timestamp_with_schema.py", line 22, in <module>
    job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[
] collection for more details.

次に、区切り文字なしの以下のデータを autodetect でロードしてみます。

col_1
20200101000000
20200615090000
20201231235959

ロードはできましたが、TIMESTAMP 型ではなく、INTEGER として格納されました。

TIMESTAMP 型 スキーマ指定でロードしようとすると、もちろんフォーマットエラーです。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp_with_schema.py data_timestamp_nosplit.csv
        Starting job ed039b8d-3e5c-45a1-bfab-9af8616dae14
Traceback (most recent call last):
  File "load_timestamp_with_schema.py", line 22, in <module>
    job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.BadRequest: 400 Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the errors[
] collection for more details.

INTEGER として格納されたデータは、PARSE_TIMESTAMP 関数を使って TIMESTAMP 型に変換できます。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT PARSE_TIMESTAMP("%Y%m%d%H%M%S", CAST(col_1 as STRING)) as parsed FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp` ORDER BY col_1'
Waiting on bqjob_r778a341b8a400d98_000001737683c5e7_1 ... (0s) Current status: DONE   
+---------------------+
|       parsed        |
+---------------------+
| 2020-01-01 00:00:00 |
| 2020-06-15 09:00:00 |
| 2020-12-31 23:59:59 |
+---------------------+

さらに、UNIXタイムフォーマットの以下のデータを autodetect でロードしてみます。

col_1
1577804400.0
1592179200.0
1609426799.0

こちらも TIMESTAMP 型ではなく、FLOAT 型でロードされましたが、TIMESTAMP_SECONDS 関数で TIMESTAMP 型として参照することが可能です。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_SECONDS(CAST(col_1 as INT64)), "Asia/Tokyo") as formatted FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp` ORD
ER BY col_1'
Waiting on bqjob_r522ad6ef2758128_00000173768ee1c2_1 ... (0s) Current status: DONE   
+---------------------+
|      formatted      |
+---------------------+
| 2020-01-01 00:00:00 |
| 2020-06-15 09:00:00 |
| 2020-12-31 23:59:59 |
+---------------------+

タイムゾーンなしの日時データを DATETIME 型でロード

BigQuery の日時データ型には、TIMESTAMP のほかに DATETIME 型もあります。

DATETIME 型ではタイムゾーンが考慮されないため、タイムゾーン付与なしの日時データをそのままロードできます。

autodetect を指定してしまうと DATETIME 型にならないので、テーブルスキーマに DATETIME を指定して、ドキュメントに記載の通り YYYY-MM-DD hh:mm:ss フォーマットのデータをロードしてみます。

col_1
2020-01-01 00:00:00
2020-06-15 09:00:00
2020-12-31 23:59:59

（省略）
table_id = 'cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp_datetime'
（省略）
client = bigquery.Client()
job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("col_1", bigquery.enums.SqlTypeNames.DATETIME),
    ],
    write_disposition="WRITE_TRUNCATE",
    skip_leading_rows=1
)
table = bigquery.Table(table_id)

job = client.load_table_from_uri(uri, table, job_config=job_config)
（省略）

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ python3 load_timestamp_datetime.py data_timestamp_norm.csv
        Starting job 68f402d4-b1ad-4d18-a5ac-ca0257cb0b5e
table: table_load_timestamp_datetime Loaded from uri.


gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT * FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp_datetime` ORDER BY col_1'
Waiting on bqjob_r561253582cba1331_00000173769c5eac_1 ... (0s) Current status: DONE   
+---------------------+
|        col_1        |
+---------------------+
| 2020-01-01T00:00:00 |
| 2020-06-15T09:00:00 |
| 2020-12-31T23:59:59 |
+---------------------+

正常にロードできました。

ロードしたデータを TIMESTAMP 型で扱いたい場合には、CAST で簡単に変換できます。

gcp_da_user@cloudshell:~/test_timestamp (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT CAST(col_1 as TIMESTAMP) FROM `cm-da-mikami-yuki-258308.dataset_1.table_load_timestamp_datetime` ORDER BY col_1'
Waiting on bqjob_r1257fd4d1e404ec0_0000017376eecc95_1 ... (0s) Current status: DONE   
+---------------------+
|         f0_         |
+---------------------+
| 2020-01-01 00:00:00 |
| 2020-06-15 09:00:00 |
| 2020-12-31 23:59:59 |
+---------------------+

複数タイムゾーンのデータを格納する予定がなく、タイムゾーンが付与されていないフォーマットの日時データをロードする場合には、DATETIME 型でロードすることを検討しても良いかもしれません。

まとめ（所感）

他のデータベースサービスでは、データベース側のデフォルトタイムゾーンを JST に変換して運用することも多々あるかと思います。 BigQuery ではデータベース側のデフォルトタイムゾーンを変更することはできないため、TIMESTAMP 型のデータを扱う場合にはロードするデータのフォーマットや実行する SQL クエリでタイムゾーンの考慮が必要です。

また、ロードする日時データのフォーマットは、autodetect 指定でロードすればドキュメントで規定されているハイフン区切りのフォーマットではなくてもロードできることが確認できました。 ソースデータはデータ型の考慮なしで autodetect 指定でロードし、ソースデータから分析用途のマートデータを作成する SQL でタイムゾーン含めたデータ型を考慮するのも良いのではないかと思います。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

クラスメソッド データアナリティクス通信(機械学習編) – 2024年11月号

クラスメソッド データアナリティクス通信(機械学習編) – 2024年11月号

User avatar
nayu.t.s
2024.11.11
BigQueryでコミュニティUDFを利用してURLのパースを行ってみた

BigQueryでコミュニティUDFを利用してURLのパースを行ってみた

User avatar
k.uehara
2024.11.06
Cloud Data Fusion で Dataplex のデータリネージ統合を試してみた

Cloud Data Fusion で Dataplex のデータリネージ統合を試してみた

User avatar
エノカワ
2024.10.31
Gemini in BigQuery の料金体系をざっくりと紹介する

Gemini in BigQuery の料金体系をざっくりと紹介する

User avatar
hanzawa.yuya
2024.10.27
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.