HIVE数据查询

数据： 

request_id	app_key	method_name	api_name	request_time	request_param	union_id
0b115ef6_jyvs8p54_39039180	unknowledge	com.jd.union.open.gateway.service.api.goods.GoodsServiceImpl.queryJFGoods	jd.union.open.goods.jingfen.query	2019-08-04 01:00:03:789	{"eliteId":1,"pageIndex":4,"pageSize":50,"sort":"desc","sortName":"commissionShare"}	1102261988

数据查询 

1、instr

select * from union_open_gateway_xxx where api_name in ('jd.union.open.promotion.common.get','jd.union.open.promotion.byunionid.get','	
jd.union.open.promotion.bysubunionid.get') and dt='2019-07-28' and instr(request_param,'ad_od')>0 order by union_id;

2、get_json_object(get_json_object(string json_column, string attri))

select * from union_open_gateway_xxx where dt='2019-08-04' and api_name='jd.union.open.goods.jingfen.query' and get_json_object(request_param,'$.eliteId')=22

select get_json_object(request_param,'$.eliteId'),get_json_object(request_param,'$.pageIndex') total from union_open_gateway_xxx where api_name='jd.union.open.goods.jingfen.query' and dt='2019-08-05' limit 10;

select request_id from union_open_gateway_comm_log where dt='2020-01-07' 
and api_name='jd.union.open.position.create' 
and get_json_object(request_param,'$.siteId') is null) 

多层

select  get_json_object(data, '$.user.city.name') from test;

数组值[]

select  get_json_object(data, '$.store.fruit[0]') from test;

3、json_tuple(元组数据获取)

select json_tuple(request_param,'eliteId','pageIndex','pageSize') from union_open_gateway_xxx where api_name='jd.union.open.goods.jingfen.query' and dt='2019-08-05' limit 10;

4、if()

select if(tareget_col is null, 0, cslt.self_amount) as new_column from table_name;

复合查询-字段中包含某字符 

 select * from union_open_gateway_comm_log where dt='2020-07-12' 
and api_name='jd.union.open.goods.query' 
and get_json_object(request_param,'$.fields') is not null  
and array_contains(split(get_json_object(request_param,'$.fields'),','),'documentInfo')
limit 100