HIve的内置函数（字符、日期、集合条件等，附带Hive调优工具）

字符函数

1、concat(string|binary A, string|binary B…)
对二进制字节码或字符串按次序进行拼接，返回string。

select concat('a','b','c');

abc

2、instr(string str, string substr)
查找字符串str中子字符串substr出现的位置，返回int

select instr('abc','a');

1

3、locate(string substr, string str[, int pos])
查找字符串str中的pos位置后字符串substr第一次出现的位置，返回int

select locate('a','abcacad',2);

4、lower(string A) /upper(string A)
将字符串A的所有字母转换成小写/大写字母

select lower('abc');

5、split(string str, string pat)
按照正则表达式pat来分割字符串str，返回的是array。

select split('a,b,c',',');

6、substr(string|binary A, int start, int len)
substring(string|binary A, int start, int len)
对字符串A,从start位置开始截取长度为len的字符串并返回

select substr('abcdefghi',2,3);

7、trim(string A)
将字符串A前后出现的空格去掉

select trim(' abc');

类型转换和数学函数

类型转换函数

1、cast(expr as )
将expr转换成type类型

select cast('1' as bigint);

将字符串1转换成了BIGINT类型
2、binary(string|binary)
将输入的值转换成二进制

select binary('abc');

数学函数

1、**round(DOUBLE a)**返回对a四舍五入的BIGINT值，返回值double类型

select round(12.3);

2、round(DOUBLE a, INT d)，返回值binary类型
返回对a四舍五入并保留d位小数位的值

select round(12.3569,2);

3、floor(DOUBLE a)，返回值bigint类型，向下取整。

select floor(6.5);

4、rand(INT seed)
返回值类型是double类型，返回一个DOUBLE型随机数，seed是随机因子。

select rand();

5、power(DOUBLE a, DOUBLE p)
计算a的p次幂，返回值类型是double

select power(2,2);

6、abs(DOUBLE a)
计算a的绝对值，返回值类型是double类型

select abs(-2);

日期函数

这一部分作为前篇文章的补充。感兴趣的伙伴也可以去看一下。里面也例举了一些例题给大家参考。
HIve的日期函数（year/month/week）
1、from_unixtime(bigint unixtime[, string format])
将时间戳转换成format格式，返回值类型是string。

select from_unixtime(1600770000,'yyyy-MM-dd HH:mm:ss');

补充：时间戳指的是格林尼治时间到当前时间的差值，已秒计算。
2、unix_timestamp()
获取本地时区下的时间戳

select unix_timestamp();

3、unix_timestamp(string date)
将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳，返回值类型是bigint。

select unix_timestamp('2020-09-23 11:00:00');

也可以这样使用：

select unix_timestamp(current_date);

4、to_date(string timestamp)
返回时间字符串的日期部分，返回值类型是string。

select to_date('2020-09-22 18:20:00');

5、date_add(string startdate, int days)
从开始时间startdate加上days。返回值类型：string。

select date_add('2020-09-22 18:20:00',2);

6、date_sub(string startdate, int days)
减去days，用法同上。我们也以通过设置days的正负值来实现加减。

select date_sub('2020-09-22 18:20:00',-2);

这样就成加了。

7、current_timestamp
返回当前时间戳，返回值类型是timestamp;

 select current_timestamp;

和current_date对比一下就会发现二者的不同：

current_date输出的只有日期，current_timestamp输出还包括时间。
8、date_format(date/timestamp/string ts, string fmt)
按指定格式返回时间date。返回值类型：string。

select date_format('2020-09-22','MM-dd');

集合函数

我们用下面的数据做演示
分别是姓名，住址，个人信息，专业，职位，各数据间用|分割。

Michael|Montreal,Toronto|Male,30|DB:80|Product:DeveloperLead
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|NewYork|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89|Sales:Lead

创建的表格信息如下：

1、size(Map<K.V>)
返回map中键值对个数。

select size(jobs) from employee;

2、size(Array)
返回数组的长度

select size(address) from employee;

3、map_keys(Map<K.V>)
返回map中的所有key，返回值类型：array

select map_keys(technol) from employee;

4、map_values(Map<K.V>)
返回map中的所有value

select map_values(technol) from employee;

6、array_contains(Array, value)
如该数组Array包含value返回true。，否则返回false。

select array_contains(address,'New York')from employee;

7、sort_array(Array)
对数组进行排序，返回值类型：array.

select sort_array(address) from employee;

条件函数

1、if(boolean testCondition, T valueTrue, T valueFalseOrNull)
如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull。（三元表达式）

select if(1=1,'yes','no');

2、nvl(T value, T default_value)
value为NULL返回default_value,否则返回value

select nvl(null,'abc');
select nvl('ABC','abc');

3、COALESCE(T v1, T v2, …)
返回第一非null的值，如果全部都为NULL就返回NULL

select coalesce(null,1,'a',null,3);

4、isnull( a )
如果a为null就返回true，否则返回false

select isnull(address) from employee;

5、isnotnull ( a )
如果a为非null就返回true，否则返回false

select isnotnull(address) from employee;

6、CASE a WHEN b THEN c
[WHEN d THEN e] [ELSE f] END*
如果a=b就返回c,a=d就返回e，否则返回f；

CASE WHEN a THEN b
[WHEN c THEN d] [ELSE e] END*
如果a=ture就返回b,c= ture就返回d,否则返回e。
case表达式相信大家已经很熟悉了，这里就不再多做演示。

Hive性能调优工具

explain
使用EXPLAIN可以帮助我们学习Hive是如何将查询转换成MapReduce任务的。在查询语句前面加上explain关键字，可以看到查询计划和其它一些信息。这个查询本身是不会执行的。

explain  select * from employee;

analyze
分析表数据，用于执行计划选择的参考。收集表的统计信息，如行数，最大值等，同时，在使用时调用该信息会加速查询。实际上analyze使用时已经把数据读取了出来，所以查询会更快。
语法示例：

ANALYZE TABLE employee COMPUTE STATISTICS; 

ANALYZE TABLE employee_partitioned 
PARTITION(year=2014, month=12) COMPUTE STATISTICS;

ANALYZE TABLE employee_id COMPUTE STATISTICS 
FOR COLUMNS employee_id;