hive sql

count(1)、count(*) 和count(字段)的区别

count(*)和count(1)功能和性能相似

  • 功能差异
    count(1)：与count(*)功能相同，统计所有行的数量，1是一个常量，不会影响统计结果。
    count(字段)：统计【指定字段非NULL】值的行数，忽略字段值为NULL的行

  • 性能差异
    count(字段)：由于需要检查字段是否为NULL，性能略低于count(1)

  • 适用场景
    count(1)：用于统计表的总行数，包含字段值为NULL
    count(字段)：用于统计特定字段非NULL值的行数

扩展关键字

  • RLike
    RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件

  • sort by
    本质上是在reduce端对【分区内的数据进行局部排序】，即每个分区内的数据是有序的，但由于存在多个分区，整体无序

  • distribute by
    配合sort by使用，我们知道sort by只能保证单个分区内的数据有序，而distribute by能指定数据应该路由到哪个分区，并且能实现路由字段的排序
    当我们结合【distribute by + sort by】时，即可实现组合条件【全局有序】
    先按照部门编号，再按照员工编号降序排序
    select * from emp distribute by deptno sort by empno desc;

当distribute by和sort by字段相同时，可以等价替换为cluster by
这种方式排序只能是升序排序，不能指定排序规则为ASC或者DESC
按照部门编号排序
select * from emp cluster by deptno;

JOIN




hive会根据表的大小自动判断是否使用map join

多表连接查询
SELECT
e.ename, d.dname, l.loc_name
FROM emp e
JOIN dept d ON d.deptno = e.deptno
JOIN location l ON d.loc = l.loc;
大多数情况下，Hive会对【每对JOIN连接对象启动一个MapReduce任务】
本例中会首先启动一个MapReduce job对表e和表d进行连接操作，
然后会再启动一个MapReduce job将第一个MapReduce job的输出和表l进行连接操作
优化：当对3个或者更多表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job

行列操作

  • 列拉宽
    使用join拉宽新的列字段

  • 行拉高
    使用union拉高新的行
    常用于多种类型只有类型枚举区别，计算逻辑一致的场景

  • UDAF（行转列）
    COLLECT_SET(col)：只接受基本数据类型，将某字段的值进行 “去重” 并汇总，
    产生array类型字段


    按星座进行聚合

  • UDTF（列转行）
    将hive一列中复杂的array或者map结构拆分成多行，类似于flatmap扁平化

explode
接受array或者map类型的值，将元素作为单独一行输出


1）hive中使用explode
需要注意的是select后面只能获得一个explode产生的视图（即只能查询explode产生的列），如果要查询源表的其他列，则需要使用Lateral View将多个视图合并
select userid, element
from origin_talble LATERAL VIEW explode(arr) udtfView AS element
这里通过LATERAL VIEW连接了origin_talble与udtfView，针对udtfView视图中的字段取名columnAlias作为最终结果表中的列名


2）spark中使用explode
select userid, element
from origin_talble explode(arr) AS element

复杂sql

  • 窗口函数
    相比于传统的group by，窗口函数的适用场景是怎样的呢？
    【将分组计算结果拉宽成组内单行数据的新字段】！
    group by后是对组内所有数据做aggregate聚合计算，得到一个结果，输出每个key一行记录
    partition by可以用于对每行记录进行拉宽字段，输出所有行记录，进一步拉宽字段是 这条记录关于所属组内所有数据 所计算出来的指标 ！！！
    由于一个聚合函数只能得出1个值域结果，比如需求求学生成绩最高的科目
    group by userid max(score) 只能计算出学生最高成绩，无法同时计算出对应的科目

=》涉及多步计算的场景应考虑拆分计算 生成中间分析表
这里第一步先算出学生的最高成绩，作为每行的拉宽字段，生成中间分析表
第二步 查询中间分析表 where每行的成绩 = 学生的最高成绩
或者
第一步将学生的对应的组数据按成绩排名，将排名作为每行的拉宽字段，生成中间分析表
第二步 查询中间分析表 where rank = 1

窗口函数的最终结果根据是否对组内数据进行了排序分两种情况
1）对组内数据进行了排序
对组内所有数据依次reduce【增量计算】，类比flink，先keyBy分组，再开窗reduce聚合计算
拿到的分析维度下的数据是截止到该行记录前的所有前置数据 做 聚合计算！！！
输出多行数据，每行带有各自的中间结果
2）没有对组内数据进行排序
对组内所有数据【全量计算】，并把最终聚合结果放到每一条数据记录中
输出多行数据，每行带有相同的最终聚合结果

由于窗口函数会输出多行结果，常用于拉宽表字段使用，比如计算访问次数需要标识每条页面日志所属的会话id，拉宽添加会话id字段



  • 窗口函数-排序函数
    为什么还有窗口函数进行排序的，因为前面的order by,sort by 等虽然可以排序但是不能给我们返回排序的值(名次)


需要注意的是排序函数【partition by指定的是分析维度】！
比如需要统计一级分类下的二级分类的销量进行排名，分析维度是一级分类而不是二级分类！
row_number() over (partition by category order by subcategory_qty desc) as sub_category_rank

应用场景1
【表去重取最新数据】，比如order表存在重复数据的场景，先以order_id为维度进行开窗，拿到每个order_id分组的数据，然后根据update_time进行计算排序，最终每条数据都带上了在各自order_id分组内的排名
接着使用查询条件where rank=1 即可实现按最新时间去重

应用场景2
通过【case when 将rank排名字段】【拉宽成另一个新字段】！
在拉宽所有记录的排名后，通过group by + max(case when)进行行转列，将【分组内多条不同排名名次】的数据转换为【1条数据带上了多个排名字段】

    SELECT 
        user_id as user_id,
        max(case when rank=1 then special else null) as special_first,
        max(case when rank=2 then special else null) as special_second,
        max(case when rank=3 then special else null) as special_third
    FROM(
        SELECT 
            msod.user_id,
            msood.special,
            COUNT(msod.order_id) AS total,
            row_number() over (partition by msod.user_id order by COUNT(msod.order_id) desc) as rank
        FROM 
            ${ydods}.s_order_data_delta msod
            LEFT JOIN ${ydods}.s_oos_order_data_delta msood ON msod.order_id = msood.order_id
        WHERE
            datediff(current_date,to_date(msod.order_time)) <= 365 
            and msod.is_deleted = 0
        GROUP BY msod.user_id,msood.special
        ) a11
    where rank <= 3
    group by user_id

多个字段排序，每个字段都要加上desc
row_number() over (partition by client_id order by is_primary desc, valid_date desc) as rank

  • 条件判断
    Hive 的条件判断可以用 case when 来实现


  • 连接字符串
    CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()，第一个参数指定连接字符串的分隔符

  • 字符串转数组
    split(str1, separator)：将一个字符串按指定分隔符转换为数组类型的值

  • 集合运算
    1）取并集
    SELECT Name FROM Person_1
    【UNION】
    SELECT Name FROM Person_2

2）取交集
SELECT Name FROM Person_1
【INTERSECT】
SELECT Name FROM Person_2
ORDER BY Name DESC　-- ORDER BY是对整个运算后的结果排序，并不是对单个数据集。

3）取差集
SELECT Name FROM Person_1
【EXCEPT】
SELECT Name FROM Person_2

  • 自定义函数
    用户自定义函数分为以下三种
    1）UDF（User-Defined-Function）
    map函数：一进一出
    2）UDAF（User-Defined Aggregation Function）
    聚集函数：多进一出
    比如count/max/min
    3）UDTF（User-Defined Table-Generating Functions）
    一进多出
    比如explode()

编程步骤：
1）继承Hive提供的类
引入依赖
<dependencies>

<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
</dependencies>
【继承Hive提供的类】
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.AbstractGenericUDAFResolver
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF

2）【实现类中的抽象方法】
initialize方法定义hive【函数输入，输出类型】



process方法定义hive【函数处理逻辑】


3）将程序【打成jar包，【上传到HDFS的/user/hive/jars路径下

4）【使用hive命令创建函数】
create [temporary] function [dbname.]function_name AS class_name;
create function explode_json_array as 'com.atguigu.hive.udtf.ExplodeJSONArray' using jar 'hdfs://hadoop102:8020/user/hive/jars/hivefunction-1.0-SNAPSHOT.jar';

5）【使用hive命令删除函数】
drop [temporary] function [if exists] [dbname.]function_name;

©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

相关阅读更多精彩内容

友情链接更多精彩内容