JSON形式のデータをDrill

このエントリはSpark, SQL on Hadoop etc. Advent Calendar 2014 - Qiitaの13日目の記事です。

f:id:kashigeru:20141209223614p:plain

Apache Drillとは

SQL分散クエリエンジンです。
標準SQLサポートや様々なデータソースへの接続などの特徴があります。
今回は特徴の一つである”スキーマレスデータに対するSQLクエリ”を実行してみます。

Drillについて詳しくは こちらを参照ください。

環境準備

MapRのSandBox版を使います。
インストール方法は下記を参考にして下さい。


Drillは下記バージョンがインストールされてました。

mapr-drill.noarch     0.5.0.275270-1    @/mapr-drill-0.5.0.275270-1.noarch

JSON形式のサンプルデータは下記を使用します。
Aggregation with the Zip Code Data Set — MongoDB Manual 2.6.6

zips.json
一部抜粋(キーと値がコロン”:”で区切られて表記されています)

{ "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" }
{ "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA" }

Drill

まず、最初にサンプルデータ(zips.jxon)をMapR-FSにアップロードします。
/dataディレクトリ配下はSandBox版Drillでは接続出来るようにあらかじめ設定されています。

# hadoop fs -put zips.json /data
# hadoop fs -ls /data
Found 6 items
-rw-r--r--   1 root root    1034773 2014-09-15 19:59 /data/customers.all.csv
drwxr-xr-x   - root root          1 2014-09-15 19:59 /data/flat
drwxr-xr-x   - root root          1 2014-09-15 19:59 /data/nested
drwxr-xr-x   - root root         10 2014-09-15 19:59 /data/orders
drwxr-xr-x   - root root          1 2014-09-15 19:59 /data/products
-rwxr-xr-x   1 root root    3182409 2014-12-08 16:50 /data/zips.json

Drillを起動します。

 # sqlline
sqlline version 1.1.6
0: jdbc:drill:>

database確認
今回は”dfs.data”を使います。

 0: jdbc:drill:> show databases;
+-------------+
| SCHEMA_NAME |
+-------------+
| hive.default |
| dfs.default |
| dfs.logs    |
| dfs.root    |
| dfs.views   |
| dfs.clicks  |
| dfs.data    |
| dfs.tmp     |
| sys         |
| maprdb      |
| cp.default  |
| INFORMATION_SCHEMA |
+-------------+
12 rows selected (0.323 seconds)

サンプルデータ(zips.json)がどのようになっているかSQLを実行して確認します。

 0: jdbc:drill:> select * from dfs.data.`zips.json` limit 1;
+------------+------------+------------+------------+------------+
|    _id     |    city    |    loc     |    pop     |   state    |
+------------+------------+------------+------------+------------+
| 01001      | AGAWAM     | [-72.622739,42.070206] | 15338      | MA         |
+------------+------------+------------+------------+------------+
1 row selected (0.475 seconds)

キーが自動でカラムになっているのでテーブル定義せずにSQLクエリが実行出来そうです。 CREATE TABLEは不要です。

fromで指定しているデータソース(dfs.data.zips.json)について
形式は[Storage].[Workspace].[ファイルパス]になります。
StorageとWorkspaceはDrillを起動するとアクセス出来る
http://[DrillのIPaddress]:8047/storageで設定出来ます。

ではSQLクエリをいくつか実行してみます。

 0: jdbc:drill:> select state,city,AVG(pop) as AVG_POP from dfs.data.`zips.json` group by state,city limit 5;
+------------+------------+------------+
|   state    |    city    |  AVG_POP   |
+------------+------------+------------+
| MA         | AGAWAM     | 15338.0    |
| MA         | CUSHMAN    | 36963.0    |
| MA         | BARRE      | 4546.0     |
| MA         | BELCHERTOWN | 10579.0    |
| MA         | BLANDFORD  | 1240.0     |
+------------+------------+------------+
5 rows selected (2.527 seconds)
 0: jdbc:drill:> select state,sum(pop) as SUM_POP from dfs.data.`zips.json` group by state order by sum(pop) desc limit 5;
+------------+------------+
|   state    |  SUM_POP   |
+------------+------------+
| CA         | 29754890   |
| NY         | 17990402   |
| TX         | 16984601   |
| FL         | 12686644   |
| PA         | 11881643   |
+------------+------------+
5 rows selected (1.911 seconds)

問題なくSQLクエリを実行することが出来ました。

もう少しDrill

サンプルデータ(zips.json)の下記の部分
キー”loc”に対して値が配列で入ってます。

"loc" : [ -72.622739, 42.070206 ]

Drillで確認すると

 0: jdbc:drill:> select loc from dfs.data.`zips.json` limit 5;
+------------+
|    loc     |
+------------+
| [-72.622739,42.070206] |
| [-72.51565,42.377017] |
| [-72.108354,42.409698] |
| [-72.410953,42.275103] |
| [-72.936114,42.182949] |
+------------+
5 rows selected (0.182 seconds)

このデータから配列の2つ目だけ取り出す方法です。

 0: jdbc:drill:> select loc[1] from dfs.data.`zips.json` limit 5;
+------------+
|   EXPR$0   |
+------------+
| 42.070206  |
| 42.377017  |
| 42.409698  |
| 42.275103  |
| 42.182949  |
+------------+
5 rows selected (0.219 seconds)

以上、JSON形式のデータをドリってみました。

まとめ

今回のように特徴があるSQLクエリエンジンがあるのはありがたいなと思いました。
選択肢がたくさんあれば用途によって使い分けが出来るので。

また今回はMapRのSandbox版を使用したのでMapR-FS上のデータに接続しましたが、
DrillはHive/MongoDB/Hbase上にあるデータに対してもクエリを実行出来ます。
さらに上記環境を構築するのが面倒であればローカルにあるファイルにも接続出来ます。

各種データソースへの接続方法は下記を参考に。
Storage Plugin Registration - Drill Wiki - Apache Software Foundation

SQL on Hadoopの一つとして気軽に試してみてください。
(ノ´▽`)ノ{+++THANK YOU+++}ヽ(´▽`ヽ)

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

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

広告を非表示にする