Apache Drill で JSON 形式の オープンデータを分析してみる

つい先日バージョン1.0がリリースされたスキーマフリーSQLクエリエンジンApache Drill。Drill登場の背景と特徴、他のSQL-on-Hadoopとの違いについて解説します。また、いろいろなオープンデータを使ってJSONデータを実際に分析する実践的な内容を盛り込みます。2015年6月10〜12日に開催されたdb tech showcase Tokyo 2015での講演資料です。

Transcript

1. ® © 2015 MapR Technologies 1 ® © 2015 MapR Technologies Apache Drill で JSON 形式の オープンデータを分析してみる 草薙 昭彦 MapR Technologies 2015 年年 6 ⽉月 11 ⽇日
2. ® © 2015 MapR Technologies 2 Apache Drill 1.0 リリース (5/19) http://drill.apache.org
3. ® © 2015 MapR Technologies 3 本⽇日のトピック •  Apache Drill 概要 •  Drill で政府統計情報 (e-Stat) を分析してみよう
4. ® © 2015 MapR Technologies 4 © 2015 MapR Technologies ® Apache Drill 概要
5. ® © 2015 MapR Technologies 5 ⾮非構造化データ 構造化データ 1980 2000 2010 1990 2020 データは２年年で倍に増える ⾮非構造化データ の割合は 企業や組織が集めたデータ の 80%以上 に増⼤大する 出典: Human-Computer Interaction & Knowledge Discovery in Complex Unstructured, Big Data 合計格納データサイズ
6. ® © 2015 MapR Technologies 6 1980 2000 2010 1990 2020 固定スキーマ DB管理理者が構造を管理理 動的／柔軟なスキーマ アプリケーションが構造を管理理 ⾮非リレーショナルデータベース リレーショナルデータベース GB〜～TB TB〜～PB データサイズ データベース データは⾮非リレーショナルデータストアに向かう データ構造 開発スタイル 構造化 構造化、半構造化、⾮非構造化 計画的（リリースサイクル＝数ヶ⽉月〜～数年年） 反復復的（リリースサイクル＝数⽇日〜～数週間）
7. ® © 2015 MapR Technologies 7 ⾮非構造化データの時代の SQL とは？ SQL の使いやすさ NoSQL の柔軟性 •  SQL •  BI (Tableau、MicroStrategy など) •  低レイテンシ •  スケーラビリティ •  スキーマ管理理なし –  HDFS (Parquet、JSON など) –  HBase –  … •  データ変換・複製なし
8. ® © 2015 MapR Technologies 8 Industry's First Schema-free SQL engine for Big Data ®
9. ® © 2015 MapR Technologies 9 セルフサービスからスキーマフリーへの拡張 迅速さ & ビジネス価値 BI のユースケース IT部⾨門主導のBI セルフサービス BI スキーマフリー データ探索索 IT部⾨門主導のBI IT部⾨門主導のBI セルフサービス BI IT部⾨門への依存なしで アナリストが主導 IT部⾨門のETLの⽀支援を 受け、アナリストが主導 IT 部⾨門が作成する レポート、スプレッドシート 1980年年代 -1990年年代 2000年年代 現在
10. ® © 2015 MapR Technologies 10 即時分析により「即断可能な」ビジネスを実現 Hadoop
 データ データ モデリング 変換 データ移動 （任意） ユーザー Hadoop
 データ ユーザー 管理理された アプローチ 探索索的な アプローチ 新しいビジネスの問いかけ ソースデータの変更更 結果を得るまでの合計時間: 数週間から数ヶ⽉月 結果を得るまでの合計時間: 数分
11. ® © 2015 MapR Technologies 11 Drill は 動的なスキーマディスカバリ をサポート •  固定スキーマ •  中央管理理されたレポジトリのスキー マを利利⽤用 (Hive メタストア) •  固定スキーマ、変化するスキーマ、 もしくはスキーマレス •  中央管理理されたレポジトリのスキーマ、 ⾃自⼰己記述型データのスキーマを利利⽤用 2 動的にスキーマを発⾒見見 事前にスキーマを宣⾔言 SCHEMA ON WRITE SCHEMA BEFORE READ SCHEMA ON THE FLY
12. ® © 2015 MapR Technologies 12 Drill のデータモデルはフレキシブル JSON BSON HBase Parquet Avro CSV TSV 動的スキーマ 固定スキーマ 複雑 フラット 柔軟性 Name! Gender! Age! Michael! M! 6! Jennifer! F! 3! {! name: {! first: Michael,! last: Smith! },! hobbies: [ski, soccer],! district: Los Altos! }! {! name: {! first: Jennifer,! last: Gates! },! hobbies: [sing],! preschool: CCLC! }! RDBMS/SQL-on-Hadoop テーブル Apache Drill テーブル 柔軟性
13. ® © 2015 MapR Technologies 13 -  サブディレクトリ -  HBase ネームスペース -  Hive データベース Drill は「SQL on Everything」を可能にする SELECT  *  FROM  dfs.yelp.`business.json`  ! ワークスペース -  パス名 -  Hive テーブル -  HBase テーブル テーブル -  DFS (Text, Parquet, JSON) -  HBase/MapR-DB -  Hive メタストアHCatalog/ - Hadoop 以外にも対応する簡単な API ストレージプラグインインスタンス
14. ® © 2015 MapR Technologies 14 アーキテクチャの概要 •  コモディティサーバのクラスタ –  各ノード上でデーモン (drillbit) が動作 •  他の実⾏行行エンジンには⾮非依存（MapReduce、Spark、Tez） –  より優れた性能と管理理性 •  ZooKeeper が逐⼀一変化するクラスタのメンバーシップ情報を管理理 –  drillbit は ZooKeeper を利利⽤用してクラスタ内の他の drillbit を⾒見見つける –  クライアントは ZooKeeper を利利⽤用して drillbit を⾒見見つける •  データ処理理単位は カラムナレコードバッチ   –  性能への影響を最⼩小限に抑えつつスキーマの柔軟性を実現
15. ® © 2015 MapR Technologies 15 Drill はデータ局所性を最⼤大限活⽤用する データソース ベストプラクティス HDFS または MapR-FS 各 DataNode 上の drillbit がアクセス HBase または MapR-DB 各 RegionServer 上の drillbit がアクセス MongoDB 各 mongod 上の drillbit がアクセス(レプリカ使⽤用時はレプリカノード上で稼働) drillbit   DataNode/ RegionServer/ mongod   drillbit   DataNode/ RegionServer/ mongod   drillbit   DataNode/ RegionServer/ mongod   ZooKeeper ZooKeeper ZooKeeper …
16. ® © 2015 MapR Technologies 16 SELECT* クエリ実⾏行行 drillbit   ZooKeeper クライアント (JDBC, ODBC, REST) 1.  drillbit を⾒見見つける （セッションごと） 3.  論論理理および物理理実⾏行行プランを作成 4.  クラスタに個別のフラグメントの実⾏行行を⾏行行わ せる（完全な分散実⾏行行） ZooKeeper ZooKeeper drillbit   drillbit   2.  drillbit  に クエリを送信 5.  クライアント に結果を返す * CTAS (CREATE TABLE AS SELECT) クエリはステップ 1〜～4 を含む
17. ® © 2015 MapR Technologies 17 drillbit 内部のコアモジュール   SQL パーサ Hive HBase 分散キャッシュ ストレージプラグイン MongoDB DFS 物理理プラン 実⾏行行エンジン 論論理理プラン オプティマイザ RPC エンドポイント
18. ® © 2015 MapR Technologies 18 © 2015 MapR Technologies ® Drill で政府統計情報 (e-Stat) を分析してみよう
19. ® © 2015 MapR Technologies 19 e-Stat API機能 http://www.e-stat.go.jp/api/
20. ® © 2015 MapR Technologies 20 e-Stat API機能 •  利利⽤用登録をする •  ログインしてユーザー毎に割り当てられるアプリケーションID を取得する •  API仕様 –  http://www.e-stat.go.jp/api/e-stat-manual/ •  提供データ –  http://www.e-stat.go.jp/api/api-data/
21. ® © 2015 MapR Technologies 21 Drill インストール •  JDK 7 が必要 •  あとは簡単 $ 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. ® © 2015 MapR Technologies 22 統計表情報の取得 •  国勢調査の政府統計コードは「00200521」 •  ⾃自分のアプリケーションID、調査年年「2010」、政府統計コード を指定して「統計表」のリストを取得 $ curl -o stats_list.json "http://api.e-stat.go.jp/rest/2.0/app/json/ getStatsList?appId=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX&surveyYea rs=2010&statsCode=00200521"
23. ® © 2015 MapR Technologies 23 中⾝身を⾒見見てみる •  FROM 句句のところは「dfs.`<ファイルの絶対パス>`」 •  JSONの最上位のオブジェクトが巨⼤大な1つのフィールドに収 まっている 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)
24. ® © 2015 MapR Technologies 24 元の JSON データ {! "GET_STATS_LIST":{! "RESULT":{ ← 取得結果のステータス! "STATUS":0,! "ERROR_MSG":"\u6B63\u5E38...",! "DATE":"2015-05-21T19:01:17..."! },! "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",...},! {"@id":"0003033023",...},! {"@id":"0003033024",...},! ...! ]! }! }! }
25. ® © 2015 MapR Technologies 25 元の JSON データ {! "GET_STATS_LIST":{! "RESULT":{! "STATUS":0,! "ERROR_MSG":"\u6B63\u5E38...",! "DATE":"2015-05-21T19:01:17..."! },! "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",...},! {"@id":"0003033023",...},! {"@id":"0003033024",...},! ...! ]! }! }! } キー 値（オブジェクト）
26. ® © 2015 MapR Technologies 26 KVGEN() 関数 •  対象のオブジェクト内の の並びを というオブジェクトの配列列に変換 [! {"key":<キー>,"value":<値>},! {"key":<キー>,"value":<値>},! ...! ] <キー>:<値>,! <キー>:<値>,! ...
27. ® © 2015 MapR Technologies 27 FLATTEN() 関数 •  配列列をレコードに展開 0: jdbc:drill:zk=local> SELECT FLATTEN(z) FROM table;! +-----------+! | z |! +-----------+! | 1 |! | 2 |! | 3 |! +-----------+! {! "z":[1,2,3]! }
28. ® © 2015 MapR Technologies 28 ⼀一つ下の階層がどうなっているかを⾒見見てみる •  GET_STATS_LISTに対してKVGEN()とFLATTEN()を適⽤用 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_LIST)) FROM dfs.`/tmp/st ats_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":"002005 21","DATA_FORMAT":"J","RESULT_INF":{},"TABLE_INF":[]}} |! | {"key":"DATALIST_INF","value":{"NUMBER":356,"RESULT_INF":{"FROM_NUMBER":1,"TO_N UMBER":356},"TABLE_INF":[{"@id":"0003033021","STAT_ |! +-------------------------------------------------------------------------------- -------------------------------------------------------+!
29. ® © 2015 MapR Technologies 29 元の JSON データ {! "GET_STATS_LIST":{! "RESULT":{! "STATUS":0,! "ERROR_MSG":"\u6B63\u5E38...",! "DATE":"2015-05-21T19:01:17..."! },! "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",...},! {"@id":"0003033023",...},! {"@id":"0003033024",...},! ...! ]! }! }! } キー 値（オブジェクト） 値（オブジェクト） キー キー 値（オブジェクト）
30. ® © 2015 MapR Technologies 30 さらにもう⼀一つ下の階層に •  データ本体であるGET_STATS_LIST.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: Ma ppify/kvgen does not support heterogeneous value types. All values in the input m ap must be of the same type. The field [`unknown`] has a differing type [minor_ty pe: LATE! mode: OPTIONAL! ].! ! Fragment 0:0! ! [Error Id: e5f8f642-d03d-4d95-b2d9-e2b47289dee6 on 192.168.111.11:31010] (state=, code=0)!
31. ® © 2015 MapR Technologies 31 なぜエラー？ {! "GET_STATS_LIST":{! "RESULT":{! "STATUS":0,! "ERROR_MSG":"\u6B63\u5E38...",! "DATE":"2015-05-21T19:01:17..."! },! "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",...},! {"@id":"0003033023",...},! {"@id":"0003033024",...},! ...! ]! }! }! } 整数 オブジェクト 配列 データ型が違うので KVGEN()では展開できない
32. ® © 2015 MapR Technologies 32 直接もう⼀一つ下の階層に •  GET_STATS_LIST.DATALIST_INF.TABLE_INFを⾒見見てみる •  TABLE_INFは配列列なので添字をつけて 0: jdbc:drill:zk=local> SELECT t.GET_STATS_LIST.DATALIST_INF.TABLE_INF[0] FROM df s.`/tmp/stats_list.json` t;! +-------------------------------------------------------------------------------- ...! ----------------------------------+! | {"@id":"0003033021","STAT_NAME":{"@code":"00200521","$":"国勢調査"},"GOV_ORG":{" @code":"00200","$":"総務省"},"STATISTICS_NAME":"平成22年国勢調査 速報集計 抽出速報集計"," TITLE":{"@no":"00110","$":"年齢(各歳)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中 位数 全国，全国市部，全国郡部"},"CYCLE":"-","SURVEY_DATE":201010,"OPEN_DATE":"2011-06-2 9","SMALL_AREA":0,"MAIN_CATEGORY":{"@code":"02","$":"人口・世帯"},"SUB_CATEGORY":{" @code":"01","$":"人口"},"OVERALL_TOTAL_NUMBER":1980,"UPDATED_DATE":"2011-08-02"} | ! +-------------------------------------------------------------------------------- ...! ----------------------------------+!
33. ® © 2015 MapR Technologies 33 2010年年の国勢調査に含まれる統計表の⼀一覧 0: jdbc:drill:zk=local> SELECT! . . . . . . . . . . . > d.table_inf.`@id`, ← 統計表ID! . . . . . . . . . . . > d.table_inf.TITLE.$ ← 統計表の表題! . . . . . . . . . . . > FROM (! . . . . . . . . . . . > SELECT! . . . . . . . . . . . > FLATTEN(t.GET_STATS_LIST.DATALIST_INF.TABLE_INF) tabl e_inf! . . . . . . . . . . . > FROM dfs.`/tmp/stats_list.json` t! . . . . . . . . . . . > ) d;! +------------+------------------------------------------------------------------+ ! | EXPR$0 | EXPR$1 | ! +------------+------------------------------------------------------------------+ ! | 0003033021 | 年齢(各歳)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中位数 全国，全国 | ! | 0003033022 | 年齢(５歳階級)，男女，国籍(総数及び日本人)別人口，平均年齢及び年齢中位数 全国， |! | 0003033023 | 配偶関係(４区分)，年齢(各歳)，男女，国籍(総数及び日本人)別15歳以上人口 全国，全 |! ...! +------------+------------------------------------------------------------------+ ! 356 rows selected (0.608 seconds)!
34. ® © 2015 MapR Technologies 34 ⼈人⼝口増減についての統計表IDを知りたい 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) tabl e_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)!
35. ® © 2015 MapR Technologies 35 メタ情報の取得 •  2010年年の国勢調査の⼈人⼝口増減のデータを含む統計表 ID「0003038586」が分かった •  ⾃自分のアプリケーションID、統計表IDを指定して表のメタ情報 を取得 $ curl -o /tmp/meta_info.json "http://api.e-stat.go.jp/rest/2.0/app/j son/getMetaInfo?appId=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&statsD ataId=0003038586"
36. ® © 2015 MapR Technologies 36 jqで整形 •  ここで取得したJSONデータは⼀一部のデータ型に問題があるので、 そのままDrillのクエリを実⾏行行するとエラーになる –  参考: マナーの悪い JSON データを jq で整形する http://nagix.hatenablog.com/entry/2015/06/03/152934 •  JSON加⼯工ツール「jq」で次のようにデータを整形して使⽤用 $ jq '(.. | objects | .CLASS | objects) |= [.]' /tmp/meta_info.json > /tmp/meta_info_modified.json
37. ® © 2015 MapR Technologies 37 メタ情報 JSON データ {! "GET_META_INFO":{! "RESULT":{ ← 取得結果のステータス! "STATUS":0,! "ERROR_MSG":"\u6B63\u5E38...",! "DATE":"2015-05-21T19:01:17..."! },! "PARAMETER":{ ← 指定したパラメータ! "LANG":"J",! "STATS_DATA_ID":"0003038586",! "DATA_FORMAT":"J"! },! "METADATA_INF":{ ← メタ情報本体! "TABLE_INF":{! "@id":"0003038586",! "STAT_NAME":{! "@code":"00200521",! "$":"\u56FD\u52E2\u8ABF..."! },! ...! },! "CLASS_INF":{! "CLASS_OBJ":[! {! "@id":"tab",! "@name":"\u8868\u7AE0...", ! "CLASS":[! {"@code":"020",...},! {"@code":"100",...},! ...! ]! },! ...! ]! }! }! }! }
38. ® © 2015 MapR Technologies 38 メタ情報の中⾝身を確認 •  GET_META_INFOに対してKVGEN()とFLATTEN()を適⽤用 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_META_INFO)) FROM dfs.`/tmp/met a_info_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"RESULT","value":{"STATUS":0,"ERROR_MSG":"正常に終了しました。","DATE":"20 |! | {"key":"PARAMETER","value":{"LANG":"J","STATS_DATA_ID":"0003038586","DATA_FO |! | {"key":"METADATA_INF","value":{"TABLE_INF":{"@id":"0003038586","STAT_NAME":{ |! +------------------------------------------------------------------------------+! 3 rows selected (0.367 seconds)!
39. ® © 2015 MapR Technologies 39 メタ情報の中⾝身を確認 •  データ本体であるGET_META_INFO.METADATA_INFを⾒見見てみ る 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_META_INFO.METADATA_INF)) FROM dfs.`/tmp/meta_info_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"TABLE_INF","value":{"@id":"0003038586","STAT_NAME":{"@code":"0020052 |! | {"key":"CLASS_INF","value":{"STAT_NAME":{},"GOV_ORG":{},"TITLE":{},"MAIN_CAT |! +------------------------------------------------------------------------------+! 2 rows selected (0.237 seconds)!
40. ® © 2015 MapR Technologies 40 メタ情報の中⾝身を確認 •  さらにGET_META_INFO.METADATA_INF.CLASS_INFを⾒見見てみ る 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_META_INFO.METADATA_INF.CLASS_I NF)) FROM dfs.`/tmp/meta_info_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"CLASS_OBJ","value":[{"@id":"tab","@name":"表章項目","CLASS":[{"@code": |! +------------------------------------------------------------------------------+! 1 row selected (5.208 seconds)!
41. ® © 2015 MapR Technologies 41 メタ情報の中⾝身を確認 •  CLASS_OBJはデータのメタ情報のオブジェクトだが、複数のオ ブジェクトが配列列として格納されている •  FLATTEN()関数のみで展開 0: jdbc:drill:zk=local> SELECT FLATTEN(t.GET_META_INFO.METADATA_INF.CLASS_INF.CLA SS_OBJ) FROM dfs.`/tmp/meta_info_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"@id":"tab","@name":"表章項目","CLASS":[{"@code":"020","@name":"人口","@level" | ! | {"@id":"cat01","@name":"全域・人口集中地区2010","CLASS":[{"@code":"00710","@name" | ! | {"@id":"area","@name":"地域（2010）","CLASS":[{"@code":"00000","@name":"全国","@l | ! | {"@id":"time","@name":"時間軸(年次)","CLASS":[{"@code":"2010000000","@name":"2 |! +------------------------------------------------------------------------------+! 4 row selected (0.148 seconds)!
42. ® © 2015 MapR Technologies 42 メタ情報の中⾝身を確認 •  メタ情報の要素はCLASSに配列列として格納されている •  CLASS_OBJに添字を付け、中⾝身のCLASSをFLATTEN()で展開 0: jdbc:drill:zk=local> SELECT FLATTEN(t.GET_META_INFO.METADATA_INF.CLASS_INF.CLA SS_OBJ[0].CLASS) FROM dfs.`/tmp/meta_info_modified.json` t;! +--------------------------------------------------------------------+! | EXPR$0 |! +--------------------------------------------------------------------+! | {"@code":"020","@name":"人口","@level":"","@unit":"人"} |! | {"@code":"100","@name":"組替人口（平成17年）","@level":"","@unit":"人"} |! | {"@code":"101","@name":"平成17年～22年の人口増減数","@level":"","@unit":"人"} |! | {"@code":"102","@name":"平成17年～22年の人口増減率","@level":"","@unit":"％"} |! | {"@code":"103","@name":"面積","@level":"","@unit":"平方km"} |! | {"@code":"104","@name":"人口密度","@level":""} |! +--------------------------------------------------------------------+! 6 rows selected (0.166 seconds)!
43. ® © 2015 MapR Technologies 43 統計データの取得 •  いよいよ統計表ID「0003038586」のデータ本体を取得 •  「⼈人⼝口」「⼈人⼝口増減率率率」「⼈人⼝口密度度」だけを対象としたいので、 パラメータとして表章項⽬目コード「020,102,104」を指定 •  jqで整形 curl -o stats_data.json "http://api.e-stat.go.jp/rest/2.0/app/json/ge tStatsData?appId=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&statsDataId =0003038586&cdTab=020,102,104"! $ jq '(.. | objects | .CLASS | objects) |= [.]' /tmp/stats_data.json > /tmp/stats_data_modified.json
44. ® © 2015 MapR Technologies 44 統計データの中⾝身を確認 •  GET_STATS_DATAに対してKVGEN()とFLATTEN()を適⽤用 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_DATA)) FROM dfs.`/tmp/st ats_data_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"RESULT","value":{"STATUS":0,"ERROR_MSG":"正常に終了しました。","DATE":"20 |! | {"key":"PARAMETER","value":{"LANG":"J","STATS_DATA_ID":"0003038586","NARROWI |! | {"key":"STATISTICAL_DATA","value":{"NARROWING_COND":{},"RESULT_INF":{"TOTAL_ |! +------------------------------------------------------------------------------+! 3 rows selected (0.495 seconds)!
45. ® © 2015 MapR Technologies 45 統計データの中⾝身を確認 •  データ本体であるGET_STATS_DATA.STATISTICAL_DATAを⾒見見 てみる 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_DATA.STATISTICAL_DATA)) FROM dfs.`/tmp/stats_data_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"RESULT_INF","value":{"TOTAL_NUMBER":19356,"FROM_NUMBER":1,"TO_NUMBER |! | {"key":"TABLE_INF","value":{"@id":"0003038586","STAT_NAME":{"@code":"0020052 |! | {"key":"CLASS_INF","value":{"STAT_NAME":{},"GOV_ORG":{},"TITLE":{},"MAIN_CAT |! | {"key":"DATA_INF","value":{"STAT_NAME":{},"GOV_ORG":{},"TITLE":{},"MAIN_CATE |! +------------------------------------------------------------------------------+! 4 rows selected (0.429 seconds)!
46. ® © 2015 MapR Technologies 46 統計データの中⾝身を確認 •  さらにGET_STATS_DATA.STATISTICAL_DATA.DATA_INFを⾒見見 てみる 0: jdbc:drill:zk=local> SELECT FLATTEN(KVGEN(t.GET_STATS_DATA.STATISTICAL_DATA.DA TA_INF)) FROM dfs.`/tmp/stats_data_modified.json` t;! +------------------------------------------------------------------------------+! | |! +------------------------------------------------------------------------------+! | {"key":"NOTE","value":[{"@char":"***","$":"当該数値がないもの"},{"@char":"-","$" |! | {"key":"VALUE","value":[{"$":"128057352","@tab":"020","@cat01":"00710","@are |! +------------------------------------------------------------------------------+! 2 rows selected (199.208 seconds)!
47. ® © 2015 MapR Technologies 47 統計データの中⾝身を確認 •  データの配列列VALUEをFLATTEN()で展開 0: jdbc:drill:zk=local> SELECT FLATTEN(t.GET_STATS_DATA.STATISTICAL_DATA.DATA_INF .`VALUE`) FROM dfs.`/tmp/stats_data_modified.json` t;! +-------------------------------------------------------------------------------- -------------------+! | EXPR$0 |! +-------------------------------------------------------------------------------- -------------------+! | {"@tab":"020","@cat01":"00710","@area":"00000","@time":"2010000000","@unit":"人 ","$":"128057352"} |! | {"@tab":"020","@cat01":"00710","@area":"00001","@time":"2010000000","@unit":"人 ","$":"116156631"} |! | ... |! +-------------------------------------------------------------------------------- -------------------+!
48. ® © 2015 MapR Technologies 48 ビューを作る •  メタ情報の地域名のデータをarea_infoというビューで定義 CREATE VIEW dfs.tmp.`area_info` AS SELECT! . . . . . . . . . . . > t.data.`@code` code,! . . . . . . . . . . . > t.data.`@name` name,! . . . . . . . . . . . > t.data.`@level` level,! . . . . . . . . . . . > t.data.`@parentCode` parent! . . . . . . . . . . . > FROM (! . . . . . . . . . . . > SELECT! . . . . . . . . . . . > FLATTEN(f.GET_STATS_DATA.STATISTICAL_DATA.CLASS_INF.C LASS_OBJ[2].CLASS) data! . . . . . . . . . . . > FROM! . . . . . . . . . . . > dfs.`/tmp/stats_data_modified.json` f! . . . . . . . . . . . > ) t;!
49. ® © 2015 MapR Technologies 49 ビューを作る •  area_infoビューを表⽰示 0: jdbc:drill:zk=local> SELECT * FROM dfs.tmp.`area_info` LIMIT 10;! +--------+--------+--------+---------+! | code | name | level | parent |! +--------+--------+--------+---------+! | 00000 | 全国 | 1 | null |! | 00001 | 全国市部 | 1 | null |! | 00002 | 全国郡部 | 1 | null |! | 01000 | 北海道 | 2 | 00000 |! | 01001 | 北海道市部 | 3 | 01000 |! | 01002 | 北海道郡部 | 3 | 01000 |! | 01100 | 札幌市 | 3 | 01000 |! | 01101 | 中央区 | 4 | 01100 |! | 01102 | 北区 | 4 | 01100 |! | 01103 | 東区 | 4 | 01100 |! +--------+--------+--------+---------+! 10 rows selected (11.902 seconds)!
50. ® © 2015 MapR Technologies 50 ビューを作る •  統計データ本体をstats_dataというビューで定義 0: jdbc:drill:zk=local> CREATE VIEW dfs.tmp.`stats_data` AS SELECT! . . . . . . . . . . . > t.data.`@tab` tab,! . . . . . . . . . . . > t.data.`@cat01` did,! . . . . . . . . . . . > t.data.`@area` area,! . . . . . . . . . . . > t.data.$ val! . . . . . . . . . . . > FROM (! . . . . . . . . . . . > SELECT! . . . . . . . . . . . > FLATTEN(f.GET_STATS_DATA.STATISTICAL_DATA.DATA_INF.`V ALUE`) data! . . . . . . . . . . . > FROM! . . . . . . . . . . . > dfs.`/tmp/stats_data_modified.json` f! . . . . . . . . . . . > ) t;!
51. ® © 2015 MapR Technologies 51 ビューを作る •  area_infoビューを表⽰示 0: jdbc:drill:zk=local> SELECT * FROM dfs.tmp.`stats_data` LIMIT 10;! +------+--------+--------+------------+! | tab | did | area | val |! +------+--------+--------+------------+! | 020 | 00710 | 00000 | 128057352 |! | 020 | 00710 | 00001 | 116156631 |! | 020 | 00710 | 00002 | 11900721 |! | 020 | 00710 | 01000 | 5506419 |! | 020 | 00710 | 01001 | 4449360 |! | 020 | 00710 | 01002 | 1057059 |! | 020 | 00710 | 01100 | 1913545 |! | 020 | 00710 | 01101 | 220189 |! | 020 | 00710 | 01102 | 278781 |! | 020 | 00710 | 01103 | 255873 |! +------+--------+--------+------------+! 10 rows selected (87.308 seconds)!
52. ® © 2015 MapR Technologies 52 ビューを作る •  ビューの定義ファイルは/tmpに「.view.drill」という拡張⼦子のつ いたJSONファイルとして保存される $ ls /tmp! area_info.view.drill! stats_data_modified.view.drill!
53. ® © 2015 MapR Technologies 53 検索索・集計する •  ここまで来たら、あとはもう普通のSQL •  ⼈人⼝口増加率率率トップ5 0: jdbc:drill:zk=local> SELECT a.name 地域, b.val 増減率! . . . . . . . . . . . > FROM dfs.tmp.`area_info` a! . . . . . . . . . . . > JOIN dfs.tmp.`stats_data` b! . . . . . . . . . . . > ON a.code = b.area AND b.tab = '102' AND! . . . . . . . . . . . > b.did = '00710' AND b.val <> '-'! . . . . . . . . . . . > ORDER BY CAST(b.val AS DOUBLE) DESC LIMIT 5;! +-----------------+-----------+! | 地域 | 増減率 |! +-----------------+-----------+! | 　　(旧 421 山古志村) | 11710 |! | 　　(旧 463 旭町) | 64.137 |! | 朝日町 | 35.31066 |! | 中央区 | 24.7594 |! | 　　(旧 483 谷和原村) | 22.32891 |! +-----------------+-----------+!
54. ® © 2015 MapR Technologies 54 検索索・集計する •  ⼈人⼝口密度度が1,000⼈人／平⽅方キロ以上で⼈人⼝口減少率率率トップ5 0: jdbc:drill:zk=local> SELECT a.name 地域, b.val 増減率, c.val 人口密度! . . . . . . . . . . . > FROM dfs.tmp.`area_info` a! . . . . . . . . . . . > JOIN dfs.tmp.`stats_data` b! . . . . . . . . . . . > ON a.code = b.area AND b.tab = '102' AND! . . . . . . . . . . . > b.did = '00710' AND b.val <> '-'! . . . . . . . . . . . > JOIN dfs.tmp.`stats_data` c! . . . . . . . . . . . > ON a.code = c.area AND c.tab = '104' AND ! . . . . . . . . . . . > c.did = '00710' AND CAST(c.val AS DOUBLE) > 1000.0! . . . . . . . . . . . > ORDER BY CAST(b.val AS DOUBLE) LIMIT 5;! +----------------+-----------+----------+! | 地域 | 増減率 | 人口密度 |! +----------------+-----------+----------+! | 西成区 | -8.13079 | 16594.8 |! | 　　(旧 564 鵜殿村) | -7.77341 | 1549.0 |! | 　　(旧 422 稲築町) | -7.35908 | 1023.6 |! | 琴平町 | -7.25784 | 1178.1 |! | 糸田町 | -5.86335 | 1196.1 |! +----------------+-----------+----------+! !
55. ® © 2015 MapR Technologies 55 まとめ •  Apache Drill で ネスト構造の JSON データを変換なしで集計で きる •  詳しい⼿手順はこちらへ –  政府統計情報 e-Stat を Apache Drill で分析してみる http://nagix.hatenablog.com/entry/2015/05/21/232526
56. ® © 2015 MapR Technologies 56 Q & A @mapr_japan maprjapan sales-jp@mapr.com お問い合わせはこちらまで MapR maprtech mapr-technologies