Apache Hive on AWS User Guide

Overview

This image runs Apache Hive, the open source data warehouse software that lets you read, write and manage large datasets in distributed storage using familiar SQL (HiveQL). You define tables over your data and query them with standard SQL, while Hive translates each query into a distributed execution plan over HDFS.

Apache Hive 4.0.1 is installed under /opt/hive and runs as a dedicated unprivileged hive system account. It sits on top of a single node Apache Hadoop 3.3.6 cluster running HDFS in pseudo distributed mode (a NameNode and a DataNode), so the Hive warehouse lives on HDFS at /user/hive/warehouse. Table and partition metadata is held in a production grade MariaDB metastore, not the embedded Derby database, with the schema initialised by schematool. The runtime is a headless OpenJDK 11.

HiveServer2 exposes a JDBC and Thrift interface on 127.0.0.1:10000 with a read only web UI on 127.0.0.1:10002; the Hive Metastore Thrift service runs on 127.0.0.1:9083. Every one of these ports, along with HDFS and MariaDB, binds to loopback only and is never exposed on the network. The HDFS data directories, the MariaDB metastore datadir and the Hadoop and Hive logs all live on a dedicated, independently resizable EBS data volume mounted at /var/lib/hive, so your warehouse and metadata survive instance replacement.

Apache HiveServer2 accepts unauthenticated connections by default. This image enables HiveServer2 client authentication with a custom per instance credential provider, and fronts the read only web UI with an nginx reverse proxy on port 80 protected by HTTP Basic authentication. 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, and the loopback only MariaDB metastore password is rotated per instance as well. The admin password is written to /root/hive-credentials.txt with mode 0600 so that only the root user can read it. No shared or default credentials ship in the image.

The Hadoop, metastore and HiveServer2 JVMs take a short time to start after first boot before HiveServer2 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 UI
• The AWS CLI (version 2) installed locally if you plan to deploy from the command line

The Hadoop, Metastore, HiveServer2 and MariaDB JVMs together are memory hungry, so choose an instance with adequate RAM. m5.large is a good starting point; scale up for larger warehouses and higher concurrency.

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 Hive. 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 UI. Leave the root volume at the default size or larger; a dedicated data volume for the warehouse and metastore is created automatically.

Select Launch instance. First boot initialisation generates the admin password and rotates the metastore password, then the JVM stack 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 Hive 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-hive}]'

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 /path/to/your-key.pem ubuntu@<public-ip>

Step 4: Confirm the Services Are Running

The data warehouse stack runs as five systemd units: hadoop.service (HDFS), mariadb.service (the metastore database), hive-metastore.service, hiveserver2.service, and nginx.service (the web UI proxy). Confirm they are all active:

systemctl is-active hadoop.service mariadb.service hive-metastore.service hiveserver2.service nginx.service

Each service reports active. On the first boot the stack can take a couple of minutes to come fully up while HDFS leaves safe mode and HiveServer2 warms up.

Step 5: Retrieve Your Hive Password

The Hive admin password is generated uniquely on the first boot of your instance and written to a root only file:

sudo cat /root/hive-credentials.txt

The HIVE_ADMIN_PASSWORD value is the password. Connect to HiveServer2 over JDBC as user hive with it, and sign in to the web UI as hive with the same password.

Step 6: Run SQL with beeline

Hive ships beeline, the JDBC command line client. The block below reads your per instance password from the credentials file and runs a CREATE / INSERT / SELECT round trip against the HDFS warehouse, then drops the demo table:

export JAVA_HOME=$(dirname "$(dirname "$(readlink -f "$(command -v java)")")")
PW=$(sudo grep '^HIVE_ADMIN_PASSWORD=' /root/hive-credentials.txt | cut -d= -f2-)
sudo -u hive JAVA_HOME="$JAVA_HOME" HADOOP_HOME=/opt/hadoop /opt/hive/bin/beeline \
  -u 'jdbc:hive2://127.0.0.1:10000/default' -n hive -p "$PW" \
  -e "CREATE TABLE IF NOT EXISTS guide_demo(id int, v string);
INSERT INTO guide_demo VALUES (1,'cloudimg');
SELECT v FROM guide_demo WHERE id=1;
DROP TABLE guide_demo;"

beeline connects, runs each statement, and prints the row cloudimg from the SELECT. Managed tables are stored in the HDFS warehouse under /user/hive/warehouse, with their schema recorded in the MariaDB metastore. To connect interactively, run the same beeline -u ... -n hive -p '<HIVE_ADMIN_PASSWORD>' command without -e and enter statements at the prompt.

Step 7: Inspect the Metastore and Warehouse

List the HDFS warehouse directory to see your managed table directories. The schema metadata itself lives in the MariaDB metastore database on the dedicated data volume.

export JAVA_HOME=$(dirname "$(dirname "$(readlink -f "$(command -v java)")")")
sudo -u hive JAVA_HOME="$JAVA_HOME" HADOOP_HOME=/opt/hadoop \
  /opt/hadoop/bin/hdfs dfs -ls /user/hive/warehouse

Step 8: Open the HiveServer2 Web UI

The read only HiveServer2 web UI is published on port 80 through nginx and protected by HTTP Basic authentication. Browse to http://<public-ip>/ and sign in as hive with the password from Step 5. The web UI shows active sessions, open and historical queries, the query execution stages, the configuration in effect, and software attributes. An unauthenticated http://<public-ip>/health endpoint returns ok for liveness checks:

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

Step 9: Connect from Your Applications

Point JDBC clients (BI tools, ETL jobs, beeline from another host) at jdbc:hive2://<public-ip>:10000/default with user hive and the per instance password. Because port 10000 is bound to loopback by default, reach it over an SSH tunnel or a private endpoint rather than opening it to the internet:

ssh -i /path/to/your-key.pem -L 10000:127.0.0.1:10000 ubuntu@<public-ip>

With the tunnel open, connect a local JDBC client to jdbc:hive2://127.0.0.1:10000/default.

Enabling HTTPS

For production, terminate TLS at nginx with a real domain pointed at the instance's public IP, and restrict the security group so port 80 and 443 are reachable only from your network. Install certbot and request a certificate (replace the domain):

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

Backup and Maintenance

The HDFS data, the MariaDB metastore and the Hive logs all live on the dedicated data volume mounted at /var/lib/hive. Snapshot that EBS volume in AWS to back up your warehouse and schema, and keep the OS patched with sudo apt update && sudo apt upgrade. The stack restarts cleanly with:

sudo systemctl restart hadoop hive-metastore hiveserver2 nginx

Support

This image is backed by 24/7 cloudimg support. Contact us by email and chat for help with Hive and Hadoop configuration, the metastore, HiveQL authoring and query tuning, JDBC and ODBC connectivity, 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.