mapping JSON object stored in HBase to struct<Array<..>> Hive external table

Question

I have a hbase table that contains a column in JSON format.So, I want create a hive external table that contains a struct> type.

Hbase table named smms:

colum name : nodeid , value : "4545781751" in STRING FORMAT
column name : events in JSON FORMAT 
value :  [{"id":12542, "status" :"true", ..},{"id":"1477", "status":"false", ..}]

Hive external table :

Create external table msg (
key INT    
nodeid STRING,
events STRUCT<ARRAY<id:INT, status: STRING>
}
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
 WITH SERDEPROPERTIES ("hbase.columns.mapping" =  ":key,data:nodeid, data:events") TBLPROPERTIES ("hbase.table.name" = "smms");

the hive query : select * from msg; return a following result :

nodeid : 4545781751 
events : NULL

Thanks

Harold Harold · Accepted Answer · 2018-03-06T12:19:35

The HBaseStorageHandler (de)serialiser only supports String and binary fields https://cwiki.apache.org/confluence/display/Hive/HBaseIntegratio

What you store in HBase is actually a string (which indeed contains a JSON) but you can't map it to a complex Hive type. The solution would be to define events as a string, and to export the data to another HIVE table using HIVE JSON deserialiser https://github.com/rcongiu/Hive-JSON-Serde

mapping JSON object stored in HBase to struct<Array<..>> Hive external table

1 Answers