SecureAnyCloud
Deployment guide

Apache Drill

Overview

Apache Drill is the open-source, schema-free distributed SQL query engine for big data exploration. It runs ANSI SQL directly against files (JSON, Parquet, CSV), object storage, NoSQL stores and relational databases, with no schemas or ETL to define up front. The SecureAnyCloud image installs Apache Drill 1.20.0 on OpenJDK 11 at /opt/drill, protects the Web UI with a unique admin password generated on the first boot of every VM, and is backed by 24/7 SecureAnyCloud support.

What you get:

  • Apache Drill 1.20.0 drillbit on OpenJDK 11 at /opt/drill, run as a dedicated drill system user.
  • A local single-node Apache ZooKeeper (zookeeper.service) on loopback :2181 for Drill cluster coordination.
  • An nginx reverse proxy on :80 in front of the Drill Web UI / REST API (:8047). The JDBC/ODBC user port is 31010.
  • A dedicated Azure data disk at /var/lib/drill holding Drill's sys.store, query profiles, logs and the ZooKeeper data. It is separate from the OS disk and re-provisioned with every VM.
  • A per-VM Web UI admin password (DRILL_ADMIN_PASSWORD) generated at first boot and stored in a root-only file, with PLAIN/htpasswd authentication.
  • zookeeper.service, drill.service and nginx.service as systemd units, enabled and active.
  • 24/7 SecureAnyCloud support.

Prerequisites

You need:

  • An active Azure subscription, an SSH key pair, and a VNet and subnet in the target region.
  • A VM size of Standard_B4ms (4 vCPU / 16 GiB RAM), which is a good starting point for the drillbit JVM. Scale up for larger scans and concurrency.
  • NSG inbound rules that allow 22/tcp from your management network and 80/tcp for the Web UI. Front the Web UI with TLS for public exposure (see Enabling HTTPS).
  • Optionally, an NSG rule for the Drill user port 31010. It is bound on the VM's private IP for JDBC/ODBC clients inside your VNet, so open it in the NSG only for the application networks that need it.

Step 1 - Deploy from the Azure Marketplace

Sign in to the Azure Portal, choose Create a resource, search the Marketplace for Apache Drill by SecureAnyCloud and select Create.

On the Basics tab, pick your subscription, resource group, region and size. Under Administrator account, choose SSH public key and paste your key. Under Inbound port rules, allow SSH (22) and HTTP (80).

Review the dedicated data disk on the Disks tab, then select Review + create and Create.

Step 2 - Deploy from the Azure CLI

Accept the Marketplace terms once per subscription, then create the VM:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:apache-drill-ubuntu-24-04:default:latest
az vm create \
  --resource-group <your-rg> \
  --name drill \
  --image sac1647283583153:apache-drill-ubuntu-24-04:default:latest \
  --size Standard_B4ms \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_ed25519.pub \
  --vnet-name <your-vnet> --subnet <your-subnet> \
  --public-ip-sku Standard
az vm open-port --resource-group <your-rg> --name drill --port 80 --priority 1010

Step 3 - Connect to your VM

SSH in to the VM as azureuser:

ssh azureuser@<vm-public-ip>

Step 4 - Confirm the services are running

Check that ZooKeeper, Drill and nginx are active:

systemctl is-active zookeeper.service drill.service nginx.service

All three services report active. On first boot, the drillbit can take a minute or two to start fully while the JVM warms up and registers with ZooKeeper. The Web UI port 8047 and the user RPC port 31010 come up once it is ready.

Step 5 - Retrieve your Web UI password

Your VM generates its own Web UI admin password on first boot and writes it to a root-only file. Print the admin user and password:

sudo grep -E '^DRILL_ADMIN_USER|^DRILL_ADMIN_PASSWORD' /root/drill-credentials.txt

The DRILL_ADMIN_PASSWORD value is the password. Sign in to the Web UI as admin with it.

Step 6 - Open the Drill Web UI

Browse to http://<vm-public-ip>/ and sign in as admin with the password from Step 5.

The Drill Web UI shows the cluster (drillbits, ports, version and uptime), a SQL Query editor, completed-query Profiles and runtime Metrics.

Step 7 - Run a SQL query through the REST API

Drill's Web UI is backed by a REST API. Log in to obtain a session cookie, then run a query against the bundled classpath dataset cp.`employee.json`. The request goes to the local drillbit through nginx:

PW='<DRILL_ADMIN_PASSWORD>'
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 employee_id, full_name, position_title FROM cp.`employee.json` LIMIT 5"}' \
  http://127.0.0.1/query.json rm -f "$CJ"

The response is JSON containing a queryId, the result columns and the matching rows. You can submit any SQL that Drill supports the same way.

Step 8 - Check the Drill version and cluster

Confirm the running version and the registered drillbits by querying Drill's system tables:

PW='<DRILL_ADMIN_PASSWORD>'
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 version FROM sys.version"}' http://127.0.0.1/query.json
curl -s -b "$CJ" -X POST -H 'Content-Type: application/json' \
  -d '{"queryType":"SQL","query":"SELECT hostname, user_port, http_port FROM sys.drillbits"}' http://127.0.0.1/query.json rm -f "$CJ"

sys.version returns 1.20.0, and sys.drillbits lists the single node with its user (31010) and http (8047) ports.

Step 9 - Query your own data and connect clients

Drill queries files and object storage directly. Configure storage plugins in the Web UI Storage tab, for example a dfs workspace pointing at a directory of Parquet/JSON/CSV, or Azure Blob via the dfs plugin with the Azure connector. Then run SELECT ... FROM dfs.\/path/to/data`. For BI tools and applications, connect over JDBC/ODBC to the Drill user port at:31010from inside your VNet (open31010/tcp` in the NSG for those application networks only).

Enabling HTTPS

For production, terminate TLS at nginx with a real domain pointed at the VM's public IP. Install certbot and request a certificate, replacing the domain with yours:

sudo apt-get update && sudo apt-get install -y certbot python3-certbot-nginx
sudo certbot --nginx -d your-domain.example.com

Backup and maintenance

Drill's working state (sys.store, query profiles, logs and the ZooKeeper data) lives on the dedicated data disk at /var/lib/drill. Snapshot that disk in Azure to back up cluster and query state. Keep the OS patched with sudo apt update && sudo apt upgrade. The drillbit restarts cleanly with sudo systemctl restart drill, and ZooKeeper with sudo systemctl restart zookeeper.

Support

This image is backed by 24/7 SecureAnyCloud support. Contact us by email and chat for help with storage plugins, query tuning, JDBC/ODBC connectivity, authentication, TLS and backups.

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.

Deploy on Azure

Launch Apache Drill on Ubuntu 24.04 LTS on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on Azure