Hive优雅的处理Json数据

准备数据 

1SOH{"name":"aaa","cource":"english","score":"90"},{"name":"bbb","cource":"chinese","score":"80"},{"name":"ccc","cource":"math","score":"70"}
2SOH{"name":"aaa","cource":"english","score":"90"},{"name":"bbb","cource":"chinese","score":"80"},{"name":"ccc","cource":"math","score":"70"}
3SOH{"name":"aaa","cource":"english","score":"90"},{"name":"bbb","cource":"chinese","score":"80"},{"name":"ccc","cource":"math","score":"70"}

 

建表

drop table test_json;
create table test_json(
id string
,info string
)
COMMENT 'test'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'
                     LINES  TERMINATED BY '\n'
NULL DEFINED AS ''
STORED AS TEXTFILE;

 

数据入库

load data local inpath '/home/dev/temp/a.txt' overwrite into table test_json;

 

成功解析

select
 id
,get_json_object(json,'$.name')
,get_json_object(json,'$.cource')
,get_json_object(json,'$.score')
from test_json lateral view explode(split(regexp_replace(info,'\\}\\,\\{','\\}\\|\\{'),'\\|')) tmp as json;