/HBase-With-Hive

Learn How Hive Work With HBase in Simple Example

HBase With Hive

Learn How Hive Work With HBase in Simple Example

We will import DATA from 'Hiking' TABLE created on HBase.

# Hive startup commande
hive
# show TABLES in Hive 
SHOW TABLES;
# Hive shutdown
exit

Create HBase TABLE 'hiking' :

The syntax to Create TABLE :

CREATE EXTERNAL TABLE table_name(key String, col1 string, col2 int...)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,columnFamily:colx...") #columnFamily:col in HBase
TBLPROPERTIES("hbase.table.name"="table_name"); #TABLE in HBase

Example :

For each family column in HBase, We will create TABLE in Hive

# import and create external table InfoTechnique
CREATE EXTERNAL TABLE InfoTechnique(key int,distance float, Altitude int)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,infoTechnique:distance,infoTechnique:Altitude")
TBLPROPERTIES("hbase.table.name"="hiking");
# import and create external table InfoTechnique
CREATE EXTERNAL TABLE infoHiking(key INT,name STRING, region STRING, suiteHiking INT)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,infoHiking:name,infoHiking:region,infoHiking:suiteHiking")
TBLPROPERTIES ("hbase.table.name" = "hiking");

Queries :

get from TABLE hiking 4 :

SELECT info.key ,info.name ,info.region, tech.distance, tech.Altitude, info.suiteHiking
FROM InfoTechnique tech
FULL JOIN  InfoHiking info ON (tech.key = info.key)
WHERE info.key=4;

get InfoHiking of hiking 5 :

SELECT key, name, region, suiteHiking
FROM   InfoHiking info
WHERE key=5;

get the distance of the hiking 'Murdjadu Mountain' :

SELECT tech.distance
FROM InfoTechnique tech FULL 
JOIN  InfoHiking info ON (tech.key = info.key)
WHERE info.name='Montagne de Murdjadju';

get the distance of the hiking in the region of 'Tizi Ouzou' :

SELECT tech.distance FROM InfoTechnique tech
FULL JOIN  InfoHiking info ON (tech.key = info.key)
WHERE info.region='Tizi Ouzou';