hive 函数

1、函数使用命令

1.1、加载当前hive回话中所有函数（包括内置函数与自定义函数）命令：

show functions;

hive> show functions;
OK
!
!=
%
&
*
+
-
/
<
<=
<=>
<>
=
==
>
>=
^
abs
acos
add_months
and
array
array_contains
...

1.2、使用 DESCRIBE FUNCTION命令可以展示对应函数的简短介绍：

hive> DESCRIBE FUNCTION concat;
OK
concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN
Time taken: 0.029 seconds, Fetched: 1 row(s)

1.3、查看函数更详细的使用文档，命令中增加EXTENDED关键字进行查看：

hive> DESCRIBE FUNCTION EXTENDED concat;
OK
concat(str1, str2, ... strN) - returns the concatenation of str1, str2, ... strN or concat(bin1, bin2, ... binN) - returns the concatenation of bytes in binary data  bin1, bin2, ... binN
Returns NULL if any argument is NULL.
Example:
  > SELECT concat('abc', 'def') FROM src LIMIT 1;
  'abcdef'
Time taken: 0.027 seconds, Fetched: 5 row(s)

2、常用查询函数

2.1、行转列

1．相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

2．数据准备

表6-6 数据准备

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A

3．需求

把星座和血型一样的人归类到一起。结果如下：

射手座,A    大海|凤姐
白羊座,A    孙悟空|猪八戒
白羊座,B    宋宋

4．创建本地constellation.txt，导入数据

[atguigu@hadoop102 datas]$ vi constellation.txt

孙悟空,白羊座,A
大海,射手座,A
宋宋,白羊座,B
猪八戒,白羊座,A
凤姐,射手座,A

5．创建hive表并导入数据

CREATE TABLE person_info (
	NAME string,
	constellation string,
	blood_type string
) ROW format delimited FIELDS TERMINATED BY ",";

LOAD DATA inpath "/hadoop/constellation.txt" INTO TABLE person_info;

6．按需求查询数据

SELECT
	t1.base,
	concat_ws('|', collect_set(t1. NAME)) NAME
FROM
	(
		SELECT
			NAME,
			concat(
				constellation,
				",",
				blood_type
			) base
		FROM
			person_info
	) t1
GROUP BY
	t1.base;

结果：

射手座,A	大海|凤姐
白羊座,A	孙悟空|猪八戒
白羊座,B	宋宋

2.2、列转行

1．函数说明

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2.3、日期处理函数

2.3.1、date_format函数（根据格式整理日期）

hive> select date_format('2020-04-21','yyyy-MM');
OK
2020-04

2.3.2、date_add函数（加减日期）

hive> select date_add('2020-04-21',-1);
OK
2020-04-20
Time taken: 0.088 seconds, Fetched: 1 row(s)
hive> select date_add('2020-04-21',1);
OK
2020-04-22
Time taken: 0.062 seconds, Fetched: 1 row(s)

2.3.3、next_day函数

（1）取当前天的下一个周一

hive> select next_day('2020-04-22','MO');
OK
2020-04-27
Time taken: 0.084 seconds, Fetched: 1 row(s)

说明：星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）

（2）取当前周的周一

hive> select date_add(next_day('2020-04-22','MO'),-7);
OK
2020-04-20
Time taken: 0.086 seconds, Fetched: 1 row(s)

2.3.4、last_day函数（求当月最后一天日期）

hive> select last_day('2020-04-22');
OK
2020-04-30
Time taken: 0.08 seconds, Fetched: 1 row(s)