MySQL / MariaDB から BigQuery にデータをインポートする

MariaDB で構築されている DB からエクスポートしたクエリの結果を BigQuery にインポートしようとしたところ、意外と面倒だったと言う話。

何が面倒なのかというと、「データに " (ダブルクオート) が含まれていると取り込めない」と言う話で、対処法は

TSV でエクスポート
" のエスケープは \" ではなく "" で

という、これだけです。

これだけなのですが、分かるまでしんどかったです...

やりたいことは、MariaDB で実行したクエリの結果を BigQuery に取り込みたいと言うだけ。

私は MariaDB で行いましたけど、手順的には MySQL でも同じだと思います。

では、まずは正解から。

手順

1. TSVファイルのエクスポート

MariaDB でクエリを実行してファイルに保存します。

SELECT *
FROM table_name
INTO OUTFILE "/path/to/output.tsv"
FIELDS ENCLOSED BY '"' ESCAPED BY '"'

SELECT文の後に INTO OUTFILE で出力先のパスを指定すれば TSV 形式で保存されます。

が、それだけだと "(ダブルクオート) がデータ内に含まれているとエラーになるので、

ESCAPED BY '"'

でダブルクオートをエスケープするように指定します。

ここでは、

FIELDS ENCLOSED BY '"'

で、全てのフィールドをダブルクオートで囲っていますが、OPTIONALLY ENCLOSED でも良いかもしれません。

ファイルサイズが小さい場合はコンソールから直接アップロードできますが、ファイルのサイズが 10MB 以上だとエラーになるので、その場合には事前に Google Cloud Storage にアップロードしておきます。

$ gsutil cp /path/to/output.tsv gs://bucket_name/

2. TSV ファイルを BigQuery へ取り込む

コンソールから「テーブルの作成」で下記のように設定を行います。

基本的には書いてある通りに設定すればよいと思いますが、TSV の場合には下記のように読み込みファイルの形式としては CSV を選択しておいて、オプションで区切り文字を「タブ」に変更します。

ソース
　テーブルの作成元：　「アップロード」もしくは「Google Cloud Storage」を選択
　ファイル形式: 　　　「CSV」を選択

詳細オプション
　フィールド区切り文字: 　「タブ」を選択

こんな感じです。

エラーになったケース

正解を書いてみると簡単な話なのですが、何も考えずに普通に取り込もうとするとエラーになってしまいます。

と言う事で、エラーになったケースについて書いていきます。

単純な TSV を取り込む

まずは、何も考えずに TSV 形式で出力した場合。

SELECT *
FROM table_name
INTO OUTFILE "/path/to/output.tsv"

このデータを取り込もうとしたら下記のようなエラーが発生しました。

Error encountered during job execution:
Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1894; errors: 1. Please look into the error stream for more details.
Failure details:
- gs://backet_name/output.tsv: Error while
reading data, error message: Error detected while parsing row
starting at position: 82021. Error: Data between close double quote
(") and field separator.

1894行目でエラーになってます。

で、その行を見てみると下記のようなデータでした。

1894	"I" have a double quote	32	エラーになった	1

エラーからして、ダブルクオートが悪さしているように見えます。

CSV で取り込んでみる

じゃぁ、CSV なら良いのかな？っと言うのと、フィールドをダブルクオートで囲ったら大丈夫なのかな？と思い下記のように出力してみました。

SELECT *
FROM table_name
INTO OUTFILE "/path/to/output.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

TERMINATED BY ','

で , (カンマ) 区切りに、

OPTIONALLY ENCLOSED BY '"'

で、必要に応じてフィールドをダブルクオートで囲っています。

で、取り込んでみると...

Error encountered during job execution:
Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 295; errors: 1. Please look into the error stream for more details.
Failure details:
- gs://bucket_name/output.csv: Error while
reading data, error message: Error detected while parsing row
starting at position: 13164. Error: Data between close double quote
(") and field separator.

また出ました。

前回とは別な場所で発生しています。

295,"I \"have\" a double quote",1594,"エラー'になった",1

これはさっきはエラーにならなかったようなので、先ほどの TSV の時にはどういう形式だったかも見てみます。

295	I "have" a double quote	1594	エラー'になった	1

結局ダブルクオート絡みでエラーになっているように見えます。

INTO OUTFILE は、 \ (バックスラッシュ) でエスケープしているようですが、エラーになると言う事は有効ではないようです。

なんで先ほどはエラーにならず今回はエラーになったのかは謎ですが。

ちなみに、TSV 形式で

OPTIONALLY ENCLOSED BY '"'

を指定しても結果は同じです。

" でエスケープしてみる

エスケープの方法が違うのか？っと言う事で、" （ダブルクオート）でエスケープしてみます。

SELECT *
FROM table_name
INTO OUTFILE "/path/to/output.tsv"
FIELDS ESCAPED BY '"'

取り込んでみると。

Error encountered during job execution:
Error while reading data, error message: CSV table encountered too many errors, giving up. Rows: 1894; errors: 1. Please look into the error stream for more details.
Failure details:
- gs://bucket_name/output.tsv: Error
while reading data, error message: Error detected while parsing row
starting at position: 82031. Error: Data between close double quote
(") and field separator.

エラーになっている行は

1894	""I"" have a double quote	32	エラーになった	1

となっています。

ちなみに、先ほどエラーになった 295行目はと言うと

295	I ""have"" a double quote	1594	エラー'になった	1

と言う感じなのですが、こちらではエラー発生していません。

区切り文字の直後にダブルクオートが出てくるかどうかで挙動が変わるのかもしれません。

' で囲ってみる

ダブルクオートがダメならシングルクォーテーションで囲ったらどうか？

っと言う事でやってみました。

SELECT *
FROM table_name
INTO OUTFILE "/path/to/output.tsv"
FIELDS ENCLOSED BY "'"

で、取り込んでみるとエラーにはなりません！

が、シングルクォーテーションもデータとして取り込まれていますw

今までエラーになった行を見てみるとそれぞれ

'295'	'I "have" a double quote'	'1594'	'エラー\'になった'	'1'
'1894'	'"I" have a double quote'	'32'	'エラーになった'	'1'

と言う感じ。

先頭にダブルクオートが出てきてなければ、途中でダブルクオートが出てきてもエラーにならないと言う事は確実なようです。

が、先頭にダブルクオートが出てくるデータはどうするのか？

っと言う事で、ダブルクォーテーションで囲ってダブルクォーテーションでエスケープすると言う正解にたどり着きました。

分かってしまえば簡単なのですが、ちょっとハマりました。

CSV (TSV) はこの手の問題が面倒ですよね...