Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Querying Json Array Data From Hbase Using Hive.

Highlighted

Querying Json Array Data From Hbase Using Hive.

New Contributor

Hi I have some data in hbase which is in form of json,i have created Hive table on top of hbase table and trying to query some specific data,but getting null values,please find the code and expected output.

Any help is appreciated.

create 'comp_data','seg','rem'
alter 'comp_data', NAME=>'seg', VERSIONS=>500



put 'comp_data','1467','seg:seg','{"id":"1467","time":"2018-05-29 12:04:00","travs":[{"fname":"jai","freqs":[{"frefy":{"frefyno":"456","freqfysup":"AC"}},{"frefy":{"frefyno":"123","freqfysup":"LH"}},{"frefy":{"frefyno":"112222","freqfysup":"QF"}}],"lname":"gadd","middlename":"redd","sea":[{"sepref":{"ltext":"jhsh","prefcod":"jhg","prefloctxt":"food"}}]}]}'




Create external table srgmsbi1417.comp_data (
key STRING,    
rem string,
seg string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES 
("hbase.columns.mapping" = ":key, rem:rem,seg:seg") TBLPROPERTIES("hbase.table.name" = "comp_data") ;

my query::
SELECT key,tra
  from
(
  SELECT key,CONCAT_WS(' ',COLLECT_LIST(seg)) AS singlelineJSON
FROM (
SELECT key, a.seg FROM srgmsbi1417.comp_data  a 
) x
GROUP BY key
)y
LATERAL VIEW JSON_TUPLE(y.singlelineJSON,'travs') q1 AS  tra


expected output::::
key    fname lname  bookdate
1497   jai   gadd   2018-05-29 12:04:00