BigQueryへフィールドが一部欠落しているJSON Linesデータをエラー抑止の上で正常転送させてみた

S3からBigQueryへJSONデータを転送する際に、フィールドの欠落が発生しているレコードを含んでいた場合にエラーとなる状況について、Table側のフィールドがNullable前提ですがJSONデータ側を補正せずに対処する方法について書いてみました。

#Google BigQuery

haoyayoi

2022.01.07

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

S3からBigQueryへのJSON形式のデータ転送にてエラーが発生していました。JSON形式を使った時の制約自体は公式ドキュメントに記載されています。ただ、この内容に抵触していない筈なのに転送ができていない状況です。

まずは転送の成功を最優先としてTableの全フィールドは一旦全てNullableにしてあり、問題があるとしたらJSONデータと想定。データを確認したところフィールドの欠落が原因と察しはついたのですが、補完するのに一手間挟む必要もあります。JSONデータ側に手を加えずになんとかならないかと試していたところ対処できたので、対応した方法を書いてみました。

抵触しやすい制約を確認する

転送でエラーが発生した場合、まずはドキュメントに記載されている制約から見ることにしましょう。ありがちなものとしては以下の通り。

JSON データは改行区切りである必要があります。各 JSON オブジェクトはファイル内でそれぞれ別の行に配置されている必要があります。

これの意味するところとしては、インデントにて一レコード内に改行が挟まっているケースや、以下のように複数レコードが一行に収まっている状況ではエラーとなります。

例1

{
    "name": "test"
    "description": "sample"
}

例2

{"name":"test", "description":"sample"}{"name":"last", "description":"sample"}

正常に取り込まれるのは以下の構成です。

{"name":"test", "description":"sample"}
{"name":"last", "description":"sample"}

例1及び例2の状況を手っ取り早く編集するのであればjqにて以下のように指定します。

jq --compact-output test.json

フィールド欠損によるエラーへの対応

制約には記載がないものの、初期設定でJSON linesを転送する場合に各レコードのフィールドで過不足があるとエラーとなります。例えば以下の構成。

{"name":"test", "description":"sample"}
{"name":"last"}

対処方法としては以下の2択になります。

各レコードの欠損フィールドを補完する
欠損フィールドを許容する

1つめの方法は単純に全レコードを元にキーを網羅するか、決めておいた固定のキー一覧で抜けがあるものを補完する或いは固定キーのみになるよう補完及び削除を行う形となります。

2つめの方法はTable側でフィールドにNullable指定しておきつつ、転送時の設定で ignore_unknown_values を有効にするものです。

ignore_unknown_values についてはスキーマ定義にないフィールドが存在する場合の対処方法だと思っていたのですが、不足しているフィールドへの対処も含まれています。要はフィールド欠落を想定してTable側でフィールドにNullableを指定していても、それだけでは取り込まれません。

以下、bqにてignore_unknown_values を有効にした状態でS3からBigQueryへのデータ転送を行う構成例です。

bq mk \
--transfer_config \
--project_id=xxxxxxxxxx \
--data_source=amazon_s3 \
--display_name=transfer_name \
--target_dataset=demonstration \
--params='{
"destination_table_name_template":"accounts",
"data_path":"s3://example/accounts.json",
"access_key_id":"XXXXXXXXXXXX",
"secret_access_key":"XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
"file_format":"JSON",
"ignore_unknown_values": "true"}'

オプションにて no_auto_scheduling を指定していないため、以降は24時間おきに実行されることになります。