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

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

## 記事の対象者
+ 改行コード等があるデータをbq loadで取り込みたい方
+ bq loadで取り込むファイルを用意する方

## 結論
+ 改行コード等があるデータをbq loadで取り込みたい方へ
データ値を"で囲み、オプションに--allow_quoted_newlines=trueを設定する必要があります。
```ruby:コマンド例
bq load --allow_quoted_newlines=true myDataset.myTable gs://mybucket/info.tsv
```
+ bq loadで取り込むファイルを用意する方へ
改行コード、または区切り文字があるデータは"で囲んでください。
またデータ内に”がある場合は、"を1つ付与して""にしてください。

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

## 目次
1. bq loadの基本
1. 囲み文字ありの挙動
1. 囲み文字なしの挙動
1. 囲み文字ありとなしが混在している場合の挙動
1. おわりに
 
## bq loadについて
BigQuery のコマンドラインツールである bqコマンドの一つで、テーブルにデータを読み込むために使用します。
詳しくは[公式ドキュメント](https://cloud.google.com/bigquery/docs/reference/bq-cli-reference?hl=ja#bq_load)を参照してください。

```ruby:コマンド例
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の<br>改行です  |
|  4 | これはLFの<br>改行です  |
|  5 | これはCRLFの<br>改行です  |
|  6 | これは　タブが含まれています  |
|  7 |   |
|  8 |   |
|  9 | "  |
| 10 |  null値 |
| 11 |  {"id":"12345"} |
| 12 |  "{"id":"12345"}" |
| 13 |  "{\\"id\\":\\"12345\\"}" |

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

用意するファイルは下記の様になります。
囲み文字ではなくダブルクォーテーションを取り込みたい場合は、エスケープ文字としてダブルクォーテーションを付与する必要があります。
```ruby:ファイルの中身
"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に取り込むことができます。

```ruby:コマンド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:不可

結果
下記の様に期待値通りに取り込むことができました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3580760/c6dc4051-b914-1733-cc9f-bb22a347adbc.png)

 ### 囲み文字なしの挙動
上記のファイルの中身から囲み文字とエスケープ文字のダブルクォーテーションを削除した下記の様なファイルの中身2を取り込んでみます。
```ruby:ファイルの中身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\"}"
```

```ruby:コマンド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行目はカラムが多いというエラーがでます。
基本的に、改行や区切り文字を含むデータ値の場合は囲み文字が必須のようです。
```ruby:エラーメッセージ
- 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を実行します。

結果
下記のようにデータが期待値通りに取り込まれました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3580760/6faa2cd5-8e9d-e63b-f6b3-da3a76deee20.png)

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

結果
期待値通りに取り込むことができました。
データ値に囲み文字、区切り文字、改行等が入っている場合のみにデータ値を囲む設定でも問題ないようです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3580760/d894838c-0541-b921-a9ae-d0053228e5cd.png)


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