Apache Drill は、データベース用の言語であるSQLを、NoSQL、クラウドストレージなど、に対して利用できるというツールです。私は最近知ったのですが、2014年頃に Apache のトップ・レベル・プロジェクトとして動き始めて、その後2018年以降は、下火になってしまっているようです。
https://ja.wikipedia.org/wiki/Apache_Drill
面白いのは、データベースではなく、コンピュータ上に置いてあるファイルに対してもSQLが実行出来るという点です。この記事では、私が試してみたことと、その上で分かった、業務利用には難しい状況について共有したいと思います。
Apache Drill は様々なデータに対して利用することが出来るのですが、私の興味を引いたのは、CSV、TSV、EXCEL、JSONといった、普段、企業で多く利用するファイル形式に対して、ローカルに置いてあるままでも利用出来るという点です。
つまり、自分のコンピュータの上にある、このようなCSVに対して：
このようなクエリを実行すると：
select * from dfs.`C:\YNET\20220819-test\勤務表*.csv`
このような結果が出ます：
これは dfs. という識別子の後ろにある、
C:\YNET\20220819-test\
というフォルダの中の、「勤務表…」で始まる CSV ファイルの中身を、全て検索するようにというクエリになります。
WHERE句を利用してみる
Apache Drill は標準的なSQLを実装しているとのことで、通常のデータベースで使うSQLがそのまま使えます。
例えば、終業時刻が 20:00時台の就業日のみ検索するには：
select * from dfs.`C:\YNET\20220819-test\勤務表*.csv` where `終業時刻` like '20:%'というSQLを実行出来します。
先の例と同じく、ファイル名は、「勤務表*.csv」という指定になっていますが、これは「勤務表」で始まるファイル名で、最後が 「.csv」で終わること、という指定になります。
複数のファイルがあると、全てに対して検索を実行してくれます。
すると、結果が：
このように出力されます。
Excelファイル に対してもSQLを実行出来る
上記はCSVファイルでしたが、Apache Drill はEXCELに対してもSQLを実行出来ます。CSVの元となっていたEXCELファイルはこのようなものでした：
EXCELの場合には少し普通のSQLとは異なる部分もありますが、このようなクエリになります：
select * from table(dfs.`C:\YNET\20220819-test\勤務表*.xlsx` (type => 'excel', headerRow => 4, firstColumn => 1, allTextMode => true ))table(…) で囲った部分が、CSVの時よりも複雑になっています。EXCELなので、どこを「表」としてみなすかを、headerRow / firstColumn といったオプションで指定するようになっています。
この例では：
- type => ‘excel’ : EXCELファイルであることを明示
- headerRow => 4: 0行目から数えた4行目（つまり 5行目）を、テーブルのカラム列とみなす
- firstColumn => 1 : 1列目から数えた最初の列（つまり１列目）を、読み取る最初のセルとする
「allTextMode => true」 というのは、取得した全てのカラムを文字列とみなす、という指定です。
結果は、CSVの時と同じですが：
このようになりました。漢字のところにカタカナでの読み仮名が含まれていますね。。。EXCEL文書中のメタ情報も取り込まれているようです。
EXCELでも複数ファイルが検索対象になれば、全てのファイルを検索してくれます。
EXCELでも検索条件を指定出来る
CSVとこれも同じです。
select * from table(dfs.`C:\YNET\20220819-test\勤務表*.xlsx` (type => 'excel', headerRow => 4, firstColumn => 1, allTextMode => true )) where `終業時刻 シュウギョウ ジコク` like '21:%'を実行すると、２１：００時台のデータを抽出してくれます。列名に、カタカナの部分も含めないと正しく動いてくれません。結果は以下のようになります：
EXCELファイルに WHERE 句を指定した場合の結果
Apache Drill の問題点
このように、Apache Drill は、EXCELファイルに対してもSQLを実行出来るということで、とても面白いツールなのですが、実際にはWindowsの日本語環境で扱うには、
難点があることが分かりました。
ですので、２バイト文字が含まれるファイルに対しては、使い道が随分と限られてしまいそうです。
- EXCELの場合、上記のサンプルのように、漢字に読み仮名を振られた形で認識されてしまう。カラム名など、実際にどういう値か判別しにくい。
- テキストファイルやCSVの場合、Shift-JISでは文字化けして日本語を認識出来ない。
- 今回はWebアプリを利用していますが、Windows上のコマンドラインだとUTF-8の扱いが悪く、コンソール上でも文字化けしてしまう。
日本語中心の環境の企業では、なかなかに難しいツールと言わざるを得ません。
とはいえアルファベットと数字のみであることが分かっているCSVやEXCELファイルの場合には、RDBMSにテーブルデータとして取り込まなくても、そのままファイルに対して
SQLを実行が出来ます。また、ファイルとデータベース上のテーブルとでJOINを行うことも可能です。
従って、マスタデータはRDBMS上にあり、手元のローカルに存在するEXCELファイルを分析しないといけないという場合には、Apache Drillがあると活用できるシーンがあるかもしれません。
必要なもの
https://drill.apache.org/docs/drill-in-10-minutes/#embedded-mode-installation-prerequisites
こちらにある通り、Java 8 以上の環境があれば、Linux / MacOS / Windows で動きます。
JAVA_HOME の設定と、Path への JDK / JRE のパス設定をしておけば、簡単に起動することが出来ます。
Web UI
また、本記事でも紹介してあるとおり、Apache Drill には Web UI もあります。
https://drill.apache.org/docs/starting-the-web-ui/
Drill を embedded モードで起動すれば、Web UIもアクセス可能です。
http://localhost:8047
へアクセスすると、Web UI が開きますので、クエリを実行することが可能です。
※前述の通り、Windows では、日本語が混在しているファイルの場合、 コマンドライン上では表示に問題がありました。Web UIだと、本記事の中のスクリーンショットのように、日本語も問題なく表示出来ていました。
以上、Apache Drill の紹介でした。
ローカルにある様々なデータに対して、横ぐしにSQLでデータを参照出来る、というのは、魅力的だと思ったのですが、そう簡単にはいかなそうです。それでもまだ、開発は続いているようなので、もしかするとまた、大きく進化することもあるかもしれません。新しい情報があれば、共有したいと思います。