creating hive table from hbase

[#1]

Hi,

I'm getting following error when I run the below command for creating hive table.

sample is my hive table I'm trying to create. hloan is my existing hbase table. Please help.

create external table sample(id int, name string)STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,hl:id,hl:name") TBLPROPERTIES ("hbase.table.name"="hloan","hbase.mapred.output.outputtable"="sample");

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hadoop.hive.hbase.HBaseSerDe: columns has 2 elements while hbase.columns.mapping has 3 elements (counting the key if implicit))

[#2 · Accepted solution]

As error describes my create external table statement having 2 columns id,name.

In Hbase mapping I'm having 3 columns :key,hl:id,hl:name

Create table with 3 columns:

hive> create external table sample(key int, id int, name string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,hl:id,hl:name") 
TBLPROPERTIES ("hbase.table.name"="hloan","hbase.mapred.output.outputtable"="hloan");

(or)

if key and id columns having same data then I can skip hl:id in mapping.

Create table with 2 columns:

hive> create external table sample(id int, name string)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES("hbase.columns.mapping"=":key,hl:name") 
TBLPROPERTIES ("hbase.table.name"="hloan","hbase.mapred.output.outputtable"="hloan");