bq loadのCSV取込について、囲み文字や改行を含むデータ値の挙動を確認してみた #BigQuery

記事作成のきっかけ

GCSにputされるファイルを、サーバーレスでBigQueryに取り込む方法の一つとしてbq loadを検討したため。

記事の対象者

改行コード等があるデータをbq loadで取り込みたい方
bq loadで取り込むファイルを用意する方

結論

改行コード等があるデータをbq loadで取り込みたい方へ
データ値を"で囲み、オプションに--allow_quoted_newlines=trueを設定する必要があります。

コマンド例

bq load --allow_quoted_newlines=true myDataset.myTable gs://mybucket/info.tsv

bq loadで取り込むファイルを用意する方へ
改行コード、または区切り文字があるデータは"で囲んでください。
またデータ内に”がある場合は、"を1つ付与して""にしてください。

挿入したいデータ	ファイルに記載するデータ
改行を含みます	"改行を含みます"
囲み文字"を含みます	囲み文字""を含みます

bq loadについて

BigQuery のコマンドラインツールである bqコマンドの一つで、テーブルにデータを読み込むために使用します。
詳しくは公式ドキュメントを参照してください。

コマンド例

bq load myDataset.myTable gs://mybucket/info.csv ./info_schema.json

補足

スキーマを設定しなくても、bq_loadは実行することができます。
スキーマが指定されず、--autodetect が false で、 myDataset.myTableが存在する場合は、myDataset.myTable のスキーマが使用されます。
また「myDataset.myTable」の部分を「myProject.myDataset.myTable」と指定したらエラーになりましたので、ご注意ください。

囲み文字ありの挙動

BigQueryに下記の様なデータを挿入したいと仮定します。

id	入れたいデータ値
1	Hello Wold
2	"Hello World"
3	これはCRの改行です
4	これはLFの改行です
5	これはCRLFの改行です
6	これは　タブが含まれています
7
8
9	"
10	null値
11	{"id":"12345"}
12	"{"id":"12345"}"
13	"{\"id\":\"12345\"}"

※7は空文字、8は半角スペースです。

用意するファイルは下記の様になります。
囲み文字ではなくダブルクォーテーションを取り込みたい場合は、エスケープ文字としてダブルクォーテーションを付与する必要があります。

ファイルの中身

"id"	"string_data"
"1"	"Hello World"
"2"	"""Hello World"""
"3"	"これはCRの
改行です"
"4"	"これはLFの
改行です"
"5"	"これはCRLFの
改行です"
"6"	"これはタブ	が含まれます"
"7"	""
"8"	" "
"9"	""""
"10"	"Null"
"11"	"{""id"":""12345""}"
"12"	"""{""id"":""12345""}"""
"13"	"""{\""id\"":\""12345\""}"""

上記ファイルを任意のGCSに配置してコマンド1を実行することで、BigQueryに取り込むことができます。

コマンド1

bq load --field_delimiter=tab --skip_leading_rows=1 --source_format=CSV --null_marker=Null --allow_quoted_newlines=true myDataset.myTable gs://mybucket/info.tsv

各オプションの説明

--field_delimiter=tab
区切り文字の設定です。
タブであれば、tabもしくは\tで設定可能です。
カンマであれば、,で設定可能です。
--skip_leading_rows
1行目からskipする行数の指定です。
デフォルトが0なので、ヘッダーがない場合は記述なしで取り込めます。
--source_format=CSV
フォーマットの設定です。TSVでもCSVを設定します。
--null_marker=Null
null値となる文字列の設定です。
--allow_quoted_newlines=true
データ内の改行を許容するかの設定です。
true:許容、false:不可

結果
下記の様に期待値通りに取り込むことができました。

囲み文字なしの挙動

上記のファイルの中身から囲み文字とエスケープ文字のダブルクォーテーションを削除した下記の様なファイルの中身2を取り込んでみます。

ファイルの中身2

id	string_data
1	Hello World
2	"Hello World"
3	これはCRの
改行です
4	これはLFの
改行です
5	これはCRLFの
改行です
6	これはタブ	が含まれます
7	
8	 
9	"
10	Null
11	{"id":"12345"}
12	"{"id":"12345"}"
13	"{\"id\":\"12345\"}"

コマンド2

bq load --field_delimiter=tab --skip_leading_rows=1 --source_format=CSV --null_marker=Null --allow_quoted_newlines=true --quote="" myDataset.myTable gs://mybucket/info.tsv

コマンド1にないオプションについて

--quote=""
囲み文字の設定です。デフォルトでは”であり、囲み文字のがない場合は""と設定します。

結果
下記の様なエラーが出力されます。
エラーメッセージの内容は5,7,9行目はカラムが1つしかないというものです。
また下記エラーにはありませんが、10行目はカラムが多いというエラーがでます。
基本的に、改行や区切り文字を含むデータ値の場合は囲み文字が必須のようです。

エラーメッセージ

- gs://mybucket/info.tsv: Error while reading data,
error message: CSV table references column position 1, but line
contains only 1 columns.; line_number: 5
byte_offset_to_start_of_line: 65 column_index: 1 column_name:
"string_data" column_type: STRING File:
gs://mybucket/info.tsv
- gs://mybucket/info.tsv: Error while reading data,
error message: CSV table references column position 1, but line
contains only 1 columns.; line_number: 7
byte_offset_to_start_of_line: 96 column_index: 1 column_name:
"string_data" column_type: STRING File:
gs://mybucket/info.tsv
- gs://mybucket/info.tsv: Error while reading data,
error message: CSV table references column position 1, but line
contains only 1 columns.; line_number: 9
byte_offset_to_start_of_line: 130 column_index: 1 column_name:
"string_data" column_type: STRING File:
gs://mybucket/info.tsv

次に上記ファイルの中身2の4～10行目のデータを削除して、コマンド2を実行します。

結果
下記のようにデータが期待値通りに取り込まれました。

囲み文字ありとなしが混在している場合の挙動

embulk等でDBからファイルを出力する場合、囲み文字があるデータとないデータが混在する可能性があります。
上記を想定して、下記の様なファイルの中身3に対してbq loadを実行します。
実行コマンドは、囲み文字ありの挙動で使用したコマンド1です。

ファイルの中身3

id	string_data
1	Hello World
2	"""Hello World"""
3	"これはCRの
改行です"
4	"これはLFの
改行です"
5	"これはCRLFの
改行です"
6	"これはタブ	が含まれます"
7	
8	 
9	""""
10	Null
11	"{""id"":""12345""}"
12	"""{""id"":""12345""}"""
13	"""{\""id\"":\""12345\""}"""

結果
期待値通りに取り込むことができました。
データ値に囲み文字、区切り文字、改行等が入っている場合のみにデータ値を囲む設定でも問題ないようです。

おわりに

本記事は、一個人の検討結果となります。
間違いやご指摘等あれば、コメント頂けると大変助かります。
本記事が、どなたかの役に立てたのであれば幸いです。
拝見頂き、ありがとうございました。

bq loadのCSV取込について、囲み文字や改行を含むデータ値の挙動を確認してみた