hive 算子和用户自定义函数 UDF UDAF UDTF

A = B

All primitive types

TRUE if expression A is equal to expression B otherwise FALSE.

A <=> B

All primitive types

Returns same result with EQUAL(=) operator for non-null operands, but returns TRUE if both are NULL, FALSE if one of the them is NULL. (As of version 0.9.0.)

A <> B

All primitive types

NULL if A or B is NULL, TRUE if expression A is NOT equal to expression B, otherwise FALSE.

A != B

All primitive types

Synonym for the <> operator.

A < B

All primitive types

NULL if A or B is NULL, TRUE if expression A is less than expression B, otherwise FALSE.

A <= B

All primitive types

NULL if A or B is NULL, TRUE if expression A is less than or equal to expression B, otherwise FALSE.

A > B

All primitive types

NULL if A or B is NULL, TRUE if expression A is greater than expression B, otherwise FALSE.

A >= B

All primitive types

NULL if A or B is NULL, TRUE if expression A is greater than or equal to expression B, otherwise FALSE.

A [NOT] BETWEEN B AND C

All primitive types

NULL if A, B or C is NULL, TRUE if A is greater than or equal to B AND A less than or equal to C, otherwise FALSE. This can be inverted by using the NOT keyword. (As of version 0.9.0.)

A IS NULL

All types

TRUE if expression A evaluates to NULL, otherwise FALSE.

A IS NOT NULL

All types

FALSE if expression A evaluates to NULL, otherwise TRUE.

Evaluates to TRUE only if A mets the condition. (since:3.0.0 )
Note: NULL is UNKNOWN, and because of that (UNKNOWN IS TRUE) and (UNKNOWN IS FALSE) both evaluates to FALSE.

A [NOT] LIKE B

strings

NULL if A or B is NULL, TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character. The character in B matches any character in A (similar to . in posix regular expressions) while the % character in B matches an arbitrary number of characters in A (similar to .* in posix regular expressions). For example, ‘foobar’ like ‘foo’ evaluates to FALSE whereas ‘foobar’ like ‘foo ‘ evaluates to TRUE and so does ‘foobar’ like ‘foo%’.

A RLIKE B

strings

NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. For example, ‘foobar’ RLIKE ‘foo’ evaluates to TRUE and so does ‘foobar’ RLIKE ‘^f.*r$’.

A REGEXP B

strings

Same as RLIKE.

A + B

All number types

Gives the result of adding A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. For example since every integer is a float, therefore float is a containing type of integer so the + operator on a float and an int will result in a float.

A - B

All number types

Gives the result of subtracting B from A. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A * B

All number types

Gives the result of multiplying A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. Note that if the multiplication causing overflow, you will have to cast one of the operators to a type higher in the type hierarchy.

A / B

All number types

Gives the result of dividing A by B. The result is a double type in most cases. When A and B are both integers, the result is a double type except when the hive.compat configuration parameter is set to “0.13” or “latest” in which case the result is a decimal type.

A % B

All number types

Gives the reminder resulting from dividing A by B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A & B

All number types

Gives the result of bitwise AND of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A | B

All number types

Gives the result of bitwise OR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A ^ B

All number types

Gives the result of bitwise XOR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

~A

All number types

Gives the result of bitwise NOT of A. The type of the result is the same as the type of A.

A AND B

boolean

TRUE if both A and B are TRUE, otherwise FALSE. NULL if A or B is NULL.

A OR B

boolean

TRUE if either A or B or both are TRUE, FALSE OR NULL is NULL, otherwise FALSE.

NOT A

boolean

TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.

! A

boolean

Same as NOT A.

A IN (val1, val2, …)

boolean

TRUE if A is equal to any of the values. As of Hive 0.13 subqueries are supported in IN statements.

A NOT IN (val1, val2, …)

boolean

TRUE if A is not equal to any of the values. As of Hive 0.13 subqueries are supported in NOT IN statements.

[NOT] EXISTS (subquery)

A || B

strings 

map

(key1, value1, key2, value2, …)

struct

(val1, val2, val3, …)

named_struct

(name1, val1, name2, val2, …)

array

(val1, val2, …)

create_union

(tag, val1, val2, …)

A[n]

A is an Array and n is an int

M[key]

M is a Map<K, V> and key has type K

S.x

S is a struct

DOUBLE

round(DOUBLE a)

返回 a 的近似值，四舍五入

DOUBLE

round(DOUBLE a, INT d)

返回 a 的近似值，小数点后留 d 位

BIGINT

floor(DOUBLE a)

BIGINT

ceil(DOUBLE a), ceiling(DOUBLE a)

DOUBLE

rand(), rand(INT seed)

DOUBLE

exp(DOUBLE a), exp(DECIMAL a)

DOUBLE

ln(DOUBLE a), ln(DECIMAL a)

DOUBLE

log10(DOUBLE a), log10(DECIMAL a)

DOUBLE

log2(DOUBLE a), log2(DECIMAL a)

DOUBLE

log(DOUBLE base, DOUBLE a)

log(DECIMAL base, DECIMAL a)

DOUBLE

pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)

Returns ap.

DOUBLE

sqrt(DOUBLE a), sqrt(DECIMAL a)

返回参数a的平方跟。在Hive 0.13.0中添加的十进制版本。

STRING

bin(BIGINT a)

STRING

hex(BIGINT a) hex(STRING a) hex(BINARY a)

如果参数是INT或二进制的，十六进制格式的字符串返回数字。否则，如果数字是字符串，则将每个字符转换为十六进制表示形式并返回结果字符串。(See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)

BINARY

unhex(STRING a)

逆的十六进制。将每对字符解释为十六进制数，并将其转换为该数的字节表示形式。 (BINARY version as of Hive 0.12.0, used to return a string.)

STRING

conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)

将数字从给定的基数转换为另一个基数 (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).

DOUBLE

abs(DOUBLE a)

INT or DOUBLE

pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)

DOUBLE

sin(DOUBLE a), sin(DECIMAL a)

DOUBLE

asin(DOUBLE a), asin(DECIMAL a)

如果-1<=a<=1返回arc sin a，否则返回NULL。十进制版本添加在Hive 0.13.0。

DOUBLE

cos(DOUBLE a), cos(DECIMAL a)

DOUBLE

acos(DOUBLE a), acos(DECIMAL a)

DOUBLE

tan(DOUBLE a), tan(DECIMAL a)

DOUBLE

atan(DOUBLE a), atan(DECIMAL a)

Returns the 反正切函数 of a. Decimal version added in Hive 0.13.0.

DOUBLE

degrees(DOUBLE a), degrees(DECIMAL a)

将a的值从弧度转换为角度。Decimal version added in Hive 0.13.0.

DOUBLE

radians(DOUBLE a), radians(DOUBLE a)

将a的值从度转换为弧度。 Decimal version added in Hive 0.13.0.

INT or DOUBLE

positive(INT a), positive(DOUBLE a)

Returns a.

INT or DOUBLE

negative(INT a), negative(DOUBLE a)

Returns -a.

DOUBLE or INT

sign(DOUBLE a), sign(DECIMAL a)

返回符号a为’1.0’(如果a是正数)或’-1.0’(如果a是负数)，否则’0.0’。十进制版本返回INT而不是DOUBLE。 Decimal version added in Hive 0.13.0.

DOUBLE

e()

Returns the value of e.

DOUBLE

pi()

Returns the value of pi.

INT

BIGINT

shiftleft(TINYINT|SMALLINT|INT a, INT b)

shiftleft(BIGINT a, INT b)

INT

BIGINT

shiftright(TINYINT|SMALLINT|INT a, INT b)

shiftright(BIGINT a, INT b)

INT

BIGINT

shiftrightunsigned(TINYINT|SMALLINT|INT a, INT b),

shiftrightunsigned(BIGINT a, INT b)

通过将expr映射到第i个大小相等的桶中，返回0到num_buckets+1之间的整数。桶是通过将[min_value, max_value]分割成大小相等的区域来实现的。如果expr < min_value，返回1，如果expr > max_value返回num_buckets+1。See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm (as of Hive 3.0.0)

int

size(Map<K.V>)

int

size(Array<T>)

array<K>

map_keys(Map<K.V>)

array<V>

map_values(Map<K.V>)

boolean

array_contains(Array<T>, value)

array<t>

sort_array(Array<T>)

binary(string|binary)

cast(expr as <type>)

string

from_unixtime(bigint unixtime[, string format])

select from_unixtime(1576217619);时间戳转时间

2019-12-12 22:13:39

bigint

unix_timestamp()

bigint

unix_timestamp(string date)

bigint

unix_timestamp(string date, string pattern)

转换时间字符串与给定的模式 (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), return 0 if fail: unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) = 1237532400.

pre 2.1.0: string

to_date(string timestamp)

int

year(string date)

int

int

month(string date)

int

day(string date) dayofmonth(date)

int

hour(string date)

int

minute(string date)

int

second(string date)

int

weekofyear(string date)

extract(field FROM source)

从源检索字段，例如天或小时(从Hive 2.2.0开始)。源文件必须是一个日期、时间戳、间隔或一个可以转换为日期或时间戳的字符串。支持的字段包括:天，星期，小时，分钟，月，季度，第二，周和年。

Examples:

1. select extract(month from “2016-10-20”) results in 10.




2. select extract(hour from “2016-10-20 05:06:07”) results in 5.




3. select extract(dayofweek from “2016-10-20 05:06:07”) results in 5.




4. select extract(month from interval ‘1-3’ year to month) results in 3.




5. select extract(minute from interval ‘3 12:20:30’ day to second) results in 20.

int

datediff(string enddate, string startdate)

pre 2.1.0: string

date_add(date/timestamp/string startdate, tinyint/smallint/int days)

pre 2.1.0: string

date_sub(date/timestamp/string startdate, tinyint/smallint/int days)

timestamp

from_utc_timestamp({ any primitive type} ts, string timezone)

将UTC中的时间戳转换为给定的时区(从Hive 0.8.0开始)。
timestamp是一个基本类型，包括timestamp/date、tinyint/smallint/int/bigint、float/double和decimal。
分数值被认为是秒。整数值被认为是毫秒。

例如，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, string timezone)

将给定时区中的时间戳转换为UTC(从Hive 0.8.0开始)。
timestamp是一个基本类型，包括timestamp/date、tinyint/smallint/int/bigint、float/double和decimal。
分数值被认为是秒。整数值被认为是毫秒。

例如，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。

返回start_date之后的num_months日期(从Hive 1.1.0开始)。start_date是一个字符串、日期或时间戳。num_months是一个整数。如果start_date是一个月的最后一天，或者结果月份的天数少于start_date的day组件，那么结果就是结果月份的最后一天。否则，结果具有与start_date相同的day组件。默认的输出格式是’yyyy-MM-dd’。
在Hive 4.0.0之前，日期的时间部分被忽略。
从Hive 4.0.0开始，add_months支持一个可选参数output_date_format，它接受一个represe字符串

For example :

add_months(‘2009-08-31’, 1) returns ‘2009-09-30’.
add_months(‘2017-12-31 14:15:16’, 2, ‘YYYY-MM-dd HH:mm:ss’) returns ‘2018-02-28 14:15:16’.

返回比start_date晚的第一个日期，并将其命名为day_of_week(从Hive 1.2.0开始)。start_date是一个字符串/日期/时间戳。day_of_week是指2个字母、3个字母或一周中某一天的全称(如Mo、tue、FRIDAY)。start_date的时间部分被忽略。

Example: next_day(‘2015-01-14’, ‘TU’) = 2015-01-20.

返回截断到格式指定的单元的日期(从Hive 1.2.0开始)。支持的格式:MONTH/MON/MM, YEAR/YYYY/YY。

Example: trunc(‘2015-03-17’, ‘MM’) = 2015-03-01.

返回日期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”格式的日期、时间戳或字符串。结果四舍五入到小数点后八位。

Example: months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677

将日期/时间戳/字符串转换为日期格式fmt指定的字符串值(从Hive 1.2.0开始)。

Supported formats are Java SimpleDateFormat formats – https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html. The second argument fmt should be constant. Example: date_format(‘2015-04-08’, ‘y’) = ‘2015’.

date_format can be used to implement other UDFs, e.g.:

• dayname(date) is date_format(date, ‘EEEE’)


• dayofyear(date) is date_format(date, ‘D’)

T

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

T

COALESCE(T v1, T v2, …)

T

CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END

When a = b, returns c; when a = d, returns e; else returns f.

T

CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END

When a = true, returns b; when c = true, returns d; else returns e.

如果a=b返回NULL;否则返回一个(从Hive 2.3.0开始)。
简写为: CASE WHEN a = b then NULL else a

int

ascii(string str)

string

base64(binary bin)

string

concat(string|binary A, string|binary B…)

array<struct<string,double>>

context_ngrams(array<array<string>>, array<string>, int K, int pf)

给定一串“上下文”，从一组标记化的句子中返回前k个上下文相关的n -gram. See StatisticsAndDataMining for more information.

string

concat_ws(string SEP, string A, string B…)

string

concat_ws(string SEP, array<string>)

就像上面的concat_ws()一样，但是使用一个字符串数组。(as of Hive 0.9.0)

string

decode(binary bin, string charset)

使用提供的字符集(“US-ASCII”、“ISO-8859-1”、“UTF-8”、“UTF-16BE”、“UTF-16LE”、“UTF-16”中的一个)将第一个参数解码为字符串。如果其中一个参数为空，那么结果也将为空。 (As of Hive 0.12.0.)

返回索引号处的字符串。例如elt(2，’hello’，’world’)返回’world’。如果N小于1或大于参数的数目，则返回NULL。

(see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_elt)

binary

encode(string src, string charset)

使用提供的字符集(“US-ASCII”、“ISO-8859-1”、“UTF-8”、“UTF-16BE”、“UTF-16LE”、“UTF-16”中的一个)将第一个参数编码为二进制。如果其中一个参数为空，那么结果也将为空。 (As of Hive 0.12.0.)

返回val1、val2、val3、…列表或0，如果没有找到。例如，field(‘world’，’say’，’hello’，’world’)返回3。
支持所有基本类型，使用str.equals(x)比较参数。如果val为空，则返回值为0。

(see https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_field)

int

find_in_set(string str, string strList)

返回strList中第一次出现的str，其中strList是逗号分隔的字符串。如果其中一个参数为空，则返回null。如果第一个参数包含任何逗号，则返回0。

For example, find_in_set(‘ab’, ‘abc,b,ab,c,def’) returns 3.

string

format_number(number x, int d)

将数字X格式化为’#，###，###。四舍五入到D位小数，并以字符串形式返回结果。如果D为0，则结果没有小数点或小数部分。 (As of Hive 0.10.0; bug with float types fixed in Hive 0.14.0, decimal type support added in Hive 0.14.0)

string

get_json_object(string json_string, string path)

boolean

in_file(string str, string filename)

int

instr(string str, string substr)

int

length(string A)

int

locate(string substr, string str[, int pos])

string

lower(string A) lcase(string A)

string

lpad(string str, int len, string pad)

string

ltrim(string A)

array<struct<string,double>>

ngrams(array<array<string>>, int N, int K, int pf)

从一组标记化的句子返回前k个n -gram，例如句子()UDAF返回的那些句子。

See StatisticsAndDataMining for more information.

string

parse_url(string urlString, string partToExtract [, string keyToExtract])

string

printf(String format, Obj… args)

返回引用字符串(包括任何单引号HIVE-4.0.0的转义字符)

string

regexp_extract(string subject, string pattern, int index)

返回使用模式提取的字符串。例如，regexp_extract(‘foothebar’， ‘foo(.*?)(bar)’， 2)返回’bar。请注意，在使用预定义的字符类时需要注意:使用’\s’作为第二个参数将匹配字母s;’\s’是匹配空格等所必需的。“index”参数是Java regex Matcher group()方法索引。

See docs/api/java/util/regex/Matcher.html for more information on the ‘index’ or Java regex group() method.

string

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

string

repeat(string str, int n)

string

reverse(string A)

string

rpad(string str, int len, string pad)

string

rtrim(string A)

array<array<string>>

sentences(string str, string lang, string locale)

将一串自然语言文本标记为单词和句子，其中每个句子在适当的句子边界处断开，并作为单词数组返回。“lang”和“locale”是可选参数。例如， sentences(‘Hello there! How are you?’) returns ( (“Hello”, “there”), (“How”, “are”, “you”) ).

string

space(int n)

array

split(string str, string pat)

map<string,string>

str_to_map(text[, delimiter1, delimiter2])

使用两个分隔符将文本分割为键-值对。Delimiter1将文本分隔成K-V对，Delimiter2将每个K-V对分隔开。默认的分隔符是 ‘,’ for delimiter1 and ‘:’ for delimiter2.

string

substr(string|binary A, int start) substring(string|binary A, int start)

返回从起始位置开始到字符串A结束的字节数组的子字符串或片段。例如，substr(‘foobar’， 4)结果’bar’(see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]).

string

substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)

返回长度为len的起始位置的字节数组的子字符串或切片。例如，substr(‘foobar’， 4,1)的结果是’b’(see [http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr]).

从字符串A中返回分隔符delim的计数前的子字符串(从Hive 1.3.0开始)。如果count为正数，则返回最后分隔符左边的所有内容(从左边计数)。如果count为负数，则返回最后分隔符右边的所有内容(从右边计数)。Substring_index在搜索delim时执行区分大小写的匹配。

Example: substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’.

string

translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)

通过将from字符串中出现的字符替换为to字符串中相应的字符来转换输入字符串。这类似于PostgreSQL中的translate函数。如果此UDF的任何参数为空，则结果也为空。(Available as of Hive 0.10.0, for string types)

Char/varchar support added as of Hive 0.14.0.

string

trim(string A)

binary

unbase64(string str)

将参数从基本64字符串转换为二进制。 (As of Hive 0.12.0.)

string

upper(string A) ucase(string A)

返回将A的所有字符转换为大写所得到的字符串。For example, upper(‘fOoBaR’) results in ‘FOOBAR’.

string

mask(string str[, string upper[, string lower[, string number]]])

返回一个隐藏的str版本，显示最后n个未隐藏的字符(as of Hive 2.1.0). 默认情况下，大写字母转换为“X”，小写字母转换为“X”，数字转换为“n”。例如，mask(“abcd-EFGH-8765-4321”)的结果是xx- xx- nnnn-nnnn。您可以通过提供额外的参数来覆盖掩码中使用的字符:第二个参数控制大写字母的掩码字符，第三个参数控制小写字母，第四个参数控制数字。For example, mask(“abcd-EFGH-8765-4321”, “U”, “l”, “#”) results in llll-UUUU-####-####.

返回前n个值被屏蔽的str的屏蔽版本 (as of Hive 2.1.0). 大写字母转换为“X”，小写字母转换为“X”，数字转换为“n”。

For example, mask_first_n(“1234-5678-8765-4321”, 4) results in nnnn-5678-8765-4321.

varies

java_method(class, method[, arg1[, arg2..]])

类似于反射 (As of Hive 0.9.0.)

varies

reflect(class, method[, arg1[, arg2..]])

使用反射通过匹配参数签名来调用Java方法。(As of Hive 0.7.0.) See Reflect (Generic) UDF for examples.

int

hash(a1[, a2…])

返回参数的哈希值。 (As of Hive 0.4.)

sha1(string/binary)

sha(string/binary)

计算SHA-2哈希函数族 (SHA-224, SHA-256, SHA-384, and SHA-512) (as of Hive 1.3.0). 第一个参数是要散列的字符串或二进制。第二个参数表示所需的结果位长，它的值必须是224、256、384、512或0(相当于256)。从Java 8开始支持SHA-224。如果参数为NULL，或者散列长度不是允许的值之一，则返回值为NULL。

Example: sha2(‘ABC’, 256) = ‘b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78’.

使用AES加密输入 (as of Hive 1.3.0). 可以使用128、192或256位的密钥长度。如果安装了Java Cryptography Extension (JCE)无限权限策略文件，则可以使用192和256位密钥。如果参数为NULL或键长度不属于允许的值之一，则返回值为NULL。

Example: base64(aes_encrypt(‘ABC’, ‘1234567890123456’)) = ‘y6Ss+zCYObpCbgfWfyNWTw==’.

BIGINT

count(), count(expr), count(DISTINCT expr[, expr…])

count() - Returns the total number of retrieved rows, including rows containing NULL values.

count(expr) - Returns the number of rows for which the supplied expression is non-NULL.

count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL. Execution of this can be optimized with hive.optimize.distinct.rewrite.

DOUBLE

sum(col), sum(DISTINCT col)

Returns the sum of the elements in the group or the sum of the distinct values of the column in the group.

DOUBLE

avg(col), avg(DISTINCT col)

Returns the average of the elements in the group or the average of the distinct values of the column in the group.

DOUBLE

min(col)

Returns the minimum of the column in the group.

DOUBLE

max(col)

Returns the maximum value of the column in the group.

DOUBLE

variance(col), var_pop(col)

DOUBLE

var_samp(col)

DOUBLE

stddev_pop(col)

DOUBLE

stddev_samp(col)

返回组中数字列的无偏样本标准差。

DOUBLE

covar_pop(col1, col2)

DOUBLE

covar_samp(col1, col2)

DOUBLE

corr(col1, col2)

DOUBLE

percentile(BIGINT col, p)

array<double>

percentile(BIGINT col, array(p1 [, p2]…))

DOUBLE

percentile_approx(DOUBLE col, p [, B])

array<double>

percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B])

regr_avgx(independent, dependent)

Equivalent to avg(dependent). As of Hive 2.2.0.

regr_avgy(independent, dependent)

Equivalent to avg(independent). As of Hive 2.2.0.

regr_count(independent, dependent)

返回用于拟合线性回归线的非空对的数目。As of Hive 2.2.0.

regr_intercept(independent, dependent)

返回线性回归线的y轴截距，即 dependent = a independent + b. As of Hive 2.2.0.

regr_r2(independent, dependent)

返回回归的确定系数. As of Hive 2.2.0.

regr_slope(independent, dependent)

返回线性回归线的斜率，即 equation dependent = a independent + b. As of Hive 2.2.0.

regr_sxx(independent, dependent)

相当于 regr_count(independent, dependent) var_pop(dependent). As of Hive 2.2.0.

regr_sxy(independent, dependent)

相当于 regr_count(independent, dependent) covar_pop(independent, dependent). As of Hive 2.2.0.

相当于 regr_count(independent, dependent) * var_pop(independent). As of Hive 2.2.0.

array<struct { ‘x’,’y’}>

histogram_numeric(col, b)

使用b个非均匀间隔的容器计算组中一个数字列的直方图。输出是一个大小为b的双值(x,y)坐标数组，代表 bin centers and heights

array

collect_set(col)

array

collect_list(col)

返回具有重复项的对象列表。 (As of Hive 0.13.0.)

将一个有序的分区划分为x组，称为bucket，并为分区中的每一行分配一个bucket编号。这样可以方便地计算三位数、四分位数、十分位数、百分位数和其他常见的汇总统计数据。(As of Hive 0.11.0.)

T

explode(ARRAY<T> a)

Tkey,Tvalue

explode(MAP<Tkey,Tvalue> m)

将映射转为为多行。返回具有两列(键、值)的行集，即输入映射中的每个键-值对对应一行. (As of Hive 0.8.0.).

T1,…,Tn

inline(ARRAY<STRUCT<f1:T1,…,fn:Tn>> a)

将结构数组转为为多行。返回一个有N列的行集(N =结构中顶层元素的数量)，每个结构从数组中取出一行。 (As of Hive 0.10.)

string1,…,stringn

json_tuple(string jsonStr,string k1,…,string kn)

string 1,…,stringn

parse_url_tuple(string urlStr,string p1,…,string pn)

获取URL字符串和一组n个URL部分，并返回一个包含n个值的元组。这类似于parse_url() UDF，但可以同时从URL提取多个部分。有效的部分名称有: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>.