Apache Drill

Introduce

Schema-free SQL Query Engine for Hadoop, NoSQL and Cloud Storage, Files(CSV, TSV, Text), RDBMS.

It can be join cross data platform on apache drill.

Official : https://drill.apache.org/

Install

Linux

• Download Drill
  • http://apache.mirrors.hoobly.com/drill/drill-1.17.0/apache-drill-1.17.0.tar.gz
• Run drill-embedded
  • ./drill-embedded
• Run browser
  • http://localhost:8047

Windows

• 日本語 : https://talklowy-jp.blogspot.com/2020/03/apache-drilldata-scientist.html#more

• Install JDK

  • https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
  • Set JAVA_HOME to Windows system environment

• Download Drill

  • http://apache.mirrors.hoobly.com/drill/drill-1.17.0/apache-drill-1.17.0.tar.gz
  • Unzip tar -xzf apache-drill-1.17.0.tar.gz
  • Run create working directory on cmd prompt

mkdir "%userprofile%\drill"
mkdir "%userprofile%\drill\udf"
mkdir "%userprofile%\drill\udf\registry"
mkdir "%userprofile%\drill\udf\tmp"
mkdir "%userprofile%\drill\udf\staging"
takeown /R /F "%userprofile%\drill"

• Run drill-embedded
  • Double click drill-embedded.bat
• Run browser
  • http://localhost:8047

Data connect

MySQL

• Download MySQL JDBC Connector
  • https://dev.mysql.com/downloads/connector/j/5.1.html
• Add connection string to Apache drill

{
  "type": "jdbc",
  "driver": "com.mysql.jdbc.Driver",
  "url": "jdbc:mysql://my.server.net:3306",
  "username": "myroot",
  "password": "mypwd",
  "enabled": true
}

PostgreSQL

• Download PostrgreSQL JDBC connector
  • https://jdbc.postgresql.org/download.html
• Add Connection string on Apache Drill

{
  "type": "jdbc",
  "driver": "org.postgresql.Driver",
  "url": "jdbc:postgresql://psql.server.net:5432",
  "username": "psroot",
  "password": "pspwd",
  "enabled": true
}

Custom file

• Modify http://localhost:8047/storage/dfs after execute apache drill
  • You may add file type or working directory information

SQL

• Get file form WebUI

curl -X POST -H "Content-Type: application/json" -d '{"queryType":"SQL", "query": "select * from mysvr.mydb.`tuser` limit 10"}' http://localhost:8047/query.json >>tuser.181012.json

• Select JSON file (using dfs)

select * from dfs.`/Users/lowyshin/Downloads/mydb.tuser.181012.json` where cast(`rows.role` as varchar)='Admin' order by uid desc;

• If fields not fixec(etc log or text file)

select f from (select FLATTEN(`columns`) as f from dfs.`/Users/lowyshin/worker.tsv` t) t2 where t2.f like '%expires%' limit 10;

• Create table

Create table tRst as select * from dfs.`/Users/lowyshin/worker.tsv`

fix

• When you can not see data then execute on drill prompt

alter system set `store.json.all_text_mode` = false

Links

• Apache Arrow(PyArrow)を使って簡単かつ高速にParquetファイルに変換する
  • https://dev.classmethod.jp/business/bigdata/20190614-apache-arrow-parquet/
• online CSV to JSON converter
  • http://shancarter.github.io/mr-data-converter/