Apache Drill 1.20.0 on AWS User Guide

Overview

This guide covers launching and using the Apache Drill 1.20.0 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg Hadoop, big data and data engineering listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

Apache Drill is a schema-free SQL query engine. It runs ANSI SQL straight against files (CSV, JSON, Parquet and more), without loading them into a database or defining tables first, and it can join them with data in object storage, relational databases and other sources through storage plugins. You use it from its web console, its REST API, or JDBC/ODBC clients such as BI tools.

Upstream Drill ships with authentication off, so anyone who reaches its web console can run SQL. The cloudimg image turns that around:

• HTTPS from the first boot. nginx serves the Drill web console and REST API on port 443 with a certificate generated on your instance's first boot. Port 80 redirects to HTTPS.
• A login unique to your instance. Drill user authentication is on. The single account admin gets a password generated on first boot and written to /root/drill-credentials.txt; only its hash is stored in Drill's password file. No password is baked into the image.
• Fail closed. The drillbit refuses to start until first boot has written that login, and nginx refuses to start until first boot has finished.
• Every Drill port on loopback. The web console (8047), the JDBC/ODBC port (31010), Drill's internal ports (31011, 31012) and ZooKeeper (2181) listen on 127.0.0.1 only. The only public ports are 22, 80 and 443. JDBC clients connect through an SSH tunnel (Step 9).
• The file plugin is confined. Drill's dfs storage plugin reads only /var/lib/drill/data, on its own EBS volume. Absolute paths, file:/// URLs and ../ do not escape it (Step 8).
• Hardened defaults. Drill and ZooKeeper run as their own unprivileged users in a systemd sandbox, Drill, ZooKeeper and nginx are blocked from the instance metadata service, the AMI requires IMDSv2, uploading Java code as user-defined functions is off, and requests for any host name the instance does not serve are dropped.
• Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: Apache Drill 1.20.0 (the official Apache binary release, checksum verified, at /opt/drill), OpenJDK 11, a single-node ZooKeeper 3.5.7 run from the jars in the Drill release, nginx, Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

Prerequisites

• An AWS account subscribed to the listing, an EC2 key pair and a VPC subnet with a route to the internet (or a private route you use for management).
• A security group that allows TCP 22 (SSH), TCP 443 (the web console and REST API) and TCP 80 (redirects to HTTPS) from your own networks only. No other port is needed.

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick Apache Drill 1.20.0 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large or larger (Drill is memory hungry; it sizes its heap and direct memory from the instance's RAM, Step 10), your key pair and a security group as above.

The AMI carries two volumes: the root volume and a 30 GiB data volume mounted at /var/lib/drill. Make the data volume larger at launch if you will copy big files onto the instance.

With the AWS CLI (replace the values in angle brackets):

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=apache-drill}]'

Step 2: Connect to your instance

Version	SSH user
Apache Drill 1.20.0 on Ubuntu 24.04	ubuntu

ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

First boot generates the certificate and the password, then ZooKeeper, the drillbit and nginx start. The drillbit takes up to a minute on the first boot:

systemctl is-active zookeeper drill nginx apache-drill-firstboot
sudo test -f /var/lib/cloudimg/apache-drill-firstboot.done && echo FIRSTBOOT_DONE

All four units are active and the first boot marker is present:

active
active
active
active
FIRSTBOOT_DONE

Only SSH and nginx listen on public addresses; every Drill and ZooKeeper port is on loopback:

sudo ss -lntp | awk 'NR>1 {print $4}' | grep -v '127.0.0.5' | sort

0.0.0.0:22
0.0.0.0:443
0.0.0.0:80
[::]:22
[::]:443
[::]:80
[::ffff:127.0.0.1]:2181
[::ffff:127.0.0.1]:31010
[::ffff:127.0.0.1]:31011
[::ffff:127.0.0.1]:31012
[::ffff:127.0.0.1]:8047

Step 4: Get your URL and password

sudo cat /root/drill-credentials.txt

The file is readable by root only and holds the URL, the user name and this instance's password:

DRILL_URL=https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/
DRILL_ADMIN_USER=admin
DRILL_ADMIN_PASSWORD=<DRILL_ADMIN_PASSWORD>

The URL uses the instance's public DNS name from EC2 instance metadata. The certificate also names the public and private IP addresses, so https://<public-ip>/ works too. To serve Drill under your own DNS name, see Step 11.

Step 5: Sign in to the web console

Open the DRILL_URL in your browser. The certificate is self signed, so your browser asks you to confirm it the first time (or install your own certificate, Step 11). Choose Log In and sign in as admin with the password from Step 4:

The home page lists the drillbit (on 127.0.0.1, version 1.20.0), its memory use and ports, and shows admin as the only administrator:

Without signing in, a visitor sees only this status page. Every query, storage, options, profile, log and shutdown page sends them to the login form.

Step 6: Run SQL from the web console

Open Query, type a query and choose Submit. The image ships sample files in /var/lib/drill/data/samples; this query reads a CSV file with a header row:

SELECT city, country, population_millions
FROM dfs.`samples/cities.csv`
ORDER BY CAST(population_millions AS DOUBLE) DESC

The result appears as a table you can search, sort and export:

Profiles lists every query with its plan and timings. cp is Drill's built-in classpath plugin with demo data (for example SELECT * FROM cp.`employee.json` LIMIT 20).

Step 7: Use the REST API

Without a login, queries are sent to the login form, port 80 redirects to HTTPS, and a plain health check stays open for load balancers:

H=$(sudo grep '^DRILL_URL=' /root/drill-credentials.txt | sed -E 's#^DRILL_URL=https://([^/]+)/.*#\1#')
curl -s -o /dev/null -w 'http://          -> %{http_code} %{redirect_url}\n' --resolve "$H:80:127.0.0.1" "http://$H/"
curl -sk -o /dev/null -w 'query, no login -> %{http_code} %{redirect_url}\n' --resolve "$H:443:127.0.0.1" -X POST -H 'Content-Type: application/json' -d '{"queryType":"SQL","query":"SELECT 1"}' "https://$H/query.json"
curl -sk -o /dev/null -w 'health          -> %{http_code}\n' --resolve "$H:443:127.0.0.1" "https://$H/health"

http://          -> 301 https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/
query, no login -> 307 https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/mainLogin?redirect=%2Fquery.json
health          -> 200

To use the API, sign in once at /j_security_check to get a session cookie, then post SQL to /query.json. This example keeps the password in a root-only temporary file, so it never appears in the process list or your shell history, and queries a JSON, a CSV and a Parquet file:

sudo bash -c '
  C=/root/drill-credentials.txt; W=$(mktemp -d /run/drill.XXXXXX); trap "rm -rf $W" EXIT
  H=$(grep "^DRILL_URL=" $C | sed -E "s#^DRILL_URL=https://([^/]+)/.*#\1#"); R="--resolve $H:443:127.0.0.1"
  { printf "j_username=admin&j_password="; grep "^DRILL_ADMIN_PASSWORD=" $C | cut -d= -f2- | tr -d "\n"; } > $W/login
  curl -sk $R -c $W/cookies -o /dev/null -w "login: HTTP %{http_code}\n" --data-binary @$W/login "https://$H/j_security_check"
  sql() { printf "{\"queryType\":\"SQL\",\"query\":\"%s\"}" "$1" > $W/q; curl -sk $R -b $W/cookies -H "Content-Type: application/json" --data-binary @$W/q "https://$H/query.json" | jq -c ".rows"; }
  sql "SELECT full_name, position_title FROM cp.\`employee.json\` LIMIT 3"
  sql "SELECT city, population_millions FROM dfs.\`samples/cities.csv\` WHERE country = '"'"'Japan'"'"'"
  sql "SELECT N_NAME, N_REGIONKEY FROM dfs.\`samples/nation.parquet\` LIMIT 3"
'

login: HTTP 303
[{"full_name":"Sheri Nowmer","position_title":"President"},{"full_name":"Derrick Whelply","position_title":"VP Country Manager"},{"full_name":"Michael Spence","position_title":"VP Country Manager"}]
[{"city":"Tokyo","population_millions":"37.1"}]
[{"N_NAME":"ALGERIA","N_REGIONKEY":0},{"N_NAME":"ARGENTINA","N_REGIONKEY":1},{"N_NAME":"BRAZIL","N_REGIONKEY":1}]

From your own machine, use the same two calls against https://<public-ip>/ with -k (the self signed certificate) and a cookie jar. nginx refuses a state-changing request whose Origin header names another web site (HTTP 403), so a malicious page cannot drive your signed-in browser session.

Step 8: Your data and what a signed-in user can reach

Put your own CSV, JSON or Parquet files in /var/lib/drill/data (readable by the drill user) and query them by their path relative to that directory, as above. The image configures the dfs file plugin with three workspaces:

sudo jq -r '.storage.dfs.workspaces | to_entries[] | "\(.key) \(.value.location) writable=\(.value.writable)"' /opt/drill/conf/storage-plugins-override.conf

default /var/lib/drill/data writable=false
root /var/lib/drill/data writable=false
tmp /var/lib/drill/data/tmp writable=true

dfs. and dfs.root. read the data directory; dfs.tmp. is where CREATE TABLE ... AS writes. The CSV format reads the first line as column names. This configuration lives in /opt/drill/conf/storage-plugins-override.conf and is reapplied every time the drillbit starts, so change the dfs plugin in that file (then sudo systemctl restart drill) rather than on the Storage page:

Queries cannot leave the data directory. Here the same Parquet file is read from the data directory, then the Drill release's copy outside it is refused whether it is named by absolute path, by file:/// URL or by ../:

sudo bash -c '
  C=/root/drill-credentials.txt; W=$(mktemp -d /run/drill.XXXXXX); trap "rm -rf $W" EXIT
  H=$(grep "^DRILL_URL=" $C | sed -E "s#^DRILL_URL=https://([^/]+)/.*#\1#"); R="--resolve $H:443:127.0.0.1"
  { printf "j_username=admin&j_password="; grep "^DRILL_ADMIN_PASSWORD=" $C | cut -d= -f2- | tr -d "\n"; } > $W/login
  curl -sk $R -c $W/cookies -o /dev/null --data-binary @$W/login "https://$H/j_security_check"
  for t in "samples/region.parquet" "/opt/drill/sample-data/region.parquet" "file:///opt/drill/sample-data/region.parquet" "../../../../opt/drill/sample-data/region.parquet"; do
    printf "{\"queryType\":\"SQL\",\"query\":\"SELECT COUNT(*) AS n FROM dfs.\`%s\`\"}" "$t" > $W/q
    printf "%-52s %s\n" "dfs.\`$t\`" "$(curl -sk $R -b $W/cookies -H "Content-Type: application/json" --data-binary @$W/q "https://$H/query.json" | jq -r "[.queryState, (.rows[0].n // \"-\")] | join(\" \")")"
  done
'

dfs.`samples/region.parquet`                         COMPLETED 5
dfs.`/opt/drill/sample-data/region.parquet`          FAILED -
dfs.`file:///opt/drill/sample-data/region.parquet`   FAILED -
dfs.`../../../../opt/drill/sample-data/region.parquet` FAILED -

Write a table to dfs.tmp, read it back and drop it:

sudo bash -c '
  C=/root/drill-credentials.txt; W=$(mktemp -d /run/drill.XXXXXX); trap "rm -rf $W" EXIT
  H=$(grep "^DRILL_URL=" $C | sed -E "s#^DRILL_URL=https://([^/]+)/.*#\1#"); R="--resolve $H:443:127.0.0.1"
  { printf "j_username=admin&j_password="; grep "^DRILL_ADMIN_PASSWORD=" $C | cut -d= -f2- | tr -d "\n"; } > $W/login
  curl -sk $R -c $W/cookies -o /dev/null --data-binary @$W/login "https://$H/j_security_check"
  sql() { printf "{\"queryType\":\"SQL\",\"query\":\"%s\"}" "$1" > $W/q; curl -sk $R -b $W/cookies -H "Content-Type: application/json" --data-binary @$W/q "https://$H/query.json" | jq -c "{state: .queryState, rows: .rows}"; }
  sql "CREATE TABLE dfs.tmp.\`big_cities\` AS SELECT city, CAST(population_millions AS DOUBLE) AS pop FROM dfs.\`samples/cities.csv\` WHERE CAST(population_millions AS DOUBLE) > 20"
  sql "SELECT * FROM dfs.tmp.\`big_cities\` ORDER BY pop DESC"
  ls /var/lib/drill/data/tmp/big_cities
  sql "DROP TABLE dfs.tmp.\`big_cities\`"
'

{"state":"COMPLETED","rows":[{"Fragment":"0_0","Number of records written":3}]}
{"state":"COMPLETED","rows":[{"city":"Tokyo","pop":37.1},{"city":"Delhi","pop":33.8},{"city":"Shanghai","pop":29.9}]}
0_0_0.parquet
{"state":"COMPLETED","rows":[{"ok":true,"summary":"Table [big_cities] dropped"}]}

Exactly what a signed-in user can reach. admin is Drill's administrator, so it can:

• read files under /var/lib/drill/data and write tables under /var/lib/drill/data/tmp, through dfs;
• read Drill's built-in demo data through cp;
• enable or create other storage plugins on the Storage page (S3, HTTP APIs, JDBC databases, MongoDB, Kafka and others ship disabled), which then reach whatever network address you configure. A new file-system plugin pointed at file:/// reads any file the unprivileged drill user can read; the systemd sandbox still hides /root, /home, the TLS key and the first boot state from it, and allows writes only under /var/lib/drill;
• view query profiles, options, logs and threads, and shut the drillbit down.

It cannot reach the instance metadata service (and so never the instance role's credentials) from Drill, ZooKeeper or nginx. Uploading Java code as dynamic user-defined functions is switched off (exec.udf.enable_dynamic_support). Keep the admin password to the people you trust with all of the above. Drill checks workspace paths by prefix, so keep /var/lib/drill itself root-owned and do not create directories beside data whose names start with data.

Step 9: Connect JDBC and ODBC clients

The JDBC/ODBC port 31010 listens on loopback only, and the user name and password travel unencrypted on it, so connect through an SSH tunnel. On the instance, the sqlline client that ships with Drill connects like this (the password is read from a root-only properties file, not the command line):

sudo bash -c '
  W=$(mktemp -d /run/drill.XXXXXX); trap "rm -rf $W" EXIT
  { echo "url=jdbc:drill:drillbit=127.0.0.1:31010"; echo "driver=org.apache.drill.jdbc.Driver"; echo "user=admin"
    printf "password="; grep "^DRILL_ADMIN_PASSWORD=" /root/drill-credentials.txt | cut -d= -f2-; } > $W/drill.properties
  printf "!properties %s\nSELECT country, COUNT(*) AS cities FROM dfs.\`samples/cities.csv\` GROUP BY country ORDER BY country;\n!quit\n" $W/drill.properties > $W/run.sql
  cd $W && HOME=$W /opt/drill/bin/sqlline --color=false -f $W/run.sql 2>/dev/null | grep -E "^[+|]|rows selected"
'

+----------------+--------+
|    country     | cities |
+----------------+--------+
| China          | 1      |
| India          | 1      |
| Japan          | 1      |
| United Kingdom | 1      |
| United States  | 1      |
+----------------+--------+

From your own machine, open a tunnel and point your BI tool or JDBC client at it:

ssh -i <your-key.pem> -N -L 31010:127.0.0.1:31010 ubuntu@<public-ip>

JDBC URL:  jdbc:drill:drillbit=localhost:31010
Driver:    org.apache.drill.jdbc.Driver (drill-jdbc-all-1.20.0.jar, also in /opt/drill/jars/jdbc-driver/)
User:      admin   Password: DRILL_ADMIN_PASSWORD from Step 4

Use the drillbit= form, not zk=: ZooKeeper advertises the drillbit as 127.0.0.1, which is only right through the tunnel.

Step 10: Memory and configuration

The drillbit sizes its Java heap at 30% and its direct memory at 40% of the instance's RAM (at least 1 GiB each), so a larger instance type gets more memory with no change. On an m5.large-sized instance:

ps -o args= -u drill | grep -oE -- '-Xm[sx][0-9]+[MG]|-XX:MaxDirectMemorySize=[0-9]+[MG]' | sort -u

-XX:MaxDirectMemorySize=3126M
-Xms2344M
-Xmx2344M

To set them yourself, create /etc/cloudimg/drill-memory.env with, for example, DRILL_HEAP=4G and DRILL_MAX_DIRECT_MEMORY=8G, then sudo systemctl restart drill.

File	Purpose
/opt/drill/conf/drill-override.conf	Drill settings: authentication, ports, data-volume paths, option defaults
/opt/drill/conf/storage-plugins-override.conf	The dfs plugin (reapplied at every start)
/opt/drill/conf/drill-env.sh	Java, host name and memory settings
/opt/drill/conf/htpasswd	The admin password hash
/etc/cloudimg/zookeeper/zoo.cfg	ZooKeeper (loopback, single node)

Change system options (for example query memory or planner settings) on the Options page or with ALTER SYSTEM SET; they are stored in ZooKeeper on the data volume.

Step 11: Your own DNS name and certificate

Point a DNS record at the instance, then set the name Drill is served as. This mints a new self signed certificate for the name, updates nginx and the URL in /root/drill-credentials.txt, and keeps the name across reboots:

sudo drill-set-hostname <your-domain>

To use a certificate from your own certificate authority instead, copy it over the generated pair and reload nginx (running drill-set-hostname again replaces it with a self signed one):

sudo install -m 0644 <your-domain>.crt /etc/ssl/drill/drill.crt
sudo install -m 0600 <your-domain>.key /etc/ssl/drill/drill.key
sudo nginx -t && sudo systemctl reload nginx

Step 12: Security checks and password rotation

The certificate was generated on this instance and names its own addresses:

H=$(sudo grep '^DRILL_URL=' /root/drill-credentials.txt | sed -E 's#^DRILL_URL=https://([^/]+)/.*#\1#')
echo | openssl s_client -connect 127.0.0.1:443 -servername "$H" 2>/dev/null | openssl x509 -noout -subject -ext subjectAltName

subject=CN = ec2-<public-ip-dashed>.<region>.compute.amazonaws.com, O = cloudimg
X509v3 Subject Alternative Name:
    DNS:ec2-<public-ip-dashed>.<region>.compute.amazonaws.com, IP Address:<public-ip>, IP Address:<private-ip>, IP Address:127.0.0.1, DNS:localhost

Drill and ZooKeeper run unprivileged in a sandbox, and none of Drill, ZooKeeper or nginx can reach the instance metadata service:

systemctl show drill -p User -p ProtectSystem -p ProtectHome -p NoNewPrivileges -p IPAddressDeny
systemctl show zookeeper -p User -p IPAddressDeny
systemctl show nginx -p IPAddressDeny

IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
User=drill
ProtectHome=yes
ProtectSystem=strict
NoNewPrivileges=yes
IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32
User=zookeeper
IPAddressDeny=fd00:ec2::254/128 168.63.129.16/32 169.254.169.254/32

Drill 1.20 has no setting for its listen address, so the image loads a small library into the drillbit and ZooKeeper (/usr/local/lib/cloudimg/libcloudimg-bindlo.so, source in /usr/local/share/cloudimg/drill-bindlo.c) that binds their listening sockets to 127.0.0.1; outbound connections are unchanged. A start-up check stops the drillbit if any of its ports is not on loopback:

sudo journalctl -u drill -o cat --no-pager | grep drill-bind-check | tail -1

drill-bind-check: every drillbit listener is loopback

nginx passes Drill a fixed host name and the real client address from the connection, never values a client sends:

grep -E 'proxy_set_header (Host|X-Forwarded-For|X-Real-IP)|proxy_redirect' /etc/nginx/sites-available/cloudimg-drill

        proxy_set_header Host 127.0.0.1:8047;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $remote_addr;
        proxy_redirect http://127.0.0.1:8047/ https://$drill_host/;

Only the hash of the password is in Drill's password file, which holds exactly one login:

sudo sed -E 's/\$apr1\$.*/$apr1$<per-instance-hash>/' /opt/drill/conf/htpasswd

admin:$apr1$<per-instance-hash>

To set a new password, write its hash (Drill reads the file at every login, no restart needed). Start the first line with a space so most shells keep it out of your history:

 printf '%s\n' '<new-password>' | openssl passwd -apr1 -stdin | sed 's/^/admin:/' | sudo tee /opt/drill/conf/htpasswd >/dev/null
sudo chown root:drill /opt/drill/conf/htpasswd && sudo chmod 0640 /opt/drill/conf/htpasswd

Then update or delete DRILL_ADMIN_PASSWORD in /root/drill-credentials.txt so it does not hold an old password. To add more users, add more user:hash lines; only users listed in the security.admin.users option are administrators.

Step 13: Server components

Component	Path
Apache Drill 1.20.0	/opt/apache-drill-1.20.0 (/opt/drill)
Data directory (dfs)	/var/lib/drill/data (data volume)
Drill logs, profiles, ZooKeeper data	/var/lib/drill/logs, /var/lib/drill/sys.store, /var/lib/drill/zookeeper
Service units	drill.service, zookeeper.service, apache-drill-firstboot.service
nginx site	/etc/nginx/sites-available/cloudimg-drill
Certificate and key	/etc/ssl/drill/
Host name helper	/usr/local/sbin/drill-set-hostname
Credentials	/root/drill-credentials.txt (mode 0600)

H=$(sudo grep '^DRILL_URL=' /root/drill-credentials.txt | sed -E 's#^DRILL_URL=https://([^/]+)/.*#\1#')
curl -sk --resolve "$H:443:127.0.0.1" "https://$H/cluster.json" | jq -r '.currentVersion'
java -version 2>&1 | head -1

1.20.0
openjdk version "11.0.32.1" 2026-08-18

Drill's own log is /var/lib/drill/logs/drillbit.log; sudo journalctl -u drill -u zookeeper -u nginx shows the services.

Step 14: Security recommendations

• Allow ports 22, 80 and 443 in the security group from your own networks only. Never open 8047, 31010 or 2181; use the SSH tunnel in Step 9.
• Keep the admin password secret and rotate it if it may have been shared (Step 12). Anyone with it can enable storage plugins that reach your other systems.
• Use your own DNS name and a certificate your clients trust (Step 11).
• Keep the operating system patched; unattended security upgrades are on, and sudo apt-get update && sudo apt-get upgrade applies everything else.
• Snapshot the data volume to back up your files, query profiles and Drill settings.

Support and licensing

Apache Drill and Apache ZooKeeper are open source under the Apache License 2.0; there is no per user or per CPU licence fee. The AMI price covers cloudimg's hardening, first boot setup and support. Apache Drill, Apache ZooKeeper and Apache are trademarks of The Apache Software Foundation; cloudimg is not affiliated with or endorsed by it.

• Email: support@cloudimg.co.uk
• Website: https://www.cloudimg.co.uk
• Support: 24/7, with a 24 hour response SLA