JSON形式のデータをもう少し深くDrill

hadoop MapR

今回は以前のエントリの続編です。

@nagixさんのエントリを参考にさせて頂きました。
ネスト構造のJSONデータにApache Drillで直接SQLクエリをかける - nagix

環境準備

Apache Drillのバージョンは0.7.0を使用します。
下記を参照してインストールしてください。
Apache Drill in 10 Minutes - Drill Wiki - Apache Software Foundation

サンプルデータ

AWSのPublic IP Address RangesがJSONで公開されていたのでこちらを使用します。
AWS Public IP Address Ranges Now Available in JSON Form | AWS Official Blog

このようなデータです。
※2014年12月にデータ取得（現在は最新版に更新されているようです）

{
  "syncToken": "1418661429",
  "createDate": "2014-12-15-16-41-01",
  "prefixes": [
    {
      "ip_prefix": "50.19.0.0/16",
      "region": "us-east-1",
      "service": "AMAZON"
    },
    {
      "ip_prefix": "54.239.98.0/24",
      "region": "us-east-1",
      "service": "AMAZON"
    },

キー："prefixes"の中にさらにキー"ip_prefix" "region" "service"があります。

Drill

データを確認します。

0: jdbc:drill:zk=local> SELECT * FROM dfs.`/tmp/ip-ranges.json`;
+------------+------------+------------+
| syncToken  | createDate |  prefixes  |
+------------+------------+------------+
| 1418661429 | 2014-12-15-16-41-01 | [{"ip_prefix":"50.19.0.0/16","region":"us-east-1","service":"AMAZON"}

キーがカラムとして表示されています。
しかしprefixesの中にあるキー："ip_prefix" "region" "service"はカラムになっていません。
このままではip_prefixやregionに対してクエリを実行出来ません。
　
こういう場合、DrillではFLATTEN()関数というものがあるそうです。
FLATTEN()関数を使ってprefixesの中にあるデータを取り出してみます。

0: jdbc:drill:zk=local> SELECT FLATTEN(prefixes) AS prefix from dfs.`/tmp/ip-ranges.json`;
+------------+
|   prefix   |
+------------+
| {"ip_prefix":"50.19.0.0/16","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"54.239.98.0/24","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"205.251.254.0/24","region":"GLOBAL","service":"AMAZON"} |
| {"ip_prefix":"75.101.128.0/17","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"54.240.192.0/22","region":"ap-southeast-2","service":"AMAZON"} |
| {"ip_prefix":"54.194.0.0/15","region":"eu-west-1","service":"AMAZON"} |
| {"ip_prefix":"54.231.224.0/21","region":"ap-northeast-1","service":"AMAZON"} |
| {"ip_prefix":"54.239.192.0/19","region":"GLOBAL","service":"AMAZON"} |
| {"ip_prefix":"54.208.0.0/15","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"54.172.0.0/15","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"50.16.0.0/15","region":"us-east-1","service":"AMAZON"} |
| {"ip_prefix":"54.239.54.0/23","region":"eu-central-1","service":"AMAZON"} |
　・  
　・  
　・

次にprefixesの中にあるキーをカラムにしてみます。

0: jdbc:drill:zk=local>  SELECT 
. . . . . . . . . . . >　 t.prefix.ip_prefix AS prefix, 
. . . . . . . . . . . >　 t.prefix.region AS region, 
. . . . . . . . . . . >　 t.prefix.service AS service 
. . . . . . . . . . . >　FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t;
+------------+------------+------------+
|   prefix   |   region   |  service   |
+------------+------------+------------+
| 50.19.0.0/16 | us-east-1  | AMAZON     |
| 54.239.98.0/24 | us-east-1  | AMAZON     |
| 205.251.254.0/24 | GLOBAL     | AMAZON     |
| 75.101.128.0/17 | us-east-1  | AMAZON     |
| 54.240.192.0/22 | ap-southeast-2 | AMAZON     |
| 54.194.0.0/15 | eu-west-1  | AMAZON     |
| 54.231.224.0/21 | ap-northeast-1 | AMAZON     |
| 54.239.192.0/19 | GLOBAL     | AMAZON     |
| 54.208.0.0/15 | us-east-1  | AMAZON     |
| 54.172.0.0/15 | us-east-1  | AMAZON     |
　・  
　・  
　・

これで様々なクエリを試せる準備が整いました。

さらにDrill

ではAWSのデータを分析してみましょう。

serviceを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.service AS service
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  GROUP BY t.prefix.service;
+------------+
|  service   |
+------------+
| AMAZON     |
| EC2        |
| ROUTE53    |
| ROUTE53_HEALTHCHECKS |
| CLOUDFRONT |
+------------+
5 rows selected (0.297 seconds)

　
'ROUTE53'で使用されているIP Rangeを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.ip_prefix AS prefix
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  WHERE t.prefix.service='ROUTE53';
+------------+
|   prefix   |
+------------+
| 205.251.192.0/21 |
+------------+
1 row selected (0.323 seconds)

　
同じように'ROUTE53_HEALTHCHECKS'で使用されているIP Rangeを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.ip_prefix AS prefix
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  WHERE t.prefix.service='ROUTE53_HEALTHCHECKS';
+------------+
|   prefix   |
+------------+
| 54.232.40.64/26 |
| 54.241.32.64/26 |
| 54.248.220.0/26 |
| 54.255.254.192/26 |
| 107.23.255.0/26 |
| 54.245.168.0/26 |
| 54.250.253.192/26 |
| 54.228.16.0/26 |
| 54.251.31.128/26 |
| 54.252.79.128/26 |
| 54.243.31.192/26 |
| 177.71.207.128/26 |
| 54.244.52.192/26 |
| 54.183.255.128/26 |
| 54.252.254.192/26 |
| 176.34.159.192/26 |
+------------+
16 rows selected (0.313 seconds)

　
regionを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.region AS region
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  GROUP BY t.prefix.region;
+------------+
|   region   |
+------------+
| us-east-1  |
| GLOBAL     |
| ap-southeast-2 |
| eu-west-1  |
| ap-northeast-1 |
| eu-central-1 |
| cn-north-1 |
| ap-southeast-1 |
| sa-east-1  |
| us-west-1  |
| us-west-2  |
| us-gov-west-1 |
+------------+
12 rows selected (0.422 seconds)

　
ap-northeast-1で使用されているIP Rangeを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.ip_prefix AS prefix
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  WHERE t.prefix.region='ap-northeast-1';
+------------+
|   prefix   |
+------------+
| 54.231.224.0/21 |
| 54.238.0.0/16 |
| 54.64.0.0/15 |
| 54.250.0.0/16 |
| 175.41.192.0/18 |
| 54.199.0.0/16 |
| 176.34.32.0/19 |
| 54.240.200.0/24 |
| 46.51.224.0/19 |
| 54.248.0.0/15 |
| 54.240.225.0/24 |
| 54.150.0.0/16 |
| 54.178.0.0/16 |
| 54.92.0.0/17 |
| 176.32.64.0/19 |
| 54.239.52.0/23 |
| 103.246.150.0/23 |
| 176.34.0.0/19 |
| 54.168.0.0/16 |
| 54.239.96.0/24 |
| 27.0.0.0/22 |
| 54.95.0.0/16 |
| 103.4.8.0/21 |
| 54.238.0.0/16 |
| 54.64.0.0/15 |
| 54.250.0.0/16 |
| 175.41.192.0/18 |
| 54.199.0.0/16 |
| 176.34.32.0/19 |
| 46.51.224.0/19 |
| 54.248.0.0/15 |
| 54.150.0.0/16 |
| 54.178.0.0/16 |
| 54.92.0.0/17 |
| 176.32.64.0/19 |
| 176.34.0.0/19 |
| 54.168.0.0/16 |
| 54.95.0.0/16 |
| 103.4.8.0/21 |
| 54.248.220.0/26 |
| 54.250.253.192/26 |
+------------+
41 rows selected (0.437 seconds)

　
ap-northeast-1のEC2で使用されているIP Rangeを抽出

0: jdbc:drill:zk=local>  SELECT t.prefix.ip_prefix AS prefix
. . . . . . . . . . . >  FROM (SELECT FLATTEN(prefixes) AS prefix FROM dfs.`/tmp/ip-ranges.json` ) t
. . . . . . . . . . . >  WHERE t.prefix.region='ap-northeast-1' AND t.prefix.service='EC2';
+------------+
|   prefix   |
+------------+
| 54.238.0.0/16 |
| 54.64.0.0/15 |
| 54.250.0.0/16 |
| 175.41.192.0/18 |
| 54.199.0.0/16 |
| 176.34.32.0/19 |
| 46.51.224.0/19 |
| 54.248.0.0/15 |
| 54.150.0.0/16 |
| 54.178.0.0/16 |
| 54.92.0.0/17 |
| 176.32.64.0/19 |
| 176.34.0.0/19 |
| 54.168.0.0/16 |
| 54.95.0.0/16 |
| 103.4.8.0/21 |
+------------+
16 rows selected (0.39 seconds)

まとめ

少し深くJSON形式のデータをDrillしました。
ここまで出来ればどんなJSON形式のデータでもクエリ実行出来そうです。
　
実は今回はサンプルデータをローカルに置いて参照しました。
この辺りも手軽にクエリが実行出来るDrillのいいところだと思います。
　
各種データソースへの接続方法は下記を参考に。
Storage Plugin Registration - Drill Wiki - Apache Software Foundation 　
SQL on Hadoopの一つとして気軽に試してみてください。
(ノ´▽｀)ノ{+++THANK YOU+++}ヽ(´▽｀ヽ)

データ集計・分析のためのSQL入門

データ集計・分析のためのSQL入門