Drill your data with Apache Drill !

Feeling the Drill

I have been using Apache Drill to explore data for a while now. Apache Drill is a low latency distributed query engine for large-scale datasets, including structured and semi-structured/nested data. Drill supports a variety of NoSQL databases and file systems, including HBase, MongoDB, MapR-DB, HDFS, MapR-FS, Amazon S3, Azure Blob Storage, Google Cloud Storage, Swift, NAS and local files.  To be clear Drill is not limited to Hadoop, you can query NoSQL databases like MongoDB, Hbase or cloud storage like Amazon S3, Azure Blob Storage  or even local files on your computer.  I have it installed on my laptop and use it as embedded mode to query my txt and cvs files. Apache Drill can be installed on Windows, Linux and MacOS with JDK.

Drill data like a table even when its not – schema on read

Drill is based on schema on read, meaning unlike traditional query engines that requires to have a predefined schema and structure, drill lets you define schema as you query the data. Cool uh ? Wait there is more  with Drill there’s no need to load the data or transform the data before it can be processed. Simply, point the query to the file or database you want to query and start querying the data.
For instance lets say you have a file  customers.csv  on a directory  /data/customer/. Once you have Drill installed (which takes about 3 mins) all you have to from a Drill prompt is :
select * from dfs./data/customer/customers.csv`;  and drill get you the data. You can even bring specific columns :
select column[0],column[1],column[6] from dfs./data/customer/customers.csv`

Drill also allows you to query against wild card files :
select * from dfs./data/orders/orders-08-*-2016.csv`
Drill lets you create views and static tables to even increase ease of use and improve performance.  You can check out the documentation for more options.

In love with your query or BI tool ? No problemo

Apache Drill supports standard SQL. So you can continue to use your favorite query tools and SQL that you have been using. Drill supports ODBC and JDBC drivers, so you it will let you access Drill using tool of your choice.  Data users can use standard BI/analytics tools such as Tableau, Qlik, MicroStrategy and so on to interact with non-relational datastores by leveraging Drill’s JDBC and ODBC drivers. Developers can leverage Drill’s simple REST API in their custom applications to create beautiful visualizations.  Drill comes with a web interface when you install in distributed mode. Drill also provides a native tool called Drill Explorer which I find really useful. You can find all the details on how to configure your tool to access Drill in the documentation.

Lets get it going …

Apache Drill is easy to download and run Drill on your computer . It runs on all standard OS and takes few minutes to install. Drill can also be installed on a cluster of servers to serve a scalable and high performance execution engine.  Drill has two install options:
1.  Installing in Embedded mode
2. Installing in Distributed mode.

Installing in your computer that has JDK installed involves:
1. Downloading the tar file
2. Untar the file
3. cd to the apache-drill<version>
4. run  bin/drill-embedded (Mac and Linux) . On windows : C:\bin\sqlline sqlline.bat –u “jdbc:drill:zk=local;schema=dfs”


Drill in to your data with Apache Drill and hopefully you will enjoy drilling as much as I do.


About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in BigData, database, hadoop and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *