ランチの時にapache drillの話をふわっと聞いた。
なんでもRDS、DynamoDBのテーブルとファイルシステム上、S3上のJSON/tsv/csvファイルをSQLライクなクエリでJoinしたりできるらしい。

面白そうなので触ってみる

document

インストール

brewで一撃

brew install apache-drill

mockデータ作成

mockarooでmockデータ作成

JSON(カラムはデフォルトのまま

/tmp/work/MOCK_DATA.json

[{"id":1,"gender":"Male","first_name":"Willie","last_name":"Gardner","email":"wgardner0@csmonitor.com","ip_address":"171.75.157.165"},
{"id":2,"gender":"Female","first_name":"Lois","last_name":"Jacobs","email":"ljacobs1@photobucket.com","ip_address":"188.171.106.166"},
{"id":3,"gender":"Male","first_name":"Shawn","last_name":"Rivera","email":"srivera2@storify.com","ip_address":"160.224.138.213"},
{"id":4,"gender":"Female","first_name":"Stephanie","last_name":"Hernandez","email":"shernandez3@shinystat.com","ip_address":"77.67.203.114"},
{"id":5,"gender":"Male","first_name":"Ralph","last_name":"Wright","email":"rwright4@booking.com","ip_address":"89.240.134.189"},
....

CSV(colorとcompany_nameを追加

/tmp/work/MOCK_DATA.csv

id,first_name,last_name,email,gender,ip_address,color,company_name
1,Kenneth,Wheeler,kwheeler0@domainmarket.com,Male,54.230.82.29,Khaki,Voonyx
2,Brenda,Young,byoung1@networkadvertising.org,Female,79.121.226.209,Goldenrod,Feedbug
3,Steven,Knight,sknight2@hud.gov,Male,161.79.31.161,Purple,Aimbo
4,Timothy,Daniels,tdaniels3@ovh.net,Male,233.15.252.170,Mauv,Oba
5,Adam,Wright,awright4@cbc.ca,Male,56.247.113.21,Indigo,Kwilith
....

動作確認

まずはdrillシェルに入る。just drill it!!

$ sqlline -u jdbc:drill:zk=local
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=512M; support was removed in 8.0
Jan 05, 2016 3:32:08 PM org.glassfish.jersey.server.ApplicationHandler initialize                                                                  INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...                                                                  apache drill 1.4.0
"just drill it"
 0: jdbc:drill:zk=local>

WEBコンソールも用意されており、シェルに入った状態で
http://127.0.0.1:8047/
にアクセスすることができる

jsonの方を引いてみる

0: jdbc:drill:zk=local> SELECT tbl.* FROM dfs.`/tmp/work/MOCK_DATA.json` AS tbl LIMIT 10;
+-----+---------+-------------+------------+------------------------------------+------------------+
| id  | gender  | first_name  | last_name  |               email                |    ip_address    |
+-----+---------+-------------+------------+------------------------------------+------------------+
| 1   | Male    | Willie      | Gardner    | wgardner0@csmonitor.com            | 171.75.157.165   |
| 2   | Female  | Lois        | Jacobs     | ljacobs1@photobucket.com           | 188.171.106.166  |
| 3   | Male    | Shawn       | Rivera     | srivera2@storify.com               | 160.224.138.213  |
| 4   | Female  | Stephanie   | Hernandez  | shernandez3@shinystat.com          | 77.67.203.114    |
| 5   | Male    | Ralph       | Wright     | rwright4@booking.com               | 89.240.134.189   |
| 6   | Male    | Donald      | Graham     | dgraham5@google.com.br             | 239.6.250.80     |
| 7   | Male    | Justin      | Howell     | jhowell6@dion.ne.jp                | 159.104.8.8      |
| 8   | Male    | Russell     | Mills      | rmills7@nsw.gov.au                 | 245.130.6.110    |
| 9   | Female  | Rose        | Gonzalez   | rgonzalez8@scientificamerican.com  | 16.9.119.25      |
| 10  | Female  | Mildred     | Ramos      | mramos9@unc.edu                    | 82.232.80.156    |
+-----+---------+-------------+------------+------------------------------------+------------------+
10 rows selected (0.215 seconds)

うほほほほ。こりゃいい。しかも結構はやい。

csvの方も引いてみる

0: jdbc:drill:zk=local> SELECT tbl.* FROM dfs.`/tmp/work/MOCK_DATA.csv` AS tbl  LIMIT 10;
+----------------------------------------------------------------------------------------------------------+
|                                                 columns                                                  |
+----------------------------------------------------------------------------------------------------------+
| ["id","first_name","last_name","email","gender","ip_address","color","company_name"]                     |
| ["1","Kenneth","Wheeler","kwheeler0@domainmarket.com","Male","54.230.82.29","Khaki","Voonyx"]            |
| ["2","Brenda","Young","byoung1@networkadvertising.org","Female","79.121.226.209","Goldenrod","Feedbug"]  |
| ["3","Steven","Knight","sknight2@hud.gov","Male","161.79.31.161","Purple","Aimbo"]                       |
| ["4","Timothy","Daniels","tdaniels3@ovh.net","Male","233.15.252.170","Mauv","Oba"]                       |
| ["5","Adam","Wright","awright4@cbc.ca","Male","56.247.113.21","Indigo","Kwilith"]                        |
| ["6","Jane","Wheeler","jwheeler5@unc.edu","Female","42.132.133.182","Teal","Gabcube"]                    |
| ["7","Jack","Smith","jsmith6@domainmarket.com","Male","199.219.253.212","Fuscia","Pixope"]               |
| ["8","James","Hayes","jhayes7@businessinsider.com","Male","237.34.224.202","Purple","Yabox"]             |
| ["9","Susan","Murphy","smurphy8@joomla.org","Female","85.200.49.196","Turquoise","Jaloo"]                |
+----------------------------------------------------------------------------------------------------------+

ほほー。csvの方はこうなっているのですね。これでJOINなんてできるの・・？
なんかオプションがあるのだろうか。

と思ってドキュメント軽く見たら書いてた。
https://drill.apache.org/docs/text-files-csv-tsv-psv/

この辺りを定義すればいける模様。

  "skipFirstLine": false,
  "extractHeader": true,

cfgファイルではなくWebコンソールかcurlで設定するらしい。
今回はwebコンソールで既存のcsvの定義を変更することにした。

メニュータブのStorageに遷移し、dfsの[Update]をクリック

csv,tsvにskipFirstLineとextractHeaderを追記し、[Update]をクリック

0: jdbc:drill:zk=local> SELECT tbl.* FROM dfs.`/tmp/work/MOCK_DATA.csv` AS tbl  LIMIT 10;
+-----+-------------+------------+---------------------------------+---------+------------------+------------+---------------+
| id  | first_name  | last_name  |              email              | gender  |    ip_address    |   color    | company_name  |
+-----+-------------+------------+---------------------------------+---------+------------------+------------+---------------+
| 1   | Kenneth     | Wheeler    | kwheeler0@domainmarket.com      | Male    | 54.230.82.29     | Khaki      | Voonyx        |
| 2   | Brenda      | Young      | byoung1@networkadvertising.org  | Female  | 79.121.226.209   | Goldenrod  | Feedbug       |
| 3   | Steven      | Knight     | sknight2@hud.gov                | Male    | 161.79.31.161    | Purple     | Aimbo         |
| 4   | Timothy     | Daniels    | tdaniels3@ovh.net               | Male    | 233.15.252.170   | Mauv       | Oba           |
| 5   | Adam        | Wright     | awright4@cbc.ca                 | Male    | 56.247.113.21    | Indigo     | Kwilith       |
| 6   | Jane        | Wheeler    | jwheeler5@unc.edu               | Female  | 42.132.133.182   | Teal       | Gabcube       |
| 7   | Jack        | Smith      | jsmith6@domainmarket.com        | Male    | 199.219.253.212  | Fuscia     | Pixope        |
| 8   | James       | Hayes      | jhayes7@businessinsider.com     | Male    | 237.34.224.202   | Purple     | Yabox         |
| 9   | Susan       | Murphy     | smurphy8@joomla.org             | Female  | 85.200.49.196    | Turquoise  | Jaloo         |
| 10  | Ann         | Hill       | ahill9@youku.com                | Female  | 254.251.33.227   | Red        | Yadel         |
+-----+-------------+------------+---------------------------------+---------+------------------+------------+---------------+

ｷﾀｰｰｰ！

大正義Join!!!

おめぇ、これできたらと思うとおらワクワクすっべ・・・

0: jdbc:drill:zk=local> SELECT tbl1.first_name, tbl1.last_name, tbl2.color, tbl2.company_name FROM dfs.`/tmp/work/MOCK_DATA.json` AS tbl1
. . . . . . . . . . . > JOIN
. . . . . . . . . . . > dfs.`/tmp/work/MOCK_DATA.csv` AS tbl2 ON tbl1.first_name=tbl2.first_name and tbl1.last_name=tbl2.last_name;
+-------------+-------------+-------------+----------------+
| first_name  |  last_name  |    color    |  company_name  |
+-------------+-------------+-------------+----------------+
| Scott       | Ray         | Maroon      | Twinte         |
| Andrew      | Brown       | Orange      | Photolist      |
| Rose        | Washington  | Orange      | Livetube       |
| Steven      | Adams       | Maroon      | Zoombeat       |
| Nancy       | Porter      | Indigo      | Kwinu          |
| Lois        | Williams    | Purple      | Flashset       |
| Justin      | Hart        | Red         | Miboo          |
| Arthur      | Scott       | Indigo      | Thoughtbeat    |
| Wanda       | Peters      | Khaki       | Teklist        |
| Christine   | Dean        | Aquamarine  | Buzzshare      |
| Paula       | Nelson      | Green       | Kare           |
| Betty       | Wagner      | Aquamarine  | Photobean      |
| Jerry       | Ross        | Mauv        | Linktype       |
| Harry       | Washington  | Green       | Reallinks      |
| Nancy       | Lynch       | Turquoise   | Twitternation  |
| Heather     | Moore       | Violet      | Buzzbean       |
| Linda       | Gray        | Violet      | Fadeo          |
+-------------+-------------+-------------+----------------+
17 rows selected (1.026 seconds)

あまり意味のないクエリですがきちんとJOINできました。
素晴らしいですね。

仕事で使えそうなケースがあれば大規模データでの検証やSparkとの比較も実施してみようと思います。