Hive 表存取 json 数据

2019独角兽企业重金招聘Python工程师标准>>>

1. 建表

create table json_temp(  
appkey string,   
jsondata string  
)   
row format delimited   
fields terminated by '|'   
stored as textfile ; 

2. 导入数据

appkey001|{"count":2,"usage":91273,"pkg":"com.example.gotest"} 
appkey001|{"count":234,"usage":9876,"pkg":"com.example.gotest"}
appkey001|{"count":34,"usage":5432,"pkg":"com.example.msg"}

load data local inpath '/home/bigdata/my/test-json-data.log' overwrite into table json_temp;

3. 查询数据

-- 使用 get_json_object 函数
select t.appkey, get_json_object(t.jsondata,'$.count'), get_json_object(t.jsondata,'$.usage') from json_temp t;

-- 使用 lateral view + json_tuple 函数
select t1.appkey, t2.* from json_temp t1 lateral view json_tuple(t1.jsondata,'count','usage') t2 as t2_count, t2_usage; 

查出的来结果如下 

appkey001    2    91273
appkey001    234    9876
appkey001    34    5432

转载于:https://my.oschina.net/zdtdtel/blog/1616120