Home › Categories › big-data › Apache Drill Tutorial: Interactive SQL on Big Da…
big-data5 min read

Apache Drill Tutorial: Interactive SQL on Big Data (2026)

Sandip Mhaske • Ayodhyya • big-data

Apache Drill Tutorial: Interactive SQL on Big Data (2026)

Published:  |  Category: Big Data  |  Reading time: ~15 min
Apache Drill Tutorial: Interactive SQL on Big Data (2026)

Apache Drill enables interactive SQL queries directly on files, NoSQL databases, and Hadoop clusters without requiring schema definition or ETL. After using Drill to query Parquet files on S3 and MongoDB collections in production, I appreciate how it eliminates the traditional 'load data into a warehouse first' bottleneck for ad-hoc analytics.

This tutorial covers Drill's architecture, storage plugins, query execution, UDFs, and performance tuning for running interactive analytics across diverse data sources.

📖 Table of Contents
  1. Drill Architecture and Query Engine
  2. Storage Plugins and Data Sources
  3. Query Optimization and Performance
  4. UDFs and Custom Functions
  5. Data Modeling and Schema
  6. Deployment and Cluster Configuration

Drill Architecture and Query Engine

Drill is a distributed SQL engine designed for low-latency queries on nested and semi-structured data. The architecture consists of a Drillbit (per-node process) that handles query parsing, optimization, and execution. Client applications connect via JDBC/ODBC to any Drillbit, which acts as the query coordinator.

Drill's query planner uses a cost-based optimizer and supports complex nested data types (arrays, maps, structs) natively. Unlike traditional SQL engines, Drill infers schema at query time — no CREATE TABLE or DDL required.

-- Start Drill in embedded mode
EmbeddedDrillDriver drill = new EmbeddedDrillDriver();

-- Query a JSON file directly
SELECT * FROM dfs.`/data/events.json` WHERE event_type = 'purchase';

-- Query with nested data
SELECT user.name, user.email, events[0].type
FROM dfs.`/data/users.json`
WHERE events[0].timestamp > '2026-01-01';

-- Drill shell
$ drill -s
> SELECT count(*) FROM dfs.`/data/*.parquet`;

Storage Plugins and Data Sources

Storage plugins define how Drill connects to data sources. The dfs plugin handles local and distributed filesystems (HDFS, S3, Azure). The mongodb plugin connects to MongoDB. The hive plugin reads Hive tables. The kafka plugin reads Kafka topics. The es plugin queries Elasticsearch.

Each plugin supports different file formats automatically. For filesystem plugins, Drill detects format by file extension: .json, .parquet, .orc, .avro, .csv, .tsv. You can also register custom format plugins.

-- Configure S3 storage plugin
ALTER SESSION SET `store.format` = 'parquet';

-- Query S3 directly
SELECT * FROM s3.`bucket/data/events.parquet`
WHERE event_date = '2026-01-01';

-- Query Hive tables
SELECT * FROM hive.default.transactions
WHERE amount > 1000;

-- Query MongoDB
clicks = SELECT * FROM mongo.analytics.page_clicks
WHERE session_id = 'abc123';

-- Join across sources
SELECT h.user_id, h.order_total, m.email
FROM hive.default.orders h
JOIN mongo.users.users m ON h.user_id = m._id
WHERE h.order_total > 500;

-- Storage plugin config in /conf/drill-override.conf
# drill.exec: {
#   store: {
#     format: "parquet",
#     parquet: {
#       block_size: 256MB
#     }
#   }
# }

Query Optimization and Performance

Drill pushes predicates down to the storage layer for file-based queries. For Parquet files, Drill reads only required columns (column pruning) and skips row groups that do not match filters (predicate pushdown). Partition pruning eliminates directories that do not match WHERE conditions.

Use EXPLAIN to understand query plans. Look for full scans (Scan 2.0) vs. filtered scans. Ensure data is partitioned on frequently filtered columns. For large queries, increase memory allocation and parallel scan threads.

-- Check query plan
EXPLAIN SELECT user_id, sum(amount) FROM dfs.`/data/orders.parquet`
WHERE region = 'us-east' GROUP BY user_id;

-- Enable query profiles
ALTER SYSTEM SET drill.exec.profile.store.enabled = true;

-- Tune memory and parallelism
ALTER SESSION SET `exec.memory.block.size` = 256 * 1024 * 1024;
ALTER SESSION SET `exec.parallelizer` = 'CONSERVATIVE';

-- Use approximate functions for large datasets
SELECT approx_distinct(user_id) FROM dfs.`/data/events.parquet`;
SELECT top(10, category) FROM dfs.`/data/events.parquet`;

-- Materialize intermediate results
CREATE VIEW orders_view AS
SELECT * FROM dfs.`/data/orders.parquet`
WHERE event_date >= '2026-01-01';
SELECT count(*) FROM orders_view;

UDFs and Custom Functions

Drill supports user-defined functions (UDFs) in Java and JavaScript. Scalar UDFs transform individual values. Aggregate UDFs combine multiple values. Table functions generate rows from external systems. UDFs are registered in the sys.boot syslogs and can be used in SQL queries like built-in functions.

Drill provides a rich library of built-in functions for string manipulation, date/time operations, mathematical calculations, and JSON/array operations that handle nested data natively.

-- Built-in functions for nested data
SELECT 
    flatten(events) AS event,
    listagg(event.types, ',') AS all_types,
    convert_to_json(event.metadata) AS meta
FROM dfs.`/data/records.parquet`
WHERE array_length(event.types) > 0;

-- Custom JavaScript UDF
CREATE FUNCTION myudf.textLength AS 'TextLength'
FROM FILE `./udfs/text_length.js`;

SELECT myudf.textLength(name) FROM dfs.`/data/users.json`;

-- Custom Java UDF
CREATE AGGREGATE FUNCTION myudf.weighted_avg AS 'WeightedAverage'
FROM JAR `./udfs/my-udfs-1.0.jar`;

SELECT myudf.weighted_avg(score, weight) FROM dfs.`/data/scores.parquet`;

Data Modeling and Schema

Drill uses a two-level namespace: storage_plugin.workspace.`path`. The workspace maps to a directory, and the path is the file or directory within it. You can create views to provide a table-like interface over files. Views are stored in the workspace metadata.

For structured access to semi-structureddata, use CTAS (CREATE TABLE AS) to materialize query results into Parquet files. This pre-materializes schema and improves query performance for repeated access patterns.

-- Create a view over JSON files
CREATE VIEW dfs.tmp.events_view AS
SELECT 
    user_id,
    event_type,
    event_data,
    cast(event_data->>'$.timestamp' as timestamp) AS event_ts
FROM dfs.`/data/events/*.json`;

-- Query the view
SELECT * FROM dfs.tmp.events_view WHERE event_type = 'click';

-- CTAS to materialize as Parquet
CREATE TABLE dfs.tmp.aggregated_events
AS
SELECT user_id, event_type, count(*) AS cnt
FROM dfs.tmp.events_view
GROUP BY user_id, event_type;

-- Schema discovery
DESCRIBE dfs.tmp.aggregated_events;
SHOW COLUMNS FROM dfs.tmp.aggregated_events;

-- Schema in file headers
SELECT * FROM dfs.`/data/mixed/*.json`
LIMIT 10;  -- Drill auto-discovers schema from first files

Deployment and Cluster Configuration

Drill runs in embedded mode (single JVM, for testing) or distributed mode (cluster of Drillbits). In distributed mode, ZooKeeper coordinates cluster membership. Each Drillbit runs on a worker node and handles a portion of query execution.

For production, allocate 8-16 GB heap per Drillbit with direct memory for query execution. Use solid-state drives for spill directories. Deploy on YARN or Kubernetes for resource management.

# drill-override.conf
 drill.exec: {
   cluster-id: "drill-cluster",
   zk.connect: "zk1:2181,zk2:2181,zk3:2181/drill",
   impersonation: {
     enabled: true,
     impersonation-principal: "drill/_HOST@REALM",
     impersonation-keytab: "/etc/drill/keytabs/drill.keytab"
   },
   security: {
     auth.mechanism: Kerberos,
     principal: "drill/_HOST@REALM",
     keytab: "/etc/drill/keytabs/drill.keytab"
   }
 }

# Start Drill cluster
$ drillbit.sh start

# Verify cluster
$ drill -s "SELECT * FROM sys.drillbits;"

Frequently Asked Questions

What file formats does Drill support?

Drill reads Parquet, ORC, Avro, JSON, CSV, TSV, and text files natively. Parquet is recommended for performance — it provides columnar storage, predicate pushdown, and compression.

How does Drill differ from Presto?

Drill emphasizes schema-free querying on files and NoSQL. Presto focuses on querying structured data across connectors. Drill handles nested data natively; Presto requires more explicit schema definitions.

Can Drill replace Hive for ad-hoc queries?

Yes, Drill is faster for interactive queries because it does not require MapReduce jobs. Drill queries execute as distributed MPP queries with sub-second latency on appropriately sized clusters.

What is the maximum query size Drill can handle?

Drill handles queries on terabytes of data with proper cluster sizing. Memory management and spill-to-disk allow queries that exceed available memory. Monitor query profiles to tune memory allocation.

Originally published on Ayodhyyya. Last updated June 1, 2026.