はじめに

既存のデータベースからデータを抽出しBigQueryへデータ投入する機会がありましたので、そのエクスポートとインポートまでの内容をざっくり記載しています。データ分析初心者向けです。

概要

記載した通り、csvデータのエクスポートとBigQueryへのインポートする必要があり、その際に発生したエラーと解消方法について、実施した記録を纏めました。

csvファイルのインポート

ソースとなるmysqlデータのエクスポート

最初にmysqlデータを以下でエクスポートし、データをインポートしようとしました。

mysql --defaults-extra-file="${CONF}" ${MYSQL_SCHEMA} -e \
"select * from ${TABLE};" | sed -e 's/^/"/g' | sed -e 's/$/"/g' | sed -e 's/\t/","/g' \
> ${PWD}/${TABLE}-output.csv

これでエクスポートするとcsvデータは以下のような形式になります。

  • 各データはダブルクォーテーションでくくられる
  • データの区切り文字はカンマが利用される
  • カラムも含めた状態で出力され、スキーマ定義せずに Big Query へインポートできる

BigQueryへのインポート

今回 python を利用して BigQuery に取り込むには取り込もうと思うのですが、その場合にオプションや注意事項があります。今回はCSVデータになるため、こちらを参照します。
以下に初めに利用するパラメータ情報を記載します。

オプション 内容 デフォルト 設定する値 コメント
job_config.allow_quoted_newlines 改行文字を含む引用符で囲まれたデータ セクションを CSV ファイルで許可する false true 改行コードが含まれていても書き込みしたいので、許可する設定にしました。
job_config.autodetect ヘッダー行を読み込みスキーマを作成する false true 意図的にヘッダーを出力し読み込みするような方式を採用していたため、許可しています。
job_config.source_format 元データのフォーマットを指定する 指定する必要あり bigquery.SourceFormat.CSV CSVデータなのでもちろんCSVです。
job_config.quote_character データセクションを囲む引用符を指定する “ “ 今回カンマ区切りの引用符「”」での出力するsedの処理をしているため、「”」となります。
job_config.null_marker CSV ファイル内で null 値を表す文字列を指定する 空 \N 当該項目は今回のCSVに限ったものではありませんが、null値としてどういうデータがあるか、この時点ではわかっていませんでしたので、例に習い「\N」としました。
job_config.write_disposition テーブルの追加データの書き込み方法 WRITE_APPEND(テーブルへ追加) WRITE_TRUNCATE(消去してデータ書き込み) こちらもCSVに限ったものではありませんが、テーブルへの書き込みは、一新したいので、消去してデータ書き込みする形にしました。

いざこちらで試すとエラーがでました。

エラー内容

大きく引用符関連とNull関連がありました。

引用符関連

Error detected while parsing row starting at position: XX. Error: Data between close quote character (") and field separator. File: gs://バケット名/ファイル名.csv

引用符「””」でくくれていないところがありそうです。
これに関するエラー箇所は数ファイルあり、パターンとしていくつか掲載します。

改行コード問題
CRLFの改行コードのデータがあり以下のようなデータになっていました。

"齋藤
\nです。"

CSVデータエクスポート時の改行コードの置換方法を考えないといけなさそうです。

フィールド内で引用符の「”」を使っている問題
引用符の箇所以外で「”」が利用されているため、フィールドの値がうまく認識できていませんでした。

"{"100":"2300"}"

こちらも同様にCSVデータエクスポート時の置換方法かそもそも引用符を「”」である必要があるか、を考えないといけなさそうです。

NULL値関連

Could not parse '' as INT64 for field カラム名 (position XX) starting at location XXX with message 'Null marker is set to '\N' but empty value found for field カラム名 that is of type different than STRING or BYTES.' File: gs://フォルダ名/ファイル名.csv

空文字の空白をNull markerで設定した「\N」ではNullにできなかったようなメッセージがでました。

これを確認してみると値が空になっているところで失敗していましたので、Null markerの設定変更が必要そうです。

解消方法検討(ソースデータ)

解消に向けて考えます。
先のエラー内容から以下の検討が必要そうです。

  • 改行コードの置換
  • フィールド内の「”」は使えるか
  • 引用符をそもそも変える
  • null markerを変える 順番に変える内容を見ていきます。

改行コード

これは素直に元データで「\r\n」となる箇所の「\r」で改行されてそうなので「\r」を除去します

sed -e 's/\r//g'

フィールド内の「”」は使えるか

引用符で「”」を使っている場合、フィールド内の「”」はエスケープする必要があり、「””」とする必要があることがわかりました。もし引用符が「”」でない場合は「\”」でエスケープする必要があり、どちらの場合でも新たに置換が必要そうです。
ただ、引用符をそのままでフィールド内の「”」を置換するのは、特定データのみ置換すれば良いわけではないため、難しそうです。

引用符の変更

引用符自体を変えても BigQuery へインポートできそうでしたので、引用符自体を変えます。上記した改行コードの置換、「”」のエスケープも加味すると以下のようになりそうです。

"select * from ${TABLE};" | sed -e 's/"/\"/g' | sed -e 's/\r//g' \
> ${PWD}/${TABLE}-output.csv

※引用符は変えると言っても、既存のsedコマンドでの置換をなくし、フィールド単位の区切りはタブ(\t)で、引用符は無い状態となります。

変更前

"名","性","所属"
"ひろたか","さいとう","アイレット株式会社"

変更後

名    性    所属
ひろたか    さいとう    アイレット株式会社

これによって元データ上は解消できそうな内容になりましたので、BigQueryで読み込む側の変更点はないか、確認します。

解消方法検討(BigQuery)

先に記載したパラメータの値を変更します。

オプション 内容 デフォルト 設定する値 コメント
job_config.allow_quoted_newlines 改行文字を含む引用符で囲まれたデータ セクションを CSV ファイルで許可する false true 改行コードが含まれていても書き込みしたいので、許可する設定にしました。
job_config.autodetect ヘッダー行を読み込みスキーマを作成する false true 意図的にヘッダーを出力し読み込みするような方式を採用していたため、許可しています。
job_config.source_format 元データのフォーマットを指定する 指定する必要あり bigquery.SourceFormat.CSV CSVデータなのでもちろんCSVです。
job_config.quote_character データセクションを囲む引用符を指定する “ \t 今回カンマ区切りの引用符はそのまま置換せずに「\t」での出力する処理に変更しているため、「\t」へ変更となります。
job_config.null_marker CSV ファイル内で null 値を表す文字列を指定する 空 空 元々空文字も扱えているものはありましたがエラー内容に従いデフォルトの「」に変更しました。
job_config.write_disposition テーブルの追加データの書き込み方法 WRITE_APPEND(テーブルへ追加) WRITE_TRUNCATE(消去してデータ書き込み) こちらもCSVに限ったものではありませんが、テーブルへの書き込みは、一新したいので、消去してデータ書き込みする形にしました。

このようにすることで、例で挙げていた値が正常にインポートすることができました。

齋藤\nです。

{\"100\":\"2300\"}

※エスケープ文字はBigQueryのプレビュー上は見えていません

所感

インフラの業務の中でこういった業務に関わる機会は多くないので貴重な体験でした。
今回のケースは難しいケースではなく容易なケースであり、わかってしまえば大したことないですが、元データの置換処理で行われていた引用符やフィールドの区切り文字を、初めのものから変更する、という発想に至ったり、良い刺激になったかなと思っています。
また、こういったレベル感のものであればインフラ業務に関わる人間でも行えることがわかり、自身としてももっと踏み込んでみたいな、と思いました。