Hive内置方法大全

一、Aggregate–聚合类
1、avg(col)

–返回某一列的平均值

2、count([DISTINCT] col)

–对某一列计数

3、stddev_pop(col)

–返回某一列的标准偏差

4、stddev_samp(col)

–返回数据列无偏样本的标准偏差

5、sum(col)

6、max(col)

7、min(col)

8、corr(col1, col2)

–返回两列的皮尔逊相关系数

9、covar_pop(col1, col2)

–返回两列数据的总体协方差

10、covar_samp(col1, col2)

–返回两列数据样本的协方差

11、collect_set(col)

–返回一组消除了重复元素的对象,返回的是一个集合

12、collect_list(col)

–返回具有重复项的对象列表

13、histogram_numeric(col, b)

–返回array<struct {‘x’, ‘y’}，b)，使用b个非均匀间隔的箱子计算组内数字列的柱状图（直方图），输出的数组大小为b，double类型的(x,y)表示直方图的中心和高度

14、ntile(INT x)

–该函数将已经排序的分区分到x个桶中，并为每行分配一个桶号。这可以容易的计算三分位，四分位，十分位，百分位和其它通用的概要统计

15、percentile(BIGINT col, p)

–返回组中列的精确第p个百分点（不适用于浮点类型）。 p必须介于0和1之间。注意：真正的百分位数只能用整数值计算。如果您的输入不是整数，请使用PERCENTILE_APPROX。

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

–返回array类型，返回组内某个列精确的第p1，p2，……位百分数，p必须在0和1之间，同上

16、percentile_approx(DOUBLE col, p [, B])

–返回组中数值列（包括浮点类型）的近似pth百分点（或百分点p1，p2，…）。 B参数以内存为代价控制逼近精度。较高的值会产生更好的近似值，默认值为10,000。当col中不同值的数量小于B时，这会得出确切的百分位数值。

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

–返回array，同上，可以返回多个百分点

18、variance(col)

–返回组中数字列的方差

19、var_pop(col)

–返回组中数字列的方差。

20、var_samp(col)

–返回组中数字列的无偏样本方差。

21、regr_avgx(T independent, T dependent)

–计算回归线的自变量(expr2)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr2)。截至Hive 2.2.0。

22、regr_avgy(T independent, T dependent)

–计算回归线的应变量(expr1)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr1)。截至Hive 2.2.0

23、regr_count(T independent, T dependent)

–返回用于拟合线性回归线的非空对数。截至Hive 2.2.0。

24、regr_intercept(T independent, T dependent)

–返回线性回归的直线的y轴截距，即方程中的b值取决于= a *独立+ b。截至Hive 2.2.0。

25、regr_r2(T independent, T dependent)

–返回回归的确定系数。截至Hive 2.2.0。

26、regr_slope(T independent, T dependent)

–返回线性回归线的斜率，即方程式中的a值依赖于=a*独立+ b。截至Hive 2.2.0。

27、regr_sxx(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)

28、regr_sxy(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

29、regr_syy(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)

二、Analytic–分析函数
1、cume_dist()

–计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置

例子：

2、dense_rank() OVER([partition_by_clause] order_by_clause)

–返回从1开始的递增整数序列。输出序列为ORDER BY表达式的重复值生成重复整数。

例：

3、first_value() OVER([partition_by_clause] order_by_clause [window_clause])

–从窗口的第一行返回表达式值。如果输入表达式为NULL，则返回值为NULL。

例：

4、lag(expr [,offset] [,default]) OVER() ([partition_by_clause] order_by_clause)

–该函数使用前一行的列值返回表达式的值。您可以指定一个整数偏移量，它指定一个行位置，位于当前行之前的某些行数。表达式参数中的任何列引用都引用该前一行中的列值。

例：

5、last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause])

–返回窗口中最后一行的表达式值。如果输入表达式为NULL，返回值为NULL。

例：

6、lead(expr[,offset] [,default]) OVER([partition_by_clause] order_by_clause)

–此函数使用以下行的列值返回表达式的值。指定一个整数偏移量，它指定行位置到当前行之后的若干行。表达式参数中的任何列引用都引用来自后面的行的列值。

例：

7、percent_rank()

–按百分比排序

例：

8、rank() OVER([partition_by_clause] order_by_clause)

–返回整数的升序，从1开始。输出序列为表达式的重复值生成重复整数。在为“绑定”输入值生成重复输出值之后，函数通过绑定值的数量来增加序列。

例：

9、row_number() OVER([partition_by_clause] order_by_clause)

–分组排序，同一组内相同值会给不同序号

三、 Collection–集合函数
1、array_contains(Arraya,val)
–如果数组内包含val,返回True
例：
如果fullname这一列不是数组形式，先用array()函数转化下

select array_contains(array(fullname),‘a’) from ods.baseorganize
2、map_keys(Map<K.V> a)

–返回map中的所有key,返回类型array<K.V>

select map_keys(complaintDetail) from tmp.crm_data_customer_complain
3、map_values(Map<K.V> a)

–返回map中的所有values,返回类型array<K.V>

4、size(Map<K.V>|Array a)

–返回映射或数组类型中的元素数目。

5、sort_array(Array a)

–根据数组元素的自然排序将输入数组按升序排序，并返回数组。

四、Complex Type–复合类型
1、array(val1，val2…) --创建具有给定元素的数组
2、create_union(tag,val1,val2,…) --用标记参数指向的值创建一个联合类型
3、map(key1, value1, …) --创建具有给定键/值对的映射。
4、named_struct(name1, val1, …) --用给定的字段名和值创建结构。
注意：这里的name和val只能是给定的值，用已有的列不行

5、struct(val1, val2, …)
–创建具有给定字段值的结构。
这里的val可以是已有的列，也可以是给定的值

五、Conditional–有条件的函数

1、assert_true(BOOLEAN condition)

–如果成立返回NULL，如果不成立报异常

2、coalesce(T v1, T v2, …)

–返回其参数中的第一个非空表达式，当你要在n个字段中选取某一个非空值

例：

3、if()

4、isnotnull(a)

–如果不是NULL返回TRUE,否则返回FALSE

5、isnull(a)

–如果是NULL返回TRUE,否则返回FALSE

6、nullif(a, b)

–如果a=b,返回NULL,否则返回a

7、nvl(T value, T default_value)

–如果value是空，返回default_value，否则返回value

六、Date—日期函数

1、add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)

–返回开始日期之后的num_months,开始日期可以是date型，string型，TIMESTAMP型的，num_months是整形的

2、current_date

–返回当前日期，同一个查询中返回的是同一个日期

3、current_timestamp()

–返回当前的时间戳，同一个查询中返回的是同一个时间戳

4、datediff(STRING enddate, STRING startdate)

–返回结束日期和开始日期之间的间隔天数

5、date_add(DATE startdate, INT days)

–开始日期加几天

6、date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)

–按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

7、date_sub(DATE startdate, INT days)

–开始日期减几天

8、day(STRING date)

–返回一个date或TIMESTAMP的日，例：day(‘1970-11-01 00:00:00’) = 1, day(‘1970-11-01’) = 1

9、dayofmonth(STRING date)

–同上

10、extract(field FROM source)

–返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

支持提取的类型有：day, dayofweek, hour, minute, month, quarter, second, week and year.

11、from_unixtime(BIGINT unixtime [, STRING format])

–将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,“yyyy-MM-dd”) 得到2009-03-12

12、from_utc_timestamp(T a, STRING timezone)

–如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳

13、hour(STRING date)

–返回时间字符串小时

14、last_day(STRING date)

–返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）

15、minute(STRING date)

–返回时间字符串的分钟

16、month(STRING date)

–返回时间字符串的月

17、months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)

–返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 如：months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677 1997-02-28 10:30:00与1996-10-30相差3.94959677个月

18、next_day(STRING start_date, STRING day_of_week)

–返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20

19、quarter(DATE|TIMESTAMP|STRING a)

–返回当前时间属于哪个季度

20、second(STRING date)

–返回时间字符串的秒

21、to_date(STRING timestamp)

–返回字符串的日期部分，结果1970-01-01这种形式

22、to_utc_timestamp(T a, STRING timezone)

–如果给定指定的时区下时间戳，则将其转化成UTC下的时间戳

23、trunc(STRING date, STRING format)

–返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY

24、unix_timestamp([STRING date [, STRING pattern]])

–获取本地时区下的时间戳

25、weekofyear(STRING date)

–返回时间字符串位于一年中的第几个周内 如weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44

26、year(STRING date)

–返回时间字符串的年份部分

七、Mathematical–数学函数

1、abs(DOUBLE a)

–求绝对值

2、acos(DECIMAL|DOUBLE a)

–求反余弦值,a介于-1到1之间，否则返回NULL值

3、asin(DECIMAL|DOUBLE a)

–求反正弦值,a介于-1到1之间，否则返回NULL值

4、atan(DECIMAL|DOUBLE a)

–求反正切值

5、bin(BIGINT a)

–计算二进制a的STRING类型，a为BIGINT类型

6、bround(DOUBLE a [, INT decimals])

–银行家舍入法（1_4：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进）

7、cbrt(DOUBLE a)

–求a的立方根

8、ceil(DOUBLE a)

–求其不小于小给定实数的最小整数如：ceil(6) =ceil(6.9) = 6

9、ceiling(DOUBLE a)

–同上

10、conv(BIGINT|STRING a, INT from_base, INT to_base)

–将GIGINT/STRING类型的num从from_base进制转换成to_base进制

11、cos(DECIMAL|DOUBLE a)

–求余弦值

12、degrees(DECIMAL|DOUBLE a)

–将弧度值转换角度值

13、e()

–返回常数e

14、exp(DECIMAL|DOUBLE a)

–返回e的a幂次方， a可为小数

15、factorial(INT a)

–求a的阶乘

16、floor(DOUBLE a)

–向下取整,最数轴上最接近要求的值的左边的值 6.10->6 -3.4->-4

17、greatest(T a1, T a2, …)

–求最大值

18、hex(BIGINT|BINARY|STRING a)

–计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制

19、least(T a1, T a2, …)

–求最小值,返回值列表的最小值。当一个或多个参数为NULL时固定为返回null，并且严格的类型限制放宽，与“<”运算符一致。

20、ln(DECIMAL|DOUBLE a)

–返回a的自然对数

21、log(DECIMAL|DOUBLE base, DECIMAL|DOUBLE a)

–以base为底的对数，base 与 a都是DOUBLE类型

22、log10(DECIMAL|DOUBLE a)

–返回以10为底a的对数

23、log2(DECIMAL|DOUBLE a)

–返回以2为底a的对数

24、negative(T<DOUBLE|INT> a)

–返回-a

25、pi()

–返回π

26、pmod(T<DOUBLE|INT> a, T b)

–a对b取模

27、positive(T<DOUBLE|INT> a)

–返回a，觉得这个函数完全没有意义

28、pow(DOUBLE a, DOUBLE p)

–返回a的p次方

29、pow(DOUBLE a, DOUBLE p)

–返回a的p次幂，同上

30、radians(DECIMAL|DOUBLE a)

–将角度值转换成弧度值

31、rand([INT seed])

–每行返回一个DOUBLE型随机数,seed是随机因子

32、round(DOUBLE a [, INT d])

–返回对a四舍五入的BIGINT值

33、shiftleft(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–按位左移

34、shiftright(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–按位右移

35、shiftrightunsigned(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–无符号按位右移（<<<）

36、sign(T<DOUBLE|INT> a)

–如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0

37、sin(DECIMAL|DOUBLE a)

–返回a的正弦

38、sqrt(DECIMAL|DOUBLE a)

–返回a的平方根

39、tan(DECIMAL|DOUBLE a)

–返回a的正切值

40、unhex(STRING a)

–hex的逆方法

41、width_bucket(NUMBER expr, NUMBER min_value, NUMBER max_value, INT num_buckets)

–通过将expr映射到第i个相同大小的存储桶中，返回0到num_buckets + 1之间的整数。通过将[min_value，max_value]分成相同大小的区域来制作桶。如果expr <min_value，则返回1，如果expr> max_value返回num_buckets + 1。 （从Hive 3.0.0开始）

八、Misc–各种各样的类型

1、aes_decrypt(BINARY input, STRING|BINARY key)

–使用AES解密输入（从Hive 1.3.0开始）。可以使用128位，192位或256位的密钥长度。如果安装了Java加密扩展（JCE）无限强度管辖策略文件，则可以使用192位和256位密钥。如果任一参数为NULL或者密钥长度不是允许值之一，则返回值为NULL。例如：aes_decrypt（unbase64（‘y6Ss + zCYObpCbgfWfyNWTw ==’），‘1234567890123456’）=‘ABC’。

2、aes_encrypt(STRING|BINARY input, STRING|BINARY key)

–使用AES加密输入（自Hive 1.3.0起）。可以使用128位，192位或256位的密钥长度。如果安装了Java加密扩展（JCE）无限强度管辖策略文件，则可以使用192位和256位密钥。如果任一参数为NULL或者密钥长度不是允许值之一，则返回值为NULL。例如：base64（aes_encrypt（‘ABC’，‘1234567890123456’））=‘y6Ss + zCYObpCbgfWfyNWTw ==’。

3、crc32(STRING|BINARY a)

–计算字符串或二进制参数的循环冗余校验值并返回bigint值（从Hive 1.3.0开始）。例如：crc32（‘ABC’）= 2743272264。

4、current_database() --返回当前的数据库名称

5、current_user() --返回当前的用户名称

6、get_json_object(STRING json, STRING jsonPath)

–从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制

7、hash(a1[, a2…]) --返回参数的哈希值

8、java_method(class, method[, arg1[, arg2…]]) --所谓的匹配参数签名java方法，利用反射。

9、md5(STRING|BINARY a)

–计算字符串或二进制的MD5 128位校验和（自Hive 1.3.0起）。该值作为32位十六进制数字的字符串返回，如果参数为NULL，则返回NULL。例如：md5（‘ABC’）=‘902fbdd2b1df0c4f70b4a5d23525e932’。

10、reflect(class, method[, arg1[, arg2…]]) --匹配参数签名java方法，利用反射。

11、sha(STRING|BINARY a)

–计算字符串或二进制文件的SHA-1摘要，并以十六进制字符串形式返回值（自Hive 1.3.0开始）。例如：sha1（‘ABC’）=‘3c01bdbb26f358bab27f267924aa2c9a03f’

12、sha1(STRING|BINARY a) --同上

13、sha2(STRING|BINARY a, INT b)

–计算SHA-2系列散列函数（SHA-224，SHA-256，SHA-384和SHA-512）（自Hive 1.3.0起）。第一个参数是要散列的字符串或二进制文件。第二个参数表示结果的所需位长度，它的值必须是224,256,384,512或0（相当于256）。从Java 8开始支持SHA-224。如果任一参数为NULL或哈希长度不是允许值之一，则返回值为NULL。例如：sha2（‘ABC’，256）=‘b5d4045c3f466fa91fe2cc6abe79232a1a’

14、version()

–返回hive的使用版本,

–字符串包含2个字段，第一个是生成号，第二个是生成哈希。示例：“"select version();可能返回:2.1.0.2.5.0.0.1245 R027 59B9C5CE1A3D7D6B6D2E6DE23 78FB0C9322。实际结果将取决于您的构建。

15、array xpath(STRING xml, STRING xpath)

–xpath UDF家族是JDK提供的Java XPath库javax.xml.xpath的包装器。该库基于XPath 1.0规范。

16、xpath_boolean(STRING xml, STRING xpath)

–16-22都是这个意思

–UDF的XPath的家庭在java XPath库包装javax.xml.xpath通过JDK提供。该库是基于XPath 1规范的。

17、xpath_float(STRING xml, STRING xpath)

18、xpath_int(STRING xml, STRING xpath)

19、xpath_long(STRING xml, STRING xpath)

20、xpath_number(STRING xml, STRING xpath)

21、xpath_short(STRING xml, STRING xpath)

22、xpath_string(STRING xml, STRING xpath)

九、String–字符串

1、ascii(STRING str)

–返回str中首个ASCII字符串的整数值

2、base64(BINARY bin)

–将二进制bin转换成64位的字符串

3、chr(BIGINT|DOUBLE a)

–返回具有与a等价的二进制字符(Hive 1.3.0到2.1.0)。如果a大于256，则结果等于chr(% 256)。例如:选择对应(88);返回“X”。

4、char_length(STRING a)

–返回str中包含的UTF-8字符的数量(从Hive 2.2.0开始)。这是字符长度的简写。

5、character_length(STRING a)

–返回str中包含的UTF-8字符的数量(从Hive 2.2.0开始)。函数char_length是这个函数的简写。

6、concat(STRING|BINARY a, STRING|BINARY b…)

–返回连接作为参数传入的字符串或字节所产生的字符串或字节。例如，concat(“foo”、“bar”)会产生“foobar”。注意，这个函数可以接受任意数量的输入字符串。

7、concat_ws(STRING sep, STRING a, STRING b…), concat_ws(STRING sep, Array)

–类似于concat()，但是使用自定义分隔符SEP。

8、context_ngrams(Array<Array>, Array, INT k, INT pf)

–返回array<struct<STRING,DOUBLE>>

–与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂)（https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining）

– Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of “context”.

9、decode(BINARY bin, STRING charset)

–使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL

10、elt(INT n, STRING str, STRING str1, …])

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

11、encode(STRING src, STRING charset)

–使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL

12、field(T val, T val1, …])

–…列表或0如果没有找到，返回val1、val2、val3中的val索引。例如字段(‘world’，‘say’，‘hello’，‘world’)返回3。支持所有原始类型，使用string .equals(x)对参数进行比较。如果val是NULL，返回值为0。

–Returns the index of val in the val1,val2,val3,… list or 0 if not found. For example field(‘world’,‘say’,‘hello’,‘world’) returns 3. All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0.

13、find_in_set(STRING str, STRING strList)

–返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL,例如，find_in_set(‘ab’， ‘abc,b,ab,c,def’)返回3。

14、format_number(NUMBER x, INT d)

–将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数

15、format_number(NUMBER x, INT d)

–从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制

16、initcap(STRING a)

–将字符串A转换第一个字母大写其余字母小写的字符串（hive1.1）

17、instr(STRING str, STRING substr)

–查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的

18、in_file(STRING str, STRING filename)

–如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true

19、length(STRING a)

–返回字符串的长度

20、levenshtein(STRING a, STRING b)

–计算两个字符串之间的差异大小 如：levenshtein(‘kitten’, ‘sitting’) = 3

21、lcase(STRING a)

22、lower(STRING a)

–21、22同一个意思，将字符串A的所有字母转换成小写字母

23、locate(STRING substr, STRING str [, INT pos])

–查找字符串str中的pos位置后字符串substr第一次出现的位置

24、lpad(STRING str, INT len, STRING pad)

–从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分

25、ltrim(STRING a)

–去掉字符串A前面的空格

26、 ngrams(Array<Array> a, INT n, INT k, INT pf)

–返回array<struct<STRING, DOUBLE>>，返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining)

27、octet_length(STRING a)

–返回在UTF-8编码中保存字符串str所需的八进制数(从Hive 2.2.0开始)。注意，octet_length(str)可以大于character_length(str)。

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

–返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) =‘facebook.com’，如果参数partToExtract值为QUERY则必须指定第三个参数key 如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) =‘v1’

–Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) returns ‘facebook.com’. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) returns ‘v1’.

29、printf(STRING format, Obj… args)

–按照printf风格格式输出字符串

30、regexp_extract(string subject, string pattern, int index)

–抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格

–Returns the string extracted using the pattern. For example, regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\s’ is necessary to match whitespace, etc. The ‘index’ parameter is the Java regex Matcher group() method index.

31、 regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

–按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, “oo|ar”, “”) = ‘fb.’ 注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格

–Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace(“foobar”, “oo|ar”, “”) returns ‘fb.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\s’ is necessary to match whitespace, etc.

32、repeat(STRING str, INT n)

–重复输出n遍字符串str

33、replace(STRING a, STRING old, STRING new)

–返回字符串a，其中所有未重叠的旧替换为新的(如Hive 1.3.0和2.1.0)。示例:选择replace(“ababab”,“abab”,“Z”);返回“Zab”。

34、reverse(STRING a)

–反转字符串

35、rpad(STRING str, INT len, STRING pad)

–从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分

36、rtrim(STRING a)

–去掉字符串后面出现的空格

37、 sentences(string str, string lang, string locale)

–返回array<array>类型，字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) )

38、soundex(STRING a)

–将普通字符串转换成soundex字符串(hive1.2)

39、space(INT n)

–返回n个空格

40、split(STRING str, STRING pat)

–返回array 在pat周围分割str (pat) (pat是一个正则表达式)。

41、str_to_map(text[, delimiter1, delimiter2])

–返回map<string,string>，将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="

–Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ‘,’ for delimiter1 and ‘=’ for delimiter2.

42、substr(STRING|BINARY A, INT start [, INT len])

–对于字符串A,从start位置开始截取字符串并返回，例如，substr(‘foobar’， 4)=‘bar’

43、substring(STRING|BINARY a, INT start [, INT len])

–同上

44、substring_index(string A, string delim, int count)

–在计数分隔符delim之前返回字符串A的子串（自Hive 1.3.0开始）。如果count是正数，则返回最后一个分隔符左边的数字（从左数起）。如果count为负数，则返回最后一个分隔符右边的数字（从右数起）。 Substring_index在搜索delim时执行区分大小写的匹配

–Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’.

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

–将input出现在from中的字符串替换成to中的字符串 如：translate(“MOBIN”,“BIN”,“M”)=“MOM”

46、trim(STRING a)

–去掉字符串A前面的空格

47、ucase(STRING a)

48、upper(STRING a)

–将字符串A中的字母转换成大写字母

49、unbase64(STRING a)

–将64位的字符串转换二进制值

十、Data Masking–数据屏蔽

1、mask(STRING str [, STRING upper [, STRING lower [, STRING number]]])

–返回一个隐藏的str版本(Hive 2.1.0)。默认情况下，大写字母转换为“X”，小写字母转换为“X”，数字转换为“n”。例如，mask(“abcd-EFGH-8765-4321”)返回xxxx- xxxx- nnn-nnnn。您可以通过提供额外的参数来覆盖掩码中使用的字符:第二个参数控制大写字母的掩码字符，小写字母的第三个参数和数字的第四个参数。例如,面具(“abcd - efgh - 8765 - 8765”,“U”、“l”、“#”)返回llll-UUUU - # # # # # # # #。

–Returns a masked version of str (as of Hive 2.1.0). By default, upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example mask(“abcd-EFGH-8765-4321”) results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask(“abcd-EFGH-8765-4321”, “U”, “l”, “#”) results in llll-UUUU-####-####.

2、mask_first_n(STRING str [, INT n])

–返回带有第一个n值的屏蔽版本的str(如Hive 2.1.0)。大写字母被转换为“X”，小写字母被转换为“X”，数字被转换为“n”。例如，mask_first_n(“1234-5678-8765-4321”，4)结果为nnnn-5678-8765-4321。

–Returns a masked version of str with the first n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_first_n(“1234-5678-8765-4321”, 4) results in nnnn-5678-8765-4321.

3、mask_last_n(STRING str [, INT n])

–返回带有最后n个值(如Hive 2.1.0)的屏蔽版本的str。大写字母被转换为“X”，小写字母被转换为“X”，数字被转换为“n”。例如，mask_last_n(“1234-5678-8765-4321”，4)结果是1234-5678-8765-nnnn。

–Returns a masked version of str with the last n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_last_n(“1234-5678-8765-4321”, 4) results in 1234-5678-8765-nnnn.

4、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。

–Returns a masked version of str, showing the first n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_first_n(“1234-5678-8765-4321”, 4) results in 1234-nnnn-nnnn-nnnn.

5、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-nnn -nn -4321。

–Returns a masked version of str, showing the last n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_last_n(“1234-5678-8765-4321”, 4) results in nnnn-nnnn-nnnn-4321.

6、mask_hash(STRING|CHAR|VARCHAR str)

–返回基于str的散列值(如Hive 2.1.0)。哈希是一致的，可以用来在表之间连接蒙面值。对于非字符串类型，此函数返回null。

–Returns a hashed value based on str (as of Hive 2.1.0). The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types.

十一、Table Generating–表格生成

1、explode(array a)

–对于a中的每个元素，将生成一行且包含该元素

2、explode(ARRAY)

–每行对应数组中的一个元素

3、explode(MAP)

–每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值

4、inline(Array<Struct [, Struct]> a)

–将结构体数组提取出来并插入到表中

5、json_tuple(STRING jsonStr, STRING k1, STRING k2, …)

–从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

–The parse_url_tuple() UDTF is similar to parse_url(), but can extract multiple parts of a given URL,

returning the data in a tuple. Values for a particular key in QUERY can be extracted by appending a colon and the key to the partToExtract argument.

6、posexplode(ARRAY)

–与explode类似，不同的是还返回各元素在数组中的位置

–posexplode() is similar to explode but instead of just returning the elements of the array it returns the element as well as its position in the original array.

7、stack(INT n, v1, v2, …, vk)

–把M列转换成N行，每行有M/N个字段，其中n必须是个常数

十二、Type Conversion–类型转化

1、binary(BINARY|STRING a) --将输入的值转化为二进制

2、cast(a as T) --格式转化，如果转化不成功会返回NULL值
————————————————
版权声明：本文为CSDN博主「huobumingbai1234」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/huobumingbai1234/article/details/80348305
一、Aggregate–聚合类
1、avg(col)

–返回某一列的平均值

2、count([DISTINCT] col)

–对某一列计数

3、stddev_pop(col)

–返回某一列的标准偏差

4、stddev_samp(col)

–返回数据列无偏样本的标准偏差

5、sum(col)

6、max(col)

7、min(col)

8、corr(col1, col2)

–返回两列的皮尔逊相关系数

9、covar_pop(col1, col2)

–返回两列数据的总体协方差

10、covar_samp(col1, col2)

–返回两列数据样本的协方差

11、collect_set(col)

–返回一组消除了重复元素的对象,返回的是一个集合

12、collect_list(col)

–返回具有重复项的对象列表

13、histogram_numeric(col, b)

–返回array<struct {‘x’, ‘y’}，b)，使用b个非均匀间隔的箱子计算组内数字列的柱状图（直方图），输出的数组大小为b，double类型的(x,y)表示直方图的中心和高度

14、ntile(INT x)

–该函数将已经排序的分区分到x个桶中，并为每行分配一个桶号。这可以容易的计算三分位，四分位，十分位，百分位和其它通用的概要统计

15、percentile(BIGINT col, p)

–返回组中列的精确第p个百分点（不适用于浮点类型）。 p必须介于0和1之间。注意：真正的百分位数只能用整数值计算。如果您的输入不是整数，请使用PERCENTILE_APPROX。

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

–返回array类型，返回组内某个列精确的第p1，p2，……位百分数，p必须在0和1之间，同上

16、percentile_approx(DOUBLE col, p [, B])

–返回组中数值列（包括浮点类型）的近似pth百分点（或百分点p1，p2，…）。 B参数以内存为代价控制逼近精度。较高的值会产生更好的近似值，默认值为10,000。当col中不同值的数量小于B时，这会得出确切的百分位数值。

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

–返回array，同上，可以返回多个百分点

18、variance(col)

–返回组中数字列的方差

19、var_pop(col)

–返回组中数字列的方差。

20、var_samp(col)

–返回组中数字列的无偏样本方差。

21、regr_avgx(T independent, T dependent)

–计算回归线的自变量(expr2)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr2)。截至Hive 2.2.0。

22、regr_avgy(T independent, T dependent)

–计算回归线的应变量(expr1)的平均值，去掉了空对(expr1, expr2)后，等于AVG(expr1)。截至Hive 2.2.0

23、regr_count(T independent, T dependent)

–返回用于拟合线性回归线的非空对数。截至Hive 2.2.0。

24、regr_intercept(T independent, T dependent)

–返回线性回归的直线的y轴截距，即方程中的b值取决于= a *独立+ b。截至Hive 2.2.0。

25、regr_r2(T independent, T dependent)

–返回回归的确定系数。截至Hive 2.2.0。

26、regr_slope(T independent, T dependent)

–返回线性回归线的斜率，即方程式中的a值依赖于=a*独立+ b。截至Hive 2.2.0。

27、regr_sxx(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)

28、regr_sxy(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

29、regr_syy(T independent, T dependent)

–返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)

二、Analytic–分析函数
1、cume_dist()

–计算一行在组中的相对位置，CUME_DIST总是返回大于0、小于或等于1的数，该数表示该行在N行中的位置

例子：

2、dense_rank() OVER([partition_by_clause] order_by_clause)

–返回从1开始的递增整数序列。输出序列为ORDER BY表达式的重复值生成重复整数。

例：

3、first_value() OVER([partition_by_clause] order_by_clause [window_clause])

–从窗口的第一行返回表达式值。如果输入表达式为NULL，则返回值为NULL。

例：

4、lag(expr [,offset] [,default]) OVER() ([partition_by_clause] order_by_clause)

–该函数使用前一行的列值返回表达式的值。您可以指定一个整数偏移量，它指定一个行位置，位于当前行之前的某些行数。表达式参数中的任何列引用都引用该前一行中的列值。

例：

5、last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause])

–返回窗口中最后一行的表达式值。如果输入表达式为NULL，返回值为NULL。

例：

6、lead(expr[,offset] [,default]) OVER([partition_by_clause] order_by_clause)

–此函数使用以下行的列值返回表达式的值。指定一个整数偏移量，它指定行位置到当前行之后的若干行。表达式参数中的任何列引用都引用来自后面的行的列值。

例：

7、percent_rank()

–按百分比排序

例：

8、rank() OVER([partition_by_clause] order_by_clause)

–返回整数的升序，从1开始。输出序列为表达式的重复值生成重复整数。在为“绑定”输入值生成重复输出值之后，函数通过绑定值的数量来增加序列。

例：

9、row_number() OVER([partition_by_clause] order_by_clause)

–分组排序，同一组内相同值会给不同序号

三、 Collection–集合函数
1、array_contains(Arraya,val)
–如果数组内包含val,返回True
例：
如果fullname这一列不是数组形式，先用array()函数转化下

select array_contains(array(fullname),‘a’) from ods.baseorganize
2、map_keys(Map<K.V> a)

–返回map中的所有key,返回类型array<K.V>

select map_keys(complaintDetail) from tmp.crm_data_customer_complain
3、map_values(Map<K.V> a)

–返回map中的所有values,返回类型array<K.V>

4、size(Map<K.V>|Array a)

–返回映射或数组类型中的元素数目。

5、sort_array(Array a)

–根据数组元素的自然排序将输入数组按升序排序，并返回数组。

四、Complex Type–复合类型
1、array(val1，val2…) --创建具有给定元素的数组
2、create_union(tag,val1,val2,…) --用标记参数指向的值创建一个联合类型
3、map(key1, value1, …) --创建具有给定键/值对的映射。
4、named_struct(name1, val1, …) --用给定的字段名和值创建结构。
注意：这里的name和val只能是给定的值，用已有的列不行

5、struct(val1, val2, …)
–创建具有给定字段值的结构。
这里的val可以是已有的列，也可以是给定的值

五、Conditional–有条件的函数

1、assert_true(BOOLEAN condition)

–如果成立返回NULL，如果不成立报异常

2、coalesce(T v1, T v2, …)

–返回其参数中的第一个非空表达式，当你要在n个字段中选取某一个非空值

例：

3、if()

4、isnotnull(a)

–如果不是NULL返回TRUE,否则返回FALSE

5、isnull(a)

–如果是NULL返回TRUE,否则返回FALSE

6、nullif(a, b)

–如果a=b,返回NULL,否则返回a

7、nvl(T value, T default_value)

–如果value是空，返回default_value，否则返回value

六、Date—日期函数

1、add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)

–返回开始日期之后的num_months,开始日期可以是date型，string型，TIMESTAMP型的，num_months是整形的

2、current_date

–返回当前日期，同一个查询中返回的是同一个日期

3、current_timestamp()

–返回当前的时间戳，同一个查询中返回的是同一个时间戳

4、datediff(STRING enddate, STRING startdate)

–返回结束日期和开始日期之间的间隔天数

5、date_add(DATE startdate, INT days)

–开始日期加几天

6、date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)

–按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

7、date_sub(DATE startdate, INT days)

–开始日期减几天

8、day(STRING date)

–返回一个date或TIMESTAMP的日，例：day(‘1970-11-01 00:00:00’) = 1, day(‘1970-11-01’) = 1

9、dayofmonth(STRING date)

–同上

10、extract(field FROM source)

–返回日期/时间的单独部分，比如年、月、日、小时、分钟等等

支持提取的类型有：day, dayofweek, hour, minute, month, quarter, second, week and year.

11、from_unixtime(BIGINT unixtime [, STRING format])

–将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,“yyyy-MM-dd”) 得到2009-03-12

12、from_utc_timestamp(T a, STRING timezone)

–如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳

13、hour(STRING date)

–返回时间字符串小时

14、last_day(STRING date)

–返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）

15、minute(STRING date)

–返回时间字符串的分钟

16、month(STRING date)

–返回时间字符串的月

17、months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)

–返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0 如：months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677 1997-02-28 10:30:00与1996-10-30相差3.94959677个月

18、next_day(STRING start_date, STRING day_of_week)

–返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20

19、quarter(DATE|TIMESTAMP|STRING a)

–返回当前时间属于哪个季度

20、second(STRING date)

–返回时间字符串的秒

21、to_date(STRING timestamp)

–返回字符串的日期部分，结果1970-01-01这种形式

22、to_utc_timestamp(T a, STRING timezone)

–如果给定指定的时区下时间戳，则将其转化成UTC下的时间戳

23、trunc(STRING date, STRING format)

–返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY

24、unix_timestamp([STRING date [, STRING pattern]])

–获取本地时区下的时间戳

25、weekofyear(STRING date)

–返回时间字符串位于一年中的第几个周内 如weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44

26、year(STRING date)

–返回时间字符串的年份部分

七、Mathematical–数学函数

1、abs(DOUBLE a)

–求绝对值

2、acos(DECIMAL|DOUBLE a)

–求反余弦值,a介于-1到1之间，否则返回NULL值

3、asin(DECIMAL|DOUBLE a)

–求反正弦值,a介于-1到1之间，否则返回NULL值

4、atan(DECIMAL|DOUBLE a)

–求反正切值

5、bin(BIGINT a)

–计算二进制a的STRING类型，a为BIGINT类型

6、bround(DOUBLE a [, INT decimals])

–银行家舍入法（1_4：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进）

7、cbrt(DOUBLE a)

–求a的立方根

8、ceil(DOUBLE a)

–求其不小于小给定实数的最小整数如：ceil(6) =ceil(6.9) = 6

9、ceiling(DOUBLE a)

–同上

10、conv(BIGINT|STRING a, INT from_base, INT to_base)

–将GIGINT/STRING类型的num从from_base进制转换成to_base进制

11、cos(DECIMAL|DOUBLE a)

–求余弦值

12、degrees(DECIMAL|DOUBLE a)

–将弧度值转换角度值

13、e()

–返回常数e

14、exp(DECIMAL|DOUBLE a)

–返回e的a幂次方， a可为小数

15、factorial(INT a)

–求a的阶乘

16、floor(DOUBLE a)

–向下取整,最数轴上最接近要求的值的左边的值 6.10->6 -3.4->-4

17、greatest(T a1, T a2, …)

–求最大值

18、hex(BIGINT|BINARY|STRING a)

–计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制

19、least(T a1, T a2, …)

–求最小值,返回值列表的最小值。当一个或多个参数为NULL时固定为返回null，并且严格的类型限制放宽，与“<”运算符一致。

20、ln(DECIMAL|DOUBLE a)

–返回a的自然对数

21、log(DECIMAL|DOUBLE base, DECIMAL|DOUBLE a)

–以base为底的对数，base 与 a都是DOUBLE类型

22、log10(DECIMAL|DOUBLE a)

–返回以10为底a的对数

23、log2(DECIMAL|DOUBLE a)

–返回以2为底a的对数

24、negative(T<DOUBLE|INT> a)

–返回-a

25、pi()

–返回π

26、pmod(T<DOUBLE|INT> a, T b)

–a对b取模

27、positive(T<DOUBLE|INT> a)

–返回a，觉得这个函数完全没有意义

28、pow(DOUBLE a, DOUBLE p)

–返回a的p次方

29、pow(DOUBLE a, DOUBLE p)

–返回a的p次幂，同上

30、radians(DECIMAL|DOUBLE a)

–将角度值转换成弧度值

31、rand([INT seed])

–每行返回一个DOUBLE型随机数,seed是随机因子

32、round(DOUBLE a [, INT d])

–返回对a四舍五入的BIGINT值

33、shiftleft(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–按位左移

34、shiftright(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–按位右移

35、shiftrightunsigned(T<BIGINT|INT|SMALLINT|TINYINT> a, INT b)

–无符号按位右移（<<<）

36、sign(T<DOUBLE|INT> a)

–如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0

37、sin(DECIMAL|DOUBLE a)

–返回a的正弦

38、sqrt(DECIMAL|DOUBLE a)

–返回a的平方根

39、tan(DECIMAL|DOUBLE a)

–返回a的正切值

40、unhex(STRING a)

–hex的逆方法

41、width_bucket(NUMBER expr, NUMBER min_value, NUMBER max_value, INT num_buckets)

–通过将expr映射到第i个相同大小的存储桶中，返回0到num_buckets + 1之间的整数。通过将[min_value，max_value]分成相同大小的区域来制作桶。如果expr <min_value，则返回1，如果expr> max_value返回num_buckets + 1。 （从Hive 3.0.0开始）

八、Misc–各种各样的类型

1、aes_decrypt(BINARY input, STRING|BINARY key)

–使用AES解密输入（从Hive 1.3.0开始）。可以使用128位，192位或256位的密钥长度。如果安装了Java加密扩展（JCE）无限强度管辖策略文件，则可以使用192位和256位密钥。如果任一参数为NULL或者密钥长度不是允许值之一，则返回值为NULL。例如：aes_decrypt（unbase64（‘y6Ss + zCYObpCbgfWfyNWTw ==’），‘1234567890123456’）=‘ABC’。

2、aes_encrypt(STRING|BINARY input, STRING|BINARY key)

–使用AES加密输入（自Hive 1.3.0起）。可以使用128位，192位或256位的密钥长度。如果安装了Java加密扩展（JCE）无限强度管辖策略文件，则可以使用192位和256位密钥。如果任一参数为NULL或者密钥长度不是允许值之一，则返回值为NULL。例如：base64（aes_encrypt（‘ABC’，‘1234567890123456’））=‘y6Ss + zCYObpCbgfWfyNWTw ==’。

3、crc32(STRING|BINARY a)

–计算字符串或二进制参数的循环冗余校验值并返回bigint值（从Hive 1.3.0开始）。例如：crc32（‘ABC’）= 2743272264。

4、current_database() --返回当前的数据库名称

5、current_user() --返回当前的用户名称

6、get_json_object(STRING json, STRING jsonPath)

–从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制

7、hash(a1[, a2…]) --返回参数的哈希值

8、java_method(class, method[, arg1[, arg2…]]) --所谓的匹配参数签名java方法，利用反射。

9、md5(STRING|BINARY a)

–计算字符串或二进制的MD5 128位校验和（自Hive 1.3.0起）。该值作为32位十六进制数字的字符串返回，如果参数为NULL，则返回NULL。例如：md5（‘ABC’）=‘902fbdd2b1df0c4f70b4a5d23525e932’。

10、reflect(class, method[, arg1[, arg2…]]) --匹配参数签名java方法，利用反射。

11、sha(STRING|BINARY a)

–计算字符串或二进制文件的SHA-1摘要，并以十六进制字符串形式返回值（自Hive 1.3.0开始）。例如：sha1（‘ABC’）=‘3c01bdbb26f358bab27f267924aa2c9a03f’

12、sha1(STRING|BINARY a) --同上

13、sha2(STRING|BINARY a, INT b)

–计算SHA-2系列散列函数（SHA-224，SHA-256，SHA-384和SHA-512）（自Hive 1.3.0起）。第一个参数是要散列的字符串或二进制文件。第二个参数表示结果的所需位长度，它的值必须是224,256,384,512或0（相当于256）。从Java 8开始支持SHA-224。如果任一参数为NULL或哈希长度不是允许值之一，则返回值为NULL。例如：sha2（‘ABC’，256）=‘b5d4045c3f466fa91fe2cc6abe79232a1a’

14、version()

–返回hive的使用版本,

–字符串包含2个字段，第一个是生成号，第二个是生成哈希。示例：“"select version();可能返回:2.1.0.2.5.0.0.1245 R027 59B9C5CE1A3D7D6B6D2E6DE23 78FB0C9322。实际结果将取决于您的构建。

15、array xpath(STRING xml, STRING xpath)

–xpath UDF家族是JDK提供的Java XPath库javax.xml.xpath的包装器。该库基于XPath 1.0规范。

16、xpath_boolean(STRING xml, STRING xpath)

–16-22都是这个意思

–UDF的XPath的家庭在java XPath库包装javax.xml.xpath通过JDK提供。该库是基于XPath 1规范的。

17、xpath_float(STRING xml, STRING xpath)

18、xpath_int(STRING xml, STRING xpath)

19、xpath_long(STRING xml, STRING xpath)

20、xpath_number(STRING xml, STRING xpath)

21、xpath_short(STRING xml, STRING xpath)

22、xpath_string(STRING xml, STRING xpath)

九、String–字符串

1、ascii(STRING str)

–返回str中首个ASCII字符串的整数值

2、base64(BINARY bin)

–将二进制bin转换成64位的字符串

3、chr(BIGINT|DOUBLE a)

–返回具有与a等价的二进制字符(Hive 1.3.0到2.1.0)。如果a大于256，则结果等于chr(% 256)。例如:选择对应(88);返回“X”。

4、char_length(STRING a)

–返回str中包含的UTF-8字符的数量(从Hive 2.2.0开始)。这是字符长度的简写。

5、character_length(STRING a)

–返回str中包含的UTF-8字符的数量(从Hive 2.2.0开始)。函数char_length是这个函数的简写。

6、concat(STRING|BINARY a, STRING|BINARY b…)

–返回连接作为参数传入的字符串或字节所产生的字符串或字节。例如，concat(“foo”、“bar”)会产生“foobar”。注意，这个函数可以接受任意数量的输入字符串。

7、concat_ws(STRING sep, STRING a, STRING b…), concat_ws(STRING sep, Array)

–类似于concat()，但是使用自定义分隔符SEP。

8、context_ngrams(Array<Array>, Array, INT k, INT pf)

–返回array<struct<STRING,DOUBLE>>

–与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂)（https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining）

– Returns the top-k contextual N-grams from a set of tokenized sentences, given a string of “context”.

9、decode(BINARY bin, STRING charset)

–使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL

10、elt(INT n, STRING str, STRING str1, …])

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

11、encode(STRING src, STRING charset)

–使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL

12、field(T val, T val1, …])

–…列表或0如果没有找到，返回val1、val2、val3中的val索引。例如字段(‘world’，‘say’，‘hello’，‘world’)返回3。支持所有原始类型，使用string .equals(x)对参数进行比较。如果val是NULL，返回值为0。

–Returns the index of val in the val1,val2,val3,… list or 0 if not found. For example field(‘world’,‘say’,‘hello’,‘world’) returns 3. All primitive types are supported, arguments are compared using str.equals(x). If val is NULL, the return value is 0.

13、find_in_set(STRING str, STRING strList)

–返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL,例如，find_in_set(‘ab’， ‘abc,b,ab,c,def’)返回3。

14、format_number(NUMBER x, INT d)

–将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数

15、format_number(NUMBER x, INT d)

–从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制

16、initcap(STRING a)

–将字符串A转换第一个字母大写其余字母小写的字符串（hive1.1）

17、instr(STRING str, STRING substr)

–查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的

18、in_file(STRING str, STRING filename)

–如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true

19、length(STRING a)

–返回字符串的长度

20、levenshtein(STRING a, STRING b)

–计算两个字符串之间的差异大小 如：levenshtein(‘kitten’, ‘sitting’) = 3

21、lcase(STRING a)

22、lower(STRING a)

–21、22同一个意思，将字符串A的所有字母转换成小写字母

23、locate(STRING substr, STRING str [, INT pos])

–查找字符串str中的pos位置后字符串substr第一次出现的位置

24、lpad(STRING str, INT len, STRING pad)

–从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分

25、ltrim(STRING a)

–去掉字符串A前面的空格

26、 ngrams(Array<Array> a, INT n, INT k, INT pf)

–返回array<struct<STRING, DOUBLE>>，返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (https://cwiki.apache.org/confluence/display/Hive/StatisticsAndDataMining)

27、octet_length(STRING a)

–返回在UTF-8编码中保存字符串str所需的八进制数(从Hive 2.2.0开始)。注意，octet_length(str)可以大于character_length(str)。

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

–返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) =‘facebook.com’，如果参数partToExtract值为QUERY则必须指定第三个参数key 如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) =‘v1’

–Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. For example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) returns ‘facebook.com’. Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, for example, parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) returns ‘v1’.

29、printf(STRING format, Obj… args)

–按照printf风格格式输出字符串

30、regexp_extract(string subject, string pattern, int index)

–抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格

–Returns the string extracted using the pattern. For example, regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\s’ is necessary to match whitespace, etc. The ‘index’ parameter is the Java regex Matcher group() method index.

31、 regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

–按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, “oo|ar”, “”) = ‘fb.’ 注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格

–Returns the string resulting from replacing all substrings in INITIAL_STRING that match the java regular expression syntax defined in PATTERN with instances of REPLACEMENT. For example, regexp_replace(“foobar”, “oo|ar”, “”) returns ‘fb.’ Note that some care is necessary in using predefined character classes: using ‘\s’ as the second argument will match the letter s; ‘\s’ is necessary to match whitespace, etc.

32、repeat(STRING str, INT n)

–重复输出n遍字符串str

33、replace(STRING a, STRING old, STRING new)

–返回字符串a，其中所有未重叠的旧替换为新的(如Hive 1.3.0和2.1.0)。示例:选择replace(“ababab”,“abab”,“Z”);返回“Zab”。

34、reverse(STRING a)

–反转字符串

35、rpad(STRING str, INT len, STRING pad)

–从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分

36、rtrim(STRING a)

–去掉字符串后面出现的空格

37、 sentences(string str, string lang, string locale)

–返回array<array>类型，字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) )

38、soundex(STRING a)

–将普通字符串转换成soundex字符串(hive1.2)

39、space(INT n)

–返回n个空格

40、split(STRING str, STRING pat)

–返回array 在pat周围分割str (pat) (pat是一个正则表达式)。

41、str_to_map(text[, delimiter1, delimiter2])

–返回map<string,string>，将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"="

–Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ‘,’ for delimiter1 and ‘=’ for delimiter2.

42、substr(STRING|BINARY A, INT start [, INT len])

–对于字符串A,从start位置开始截取字符串并返回，例如，substr(‘foobar’， 4)=‘bar’

43、substring(STRING|BINARY a, INT start [, INT len])

–同上

44、substring_index(string A, string delim, int count)

–在计数分隔符delim之前返回字符串A的子串（自Hive 1.3.0开始）。如果count是正数，则返回最后一个分隔符左边的数字（从左数起）。如果count为负数，则返回最后一个分隔符右边的数字（从右数起）。 Substring_index在搜索delim时执行区分大小写的匹配

–Returns the substring from string A before count occurrences of the delimiter delim (as of Hive 1.3.0). If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Substring_index performs a case-sensitive match when searching for delim. Example: substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’.

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

–将input出现在from中的字符串替换成to中的字符串 如：translate(“MOBIN”,“BIN”,“M”)=“MOM”

46、trim(STRING a)

–去掉字符串A前面的空格

47、ucase(STRING a)

48、upper(STRING a)

–将字符串A中的字母转换成大写字母

49、unbase64(STRING a)

–将64位的字符串转换二进制值

十、Data Masking–数据屏蔽

1、mask(STRING str [, STRING upper [, STRING lower [, STRING number]]])

–返回一个隐藏的str版本(Hive 2.1.0)。默认情况下，大写字母转换为“X”，小写字母转换为“X”，数字转换为“n”。例如，mask(“abcd-EFGH-8765-4321”)返回xxxx- xxxx- nnn-nnnn。您可以通过提供额外的参数来覆盖掩码中使用的字符:第二个参数控制大写字母的掩码字符，小写字母的第三个参数和数字的第四个参数。例如,面具(“abcd - efgh - 8765 - 8765”,“U”、“l”、“#”)返回llll-UUUU - # # # # # # # #。

–Returns a masked version of str (as of Hive 2.1.0). By default, upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example mask(“abcd-EFGH-8765-4321”) results in xxxx-XXXX-nnnn-nnnn. You can override the characters used in the mask by supplying additional arguments: the second argument controls the mask character for upper case letters, the third argument for lower case letters and the fourth argument for numbers. For example, mask(“abcd-EFGH-8765-4321”, “U”, “l”, “#”) results in llll-UUUU-####-####.

2、mask_first_n(STRING str [, INT n])

–返回带有第一个n值的屏蔽版本的str(如Hive 2.1.0)。大写字母被转换为“X”，小写字母被转换为“X”，数字被转换为“n”。例如，mask_first_n(“1234-5678-8765-4321”，4)结果为nnnn-5678-8765-4321。

–Returns a masked version of str with the first n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_first_n(“1234-5678-8765-4321”, 4) results in nnnn-5678-8765-4321.

3、mask_last_n(STRING str [, INT n])

–返回带有最后n个值(如Hive 2.1.0)的屏蔽版本的str。大写字母被转换为“X”，小写字母被转换为“X”，数字被转换为“n”。例如，mask_last_n(“1234-5678-8765-4321”，4)结果是1234-5678-8765-nnnn。

–Returns a masked version of str with the last n values masked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_last_n(“1234-5678-8765-4321”, 4) results in 1234-5678-8765-nnnn.

4、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。

–Returns a masked version of str, showing the first n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_first_n(“1234-5678-8765-4321”, 4) results in 1234-nnnn-nnnn-nnnn.

5、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-nnn -nn -4321。

–Returns a masked version of str, showing the last n characters unmasked (as of Hive 2.1.0). Upper case letters are converted to “X”, lower case letters are converted to “x” and numbers are converted to “n”. For example, mask_show_last_n(“1234-5678-8765-4321”, 4) results in nnnn-nnnn-nnnn-4321.

6、mask_hash(STRING|CHAR|VARCHAR str)

–返回基于str的散列值(如Hive 2.1.0)。哈希是一致的，可以用来在表之间连接蒙面值。对于非字符串类型，此函数返回null。

–Returns a hashed value based on str (as of Hive 2.1.0). The hash is consistent and can be used to join masked values together across tables. This function returns null for non-string types.

十一、Table Generating–表格生成

1、explode(array a)

–对于a中的每个元素，将生成一行且包含该元素

2、explode(ARRAY)

–每行对应数组中的一个元素

3、explode(MAP)

–每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值

4、inline(Array<Struct [, Struct]> a)

–将结构体数组提取出来并插入到表中

5、json_tuple(STRING jsonStr, STRING k1, STRING k2, …)

–从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

–The parse_url_tuple() UDTF is similar to parse_url(), but can extract multiple parts of a given URL,

returning the data in a tuple. Values for a particular key in QUERY can be extracted by appending a colon and the key to the partToExtract argument.

6、posexplode(ARRAY)

–与explode类似，不同的是还返回各元素在数组中的位置

–posexplode() is similar to explode but instead of just returning the elements of the array it returns the element as well as its position in the original array.

7、stack(INT n, v1, v2, …, vk)

–把M列转换成N行，每行有M/N个字段，其中n必须是个常数

十二、Type Conversion–类型转化

1、binary(BINARY|STRING a) --将输入的值转化为二进制

2、cast(a as T) --格式转化，如果转化不成功会返回NULL值
————————————————
版权声明：本文为CSDN博主「huobumingbai1234」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/huobumingbai1234/article/details/80348305