函数

7.1 系统自带的函数
1）查看系统自带的函数
hive> show functions;
2）显示自带的函数的用法
hive> desc function upper;
3）详细显示自带的函数的用法
hive> desc function extended upper;
7.2 自定义函数
1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
3）根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
（2）UDAF（User-Defined Aggregation Function）
聚集函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
一进多出
如lateral view explore()
4）官方文档地址
https://cwiki.apache.org/confluence/display/Hive/HivePlugins
5）编程步骤：
（1）继承org.apache.hadoop.hive.ql.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
（3）在hive的命令行窗口创建函数
a）添加jar
add jar linux_jar_path
b）创建function，
create [temporary] function [dbname.]function_name AS class_name;
（4）在hive的命令行窗口删除函数
Drop [temporary] function [if exists] [dbname.]function_name;
6）注意事项
（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
7.3 自定义UDF函数开发案例
1）创建一个java工程，并创建一个lib文件夹
2）将hive的jar包解压后，将apache-hive-1.2.1-bin\lib文件下的jar包都拷贝到java工程中。
3）创建一个类

package com.itstar.hive;
import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {

    public String evaluate (final String s) {
        
        if (s == null) {
            return null;
        }
        
        return s.toString().toLowerCase();
    }
}

4）打成jar包上传到服务器/opt/module/jars/udf.jar
5）将jar包添加到hive的classpath
hive (default)> add jar /opt/module/datas/udf.jar;
6）创建临时函数与开发好的java class关联(全类名)
hive (default)> create temporary function udf_lower as "Lower";
7）即可在hql中使用自定义的函数strip
hive (default)> select ename, udf_lower(ename) lowername from emp;

7.4 行转列、列转行（UDAF与UDTF）
7.4.1、列转行
表结构：
name constellation blood_type
孙悟空 白羊座 A
白骨精 射手座 A
沙和尚 白羊座 B
猪八戒 白羊座 A
蛤蟆精 射手座 A
创建表及数据导入：

create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/datas/person_info.tsv' into table person_info;

注：
1）Concat()函数 将多个字符串用特定符号链接成一个字符串
a.concat(constellation, ",", blood_type,",",字段1，",",字段2，",",字段3)
b.select concat(constellation, ",", blood_type, ":", name) from person_info;
2）concat_ws()函数 将多个字符串用特定符号链接成一个字符串
a.select concat_ws(",", blood_type, constellation, name) from person_info;
3）collect_set()函数 将多个字符串用特定符号链接成一个字符串且去除重复元素。
a.select collect_set(constellation) from person_info;
例如：把星座和血型一样的人归类到一起
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;

7.4.2、行转列
表结构：
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
创建表及导入数据：

create table movie_info(
    movie string, 
    category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";
load data local inpath "/opt/module/datas/movie_info.tsv" into table movie_info;

例如：将电影分类中的数组数据展开
select
movie,
category_name
from
movie_info
lateral view explode(category) table_tmp as category_name;
lateral view 语法式