JSON Table Schema から SQL の CREATE 文を生成する

JSON Schema とは違うものとして、Open Knowledge Foundation が JSON Table Schema というデータプロトコルを定義しています。エクセルなどで使うような表形式のデータ構造を定義し、機械的に使いやすくしましょう、という試みです。
Data Packaged Core Datasets でデータの作成が進められています。GDP や金の価格、CO2 排出量などの社会的なデータはもちろんなのですが、通貨コード（ISO 4217）、言語コード（ISO 639-1、639-2）、国・地域コード（ISO 3166-2）、MIME-Type など、たまに必要になるけどどこにあるんだっけ、と思うデータもまとめられています。
CSV 形式でデータが公開されていますので、既存のデータベースに取り込めばすぐに他のデータと結合して利用できます。ただ、データベースに取り込むときに CREATE 文を書くのも煩雑なので変換プログラムを作成してみました。

• skitazaki/jtsddl　 - GitHub.com

使い方やサンプル

準備：

$ go get -d -v
$ go build -o jtsddl
$ ./jtsddl --help

ISO 3166-2 のデータセット（datasets/country-list）を試してみます。カラム情報を取得し、プラスαで管理用のフィールドも追加したテーブル定義を出力します。
※ データの構造を少し変更する必要がありますので、 jq を使っています。

$ curl -LO https://raw.githubusercontent.com/datasets/country-list/master/datapackage.json
$ jq '.resources[0]' datapackage.json > country_list.json
$ ./jtsddl -t country_list country_list.json
CREATE TABLE country_list (
    _id SERIAL,
    "Name" text,
    "Code" text,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
);

当然ですが、スキーマ定義は自分で用意することも可能です。統計局ホームページ/統計に用いる標準地域コードからダウンロードできるCSVファイルに関するサンプルデータを上記レポジトリの example に置きました。制約条件を付加しておくことで、テーブル定義もそれらしくなっていると思います。

$ ./jtsddl example/address_code_jp.json
CREATE TABLE address_code_jp (
    _id SERIAL,
    "prefecture_code" varchar(2) NOT NULL,
    "municipality_code" varchar(3) NOT NULL,
    "address_code" varchar(5) NOT NULL UNIQUE,
    "prefecture_name" varchar(200) NOT NULL,
    "municipality_name_1" varchar(200),
    "municipality_name_2" varchar(200),
    "municipality_name_3" varchar(200),
    "yomigana" varchar(200),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL
);

現在は PostgreSQL をメインターゲットにしていますのでデータ型が微妙ではありますが、外部データを取り込むときに手作業で CREATE 文を書くよりはマシかなと思います。

バイナリの作成

Go言語だとクロスコンパイルが可能ですが、そのビルド環境の管理をどうしようかなと考えることがありました。イントラネットだと自前でなんとかする必要がある点が課題です。
Docker Hub の公式イメージには cross というタグがあり、これを使うと割と簡単にビルドできます。

• golang Repository - Docker Hub Registry

たとえば、Windows, MacOSX, Linux の 32bit, 64bit 環境向けにバイナリを作成するには、コンテナ内で以下のようなシェルスクリプトを実行します。必要に応じて go test も実行すると良いでしょう。

#/bin/bash
set -eux

APP=jtsddl

go get -d -v
# go test -v 2>&1 | tee test.txt
for GOOS in darwin linux windows; do
    for GOARCH in 386 amd64; do
        env GOOS=\$GOOS GOARCH=\$GOARCH go build -v -o $APP-\$GOOS-\$GOARCH
    done
done

その他

SQL 文を書き出すのではなく、GORM（Go言語製のO/Rマッパー; jinzhu/gorm） 用の構造体を書き出すのも一案かなと思いました。システムの規模感や、既存システムとの親和性に応じて出力方式を選択できると良さそうです。