一、函数分类

9.Hive常用函数 - 图1

二、简单函数

一、关系运算

1.不等值比较: <>

语法: A <> B 操作类型: 所有基本类型 描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE

  1. hive> select 1 from iteblog where 1 <> 2;
  2. 1

2.LIKE比较: LIKE

语法: A LIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。

  1. hive> select 1 from iteblog where 'football' like 'foot%';
  2. 1
  3. hive> select 1 from iteblog where 'football' like 'foot____';
  4. 1
  5. <strong>注意：否定比较时候用NOT A LIKE B</strong>
  6. hive> select 1 from iteblog where NOT 'football' like 'fff%';
  7. 1

3.JAVA的LIKE操作: RLIKE

语法: A RLIKE B 操作类型: strings 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。

  1. hive> select 1 from iteblog where 'footbar’ rlike '^f.*r$’;
  2. 1
  3. 注意：判断一个字符串是否全为数字：
  4. hive>select 1 from iteblog where '123456' rlike '^\\d+$';
  5. 1
  6. hive> select 1 from iteblog where '123456aa' rlike '^\\d+$';

4.REGEXP操作: REGEXP

语法: A REGEXP B 操作类型: strings 描述: 功能与RLIKE相同

  1. hive> select 1 from iteblog where 'footbar' REGEXP '^f.*r$';
  2. 1

二、数学运算

1.取余操作: %

语法: A % B 操作类型：所有数值类型 说明：返回A除以B的余数。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

  1. hive> select 41 % 5 from iteblog;
  2. 1
  3. hive> select 8.4 % 4 from iteblog;
  4. 0.40000000000000036
  5. <strong>注意</strong>：精度在hive中是个很大的问题，类似这样的操作最好通过round指定精度
  6. hive> select round(8.4 % 4 , 2) from iteblog;
  7. 0.4

2.位与操作: &

语法: A & B 操作类型：所有数值类型 说明：返回A和B按位进行与操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

  1. hive> select 4 & 8 from iteblog;
  2. 0
  3. hive> select 6 & 4 from iteblog;
  4. 4

3.位或操作: |

语法: A | B 操作类型：所有数值类型 说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

  1. hive> select 4 | 8 from iteblog;
  2. 12
  3. hive> select 6 | 8 from iteblog;
  4. 14

4.位异或操作: ^

语法: A ^ B 操作类型：所有数值类型 说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。

  1. hive> select 4 ^ 8 from iteblog;
  2. 12
  3. hive> select 6 ^ 4 from iteblog;
  4. 2

5.位取反操作: ~

语法: ~A 操作类型：所有数值类型 说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。

  1. hive> select ~6 from iteblog;
  2. -7
  3. hive> select ~4 from iteblog;
  4. -5

三、数值运算

1.取整函数: round

语法: round(double a) 返回值: BIGINT 说明: 返回double类型的整数值部分 （遵循四舍五入）

  1. hive> select round(3.1415926) from iteblog;
  2. 3
  3. hive> select round(3.5) from iteblog;
  4. 4
  5. hive> create table iteblog as select round(9542.158) from iteblog;
  6. hive> describe iteblog;
  7. _c0     bigint

2.指定精度取整函数: round

语法: round(double a, int d) 返回值: DOUBLE 说明: 返回指定精度d的double类型

  1. hive> select round(3.1415926,4) from iteblog;
  2. 3.1416

3.向下取整函数: floor

语法: floor(double a) 返回值: BIGINT 说明: 返回等于或者小于该double变量的最大的整数

  1. hive> select floor(3.1415926) from iteblog;
  2. 3
  3. hive> select floor(25) from iteblog;
  4. 25

4.向上取整函数: ceil

语法: ceil(double a) 返回值: BIGINT 说明: 返回等于或者大于该double变量的最小的整数

  1. hive> select ceil(3.1415926) from iteblog;
  2. 4
  3. hive> select ceil(46) from iteblog;
  4. 46

5.取随机数函数: rand

语法: rand(),rand(int seed) 返回值: double 说明: 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列

  1. hive> select rand() from iteblog;
  2. 0.5577432776034763
  3. hive> select rand() from iteblog;
  4. 0.6638336467363424
  5. hive> select rand(100) from iteblog;
  6. 0.7220096548596434
  7. hive> select rand(100) from iteblog;
  8. 0.7220096548596434

四、日期函数

1.获取当前时间

语法: 1. CURRENT_DATE/current_date()
2.current_timestamp()
返回值: string 说明: 时间格式为：YYYY-MM-dd

  1. hive> SELECT current_date();
  2. 2020-08-29
  4. SELECT current_timestamp();
  5. 2019-04-02 17:53:06.138

2.日期增加/减少函数: date_add/date_sub

语法: date_add(string startdate, int days)，date_sub (string startdate, int days) 返回值: string 说明: 返回开始日期startdate增加days天后的日期，返回开始日期startdate减少days天后的日期。

  1. hive> select date_add('2012-12-08',10)
  2. 2012-12-18
  3. hive> select date_sub('2012-12-08',10)
  4. 2012-11-28

3.UNIX时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format]) 返回值: string 说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

  1. hive> select from_unixtime(1323308943,'yyyyMMdd') from iteblog;
  2. 20111208

4.获取时间戳函数: unix_timestamp

语法: unix_timestamp(string date) 返回值: bigint 说明: 转换格式为”yyyy-MM-dd HH:mm:ss”的日期到UNIX时间戳。如果转化失败，则返回0。

  1. -- unix_timestamp() 获取当前时间戳
  2. hive> select unix_timestamp() from iteblog;
  3. 1323309615
  5. hive> select unix_timestamp('2011-12-07 13:01:03') from iteblog;
  6. 1323234063

5.指定格式日期转UNIX时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern) 返回值: bigint 说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。

  1. hive> select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss') from iteblog;
  2. 1323234063

6.日期时间转日期函数: to_date

语法: to_date(string timestamp) 返回值: string 说明: 返回日期时间字段中的日期部分。

  1. hive> select to_date('2011-12-08 10:03:01') from iteblog;
  2. 2011-12-08

五、条件函数

1.If函数: if

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

  1. hive> select if(1=2,100,200) from iteblog;
  2. 200
  3. hive> select if(1=1,100,200) from iteblog;
  4. 100

2.条件判断函数：CASE

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

  1. hive> Select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from iteblog;
  2. mary
  3. hive> Select case 200 when 50 then 'tom' when 100 then 'mary' else 'tim' end from iteblog;
  4. tim

3.条件判断函数：CASE

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

  1. hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from iteblog;
  2. mary
  3. hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else 'tim' end from iteblog;
  4. tom

4.非空查找函数: COALESCE

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

  1. hive> select COALESCE(null,'100','50′) from iteblog;
  2. 100

六、字符串函数

1.字符串连接函数：concat

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

  1. hive> select concat(‘abc’,'def’,'gh’) from iteblog;
  2. abcdefgh

2.带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…) /concat_ws(string SEP, array) 返回值: string 说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符

  1. hive> select concat_ws(',','abc','def','gh') from iteblog;
  2. abc,def,gh

3.字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start) 返回值: string 说明：返回字符串A从start位置到结尾的字符串

  1. hive> select substr('abcde',3) from iteblog;
  2. cde
  3. hive> select substring('abcde',3) from iteblog;
  4. cde
  5. hive>  select substr('abcde',-1) from iteblog;  （和ORACLE相同）
  6. e

4.字符串截取函数：substr,substring

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

  1. hive> select substr('abcde',3,2) from iteblog;
  2. cd
  3. hive> select substring('abcde',3,2) from iteblog;
  4. cd
  5. hive>select substring('abcde',-2,2) from iteblog;
  6. de

5.字符串转大写函数：upper,ucase

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

  1. hive> select upper('abSEd') from iteblog;
  2. ABSED
  3. hive> select ucase('abSEd') from iteblog;
  4. ABSED

6.字符串转小写函数：lower,lcase

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

  1. hive> select lower('abSEd') from iteblog;
  2. absed
  3. hive> select lcase('abSEd') from iteblog;
  4. absed

7.去空格函数：trim

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

  1. hive> select trim(' abc ') from iteblog;
  2. abc

8.正则表达式替换函数：regexp_replace

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

  1. hive> select regexp_replace('foobar', 'oo|ar', '') from iteblog;
  2. fb

9.正则表达式解析函数：regexp_extract

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

hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from iteblog;
the
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 2) from iteblog;
bar
hive> select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) from iteblog;
foothebar
strong>注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
select data_field,
  regexp_extract(data_field,'.*?bgStart\\=([^&]+)',1) as aaa,
  regexp_extract(data_field,'.*?contentLoaded_headStart\\=([^&]+)',1) as bbb,
  regexp_extract(data_field,'.*?AppLoad2Req\\=([^&]+)',1) as ccc 
  from pt_nginx_loginlog_st 
  where pt = '2012-03-26' limit 2;

10.json解析函数：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@only_for_json_udf_test.net",
>  "owner":"amy"
> }
> ','$.owner') from iteblog;
amy

11.URL解析函数：parse_url

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

hive> select parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from iteblog;
facebook.com
hive> select parse_url('https://www.iteblog.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') from iteblog;
v1

12.分割字符串函数: split

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

hive> select split('abtcdtef','t') from iteblog;
["ab","cd","ef"]

13.左补足函数：lpad

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

hive> select lpad('abc',10,'td') from iteblog;
tdtdtdtabc
注意：与GP，ORACLE不同，pad 不能默认

14.右补足函数：rpad

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

hive> select rpad('abc',10,'td') from iteblog;
abctdtdtdt

七、类型转换函数

1.基础类型之间强制转换：cast

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

hive> select cast('1' as DOUBLE) from lxw1234;
OK
1.0

2.二进制转换：binary

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

hive> select binary('lxw1234') from lxw1234;
OK
lxw1234

三、集合函数

一、arry函数

1.array构建: array

语法：array(val1,val2,val3,…) 操作类型：array 说明：使用给定的表达式，构造一个 array 数据结构

hive> select array(1,2,3) from lxw1234;
OK
[1,2,3]

2.集合不去重函数：collect_list

语法: collect_list (col) 返回值: array 说明: 将 col 字段合并成一个数组,不去重

hive> select cookie,ip from lxw1234;
cookie1 127.0.0.1
cookie1 127.0.0.1
cookie1 127.0.0.2
cookie1 127.0.0.3
hive>select cookie,collect_list(ip) from lxw1234 group by cookie;
cookie1 ["127.0.0.1","127.0.0.1","127.0.0.2","127.0.0.3"]

3.集合去重数：collect_set

语法: collect_set (col) 返回值: array 说明: 将 col 字段进行去重，合并成一个数组。

hive> select cookie,ip from lxw1234;
cookie1 127.0.0.1
cookie1 127.0.0.1
cookie1 127.0.0.2
cookie1 127.0.0.3
hive> select cookie,collect_set(ip) from lxw1234 group by cookie;
cookie1 ["127.0.0.1","127.0.0.2","127.0.0.3"]

4. 获取 array 中的大小

语法: size(Array) 返回值: int 说明: 返回 array 类型的 size

hive> select size(array(1,2,3,4,5)) from lxw1234;
OK
5

5. 获取 array 中的元素

语法：A[n] 操作类型：所有基础类型 说明：返回数组 A 中第 n 个索引的元素值。

hive> select array('a','b','c')[1] from lxw1234;
OK
b

6. 判断元素数组是否包含元素：array_contains

语法: array_contains(Array, value) 返回值: boolean 说明: 返回 Array中是否包含元素 value

hive> select array_contains(array(1,2,3,4,5),3) from lxw1234;
OK
true

7. 数组排序

语法: sort_array(Array) 返回值: array 说明: 对 Array进行升序排序

hive> select sort_array(array(5,7,3,6,9)) from lxw1234;
OK
[3,5,6,7,9]

8. 数组拆分成多行：explode

语法: explode(ARRAY) 返回值: 多行 说明: 将数组中的元素拆分成多行显示

hive> select explode(array(1,2,3)) from lxw1234;
OK
1
2
3

二、map函数

1.Map构建: map

语法：map(k1,v1,k2,v2,…) 操作类型：map 说明：使用给定的 key-value 对，构造一个 map 数据结构

hive> select map('k1','v1','k2','v2') from lxw1234;
OK
{"k2":"v2","k1":"v1"}

2.Map长度获取函数: size(Map)

语法: size(Map) 返回值: int 说明: 返回map类型的长度

hive> select size(map('100','tom','101','mary')) from iteblog;
2

3.获取 map 中的元素: M[key]

语法: M[key] 操作类型: M为map类型，key为map中的key值 说明：返回map类型M中，key值为指定值的value值。比如，M是值为{‘f’ -> ‘foo’, ‘b’ -> ‘bar’, ‘all’ -> ‘foobar’}的map类型，那么M[‘all’]将会返回’foobar’

hive> Create table iteblog as select map('100','tom','200','mary') as t from iteblog;
hive> select t['200'],t['100'] from iteblog;
mary    tom

4.获取 map 中所有 key 集合

语法: map_keys(Map) 返回值: array 说明: 返回 Map中所有 key 的集合

hive> select map_keys(map('k1','v1','k2','v2')) from lxw1234; 
OK
["k2","k1"]

5.获取 map 中所有 value 集合

语法: map_values(Map) 返回值: array 说明: 返回 Map中所有 value 的集合

hive> select map_values(map('k1','v1','k2','v2')) from lxw1234;
OK
["v2","v1"]

6.字符串转换成 map 函数：str_to_map

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

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

7.Map 拆分成多行：explode

语法: explode(Map) 返回值: 多行 说明: 将 Map 中的元素拆分成多行显示

hive> select explode(map('k1','v1','k2','v2')) from lxw1234;
OK
k2 v2
k1 v1

三、 struct 函数

1.struct构建: struct

语法：struct(val1,val2,val3,…) 操作类型：struct 说明：使用给定的表达式，构造一个 struct 数据结构

hive> select struct(1,'aaa',FALSE) from lxw1234;
OK
{"col1":1,"col2":"aaa","col3":false}

2. named_struct构建

语法：named_struct(name1,val1,name2,val2,name3,val3,…) 操作类型：struct 说明：使用给定的表达式，构造一个指定列名的 struct 数据结构

hive> select named_struct('a',1,'b','aaa','c',FALSE) from lxw1234;
OK
{"a":1,"b":"aaa","c":false}

四、特殊函数

一、lateral view

Lateral View是Hive中提供给UDTF的conjunction，它可以解决UDTF不能添加额外的select列的问题。

1.Why we need Lateral View？

当我们想对hive表中某一列进行split之后，想对其转换成1 to N的模式，即一行转多列。
hive不允许我们在UDTF函数之外，再添加其它select语句。
如下，我们想将登录某个游戏的用户id放在一个字段user_ids里，对每一行数据用UDTF后输出多行。

select game_id, explode(split(user_ids,'\\[\\[\\[')) as user_id   from login_game_log  where dt='2014-05-15' 
FAILED: Error in semantic analysis: UDTF's are not supported outside the SELECT clause, nor nested in expressions。
提示语法分析错误，UDTF不支持函数之外的select 语句。如果我们想支持怎么办呢？接下来就是Lateral View 登场的时候了。

2. Lateral View explain

Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行即每个game_id进行join 来达到连接UDTF外的select字段的目的。

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*

可以看出，可以在2个地方用Lateral view：
1. 在udtf前面用
2. 在from baseTable后面用

3.使用lateral view

说明：lateral view用于和json_tuple，parse_url_tuple，split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

hive>select s.x,sp from test.dual s lateral view explode(split(concat_ws(',','1','2','3','4','5','6','7','8','9'),',')) t as sp;
x   sp  
a   1  
b   2  
a   3
-- 解释一下，from后面是你的表名，在表名后面加lateral view explode。。。（你的行转列sql） ，还必须要起一个别名，我这个字段的别名为sp。然后再看看select后面的 s.*，就是原表的字段，我这里面只有一个字段，且为X

多个lateral view的sql类如：
hive> SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(myCol1) myTable2 AS myCol2;
OK

4.Outer Lateral View

还有一种情况，如果UDTF转换的Array是空的怎么办呢？
在Hive0.12里面会支持outer关键字，如果UDTF的结果是空，默认会被忽略输出。
如果加上outer关键字，则会像left outer join 一样，还是会输出select出的列，而UDTF的输出结果是NULL。

hive> select * FROM test_lateral_view_shengli LATERAL VIEW explode(array()) C AS a ;

--加上outer关键字：
SELECT * FROM src LATERAL VIEW OUTER explode(array()) C AS a limit 10;
238 val_238 NULL
86 val_86 NULL
311 val_311 NULL
27 val_27 NULL
165 val_165 NULL
409 val_409 NULL
255 val_255 NULL
278 val_278 NULL
98 val_98 NULL
...


总结： 1.Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。 2.Multiple Lateral View可以实现类似笛卡尔乘积。

3.Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

五、实例

1.Hive collect_set()排序和concat_ws()的使用

select *,concat_ws(",",sort_array(collect_set(b) over(distribute by a))) c from a;

参考资料：
1.Hive函数大全-完整版
2.Lateral View用法 与 Hive UDTF explode