Shift_JISエンコードされたローカルCSVファイルをBigQueryにインポートする!

ソースCSV

この記事では"DATA GO JP"から入手できるcsvファイルをインポートすることにした。各データの扱いについてはサイトにある利用規約を確認してほしい。
https://www.data.go.jp/

ソースの調査

ターゲットのCSVファイルにはヘッダ行が含まれており、SJISでエンコードされていた。

$ nkf opendata_lg_mani_list.csv |head -n 2
団体コード,団体名,サイトのURL1,サイトのURL2,サイトのURL3,追加日,更新日
11002,北海道札幌市,http://www.city.sapporo.jp/kikaku/ictplan/opendata/,https://data.pf-sapporo.jp/,,,

なお、BQのdocsではロードされるCSVファイルにヘッダ行がついていることがロードの前提となっている。

--format=FORMAT
    コマンドの出力の形式を指定します。次の値のいずれかを使用できます。
...
csv: ヘッダー付き CSV 形式

bq cli

既存csvファイルにデータを読み込むにはbq loadコマンドを使う。

データの場所

bq loadでは以下の場所にあるファイルをテーブルに読み込める。

Cloud Storage
bq loadを実行するPCのファイルシステム

Googleのdocsによると有効なソースCSVファイルの場所はCloud Storageのみだが、gcloud SDKに備わるファイル受け渡し機能によってbqコマンドからダイレクトにローカルの内容をBQテーブルにロードできる。bq loadのたびにわざわざCloud Storageにソースファイルをアップロードしておく必要はない。

SOURCE_DATA

    読み込むデータが含まれているファイルの Cloud Storage URI。

bq コマンドラインツールリファレンス | BigQuery | Google Cloud

このリファレンスでは、BigQuery コマンドラインツールである bq のコマンドの概要、説明、例、フラグ、引数について説明します。

スキーマ

自動判別

--autodetectフラグを使うとCSVファイルのヘッダとデータからスキーマを自動判別してくれる。

スキーマの指定

明示的にスキーマを指定する場合はjsonファイルかカンマ区切りのテキストをbq loadのスキーマフラグに渡せばよい。

--schema=/tmp/tabledef
--schema=Region:STRING,Quarter:STRING,Total_sales:INTEGER

なお、ロード先テーブルがすでに存在する場合はなにもしなくても既存のスキーマが使用される。

ロードしてみる

パラメータ	値
BQデータセット	test
テーブル	lg_mani(新規)
スキーマ	自動判別
ファイル	カレントディレクトリのopendata_lg_mani_list.csv

テーブルが存在しない場合はbqが自動で作成してくれる。

$ bq load --autodetect=true --source_format=CSV test.lg_mani ./opendata_lg_mani_list.csv
Upload complete.
Waiting on bqjob_r3354ca169110aba5_0000018534ab6d37_1 ... (1s) Current status: DONE

中身を見てみる

文字化けしてしまった。
インポート時にBigQueryがサポートするソースCSVファイルのエンコードはUTF-8だけなので、日本語文字列を含むファイルについてはインポート前に変換が必要だ。

$ bq head --max_rows=1 test.lg_mani|nkf
+-----------------+--------------+-----------------------------------------------------+-----------------------------+--------------------+---------------+-----------+
| ___c_____R____h | ___c_______  |                 __T__C__g_____URL1                  |     __T__C__g_____URL2      | __T__C__g_____URL3 | _____________ | _X_V_____ |
+-----------------+--------------+-----------------------------------------------------+-----------------------------+--------------------+---------------+-----------+
|           11002 | –kŠC��ŽD–yŽs | http://www.city.sapporo.jp/kikaku/ictplan/opendata/ | https://data.pf-sapporo.jp/ | NULL               |          NULL |      NULL |
+-----------------+--------------+-----------------------------------------------------+-----------------------------+--------------------+---------------+-----------+

ソースCSVファイルをUTF-8に変換してbq load

#既存BQテーブルを削除
$ bq rm test.lg_mani
rm: remove table '******:test.lg_mani'? (y/N) y
#エンコードをUTF-8に変換
$ nkf -S -w ./opendata_lg_mani_list.csv > ./opendata_lg_mani_list_utf_8.csv
#CSVロード
$ bq load --autodetect=true --source_format=CSV test.lg_mani ./opendata_lg_mani_list_utf_8.csv
Upload complete.
Waiting on bqjob_r76cf42149b979ff1_0000018534c1cb38_1 ... (1s) Current status: DONE   
#ロードされたテーブルの内容を確認
$ bq head --max_rows=1 test.lg_mani
+-----------------+--------------+-----------------------------------------------------+-----------------------------+------------------+-------------+-------------+
| _______________ |  _________   |                  ____________URL1                   |      ____________URL2       | ____________URL3 | __________5 | __________6 |
+-----------------+--------------+-----------------------------------------------------+-----------------------------+------------------+-------------+-------------+
|           11002 | 北海道札幌市 | http://www.city.sapporo.jp/kikaku/ictplan/opendata/ | https://data.pf-sapporo.jp/ | NULL             |        NULL |        NULL |
+-----------------+--------------+-----------------------------------------------------+-----------------------------+------------------+-------------+-------------+

文字化けは解消されたがカラム名から内容が判別できない。インポート元であるCSVのヘッダがマルチバイト文字のため、bq loadの--autodetectが仕様に沿うよう変換してくれたのだが、列名から内容がわからずこのままではテーブルを使いづらい。

$ head -n 1 ./opendata_lg_mani_list_utf_8.csv
団体コード,団体名,サイトのURL1,サイトのURL2,サイトのURL3,追加日,更新日

BigQueryテーブルカラムの命名規則

日本語は利用できない。

列名

列名には、英字（a-z、A-Z）、数字（0-9）、アンダースコア（_）のみを使用できます。先頭文字は英字またはアンダースコアにする必要があります。列名の最大長は 300 文字です。...

スキーマを与えて再度bq load

元CSVのヘッダを参考に、BigQueryテーブルカラムの命名規則に沿ったスキーマを与える。

$ bq load --autodetect=true --source_format=CSV --schema=code:STRING,name:STRING,site_url1:STRING,site_url2:STRING,site_url3:STRING,added_date:STRING,modified_date:STRING test.lg_mani  ./opendata_lg_mani_list_utf_8.csv
Upload complete.
Waiting on bqjob_r37d6f530f3f99e59_0000018534d47b78_1 ... (1s) Current status: DONEING

selectしやすい列名でbq loadできた。

$ bq head --max_rows=1 test.lg_mani
+-------+--------------+-----------------------------------------------------+-----------------------------+-----------+------------+---------------+
| code  |     name     |                      site_url1                      |          site_url2          | site_url3 | added_date | modified_date |
+-------+--------------+-----------------------------------------------------+-----------------------------+-----------+------------+---------------+
| 11002 | 北海道札幌市 | http://www.city.sapporo.jp/kikaku/ictplan/opendata/ | https://data.pf-sapporo.jp/ | NULL      | NULL       | NULL          |
+-------+--------------+-----------------------------------------------------+-----------------------------+-----------+------------+---------------+

リンク

https://cloud.google.com/bigquery/docs/reference/bq-cli-reference
https://cloud.google.com/bigquery/docs/schemas?hl=ja#schema_components