はじめに

サムザップ #2 AdventCalendar 2020 の12/8の記事です。

今回はBigQueryの日時データの型について書いていきたいと思います。

困った事

BigQueryに格納している元データは、DBからCSV形式等で出力してロードしています。
サンプルとして、以下の様なCSVデータをBigQueryにテーブルスキーマ自動検出モードでロードしてみます。

BigQueryにロードした結果は以下のようになります。

プレビューで確認すると、日時の型がtimestamp型になっており、日時の後に「UTC」表示が付いています。
本来の意図としては「JST」になってほしいのですが、表示上はCSVのデータとあっているものの、タイムゾーンがUTCと表示されてしまっているので、ちょっと違和感があります。これを解決するために色々試してみたいと思います。

タイムゾーンを付けてBigQueryにロード

ロードするCSVの日時データを、タイムゾーンを付与した以下のISO 8601形式にしてロードしてみます。

BigQueryのプレビューを確認します。

CSVに記載した日時がUTC時間に変換されて格納されました。データ的にはあっているのですが、表示はあくまでもUTC表示なのでちょっと使いづらいです。
ただし、BigQueryのクエリエディタで「FORMAT_TIMESTAMP」関数を使う事で、JST表示に変換する事は出来ます。


ただし、これだとBigQueryのデータを使う側で毎回FORMAT_TIMESTAMPをしなければならないので、ちょっと面倒です。

BigQueryのテーブル定義をdatetime型で指定する

BigQueryにデータをロードする際、テーブルスキーマ自動検出モードにすると日時データがtimestamp型になってしまうので、以下の様に手動でスキーマをdatetime型にしてロードしてみます。


BigQueryのプレビューを確認してみます。

CSVに記載されている通りの日時が表示されています。datetime型はタイムゾーンを持たないので、当然と言えば当然なのですが・・・。

BigQueryのtimestamp型をdatetime型に変更したい！

既に大量のデータが格納されているBigQueryのテーブルで、日時データがtimestamp型になっている物が多数あるため、datetime型に変換したいと思いましたが、どうやら型の変更はできないようです。
BigQuery ドキュメント テーブル スキーマの変更
取れる方法としては、datetime型に変更した新たなテーブルを作って、古いテーブルから新しいテーブルへデータを移行するしかないようです。

終わりに

BigQueryのプレビュー表示で、日時の表示に「UTC」が付いている事に違和感を感じて調べ始めたのですが、既にtimestamp型で格納されてしまっているデータについては、新たなテーブルを作って入れ直しが必要なことがわかりました。ただデータ入れ直しとなると、BigQueryの検索時にかかる費用が大きくなりそうなので、現状は「UTCって表示されているけど、データ的にはJSTだよ」って解釈で使っていくしかなさそうです。
今後新たなデータ取り込みをする場合は、BigQueryのテーブル作成時に「テーブルスキーマ自動検出モード」を使わずに、手動でテーブルスキーマを指定して取り込むようにしたいと思います。

（いつかBigQueryでテーブルスキーマの型変更が出来るようにならないかな・・・）