准备数据
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;