【Hive学习二】Hive函数介绍

现在虽然有很多SQL ON Hadoop的解决方案，像Spark SQL、Impala、Presto等等，但就目前来看，在基于Hadoop的大数据分析平台、数据仓库中，Hive仍然是不可替代的角色。尽管它的相应延迟大，尽管它启动MapReduce的时间相当长，但是它太方便、功能太强大了，做离线批量计算、ad-hoc查询甚至是实现数据挖掘算法，而且，和HBase、Spark都能整合使用。

如果是做大数据分析平台和数据仓库相关的，就目前来说，Hive是必须应该掌握的。

常用命令：
1.显示所有函数：
show functions;

2.显示函数信息：
desc function concat;

3.显示函数扩展信息
desc function extended concat;

Hive操作手册：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

Hive自带的UDF函数非常多，我整理了下目录，以及部分函数的使用要点和方法，希望能对Hive使用者有所帮助。
Hive函数大全 目录：

一、关系运算：

1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 区间比较
9. 空值判断: IS NULL
10. 非空判断: IS NOT NULL
11. LIKE比较: LIKE
    （1）字符”_”表示任意单个字符，而字符”%”表示任意数量的字符
    （2）注意：否定比较时候用 NOT A LIKE B hive> select 1 from table where NOT 'football' like 'fff%';
12. JAVA的LIKE操作: RLIKE：
    如果字符串 A 符合 JAVA 正则表达式 B 的正则语法，则为 TRUE；否则为 FALSE。
13. REGEXP操作: REGEXP：功能与 RLIKE 相同

**二、数学运算： **

1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
   9．位取反操作: ~ 注意将~运算后的值，进行补码运算

**三、逻辑运算： **

1. 逻辑与操作: AND 、&&
2. 逻辑或操作: OR 、||
3. 逻辑非操作: NOT、!

**四、复合类型构造函数 **

1. map结构
2. struct结构
3. named_struct结构
4. array结构
5. create_union

五、复合类型操作符

1. 获取array中的元素：
   返回数组 A 中第 n 个索引的元素值：
   hive> select array('a','b','c')[1] from table;
   结果：b
2. 获取map中的元素:
   返回 map 结构 M 中 key 对应的 value：
   hive> select map('k1','v1')['k1'] from table;
   结果：v1
3. 获取struct中的元素
   返回 struct 结构 S 中名为 x 的元素：
   hive> select named_struct('a',1,'b','aaa','c',FALSE).c from table;
   结果：false

六、数值计算函数

1. 取整函数: round
   语法: round(double a)
   返回值：BIGINT 四舍五入
2. 指定精度取整函数: round
   语法: round(double a, int d)
   返回值:返回指定精度 d 的 double 类型，小数点后d位
3. 向下取整函数: floor
4. 向上取整函数: ceil
5. 向上取整函数: ceiling
6. 取随机数函数: rand
7. 自然指数函数（返回自然对数e的a次方）: exp(double a)
   自然对数函数（返回a的自然对数）：ln(double a)
8. 以10为底对数函数: log10(double a)
9. 以2为底对数函数: log2(double a)
10. 对数函数: log(double base, double a) 返回以 base 为底的 a 的对数，double类型
11. 幂运算函数: pow(double a, double p)
12. 幂运算函数: power(double a, double p)
13. 开平方函数: sqrt(double a) 返回a的平方根
14. 二进制函数: bin bin(BIGINT a) 返回 a 的二进制代码表示，string类型
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv conv(BIGINT num, int from_base, int to_base) 将数值 num 从 from_base 进制转化到 to_base 进制
18. 绝对值函数: abs abs(double a) / abs(int a)
19. 正取余函数: pmod pmod(int a, int b),pmod(double a, double b) 返回正的 a 除以 b 的余数
20. 正弦函数: sin sin(double a)
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive函数: positive positive(int a), positive(double a) 返回a
25. negative函数: negative 返回-a

七、集合操作函数

1. map类型大小：size size(map('k1','v1','k2','v2'))

2. array类型大小：size size(array(1,2,3,4,5))

3. 判断元素数组是否包含元素：array_contains
   hive> select array_contains(array(1,2,3,4,5),3) from table;
   true

4. 获取map中所有value集合
   hive> select map_values(map('k1','v1','k2','v2')) from table;
   ["k2","k1"]

5. 获取map中所有key集合
   map_keys(map('k1','v1','k2','v2'))

6. 数组排序
   hive> select sort_array(array(5,7,3,6,9)) from table;
   [3,5,6,7,9]

八、类型转换函数

1. 二进制转换：binary
2. 基础类型之间强制转换：cast
   举例：cast(name as int)： 将name字段的数据类型转为int类型

九、日期函数

1. UNIX时间戳转日期函数: from_unixtime
   语法: from_unixtime(bigint unixtime[, string format])
   返回值: string
   说明: 转化 UNIX 时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式
   举例：
   hive> select from_unixtime(1323308943,'yyyyMMdd') from table;
   结果：20111208

2. 获取当前UNIX时间戳函数: unix_timestamp() 无参数

3. 日期转UNIX时间戳函数: unix_timestamp
   语法: unix_timestamp(string date)
   返回值: bigint
   说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到 UNIX 时间戳。如果转化失败，则返回 0。
   举例：
   hive> select unix_timestamp('2011-12-07 13:01:03') from table;
   结果：1323234063

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

5. 日期时间转日期函数: to_date
   语法: to_date(string timestamp)
   返回值: string
   说明: 返回日期时间字段中的日期部分。
   举例：
   hive> select to_date('2011-12-08 10:03:01') from table;
   结果：2011-12-08

6. 日期转年函数: year

7. 日期转月函数: month

8. 日期转天函数: day

9. 日期转小时函数: hour

10. 日期转分钟函数: minute

11. 日期转秒函数: second

12. 日期转周函数: weekofyear

13. 日期比较函数: datediff

14. 日期增加函数: date_add
    语法: date_add(string startdate, int days)
    返回值: string
    说明: 返回开始日期 startdate 增加 days 天后的日期。
    举例：
    hive> select date_add('2012-12-08',10) from table;
    结果：2012-12-18

15. 日期减少函数: date_sub

十、条件函数

1. If函数: if
   语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
   说明: 当条件 testCondition 为 TRUE 时，返回 valueTrue；否则返回 valueFalseOrNull
2. 非空查找函数: COALESCE
3. 条件判断函数：CASE
   语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
   说明：如果 a 为 TRUE,则返回 b；如果 c 为 TRUE，则返回 d；否则返回 e

十一、字符串函数

1. 字符ascii码函数：ascii

2. base64字符串

3. 字符串连接函数：concat concat(string A, string B…)

4. 带分隔符字符串连接函数：concat_ws concat_ws(string SEP, string A, string B…)，其中SEP为分隔符

5. 数组转换成字符串的函数：concat_ws 举例：concat_ws('|',array('a','b','c'))

6. 小数位格式化成字符串函数：format_number
   语法: format_number(number x, int d)
   返回值: string
   说明：将数值 x 的小数位格式化成 d 位，四舍五入
   举例：
   hive> select format_number(5.23456,3) from table;
   结果：5.235

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

8. 字符串截取函数：substr,substring
   语法：substr(string A, int start, int len),substring(string A, int start, int len)
   说明：返回字符串 A 从 start 位置开始，长度为 len 的字符串

9. 字符串查找函数： instr(string str, string substr)

10. 字符串长度函数：length

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

12. 字符串格式化函数：printf

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

14. base64解码函数：unbase64(string str)

15. 字符串转大写函数：upper/ucase

16. 字符串转小写函数：lower/lcase

17. 去空格函数（去除字符串两边的空格）：trim

18. 左边去空格函数：ltrim

19. 右边去空格函数：rtrim

20. 正则表达式替换函数：regexp_replace
    语法: regexp_replace(string A, string B, string C)
    返回值: string
    说明：将字符串 A 中的符合 java 正则表达式 B 的部分替换为 C。注意，在有些情况下要
    使用转义字符,类似 oracle 中的 regexp_replace 函数。
    举例：
    hive> select regexp_replace('foobar', 'oo|ar', '') from table;
    结果：fb

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

22. 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('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') from
    table;
    facebook.com

23. json解析函数：get_json_object

24. 空格字符串函数：space

25. 重复字符串函数：repeat repeat(string str, int n)

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

27. 右补足函数：rpad

28. 分割字符串函数: split
    语法: split(string str, string pat)
    返回值: array
    说明: 按照 pat 字符串分割 str，会返回分割后的字符串数组
    举例：
    hive> select split('abtcdtef','t') from table;
    ["ab","cd","ef"]

29. 集合查找函数: find_in_set

30. 分词函数：sentences

31. 分词后统计一起出现频次最高的TOP-K

32. 分词后统计与指定单词一起出现频次最高的TOP-K

十二、混合函数

1. 调用Java函数：java_method
2. 调用Java函数：reflect
3. 字符串的hash值：hash

十三、XPath解析XML函数

1. xpath
2. xpath_string
3. xpath_boolean
4. xpath_short, xpath_int, xpath_long
5. xpath_float, xpath_double, xpath_number

十四、汇总统计函数（UDAF）

1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 非空集合总体变量函数: var_pop
7. 非空集合样本变量函数: var_samp
8. 总体标准偏离函数: stddev_pop
9. 样本标准偏离函数: stddev_samp
   10．中位数函数: percentile
10. 中位数函数: percentile
11. 近似中位数函数: percentile_approx
12. 近似中位数函数: percentile_approx
13. 直方图: histogram_numeric
14. 集合去重数：collect_set
15. 集合不去重函数：collect_list

十五、表格生成函数Table-Generating Functions (UDTF)

1. 数组拆分成多行：explode
2. Map拆分成多行：explode