前回の記事からの続きです。
ビューを作成
前回までに、平成22年の国勢調査における人口増減に関する統計データを取得するところまでいきました。今回はSQLを使って統計データの集計をしていきます。
まずデータをSQLで扱いやすくするために、使用するデータはビューとして定義しておくことにします。 「CREATE VIEW」句を使用しますが、ビューの名前は「dfs.tmp.`ビュー名`」のようになっています。dfs.tmpはスキーマの名前で、ファイルシステムのテンポラリディレクトリにビューの定義ファイルが保存されることを意味しています。ここでは地域名を取り出すためのビューと、統計データを取り出すためのビューを定義します。
0: jdbc:drill:zk=local> 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.CLASS_OBJ[2].CLASS) data . . . . . . . . . . . > FROM . . . . . . . . . . . > dfs.`/tmp/stats_data_modified.json` f . . . . . . . . . . . > ) t; +-------+------------------------------------------------------------+ | ok | summary | +-------+------------------------------------------------------------+ | true | View 'area_info' created successfully in 'dfs.tmp' schema | +-------+------------------------------------------------------------+ 1 row selected (0.103 seconds)
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.`VALUE`) data . . . . . . . . . . . > FROM . . . . . . . . . . . > dfs.`/tmp/stats_data_modified.json` f . . . . . . . . . . . > ) t; +-------+-------------------------------------------------------------+ | ok | summary | +-------+-------------------------------------------------------------+ | true | View 'stats_data' created successfully in 'dfs.tmp' schema | +-------+-------------------------------------------------------------+ 1 row selected (0.155 seconds)
ビューの定義ファイルは/tmpに「.view.drill」という拡張子のついたJSONファイルとして保存されています。
$ ls /tmp area_info.view.drill stats_data_modified.view.drill
ビューの内容を確認してみます。
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)
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)
検索・集計する
さて、ここまで来たら、あとはもう普通のSQLです。まずは市区町村の人口増加率トップ10を出してみましょう。
下記のクエリの条件の部分では、表章項目コードが102（＝人口）、人口集中地区区分を表すDIDコードが00710（＝全域）、値が「-」（＝無効）ではないレコードを取り出しています。また、ソートをする際には値をDOUBLE型にキャストして、正負を適切に比較できるようにしています。
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 10; +-----------------+-----------+ | 地域 | 増減率 | +-----------------+-----------+ | (旧 421 山古志村) | 11710 | | (旧 463 旭町) | 64.137 | | 朝日町 | 35.31066 | | 中央区 | 24.7594 | | (旧 483 谷和原村) | 22.32891 | | 御蔵島村 | 19.17808 | | 中央区 | 17.76318 | | (旧 362 木津町) | 17.5982 | | (旧 407 三雲町) | 16.44737 | | 守谷市 | 16.35382 | +-----------------+-----------+ 10 rows selected (363.228 seconds)
増減率トップの11710％増というのは何かの間違いか？と思いましたが、山古志村というのはあの平成16年の新潟県中越地震が直撃したところでしたよね。この統計データは平成17年から平成22年の間の増減率なので、平成17年時点では全村民に避難指示が出ていたものと思われます。ちなみに第2位の島根県の旧旭町の人口の急増は、平成22年に開所した刑務所の影響のようです。
次に、人口密度が1,000人／平方キロ以上で人口減少率トップ10を出してみます。人口増減率と人口密度のデータは同じstats_dataビューに入っているため、条件を変えてセルフジョインしています。
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 10; +----------------+-----------+----------+ | 地域 | 増減率 | 人口密度 | +----------------+-----------+----------+ | 西成区 | -8.13079 | 16594.8 | | (旧 564 鵜殿村) | -7.77341 | 1549.0 | | (旧 422 稲築町) | -7.35908 | 1023.6 | | 琴平町 | -7.25784 | 1178.1 | | 糸田町 | -5.86335 | 1196.1 | | 真鶴町 | -5.76084 | 1169.8 | | 大牟田市 | -5.68464 | 1516.1 | | 川西町 | -5.67909 | 1456.7 | | 芦屋町 | -5.40407 | 1345.8 | | (旧 321 赤岡町) | -5.32491 | 1918.9 | +----------------+-----------+----------+ 10 rows selected (674.226 seconds)
減少率の高い大阪府西成区では雇用減少と高齢化、三重県の旧鵜殿村は「日本一人口密度が高い村」として有名だったみたいですが人口減少の影響は大きく受けているようですね。
最後に東京都23区の人口と人口増減率を並べてみましょう。23区だけを絞り込むために、まず「千代田区」の上位地域コードを取り出し、同じ上位地域コード「13100」を持つ地域を抜き出すように条件を指定します。
0: jdbc:drill:zk=local> SELECT * FROM dfs.tmp.`area_info` a WHERE a.name = _UTF16'千代田区'; +--------+-------+--------+---------+ | code | name | level | parent | +--------+-------+--------+---------+ | 13101 | 千代田区 | 4 | 13100 | +--------+-------+--------+---------+ 1 row selected (15.284 seconds) 0: jdbc:drill:zk=local> SELECT a.name 地域, b.val 人口, c.val 増減率 . . . . . . . . . . . > FROM (SELECT * FROM dfs.tmp.`area_info` t WHERE t.parent = '13100') a . . . . . . . . . . . > JOIN dfs.tmp.`stats_data` b . . . . . . . . . . . > ON a.code = b.area AND b.tab = '020' AND . . . . . . . . . . . > b.did = '00710' . . . . . . . . . . . > JOIN dfs.tmp.`stats_data` c . . . . . . . . . . . > ON a.code = c.area AND c.tab = '102' AND . . . . . . . . . . . > c.did = '00710' AND c.val <> '-' . . . . . . . . . . . > ORDER BY CAST(c.val AS DOUBLE) DESC; +-------+---------+-----------+ | 地域 | 人口 | 増減率 | +-------+---------+-----------+ | 中央区 | 122762 | 24.7594 | | 豊島区 | 284678 | 13.60536 | | 千代田区 | 47115 | 12.77467 | | 港区 | 205131 | 10.36796 | | 江東区 | 460819 | 9.49851 | | 足立区 | 683426 | 9.38194 | | 文京区 | 206626 | 8.96157 | | 墨田区 | 247606 | 7.10853 | | 新宿区 | 326309 | 6.73599 | | 台東区 | 175928 | 6.50297 | | 荒川区 | 203296 | 6.32247 | | 品川区 | 365302 | 5.46979 | | 世田谷区 | 877138 | 4.27657 | | 葛飾区 | 442586 | 4.16778 | | 大田区 | 693373 | 4.16105 | | 杉並区 | 549569 | 3.96945 | | 江戸川区 | 678967 | 3.82647 | | 練馬区 | 716124 | 3.43546 | | 板橋区 | 535824 | 2.43575 | | 目黒区 | 268330 | 1.61552 | | 北区 | 335544 | 1.55321 | | 中野区 | 314750 | 1.32732 | | 渋谷区 | 204492 | 0.56951 | +-------+---------+-----------+ 23 rows selected (878.589 seconds)
東京都下では中央区の人口の伸びがダントツです。特に日本橋エリアのマンション開発が進んだ影響のようですね。豊島区の伸びは池袋近辺の中国人人口の増加によるものが大きいとのことです。
いかがでしたでしょうか。Apache Drillの強みは、どんな形式のデータが格納されているかわからない状況で、スキーマが事前に整っていなくても比較的柔軟にユーザーが思い通りにデータの探索をできることです。ぜひ手元の環境で試してみてください。
