配置单元运算符和用户定义的函数(UDF)

在Beeline或CLI中，使用以下命令显示最新文档：

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;

Built-in Operators

Operators Precedences

Relational Operators

以下运算符比较传递的操作数，并根据操作数之间的比较是否成立来生成 TRUE 或 FALSE 值。

注意：NULL 为 UNKNOWN，因此(UNKNOWN 为 TRUE)和(UNKNOWN IS 为 FALSE)都将评估为 FALSE。
| A [NOT]如果 B 或 strings | NULL 如果 A 或 B 为 NULL，则为 TRUE，如果字符串 A 与 SQL 简单正则表达式 B 匹配，则为 FALSE。逐个字符进行比较。 B 中的字符匹配 A 中的任何字符(类似于 posix 正则表达式中的.)，而 B 中的％字符匹配 A 中任意数目的字符(类似于 posix 正则表达式中的.)。例如，“ foobar”之类的“ foobar”评估为 FALSE，而诸如“ foo”之类的“ foobar”评估为 TRUE，“ foobar”之类的评估为“ foo％”。
| A RLIKE B | strings | NULL，如果 A 或 B 为 NULL，则为 TRUE，如果 A 的任何子字符串(可能为空)与 Java 正则表达式 B 匹配，否则为 FALSE。例如，'foobar'RLIKE'foo'的计算结果为 TRUE，'foobar'RLIKE'^ f. r $'也是如此。
| A REGEXP B |字符串|与 RLIKE 相同。|

Arithmetic Operators

以下运算符支持对操作数的各种常见算术运算。所有返回 Numbers 类型；如果任何操作数为 NULL，则结果也为 NULL。

Logical Operators

以下运算符为创建逻辑表达式提供支持。它们都根据操作数的布尔值返回布尔 TRUE，FALSE 或 NULL。 NULL 表现为“未知”标志，因此，如果结果取决于未知状态，则结果本身是未知的。

String Operators

复杂类型的构造函数

以下函数构造复杂类型的实例。

复杂类型上的运算符

以下运算符提供了访问复杂类型中的元素的机制。

Built-in Functions

Mathematical Functions

Hive 支持以下内置 math 函数；当参数为 NULL 时，大多数返回 NULL：

小数数据类型的 math 函数和运算符

所有常规算术运算符(例如，-，*，/)和相关的 mathUDF(Floor，Ceil，Round 等)都已更新为处理十进制类型。有关受支持的 UDF 的列表，请参见Hive 数据类型中的Mathematical UDFs。

Collection Functions

Hive 支持以下内置收集功能：

类型转换功能

Hive 支持以下类型转换功能：

Date Functions

Hive 支持以下内置日期功能：

在 Hive 2.1.0(HIVE-13248)之前，返回类型为 String，因为创建方法时不存在 Date 类型。
| int | year(字符串日期)|返回日期或时间戳字符串的年份部分：year(“ 1970-01-01 00:00:00”)= 1970，year(“ 1970-01-01”)= 1970.|
| int | quarter(date/timestamp/string)|返回日期，时间戳或字符串在 1 到 4 范围内的一年的四分之一(从 Hive 1.3.0开始)。示例：quarter('2015-04-08')= 2 |
| int | month(string date)|返回日期或时间戳记字符串的月份部分：month(“ 1970-11-01 00:00:00”)= 11，month(“ 1970-11-01”)= 11 |
| int | day(string date)dayofmonth(date)|返回日期或时间戳记字符串的 day 部分：day(“ 1970-11-01 00:00:00”)= 1，day(“ 1970-11- 01“)= 1. |
| int | hour(字符串日期)|返回时间戳的小时：hour('2009-07-30 12:58:59')= 12，hour('12：58：59')= 12.
| int | minute(字符串日期)|返回时间戳记的分钟。
| int | second(字符串日期)|返回时间戳的秒。
| int | weekofyear(字符串日期)|返回时间戳字符串的星期数：weekofyear(“ 1970-11-01 00:00:00”)= 44，weekofyear(“ 1970-11-01”)= 44.
| int | extract(字段 FROM 源)|从源中检索字段，例如天或小时(自 Hive 2.2.0起)。源必须是日期，时间戳记，间隔或可以转换为日期或时间戳记的字符串。支持的字段包括：日，星期几，小时，分钟，月，季度，秒，周和年。

Examples:

选择摘录(“ 2016-10-20”中的月份)得出 10.

选择摘录(来自“ 2016-10-20 05:06:07”的小时)的结果为 5.

选择摘录(dayofweek，来自“ 2016-10-20 05:06:07”)，结果为 5.

选择摘录(从“ 1-3”年到月的月份中的月份)结果为 3.

选择摘录(从间隔'3 12:20:30 天到第二分钟的分钟数)得到 20.
| int | datediff(字符串结束日期，字符串开始日期)|返回从开始日期到结束日期的天数：datediff('2009-03-01'，'2009-02-27')= 2.
| * pre 2.1.0：字符串

2.1.0 on：* date | date_add(date/timestamp/string startdate，tinyint/smallint/int days)|添加了若干天作为开始日期：date_add('2008-12-31'，1)='2009- 01-01'。

在 Hive 2.1.0(HIVE-13248)之前，返回类型为 String，因为创建方法时不存在 Date 类型。
| * pre 2.1.0：字符串

2.1.0 on：* date | date_sub(日期/时间戳记/字符串开始日期，tinyint/smallint/int 天)|减去开始日期的天数：date_sub('2008-12-31'，1)='2008- 12-30'。

在 Hive 2.1.0(HIVE-13248)之前，返回类型为 String，因为创建方法时不存在 Date 类型。
| timestamp | from_utc_timestamp( { any primitive type } ts，字符串 timezone)|将 UTC 中的时间戳*转换为给定的时区(从 Hive 0.8.0开始)。

时间戳是一种原始类型，包括时间戳/日期，tinyint/smallint/int/bigint，float/double 和十进制。

小数部分被视为秒。整数值以毫秒为单位。例如，from_utc_timestamp(2592000.0，'PST')，from_utc_timestamp(2592000000，'PST')和 from_utc_timestamp(timestamp'1970-01-30 16:00:00'，'PST')都返回时间戳 1970-01-30 08：00：00. |
| timestamp | to_utc_timestamp( { any primitive type } ts，字符串时区)|将给定时区中的时间戳转换为 UTC(从 Hive 0.8.0开始)。

*时间戳是一种原始类型，包括时间戳/日期，tinyint/smallint/int/bigint，float/double 和十进制。

小数部分被视为秒。整数值以毫秒为单位。例如，to_utc_timestamp(2592000.0，'PST')，to_utc_timestamp(2592000000，'PST')和 to_utc_timestamp(timestamp'1970-01-30 16:00:00'，'PST')都返回时间戳 1970-01-31 00：00：00.|
| date | current_date |返回查询评估开始时的当前日期(从 Hive 1.2.0开始)。在同一查询中对 current_date 的所有调用均返回相同的值。
| timestamp | current_timestamp |返回查询评估开始时的当前时间戳(从 Hive 1.2.0开始)。在同一查询中对 current_timestamp 的所有调用均返回相同的值。
| string | add_months(字符串起始日期，int num_months，output_date_format)|返回起始日期之后 num_months 的日期(从 Hive 1.1.0开始)。 start_date 是字符串，日期或时间戳。 num_months 是一个整数。如果 start_date 是该月的最后一天，或者如果结果月份的天数少于 start_date 的天部分，则结果是结果月份的最后一天。否则，结果与 start_date 具有相同的天组成部分。默认输出格式为“ yyyy-MM-dd”。

在 Hive 4.0.0 之前，日期的时间部分将被忽略。

从 Hive 4.0.0开始，add_months 支持可选参数 output_date_format，该参数接受一个 String，该 String 表示输出的有效日期格式。这样可以在输出中保留时间格式。

例如 ：

add_months('2009-08-31'，1)返回'2009-09-30'。

| add_months('2017-12-31 14:15:16'，2，'YYYY-MM-dd HH：mm：ss')返回'2018-02-28 14:15:16'。
| string | last_day(字符串日期)|返回日期所属月份的最后一天(从 Hive 1.1.0开始)。 date 是格式为“ yyyy-MM-dd HH：mm：ss”或“ yyyy-MM-dd”的字符串。日期的时间部分将被忽略。
| string | next_day(字符串 start_date，字符串 day_of_week)|返回晚于 start_date 并命名为 day_of_week 的第一个日期(从 Hive 1.2.0开始)。 start_date 是一个字符串/日期/时间戳。 day_of_week 是 2 个字母，3 个字母或一周中某天的全名(例如 Mo，tue 和 FRIDAY)。 start_date 的时间部分将被忽略。例如：next_day('2015-01-14'，'TU')= 2015-01-20.
| string | trunc(字符串日期，字符串格式)|返回被截断为该格式指定的单位的日期(从 Hive 1.2.0开始)。支持的格式：MONTH/MON/MM，YEAR/YYYY/YY。示例：trunc('2015-03-17'，'MM')= 2015-03-01.
| double | months_between(date1，date2)|返回日期 date1 和 date2 之间的月份数(从 Hive 1.2.0开始)。如果 date1 晚于 date2，则结果为正。如果 date1 早于 date2，则结果为负。如果 date1 和 date2 是月份的同一天或月份的最后几天，则结果始终是整数。否则，UDF 将基于 31 天的月份来计算结果的小数部分，并考虑时间分量 date1 和 date2 的差异。 date1 和 date2 类型可以是日期，时间戳或字符串，格式为“ yyyy-MM-dd”或“ yyyy-MM-dd HH：mm：ss”。结果四舍五入到小数点后 8 位。例如：months_between('1997-02-28 10:30:00'，'1996-10-30')= 3.94959677 |
| string | date_format(日期/时间戳记/字符串 ts，字符串 fmt)|将日期/时间戳记/字符串转换为日期格式 fmt(从 Hive 1.2.0起)指定的格式的字符串值。支持的格式是 Java SimpleDateFormat 格式https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html。第二个参数 fmt 应该是常量。示例：date_format('2015-04-08'，'y')='2015'。

date_format 可用于实现其他 UDF，例如：

dayname(date)是 date_format(date，'EEEE')

dayofyear(date)是 date_format(date，'D')|

Conditional Functions

String Functions

Hive 支持以下内置的 String 函数：

数据屏蔽功能

Hive 支持以下内置数据屏蔽功能：

Misc. Functions

xpath

LanguageManual XPathUDF中描述了以下功能：

• xpath，xpath_short，xpath_int，xpath_long，xpath_float，xpath_double，xpath_number，xpath_string

get_json_object

支持受限版本的 JSONPath：

• $：根对象

• 。 ：子运算符

• []：数组的下标运算符

• *：[]的通配符

不支持的语法值得注意：

• ：零长度字符串作为键

• ..：递归下降

• @：当前对象/元素

• ()：脚本表达式

• ？()：过滤(脚本)表达式。

• [，]：联合运算符

• [start：end.step]：数组切片运算符

示例：src_json 表是单列(json)，单行表：

+----+
                               json
+----+
{"store":
  {"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
   "bicycle":{"price":19.95,"color":"red"}
  },
 "email":"amy@only_for_json_udf_test.net",
 "owner":"amy"
}
+----+

可以使用以下查询来提取 json 对象的字段：

hive> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
amy

hive> SELECT get_json_object(src_json.json, '$.store.fruit\[0]') FROM src_json;
{"weight":8,"type":"apple"}

hive> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
NULL

内置的汇总函数(UDAF)

Hive 支持以下内置的聚合函数：

count(expr)-返回提供的表达式为非 NULL 的行数。
count(DISTINCT expr [，expr])-返回为其提供的表达式唯一且非 NULL 的行数。可以使用hive.optimize.distinct.rewrite来优化执行。
|| DOUBLE | sum(col)，sum(DISTINCT col)|返回组中元素的总和或组中列的不同值的总和。
|| DOUBLE | avg(col)，avg(DISTINCT col)|返回组中元素的平均值或组中列的不同值的平均值。
| DOUBLE | min(col)|返回组中列的最小值。
| DOUBLE | max(col)|返回组中列的最大值。
|| DOUBLE | variance(col)，var_pop(col)|返回组中数字列的方差。
|| DOUBLE | var_samp(col)|返回组中数字列的无偏 samples 方差。
| DOUBLE | stddev_pop(col)|返回组中数字列的标准偏差。
| DOUBLE | stddev_samp(col)|返回组中数字列的无偏 samples 标准差。
| DOUBLE | covar_pop(col1，col2)|返回组中一对数字列的总体协方差。
|| DOUBLE | covar_samp(col1，col2)|返回组中一对数字列的 samples 协方差。
|| DOUBLE | corr(col1，col2)|返回组中一对数字列的皮尔逊相关系数。
| DOUBLE | percentile(BIGINT col，p)|返回组中列的精确 pth 百分位数(不适用于浮点类型)。 p 必须在 0 到 1 之间。注意：只能为整数值计算真实的百分位数。如果您 Importing 的内容不是整数，请使用 PERCENTILE_APPROX。
| array<double> | percentile(BIGINT col，array(p1 [，p2] ...))|返回组中列的确切百分位数 p1，p2，...(不适用于浮点类型)。 pi 必须在 0 到 1 之间。注意：只能为整数值计算真实百分位数。如果您 Importing 的内容不是整数，请使用 PERCENTILE_APPROX。
| DOUBLE | percentile_approx(DOUBLE col，p [，B])|返回组中数字列(包括浮点类型)的大约 pth 百分位数。 B 参数控制近似精度，但以存储为代价。值越高，近似值越好，默认值为 10,000.当 col 中的不同值的数量小于 B 时，这将给出精确的百分位数。
| array<double> | percentile_approx(DOUBLE col，array(p1 [，p2] ...)[，B])|与上述相同，但是接受并返回一个百分数值数组，而不是单个百分数值。
| double | regr_avgx(独立，从属)|相当于 avg(独立)。自Hive 2.2.0。
| double | regr_avgy(独立，从属)|相当于 avg(独立)。自Hive 2.2.0。
| double | regr_count(独立，从属)|返回用于拟合线性回归线的非空对的数量。自Hive 2.2.0。
| double | regr_intercept(独立，从属)|返回线性回归线的 y 截距，即方程式 dependent = a 独立 b 中的 b 值。自Hive 2.2.0。|
| double | regr_r2(独立，从属)|返回确定系数以进行回归。自Hive 2.2.0。|
| double | regr_slope(独立，从属)|返回线性回归线的斜率，即方程式 dependent = a 独立 b 中 a 的值。自Hive 2.2.0。|
| double | regr_sxx(独立，从属)|等效于 regr_count(独立，从属) var_pop(独立)。自Hive 2.2.0。
| double | regr_sxy(独立，从属)|相当于 regr_count(独立，从属) covar_pop(独立，从属)。自Hive 2.2.0。
| double | regr_syy(独立，从属)|等效于 regr_count(独立，从属)* var_pop(独立)。自Hive 2.2.0。
| array<struct { 'x','y' } > | histogram_numeric(col，b)|使用 b 个非均匀间隔的 bin 计算组中数字列的直方图。输出是大小为 b 的双值(x，y)坐标数组，这些数组表示 bin 的中心和高度|
| array | collect_set(col)|返回消除了重复元素的一组对象。
| array | collect_list(col)|返回具有重复项的对象列表。 (自 Hive 0.13.0起。)|
| INTEGER | ntile(INTEGER x)|将有序分区划分为称为桶的x组，并为分区中的每一行分配一个桶号。这样可以轻松计算三分位数，四分位数，十分位数，百分位数和其他常见的汇总统计信息。 (自 Hive 0.11.0起。)|

内置表生成函数(UDTF)

普通的用户定义函数(例如 concat())接受单个 Importing 行并输出单个输出行。相反，表生成函数将单个 Importing 行转换为多个输出行。

Usage Examples

explode (array)

select explode(array('A','B','C'));
select explode(array('A','B','C')) as col;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view explode(array('A','B','C')) tf as col;

---

---

explode (map)

select explode(map('A',10,'B',20,'C',30));
select explode(map('A',10,'B',20,'C',30)) as (key,value);
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf;
select tf.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf as key,value;

posexplode (array)

select posexplode(array('A','B','C'));
select posexplode(array('A','B','C')) as (pos,val);
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf;
select tf.* from (select 0) t lateral view posexplode(array('A','B','C')) tf as pos,val;

内联(结构数组)

select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02')));
select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) as (col1,col2,col3);
select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf;
select tf.* from (select 0) t lateral view inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) tf as col1,col2,col3;

---

---

stack (values)

select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01');
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') tf;
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') tf as col0,col1,col2;

使用语法“ SELECT udtf(col)AS colAlias ...”有一些限制：

• SELECT 中不允许其他表达式

• 不支持 SELECT pageid，explode(adid_list)AS myCol ...

• UDTF 不能嵌套

• 不支持 SELECT explode(explode(adid_list))AS myCol ...

• 不支持 GROUP BY/CLUSTER BY/DISTRIBUTE BY/SORT BY

• SELECT explode(adid_list)AS myCol ...不支持 GROUP BY myCol

不具有这些限制的其他语法，请参见LanguageManual LateralView。

如果要创建自定义 UDTF，也请参见Writing UDTFs。

explode

explode()接受数组(或 Map)作为 Importing，并将数组(Map)的元素作为单独的行输出。 UDTF 可以在 SELECT 表达式列表中使用，也可以作为 LATERAL VIEW 的一部分使用。

作为在 SELECT 表达式列表中使用explode()的示例，请考虑一个名为 myTable 的表，该表具有单列(myCol)和两行：

然后运行查询：

SELECT explode(myCol) AS myNewCol FROM myTable;

will produce:

Maps 的用法类似：

SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable;

posexplode

posexplode()与explode类似，但它不仅返回数组的元素，还返回元素及其在原始数组中的位置。

作为在 SELECT 表达式列表中使用posexplode()的示例，请考虑一个名为 myTable 的表，该表具有单列(myCol)和两行：

然后运行查询：

SELECT posexplode(myCol) AS pos, myNewCol FROM myTable;

will produce:

json_tuple

Hive 0.7 中引入了新的 json_tuple()UDTF。它使用一组名称(键)和一个 JSON 字符串，并使用一个函数返回值的 Tuples。这比调用 GET_JSON_OBJECT 从单个 JSON 字符串中检索多个密钥要有效得多。在任何情况下，单个 JSON 字符串都会被解析多次，如果您解析一次 JSON_TUPLE，查询将更加高效。由于 JSON_TUPLE 是 UDTF，因此您将需要使用LATERAL VIEW语法来实现相同的目标。

For example,

select a.timestamp, get_json_object(a.appevents, '$.eventid'), get_json_object(a.appenvets, '$.eventname') from log a;

应该更改为：

select a.timestamp, b.*
from log a lateral view json_tuple(a.appevent, 'eventid', 'eventname') b as f1, f2;

parse_url_tuple

parse_url_tuple()UDTF 与 parse_url()相似，但是可以提取给定 URL 的多个部分，以 Tuples 形式返回数据。可以通过将冒号和键附加到 partToExtract 参数来提取 QUERY 中特定键的值，例如 parse_url_tuple('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1' ，'QUERY：k1'，'QUERY：k2')返回值为'v1'，'v2'的 Tuples。这比多次调用 parse_url()更有效。所有 Importing 参数和输出列类型都是字符串。

SELECT b.*
FROM src LATERAL VIEW parse_url_tuple(fullurl, 'HOST', 'PATH', 'QUERY', 'QUERY:id') b as host, path, query, query_id LIMIT 1;

对 f(列)进行分组和排序

典型的 OLAP 模式是您有一个 timestamp 列，并且希望按每日或其他较不精确的日期窗口进行分组，而不是按秒进行分组。因此，您可能要选择 concat(year(dt)，month(dt))，然后在该 concat()上分组。但是，如果您尝试在应用了函数和别名的列上使用 GROUP BY 或 SORT BY，如下所示：

select f(col) as fc, count(*) from table_name group by fc;

你会得到一个错误：

FAILED: Error in semantic analysis: line 1:69 Invalid Table Alias or Column Reference fc

因为您无法对已应用功能的列别名进行 GROUP BY 或 SORT BY。有两种解决方法。首先，您可以使用子查询来重新构造此查询，这有点复杂：

select sq.fc,col1,col2,...,colN,count(*) from
  (select f(col) as fc,col1,col2,...,colN from table_name) sq
 group by sq.fc,col1,col2,...,colN;

或者您可以确保不使用更简单的列别名：

select f(col) as fc, count(*) from table_name group by f(col);

如果您想进一步讨论，请与 RiotGames dot com 的 Tim Ellis(tellis)联系。

Utility Functions

UDF internals

UDF 的评估方法的上下文一次是一行。像这样的 UDF 的简单调用

SELECT length(string_col) FROM table_name;

将评估作业的 Map 部分中每个 string_col 值的长度。在 Map 端评估 UDF 的副作用是您无法控制发送到 Map 器的行的 Sequences。发送到 Map 器的文件拆分的序列化 Sequences 与此相同。任何 reduce 边操作(例如 SORT BY，ORDER BY，常规 JOIN 等)将应用于 UDF 输出，就好像它只是表的另一列一样。这很好，因为 UDF 的评估方法的上下文一次只能排成一行。

如果您想控制将哪些行发送到相同的 UDF(可能以什么 Sequences)，您将敦促在简化阶段对 UDF 进行评估。这可以通过使用分配依据，排序依据，分组依据实现。查询示例为：

SELECT reducer_udf(my_col, distribute_col, sort_col) FROM
(SELECT my_col, distribute_col, sort_col FROM table_name DISTRIBUTE BY distribute_col SORT BY distribute_col, sort_col) t

但是，有人可能会争辩说，控制发送到同一 UDF 的行集的前提是在该 UDF 中进行聚合。在这种情况下，使用用户定义的聚合函数(UDAF)是更好的选择。您可以阅读有关编写 UDAF here的更多信息。或者，您可以使用Hive 的转换功能使用自定义的 reduce 脚本来完成相同的任务。这两个选项都将在归约方面进行汇总。

创建自定义 UDF

有关如何创建自定义 UDF 的信息，请参见Hive Plugins和Create Function。

选择 explode(array('A'，'B'，'C'));选择 explode(array('A'，'B'，'C'))作为 col;从(选择 0)t 中选择 tf.侧面爆炸(array('A'，'B'，'C'))tf;从(选择 0)中选择 tf. t 侧面爆炸(array('A'，'B'，'C')) tf 作为 col;