鯖江市のオープンデータ（人口統計情報）をDBに登録する流れ

# 趣旨

オープンデータ流行っているみたいなので乗っかりましょう！
なるべくスクラッチでお勉強がてら。

# オープンデータとは
政府において、オープンデータとは、「機械判読に適したデータ形式で、二次利用が可能な利用ルールで公開されたデータ」であり「人手を多くかけずにデータの二次利用を可能とするもの」のことを言います。
 [総務省｜ICT利活用の促進｜オープンデータ戦略の推進](http://www.soumu.go.jp/menu_seisaku/ictseisaku/ictriyou/opendata/)より引用
 
 色々なサービスの創造に繋げたかったり、眠っているデータの利活用を目的としているようです。
 
 # 鯖江市とは
鯖江市では、ホームページで公開する情報を多方面で利用できるXML，RDFで積極的に公開する”データシティ鯖江”を目指しています。
[福井県鯖江市＞データシティ鯖江（XML,RDFによるオープンデータ化の推進）](http://www.city.sabae.fukui.jp/pageview.html?id=11552)より引用。 

いち早くオープンデータの公開に踏み切った自治体として有名ですよね。

# 流れ

1. XMLダウンロード
2. CSV変換
3. DB取り込み（JDBC）

え？ ```xml2csv-conv```とか使えばいいじゃん？
そ、それを言ったら一瞬で終わってしまう‥

# 1. XMLダウンロード

プログラム中で直接XMLファイルを落としてきても良いですが、
テストやデバッグがしにくいのでwgetで。
社内LANから実行する際はproxy設定に気をつけましょう。

```sh
% 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]
```

中身は以下のような構造です

```sh
 % 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というクエリを実行できるライブラリです。

依存ライブラリは以下です。

```xml:pom.xml
<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を取り込むことにします。

```java:Xml2Csv.java
	@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("完了");
	}
```

結果

```csv:population.csv
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使います。

```sql:create_population.sql
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クライアント入っていない環境だと動きません。

```sql:population.ctl
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件取り込めたようです。

```sh:実行結果
>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でも実装します。

```Csv2db.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:console.out
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取り込んで、更に便利なサービスを生み出せたらよいですね！