スキーマフリーSQLクエリエンジンApache Drillがついにバージョン1.0になりました。
Apache Drill - Schema-free SQL for Hadoop, NoSQL and Cloud Storage
これを機会に、Drillの得意とするJSON形式の結構複雑なデータの分析を、いろいろなオープンデータを使ってやってみたいと思います。本来Hadoopのようなビッグデータ分散処理基盤で威力を発揮するDrillですが、ラップトップ環境でスタンドアローンで動かしても全く同じ使い勝手を体験できます。
準備
最初にこちらの政府統計情報 e-Statのサイトの利用登録を行います。
登録後にログインをして、ユーザー毎に割り当てられるアプリケーションIDを取得することで統計データのダウンロードができるようになります。
Apache Drillの実行にはJDK 7が必要です。LinuxかMac OSであれば次の3つのコマンドで、インストールしてDrillのフロントエンドであるSQLLineが起動するところまで行きます。Windowsの場合はこちらのページをご参考に。
$ wget http://getdrill.org/drill/download/apache-drill-1.0.0.tar.gz
$ tar -xvzf apache-drill-1.0.0.tar.gz
$ apache-drill-1.0.0/bin/drill-embedded
0: jdbc:drill:zk=local>
統計表情報の取得
本記事では、平成22年度の国勢調査の人口統計をいろいろ集計してみたいと思います。まずはデータとしてどのようなものがあるのかがわからないので、「統計表情報取得」APIを使用して「統計表」のリストを取得しましょう。必要なパラメータは自分のアプリケーションID、調査年「2010」、政府統計コードです。
提供データ | 政府統計の総合窓口(e-Stat)−API機能
によれば国勢調査の政府統計コードは「00200521」ですね。次のコマンドで統計表情報JSONファイルをダウンロードします。
$ curl -o stats_list.json "http://api.e-stat.go.jp/rest/2.0/app/json/getStatsList?appId=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX&surveyYears=2010&statsCode=00200521"
早速データの中身を見てみましょう。まずは全部をSELECT。FROM句のところには「dfs.`<ファイルの絶対パス>`」を指定します。
0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/stats_list.json` t; +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"RESULT":{"STATUS":0,"ERROR_MSG":"正常に終了しました。","DATE":"2015-05-21T19:01:17.540+09:00"},"PARAMETER":{"LANG":"J","SURVEY_YEARS":2010,"STATS_CODE":"0 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row selected (1.257 seconds)
すると、巨大な1つのフィールドにすべてのデータが入っているのがわかります（あまりにも大きいのでカットしています）。これは、JSONデータの最上位のオブジェクトのキー「GET_STATS_LIST」と値に対応しているためです。実際のJSONファイルも見てみましょう。
{
"GET_STATS_LIST":{
"RESULT":{ ← 取得結果のステータス情報
"STATUS":0,
"ERROR_MSG":"\u6B63\u5E38\u306B\u7D42\u4E86\u3057\u307E\u3057\u305F\u3002",
"DATE":"2015-05-21T19:01:17.540+09:00"
},
"PARAMETER":{ ← 取得時に指定したパラメータ
"LANG":"J",
"SURVEY_YEARS":2010,
"STATS_CODE":"00200521",
"DATA_FORMAT":"J"
},
"DATALIST_INF":{ ← 統計表のリストデータ本体
"NUMBER":356,
"RESULT_INF":{
"FROM_NUMBER":1,
"TO_NUMBER":356
},
"TABLE_INF":[ ← 統計表のリスト
{"@id":"0003033021",...},
{"@id":"0003033022",...},
...
]
}
}
}
データの詳細仕様は次のページが参考になります。
政府統計の総合窓口（e-Stat）のAPI 仕様 | 政府統計の総合窓口(e-Stat)−API機能
KVGEN()関数とFLATTEN()関数によるデータの展開
さて、では一つ下の階層にどんなキーがあるかわかりにくいので、Drill特有のKVGEN()関数とFLATTEN()関数を使って分解していきます。KVGEN()関数は、対象のオブジェクト内の
<キー>:<値>, <キー>:<値>, ...
の並びを、
[
{"key":<キー>,"value":<値>},
{"key":<キー>,"value":<値>},
...
]
というオブジェクトの配列に変換します。FLATTEN()関数は配列をレコードに展開します。ここで「GET_STATS_LIST」に対してKVGEN()関数とFLATTEN()関数を使うことで、一つ下の階層のキーと値の組がレコードとして展開されます。
0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_LIST)) FROM dfs.`/tmp/stats_list.json` t; +---------------------------------------------------------------------------------------------------------------------------------------+ | EXPR$0 | +---------------------------------------------------------------------------------------------------------------------------------------+ | {"key":"RESULT","value":{"STATUS":0,"ERROR_MSG":"正常に終了しました。","DATE":"2015-05-21T19:01:17.540+09:00","RESULT_INF":{},"TA | | {"key":"PARAMETER","value":{"LANG":"J","SURVEY_YEARS":2010,"STATS_CODE":"00200521","DATA_FORMAT":"J","RESULT_INF":{},"TABLE_INF":[]}} | | {"key":"DATALIST_INF","value":{"NUMBER":356,"RESULT_INF":{"FROM_NUMBER":1,"TO_NUMBER":356},"TABLE_INF":[{"@id":"0003033021","STAT_ | +---------------------------------------------------------------------------------------------------------------------------------------+ 3 rows selected (0.452 seconds)
これで、「RESULT」「PARAMETER」「DATALIST_INF」という3つのキーがあることがわかりますね。では、さらに階層を降りていきましょう。DATALIST_INFというのがデータ本体ですので、これを展開しようとすると
0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_LIST.DATALIST_INF)) FROM dfs.`/tmp/stats_list.json` t; Error: SYSTEM ERROR: org.apache.drill.common.exceptions.DrillRuntimeException: Mappify/kvgen does not support heterogeneous value types. All values in the input map must be of the same type. The field [`unknown`] has a differing type [minor_type: LATE mode: OPTIONAL ]. Fragment 0:0 [Error Id: e5f8f642-d03d-4d95-b2d9-e2b47289dee6 on 192.168.111.11:31010] (state=,code=0)
今度はエラーです。これは、次の階層のキー「NUMBER」「RESULT_INF」「TABLE_INF」がそれぞれ整数、オブジェクト、配列、という別々のデータ型になっているのが原因です。今のところKVGEN()関数ではこれを変換できないので、やむを得ず直接要素を指定することで次の階層に降りていくことにしましょう。
配列の要素へのアクセス
次に見たいのは「TABLE_INF」です。これは配列で、「統計表」のリストになっていると思われます。試しに配列の先頭の要素を見るには「TABLE_INF[0]」と指定します。
0: jdbc:drill:zk=local> SELECT t.GET_STATS_LIST.DATALIST_INF.TABLE_INF[0] FROM dfs.`/tmp/stats_list.json` t; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPR$0 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | {"@id":"0003033021","STAT_NAME":{"@code":"00200521","$":"国勢調査"},"GOV_ORG":{"@code":"00200","$":"総務省"},"STATISTICS_NAME":"平成22年国勢調査 速報集計 抽出速報集計","TITLE":{"@no":"00110","$":"年齢(各歳)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中位数 全国，全国市部，全国郡部"},"CYCLE":"-","SURVEY_DATE":201010,"OPEN_DATE":"2011-06-29","SMALL_AREA":0,"MAIN_CATEGORY":{"@code":"02","$":"人口・世帯"},"SUB_CATEGORY":{"@code":"01","$":"人口"},"OVERALL_TOTAL_NUMBER":1980,"UPDATED_DATE":"2011-08-02"} | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row selected (0.187 seconds)
これで統計表に関するデータの要素が分かります。
サブクエリとフィルタリング
次に、統計表ID「@id」、統計表の表題「TITLE.$」を取り出して一覧にしてみましょう。TABLE_INFは配列なのでFLATTEN()でレコードに展開して別名「table_inf」を付けておきます。また、内側をサブクエリにしてテーブルに「d」という別名を付けた上で、外側で「d.table_inf」を使って下位の要素にアクセスします。ちなみに「@」という文字は特殊文字なのでバッククォートで囲っています。
0: jdbc:drill:zk=local> SELECT . . . . . . . . . . . > d.table_inf.`@id`, . . . . . . . . . . . > d.table_inf.TITLE.$ . . . . . . . . . . . > FROM ( . . . . . . . . . . . > SELECT . . . . . . . . . . . > FLATTEN(t.GET_STATS_LIST.DATALIST_INF.TABLE_INF) table_inf . . . . . . . . . . . > FROM dfs.`/tmp/stats_list.json` t . . . . . . . . . . . > ) d; +-------------+-----------------------------------------------------------------------------------------------------------------------------------+ | EXPR$0 | EXPR$1 | +-------------+-----------------------------------------------------------------------------------------------------------------------------------+ | 0003033021 | 年齢(各歳)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中位数 全国，全国市部，全国郡部 | | 0003033022 | 年齢(５歳階級)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中位数 全国，全国市部，全国郡部，都道府県，都道府県市部，人口20万以上の市 | | 0003033023 | 配偶関係(４区分)，年齢(各歳)，男女，国籍(総数及び日本人)別15歳以上人口 全国，全国市部，全国郡部 | ... +------------+-----------------------------------------------------------------------------------------+--+ 356 rows selected (0.608 seconds)
さて、最終的には分析の対象となるデータの「統計表ID」を知らないといけないのですが、表題に「人口増減」という言葉が入っている統計表を探して、その統計表IDを取得しましょう。条件はWHERE句のLIKEで指定しますが、日本語をそのまま指定するとエラーになるので、「_UTF16'%人口増減%'」というように文字列を指定しなければいけないのが少しトリッキーなところです（詳細はこちら）。
0: jdbc:drill:zk=local> SELECT . . . . . . . . . . . > d.table_inf.`@id`, . . . . . . . . . . . > d.table_inf.TITLE.$ . . . . . . . . . . . > FROM ( . . . . . . . . . . . > SELECT . . . . . . . . . . . > FLATTEN(t.GET_STATS_LIST.DATALIST_INF.TABLE_INF) table_inf . . . . . . . . . . . > FROM dfs.`/tmp/stats_list.json` t . . . . . . . . . . . > ) d . . . . . . . . . . . > WHERE d.TABLE_INF.TITLE.$ LIKE _UTF16'%人口増減%'; +-------------+-----------------------------------------------------------------+ | EXPR$0 | EXPR$1 | +-------------+-----------------------------------------------------------------+ | 0003038586 | 人口，人口増減，面積及び人口密度 全国，市部・郡部，都道府県，市部・郡部，支庁，郡計，市区町村・旧市町村，全域・人口集中地区 | +-------------+-----------------------------------------------------------------+ 1 row selected (0.72 seconds)
はい、全国の人口増減のデータを含む統計表の統計表ID「0003038586」が取得できました。ではいよいよ、国勢調査のデータそのものをダウンロードしてみましょう、といきたいところですが、長くなってきたので次の記事で！
