How to create external hive table with complex data types which points to hbase table?

2
votes

I have a hbase table with Column families (Name, Contact) and columns, Name(String), Age(String), workStreet(String), workCity(String), workState(String).

I want to create an external hive table which points to this hbase table with following columns.
Name(String), Age(String), Address(Struct).

CREATE EXTERNAL TABLE hiveTable(id INT,name STRING, age STRING, 
address STRUCT<Street:STRING,City:STRING,State:STRING>) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ("hbase.columns.mapping" ="Name:name,Name:age,Contact:workStreet, Contact:workCity, Contact:workState") 
TBLPROPERTIES("hbase.table.name" = "hbaseTable");

It ran into the following error,

Error while processing statement: 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 3 elements while hbase.columns.mapping
has 5 elements (counting the key if implicit))
1
hadoophivehbasehadoop2
have you checked here ? – Ram Ghadiyaram
@RamPrasadG Thanks for your help. – sen

1 Answers

1
votes

I have tried using Map instead of Struct. Below is the query,

CREATE EXTERNAL TABLE hiveTable(id INT,name STRING,age STRING,
                           address MAP<String,String>) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "Name:name,Name:,Contact:") 
TBLPROPERTIES("hbase.table.name" = "hbaseTable");