HIVE数据查询

最新推荐文章于 2024-11-07 19:50:24 发布

原创最新推荐文章于 2024-11-07 19:50:24 发布 · 518 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#HIVE

HIVE 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了一种针对京东联盟开放平台API数据的查询方法，包括使用SQL指令筛选特定API调用、解析JSON参数及利用各种函数进行数据提取。通过具体实例展示了如何高效地从海量数据中筛选出有价值的信息。

数据：

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