Apache Drill でローカルのEXCELファイルに、SQLを実行する

2022.08.23

Apache Drill は、データベース用の言語であるSQLを、NoSQL、クラウドストレージなど、に対して利用できるというツールです。私は最近知ったのですが、2014年頃に Apache のトップ・レベル・プロジェクトとして動き始めて、その後2018年以降は、下火になってしまっているようです。

https://ja.wikipedia.org/wiki/Apache_Drill

面白いのは、データベースではなく、コンピュータ上に置いてあるファイルに対してもSQLが実行出来るという点です。この記事では、私が試してみたことと、その上で分かった、業務利用には難しい状況について共有したいと思います。


Apache Drill は様々なデータに対して利用することが出来るのですが、私の興味を引いたのは、CSV、TSV、EXCEL、JSONといった、普段、企業で多く利用するファイル形式に対して、ローカルに置いてあるままでも利用出来るという点です。

つまり、自分のコンピュータの上にある、このようなCSVに対して:

CSVファイル

このようなクエリを実行すると:

select * from dfs.`C:\YNET\20220819-test\勤務表*.csv`
CSVファイルに対してSQLを実行する。

このような結果が出ます:

実行結果

これは 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」で終わること、という指定になります。

複数のファイルがあると、全てに対して検索を実行してくれます。

すると、結果が:

LIKE文を利用したSQLの実行結果

このように出力されます。

Excelファイル に対してもSQLを実行出来る

上記はCSVファイルでしたが、Apache Drill はEXCELに対してもSQLを実行出来ます。CSVの元となっていたEXCELファイルはこのようなものでした:

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列目から数えた最初の列(つまり1列目)を、読み取る最初のセルとする

「allTextMode => true」 というのは、取得した全てのカラムを文字列とみなす、という指定です。

結果は、CSVの時と同じですが:

EXCELへのクエリ実行結果

このようになりました。漢字のところにカタカナでの読み仮名が含まれていますね。。。EXCEL文書中のメタ情報も取り込まれているようです。

EXCELでも複数ファイルが検索対象になれば、全てのファイルを検索してくれます。

EXCELでも検索条件を指定出来る

CSVとこれも同じです。

select * from table(dfs.`C:\YNET\20220819-test\勤務表*.xlsx` (type => 'excel', headerRow => 4, firstColumn => 1, allTextMode => true )) where `終業時刻 シュウギョウ ジコク` like '21:%'

を実行すると、21:00時台のデータを抽出してくれます。列名に、カタカナの部分も含めないと正しく動いてくれません。結果は以下のようになります:

EXCELファイルに WHERE 句を指定した場合の結果

Apache Drill の問題点

このように、Apache Drill は、EXCELファイルに対してもSQLを実行出来るということで、とても面白いツールなのですが、実際にはWindowsの日本語環境で扱うには、

難点があることが分かりました。

ですので、2バイト文字が含まれるファイルに対しては、使い道が随分と限られてしまいそうです。

  • 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でデータを参照出来る、というのは、魅力的だと思ったのですが、そう簡単にはいかなそうです。それでもまだ、開発は続いているようなので、もしかするとまた、大きく進化することもあるかもしれません。新しい情報があれば、共有したいと思います。