JSON形式のデータをもう少し深くDrill
今回は以前のエントリの続編です。
@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+++}ヽ(´▽｀ヽ)
- 作者: 株式会社ALBERT 巣山剛,データ分析部,システム開発・コンサルティング部
- 出版社/メーカー: マイナビ
- 発売日: 2014/09/23
- メディア: 単行本（ソフトカバー）
- この商品を含むブログ (2件) を見る