CSVファイルに向かってSQL投げたくなったときに

DISCLAIMER: この記事は元記事（英語）が書かれてからだいたい3年くらいたっていて、いまだに書きかけです。

Apache Drill

Apache Drill を使うとできる。

基本

root@drill:/microsoft# ~/apache-drill/bin/drill-embedded
May 03, 2016 1:05:03 AM org.glassfish.jersey.server.ApplicationHandler initialize
INFO: Initiating Jersey application, version Jersey: 2.8 2014-04-29 01:25:26...
apache drill 1.6.0
"got drill?"

CSVファイルにおもむろにSQLを投げることができる。また、CSVファイルの一行目をカラム名にすると、よりデータベーステーブルっぽく扱える。その際は、拡張子を .csvh にしておくと drill が自動的に一行目をカラム名として認識してくれるので楽です。

select * from dfs.`/microsoft/tmp2/B85-R2V.csvh` limit 10;

0: jdbc:drill:zk=local> select distinct(address), reputation_rating from dfs.`/data/iprdb/gtip-deepsight-001/archive/IP_Attack-2016-05-05-01-30-01-Raw.csvh` where reputation_rating > 5 order by reputation_rating  limit 1000;

limit とか group by とか order by とか desc とか普通に動きます。

select columns[6], count(*) from dfs.`/data/iprdb/gtip-microsoft-001/archive/MicroSoft-Csv-Feed-2016-05-04*-Raw.csv` where columns[2] = 'Conficker' group by columns[6] order by EXPR$1 desc limit 10;
+-----------------+---------+
|     EXPR$0      | EXPR$1  |
+-----------------+---------+
| 38.102.150.28   | 9427    |
| 104.244.14.252  | 7092    |
| 38.102.150.27   | 6997    |
| 104.244.14.253  | 5897    |
| 149.93.118.66   | 2       |
| 149.93.84.35    | 2       |
| 38.229.134.119  | 2       |
| 149.93.29.106   | 2       |
| 38.229.134.193  | 2       |
| 38.229.164.188  | 2       |
+-----------------+---------+

0: jdbc:drill:zk=local> select columns[2], count(*) from dfs.`/data/iprdb/gtip-microsoft-001/archive/MicroSoft-Csv-Feed-2016-05-04*-Raw.csv` group by columns[2] order by EXPR$1 desc limit 10;
+---------------+---------+
|    EXPR$0     | EXPR$1  |
+---------------+---------+
| B68-2-64      | 49184   |
| B68-2-32      | 39066   |
| B68-1-32      | 37842   |
| B68-1-64      | 35390   |
| Conficker     | 29501   |
| B85-R2V       | 16209   |
| B54-CONFIG    | 15170   |
| B106-MULTI    | 14301   |
| B157-R1       | 7561    |
| B106-Jenxcus  | 5474    |
+---------------+---------+

CSVファイルが複数に別れているときは、ファイル名に * を使うとまとめて一つのファイル（テーブル）として扱えます。なんか UNIX な感じでいいっすね。

select count(*) from dfs.`/root/csv/2016-04-10-*-Raw.csvh`;

select address, count(*) from dfs.`/data/iprdb/gtip-deepsight-001/archive/IP_Phishing-2016-05-05-01-30-01-Raw.csvh` where reputation_rating = 10 group by address order by EXPR$1 desc limit 100;

シェルでパラメターをいろいろ設定する

0: jdbc:drill:zk=local> !set verbose false
0: jdbc:drill:zk=local> !verbose
verbose: on

出力にフレーム（テーブル）がいらない場合

0: jdbc:drill:zk=local> !set outputFormat csv

JSON や S3 扱えたりする

別途、ちゃんと書きたい。

SELECT * FROM dfs.root.`/web/logs`;

SELECT country, count(*)
  FROM mongodb.web.users
  GROUP BY country;

SELECT timestamp
  FROM s3.root.`clicks.json`
  WHERE user_id = 'jdoe';

スケーラビリティ

ラップトップから1000+サーバまでスケール。全ノードが対等でマスタのないアーキテクチャ。別途ちゃんと書きたい

ちなみに

ここでサンプルで扱っている CSV はセキュリティ界隈の人がみると、ニヤっとする系のデータです。