この記事は Spark, SQL on Hadoop etc. Advent Calendar 2014 の18日目の記事です。
Drill のここがすごい
先日 Apache Drill も晴れて Apache Software Foundation のトップレベルプロジェクトになりまして、来年初頭のバージョン1.0リリースに向けて機能が追加され品質も徐々に高まってきています。さて、Drillとはどういうものかについては去年のHadoopアドベントカレンダーの記事でも触れました。
が、改めてDrillの特徴と他のSQL-on-Hadoopプロジェクトとどういうところが違うのかについて説明しましょう。
速攻使える (Agility)
リレーショナルデータベースの世界でも、データウェアハウスの世界でも、そして近年のHadoopのような分散処理プラットフォームの世界でも、データ分析を行う上での大きな課題は、いかに迅速に必要なデータを集めて処理に必要な準備を整えるか、ということでした。大抵の場合、データは複数の場所に散在しておりそれぞれのデータのスキーマ管理も別々に行われているため、データを1箇所に移動してスキーマの整合性を取り、フォーマットを揃え・・・といった作業はものすごく骨が折れます。よく分析を専門としている方々が、分析プロセスの8割9割は前処理だ、なんて言っているところの結構な部分を占めているのがこれです。
この課題をいかに解決するか、というのがDrillが生まれた背景の一つです。このためDrillは専用のデータストアやスキーマリポジトリを持たず、ファイルシステム、Hive、HBase、MongoDBといった既存のデータストアに接続し、格納されているデータに対しネイティブフォーマットのまま直接処理を行います。Drillはクエリ処理を行うエンジンのヘッドのみを持ち、既存のスキーマをもつデータストアはそれをそのまま活用、自己記述型のデータやスキーマレスなフラットファイルはクエリ実行時にスキーマを動的に適用するしくみになっています。つまり、分析のための事前準備を極力省くことを可能にし、思い立ったらすぐにデータの探索を始められるようになるのです。
柔軟 (Flexibility)
SQLといえば通常は表形式のデータに対して処理を行うものというイメージがありますが、Drillは複雑な構造のデータにも対応するためJSONに似た構造の内部形式でデータを扱っており、ネスト構造やアレイ構造、さらにデータの構成が動的に変化することも許容しています。そしてそのような複雑な構造のデータをアプリケーションから操作できるように、SQLを拡張して柔軟に処理を行えるインターフェースも追加しています。
他のSQL-on-Hadoopプロジェクトは基本的にそれぞれの独自のデータストアに最適化された形式で事前にロードすることを想定しており、いったん格納してしまうとデータ構造を変更するのが難しい面があります。一方Drillはクエリ実行時に動的に対応するため、遥かに高い柔軟性を備えています。
見たことある感じ (Familiarity)
いくら技術が優れていても、それが使いにくければやがて誰も触らなくなります。DrillではHadoopや分散処理プラットフォームに詳しくないユーザでも使いやすいようにANSI標準SQLに対応し、JDBC/ODBC経由であれば使い慣れた既存のBIツールや可視化ツールを通じても利用可能です。また、DrillはHiveとの親和性も高く、Hiveが対応しているファイルフォーマットはすべてサポートするほか、Hive UDFも再利用可能です。
ネスト構造のJSONデータにSQLクエリをかけてみる
さて、数日前に @ka4geru さんも Advent Calendar 13日目の記事でDrillを使ったJSON形式のデータ処理について書かれておりましたが、本記事ではフラットなJSONデータではなく、もう少し複雑なネスト構造のJSONの扱いについて見てみましょう。Drillの環境にはやはりお手軽な MapR の Sandbox（VMwareまたはVirtualBoxの仮想マシンイメージ）を使用します。
Drillは最新の0.6.0.r2が入っています。
[user01@maprdemo ~]$ rpm -q mapr-drill
mapr-drill-0.6.0.28642.r2-1.noarch
JSONデータは、私ががんばって構造化したゆるキャラデータを使用します。ゆるキャラの名前、都道府県、市区町村、投票の獲得票数が入っています。
[user01@maprdemo ~]$ hadoop fs -cat /yuru/grandprix2014.json
{
"title":"ゆるキャラグランプリ2014",
"date":"2014-11-03",
"url":"http://www.yurugp.jp",
"data":{
"chars":[
{"name":"ぐんまちゃん","prefecture":"群馬県","city":"","point":"1002505"},
{"name":"ふっかちゃん","prefecture":"埼玉県","city":"深谷市","point":"835981"},
{"name":"みきゃん","prefecture":"愛媛県","city":"","point":"749911"},
{"name":"しんじょう君","prefecture":"高知県","city":"須崎市","point":"525306"},
{"name":"チャチャ王国のおうじちゃま","prefecture":"京都府","city":"宇治市","point":"522704"},
{"name":"与一くん","prefecture":"栃木県","city":"大田原市","point":"501122"},
{"name":"しまねっこ","prefecture":"島根県","city":"","point":"469791"},
{"name":"とち介","prefecture":"栃木県","city":"栃木市","point":"469684"},
{"name":"あゆコロちゃん","prefecture":"神奈川県","city":"厚木市","point":"428061"},
{"name":"しっぺい","prefecture":"静岡県","city":"磐田市","point":"404123"}
]
}
}
これを使い、まずは単純にこのJSONファイルをデータソースに指定して、SELECT * をしてみましょう。フロントエンドのsqllineを起動して、SELECT文のFROMには分散ファイルシステムを表す「dfs」に続いてJSONファイルのフルパスを指定します。
[user01@maprdemo ~]$ sqlline
sqlline version 1.1.6
0: jdbc:drill:> SELECT * FROM dfs.`/yuru/grandprix2014.json`;
+------------+------------+------------+------------+
| title | date | url | data |
+------------+------------+------------+------------+
| ゆるキャラグランプリ2014 | 2014-11-03 | http://www.yurugp.jp | {"chars":[{"name":"ぐんまちゃん","prefecture":"群馬県","city":"","point":"1002505"},{"name":"ふっかちゃん","prefecture":"埼玉県","city":"深谷市","point":"835981"},{"name":"みきゃん","prefecture":"愛媛県","city":"","point":"749911"},{"name":"しんじょう君","prefecture":"高知県","city":"須崎市","point":"525306"},{"name":"チャチャ王国のおうじちゃま","prefecture":"京都府","city":"宇治市","point":"522704"},{"name":"与一くん","prefecture":"栃木県","city":"大田原市","point":"501122"},{"name":"しまねっこ","prefecture":"島根県","city":"","point":"469791"},{"name":"とち介","prefecture":"栃木県","city":"栃木市","point":"469684"},{"name":"あゆコロちゃん","prefecture":"神奈川県","city":"厚木市","point":"428061"},{"name":"しっぺい","prefecture":"静岡県","city":"磐田市","point":"404123"}]} |
+------------+------------+------------+------------+
1 row selected (2.194 seconds)
そうすると、JSONのトップレベルの項目はカラムとして出てきますが、dataカラムは巨大な1フィールドとしてしか見えていません。ではdataの中身に対してクエリをかけるにはどうすればよいでしょうか。
ここではDrillで導入されたFLATTEN()関数を使います。FLATTEN()は繰り返されているフィールドを個別のレコードに分解し、その他のフィールドのカラムはそれぞれの新しいレコードに値がコピーされます。下の例では、data.chars内の繰り返しがレコードに展開されました。
0: jdbc:drill:> SELECT FLATTEN(t.data.chars) AS char FROM dfs.`/yuru/grandprix2014.json` t;
+------------+
| char |
+------------+
| {"name":"ぐんまちゃん","prefecture":"群馬県","city":"","point":"1002505"} |
| {"name":"ふっかちゃん","prefecture":"埼玉県","city":"深谷市","point":"835981"} |
| {"name":"みきゃん","prefecture":"愛媛県","city":"","point":"749911"} |
| {"name":"しんじょう君","prefecture":"高知県","city":"須崎市","point":"525306"} |
| {"name":"チャチャ王国のおうじちゃま","prefecture":"京都府","city":"宇治市","point":"522704"} |
| {"name":"与一くん","prefecture":"栃木県","city":"大田原市","point":"501122"} |
| {"name":"しまねっこ","prefecture":"島根県","city":"","point":"469791"} |
| {"name":"とち介","prefecture":"栃木県","city":"栃木市","point":"469684"} |
| {"name":"あゆコロちゃん","prefecture":"神奈川県","city":"厚木市","point":"428061"} |
| {"name":"しっぺい","prefecture":"静岡県","city":"磐田市","point":"404123"} |
+------------+
10 rows selected (0.308 seconds)
さらにこれをカラムに分解しましょう。こうすると、普通のテーブル風にアクセスできるようになりますね。ちなみに`char`にバッククォートがついているのは、charは予約語のため、名前と区別するためです。
0: jdbc:drill:> SELECT
. . . . . . . > t2.`char`.name AS ゆるキャラ,
. . . . . . . > t2.`char`.prefecture AS 都道府県,
. . . . . . . > t2.`char`.city AS 市区町村,
. . . . . . . > t2.`char`.point AS ポイント
. . . . . . . > FROM (
. . . . . . . > SELECT FLATTEN(t1.data.chars) AS `char` FROM dfs.`/yuru/grandprix2014.json` t1
. . . . . . . > ) t2;
+------------+------------+------------+------------+
| ゆるキャラ | 都道府県 | 市区町村 | ポイント |
+------------+------------+------------+------------+
| ぐんまちゃん | 群馬県 | | 1002505 |
| ふっかちゃん | 埼玉県 | 深谷市 | 835981 |
| みきゃん | 愛媛県 | | 749911 |
| しんじょう君 | 高知県 | 須崎市 | 525306 |
| チャチャ王国のおうじちゃま | 京都府 | 宇治市 | 522704 |
| 与一くん | 栃木県 | 大田原市 | 501122 |
| しまねっこ | 島根県 | | 469791 |
| とち介 | 栃木県 | 栃木市 | 469684 |
| あゆコロちゃん | 神奈川県 | 厚木市 | 428061 |
| しっぺい | 静岡県 | 磐田市 | 404123 |
+------------+------------+------------+------------+
10 rows selected (0.263 seconds)
では今度は2014年分だけでなく、2013年分、2012年分の複数のJSONファイルを用意します。
[user01@maprdemo ~]$ hadoop fs -ls /yuru
Found 3 items
-rw-r--r-- 1 user01 mapr 1079 2014-12-18 08:02 /yuru/grandprix2012.json
-rw-r--r-- 1 user01 mapr 1078 2014-12-18 07:54 /yuru/grandprix2013.json
-rw-r--r-- 1 user01 mapr 1095 2014-12-18 07:56 /yuru/grandprix2014.json
で、FROMのデータソースに指定するパスはファイルではなく、ファイルが格納されているディレクトリです。こうすることにより、ディレクトリ配下のファイル全てを1つのデータソースとして扱うことが可能です。
そして3年分のデータから各ゆるキャラの合計票数を求め、ランキングにしてみます。
0: jdbc:drill:> SELECT
. . . . . . . > t2.`char`.name AS ゆるキャラ,
. . . . . . . > SUM(CAST(t2.`char`.point AS INT)) AS 合計
. . . . . . . > FROM (
. . . . . . . > SELECT FLATTEN(t1.data.chars) AS `char` FROM dfs.`/yuru` t1
. . . . . . . > ) t2
. . . . . . . > GROUP BY t2.`char`.name
. . . . . . . > ORDER BY 合計 DESC;
+------------+------------+
| ゆるキャラ | 合計 |
+------------+------------+
| ぐんまちゃん | 2015806 |
| ふっかちゃん | 1420018 |
| さのまる | 1367513 |
| 出世大名家康くん | 1285684 |
| しまねっこ | 955209 |
| あゆコロちゃん | 954866 |
| 与一くん | 908090 |
| ちょるる | 847997 |
| しっぺい | 762442 |
| みきゃん | 749911 |
| バリィさん | 547284 |
| しんじょう君 | 525306 |
| チャチャ王国のおうじちゃま | 522704 |
| とち介 | 469684 |
| かわりみ千兵衛 | 361104 |
| やなな | 127820 |
| 滝ノ道ゆずる | 114600 |
+------------+------------+
17 rows selected (0.807 seconds)
続いて、今度はオンライン投票で出力された（つもりの）、CSV形式のログファイルを持ってきました。以下のように、タイムスタンプ、ユーザID、ゆるキャラ名がカンマ区切りで格納されています。
$ hadoop fs -cat vote.csv | head -n 5
2014-10-04 15:01:43.231,3498645938221,さのまる
2014-10-04 15:01:57.734,1348062483024,バリィさん
2014-10-04 15:02:33.656,2309123985276,しんじょう君
2014-10-04 15:03:18.674,3428705610523,ぐんまちゃん
2014-10-04 15:03:31.530,2309868237482,あゆコロちゃん
このログと、先ほどの2014年のゆるキャラデータをもとに、「2014年10月4日17時以降に投票された票数」を求めてみましょう。下のSQLではCSVログとJSONデータをゆるキャラ名をキーにしてジョインし、時刻の条件をつけてフィルタをかけた後に並び替えています。
0: jdbc:drill:> SELECT
. . . . . . . > t1.columns[2] AS ゆるキャラ,
. . . . . . . > count(*) AS カウント
. . . . . . . > FROM dfs.`/vote.csv` AS t1
. . . . . . . > JOIN (SELECT FLATTEN(t2.data.chars) AS `char` FROM dfs.`/yuru/grandprix2014.json` t2) t3
. . . . . . . > ON t1.columns[2] = CAST(t3.`char`.name as VARCHAR(20)) AND
. . . . . . . > t1.columns[0] > '2014-10-04 17:00:00.000'
. . . . . . . > GROUP BY t1.columns[2];
. . . . . . . > ORDER BY カウント DESC;
+------------+------------+
| ゆるキャラ | カウント |
+------------+------------+
| ぐんまちゃん | 1002 |
| ふっかちゃん | 781 |
| みきゃん | 691 |
| しんじょう君 | 506 |
| チャチャ王国のおうじちゃま | 505 |
| 与一くん | 478 |
| しまねっこ | 369 |
| とち介 | 354 |
| あゆコロちゃん | 261 |
| しっぺい | 223 |
+------------+------------+
10 rows selected (1.043 seconds)
以上、ネスト構造のJSONデータにいろいろなSQLをかけてみました。このように、Drillを使えば事前にフォーマット変換やデータのロードを行うことなく、素早く柔軟にクエリをかけられることがお分かりいただけたのではと思います。
全国のドリラーの皆様もぜひ、レッツドリる！
