BigQueryの外部テーブルでAvroを扱った際にTIMESTAMPでハマった話
はじめに
データアナリティクス事業本部のkobayashiです。
BigQueryでGoogleCloudStorageにおいたAvroファイルを外部テーブルのデータソースとして扱った際にデータ型がTIMESTAMPのカラムの扱いでハマってしまったのでその内容をまとめます。
ハマった内容
以下のような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のテーブルスキーマ
| フィールド名 | 種類 |
|---|---|
| parsed_date | TIMESTAMP |
| total_transactionRevenue | INTEGER |
ハマりポイントその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のテーブルスキーマ
| フィールド名 | 種類 |
|---|---|
| parsed_date | INTEGER |
| total_transactionRevenue | INTEGER |
これはドキュメンをきちんと読めば書いてありましたので自分の理解不足でした。正しくは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オプションを追加するという単純なことですが、改めて公式ドキュメントをきちんと読むのとツールを最新バージョンにすることは注意しないといけないと自戒しました。ただ解決するために様々なドキュメントを読み込んだので理解が深まりその点は良かったです。
最後まで読んで頂いてありがとうございました。
