Apache Drill on AWS User Guide

Overview

This image runs Apache Drill, the open source schema free distributed SQL query engine. Drill lets you run standard ANSI SQL directly against files, JSON, Parquet, CSV, relational databases, NoSQL stores and object storage, with no schema definition or data loading step. You point Drill at your data and query it in place.

Apache Drill 1.22.0 is installed under /opt/drill and runs as a dedicated unprivileged drill system account under a systemd service that starts the drillbit on boot and restarts it on failure. The runtime is a headless OpenJDK 11. A single node Apache ZooKeeper, bound to loopback, coordinates the drillbit. The drillbit sys.store, query profiles and the ZooKeeper data directory live on a dedicated, independently resizable EBS data volume mounted at /var/lib/drill, so your query state survives instance replacement.

Apache Drill ships with authentication disabled. This image enables Drill Web and REST authentication using the PLAIN mechanism with an htpasswd credential store, and designates admin as a Drill administrator, so the Web console is locked behind a sign in and only the admin account can manage storage plugins and system options. The drillbit binds to 127.0.0.1:8047 (Web and REST) and to the instance private address for the RPC ports, and is never exposed directly. An nginx reverse proxy publishes the Web console on port 80. The admin password is generated on the first boot of every deployed instance, so two instances launched from the same Amazon Machine Image never share a password. It is written to /root/drill-credentials.txt with mode 0600 so that only the root user can read it.

The drillbit is a JVM application and takes a short time to start after first boot before the Web console answers.

Prerequisites

Before you deploy this image you need:

• An Amazon Web Services account where you can launch EC2 instances
• IAM permissions to launch instances, create security groups, and subscribe to AWS Marketplace products
• An EC2 key pair in the target Region for SSH access to the instance
• A VPC and subnet in the target Region, with a security group allowing inbound port 22 from your management network and port 80 for the Web console
• The AWS CLI (version 2) installed locally if you plan to deploy from the command line

Step 1: Launch the Instance from the AWS Marketplace

Sign in to the AWS Management Console, open the EC2 service, and select Launch instance. Under Application and OS Images choose AWS Marketplace AMIs and search for Apache Drill. Select the cloudimg listing and choose Select, then Continue on the subscription summary.

Pick an instance type of m5.large or larger. Choose your EC2 key pair under Key pair (login). Under Network settings select your VPC and subnet, and either create or select a security group that opens port 22 from your management network and port 80 for the Web console. Leave the root volume at the default size or larger.

Select Launch instance. First boot initialisation generates the admin password, and the JVM drillbit then takes a short time to become ready after the instance state becomes Running and the status checks pass.

Step 2: Launch the Instance from the AWS CLI

The following block launches an instance from the cloudimg Apache Drill Marketplace AMI into an existing subnet and security group. Replace <ami-id> with the AMI ID shown on the Marketplace listing, <key-name> with your EC2 key pair name, <subnet-id> with your subnet ID, and <security-group-id> with a security group that opens ports 22 and 80 as described above.

aws ec2 run-instances \
  --image-id <ami-id> \
  --instance-type m5.large \
  --key-name <key-name> \
  --subnet-id <subnet-id> \
  --security-group-ids <security-group-id> \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=apache-drill}]'

When the instance reaches the Running state and its status checks pass, note its public IP address or DNS name from the EC2 console or with aws ec2 describe-instances.

Step 3: Connect to Your Instance

Connect over SSH using your key pair and the login user for your operating system variant.

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

Step 4: Retrieve the Admin Password

The Drill admin password is unique to your instance and was generated on first boot. Read it as root with the command below. The file lists the console URL, the admin user (admin) and the generated password. Keep this password somewhere safe.

sudo cat /root/drill-credentials.txt

Step 5: Confirm Apache Drill Is Running

Over SSH, confirm that ZooKeeper, the drillbit and the nginx proxy are all active.

sudo systemctl is-active zookeeper drill nginx

All three services should report active. The drillbit binds to loopback for the Web console, fronted by nginx on port 80. Confirm its unauthenticated health endpoint answers locally:

curl -s -o /dev/null -w '%{http_code}\n' http://127.0.0.1/health

This returns 200 once nginx and the drillbit are up. You can also confirm the drillbit is listening on its Web and RPC ports:

sudo ss -tlnp | grep -E ':8047|:31010'

Step 6: Sign In to the Web Console

The Apache Drill Web console is served on port 80 by nginx. In a browser, go to:

http://<instance-public-ip>/

The cluster overview page is shown, listing the online drillbit, its version and its ports. Choose Log In at the top right and sign in as admin with the password from the credentials file. As an administrator you have access to the query editor, query profiles, storage plugin configuration and system options.

Step 7: Run a SQL Query

Open the Query tab. Drill queries self describing data in place, so you can query the bundled sample data straight away. Enter a query and choose Submit:

SELECT full_name, position_title, salary FROM cp.`employee.json` LIMIT 10

The results are returned as a sortable, searchable grid, with the query recorded as a completed profile.

You can also run queries against the REST API. The following block signs in with the per instance admin password and runs the same query, returning JSON:

PW=$(sudo grep '^DRILL_ADMIN_PASSWORD=' /root/drill-credentials.txt | cut -d= -f2-)
CJ=$(mktemp)
curl -s -c "$CJ" -o /dev/null -X POST \
  --data-urlencode 'j_username=admin' --data-urlencode "j_password=$PW" \
  http://127.0.0.1/j_security_check
curl -s -b "$CJ" -X POST -H 'Content-Type: application/json' \
  -d '{"queryType":"SQL","query":"SELECT * FROM cp.`employee.json` LIMIT 5"}' \
  http://127.0.0.1/query.json
rm -f "$CJ"

Step 8: Review Query Profiles

Open the Profiles tab to see the history of completed and running queries. Each profile records the query text, the user, the state, the duration and the foreman drillbit, and you can open a profile to inspect the full execution plan and operator level metrics.

Step 9: Configure Storage Plugins

Drill reads data through storage plugins. Open the Storage page (available to the admin account, which this image designates as a Drill administrator) to enable, disable and configure plugins. From here you can add a plugin for Amazon S3, a local or network file system, a relational database over JDBC, or one of the other supported sources, and define workspaces and file formats. Once a plugin is configured you query its data with the same SQL you used against the sample data, for example SELECT * FROM s3.mybucket.data/events.parquet``.

Storage plugin definitions are persisted in the ZooKeeper backed system store, so they survive a drillbit restart and instance replacement.

Step 10: Connect JDBC and ODBC Clients

Applications and BI tools connect to Drill over its user RPC port (31010) using the Drill JDBC or ODBC drivers, authenticating with the admin user and the per instance password. The RPC port is bound to the instance private address and is not opened in the image's security group by default; open port 31010 to trusted client networks, or tunnel it over SSH, before connecting a client. The Web console remains the simplest way to run ad hoc SQL.

Step 11: The Data Volume

The drillbit sys.store, the query profiles and the ZooKeeper data directory live on a dedicated EBS volume mounted at /var/lib/drill. This keeps query state off the operating system disk and lets you resize or snapshot it independently. Confirm the mount with:

df -h /var/lib/drill

To grow the store, expand the EBS volume in the AWS console, then grow the filesystem on the instance with sudo resize2fs on the underlying device. Because the query state lives on this volume, it survives instance replacement when you re attach or restore the volume.

Step 12: Enable HTTPS

The Web console is served over plain HTTP on port 80 by nginx. For production use, place it behind TLS. Obtain a certificate for your domain (for example with a managed certificate on an Application Load Balancer in front of the instance, or with Certbot installed on the instance), then configure nginx to listen on 443 with your certificate and proxy to 127.0.0.1:8047 exactly as the bundled site does for port 80, keeping the Drill login in place. Restrict the security group so ports 80 and 443 are reachable only from the networks that use the console.

Step 13: Backup and Maintenance

Back up your query state and storage plugin configuration by snapshotting the /var/lib/drill EBS volume, which captures the data tier including the ZooKeeper store. Apply operating system security updates with sudo apt-get update && sudo apt-get upgrade and reboot when a new kernel is installed; ZooKeeper, Apache Drill and nginx start automatically on boot, and the drillbit takes a short time to become ready again after a reboot.

Support

This image is published and supported by cloudimg. Support covers deployment, storage plugin configuration, query authoring and optimisation, authentication, JDBC and ODBC connectivity, TLS and JVM tuning. Contact cloudimg through the support channel listed on the AWS Marketplace listing.

All product and company names are trademarks or registered trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.