Hbase hive integration

Problem statement : Integrate Hive and HBase in a way that if you load data in hive table that should reflect in your HBase table and vice versa.

Create a file in cloudera -
vi testdata.csv
1, 'hyd'25000
2, 'blr', 30000
3,'kol',50000

we want to store this csv file testdata.csv in our hive table as well as hbase table. STEP-1: Go to Hbase shell and create a Hbase table create 'hbase_demo', 'location', 'price' STEP-2: Go to hive shell and create a hive table create a temporary Hive stage table where we must load our csv data set

CREATE EXTERNAL TABLE IF not exists hive_stage( key string, city string, cost int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' ;

Then load csv data into hive_stage table

load data local inpath 'file:///testdata.csv' into table hive_stage;

After then we have to create a another hive table ,where we must map hive table's column to hbase table's column

CREATE EXTERNAL TABLE IF not exists hive_table
(key string,
city string,
cost int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES
("hbase.columns.mapping"=":key,location:city,price:cost")
TBLPROPERTIES("hbase.table.name"="hbase_demo")
;

STEP-3: Then load data into hive_table from hive_stage table

insert into table hive_table select * from hive_stage;

select * from hive_table;

STEP-4:Now go to hbase shell

After going to hbase shell type scan 'hbase_demo' then It will show same data which stored in hive table(hive_table).

scan 'hbase_demo'

Now add some record in hbase_demo table to check that - is it reflecting in habse_demo and hive_table tables.

put 'hbase_demo' , '4', 'location:city','mumbai'
put 'hbase_demo' , '4', 'price:cost','7800'

Now check your hbase table hbase_demo

scan 'hbase_demo'

STEP-5:Now go to hbase shell

check your hive table hive_table to verify data modification using hbase.

select * from hive_table;