Hive函数（内置函数和自定义函数）

一.内置函数1.字符串函数（1）ascii（2）base64（3）concat（4）concat_ws（5）format_number（6）substr,substring（7）instr（8）length（9）locate（10）printf（11）str_to_map（13）unbase64（14）upper，ucase（15）lower，lcase（16）trim,ltrim，rtrim（17）regexp_replace（18）regexp_extract（19）parse_url（20）get_json_object（21）space（22）repeat（23）lpad（24）rpad（25）split（26）find_in_set—集合查找函数（27）sentences—分词函数（28）ngrams—词频（29）context_ngrams2.类型转换函数（1）binary—二进制转换（2）cast—类型强转3.数学函数（1）round（2）floor（3）ceil（4）rand（5）exp（6）ln（7）log10,log2,log（8）pow（9）sqrt（10）bin（11）hex（12）unhex（13）conv（14）abs（15）pmod4.日期函数（1）unix_timestamp/ from_unixtime—**时间戳函数**（2）current_date—获取当前日期（3）to_date—日期时间转日期（4）获取日期中的年月/日/时/分/秒/周（5）datediff—日期之差（6）date_add/date_sub—日期增加和减少（7）其他日期函数5.集合函数6.条件函数（1）if函数（2）coalesce—非空查找函数（3）case—条件判断7.聚合函数（1）sum—求和（2）count—求数据量（3）avg—平均值（4）distinct—去重（5）min—最小值（6）max—最大值8.表生成函数（1）explode（2）json_tuple（3）parse_url_tuple（4）lateral view二.自定义函数1.自定义函数分类（1）UDF(（User-Defined-Function)（2）UDAF（User-Defined Aggregation Function）（3）UDTF（User-Defined Table-Generating Functions）2.自定义函数创建流程（1）编程步骤（2）实例（3）添加jar包的几种方法3.自定义临时函数和自定义永久函数（1）临时函数（2）永久函数

一.内置函数

--显示hive下内置所有函数show functions;--查看函数的详细使用方法desc function extended 函数名;

1.字符串函数

（1）ascii

语法：ascii(string str)返回值：int说明：返回字符串str第一个字符的ascii码

--示例hive> select ascii("ba");OK98

（2）base64

语法：base64(binary bin)返回值：string说明：返回二进制bin的base编码字符串

hive> select base64(binary('test'));OKdGVzdA==

（3）concat

语法：concat(string A,string B)返回值：string说明：返回输入字符串连接后的结果，支持任意个输入字符串

hive> select concat('a','1','b');OKa1b

（4）concat_ws

语法： concat_ws(string SEP, string A, string B...)返回值：string说明：返回输入字符串连接后的结果,SEP 表示各个字符串间的分隔符--数组转换成字符串语法: concat_ws(string SEP, array<string>)返回值: string说明:返回将数组链接成字符串后的结果,SEP 表示各个字符串间的分隔符

hive> select concat_ws('---','a','1','b');OKa---1---b--列转行hive> select * from t3;OKA 1A 2A 3A 4B 2B 5B 1hive> select item ,concat_ws(",",collect_list(nums)) as num from t3 group by item;OKA 1,2,3,4B 2,5,1hive> select concat_ws('|',array('a','b','c'));OKa|b|c

（5）format_number

语法： format_number(number x, int d)返回值：string说明：:将数值 x 的小数位格式化成 d 位,四舍五入

hive> select format_number(5.23456,3);OK5.235

（6）substr,substring

从1开始数

语法: substr(string A, int start),substring(string A, int start)返回值: string说明:返回字符串 A 从 start 位置到结尾的字符串语法: substr(string A, int start, int len),substring(string A, intstart, int len)返回值: string说明:返回字符串 A 从 start 位置开始,长度为 len 的字符串

hive> select substr('abcde',3);OKcdeTime taken: 0.016 seconds, Fetched: 1 row(s)hive> select substr('abcde',3,1);OKc

（7）instr

语法: instr(string str, string substr)返回值: int说明:返回字符串 substr 在 str 中首次出现的位置

hive> select instr('asdfs','f');OK4hive> select instr('asdfs','fd');OK0

（8）length

语法: length(string A)返回值: int说明:返回字符串的长度

hive> select length('abn');OK3

（9）locate

语法: locate(string substr, string str[, int pos])返回值: int说明:返回字符串 substr 在 str 中从 pos 后查找,首次出现的位置

hive> select locate('a','abcba');OK1hive> select locate('a','abcba',2);OK5

（10）printf

语法: printf(String format, Obj... args)返回值: string说明:将指定对象用 format 格式进行格式化

hive> select printf("%08x",123);OK0000007b

（11）str_to_map

语法: str_to_map(text[, delimiter1, delimiter2])返回值: map<string,string>说明:将字符串按照给定的分隔符转换成 map 结构.

hive> select str_to_map('k1:v1,k2:v2');OK{"k1":"v1","k2":"v2"}hive> select str_to_map('k1=v1,k2=v2',',','=');OK{"k1":"v1","k2":"v2"}

（13）unbase64

语法: unbase64(string str)返回值: binary说明:将给定的 base64 字符串解码成二进制

hive> select unbase64('dGVzdA==');OKtest

（14）upper，ucase

语法: upper(string A) ucase(string A)返回值: string说明:返回字符串 A 的大写格式

hive> select upper('djkFssFK');OKDJKFSSFKhive> select ucase('djkFssFK');OKDJKFSSFK

（15）lower，lcase

语法: lower(string A) lcase(string A)返回值: string说明:返回字符串 A 的小写格式

hive> select lower('djkFssFK');OKdjkfssfkhive> select lcase('djkFssFK');OKdjkfssfk

（16）trim,ltrim，rtrim

语法: trim(string A)返回值: string说明:去除字符串两边的空格语法: ltrim(string A)返回值: string说明:去除字符串左边的空格语法: rtrim(string A)返回值: string说明:去除字符串右边的空格

hive> select trim(' abc ');OKabchive> select ltrim(' abc ');OKabchive> select rtrim(' abc ');OKabc

（17）regexp_replace

语法: regexp_replace(string A, string B, string C)返回值: string说明:将字符串 A 中的符合 java 正则表达式 B 的部分替换为 C。注意,在有些情况下要使用转义字符,类似 oracle 中的 regexp_replace 函数。

--将oo和ar替换成''hive> select regexp_replace('foobar','oo|ar','');OKfb

（18）regexp_extract

语法: regexp_extract(string subject, string pattern, intindex)返回值: string说明:将字符串 subject 按照 pattern 正则表达式的规则拆分,返回 index 指定的字符。

hive> select regexp_extract('footbar','foo(.*?)(bar)',0);OKfootbarhive> select regexp_extract('footbar','foo(.*?)(bar)',1);OKthive> select regexp_extract('footbar','foo(.*?)(bar)',2);OKbar

（19）parse_url

语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])返回值: string说明:返回 URL 中指定的部分。partToExtract 的有效值为:HOST, PATH,QUERY,REF,PROTOCOL, AUTHORITY, FILE, and USERINFO.

hive> select parse_url('/path1/p.php?k1=v1&k2=v2#Ref1','HOST');hive> select parse_url('/path1/p.php?k1=v1&k2=v2#Ref1','QUERY','k1');OKv1

（20）get_json_object

语法: get_json_object(string json_string, string path)返回值: string说明:解析 json 的字符串 json_string,返回 path 指定的内容。如果输入的json 字符串无效,那么返回 NULL。

hive> select get_json_object('{"store":{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"} },"email":"amy@", "owner":"amy" } ','$.owner');OKamy

（21）space

语法: space(int n)返回值: string说明:返回长度为 n 的字符串

hive> select space(10);OKhive> select length(space(10));OK10

（22）repeat

语法: repeat(string str, int n)返回值: string说明:返回重复 n 次后的 str 字符串

hive> select repeat('abc',5);OKabcabcabcabcabc

（23）lpad

语法: lpad(string str, int len, string pad)返回值: string说明:将 str 进行用 pad 进行左补足到 len 位

--补足总长度为10hive> select lpad('abc',10,'td');OKtdtdtdtabc

（24）rpad

语法: rpad(string str, int len, string pad)返回值: string说明:将 str 进行用 pad 进行右补足到 len 位

hive> select rpad('abc',10,'td');OKabctdtdtdtTime taken: 0.026 seconds, Fetched: 1 row(s)

（25）split

语法: split(string str, string pat)返回值: array说明: 按照 pat 字符串分割 str,会返回分割后的字符串数组

hive> select split('adfsfdsts','s');OK["adf","fd","t",""]

（26）find_in_set—集合查找函数

语法: find_in_set(string str, string strList)返回值: int说明: 返回 str 在 strlist 第一次出现的位置,strlist 是用逗号分割的字符串。如果没有找到该 str 字符,则返回 0

hive> select find_in_set('ab','ef,ab,de');OK2

（27）sentences—分词函数

语法: sentences(string str, string lang, string locale)返回值: array<array<string>>说明:返回输入 str 分词后的单词数组

hive> select sentences('hello world!hello hive,hi hive,hello hive');OK[["hello","world"],["hello","hive","hi","hive","hello","hive"]]

（28）ngrams—词频

语法: ngrams(array<array<string>>, int N, int K, int pf)返回值: array<struct<string,double>>说明:与 sentences()函数一起使用,分词后,统计分词结果中一起出现频次最高的 TOP-K 结果

hive>SELECT ngrams(sentences('hello word!hello hive,hi hive,hello hive'),2,2)OK[{"ngram":["hello","hive"],"estfrequency":2.0},{"ngram":["hive","hi"],"estfrequency":1.0}]--该查询中,统计的是两个词在一起出现频次最高的 TOP-2;结果中,hello 与 hive 同时出现 2 次

（29）context_ngrams

语法: context_ngrams(array<array<string>>,array<string>, int K, int pf)返回值: array<struct<string,double>>说明:与 sentences()函数一起使用,分词后,统计分词结果中与数组中指定的单词一起出现(包括顺序)频次最高的 TOP-K 结果

hive> select context_ngrams(sentences('hello> word!hello> hive,hi> hive,hello> hive'),array('hello',null),3);[{"ngram":["hive"],"estfrequency":2.0},{"ngram":["word"],"estfrequency":1.0}]--该查询中,统计的是与’hello’一起出现,并且在 hello 后面的频次最高的TOP-3结果中,hello 与 hive 同时出现 2 次,hello 与 word 同时出现 1 次

2.类型转换函数

（1）binary—二进制转换

语法: binary(string|binary)返回值: binary说明: 将 string 类型转换为二进制

hive> select binary('charels');OKcharels

（2）cast—类型强转

语法: cast(expr as )返回值:说明: 将 expr 转换成

hive> select cast('1' as DOUBLE);OK1.0

3.数学函数

（1）round

语法：round(DOUBLE a)返回类型：DOUBLE说明：返回数字 a 四舍五入后的值语法：round(DOUBLE a, INT d)返回类型：DOUBLE说明：返回数字 a 四舍五入到小数点后 d 位的值

hive> select round(1.5);OK2.0hive> select round(1.12, 4);OK1.12hive> select round(1.12, -1);OK0.0

（2）floor

语法:floor(DOUBLE a)返回类型：bigint说明：对数字 a 向下取整

hive> select floor(1.1);OK1hive> select floor(1.6);OK1hive> select floor(-1.6);OK-2hive> select floor(-1.1);OK-2

（3）ceil

语法：ceil(DOUBLE a), ceiling(DOUBLE a)返回类型：bigint说明：对数字 a 向上取整

hive> select ceil(-1.1);OK-1hive> select ceil(-1.6);OK-1hive> select ceil(1.6);OK2hive> select ceil(1.1);OK2

（4）rand

语法：rand(), rand(INT seed)返回类型：double说明：如果 seed 不指定，则返回 0 到 1 之间的随机数

hive> select rand();OK0.5912090525580687hive> select rand(2);OK0.7311469360199058

（5）exp

语法：exp(DOUBLE a), exp(DECIMAL a)返回类型：double说明：返回 e 的 a 次方

hive> select exp(2);OK7.38905609893065

（6）ln

语法：ln(DOUBLE a), ln(DECIMAL a)返回类型：double说明：返回以 e 为底，a 的对数

hive> select ln(0.0);OKNULLTime taken: 0.015 seconds, Fetched: 1 row(s)hive> select ln(1);OK0.0

（7）log10,log2,log

语法：log10(DOUBLE a), log10(DECIMAL a)返回类型：double说明：返回以 10 为底，a 的对数语法：log2(DOUBLE a), log2(DECIMAL a)返回类型：double说明：返回以 2 为底，a 的对数语法：log(DOUBLE base, DOUBLE a)，log(DECIMAL base, DECIMAL a)返回类型：double说明：返回以 base 为底，a 的对数

hive> select log10(10);OK1.0hive> select log2(10);OK3.3219280948873626hive> select log(2,4);OK2.0

（8）pow

语法：pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)返回类型：double说明：返回 a 的 p 次方

hive> select pow(2,4);OK16.0

（9）sqrt

语法：sqrt(DOUBLE a), sqrt(DECIMAL a)返回类型：double说明：返回 a 的 平方根

hive> select sqrt(4);OK2.0

（10）bin

语法：：bin(BIGINT a)返回类型：STRING说明：返回 a 的 二进制表达式

hive> select bin(-8);OK1111111111111111111111111111111111111111111111111111111111111000

（11）hex

语法：：hex(BIGINT a) hex(STRING a) hex(BINARY a)返回类型：STRING说明：如果参数 a 是整数，则返回十六进制表达式;如果参数 a 是字符串，则逐一将字符串中的每个字符对应的 ASCII 码以十六进制表示并返回，

hive> select hex(16);OK10hive> select hex(-1);OKFFFFFFFFFFFFFFFF

（12）unhex

语法：unhex(STRING a)返回类型：STRING说明：hex 的逆函数，以十六进制解释参数 a ，并进行 ASCII 转换，返回对应的字符

hive> select unhex('41');OKA

（13）conv

语法：conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)返回类型：STRING说明：将数字 num 或者字符串 num 从 from_base 进制转换到 to_base 进制

hive> select conv(10000000000,2,10);OK1024hive> select conv('1024', 10, 2);OK10000000000

（14）abs

语法：abs(DOUBLE a)返回类型：DOUBLE说明：返回 a 的绝对值

hive> select abs('-1212');OK1212.0

（15）pmod

语法：pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)返回类型：INT or DOUBLE说明：返回 a 模 b 的值，pmod(a, b) 同 a % b

hive> select pmod(10,3);OK1

4.日期函数

（1）unix_timestamp/ from_unixtime—时间戳函数

--unix_timestamp日期转时间戳：从 1970-01-01 00:00:00 UTC 到指定时间的秒数--from_unixtime时间戳转日期

hive> select unix_timestamp('0915 14:23:00','yyyyMMdd HH:mm:ss');OK1505456580hive> select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'); --获取系统当前时间unix_timestamp(void) is deprecated. Use current_timestamp instead.OK-12-17 17:32:31

（2）current_date—获取当前日期

hive> select current_date;OK-12-17

（3）to_date—日期时间转日期

hive> select to_date('-09-15 11:12:00');OK-09-15

（4）获取日期中的年月/日/时/分/秒/周

hive> with dtime as(select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')> as dt)> select> year(dt),month(dt),day(dt),hour(dt),minute(dt),second(dt),weekofyear(dt)> from dtime;unix_timestamp(void) is deprecated. Use current_timestamp instead.OK 121717361151

（5）datediff—日期之差

hive> select datediff('-09-15','-09-01');OK14

（6）date_add/date_sub—日期增加和减少

hive> select date_add('-12-17',12);OK-12-29hive> select date_sub('-12-17',12);OK-12-05

（7）其他日期函数

--查询当前系统时间(包括毫秒数)hive> select current_timestamp;OK-12-17 17:39:47.868--查询当月第几天hive> select dayofmonth(current_date);OK17--本月最后一天hive> select last_day(current_date);OK-12-31--本月第一天hive> select date_sub(current_date,dayofmonth(current_date)-1);OK-12-01Time taken: 0.033 seconds, Fetched: 1 row(s)--下个月第一天hive> select add_months(date_sub(current_date,dayofmonth(current_date)-1),1);OK-01-01

5.集合函数

6.条件函数

（1）if函数

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)返回值: T说明: 当条件 testCondition 为 TRUE 时，返回 valueTrue；否则返回valueFalseOrNull

hive> select if(1=2,100,200);OK200

（2）coalesce—非空查找函数

语法: COALESCE(T v1, T v2, …)返回值: T说明: 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回 NULL

hive> select COALESCE(null,'100','50');OK100

（3）case—条件判断

语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END返回值: T说明：如果 a 等于 b，那么返回 c；如果 a 等于 d，那么返回 e；否则返回 f语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END返回值: T说明：如果 a 为 TRUE,则返回 b；如果 c 为 TRUE，则返回 d；否则返回 e

hive> select case 100 when 50 then 'tom'> when 100 then 'mary'> else 'tim'> end;OKmaryhive> select case when 1=2 then 'tom'> when 2=2 then 'mary'> else 'tim'> end> ;OKmary

7.聚合函数

（1）sum—求和

（2）count—求数据量

（3）avg—平均值

（4）distinct—去重

（5）min—最小值

（6）max—最大值

8.表生成函数

（1）explode

语法：explode(ARRAY)说明：返回 n 行，每行对应数组中的一个元素语法：explode(MAP)说明：返回 n 行两列，每行对应每个 map 键-值，第一列是 map 的键，第二列是map 的值。（不常用）

hive> select explode(array(1,2,3,4));OK1234hive> select explode(map(1,2,3,4));OK1 23 4

（2）json_tuple

语法：json_tuple(jsonStr, k1, k2, …)说明：从一个 JSON 字符串中获取多个 key 对应的 value 并作为一个元组返回。

（3）parse_url_tuple

语法：parse_url_tuple(url, p1, p2, …)说明：返回从 URL 中抽取指定 N 部分的内容并作为一个元组返回，参数 url 是 URL字符串，而参数 p1,p2,…是要抽取的部分

（4）lateral view

udtf有一个很大的限制，在使用 UDTF 时，select 后面只能跟 UDTF，不能跟其他任何字段，否则会报错

hive> select 1,explode(array(1,2,3,4));FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

​ lateral view 就是为了解决在 select 使用 UDTF 做查询过程中，查询只能包含单个 UDTF，不能包含其他字段、以及多个 UDTF 的问题。

​ lateral view 会将 UDTF 拆分成多行的结果放到一个支持别名的虚拟表中，然后这个虚拟表会和输入行进行 join 来达到连接 UDTF 外的 select 字段的目的。

select uid,name,tmp.cfrom courselateral view explode(kc) tmp as c;

二.自定义函数

当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

1.自定义函数分类

（1）UDF(（User-Defined-Function)

一进一出

（2）UDAF（User-Defined Aggregation Function）

多进一出，类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

一进多出，如lateral view explore()

2.自定义函数创建流程

​ 编写一个 UDF，需要继承 UDF 并实现 evaluate()函数。在查询过程中，查询中对应的每个应用到这个函数的地方都会对这个类进行实例化。对于每行输入都会调用 evaluate()函数。而 evaluate()处理后的值会返回给 Hive。同时用户是可以重载 evaluate 方法的。Hive 会像 Java 的方法重载一样，自动选择匹配的方法。

（1）编程步骤

1）继承org.apache.hadoop.hive.ql.UDF

2）需要实现 evaluate 函数；evaluate 函数支持重载；

3）在 hive 的命令行窗口创建函数

添加jaradd jar linux_jar_path创建functioncreate [temporary] function [dbname.]function_name AS class_name;

4）调用函数

注意：UDF 必须要有返回类型，可以返回 null，但是返回类型不能为 void；

（2）实例

1）创建一个 maven 项目，添加依赖

<dependencies><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-common</artifactId><version>2.6.0-cdh5.14.2</version></dependency><dependency><groupId>org.apache.hive</groupId><artifactId>hive-exec</artifactId><version>1.1.0-cdh5.14.2</version></dependency></dependencies>

添加打包插件

<build><plugins><plugin><artifactId>maven-compiler-plugin</artifactId><version>3.3</version><configuration><source>1.8</source><target>1.8</target></configuration></plugin><plugin><artifactId>maven-assembly-plugin</artifactId><executions><execution><phase>package</phase><goals><goal>single</goal></goals></execution></executions><configuration><descriptorRefs><descriptorRef>jar-with-dependencies</descriptorRef></descriptorRefs></configuration></plugin></plugins></build>

2）创建一个类

package cn.kgc.udf;import org.apache.hadoop.hive.ql.exec.UDF;public class Str2Lower extends UDF {public Text evaluate(Text s){if (s==null){return null;}return new Text(s.toString().toLowerCase());} }

3）打成 jar 包上传到服务器/opt/jars/udf.jar

4）将 jar 包添加到 hive 的 classpath

hive (default)> add jar /opt/jars/udf.jar;

5）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function my_lower as "cn.kgc.udf.Str2Lower";

6）可在 hql 中使用自定义的函数 strip

hive (default)> select ename, my_lower(ename) lowername from emp;

（3）添加jar包的几种方法

1）方法一：使用 add jar 命令

在 hive 下执行:add jar /home/hadoop/DefTextInputFormat.jar;

Added [/home/hdfs/DefTextInputFormat.jar] to class path

Added resources: [/home/hdfs/DefTextInputFormat.jar]

该方法每次启动 Hive 的时候都要重新加入，退出 hive 就会失效。

2）hive-site.xml 文件配置 hive.aux.jars.path

<property><name>hive.aux.jars.path</name><value>file:///home/hadoop/DefTextInputFormat.jar,file:///jarpath/test.jar</value></property>

3）方法三：根目录${HIVE_HOME}下创建文件夹 auxlib，然后将自定义 jar文件放入该文件夹中；

3.自定义临时函数和自定义永久函数

（1）临时函数

仅对当前session（黑窗口）有效，一旦退出 HIVE 客户端，自定义临时函数即被删除

--添加 jar 包hive> add jar /opt/datas/addPrefix.jar;--创建自定义临时函数hive> create temporary function add_prefix as 'com.test.AddPrefix';--使用自定义临时函数hive> select add_prefix('aa');HIVE UDF Prefix:aa

（2）永久函数

如果需要使用自定义永久函数，可以先上传到 HDFS

[root@hadoop101 ~]# hadoop fs -put addPrefix.jar /opt/datas

--创建永久函数hive > create function add_prefix as 'com.test.AddPrefix'using jar 'hdfs:///opt/datas/addPrefix.jar'--与自定义临时函数不同，使用永久函数只能在当前数据库--这里我们放在 test 库，如果在其它库执行，会报错hive (test)> select add_prefix('aa');HIVE UDF Prefix:aahive> use tmp;hive (tmp)> select add_prefix('aa');FAILED: SemanticException [Error 10011]: Invalid function add_prefix--删除自定义永久函数hive (test)> drop function add_prefix;

Hive函数（内置函数（字符串函数 数学函数 日期函数 集合函数 条件函数 聚合函数 表生成函数）和自定义函数（自定义函数创建流程 临时函数 永久函数）））（四）