网站开发公司如何拓展业务,网站插件模块原理,游戏小程序开发需要多少资本,免费代理免费拿货HIVE作为数据仓库处理常用工具#xff0c;如同RDBMS关系型数据库中标准SQL语法一样#xff0c;Hive SQL也内置了不少系统函数#xff0c;满足于用户在不同场景下的数据分析需求#xff0c;以提高开发SQL数据分析的效率。 我们可以使用show functions查看当下版本支持的函数…HIVE作为数据仓库处理常用工具如同RDBMS关系型数据库中标准SQL语法一样Hive SQL也内置了不少系统函数满足于用户在不同场景下的数据分析需求以提高开发SQL数据分析的效率。 我们可以使用show functions查看当下版本支持的函数并且可以通过describe function extended funcname来查看函数对应的使用方式和方法下面我们将描述HIVE SQL中常用函数的高阶使用场景。
1、行转列(explode)
如下活动列表tb_activities
活动ID活动名称列表1双111,国庆,元旦2黄金周,国庆,元旦
希望转换为列类型活动表tb_activitity
活动ID活动名称1双111国庆1元旦2黄金周2国庆2元旦
使用到Hive内置一个非常著名的UDTF函数名字叫做explode函数中文戏称为“爆炸函数”可以炸开数据转换为多行。
insert into table tb_activitity select id,activity from tb_activities
lateral view explode(split(activities,,))enum_tmp as activity;2、列转行
如上1所示希望从tb_activity转换为tb_activities通过collect_set()方法和group by id 将列转换为行实现如下 select id, concat_ws(,,collect_set(activity)) as activities from tb_activity group by id;3、排名(rank())
可以通过rank() 方法的使用实现对指定列进行排名输出排名结果。例如商品总数表t_item_sum需要实现排名功能
item_iditem_sum100120100212100362100415
期望得到
item_iditem_sumrank1003621100120210041531002124
代码实现如下
select item_id,item_sum,rank()over(order by item_sum desc) as rank from t_item_sum;4、分组去重
在查询数据时如果有重复我们可以使用用distinct 去除重复值但使用 distinct 只能去除所有查询列都相同的记录如果某个字段不同distinct 就无法去重。这时我们可以用 row_number()over(partitioon by column1 order by column2) 先进行分组。 例如有活动表数据列“活动id用户id活动名称客户群组过期时间”希望按照”活动id活动名称客户群组”去重取最新一条数据。
iduser_idactivitycust_groupexpired_atBCP0151001春节活动A高价值2023-10-05BCP0151001春节活动A高价值2023-10-15BCP0151001春节活动A高价值2023-10-28BCP0251002春节活动B中价值2023-10-05BCP0251002春节活动B中价值2023-10-25BCP0301003春节活动C中价值2023-10-25
期望得到
iduser_idactivitycust_groupexpired_atBCP0151001春节活动A高价值2023-10-28BCP0251002春节活动B中价值2023-10-25BCP0301003春节活动C中价值2023-10-25
使用row_number()over(partitioon by) 分组去重。 select tt1.* from(select id, user_id, activity,cust_group,row_number() over(partition by concat(id,activity,cust_group)order by expired_at desc)as row_num from tb_acitivity_full)tt1 where tt1.row_num1;5、指标统计
GROUPING SETS,GROUPING__ID,CUBE,ROLLUP这几个hive分析函数通常用于OLAP中不能累加而且需要根据不同维度上钻 roll up 和下钻 drill down 的指标统计比如分小时、天、月的UV数。上钻是沿着维度的层次向上聚集汇总数据下钻是在分析时加深维度对数据进行层层深入的查看。通过逐层下钻数据更加一目了然更能充分挖掘数据背后的价值及时做出更加正确的决策。
OLAP函数使用说明GROUPING SETS根据不同的维度组合进行聚合等价于将不同维度的GROUP BY结果集进行UNION ALLGROUPING__ID表示结果属于哪一个分组集合属于虚字段CUBE可根据GROUP BY的维度的所有组合进行聚合ROLLUP作为CUBE的子集以最左侧的维度为主从该维度进行层级聚合
如4所示希望对指标值进行统计期望结果
indicatorenum_valuecountidBCP0153idBCP0252idBCP0301activity春节活动A3activity春节活动B2activity春节活动C1cust_group高价值3cust_group中价值3
通过grouping__id 内层SQL处理结果,表2
groupIdidactivitycust_groupuv1728id_BCP015NULLNULL31724id_BCP025NULLNULL21723id_BCP030NULLNULL12728NULLactivity_春节活动ANULL 32724NULLactivity_春节活动BNULL 22723NULLactivity_春节活动CNULL 13723NULLNULLcust_group_高价值33724NULLNULLcust_group_中价值3
select split(coalesce(id,activity,cust_group),\\_)[0] as indicator,coalesce(split(id, \\_)[1],split(activity, \\_)[1],split(cust_group, \\_)[1],) as enum_valuesum(uv) as countfrom (-- 内层SQL处理结果对应上表2select grouping__id as groupId,concat(id|,id) as id,concat(activity|, activity) as activity,concat(cust_group|,cust_group) as cust_group,count(*) as uvfrom tb_acitivity_fullgroup by concat(id|,id),concat(activity|, activity),concat(cust_group|,cust_group)grouping sets(concat(id|,id),concat(activity|, activity),concat(cust_group|,cust_group))as tt1group by split(coalesce(id,activity,cust_group),\\_)[0],coalesce(split(id, \\_)[1],split(activity, \\_)[1],split(cust_group, \\_)[1],);6、JSON数据处理
JSON数据作为数据存储和数据处理中最常见的结构化数据格式之一许多场景下都会将数据以JSON格式存储在文件系统(HDFS/MINIO等)中当构建数据仓库时对JSON格式的数据进行处理和分析就需要在Hive中使用对应函数对JSON格式的数据进行解析读取。 例如JSON格式的数据如下
商品ID商品名称额外信息1001IP15“fixedIntegral”:200, “source”:“wechat”,“stages”:12}
获取商品可使用的固定积分
select get_json_object(extra_json, $fixedIntegral) as integral
from t_items;7、替换
7.1 translate 函数用法
select translate(abcdef, adc, 19) tb_translate_exe
输出
1b9eftranslate(input,from,to)input输入字符串from需要匹配的字符to 用哪些字符来替换被匹配到的字符 注意点这里from的字符与to字符在位置上存在一 一对应关系也就是from中每个位置上的字符用to中对应位置的字符替换。
7.1 regexp_replace 函数
正则替换
SELECT aa,REGEXP_REPLACE(aa, [a-z], ) -- 替换所有字母,REGEXP_REPLACE(aa, [abc], ) -- 替换指定字母,REGEXP_REPLACE(aa, [^abc], ) -- 替换所有非字母,REGEXP_REPLACE(aa, [0-9], ) -- 替换所有数字,REGEXP_REPLACE(aa, [\\s\\S], ) -- 替换空白符、换行\\s是匹配所有空白符包括换行\\S非空白符不包括换行。,REGEXP_REPLACE(aa, \\w, ) -- 替换所有字母、数字、下划线。等价于 [A-Za-z0-9_],REGEXP_REPLACE(aa, [-8], ) -- 只替换-8这个字符,REGEXP_REPLACE(aa, [-8*], ) -- 替换-8、-、8这几个字符
FROM (SELECT 5e40b2b8-0916-42c0-899a-eaf4b2df 5268 AS aaUNION ALLSELECT c81b5906-38d7-482c-8b66-be5d3359cbf6 AS aaUNION ALLSELECT 8856fd0a-2337-4605-963f-0d0d059b1937 AS aa) t
;
