趣旨
オープンデータ流行っているみたいなので乗っかりましょう！
なるべくスクラッチでお勉強がてら。
オープンデータとは
政府において、オープンデータとは、「機械判読に適したデータ形式で、二次利用が可能な利用ルールで公開されたデータ」であり「人手を多くかけずにデータの二次利用を可能とするもの」のことを言います。
総務省｜ICT利活用の促進｜オープンデータ戦略の推進より引用
色々なサービスの創造に繋げたかったり、眠っているデータの利活用を目的としているようです。
鯖江市とは
鯖江市では、ホームページで公開する情報を多方面で利用できるXML，RDFで積極的に公開する”データシティ鯖江”を目指しています。
福井県鯖江市＞データシティ鯖江（XML,RDFによるオープンデータ化の推進）より引用。
いち早くオープンデータの公開に踏み切った自治体として有名ですよね。
流れ
- XMLダウンロード
- CSV変換
- DB取り込み（JDBC）
え？ xml2csv-convとか使えばいいじゃん？
そ、それを言ったら一瞬で終わってしまう‥
1. XMLダウンロード
プログラム中で直接XMLファイルを落としてきても良いですが、
テストやデバッグがしにくいのでwgetで。
社内LANから実行する際はproxy設定に気をつけましょう。
% wget http://www3.city.sabae.fukui.jp/xml/population/population.xml
--2015-06-13 17:32:41-- http://www3.city.sabae.fukui.jp/xml/population/population.xml
長さ: 193129 (189K) [text/xml]
`population.xml' に保存中
100%[============================================================================>] 193,129 840KB/s 時間 0.2s
2015-06-13 17:32:41 (840 KB/s) - `population.xml' へ保存完了 [193129/193129]
中身は以下のような構造です
% head population.xml
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="population.xsd" generated="2014-11-20T09:45:58">
<population>
<No>1</No>
<year>2003</year>
<age>0</age>
<man>341</man>
<fem>329</fem>
</population>
# 以下、populationが延々と続きます
2. CSV変換
環境セットアップ
xmllint で完結して対応できそうですが、触ったことないのでJavaで対応します。
Dom4Jというライブラリを利用します。
Dom4JはXMLに対してxpathというクエリを実行できるライブラリです。
依存ライブラリは以下です。
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
とある理由で今回はMavenを使えなかったので、直接JARをパスに通しました。
以下の2つのJARで動きました。依存少なくて素敵！
- dom4j-1.6.1.jar
- jaxen-1.1.6.jar
コード
Stream中のtry-catchが激しく可読性を下げますが、こんな感じ。
XPathで/datarott/populationを指定し、その要素をJavaでごりっと編集しています。
全項目を取り込んでも良いですが、今回はuriを省いてNo、year、age、man、femを取り込むことにします。
@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception {
// ファイル情報
String xmlFilePath = "population.xml";
String csvFilePath = "population.csv";
String separator = ",";
String lineFeedCd = System.getProperty("line.separator");
// XPath
String xpath = "/dataroot/population";
// 検索キー
List<String> searchKeys = Arrays.asList("No", "year", "age", "man", "fem");
// XML読み込み
SAXReader reader = new SAXReader();
Document document = reader.read(xmlFilePath);
try (BufferedWriter writer = Files.newBufferedWriter(Paths.get(csvFilePath))) {
// XPathに一致するデータのみ取得
List<Element> nodes = document.selectNodes(xpath);
// populationの子要素に対してCSV1行に変換
nodes.stream().forEach(element -> {
StringJoiner joiner = new StringJoiner(separator);
searchKeys.stream().forEach(key -> {
try {
joiner.add(element.element(key).getStringValue());
} catch (Exception e) {
// omitted
}
});
try {
// ファイルに書き込み
writer.write(joiner.toString());
writer.write(lineFeedCd);
} catch (Exception e) {
// omitted
}
});
}
System.out.println("完了");
}
結果
1,2003,0,341,329
2,2003,1,366,374
3,2003,2,360,367
4,2003,3,384,372
5,2003,4,399,351
6,2003,5,380,294
7,2003,6,350,343
# 以下略
3. DB取り込み
テーブル定義
Oracle使います。
create table m_population (
no numeric(10) not null
, year numeric(4)
, age numeric(3)
, man numeric(6)
, fem numeric(6)
, constraint pk_m_population primary key(no)
)
取り込み定義
SqlLoader
OracleなんだからSqlLoaderの定義も一応書いておきます。
Oracleクライアント入っていない環境だと動きません。
OPTIONS
(
ERRORS=50
,DIRECT=TRUE
,ROWS=100000
,SKIP=0
)
LOAD DATA CHARACTERSET UTF8
TRUNCATE
PRESERVE BLANKS
INTO TABLE M_POPULATION
FIELDS TERMINATED BY X'09'
TRAILING NULLCOLS
(
NO
,YEAR
,AGE
,MAN
,FEM
)
この定義と先ほど作成したCSVファイルを引数に渡して、sqlldrコマンドを実行します。
以下の結果を見ると、1111件取り込めたようです。
>sqlldr -control m_population.ctl -data population.csv -userid webapp/webapp@localhost:1521/XE
SQL*Loader: Release 11.2.0.2.0 - Production on 日 6月 14 18:50:21 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ロードは完了しました。 - 論理レコード件数1111
スクラッチ
そもそもOracleロックインなSqlLoader使わなくても
CSV2DBのツールはたくさんありますが（と言いながら自分はAntタスクくらいしか知りません。笑）、
折角なのでJavaでも実装します。
public static void main(String[] args) throws Exception {
String csvPath = "population.csv";
String separator = ",";
// ドライバのロード
Class.forName("oracle.jdbc.driver.OracleDriver");
try (Connection con = DriverManager.getConnection("jdbc:oracle:thin:@<ホスト名>:<ポート>:<インスタンス名>", "<ユー名>", "<パスワード>");
PreparedStatement stmt = generateStatement(con);) {
try (BufferedReader reader = Files.newBufferedReader(Paths.get(csvPath));) {
reader.lines().forEach(line -> {
String[] items = line.split(separator);
IntStream.range(0, items.length).forEach(count -> {
try {
stmt.setString(count + 1, items[count]);
} catch (Exception e) {
// omitted
}
});
try {
stmt.addBatch();
} catch (Exception e) {
// omitted
}
});
}
stmt.executeBatch();
System.out.println("登録件数=" + stmt.getUpdateCount());
}
}
private static PreparedStatement generateStatement(Connection con) throws SQLException {
String sql = "insert into M_POPULATION"
+ "(NO, YEAR, AGE, MAN, FEM)"
+ "values(?, ?, ?, ?, ?)";
PreparedStatement prepareStatement = con.prepareStatement(sql);
return prepareStatement;
}
処理が終わると登録件数=1111とコンソールに出力されます。
確認
DBを検索すると正しく1111件入っているようです。
中身もそれっぽい。
SQL> select count(*)
2 from M_POPULATION
3 ;
COUNT(*)
----------
1111
SQL> select *
2 from M_POPULATION
3 where rownum <= 10
4 ;
NO YEAR AGE MAN FEM
---------- ---------- ---------- ---------- ----------
923 2012 13 407 324
924 2012 14 355 327
925 2012 15 341 358
926 2012 16 369 367
927 2012 17 350 357
928 2012 18 350 314
929 2012 19 344 337
930 2012 20 325 320
931 2012 21 323 343
932 2012 22 312 323
10行が選択されました。
おしまい
- その辺のツール使えば一瞬で終わりそうな作業をスクラッチでやってみると意外に面倒くさいですね
- XMLよりCSV最高大好きです
- 色々なオープンデータを自システムのDB取り込んで、更に便利なサービスを生み出せたらよいですね！