update query in hive/hbase

Question

I have already created a table in hbase using hive:

hive> CREATE TABLE hbase_table_emp(id int, name string, role string) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,cf1:name,cf1:role")
TBLPROPERTIES ("hbase.table.name" = "emp");

and created another table to load data on it :

hive> create table testemp(id int, name string, role string) row format delimited fields terminated by '\t';
hive> load data local inpath '/home/user/sample.txt' into table testemp;

and finally insert data into the hbase table:

hive> insert overwrite table hbase_table_emp select * from testemp;
hive> select * from hbase_table_emp;
OK
123 Ram     TeamLead
456 Silva   Member
789 Krishna Member
time taken: 0.160 seconds, Fetched: 3 row(s)

the table looks like this in hbase:

hbase(main):002:0> scan 'emp'
ROW                   COLUMN+CELL                                               
 123                  column=cf1:name, timestamp=1422540225254, value=Ram       
 123                  column=cf1:role, timestamp=1422540225254, value=TeamLead  
 456                  column=cf1:name, timestamp=1422540225254, value=Silva     
 456                  column=cf1:role, timestamp=1422540225254, value=Member    
 789                  column=cf1:name, timestamp=1422540225254, value=Krishna      
 789                  column=cf1:role, timestamp=1422540225254, value=Member    
3 row(s) in 2.1230 seconds

Now I'm trying to update a value in this table for exemple, I want to change the "role" of "Ram" from "Teamlead" to "Member", which query should I use?

Rubén Moraleda Rubén Moraleda · Accepted Answer · 2015-01-30T17:04:02

You can do it with HIVE since v0.14+:

INSERT INTO TABLE hbase_table_emp VALUES (123, null, "Member");

You've got to provide the key you want to update and null values on fields you don't want to update... yeah, it's odd, like having to compile and run a MapReduce job for a single update, but it's also odd to pretend HIVE+HBase to work like a regular RDBMS and provide full ACID support in the process :)

For updating data I'd stick to the APIs provided by HBase (Stargate, Thrift, Native or even the hbase shell) and use HIVE just for mass imports and data analysis.

update query in hive/hbase

2 Answers