こんにちは！
分析官のMです。
最近、Cloud SDKを用いてGCS上のcsvファイルをBigQueryのテーブルとしてインポートする機会があったのですが、
思いのほか手こずってしまい、解決に1日ほど費やしてしまいました…
本記事では、Cloud SDK初心者の私が主にどこで躓いてしまったのか、備忘録的にまとめてみました。
これから同じようなことをやろうとしている方のお役に立てれば幸いです。
やろうとしたこと
GCS上には
YYYYMM > YYYYMMDD > YYYYMMDDHH.csv
のような構造でweb行動履歴情報が1時間単位でcsvファイルに格納されており、
今回はこれらすべてのcsvファイルを1つのテーブルに格納することを試みました。
各csvファイルには日本語名のヘッダーがついている状態であり、
このままではBigQueryにインポートできないので、
[英語名のスキーマをjson形式で作成]
↓
[Cloud SDKを用いてスキーマ情報が入った空テーブルを作成]
↓
[Cloud SDKを用いて各csvファイルの1行目(ヘッダー部)をスキップしながら上記テーブルにインサート]
という段取りになります。
躓きポイント1：スキーマ情報が入ったjsonファイルはローカル上に置いておく必要がある(躓き度:★☆☆)
スキーマ自体は以下のように”name(カラム名)”と”type(型)”を指定しておけばとりあえずOKです。
[{“name”: “user_id”, “type”: “string”},{“name”: “point”,”type”: “integer”}]
私は最初、このjsonファイルをgcs上に格納してから、
以下のようなコマンドを実行し、空テーブルの作成を試みました。
bq mk ^
–schema [GCS上のjsonファイルパス] ^
–table [プロジェクトID]:[データベース名].[テーブル名]
ただ、これだとエラーが返ってきます。
師(Cloud SDKの出力)曰く、
「Cannot load schema files from GCS.」
とのこと。
どうやらGCS上に格納されたjsonファイルは読み込めないようです。
公式サイト(https://cloud.google.com/bigquery/docs/reference/bq-cli-reference#bq_mk)をよくみてみると、
スキーマ情報が入ったjsonファイルはGCS上ではなくローカル上に格納しておく必要があるそうです。
あらためてjsonファイルをローカル上(今回は自分のPC)に置きなおし、
以下のようなコマンドを実行すると無事スキーマ情報が入った空テーブルが作成されました。
bq mk ^
–schema [ローカル上のjsonファイルパス] ^
–table [プロジェクトID]:[データベース名].[テーブル名]
躓きポイント2：引用符の中に改行が入っている場合は、その旨をコマンドに記述する必要がある(躓き度:★★★)
正直、ここの原因特定が前述の”躓きポイント1″よりはるかに時間がかかりました。
空テーブルの作成が終わり、いざインサートを試みるため以下のようなコマンドを実行したところ、またもや上手くいきません。
bq load ^
–source_format=CSV ^
–skip_leading_rows=1 ^
[データベース名].[対象のテーブル名] ^
[GCS上の対象csvファイルが格納されたフォルダ] ^
[ローカル上のjsonファイルパス]
師曰く、
「Missing close double quote (“) character.」
とのこと。
最初は引用符で囲まれているカラムとそうでないカラムが混在していることが原因だと考えたのですが、どうやらそうでもなさそう。
しばらく行きづまり、データをテキストエディタでぼんやり眺めていたところ以下のようなレコードを発見します。
“xxxxxxxxxx”,…,”yyyy
yyyy”
「なんかここらへんが怪しいな」と感じ、再度公式サイト(https://cloud.google.com/bigquery/docs/reference/bq-cli-reference#bq_load)を確認すると、
引用符で囲まれた改行を許可するためには”–allow_quoted_newlines”をコマンドに追記する必要があるそうです。
あらためて以下のようなコマンドを実行すると無事データがテーブルにインサートされました。
bq load ^
–source_format=CSV ^
–skip_leading_rows=1 ^
–allow_quoted_newlines ^
[データベース名].[対象のテーブル名] ^
[GCS上の対象csvファイルが格納されたフォルダ] ^
[ローカル上のjsonファイルパス]
確かにエラー内容は正しいのですが、引用符内に改行が入っていることを直接示唆しているわけではないので、
初心者にとっては気づきづらかったですw
躓きから学んだこと
今回の経験を通して、エラーが出た場合には
・公式サイトをちゃんとチェックすること
・エラー文だけでなく、時にはデータも眺めながら原因を探っていくこと
が大切だと感じました。
