Apache Hive integrates with HBase through the HBaseStorageHandler class, enabling SQL-based querying of HBase tables while maintaining real-time access capabilities. This implementation relies on the hive-hbase-handler JAR to bridge both systems, allowing batch ETL operations via HiveQL against HBase storage.

Environment Prerequisites

Infrastructure Specifications

• Platform: CentOS 7.x or compatible Linux distribution
• Architecture: Single-node or pseudo-distributed setup
• Resources: 2+ CPU cores, 4GB+ RAM recommended

Software Stack

• JDK 1.8 or later
• Apache Hadoop 2.8.x or 3.x
• Apache Hive 2.1.x or 3.x
• Apache HBase 1.2.x or 2.x

Network Configuration Configure hostname resolution and disable firewalls for inter-service communication:

# Set hostname
hostnamectl set-hostname analytics-node

# Update hosts file
echo "192.168.1.100 analytics-node" >> /etc/hosts

# Disable firewall (CentOS 7+)
systemctl stop firewalld
systemctl disable firewalld

System Environment Configure /etc/profile with installation paths:

export JAVA_HOME=/usr/lib/jvm/java-8-openjdk
export HADOOP_HOME=/usr/local/hadoop
export HIVE_HOME=/usr/local/hive
export HBASE_HOME=/usr/local/hbase
export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin:$HBASE_HOME/bin

Hadoop Core Configuration

core-site.xml

<configuration>
  <property>
    <name>fs.defaultFS</name>
    <value>hdfs://analytics-node:9000</value>
  </property>
  <property>
    <name>hadoop.tmp.dir</name>
    <value>/data/hadoop/tmp</value>
  </property>
</configuration>

hdfs-site.xml

<configuration>
  <property>
    <name>dfs.replication</name>
    <value>1</value>
  </property>
  <property>
    <name>dfs.namenode.name.dir</name>
    <value>file:///data/hadoop/namenode</value>
  </property>
  <property>
    <name>dfs.datanode.data.dir</name>
    <value>file:///data/hadoop/datanode</value>
  </property>
  <property>
    <name>dfs.permissions.enabled</name>
    <value>false</value>
  </property>
</configuration>

mapred-site.xml

<configuration>
  <property>
    <name>mapreduce.framework.name</name>
    <value>yarn</value>
  </property>
  <property>
    <name>mapreduce.jobhistory.address</name>
    <value>analytics-node:10020</value>
  </property>
</configuration>

yarn-site.xml

<configuration>
  <property>
    <name>yarn.resourcemanager.hostname</name>
    <value>analytics-node</value>
  </property>
  <property>
    <name>yarn.nodemanager.aux-services</name>
    <value>mapreduce_shuffle</value>
  </property>
</configuration>

Initialize and start services:

hdfs namenode -format
start-dfs.sh
start-yarn.sh

Hive Metastore Configuraton

hive-site.xml

<configuration>
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://analytics-node:3306/hive_metastore?createDatabaseIfNotExist=true</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.cj.jdbc.Driver</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hiveuser</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>hivepassword</value>
  </property>
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
  </property>
  <property>
    <name>hive.exec.scratchdir</name>
    <value>/tmp/hive</value>
  </property>
</configuration>

hive-env.sh

export HADOOP_HOME=/usr/local/hadoop
export HIVE_CONF_DIR=/usr/local/hive/conf
export HIVE_AUX_JARS_PATH=/usr/local/hive/lib

Download MySQL connector JAR to $HIVE_HOME/lib/.

HBase Distributed Setup

hbase-env.sh

export JAVA_HOME=/usr/lib/jvm/java-8-openjdk
export HBASE_MANAGES_ZK=false
export HBASE_PID_DIR=/var/hbase/pids
export HBASE_CLASSPATH=/usr/local/hadoop/etc/hadoop

hbase-site.xml

<configuration>
  <property>
    <name>hbase.rootdir</name>
    <value>hdfs://analytics-node:9000/hbase</value>
  </property>
  <property>
    <name>hbase.cluster.distributed</name>
    <value>true</value>
  </property>
  <property>
    <name>hbase.zookeeper.quorum</name>
    <value>analytics-node</value>
  </property>
  <property>
    <name>hbase.zookeeper.property.clientPort</name>
    <value>2181</value>
  </property>
  <property>
    <name>zookeeper.session.timeout</name>
    <value>120000</value>
  </property>
</configuration>

Start HBase:

start-hbase.sh

Integration Implementation

The integration requires copying Hive's HBase handler to HBase's library path:

cp $HIVE_HOME/lib/hive-hbase-handler-*.jar $HBASE_HOME/lib/

If version conflicts occur between Hive and HBase dependencies, prioritize HBase's JAR files by replacing conflicting libraries in Hive's lib directory with those from HBase.

Practical Usage Examples

Internal Table Mapping

Create a managed table in Hive that maps directly to an HBase table:

CREATE TABLE user_metrics (
    user_id INT,
    username STRING,
    score DOUBLE
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
    "hbase.columns.mapping" = ":key,profile:name,metrics:value"
)
TBLPROPERTIES (
    "hbase.table.name" = "user_data"
);

Parameters explained:

• user_metrics: Hive table identifier
• user_data: Physical HBase table name
• :key: Maps to HBase rowkey
• profile:name: Column family profile, column name
• metrics:value: Column family metrics, column value

Insert data via Hive:

INSERT INTO TABLE user_metrics VALUES (1001, 'alice', 95.5);
INSERT INTO TABLE user_metrics VALUES (1002, 'bob', 87.0);

Query from HBase shell:

scan 'user_data'

External Table Associasion

To existing HBase tables, use external tables to prevent accidental deletion:

# Create HBase table first
create 'customer_records', 'demographics', 'preferences'

# Insert sample data
put 'customer_records', 'C001', 'demographics:age', '34'
put 'customer_records', 'C001', 'demographics:city', 'Seattle'
put 'customer_records', 'C001', 'preferences:category', 'electronics'
put 'customer_records', 'C002', 'demographics:age', '28'
put 'customer_records', 'C002', 'preferences:category', 'books'

CREATE EXTERNAL TABLE customer_analytics (
    cust_id STRING,
    age INT,
    city STRING,
    interest STRING
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
    "hbase.columns.mapping" = ":key,demographics:age,demographics:city,preferences:category"
)
TBLPROPERTIES (
    "hbase.table.name" = "customer_records"
);

Query the external table:

SELECT cust_id, age, city, interest 
FROM customer_analytics 
WHERE age > 30;

Cross-System Joins

Hive can perform SQL joins between HBase-backed tables and native Hive tables:

-- Assuming sales_data is a native Hive ORC table
SELECT c.cust_id, c.city, s.total_amount
FROM customer_analytics c
JOIN sales_data s ON c.cust_id = s.customer_ref
WHERE c.interest = 'electronics';

Data Ingestion Patterns

Bulk load data from files into HBase via Hive:

-- Load CSV data into Hive staging table
CREATE TABLE staging_users (id INT, name STRING, points INT)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',';

LOAD DATA INPATH '/raw_data/users.csv' INTO TABLE staging_users;

-- Insert into HBase-backed table
INSERT OVERWRITE TABLE user_metrics
SELECT id, name, points FROM staging_users;

Verification and Troubleshooting

Verify integration status:

# Check HBase table creation from Hive shell
hbase shell> list
hbase shell> describe 'user_data'

# Verify Hive metadata
hive> SHOW TABLES;
hive> DESCRIBE FORMATTED user_metrics;

Common issues:

• ClassNotFoundException: Ensure hive-hbase-handler JAR is in both Hive and HBase lib directories
• Zookeeper connection failures: Verify hbase.zookeeper.quorum matches your Zookeeper ensemble
• Permission denied: Check HDFS permissions on /user/hive/warehouse and /hbase directories