BigQueryの外部テーブルでAvroを扱った際にTIMESTAMPでハマった話

はじめに

データアナリティクス事業本部のkobayashiです。

BigQueryでGoogleCloudStorageにおいたAvroファイルを外部テーブルのデータソースとして扱った際にデータ型がTIMESTAMPのカラムの扱いでハマってしまったのでその内容をまとめます。

• Cloud Storage データのクエリ  |  BigQuery  |  Google Cloud

ハマった内容

以下のようなTIMESTAMP形式を持つAvroファイルからBigQueryにテーブルを作成しようとしていました。

Avroスキーマ

{"namespace": "ga_transaction.avro",
 "type": "record",
 "name": "Root",
 "fields": [
     {'name': 'parsed_date', 'type': ['null', {'logicalType': 'timestamp-micros', 'type': 'long'}]},
     {'name': 'total_transactionRevenue', 'type': ['null', 'long']}
 ]

BigQueryのテーブルスキーマ

ハマりポイントその1

AvroファイルからBigQueryにテーブルを作成する際にはAvroファイルを読み込むとファイル内のAvroスキーマから自動的にテーブルスキーマが作成されます（ Cloud Storage からの Avro データの読み込み  |  BigQuery  |  Google Cloud ）。

その際に注意しないといけないのがBigQueryでAvroファイルからテーブルを作る際にデフォルトではlogicalTypeが無効になっている点です（Cloud Storage からの Avro データの読み込み-論理型  |  BigQuery  |  Google Cloud ）。そのため単純に以下のようなコードでテーブルを作成するとTIMESTAMP型がINTEGER型として作成されてしまい意図しない形式のテーブルが作成されていまいます。まず最初にここでハマりました。

bq load \
--replace \
--source_format=AVRO \
data_set.ga_transaction \
gs://cm_koba/ga_transaction.avro

作成されたBigQueryのテーブルスキーマ

これはドキュメンをきちんと読めば書いてありましたので自分の理解不足でした。正しくはuse_avro_logical_typesフラグを付けてデータを読み込む必要があるため以下のようなコマンドにする必要がありました。

bq load \
--replace \
--source_format=AVRO \
--use_avro_logical_types \
data_set.ga_transaction \
gs://cm_koba/ga_transaction.avro

ハマリポイントその2

GoogleCloudStorageにあるAvroファイルを外部テーブルとしたいのでbq mkコマンドを使いますが(Cloud Storage データのクエリ  |  BigQuery  |  Google Cloud )、ここでもTIMESTAMP形式のデータを扱いたいのでbq mkコマンドのオプションで--use_avro_logical_typesを指定した以下のコマンドを実行しました。

bq mk \
--external_table_definition=AVRO=gs://cm_koba/ga_transaction.avro \
--use_avro_logical_types \
data_set.ga_transaction2

しかし、以下のようなエラーが出てしまいました。

FATAL Flags parsing error: Unknown command line flag 'use_avro_logical_types'
Run 'bq.py help' to get help

bq mkのドキュメントにはuse_avro_logical_typesフラグが記述されていたので（bq コマンドライン ツール リファレンス - mk-table  |  BigQuery  |  Google Cloud ）use_avro_logical_typesフラグは使えるはずですが、再度同じコマンドを実行しても返されるエラーは同じでした。そこで調べていくとIssueTrackerに関連する項目がありました。

Avro Logical Types Support for External Tables [154155364] - Visible to Public - Issue Tracker

これによると2020年4月の時点ではbq mkでuse_avro_logical_typesが使えなかったとあったので自分の環境を調べるとbqコマンドのバーションが2.0.61と古かったためバージョンアップを行いバージョンを最新バージョンまでアップデートしてみました。

-- version確認
$ bq version
This is BigQuery CLI 2.0.61

-- bqコマンドのversionアップ
$ gcloud components update

-- version再確認
$ bq version
bq 2.0.71

その状態で再度use_avro_logical_typesオプションを付けた状態のbq mkコマンドを実行すると無事にGoogleCloudStorageにおいたAvroファイルをデータソースとした外部テーブルがBigQueryに作成できました。

-- コマンド再実行
$ bq mk \
--external_table_definition=AVRO=gs://cm_koba/ga_transaction.avro \
--use_avro_logical_types \
data_set.ga_transaction2

Table 'project-name:data_set.ga_transaction2' successfully created.

-- 作成されたテーブルの確認
$ bq show data_set.ga_transaction2
Table project-name:data_set.ga_transaction2

   Last modified                   Schema                  Total Rows   Total Bytes   Expiration   Time Partitioning   Clustered Fields   Labels  
 ----------------- -------------------------------------- ------------ ------------- ------------ ------------------- ------------------ -------- 
  20 Aug 14:48:17   |- parsed_date: timestamp              EXTERNAL   1                                 
                    |- total_transactionRevenue: integer

まとめ

BigQueryの外部テーブルでAvroファイルをデータソースとする際にTIMESTAMP形式でハマった内容と解決した手順をまとめました。実行コマンドに--use_avro_logical_typesオプションを追加するという単純なことですが、改めて公式ドキュメントをきちんと読むのとツールを最新バージョンにすることは注意しないといけないと自戒しました。ただ解決するために様々なドキュメントを読み込んだので理解が深まりその点は良かったです。

最後まで読んで頂いてありがとうございました。

参考URL

• Cloud Storage からの Avro データの読み込み  |  BigQuery  |  Google Cloud
• Cloud Storage データのクエリ  |  BigQuery  |  Google Cloud
• bq コマンドライン ツール リファレンス  |  BigQuery  |  Google Cloud
• Avro Logical Types Support for External Tables [154155364] - Visible to Public - Issue Tracker