Hadoop Hive Hbase How to insert data into Hbase using Hive JSON file

Question

I usually insert data into the hbase table like this:

hive> insert overwrite table hbase_table_emp select * from testemp;

Can I do the same for a JSON file :

 {"id": 123, "name": "Ram", "role":"TeamLead"}
 {"id": 456, "name": "Silva", "role":"Member"}
 {"id": 789, "name": "Krishna", "role":"Member"}

and do :

hive> load data local inpath '/home/user/sample.json' into table testemp;

Answer 1

You can use the get_json_object function to parse the data as a JSON object. For instance, if you create a staging table with your JSON data:

DROP TABLE IF EXISTS staging;
CREATE TABLE staging (json STRING);
LOAD DATA LOCAL INPATH '/local/path/to/jsonfile' INTO TABLE staging;

Then use get_json_object to extract the attributes you want to load into the table:

INSERT OVERWRITE TABLE hbase_table_emp SELECT
  get_json_object(json, "$.id") AS id,
  get_json_object(json, "$.name") AS name,
  get_json_object(json, "$.role") AS role
FROM staging;