Requirement

Suppose we have data in Hive table. We want the same data into HBase table. So, our requirement is to migrate the data from Hive to HBase table.

Components Involved

• Hive – Source table
• HBase – Target Table

Solution

We cannot load data directly into HBase table from the hive. In order to achieve the requirement, we have to go through the following steps:

Step 1: Create Hive table

If you already have hive table with data then jump to step 3.

We are creating this hive table as a source. This table data, we want in HBase table.

CREATE TABLE hive_table(
  empno int,
  ename string,
  designation string,
  manager int,
  sal int,
  deptno int)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY '\t'
  LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

Step 2: Load data into Hive

Loading the data from the local path. In my case, the local path is /root/bdp/hbase/data/emp_data.csv.

LOAD DATA LOCAL INPATH '/root/bdp/hbase/data/emp_data.csv' INTO TABLE hive_table;

Step 3: Create HBase-Hive Mapping table

In this step, we are creating another hive table which actually points to an HBase table.

CREATE TABLE hbase_table_employee 
  ( 
     empno       INT, 
     ename       STRING, 
     designation STRING, 
     manager     INT, 
     hire_date   STRING, 
     sal         INT, 
     deptno      INT 
  )
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf:ename,cf:designation,cf:manager,cf:hire_date,cf:sal,cf:deptno")
TBLPROPERTIES ("hbase.table.name" = "employee_hbase");

Here, we are specifying HBaseStorageHandler in Stored By option. Also, mapping the hive column with HBase column family using SERDEPROPERTIES. It will create an HBase table named employee_hbase which will point to this hive table.

Note: Key represents the first column of the hive table. So Id will become the key.

In case, you are already having an HBase table and want to load data into existing HBase table, then you have to use EXTERNAL in your above hive DDL. Otherwise, you will get an error:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:MetaException(message:Table employee_hbase_2 already exists within HBase; use CREATE EXTERNAL TABLE instead to register it in Hive.)

CREATE EXTERNAL TABLE hbase_table_employee_2
  ( 
     empno       INT, 
     ename       STRING, 
     designation STRING, 
     manager     INT, 
     hire_date   STRING, 
     sal         INT, 
     deptno      INT 
  )
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf:ename,cf:designation,cf:manager,cf:hire_date,cf:sal,cf:deptno")
TBLPROPERTIES ("hbase.table.name" = "employee_hbase_2");

Step 4: Load data into HBase from Hive

In this step, we are going to migrate hive table data to HBase. That means we will load the hive (created in step 1) data to hive table created in step 3.

INSERT INTO TABLE hbase_table_employee SELECT * FROM hive_table;

We have loaded data into hbase_table_employee table which is pointing to HBase table employee_hbase.

Step 5: Scan HBase Table

Let’s check the data in HBase table:

Wrapping Up

In this post, we have created a hive to hbase mapping table in order to migrate data from hive to hbase. There is an HBase table on top of our Hive table. If your hive table contains a record which has NULL values for all the columns, in that case, hive and hbase records count would differ. The reason is, HBase table will ignore that record.