Hive函数

1.Hive函数

Hive的函数分为三类： 聚合函数、内置函数，表生成函数

1.1 内置函数

1:数学函数
2:字符串函数
3:日期函数
4:条件函数
	1) if 函数
		hive> select if(1=2,100,200) ; -- 三元
		200
		hive> select if(1=1,100,200) ;
		100

	2) 条件判断 case
		hive> select case when 1=2 then 'tom' when 2=2 then 'mary' else'tim' end ;
				mary
		hive> select case when 1=1 then 'tom' when 2=2 then 'mary' else'tim' end ;
				tom

 select s_id  ,case when s_score>=60 then '及格' when s_score<60 then '不及格' else '其他' end  from score;

1.2 转换函数(*)

行转列案例需求(列多了 分散开了):

• 1、行转列是指多行数据转换为一个列的字段。
• LATERAL VIEW explode 函数

如何将
a	b	1,2,3
c	d	4,5,6
变为：
name1   name2    nums
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6 

SQL答案: SELECT name1,name2,nums FROM rtc LATERAL VIEW explode(num)r as nums;

列转行(行少 在一起了):
可以参考: https://www.cnblogs.com/cc11001100/p/9043946.html

• collect_list列出该字段所有的值，不去重 select collect_list(id) from table;
• collect_set 去重
  

1.3 explode函数

explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
explode(ARRAY) 数组的每个元素生成一行
explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列

数据:
10	 CLARK|KING|MILLER
20	 SMITH|JONES|SCOTT|ADAMS|FORD
30 	ALLEN|WARD|MARTIN|BLAKE|TURNER|JAMES

1)建表
create table emp2(
deptno int,
names array<string>
)
row format delimited fields terminated by '\t'
collection items terminated by '|';

2)加载数据
3)查询
elect explode(字段) as 别名 from emp;

1.4 Reflect函数

reflect函数可以支持在sql中调用java中的自带函数

需求:
使用java.lang.Math当中的Max求两列中最大值

--创建hive表
create table test_udf(col1 int,col2 int) row format delimited fields terminated by ',';

--准备数据 test_udf.txt
1,2
4,3
6,4
7,5
5,6

--加载数据

load data local inpath '文件路径'  into table test_udf;

--使用java.lang.Math当中的Max求两列当中的最大值   (只能静态的)
select reflect("java.lang.Math","max",col1,col2) from test_udf;

1.5 开窗函数

row_number() OVER 从1开始，按照顺序，生成分组内记录的序列(相等没有并列)
rank() 生成数据项在分组中的排名，排名相等会在名次中留下空位(有并列,后一个不显示)
dense_rank() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位(有并列,后一个显示)
SELECT 
  cookieid,
  createtime,
  pv,
  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
  FROM 表名;

------------------------------------------------------------
SELECT 
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
FROM 表名 
WHERE cookieid = 'cookie1';

2. 自定义函数

当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
根据用户自定义函数类别分为以下三种：
	1、UDF（User-Defined-Function）
		一进一出  
类似于:upper/lower/reverse
	2、UDAF（User-Defined Aggregation Function）
		聚集函数，多进一出
		      类似于：count/max/min
	3、UDTF（User-Defined Table-Generating Functions）
		一进多出
		如lateral view explode()

2.1自定义UDF

/*
编程步骤：
	（1）继承org.apache.hadoop.hive.ql.exec.UDF
	（2）需要实现evaluate函数；evaluate函数支持重载；
注意事项:
	（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
	（2）UDF中常用Text/LongWritable等类型，不推荐使用java类型；
 */
//1.自定义类  继承UDF类
public class MyUDF extends UDF {
    //重载evaluate
    public Text evaluate (final Text lineStr){
        if (lineStr == null) {
            return null;
        }
        String upperStr = lineStr.toString().toUpperCase();  //toLowerCase将字符串转大写
        return new Text(upperStr);
    }
}

1)打包  jar包
2)上传 安装hive的  lib目录下
3、hive的客户端添加我们的jar包
hive>add jar /export/server/hive-2.1.0/lib/my_upper.jar

4、设置函数与我们的自定义函数关联-临时函数
hive>create temporary function my_upper as '全类名';

5、使用自定义函数
hive>select my_upper('abc');

2.2自定义UDTF

//1：定义类继承GenericUDTF
public class MyUDTF extends GenericUDTF {
      private final transient Object[] forwardListObj = new Object[1];  //创建一个数组  里面一个元素  根据需求可以有多个

    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        //该集合用来存放UDTF函数执行之后列的名字
        ArrayList<String> fieldsName = new ArrayList<String>();
        //设置列名
        fieldsName.add("column_1");
        // fieldNames.add("column_02");

        //该集合用来指定列的类型
        ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>(); //检查器列表
        //设置列表类型
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        // fieldOIs.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);

        //将列名和列类型返回
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldsName,fieldOIs);
    }

    //该方法每读取一行数据都会执行，类似于MapReduce中的map方法
    /*
      "zookeeper,hadoop,hdfs,hive,MapReduce"

      select my_udtf("zookeeper,hadoop,hdfs,hive,MapReduce",",") word;
     */
    public void process(Object[] objects) throws HiveException {
        //1.获取原始数据资源
        String lineStr = objects[0].toString();  //"zookeeper,hadoop,hdfs,hive,MapReduce"
        //2:获取数据传入的第二个参数，此处为分隔符
        String splitKey = objects[1].toString(); //","

        //3.将原始数据按照传入的分隔符进行切分
        String[] split = lineStr.split(splitKey);  // [zookeeper,hadoop,hdfs,hive,MapReduce]
        for (String s : split) {
            //将每一个单词添加值对象数组
            forwardListObj[0] = s;
            //将对象数组内容写出
            forward(forwardListObj);
        }
    }

    //关流或者资源释放
    public void close() throws HiveException {

    }
}



1)打包  jar包
2)上传 安装hive的  lib目录下
3、hive的客户端添加我们的jar包
hive>add jar /export/server/hive-2.1.0/lib/my_upper.jar

4、设置函数与我们的自定义函数关联-临时函数
hive>create temporary function  my_udtf as '全类名';

5、使用自定义函数
hive>select my_udtf("zookeeper,hadoop,hdfs,hive,MapReduce",",");