BigQueryに郵便場号データを投入してみた

BigQueryに郵便番号データを入れてみたのでそのメモを残します。

前提

今回はGoogle Cloud SDKにて提供されているコマンド(bq, gsutil)を利用してます。インストール・設定をされて無い方はこちらで入手。
インストールされてる方も最近更新が多いので最新にしておきましょう。

gcloud components update

元データの取得

まずはデータの取得です。日本郵便の郵便番号データダウンロードサイトで提供されているものを使います。

ここでは "読み仮名データの促音・拗音を小書きで表記するもの" を選択して、全国一括の.zipを取得し展開します。

curl -O http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip
unzip ken_all.zip

小書き表記でないものを必要とする（そういうデータをマッチングかけたいなど）なら、そちらを選んでから後から適宜変換するのもよいかもしれません。
他にもローマ字表記版や大口事業所個別番号データ なんてのもありますね。
(こうしたデータがバラバラに存在していることに意味はないので、そのうちすべてを結合したものを作るかもしれません。)

データのアップロード

データはShift_JISで扱いにくかったため、とりあえずUTF-8に変換。

nkf -w KEN_ALL.CSV > KEN_ALL_UTF8_20150630.CSV

ファイルが10MB以上だったので、ブラウザ上でBigQueryに直接上げるのではなく、いったんGoogle Cloud Storageに上げることになります。

というわけで今回は新規にBucketの作成し（ASIAである必要は全然ない・・・　癖です）、そこにCSVファイルをアップロードしました。

gsutil mb -l ASIA gs://sandbox-moritani-opendata/
gsutil cp ./KEN_ALL_UTF8_20150630.CSV gs://sandbox-moritani-opendata/

テーブル準備とデータ投入

今後のために自分のプロジェクト内にOpendata専用のDataSetを作ることにします。

bq mk opendata

続いて大事なテーブルです。郵便番号データの説明に各フィールドについて記載されているので、これを元にスキーマを決めることとします。（以下、サイトから抜粋）

1. 全国地方公共団体コード（JIS X0401、X0402）………　半角数字
2. （旧）郵便番号（5桁）………………………………………　半角数字
3. 郵便番号（7桁）………………………………………　半角数字
4. 都道府県名　…………　半角カタカナ（コード順に掲載）　（注1）
5. 市区町村名　…………　半角カタカナ（コード順に掲載）　（注1）
6. 町域名　………………　半角カタカナ（五十音順に掲載）　（注1）
7. 都道府県名　…………　漢字（コード順に掲載）　（注1,2）
8. 市区町村名　…………　漢字（コード順に掲載）　（注1,2）
9. 町域名　………………　漢字（五十音順に掲載）　（注1,2）
10. 一町域が二以上の郵便番号で表される場合の表示　（注3）　（「1」は該当、「0」は該当せず）
11. 小字毎に番地が起番されている町域の表示　（注4）　（「1」は該当、「0」は該当せず）
12. 丁目を有する町域の場合の表示　（「1」は該当、「0」は該当せず）
    一つの郵便番号で二以上の町域を表す場合の表示　（注5）　（「1」は該当、「0」は該当せず）
13. 更新の表示（注6）（「0」は変更なし、「1」は変更あり、「2」廃止（廃止データのみ使用））
14. 変更理由　（「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整等、「5」訂正、「6」廃止（廃止データのみ使用））

最初の８フィールドは文字列(String)のまま入れることにしました。郵便番号を数値として比較することは稀だし、データを見ると旧郵便番号は3文字しかない場合には後に半角スペース2つ足して長さ5に揃えてあったりするのでそれを尊重する形です。
必要であれば後からQueryで整形することも可能ですし。
地方公共団体コードも同様にそのまんまです。

後半はいくつか数値のフラグが続くのですがこちらは整数（INTEGER)とました。

フィールド名は残念ながら翻訳も難しいので、日本語をローマ字表記という残念な命名が主になってます。後半のフラグについては本当に名前が思いつかずもっと残念な名前になってますがご容赦を・・

CODE:STRING,
ZIP_OLD:STRING,
ZIP:STRING,
TODOUFUKEN_KANA:STRING,
SHIKUCHOSON_KANA:STRING,
CHOUIKI_KANA:STRING,
TODOUFUKEN:STRING,
SHIKUCHOSON:STRING,
CHOUIKI:STRING,
FLAG1:INTEGER,
FLAG2:INTEGER,
FLAG3:INTEGER,
FLAG4:INTEGER,
FLAG_UPDATED:INTEGER,
UPDATE_REASON:INTEGER

スキーマが決まったところでBigQueryのテーブルを作成します。以下のコマンドで一発です。

bq mk -t opendata.japan_zipcode20150630 CODE:STRING,ZIP_OLD:STRING,ZIP:STRING,TODOUFUKEN_KANA:STRING,SHIKUCHOSON_KANA:STRING,CHOUIKI_KANA:STRING,TODOUFUKEN:STRING,SHIKUCHOSON:STRING,CHOUIKI:STRING,FLAG1:INTEGER,FLAG2:INTEGER,FLAG3:INTEGER,FLAG4:INTEGER,FLAG_UPDATED:INTEGER,UPDATE_REASON:INTEGER

そしていよいよデータの取り込みです。こんな感じでGoogle Cloud Storage上のファイルを指定します。

bq load --skip_leading_rows=0 opendata.japan_zipcode20150630 gs://sandbox-moritani-opendata/KEN_ALL_UTF8_20150630.CSV

以下のように66秒後に無事終了してました。

Waiting on bqjob_r49f767ae505d0de0_0000014ec431491a_1 ... (66s) Current status: DONE

テスト

データが入ったからにはクエリを投げてみないとですね。

bq query "select count(*) as count from opendata.japan_zipcode20150630"
Waiting on bqjob_rd2f3386db499350_0000014ec43e2abc_1 ... (0s) Current status: DONE   
+--------+
| count  |
+--------+
| 123807 |
+--------+

12万行強の小さめのデータではありますが、

bq query "select count(*) as count from opendata.japan_zipcode20150630 where shikuchoson=\"世田谷区\""
Waiting on bqjob_r5352e48d846be450_0000014ec4388bef_1 ... (0s) Current status: DONE   
+-------+
| count |
+-------+
|    62 |
+-------+

世田谷区に62の郵便番号があることもわかり、これをもって郵便番号データが利用できる状態となっていることが確認できました。

最後に

もっとデータ整形が必要かもと予測してましたが意外にもあっさりでした。

郵便番号でJOINして他のデータを掛け合わせる、全国地方公共団体コードをつかって該当住所を一覧する　といった利用方法は普通にできそうですが、じつは漢字と読みのデータがそろってるのも便利かもと思いました。既存データに漢字しかないところにこのテーブルをつかって読み情報を付加すると用途によっては有用ですよね。

本当はこのテーブルを誰もが使えるように公開してしまいたいのですが、その分の課金を考えると・・・　というわけで手順紹介にとどめさせていただきます。

以上、メモでした。