hive函数大全及使用示例
wiki:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
map,array: https://cwiki.apache.org/Hive/languagemanual-lateralview.html
doc下载:http://ishare.iask.sina.com.cn/f/25020082.html
1.内置运算符
1.1关系运算符
运算符 | 类型 | 说明 |
A = B | 原始类型 | 如果A与B相等,返回TRUE,否则返回FALSE |
A == B | 无 | 失败,因为无效的语法。 SQL使用”=”,不使用”==”。 |
A <=> B | 所有原始类型 | 对于非空操作数,使用EQUAL(=)运算符返回相同的结果,但如果两个均为NULL,则返回TRUE,如果其中之一为NULL,则返回FALSE。(从0.9.0版开始。) |
A <> B | 原始类型 | 如果A不等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL”。 |
A != B | 所有原始类型 | <>运算符的同义词 |
A < B | 原始类型 | 如果A小于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL”。 |
A <= B | 原始类型 | 如果A小于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL”。 |
A > B | 原始类型 | 如果A大于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL”。 |
A >= B | 原始类型 | 如果A大于等于B返回TRUE,否则返回FALSE。如果A或B值为”NULL”,结果返回”NULL”。 |
A [NOT] BETWEEN B AND C | 所有原始类型 | 如果A,B或C为NULL,则为NULL;如果A大于或等于B,而A小于或等于C,则为TRUE,否则为FALSE。可以使用NOT关键字将其反转。(从0.9.0版开始。) |
A IS NULL | 所有类型 | 如果A值为”NULL”,返回TRUE,否则返回FALSE |
A IS NOT NULL | 所有类型 | 如果A值不为”NULL”,返回TRUE,否则返回FALSE |
A IS [NOT] (TRUE|FALSE) | 布尔类型 | 仅当A满足条件时评估为TRUE。(因为:3.0.0 )注意:NULL为UNKNOWN,因此(UNKNOWN IS TRUE)和(UNKNOWN IS FALSE)都评估为FALSE。 |
A [NOT] LIKE B | 字符串 | 如果A或B值为”NULL”,结果返回”NULL”。字符串A与B通过sql进行匹配,如果相符返回TRUE, 不符返回FALSE。 B字符串中 的”_”代表任一字符,”%”则代表多个任意字符。 例如: (‘foobar’ like ‘foo’)返回FALSE,( ‘foobar’ like ‘foo_ _ _’或者 ‘foobar’ like ‘foo%’) 则返回TURE |
A RLIKE B | 字符串 | 如果A或B值为”NULL”,结果返回”NULL”。字符串A与B通过java进行匹配,如果相符返回TRUE, 不符返回FALSE。 例如:( ‘foobar’ rlike ‘foo’)返回FALSE,(’foobar’ rlike ‘^f.*r$’ )返回TRUE。 |
A REGEXP B | 字符串 | 与RLIKE相同。 |
1.2算术运算符
运算符 | 类型 | 说明 |
A + B | 数字类型 | A和B相加。结果的与操作数值有共同类型。例如每一个整数是一个浮点数,浮点数包含整数。 所以,一个浮点数和一个整数相加结果也是一个浮点数。 |
A – B | 数字类型 | A和B相减。结果的与操作数值有共同类型。 |
A * B | 数字类型 | A和B相乘,结果的与操作数值有共同类型。需要说明的是,如果乘法造成溢出,将选择更高的类型。 |
A / B | 数字类型 | A和B相除,结果是一个double(双精度)类型的结果。 |
A DIV B | 整数类型 | 给出将A除以BEg 17 div 3所得的整数部分结果为5。 |
A % B | 数字类型 | A除以B余数与操作数值有共同类型。 |
A & B | 数字类型 | 运算符查看两个参数的二进制表示法的值,并执行按位”与”操作。两个表达式的一位均为1时,则结果的该位为 1。 否则,结果的该位为 0。 |
A | B | 数字类型 | 运算符查看两个参数的二进制表示法的值,并执行按位”或”操作。只要任一表达式的一位为 1,则结果的该位为 1。 否则,结果的该位为 0。 |
A ^ B | 数字类型 | 运算符查看两个参数的二进制表示法的值,并执行按位”异或”操作。当且仅当只有一个表达式的某位上为 1 时, 结果的该位才为 1。 否则结果的该位为 0。 |
~A | 数字类型 | 对一个表达式执行按位”非”(取反)。 |
1.3逻辑运算符
运算符 | 类型 | 说明 |
A AND B | 布尔值 | A和B同时正确时,返回TRUE,否则FALSE。如果A或B值为NULL,返回NULL。 |
A OR B | 布尔值 | A或B正确,或两者同时正确返返回TRUE,否则FALSE。如果A和B值同时为NULL,返回NULL。 |
A [NOT] IN (val1, val2, ...) | 布尔值 | 如果A [不]等于 任何值,则为TRUE。从Hive开始,IN语句支持0.13 子查询 |
A && B | 布尔值 | 与”A AND B”相同 |
A | B | 布尔值 | 与”A OR B”相同 |
NOT A | 布尔值 | 如果A为NULL或错误的时候返回TURE,否则返回FALSE。 |
! A | 布尔值 | 与”NOT A”相同 |
1.4字符串运算符
函数 | 类型 | 说明 |
A || B | 字符串 | 连接操作数-的简写concat(A,B) 。从Hive 2.2.0开始受支持。 |
1.5复杂类型函数
函数 | 类型 | 说明 |
map | (key1, value1, key2, value2, …) | 通过指定的键/值对,创建一个map。 |
struct | (val1, val2, val3, …) | 通过指定的字段值,创建一个结构。结构字段名称将COL1,COL2,… |
named_struct | (name1, val1, name2, val2, ...) | 用给定的字段名称和值创建一个结构。(从Hive 0.8.0开始。) |
array | (val1, val2, …) | 通过指定的元素,创建一个数组。 |
create_union | (tag, val1, val2, ...) | 使用tag参数指向的值创建联合类型 |
1.6对复杂类型函数操作
函数 | 类型 | 说明 |
A[n] | A是一个数组,n为int型 | 返回数组A的第n个元素,第一个元素的索引为0。如果A数组为['foo','bar'], 则 A[0]返回’foo’和A[1]返回”bar”。 |
M[key] | M是Map<K, V>,关键K型 | 返回关键值对应的值,例如mapM为 \{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’\}, 则 M['all'] 返回’foobar’。 |
S.x | S为struct | 返回结构x字符串在结构S中的存储位置。 如 foobar \{int foo, int bar\} foobar.foo的领域中存储的整数。 |
2.内置函数
2.1数学函数
返回类型 | 函数 | 说明 |
BIGINT | round(double a) | 四舍五入 |
DOUBLE | round(double a, int d) | 小数部分d位之后数字四舍五入,例如round(21.263,2),返回21.26 |
DOUBLE | bround(DOUBLE a) |
返回 也称为高斯舍入或银行家舍入。例如:bround(2.5)= 2,bround(3.5)= 4。 |
DOUBLE | bround(DOUBLE a, INT d) |
使用HALF_EVEN舍入模式返回 例如:bround(8.25,1)= 8.2,bround(8.35,1)= 8.4。 |
BIGINT | floor(double a) | 对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21。 |
BIGINT | ceil(double a) ceiling(double a) |
将参数向上舍入为最接近的整数。例如ceil(21.2),返回23. |
double | rand(), rand(int seed) | 返回大于或等于0且小于1的平均分布随机数(依重新计算而变) |
double | exp(double a) | 返回e的n次方 |
double | ln(double a) | 返回给定数值的自然对数 |
double | log10(double a) | 返回给定数值的以10为底自然对数 |
double | log2(double a) | 返回给定数值的以2为底自然对数 |
double | log(double base, double a) | 返回给定底数及指数返回自然对数 |
double | pow(double a, double p) power(double a, double p) |
返回某数的乘幂 |
double | sqrt(double a) | 返回数值的平方根 |
string | bin(BIGINT a) | 返回二进制格式, 参考:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex |
string | hex(BIGINT a) hex(string a) |
将整数或字符转换为十六进制格式。 参考:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex |
string | unhex(string a) | 十六进制字符转换由数字表示的字符。 |
string | conv(BIGINT num, int from_base, int to_base) | 将指定数值,由原来的度量体系转换为指定的试题体系。例如CONV(‘a’,16,2),返回。 参考:’1010′ http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv |
double | abs(double a) | 取绝对值 |
int double | pmod(int a, int b) pmod(double a, double b) |
返回a除b的余数的绝对值 |
double | sin(double a) | 返回给定角度的正弦值 |
double | asin(double a) | 返回x的反正弦,即是X。如果X是在-1到1的正弦值,返回NULL。 |
double | cos(double a) | 返回余弦 |
double | acos(double a) | 返回X的反余弦,即余弦是X,,如果-1<= A <= 1,否则返回null. |
double | tan(DOUBLE a), tan(DECIMAL a) | 返回a (a 以弧度为单位)的切线。Hive 0.13.0中添加了十进制版本 |
double | atan(DOUBLE a), atan(DECIMAL a) | 返回的反正切值a 。Hive 0.13.0中添加了十进制版本 |
double | degrees(DOUBLE a), degrees(DECIMAL a) | 将的值a 从弧度转换为度。Hive 0.13.0中添加了十进制版本 |
double | radians(DOUBLE a), radians(DOUBLE a) | 将值a 从度转换为弧度。Hive 0.13.0中添加了十进制版本 |
int double | positive(int a) positive(double a) |
返回A的值,例如positive(2),返回2。 |
int double | negative(int a) negative(double a) |
返回A的相反数,例如negative(2),返回-2。 |
DOUBLE or INT | sign(DOUBLE a), sign(DECIMAL a) |
返回符号 Hive 0.13.0中添加了十进制版本。 |
DOUBLE | e() | 返回的值e 。 |
DOUBLE | pi() | 返回的值pi。 |
BIGINT | factorial(INT a) | 返回a (从Hive 1.2.0开始)的阶乘。有效a 值为[0..20]。 |
DOUBLE | cbrt(DOUBLE a) | 返回a double值的多维数据集根(从Hive 1.2.0开始)。 |
INT BIGINT |
shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b) |
按位左移(从Hive 1.2.0开始)。 为tinyint,smallint和int返回int |
INT BIGINT |
shiftright(TINYINT|SMALLINT|INT a, INT b) shiftright(BIGINT a, INT b) |
按位右移(从Hive 1.2.0开始)。 为tinyint,smallint和int返回int |
INT BIGINT |
shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b), shiftrightunsigned(BIGINT a, INT b) |
按位无符号右移(从Hive 1.2.0开始)。向右移动位置。 为tinyint,smallint和int返回int |
T | greatest(T v1, T v2, ...) | 返回值列表的最大值。 |
T | least(T v1, T v2, ...) | 返回值列表中的最小值。 |
INT | width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets) |
通过将expr映射到第i个大小相等的存储桶中,返回0到num_buckets + 1之间的整数。 |
2.2收集函数
返回类型 | 函数 | 说明 |
int | size(Map<K.V>) | 返回的map类型的元素的数量 |
int | size(Array<T>) | 返回数组类型的元素数量 |
array<K> | map_keys(Map<K.V>) | 返回包含输入映射键的无序数组。 |
array<V> | map_values(Map<K.V>) | 返回包含输入映射值的无序数组 |
boolean | array_contains(Array<T>, value) | 如果数组包含值,则返回TRUE |
array<t> | sort_array(Array<T>) | 根据数组元素的自然顺序对输入数组进行升序排序并返回(从0.9.0版本开始) |
2.3类型转换函数
返回类型 | 函数 | 说明 |
指定 “type” | cast(expr as <type>) | 类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint),如果转换失败返回NULL。 |
binary | binary(string|binary) | 将参数转换为二进制 |
2.4日期函数
返回类型 | 函数 | 说明 |
string | from_unixtime(bigint unixtime[, string format]) | UNIX_TIMESTAMP参数表示返回一个值’YYYY- MM – DD HH:MM:SS’ 或YYYYMMDDHHMMSS.uuuuuu格式,这取决于是否是在一个字符串或数字语境中使用的功能。 该值表示在当前的时区。 |
bigint | unix_timestamp() | 如果不带参数的调用,返回一个Unix时间戳(从’1970- 01 – 0100:00:00′到现在的 UTC秒数) 为无符号整数。 |
bigint | unix_timestamp(string date) | 指定日期参数调用UNIX_TIMESTAMP(),它返回参数值’1970- 01 – 0100:00:00′到 指定日期的秒数。 |
bigint | unix_timestamp(string date, string pattern) | 指定时间输入格式,返回到1970年秒数: unix_timestamp(’2009-03-20′, ‘yyyy-MM-dd’) = 1237532400 参考:http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html |
string | to_date(string timestamp) | 返回时间中的年月日: to_date(“1970-01-01 00:00:00″) = “1970-01-01″ |
int | year(string date) | 返回指定时间的年份,范围在1000到9999,或为”零”日期的0。 |
int | quarter(date/timestamp/string) |
返回日期,时间戳或范围在1到4之间的字符串的一年的四分之一(从Hive 1.3.0开始)。 示例:quarter('2015-04-08')= 2。 |
int | month(string date) | 返回指定时间的月份,范围为1至12月,或0一个月的一部分,如’0000-00-00′ 或’2008-00-00′的日期。 |
int | day(string date) dayofmonth(date) | 返回指定时间的日期 |
int | hour(string date) | 返回指定时间的小时,范围为0到23。 |
int | minute(string date) | 返回指定时间的分钟,范围为0到59。 |
int | second(string date) | 返回指定时间的秒,范围为0到59。 |
int | weekofyear(string date) | 返回指定日期所在一年中的星期号,范围为0到53。 |
int | extract(field FROM source) |
从源中检索字段,例如天或小时(从Hive 2.2.0开始)。源必须是日期,时间戳,时间间隔或可以转换为日期或时间戳的字符串。 例子:
|
int | datediff(string enddate, string startdate) | 两个时间参数的日期之差。 |
int | date_add(string startdate, int days) | 给定时间,在此基础上加上指定的时间段。 |
int | date_sub(string startdate, int days) | 给定时间,在此基础上减去指定的时间段。 |
timestamp | from_utc_timestamp({any primitive type} ts, string timezone) |
将UTC中的timestamp *转换为给定的时区(从Hive 0.8.0开始)。 |
timestamp | to_utc_timestamp({any primitive type} ts, string timezone) |
将给定时区中的时间戳*转换为UTC(从Hive 0.8.0开始)。 |
date | current_date | 返回查询评估开始时的当前日期(从Hive 1.2.0开始)。同一查询中对current_date的所有调用均返回相同的值。 |
timestamp | current_timestamp | 返回查询评估开始时的当前时间戳(从Hive 1.2.0开始)。同一查询中对current_timestamp的所有调用均返回相同的值。 |
string | add_months(string start_date, int months, output_format) |
返回起始日期之后months的日期。start_date是字符串,日期或时间戳。 add_months支持可选参数output_format,该参数接受一个String, 例如 : add_months('2009-08-31',1)返回'2009-09-30'。 |
string | last_day(string date) | 返回日期所属月份的最后一天(从Hive 1.1.0开始)。date是格式为“ yyyy-MM-dd HH:mm:ss”或“ yyyy-MM-dd”的字符串。 |
string | next_day(string start_date, string day_of_week) | 返回第一个日期,该日期晚于start_date,并命名为day_of_week (从Hive 1.2.0开始)。 start_date是字符串/日期/时间戳。day_of_week是2个字母,3个字母或一周中某天的全名(例如Mo,tue,FRIDAY)。 例如:next_day('2015-01-14','TU')= 2015-01-20。 |
string | trunc(string date, string format) | 返回截断为格式指定单位的日期(从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类型可以是日期,时间戳或字符串,格式为“ 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(date/timestamp/string ts, string fmt) |
将日期/时间戳记/字符串转换为日期格式fmt指定的格式的字符串值(从Hive 1.2.0开始)。 date_format可用于实现其他UDF,例如:
|
2.5条件函数
返回类型 | 函数 | 说明 |
T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 判断是否满足条件,如果满足返回一个值,如果不满足则返回另一个值。 |
boolean | isnull ( a ) | 如果a为NULL,则返回true,否则返回false。 |
boolean | isnotnull ( a ) | 如果a不为NULL,则返回true,否则返回false。 |
T | nvl(T value, T default_value) | 如果value为null,则返回默认值,否则返回值(从HIve 0.11开始)。 |
T | COALESCE(T v1, T v2, …) | 返回一组数据中,第一个不为NULL的值,如果均为NULL,返回NULL。 |
T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 当a=b时,返回c;当a=d时,返回e,否则返回f。 |
T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 当值为a时返回b,当值为c时返回d。否则返回e。 |
T | nullif( a, b ) |
如果a = b,则返回NULL。否则返回a (从Hive 2.3.0开始)。 简写:CASE,当a = b时为NULL,否则为a |
void | assert_true(boolean condition) | 如果'condition'不为true,则引发异常,否则返回null(从Hive 0.8.0开始)。 例如,选择assert_true(2 <1)。 |
2.6字符函数
返回类型 |
函数 |
描述 | ||||||||
---|---|---|---|---|---|---|---|---|---|---|
int |
ascii(string str) |
返回str的第一个字符的数值。 |
||||||||
string |
base64(binary bin) |
将参数从二进制转换为基本64字符串(从Hive 0.12.0开始)。 |
||||||||
int | character_length(string str) | 返回str中包含的UTF-8字符数(从Hive 2.2.0开始)。函数char_length是该函数的简写。 | ||||||||
string | chr(bigint|double A) | 返回具有与A等效的二进制值的ASCII字符(从Hive 1.3.0和2.1.0开始)。如果A大于256,则结果等于chr(A%256)。 示例:选择chr(88); 返回“ X”。 |
||||||||
string |
concat(string|binary A, string|binary B...) |
返回按顺序串联作为参数传入的字符串或字节所得到的字符串或字节。 |
||||||||
array<struct<string,double>> |
context_ngrams(array<array<string>>, array<string>, int K, int pf) |
给定字符串“ context”,从一组标记化语句返回前k个上下文N-gram。 |
||||||||
string |
concat_ws(string SEP, string A, string B...) |
与上面的concat()类似,但具有自定义分隔符SEP。 |
||||||||
string |
concat_ws(string SEP, array<string>) |
就像上面的concat_ws()一样,但是采用字符串数组。(从Hive 0.9.0开始) |
||||||||
string |
decode(binary bin, string charset) |
使用提供的字符集 (“US-ASCII”, “ISO-8859-1”, “UTF-8”, “UTF-16BE”, “UTF-16LE”, “UTF- 16') |
||||||||
string | elt(N int,str1 string,str2 string,str3 string,...) |
返回索引号处的字符串。 (请参阅https://dev.mysql.com/doc/refman/5.7/zh-CN/string-functions.html#function_elt) |
||||||||
binary |
encode(string src, string charset) |
使用提供的字符集 (“US-ASCII”, “ISO-8859-1”, “UTF-8”, “UTF-16BE”, “UTF-16LE”, “UTF- 16') |
||||||||
int | field(val T,val1 T,val2 T,val3 T,...) |
返回val1,val2,val3,...列表中val的索引;如果未找到,则返回0。 (请参阅https://dev.mysql.com/doc/refman/5.7/zh-CN/string-functions.html#function_field) |
||||||||
int |
find_in_set(string str, string strList) |
返回str在strList中的第一次出现,其中strList是一个逗号分隔的字符串。 |
||||||||
string |
format_number(number x, int d) |
将数字X格式化为#,###,###。##之类的格式,四舍五入后将结果作为字符串返回。 |
||||||||
string |
get_json_object(string json_string, string path) |
根据指定的json路径从json字符串中提取json对象,并返回提取的json对象的json字符串。 |
||||||||
boolean |
in_file(string str, string filename) |
如果字符串str在文件名中显示为整行,则返回true。 |
||||||||
int |
instr(string str, string substr) |
|
||||||||
int |
length(string A) |
返回字符串的长度。 |
||||||||
int |
locate(string substr, string str[, int pos]) |
返回在位置pos之后的str中第一次出现substr的位置。 |
||||||||
string |
lower(string A) lcase(string A) |
返回将B的所有字符都转换为小写形式的字符串。 |
||||||||
string |
lpad(string str, int len, string pad) |
返回str,在其左边填充pad,长度为len。如果str大于len,则返回值缩短为len个字符。 |
||||||||
string |
ltrim(string A) |
返回从A的开头(左侧)起修剪空格所得的字符串。 |
||||||||
array<struct<string,double>> |
ngrams(array<array<string>>, int N, int K, int pf) |
从一组标记化的句子中返回前k个N-gram,例如句子()UDAF返回的句子。 有关更多信息,请参见StatisticsAndDataMining。 |
||||||||
int | octet_length(string str) | 返回以UTF-8编码保存字符串str所需的八位字节数(从Hive 2.2.0开始)。 请注意,octet_length(str)可以大于character_length(str)。 |
||||||||
string |
parse_url(string urlString, string partToExtract [, string keyToExtract]) |
从URL返回指定的部分。partToExtract的有效值包括HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE和USERINFO。 |
||||||||
string |
printf(String format, Obj... args) |
返回根据do printf样式格式字符串格式化的输入(从Hive 0.9.0开始)。 |
||||||||
string | quote(String text) |
返回带引号的字符串(包括任何单引号HIVE-4.0.0的转义字符)
|
||||||||
string |
regexp_extract(string subject, string pattern, int index) |
返回使用模式提取的字符串。 使用'\ s'作为第二个参数将与字母s匹配;'\\ s'是匹配空格等所必需的。 |
||||||||
string |
regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT) |
返回将替换INITIAL_STRING中所有与PATTERN中定义的Java正则表达式语法匹配的子字符串替换为REPLACEMENT的实例所产生的字符串。 |
||||||||
string |
repeat(string str, int n) |
重复str n次。 |
||||||||
string | replace(string A, string OLD, string NEW) | 返回字符串A,其中所有不重叠的OLD都替换为NEW(从Hive 1.3.0和2.1.0开始)。 示例:选择replace(“ ababab”,“ abab”,“ Z”); 返回“ Zab”。 |
||||||||
string |
reverse(string A) |
返回反转的字符串。 |
||||||||
string |
rpad(string str, int len, string pad) |
返回str,右用pad填充到len的长度。如果str大于len,则返回值缩短为len个字符。 |
||||||||
string |
rtrim(string A) |
返回从A的结尾(右侧)修剪空格所得到的字符串。 |
||||||||
array<array<string>> |
sentences(string str, string lang, string locale) |
将一串自然语言文本标记为单词和句子,其中每个句子在适当的句子边界处断开并作为单词数组返回。“ lang”和“ locale”是可选参数。 |
||||||||
string |
space(int n) |
返回n个空格的字符串。 |
||||||||
array |
split(string str, string pat) |
在pat周围拆分str(pat是一个正则表达式)。 |
||||||||
map<string,string> |
str_to_map(text[, delimiter1, delimiter2]) |
使用两个定界符将文本拆分为键/值对。Delimiter1将文本分成KV对,Delimiter2将每个KV对分开。默认的定界符是','代表定界符1,':'代表定界符2。 |
||||||||
string |
substr(string|binary A, int start) substring(string|binary A, int start) |
返回A的字节数组的子字符串或切片,从字符串的起始位置开始到字符串A的结尾。例如,substr('foobar',4)的结果为'bar'(请参见[ http://dev.mysql.com /doc/refman/5.0/zh-CN/string-functions.html#function_substr ])。 |
||||||||
string |
substr(string|binary A, int start, int len) substring(string|binary A, int start, int len) |
从长度为len的起始位置返回A的字节数组的子字符串或切片。例如,substr('foobar',4,1)的结果为'b'(请参阅[ http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr ])。 |
||||||||
string | substring_index(string A, string delim, int count) | 在计数出现定界符delim之前,从字符串A返回子字符串(从Hive 1.3.0开始)。如果count为正,则返回最后定界符左侧的所有内容(从左侧开始计数)。如果count为负,则返回最后定界符右边的所有内容(从右边开始计数)。搜索delim时,Substring_index执行区分大小写的匹配。例如:substring_index('www.apache.org','。',2)='www.apache'。 | ||||||||
string |
translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) |
通过将字符串中存在的字符替换为 从Hive 0.14.0开始添加了对Char / varchar的支持。 |
||||||||
string |
trim(string A) |
返回由A两端的空格修剪产生的字符串。例如,trim('foobar')结果为'foobar' |
||||||||
binary |
unbase64(string str) |
将参数从基数为64的字符串转换为BINARY。(从Hive 0.12.0开始。) |
||||||||
string |
upper(string A) ucase(string A) |
返回将A的所有字符都转换为大写形式的字符串。例如,upper('fOoBaR')的结果为'FOOBAR'。 |
||||||||
string | initcap(string A) | 返回字符串,每个单词的首字母大写,所有其他字母小写。单词由空格分隔。(从Hive 1.1.0开始。) | ||||||||
int | levenshtein(string A, string B) | 返回两个字符串之间的Levenshtein距离(从Hive 1.2.0开始)。例如,levenshtein('kitten','sitting')得出3。 | ||||||||
string | soundex(string A) | 返回字符串的soundex代码(从Hive 1.2.0开始)。例如,soundex('Miller')生成M460。 |
2.7 数据屏蔽功能(Hive支持以下内置数据屏蔽功能)
返回类型 |
函数 |
描述 |
---|---|---|
string |
mask(string str[, string upper[, string lower[, string number]]]) |
返回str的掩码版本(从Hive 2.1.0开始)。默认情况下,大写字母转换为“ X”,小写字母转换为“ x”,数字转换为“ n”。 |
string | mask_first_n(string str[, int n]) |
返回带有str的被屏蔽版本,其中前n个值被屏蔽(从Hive 2.1.0开始)。大写字母转换为“ X”,小写字母转换为“ x”,数字转换为“ n”。 |
string | mask_last_n(string str[, int n]) | 返回带有掩码的最后一个n值的str的掩码版本(从Hive 2.1.0开始)。大写字母转换为“ X”,小写字母转换为“ x”,数字转换为“ n”。 例如,mask_last_n(“ 1234-5678-8765-4321”,4)生成1234-5678-8765-nnnn。 |
string | mask_show_first_n(string str[, int n]) | 返回带掩码的str版本,显示未掩码的前n个字符(从Hive 2.1.0开始)。大写字母转换为“ X”,小写字母转换为“ x”,数字转换为“ n”。 例如,mask_show_first_n(“ 1234-5678-8765-4321”,4)的结果为1234-nnnn-nnnn-nnnn。 |
string | mask_show_last_n(string str[, int n]) | 返回str的掩码版本,显示未掩码的最后n个字符(从Hive 2.1.0开始)。大写字母转换为“ X”,小写字母转换为“ x”,数字转换为“ n”。 例如,mask_show_last_n(“ 1234-5678-8765-4321”,4)的结果为nnnn-nnnn-nnnn-4321。 |
string | mask_hash(string|char|varchar str) | 返回基于str的哈希值(从Hive 2.1.0开始)。哈希是一致的,可用于将跨表的掩码值连接在一起。 对于非字符串类型,此函数返回null。 |
2.8 Misc. 功能
返回类型 |
函数 |
描述 |
---|---|---|
varies |
java_method(class, method[, arg1[, arg2..]]) |
的同义词 |
varies |
reflect(class, method[, arg1[, arg2..]]) |
通过使用反射匹配参数签名来调用Java方法。有关示例,请参见反射(通用)UDF。 |
int |
hash(a1[, a2...]) |
返回参数的哈希值。 |
string | current_user() | 从配置的身份验证器管理器返回当前用户名。可以与连接时提供的用户相同,但是与某些身份验证管理器(例如HadoopDefaultAuthenticator)不同。 |
string | logged_in_user() | 从会话状态返回当前的用户名。这是连接到Hive时提供的用户名。 |
string | current_database() | 返回当前数据库名称。 |
string | md5(string/binary) | 计算字符串或二进制文件的MD5 128位校验和。该值以32个十六进制数字的字符串形式返回,如果参数为NULL,则返回NULL。 示例:md5('ABC')='902fbdd2b1df0c4f70b4a5d23525e932'。 |
string |
sha1(string/binary) sha(string/binary) |
计算字符串或二进制文件的SHA-1摘要,并以十六进制字符串形式返回值。 例如:sha1('ABC')='3c01bdbb26f358bab27f267924aa2c9a03fcfdb8'。 |
bigint | crc32(string/binary) | 计算字符串或二进制参数的循环冗余校验值,并返回bigint值。例如:crc32('ABC')= 2743272264。 |
string | sha2(string/binary, int) |
计算SHA-2系列哈希函数(SHA-224,SHA-256,SHA-384和SHA-512)。 |
binary | aes_encrypt(input string/binary, key string/binary) |
使用AES加密输入。可以使用128、192或256位的密钥长度。 |
binary | aes_decrypt(input binary, key string/binary) | 使用AES解密输入。可以使用128、192或256位的密钥长度。 如果安装了Java密码学扩展(JCE)无限强度管辖权策略文件,则可以使用192位和256位密钥。 如果任一参数为NULL或密钥长度不是允许的值之一,则返回值为NULL。 示例:aes_decrypt(unbase64('y6Ss + zCYObpCbgfWfyNWTw =='),'1234567890123456')='ABC'。 |
string | version() | 返回Hive版本。该字符串包含2个字段,第一个是内部版本号,第二个是内部散列。 示例:“ select version();”可能会返回“ 2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232”。实际结果将取决于您的构建。 |
bigint | surrogate_key([write_id_bits, task_id_bits]) | 在向表中输入数据时自动为行生成数字ID。只能用作酸表或仅插入表的默认值。 |
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对象的字段:
|
3.内置的聚合函数(UDAF)
返回类型 |
函数 |
描述 |
---|---|---|
BIGINT |
count(*), count(expr), count(DISTINCT expr[, expr...]) |
count(*)-返回检索到的行总数,包括包含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) |
返回组中数字列的无偏样本方差。 |
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) |
返回组中列的精确第p 个百分位数(不适用于浮点类型)。p必须在0到1之间。 |
array<double> |
percentile(BIGINT col, array(p1 [, p2]...)) |
返回组中列的精确百分位数p 1,p 2,...(不适用于浮点类型)。p i必须在0到1之间。 |
DOUBLE |
percentile_approx(DOUBLE col, p [, B]) |
返回组中数字列(包括浮点类型)的大约p 个百分位数。B参数控制近似精度,但要以存储为代价。值越高,近似值越好,默认值为10,000。 |
array<double> |
percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B]) |
与上面相同,但是接受并返回一个百分位值数组,而不是单个值。 |
double |
regr_avgx(independent, dependent) |
等效于avg(dependent)。从Hive 2.2.0开始。 |
double |
regr_avgy(independent, dependent) |
等效于avg(独立)。从Hive 2.2.0开始。 |
double |
regr_count(independent, dependent) |
返回用于拟合线性回归线的非空对的数量。从Hive 2.2.0开始。 |
double |
regr_intercept(independent, dependent) |
返回线性回归线的y截距,即等式中的b值=独立* a *独立+ b。从Hive 2.2.0开始。 |
double |
regr_r2(independent, dependent) |
返回回归的确定系数。从Hive 2.2.0开始。 |
double |
regr_slope(independent, dependent) |
返回线性回归线的斜率,即等式= a *独立+ b中a的值。从Hive 2.2.0开始。 |
double |
regr_sxx(independent, dependent) |
等效于regr_count(独立,从属)* var_pop(独立)。从Hive 2.2.0开始。 |
double |
regr_sxy(independent, dependent) |
等效于regr_count(独立,从属)* covar_pop(独立,从属)。从Hive 2.2.0开始。 |
double | regr_syy(independent, dependent) |
等效于regr_count(独立,从属)* var_pop(独立)。从Hive 2.2.0开始。 |
array<struct { |
histogram_numeric(col, b) |
使用b个非均匀间隔的bin计算组中数字列的直方图。输出是大小为b的双值(x,y)坐标数组,这些坐标表示箱的中心和高度 |
array |
collect_set(col) |
返回消除了重复元素的一组对象。 |
array |
collect_list(col) |
返回具有重复项的对象列表。(从Hive 0.13.0开始。) |
INTEGER | ntile(INTEGER x) |
将有序分区划分为 |
4.内置表生成函数(UDTF)
行集列类型 |
函数 |
描述 |
---|---|---|
T |
explode(ARRAY<T> a) |
将数组分解为多行。返回带有单列(col)的行集,该数组代表数组中每个元素的一行。 |
Tkey,Tvalue |
explode(MAP<Tkey,Tvalue> m) |
将地图分解为多行。返回一个行集合与两列(键,值),一个行从输入图中的每个键-值对。(从Hive 0.8.0开始。)。 |
int,T | posexplode(ARRAY<T> a) | 使用附加的int类型位置列将数组分解为多行(原始数组中项的位置,从0开始)。返回具有两列(pos,val)的行集,该数组中的每个元素一行。 |
T1,...,Tn |
inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) |
将结构数组分解为多行。返回具有N列的行集(N =结构中顶级元素的数量),数组中每个结构一行一行。(从Hive0.10开始。) |
T1,...,Tn/r | stack(int r,T1 V1,...,Tn/r Vn) | 将n个值V 1,...,V n分解为r行。每行将有n / r列。r必须是常数。 |
string1,...,stringn |
json_tuple(string jsonStr,string k1,...,string kn) |
接收JSON字符串和一组n个键,并返回n个值的元组。这是 |
string 1,...,stringn |
parse_url_tuple(string urlStr,string p1,...,string pn) |
接受URL字符串和一组n个URL部分,并返回n个值的元组。这类似于 |
使用范例
爆炸(数组)
|
col |
---|
A |
B |
C |
explode (map)
|
key |
value |
---|---|
A | 10 |
B | 20 |
C | 30 |
posexplode(array)
|
pos |
val |
---|---|
0 | A |
1 | B |
2 | C |
inline (array of structs)
|
col1 |
col2 |
col3 |
---|---|---|
A | 10 | 2015-01-01 |
B | 20 | 2016-02-02 |
stack (values)
|
col0 |
col1 |
col2 |
---|---|---|
A | 10 | 2015-01-01 |
B | 20 | 2016-01-01 |
使用语法 "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,也请参见编写 UDTF。
explode
explode()
接受数组(or a map)作为输入,并将array (map)的元素作为单独的行输出。UDTF可以在SELECT表达式列表中使用,也可以作为LATERAL VIEW的一部分使用。
作为explode()
在SELECT表达式列表中使用的示例 ,请考虑一个名为myTable的表,该表具有单列(myCol)和两行:
Array <int> myCol |
---|
[100,200,300] |
[400,500,600] |
然后运行查询:
SELECT explode(myCol) AS myNewCol FROM myTable; |
将产生:
(int)myNewCol |
---|
100 |
200 |
300 |
400 |
500 |
600 |
Maps的用法类似:
SELECT explode(myMap) AS (myMapKey, myMapValue) FROM myMapTable; |
posexplode
Version
自Hive 0.13.0起可用。参见HIVE-4943。
posexplode()
类似于explode
但不只是返回数组的元素,它还返回元素及其在原始数组中的位置。
作为posexplode()
在SELECT表达式列表中使用的示例 ,请考虑一个名为myTable的表,该表具有单列(myCol)和两行:
Array <int> myCol |
---|
[100,200,300] |
[400,500,600] |
然后运行查询:
SELECT posexplode(myCol) AS pos, myNewCol FROM myTable; |
将产生:
(int)pos |
(int)myNewCol |
---|---|
1 |
100 |
2 |
200 |
3 |
300 |
1 |
400 |
2 |
500 |
3 |
600 |
json_tuple
Hive 0.7中引入了新的json_tuple()UDTF。它使用一组名称(键)和一个JSON字符串,并使用一个函数返回值的元组。这比调用GET_JSON_OBJECT从单个JSON字符串中检索多个密钥要有效得多。在任何情况下,单个JSON字符串都会被解析多次,如果您解析一次JSON_TUPLE,查询将更加高效。由于JSON_TUPLE是UDTF,因此您需要使用LATERAL VIEW语法才能实现相同的目标。
例如,
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的多个部分,以元组形式返回数据。可以通过将冒号和键附加到partToExtract参数来提取QUERY中特定键的值,例如parse_url_tuple(''http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY:k1', 'QUERY:k2')返回值为'v1','v2'的元组。这比多次调用parse_url()更有效。所有输入参数和输出列类型都是字符串。
SELECT b.* FROM src LATERAL VIEW parse_url_tuple(fullurl, 'HOST' , 'PATH' , 'QUERY' , 'QUERY:id' ) b as host, path, query, query_id LIMIT 1 ; |
GROUPing and SORTing on f(column)
一个典型的OLAP模式是您有一个timestamp列,并且您希望按每日或其他粒度较小的日期窗口(而不是按秒)进行分组。因此,您可能要select concat(year(dt),month(dt)) and then group on that 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)联系。
实用功能
功能名称 |
返回类型 |
描述 |
跑步
|
---|---|---|---|
version |
String |
提供Hive版本详细信息(软件包内置版本) |
select version(); |
buildversion | String | 版本功能的扩展,其中包括校验和 | select buildversion(); |
UDF内部
UDF的评估方法的上下文是一次一行。像这样的UDF的简单调用
SELECT length(string_col) FROM table_name; |
将评估作业的地图部分中每个string_col值的长度。在地图端评估UDF的副作用是您无法控制发送到映射器的行的顺序。发送到映射器的文件拆分的序列化顺序与此相同。任何reduce边操作(例如SORT BY,ORDER BY,常规JOIN等)都将应用于UDF输出,就好像它只是表的另一列一样。这很好,因为UDF的评估方法的上下文一次只能排成一行。
如果您想控制将哪些行发送到相同的UDF(并可能以什么顺序),您将敦促在简化阶段对UDF进行评估。这可以通过使用DISTRIBUTE BY,DISTRIBUTE BY + SORT BY,CLUSTER BY来实现。查询示例为:
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的更多信息。另外,您可以使用Hive的Transform功能,使用自定义的reduce脚本来完成相同的任务。这两个选项都将在归约方面进行汇总。
创建自定义UDF
有关如何创建自定义UDF的信息,请参见Hive插件 和创建功能。
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;