Hive函数怎么用

这篇文章主要介绍了Hive函数怎么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

1、函数

1.1 、系统内置函数

1）查看系统自带的函数 show functions;   --289个 2）显示自带的函数的用法 desc function upper; 3）详细显示自带的函数的用法 desc function extended upper;

1.2、常用内置函数

1.2.1、空字段赋值（NVL）

--格式：NVL(value，default_value)  value为字段 default_value返回默认值--案例：当奖金为Null时,用0替代select ename, comm, sal, nvl(comm,0)from emp;

1.2.2、CASE WHEN THEN ELSE END

--格式：case 字段 when 'char' then 1 else 0 end;--案例：+---------------+------------------+--------------+| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  |+---------------+------------------+--------------+| 悟空           | A                | 男           || 大海           | A                | 男           || 宋宋           | B                | 男           || 凤姐           | A                | 女           || 婷姐           | B                | 女           || 婷婷           | B                | 女           |+---------------+------------------+--------------++----------+-------+---------+| dept_id  | male  | female  |+----------+-------+---------+| A        | 2     | 1       || B        | 1     | 2       |+----------+-------+---------+select   dept_id,  sum(case sex when '男' then 1 else 0 end) male,  sum(case sex when '女' then 1 else 0 end) femalefrom emp_sexgroup by dept_id;--拓展函数之if:  格式:if(sex='男',1,0)  true返回1，false返回0select   dept_id,  sum(if(sex='男',1,0)) male,  sum(if(sex='女',1,0)) femalefrom emp_sexgroup by dept_id;

1.2.3、行转列

1.2.3.1、相关函数说明

--CONCAT(string A/col, string B/col…)：//返回输入字符串连接后的结果，支持任意个输入字符串;--CONCAT_WS(separator, str1, str2,...)：//它是一个特殊形式的 CONCAT()。第一个参数为参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何NULL 和空字符串。分隔符将被加到被连接的字符串之间;注意：CONCAT_WS must be "string or array"--COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。--COLLECT_LIST(col)：函数只接受基本数据类型，主要作用是将某字段的值进行不去重汇总，产生array类型字段。

1.2.3.2、案例实操

数据准备：+---------------+------------------+--------------+|     name      |  constellation   |  blood_type  |+---------------+------------------+--------------+| 悟空           | 射手座           | A            || 八戒           | 天秤座           | A            || 路飞           | 射手座           | B            || 娜美           | 射手座           | A            || 女帝           | 天秤座           | A            || 罗宾           | 射手座           | B            |+---------------+------------------+--------------+需求：把星座和血型一样的人归类到一起。结果如下：+-------------+-----------------+|  天秤座，A   |   八戒|女帝     ||  射手座，A   |   悟空|娜美     ||  射手座，B   |   路飞|罗宾     |+-------------+-----------------+按需求查询数据：SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))FROM (SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_bFROM person_info)t1GROUP BY t1.c_b

1.2.4、列转行

1.2.4.1、相关函数说明

--Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。--EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。--LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

1.2.4.2、案例实操

数据准备：+------------------+--------------------------+|      movie       |  category                |+------------------+--------------------------+| 《疑犯追踪》      |  悬疑,动作,科幻,剧情      || 《Lie to me》    |  悬疑,警匪,动作,心理,剧情  || 《战狼2》        |  战争,动作,灾难            |+-----------------+---------------------------+需求：将电影分类中的数组数据展开。结果如下《疑犯追踪》      悬疑《疑犯追踪》      动作《疑犯追踪》      科幻《疑犯追踪》      剧情《Lie to me》    悬疑《Lie to me》    警匪《Lie to me》    动作《Lie to me》    心理《Lie to me》    剧情《战狼2》        战争《战狼2》        动作《战狼2》        灾难按需求查询数据：SELECT movie,category_name FROM movie_info lateral VIEWexplode(split(category,",")) movie_info_tmp  AS category_name ;

1.2.5、窗口函数（开窗函数）

1.2.5.1、相关函数说明

--OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。--CURRENT ROW：当前行--n PRECEDING：往前n行数据--n FOLLOWING：往后n行数据--UNBOUNDED：无边界        --UNBOUNDED PRECEDING 前无边界，表示从前面的起点，         --UNBOUNDED FOLLOWING 后无边界，表示到后面的终点--LAG(col,n,default_val)：往前第n行数据--LEAD(col,n, default_val)：往后第n行数据--FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值--LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值--NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

1.2.5.2、案例实操

数据准备：name,orderdate,cost         jack,2017-01-01,10         tony,2017-01-02,15         jack,2017-02-03,23         tony,2017-01-04,29         jack,2017-01-05,46         jack,2017-04-06,42         tony,2017-01-07,50         jack,2017-01-08,55         mart,2017-04-08,62         mart,2017-04-09,68         neil,2017-05-10,12         mart,2017-04-11,75         neil,2017-06-12,80         mart,2017-04-13,94需求：（1）查询在2017年4月份购买过的顾客及总人数（2）查询顾客的购买明细及月购买总额（3）上述的场景, 将每个顾客的cost按照日期进行累加（4）查询顾客购买明细以及上次的购买时间和下次购买时间（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间（6）查询前20%时间的订单信息按需求查询数据：（1）查询在2017年4月份购买过的顾客及总人数        select name,count(name) over() Person_num        from business        where month(orderdate)=4  //或者where substring(orderdate,1,7)='2017-04'        group by name;（2）查询顾客的购买明细及月购买总额        select name,orderdate,cost,                sum(cost) over(partition by name,month(orderdate)) month_amount        from business;（3）将每个顾客的cost按照日期进行累加        select name,orderdate,cost,                sum(cost) over(partition by name order by orderdate                 //rows between unbounded preceding and current row) accu_cost --默认 可以不加        from business;注意：rows必须跟在Order by子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量order by子句后面不加 rows between and,默认为rows between unbounded preceding and current row（4）查询顾客购买明细以及上次的购买时间和下次购买时间        select name,orderdate,cost,             lag(orderdate,1,'1970-01-01') over(partition by name order by orderdate) last_date,             lead(orderdate,1,'1970-01-01') over(partition by name order by orderdate) next_date        from business;（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间        select name,orderdate,cost,             first_value(orderdate)                     over(partition by name,month(orderdate) order by orderdate                      rows between unbounded preceding and unbounded following) first_date,             last_value(orderdate)                      over(partition by name,month(orderdate) order by orderdate                      rows between unbounded preceding and unbounded following) last_date        from business;（6）查询前20%时间的订单信息        select *        from (                select name, orderdate, cost, ntile(5) over (order by orderdate) date_rank                from business                 )t1        where t1.date_rank=1;

1.2.6、Rank

1.2.6.1、相关函数说明

--RANK() 排序相同时会重复(考虑并列,会跳号)，总数不会变--DENSE_RANK() 排序相同时会重复(考虑并列,不跳号)，总数会减少--ROW_NUMBER() 会根据顺序计算(不考虑并列,不跳号,行号)

1.2.6.2、案例实操

数据准备：        name    subject score        孙悟空     语文      87        孙悟空     数学      95        孙悟空     英语      68        路飞      语文      94        路飞      数学      56        路飞      英语      84        柯南      语文      64        柯南      数学      86        柯南      英语      84        艾伦      语文      65        艾伦      数学      85        艾伦      英语      78需求：计算每门学科成绩排名。按需求查询数据：select name,subject,score,        rank() over(partition by subject order by score desc) rp,        dense_rank() over(partition by subject order by score desc) drp,        row_number() over(partition by subject order by score desc) rmpfrom score;结果：name    subject score   rp      drp     rmp孙悟空   数学    95      1       1       1柯南     数学    86      2       2       2艾伦     数学    85      3       3       3路飞     数学    56      4       4       4柯南     英语    84      1       1       1路飞     英语    84      1       1       2艾伦     英语    78      3       2       3孙悟空   英语    68      4       3       4路飞     语文    94      1       1       1孙悟空   语文    87      2       2       2艾伦     语文    65      3       3       3柯南     语文    64      4       4       4

1.3、自定义函数

1）Hive 自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。2）当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数3）根据用户自定义函数类别分为以下三种：        --UDF（User-Defined-Function）                一进一出        --UDAF（User-Defined Aggregation Function）                用户自定义聚合函数，多进一出                类似于：count/max/min        --UDTF（User-Defined Table-Generating Functions）                用户自定义表生成函数，一进多出                如lateral view explode()4）官方文档地址https://cwiki.apache.org/confluence/display/Hive/HivePlugins5）编程步骤：        ①继承Hive提供的类           org.apache.hadoop.hive.ql.udf.generic.GenericUDF                  org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;        ②实现类中的抽象方法        ③在hive的命令行窗口创建函数                添加jar                        add jar linux_jar_path                创建function                        create [temporary] function [dbname.]function_name AS class_name;                ④在hive的命令行窗口删除函数                        drop [temporary] function [if exists] [dbname.]function_name;

1.4、自定义UDF函数

需求:自定义一个UDF实现计算给定字符串的长度，例如：        select my_len("abcd");        41）创建一个Maven工程Hive2）导入依赖                        org.apache.hive                hive-exec                3.1.2        

3）创建一个类/** * 一、自定义UDF函数，需要继承GenericUDF类 * 需求: 计算指定字符串的长度 */public class Mylength extends GenericUDF {    /**     * 二、初始化方法,里面要做三件事     * 1.约束函数传入参数的个数     * 2.约束函数传入参数的类型     * 3.约束函数返回值的类型     * @param arguments  函数传入参数的类型     * @return     * @throws UDFArgumentException     */    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {        //1.约束函数传入参数的个数        if (arguments.length != 1){            throw new UDFArgumentLengthException("Input args num error!!!");        }        //2.约束函数传入参数的类型        if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE))            //第一个参数：第几个数据            //第二个参数 ：错误信息            throw new UDFArgumentTypeException(0,"Input args type error!!!");        //3.约束函数返回值的类型        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;    }    /**     * 函数逻辑处理方法     * @param arguments  函数传入参数的值     * @return     * @throws HiveException     */    public Object evaluate(DeferredObject[] arguments) throws HiveException {        //获取函数传入参数的值        Object o = arguments[0].get();        //将object转换为字符串        int length = o.toString().length();        //因为在上面的初始化方法里面已经对函数返回值类型做了约束,必须返回一个int类型        //所以我们要在这个地方直接返回length        return length;    }    /**     * 返回显示字符串方法,这个方法不用管,直接返回一个空字符串     * @param children     * @return     */    public String getDisplayString(String[] children) {        return "";    }}

4）创建临时函数        ①打成jar包上传到服务器/opt/module/hive/datas/myudf.jar        ②将jar包添加到hive的classpath，临时生效                hive (default)> add jar /opt/module/hive/datas/myudf.jar;        ③创建临时函数与开发好的java class关联                hive (default)> create temporary function my_len as "com.atguigu.test.Mylength";        ④即可在hql中使用自定义的临时函数                hive (default)> select ename,my_len(ename) ename_len from emp;        ⑤删除临时函数                hive (default)> drop  temporary function my_len;--注意：临时函数只跟会话有关系，跟库没有关系。只要创建临时函数的会话不断，在当前会话下，任意一个库都可以使用，其他会话全都不能使用。

5）创建永久函数        ①在$HIVE_HOME下面创建auxlib目录(固定名称不能更改)                mkdir auxlib        ②将jar包上传到$HIVE_HOME/auxlib下，然后重启hive        ③创建永久函数                hive (default)> create function my_len2 as " com.atguigu.test.Mylength";        ④即可在hql中使用自定义的永久函数                 hive (default)> select ename,my_len2(ename) ename_len from emp;        ⑤删除永久函数                 hive (default)> drop function my_len2;--注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名

感谢你能够认真阅读完这篇文章，希望小编分享的"Hive函数怎么用"这篇文章对大家有帮助，同时也希望大家多多支持，关注行业资讯频道，更多相关知识等着你来学习!