Hive常用函数详解

一、Hive函数大全

在使用Hive进行数据处理时，我们会经常用到各种常用函数，如数学函数、字符串函数、条件函数等等。在这里，我们对Hive的函数进行简要介绍。

1.数学函数

ABS(x) 返回x的绝对值
CEIL(x) 返回不小于x的最小整数值
FLOOR(x) 返回不大于x的最大整数值
EXP(x) 返回x的指数值
LN(x) 返回以e为底，x的对数值
LOG10(x) 返回以10为底的x的对数值
PI() 返回一个常量pi
POWER(x,y) 返回x的y次方
RAND() 返回一个随机数
ROUND(x,n) 返回x保留到小数点后n位的近似值
SQRT(x) 返回x的平方根

2.字符串函数

CONCAT(str1, str2, ...) 返回连接后的字符串
SUBSTR(str, start, len) 返回从start位置开始，长度为len的子字符串
TRIM([BOTH | LEADING | TRAILING] trimstr FROM str) 从字符串str中去掉开始、结尾、或者前后各自的trimstr
LOWER(str) 将字符串转化为小写
UPPER(str) 将字符串转化为大写
REPLACE(str,search,replace) 返回字符串str中search被replace替换后的结果
REGEXP_EXTRACT(str, regexp) 用正则表达式regexp在str中进行匹配
INSTR(str, substr) 返回substr在str中第一次出现的下标
LENGTH(str) 返回字符串的长度

3.日期函数

CURRENT_DATE() 返回系统当前日期
CURRENT_TIMESTAMP() 返回系统当前时间戳
DATEDIFF(enddate,startdate) 返回enddate与startdate之间的天数差
FROM_UNIXTIME(unixtime) 将unix时间戳转化为日期格式

二、Hive常用的排名函数

Hive的排名函数主要涉及到三个函数：ROW_NUMBER(), RANK()和DENSE_RANK()。

1. ROW_NUMBER()

ROW_NUMBER()函数返回分组内结果集的行号，最好配合OVER()进行使用。

SELECT ROW_NUMBER() OVER()
FROM tablename
WHERE condition;

2. RANK()

RANK()函数会为每个分组返回一个排名值，如果有多个排名值相同，则它们的排名值也相同，下一位是排名值+1，排名值可能会有间隔。函数调用方法与ROW_NUMBER()相似。

3. DENSE_RANK()

DENSE_RANK()也会为每个分组返回一个排名值，但是排名值之间是连续的，没有间隔。与前两种不同的是，如果有多个排名值相同，它们的排名值也相同，但是下一位的排名值是仍然是排名值。

三、Hive数组函数

Hive中有很多数组函数可以对数组进行操作，如collect_set,collect_list等。

1. collect_set(元素)

collect_set()函数将列中的元素取出，存放到一个set集合中，由此去重并存储，返回一个set类型的结果。

SELECT collect_set(column) FROM tablename WHERE condition;

2. collect_list(元素)

collect_list()函数将列中的元素取出，存放到一个list集合中，由此可以保留相同元素，返回一个list类型的结果。

SELECT collect_list(column) FROM tablename WHERE condition;

四、Hive lag函数

Lag函数可以返回一个给定行之前的结果集。

LAG(column, n) OVER (ORDER BY column)

其中，LAG()函数接收两个参数：列和一个整数值。

另外需要注意的是，LAG()函数最好和ORDER BY一起使用。

五、Hive里的CEIL函数

CEIL()函数返回给定表达式的最小整数大于或等于结果。

SELECT CEIL(numerator / denominator) FROM tablename;

代码示例中，我们将numerator和denominator相除（前提是numerator和denominator都是整数），然后取最小的整数作为结果。

六、Hive求和函数

在Hive中，我们可以使用SUM函数来对某个指定的列进行求和。

SELECT SUM(column) FROM tablename WHERE condition;

七、Hive中常用的系统函数

在Hive中，常用的系统函数包含如下：

FILESYSTEM(), SYSTEM(), ENVIRONMENT(), RDD(), TABLE(), VALIDATE(), MICROSECOND(), SECOND(), MINUTE(), HOUR(), DAY(), MONTH(), YEAR()。

这些函数可以用于调试、设置参数等等。

八、Hive DECODE函数

DECODE()函数是一个非标准函数，用于比较两个参数，如果它们相等，返回第三个参数。

DECODE()函数参数如下：

DECODE(string1, value1, result1, value2, result2, value3, result3,...... valueN, resultN, default_result)

DECODE()函数根据一系列规则解析参数string1。如果某个值与string1匹配，则返回相应的结果result1。如果没有匹配任何值，则返回默认结果default_result。也可以省略该参数，则默认值为null。

九、Hive分组函数

Hive中常用与分组的函数有AVG, COUNT, MAX, MIN和SUM。这些函数的使用方法与前面介绍的函数大致相同。

1. AVG()

AVG()函数用于计算分组中列的平均值。

SELECT AVG(column) FROM tablename GROUP BY column;

2. COUNT()

COUNT()函数返回一个分组中的行数。

SELECT COUNT(column) FROM tablename GROUP BY column;

3. MAX()

MAX()函数用于返回分组中列的最大值。

SELECT MAX(column) FROM tablename GROUP BY column;

4. MIN()

MIN()函数用于返回分组中列的最小值。

SELECT MIN(column) FROM tablename GROUP BY column;

5. SUM()

SUM()函数用于返回一个分组中所有列的总和。

SELECT SUM(column) FROM tablename GROUP BY column;

十、Hive字符串替换函数

Hive中常用的字符串替换函数是replace()函数。

replace()函数语法如下：

replace(string initial_string, string pattern, string replace)

在initial_string字符串中，将pattern字符串替换为replace字符串。

代码示例如下：

SELECT replace(column, 'old_string', 'new_string') FROM tablename WHERE condition;

结论

以上就是对Hive常用函数的详细介绍，包括数学函数、字符串函数、日期函数、数组函数、排名函数、分组函数、替换函数等等。可以看出，在Hive的数据处理中，这些常用函数会让我们的开发变得更加便捷。