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

記事作成のきっかけ

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つ付与して""にしてください。

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

目次

1. bq loadの基本
2. 囲み文字ありの挙動
3. 囲み文字なしの挙動
4. 囲み文字ありとなしが混在している場合の挙動
5. おわりに

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\""}"""

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

おわりに

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