Hive SQL 查询函数手册

更新时间：2022-03-09 12:24:40 标签：hive sql 大数据 数据分析

提示

Hive SQL 教程编写中，使用过程中有任何建议，提供意见、建议、纠错、催更加微信 gairuo123。欢迎关注本教程作者新书《深入浅出Pandas：利用Python进行数据处理与分析》。作者开办 Python 数据分析培训，详情 Python 数据分析培训。

Hive 提供了较完整的 SQL 功能，HQL 与 SQL 基本上一致，旨在让会 SQL 而不懂 MapReduce 编程的用户可以调取 Hadoop 中的数据，进行数据处理和分析。

记录日常数据分析过程中 Hive SQL 需要的查询函数，方便手头随时查询，定期更新补充。

基本语法

SELECT t1.a as name_a, -- 说明a
      t2.b as name_b, -- 说明b
      max(t2.f) as name_f -- 说明f
FROM
  (SELECT <list1>
   FROM T) AS t1
<LEFT / RIGHT> JOIN
  (SELECT <list2>
   FROM C) AS c1 ON t1.id = c1.id AND t1.name = c1.name
WHERE X=a AND (Y > b or Z != c)
GROUP BY t1.m, t2.p
ORDER BY J, H DESC

执行顺序：

FROM —> WHERE —> GROUP BY—> 聚合函数 —> HAVING—> SELECT —> ORDER BY —> LIMIT

聚合计算

函数概览

函数语法	功能说明
`avg(x)`	平均数
`count([DISTINCT] col)`	个数，数量，记录数。count(distinct x) 为去重后数量
`sum(x)`	总数，求和
`max(x)`	最大值，日期时间字段时为最近的
`min(x)`	最小值，日期时间字段时为最早的
`collect_set(col)`	收集 group by 聚合字段，返回去重后数组（集合）
`collect_list(col)`	收集 group by 聚合字段，返回不去重的数组
`ntile(INTEGER x)`	用于将分组数据按照顺序切分成n片，返回当前切片值
`approx_percentile(array, decimal '0.50')`	中位数，未提供 median 函数

Collect_set 样例：

分组后聚合到一个字段：

SELECT UID,
       collect_set(order_id)
FROM dwd.order_detail
GROUP BY UID
-- 返回类似 111 | ["123","124"]，集合的内容去重

用指定字符分隔内容：

SELECT UID,
       concat_ws(',', collect_set(cast(order_id AS string)))
FROM dwd.order_detail
GROUP BY UID
-- 返回类似 111 | 12345,45678

注：
1. 聚合函数需要 group by 才可使用
2. collect_list 不去重，collect_set 去重

字符处理

函数概览

函数语法	功能说明
`cast(expr as <type>)`	转换表达式 expr 为 type 类型
`length(int/str)`	长度
`reverse(int/str)`	反转顺序
`concat(1,2,'a')`	字符串连接
`concat_ws('-','a','b')`	指定分隔符字符串连接
`substr('foobar', 4)`	字符串截取, 或 `substring`
`substring_index(str, '-', 2)`	分隔后取前几块
`instr('abc', 'ab')`	子串的位置，0为不存在
`locate('a', 'abc', 1)`	子串在第 n 个位置上是否存在
`upper, ucase`	转大写
`lower,lcase`	转小写
`trim ltrim rtrim`	去空格，左右空格
`format_number(NUMBER x, INT d)`	将数字转为千分位及小数
`corr(col1, col2)`
`corr(col1, col2)`

todo:
•正则表达式替换函数：regexp_replace
•正则表达式解析函数：regexp_extract
•URL解析函数：parse_url
•json解析函数：get_json_object
•空格字符串函数：space
•重复字符串函数：repeat
•首字符ascii函数：ascii
•左补足函数：lpad
•右补足函数：rpad
•分割字符串函数: split
•集合查找函数: find_in_set

样例：

常见的类型有：

BIGINT，DOUBLE，FLOAT，TIMESTAMP，DATE，INTERVAL，STRING，BOOLEAN
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

-- 返回：tom
select get_json_object('{"name":"tom"}', '$.name')

有效的转换结果：

SELECT cast(date as date) -- 返回 date 类型；
-- timestamp 中的年/月/日的值是依赖与当地的时区，结果返回 date 类型
SELECT cast(timestamp as date)
-- 如果 string 是 yyyy-MM-dd 格式的，则相应的年/月/日的 date 类型的数据将会返回；
-- 但如果 string 不是 yyyy-MM-dd 格式的，结果则会返回 NULL
SELECT cast(string as date)
-- 基于当地的时区，生成一个对应 date 的年/月/日的时间戳值；
SELECT cast(date as timestamp)
-- date所代表的年/月/日时间将会转换成 yyyy-MM-dd 的字符串。
SELECT cast(date as string)
-- 转化为 bigint
SELECT cast('1' as BIGINT)

字符的处理：

-- 文本连接：concat->demo
select CONCAT('concat','->','demo')
-- 文本截取：hive
select substr('This is hive demo',9,4)
-- 返回：202108 月份
select substr('202108', 0, 6)
-- 按字符拆分并取部分，返回：abc-def
SELECT substring_index('abc-def-ghi', '-', 2)
-- 文本长度：6
select length('hadoop')
-- 文本大写：HADOOP HADOOP
select upper('hadoop'), ucase('hadoop')
-- 文本小写：hadoop hadoop
select lower('HADOOP'), lcase('HADOOP')
-- HHhadoop, 保证长度，空为向左拼 H
select lpad('hadoop',8,'H')
-- 用指定字符合成指定长度字符：hadooppp
select rpad('hadoop',8,'p')
-- 字符重复：HadoopHadoop
select repeat('Hadoop',2)
-- 字符反转：poodaH
select reverse('Hadoop')
-- 按字符拆分成列表：["hadoop","supports","split","function"]
select split('hadoop~supports~split~function','~')
-- 转为千分位格式, 21,312,442.123
SELECT format_number(21312442.12345, 3)

正则表达式的使用：

-- HA^G^FER$JY 替换为 HA$G$FER$JY
select regexp_replace('HA^G^FER$JY',"\\^","\\$")
-- bar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2)

集合函数

函数概览

函数语法	功能说明
`array_contains(Array<T>, value)`	返回Array是否包含value
`size(Map<K.V>)`	返回Map的大小
`size(Array<T>)`	返回Array的大小
`map_keys(Map<K.V>)`	返回Map的key集合
`map_values(Map<K.V>)`	返回Map的value集合
`sort_array(Array<T>)`	返回Array是否包含value

样例：

-- TRUE col 列 array 中是否包含 hello
array_contains(col, 'hello')

统计运算

函数概览

函数语法	功能说明
`+， -， *， /`	加减乘除
`%`	取余数 10%3=1
`DIV`	取整数部分 17 DIV 3 = 5
`AND ，OR、NOT`	和、或、非
`[NOT] IN (val1, val2, ...)`	是否在列表中有
`[NOT] EXISTS (subquery)`	只否在列表中存在
`= ，<>`	等值、不等值比较
`> ，<`	大于、小于比较
`>= ，<=`	大于等于、小于等于比较
`IS [NOT] NULL`	空值、非空值判断
`LIKE、RLIKE`	LIKE、JAVA 的 LIKE 操作
`REGEXP`	正则表达式判断

样例：

-- 0 做除数（分母）时返回 NULL
select 1/0

Hive正则表达式: https://www.jianshu.com/p/e81ab9a6f481

窗口函数

函数概览

函数语法	功能说明
`row_number()`	顺序排序，值同序号不同，序号不重 1234
`dense_rank()`	相同值序号一样，不跳过下个序号 11223
`rank()`	相同值序号一样，跳过下个序号 11335
`cume_dist()`	同列占比，小于等于当前值的行数/分组内总行数
`lag(col,n=1,DEFAULT)`	统计窗口内往上第n行值
`lead(col,n=1,DEFAULT)`	统计窗口内往下第n行值
`first_value(col)`	分组内排序后，截止到当前行第一个值
`last_value(col)`	分组内排序后，截止到当前行最后一个值

样例：

-- 累积求和
sum(col) over(partition by grpcol order by ordcol asc/desc)
-- 滑动求和
sum(col) over(partition by grpc order by ordc range between ... and ...)
-- 按值大小增加序号
row_number() OVER (ORDER BY amount DESC) AS rn
-- 窗口计数
count() OVER (partition by grpcol order by ordcol) AS rn
-- 窗口去重计数，注：不支持 count(distinct col) over(partition by ..)
size(collect_set(col3) over(partition by col1 ORDER BY col2 asc))
-- 窗口去重计数，方法2
dense_rank() over(partition by [dt] order by [math])
 + dense_rank() over(partition by [dt] order by [math] desc)
 - 1

TODO

时间函数

函数概览

函数语法	功能说明
`unix_timestamp(时间int,格式)`	指定格式日期转UNIX时间戳
`from_unixtime()`	UNIX时间戳（秒）转日期
`unix_timestamp()`	当前UNIX时间戳
`to_date()`	日期时间转日期
`date_format(time, 格式)`	对时间日期进行格式化
`year month day`	日期转年、月、日
`hour minute second`	日期转时、分、秒
`weekofyear()`	日期转周
`datediff()`	日期比较，时间相差
`date_add()`	日期增加
`date_sub()`	日期减少
`TRUNC（date[,fmt]）`	指定元素截去日期值
`last_day(STRING date)`	指定日期字符所在月份的最后一天

样例：

-- 当前时间 2019-10-01 00:54:14.736
select current_timestamp()
-- 当前 日期 2019-10-01
select current_date()
-- 当前日期加一天，2019-10-02
select date_add(current_date(), 1)
-- 当前时间减一天， 2019-09-30
select date_sub(current_date(),1)
-- 当前日期所在月份的第一天，2019-10-01
select trunc(current_timestamp(), 'MONTH')
-- UNIX 时间戳（秒）转指定格式， 20190001， 'yyyymmdd' 也可以
select from_unixtime(unix_timestamp(current_date()), 'yyyyMMdd')
-- 如果是毫秒要除以1000
select from_unixtime(cast(buy_time/1000 as BIGINT),'yyyyMMdd')
-- 带时分秒，buy_time
select from_unixtime(buy_time,'yyyy-MM-dd hh:mm:ss')

-- 根据时间格式转为UNIX时间戳
SELECT unix_timestamp(20190808,'yyyyMMdd')
-- 算出日期为周几，2000-01-03 为固定值，是个周一
SELECT datediff('2019-08-15', '2000-01-03')%7+1 AS week_day
-- 算出自然周数，2000-01-03 为固定值
-- 可用于按周分组等, weekofyear() 跨年会不足一周
SELECT floor(datediff('2019-08-08', '2000-01-03')/7) AS week_number
SELECT datediff('2019-08-30', '2000-01-03') DIV 7
-- 格式化数据
SELECT date_format(time, 'yyyy-MM-dd HH:mm:ss')
-- 只取日期 2021-09-01
SELECT to_date('2021-09-01 14:00:04')
-- 当月的最后一天，返回 2021-02-28
SELECT last_day('2021-02-22')
-- 返回当月第一天，如 2019-09-01
select trunc(sysdate, 'mm')
-- 当前月的季度数的算法，如 4 月为 2
select floor(substr('2019-09-01',6,2)/3.1)+1
-- 昨日的日期，形式如 20220304
select regexp_replace(date_sub(current_date(),1), '-', ''))

逻辑判断

函数概览

函数语法	功能说明
`if(条件,真时值,假时值)`	条件判断
`case when`	多条件分支
`COALESCE(a1,a2,...,an)`	返回第一个不为 Null 的值
`isnull(a)/isnotnull(a)`	判断是否为/不为 Null
`nvl(a, b)`	a 为 Null 时返回 b, 否则为 a
`nullif(a, b)`	a = b 时，返回null，否则为a

样例：

-- if 语句语法，请为 Null 的设置为 0
SELECT if(var IS NULL, 0 ,var) AS var_name
-- 满足一定条件的总数，Null count() 不计数
SELECT count(if(score>=80, score, NULL)) AS good
-- case when 枚举翻译
SELECT page_id AS page_id,
      CASE page_id
          WHEN 001 THEN '第一名'
          WHEN 002 THEN '第二名'
          ELSE '-'
      END AS name

混合函数

Reflect 调用 java 函数

-- reflect (也可 java_method) 支持调用 java 自带函数，计算一行最高成绩
select reflect("java.lang.Math","max", englist, chinese) from exam
-- 返回 1 true    3   2   3   2.718281828459045   1.0
SELECT reflect("java.lang.String", "valueOf", 1),
       reflect("java.lang.String", "isEmpty"),
       reflect("java.lang.Math", "max", 2, 3),
       reflect("java.lang.Math", "min", 2, 3),
       reflect("java.lang.Math", "round", 2.5),
       reflect("java.lang.Math", "exp", 1.0),
       reflect("java.lang.Math", "floor", 1.9)

虚表生成

stack 堆叠

-- stack(INT n, v1, v2, ..., vk)，n 为列数（与 as 数对应）
SELECT stack(2, 'b', 'y', 'b2') as (b, y)
-- 可简少写 as
SELECT stack(1, page_id, page_name) as (page_id, page_name) FROM tt

explode 行转列

-- explode(列)：将列中复杂的array或者map结构数据拆分成多行
SELECT t.info_id,t.d
FROM
  (SELECT explode(info_detail) AS (info_id, detail)
   FROM my_table) as t

inline 列转行

-- inline(Array<Struct [, Struct]> a) 分解struct数组到表中
select *
from table1 t
  lateral view inline(array_of_structs) a;

多维分析

GROUPING SETS

Hive SQL 的多维度分析可以在分组中上增强聚合功能，指定多个分组维度之后，可以在这些维度组合进行聚合，对应维度上不做分组时输出 NULL 代表当前维度的全体：

-- GROUPING SETS ( (a,b) ) 指定维度组合
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b))
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b),a)
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS (a,b)
SELECT a,b, SUM(c) FROM tab GROUP BY a,b GROUPING SETS ((a,b),a,b, ())

WITH CUBE

-- WITH CUBE 所有维度的组合
SELECT a,b,c,SUM(d) FROM tab GROUP BY a, b, c WITH CUBE
-- 相当于
GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),(c),())

WITH ROLLUP

-- WITH ROLLUP 以最左侧的维度为主进行层级聚合
SELECT a,b,c,SUM(d) GROUP BY a, b, c WITH ROLLUP
-- 相当于
GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ())

Grouping__ID

Grouping__ID 功能可以标示结果属于哪一个分组集合，对应如果参与聚合为 1，不参与为 0，列按顺序从右向左最终组合一个二进制数，以十进制显示出来：

-- 显示 GROUPING__ID 列，取值 a,b:11->3, a,null:01->1, null,null:00->0
SELECT a, b, count(*), GROUPING__ID
FROM tab
GROUP BY a, b WITH ROLLUP;

为列显示聚合时，其值为空。如果列本身有一些空值，这可能会发生冲突。需要有某种方法来标识列中的NULL（表示聚合），以及列中的NULL（表示值）。分组ID函数就是解决这个问题的方法。此函数返回与每列是否存在对应的位向量。对于每一列，如果结果集中的某一行已在该行中聚合，则会为该行生成一个值“1”，否则该值为“0”。这可用于区分数据中是否存在空值。

Grouping

Grouping 方法, 指示 GROUPBY 中是否针对给定行进行聚合。0 表示属于分组集的列，1 表示不属于分组集的列。简单说就是这些维度会出现在列上。

SELECT a, b, count(*), GROUPING__ID,  grouping(a, b), grouping(a)
FROM tab
GROUP BY a, b WITH CUBE;

本节参考：https://cwiki.apache.org/confluence/x/mxLMAQ

其他

内置命令

show functions   -- 查看所有函数
desc function count   -- 查看函数用法
desc function extended count  -- 详细用法，简单例子
 -- 查看在hdfs上的存储路径及建表语句
show create table dws.dws_ads_logs
DESCRIBE dws.dws_ads_logs -- 查看表结构
-- 查看分区信息（大小、创建时间、字段等）
describe formatted dws.dws_ads_logs partition(dt="20210801");
-- 开启并行模式(默认关闭)，设定并行度（默认是8）
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=14;

WITH AS

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。

-- 相当于建了 a、b 临时表
with
     a as (select * from scott.emp),
     b as (select * from scott.dept)

select * from a, b where a.deptno = b.deptno;

UNION

用于连接表：

UNION 操作符合并的结果集，不允许重复值；
UNION ALL 允许有重复值。

SQL 性能

可以查看文前的 SQL 执行顺序。

-- 比 ORDER BY sum(b) DESC 性能好
SELECT  a, sum(b) as total_b
FROM tab
GROUP BY a
ORDER BY total_b DESC limit 5;

使用总结

与 SQL 类似
代码不区分大小写
可以写自定义函数代码，可搜索 Hive UDF 了解
官方手册

<< 更多 Hive SQL 案例 Hive SQL 教程 SQL JOIN 逻辑 >>