hive处理json数据_HIVE 处理json结构数据

测试

-- 建表

drop table if exists jsontest;

CREATE TABLE IF NOT EXISTS jsonTest

(teacher_name varchar(10),

major varchar(10),

students_info string comment "学生信息"

)

comment "学生课程信息"

row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'

LOCATION

'hdfs://nameservice1/user/hive/warehouse/bigdata.db/jsontest';

insert into jsontest values

('t1','语文','{"grade":1,"info":{"name":"xinzi","age":14,"sex":"M"}}|{"grade":3,"info":{"name":"lisi","age":14,"sex":"M"}}'),

('t2','maths','{"grade":2,"info":{"name":"zhangs","age":14,"sex":"F"}}')

;

-- 查看已有数据

select row_number() over() , a.* from jsontest a;

-- 创建视图

drop view if exists v_jsontest;

create view if not exists v_jsontest

as

select teacher_name,major,a.grade,b.name,b.age,b.gender from jsontest lateral view explode(split(students_info,'\\|')) stabcdefghi as stin

lateral view json_tuple(stin,'grade','info') a as grade,info

lateral view json_tuple(a.info,'name','age','sex') b as name,age,gender;