Hive学习笔记三之函数操作

admin管理员组

文章数量:1026989

Hive学习笔记三之函数操作

文章目录

• • 5 函数
  • • 5.1 系统内置函数
    • 5.2 常用内置函数
    • • 5.2.1 空字段赋值
      • 5.2.2 CASE WHEN THEN ELSE END(类似于java中的switch case)
      • 5.2.3 行转列(多列合并成一列)
      • 5.2.4 列转行（一行转成多行）
      • 5.2.4 窗口函数（开窗函数）重点
      • 5.2.5：over函数练习：
      • 5.2.6 Rank
    • 5.3 自定义函数
    • • 5.3.1 自定义 UDF 函数
      • 5.3.2 自定义 UDTF 函数

5 函数

5.1 系统内置函数

1）查看系统自带的函数

hive> show functions;

2）显示自带的函数的用法

hive> desc function upper;

3）详细显示自带的函数的用法

hive> desc function extended upper;

数据准备：

5.2 常用内置函数

5.2.1 空字段赋值

1）函数说明
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。 2）数据准备：采用员工表
3）查询：如果员工的 comm 为 NULL，则用-1 代替

hive (default)> select comm,nvl(comm, -1) from emp;

4）查询：如果员工的 comm 为 NULL，则用领导 id 代替

hive (default)> select comm, nvl(comm,mgr) from emp;

5.2.2 CASE WHEN THEN ELSE END(类似于java中的switch case)

1. 需求：求出不同部门男女各多少人，结果如下
   

2. 创建本地 emp_sex.txt，导入数据
   

3. 创建 hive 表并导入数据

   create table emp_sex(
   name string, 
   dept_id string, 
   sex string) 
   row format delimited fields terminated by " ";load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;
   

4. 按需求查询数据

   selectdept_id,sum(case sex when '男' then 1 else 0 end) male_count,sum(case sex when '女' then 1 else 0 end) female_count
   from emp_sex
   group by dept_id;
   

5.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 类型字段。

2. 数据准备

   周嘉伟 白羊座 A
   吴文搏 射手座 A
   许龙 白羊座 B
   任逸飞 白羊座 A
   马骏 射手座 A
   肖智文 白羊座 B
   

3. 需求
   把星座和血型一样的人归类到一起。结果如下：
   

4. 创建本地 person_info.txt，导入数据：vim person_info.txt

5. 创建 hive 表并导入数据

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

6. 按需求查询数据

   SELECT
   t1.c_b,
   CONCAT_WS("|",collect_set(t1.name)) Name
   FROM (
   SELECT
   NAME,
   CONCAT_WS(',',constellation,blood_type) c_b
   FROM person_info
   )t1
   GROUP BY t1.c_b;
   

5.2.4 列转行（一行转成多行）

1. 函数说明：
   （1）EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
   （2）LATERAL VIEW
   用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
   解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2. 数据准备
   

3. 需求
   将电影分类中的数组数据展开。结果如下：

4. 创建本地 movie.txt，导入数据

   vi movie_info.txt
   

   《疑犯追踪》	悬疑,动作,科幻,剧情
   《Lie to me》	悬疑,警匪,动作,心理,剧情
   《战狼 2》	战争,动作,灾难
   

5. 创建 hive 表并导入数据

   create table movie_info(movie string,category string)
   row format delimited fields terminated by "\t";load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
   

6. 按需求查询数据

   SELECT
   movie,
   category_name
   FROM
   movie_info
   lateral VIEW
   explode(split(category,",")) movie_info_tmp AS category_name;
   

5.2.4 窗口函数（开窗函数）重点

使用背景：
平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了。
注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！

1. 函数说明：
   分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是： 对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
   基础结构：分析函数（如:sum(),max(),row_number()...） + 窗口子句（over函数）
   over函数写法： over（partition by cookieid order by createtime） 先根据cookieid字段分区，相同的cookieid分为一区，每个分区内根据createtime字段排序（默认升序）。
   注：加 partition by 的话则把整个数据集当作一个分区，不加 order by的话会对某些函数统计结果产生影响，如sum()

2. 测试数据
   
   建表语句：

    create table test1(cookieid string,createtime string,pv int) row format delimited fields terminated by ' ';
   

   数据：

   a 2017-12-01 3
   b 2017-12-00 3 
   cookie1 2017-12-10 1 
   cookie1 2017-12-11 5 
   cookie1 2017-12-12 7 
   cookie1 2017-12-13 3 
   cookie1 2017-12-14 2 
   cookie1 2017-12-15 4 
   cookie1 2017-12-16 4 
   cookie2 2017-12-12 7 
   cookie2 2017-12-16 6 
   cookie2 2017-12-24 1 
   cookie3 2017-12-22 5
   

3. 各种操作

   SELECT cookieid,createtime,pv,
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --当前行+往前3行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --当前行+往前3行+往后1行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---当前行+往后所有行  
   FROM test1;
   

   结果：

   关键是理解 ROWS BETWEEN 含义,也叫做window子句：
   PRECEDING：往前
   FOLLOWING：往后
   CURRENT ROW：当前行
   UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
   –其他AVG，MIN，MAX，和SUM用法一样

   其它的关键字：
   LAG(col,n,default_val)：往前第 n 行数据
   LEAD(col,n, default_val)：往后第 n 行数据
   **NTILE(n)：**把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

   注：
   (1) 这些窗口的划分都是在分区内部！超过分区大小就无效了
   (2)如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;如果不指定ORDER BY，则将分组内所有值累加

5.2.5：over函数练习：

1. 数据准备：

   name，orderdate，costjack,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
   

2. 需求
   （1）查询在 2017 年 4 月份购买过的顾客及总人数
   （2）查询顾客的购买明细及月购买总额
   （3）上述的场景, 将每个顾客的 cost 按照日期进行累加
   （4）查询每个顾客上次的购买时间
   （5）查询前 20%时间的订单信息

3. 创建本地 business.txt，导入数据

   vi business.txt
   

4. 创建 hive 表并导入数据

   create table business(
   name string,
   orderdate string,
   cost int
   ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';load data local inpath "/opt/module/hive/datas/business.txt" into table business;
   

5. 按需求查询数据
   (1)查询在 2017 年 4 月份购买过的顾客及总人数

   select name,count(*) over () 
   from business
   where substring(orderdate,1,7) = '2017-04'
   group by name;
   

   (2)查询顾客的购买明细及月购买总额

   select name,orderdate,cost,sum(cost) over(partition by month (orderdate)  ) from business order by orderdate;
   

   (3)将每个顾客的 cost 按照日期进行累加

   select name,orderdate,cost,
   sum(cost) over() as sample1,--所有行相加
   sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
   sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据累加
   sum(cost) over(partition by name order by orderdate rows between 
   UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合
   sum(cost) over(partition by name order by orderdate rows between 1 
   PRECEDING and current row) as sample5, --当前行和前面一行做聚合
   sum(cost) over(partition by name order by orderdate rows between 1 
   PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
   sum(cost) over(partition by name order by orderdate rows between current 
   row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
   from business;
   

   rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分
   (4)查看顾客上次的购买时间

   select name,orderdate,cost,
   lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
   as time1, lag(orderdate,2) over (partition by name order by orderdate) as 
   time2 
   from business;
   

   lag()函数在前面有讲过
   (5)查询前 20%时间的订单信息

   select * from (select name,orderdate,cost, ntile(5) over(order by orderdate) sortedfrom business
   ) t
   where sorted = 1;
   

5.2.6 Rank

1. 函数说明
   RANK() 排序相同时会重复，总数不会变 (例如 两个并列第3，则下一个直接第5)
   DENSE_RANK() 排序相同时会重复，总数会减少 （例如 两个并列第3，下一个还是4）
   ROW_NUMBER() 会根据顺序计算

2. 需求
   计算每门学科成绩排名。

3. 数据准备

   a 语文 87
   a 数学 95
   a 英语 68
   b 语文 94
   b 数学 56
   b 英语 84
   c 语文 64
   c 数学 86
   c 英语 84
   d 语文 65
   d 数学 86
   d 英语 78
   

4. 创建本地 score.txt，导入数据:
   vi score.txt

5. 创建 hive 表并导入数据

   create table score(
   name string,
   subject string, 
   score int) 
   row format delimited fields terminated by " ";
   load data local inpath '/opt/module/hive/datas/score.txt' into table score;
   

6. 按需求查询数据

   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) rmp
   from score;
   

   扩展：求出每门学科前三名的学生？

5.3 自定义函数

Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

根据用户自定义函数类别分为以下三种：

1. UDF（User-Defined-Function）
   一进一出
2. UDAF（User-Defined Aggregation Function）
   聚集函数，多进一出
   类似于：count/max/min
3. UDTF（User-Defined Table-Generating Functions）
   一进多出
   如 lateral view explode()

编程步骤：
（1）继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法
（3）在 hive 的命令行窗口创建函数
添加 jar
add jar linux_jar_path
创建 function

create [temporary] function [dbname.]function_name AS class_name;

（4）在 hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

5.3.1 自定义 UDF 函数

1. 需求
   自定义一个 UDF 实现计算给定字符串的长度，例如:

   hive(default)> select my_len("abcd"); 4
   

2. 创建一个 Maven 工程 Hive

3. 导入依赖

   <dependencies>
   <dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-exec</artifactId>
   <version>3.1.2</version>
   </dependency>
   </dependencies>
   

4. 创建一个类

   package com.zjw;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   /*** 自定义 UDF 函数，需要继承 GenericUDF 类* 需求: 计算指定字符串的长度*/
   public class MyStringLength extends GenericUDF {/**** @param arguments 输入参数类型的鉴别器对象* @return 返回值类型的鉴别器对象* @throws UDFArgumentException*/@Overridepublic ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {// 判断输入参数的个数if(arguments.length !=1){throw new UDFArgumentLengthException("Input Args Length Error!!!");}// 判断输入参数的类型if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");}//函数本身返回值为 int，需要返回 int 类型的鉴别器对象return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}/*** 函数的逻辑处理* @param arguments 输入的参数* @return 返回值* @throws HiveException*/@Overridepublic Object evaluate(DeferredObject[] arguments) throwsHiveException {if(arguments[0].get() == null){return 0;}return arguments[0].get().toString().length();}@Overridepublic String getDisplayString(String[] children) {return "";}}
   

5. 打成 jar 包上传到服务器/opt/module/hive/datas/myudf.jar
   
   

6. 将 jar 包添加到 hive 的 classpath

   hive (default)> add jar /opt/module/hive/datas/myudf.jar;
   

7. 创建临时函数与开发好的 java class 关联

   hive (default)> create temporary function my_len as "com.zjw.MyStringLength";
   

8. 在 hql 中使用自定义的函数

   hive (default)> select ename,my_len(ename) ename_len from emp;
   

5.3.2 自定义 UDTF 函数

1. 需求
   自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

   hive(default)> select myudtf("hello,world,hadoop,hive", ",");
   hello
   world
   hadoop
   

2. 代码实现

   package com.zjw;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
   import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   import java.util.ArrayList;
   import java.util.List;
   public class MyUDTF extends GenericUDTF {private ArrayList<String> outList = new ArrayList<>();@Overridepublic StructObjectInspector initialize(StructObjectInspector argOIs)throws UDFArgumentException {//1.定义输出数据的列名和类型List<String> fieldNames = new ArrayList<>();List<ObjectInspector> fieldOIs = new ArrayList<>();//2.添加输出数据的列名和类型fieldNames.add("lineToWord");fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);returnObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);}@Overridepublic void process(Object[] args) throws HiveException {//1.获取原始数据String arg = args[0].toString();//2.获取数据传入的第二个参数，此处为分隔符String splitKey = args[1].toString();//3.将原始数据按照传入的分隔符进行切分String[] fields = arg.split(splitKey);//4.遍历切分后的结果，并写出for (String field : fields) {//集合为复用的，首先清空集合outList.clear();//将每一个单词添加至集合outList.add(field);//将集合内容写出forward(outList);}}@Overridepublic void close() throws HiveException {}
   }
   

其它步骤类似于UDF

Hive相关文章：
Hive学习笔记之一hive的介绍与安装
Hive学习笔记二之表操作
Hive学习笔记三之函数操作
Hive学习笔记四之Hive实战

Hive学习笔记三之函数操作

文章目录

• • 5 函数
  • • 5.1 系统内置函数
    • 5.2 常用内置函数
    • • 5.2.1 空字段赋值
      • 5.2.2 CASE WHEN THEN ELSE END(类似于java中的switch case)
      • 5.2.3 行转列(多列合并成一列)
      • 5.2.4 列转行（一行转成多行）
      • 5.2.4 窗口函数（开窗函数）重点
      • 5.2.5：over函数练习：
      • 5.2.6 Rank
    • 5.3 自定义函数
    • • 5.3.1 自定义 UDF 函数
      • 5.3.2 自定义 UDTF 函数

5 函数

5.1 系统内置函数

1）查看系统自带的函数

hive> show functions;

2）显示自带的函数的用法

hive> desc function upper;

3）详细显示自带的函数的用法

hive> desc function extended upper;

数据准备：

5.2 常用内置函数

5.2.1 空字段赋值

1）函数说明
NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。 2）数据准备：采用员工表
3）查询：如果员工的 comm 为 NULL，则用-1 代替

hive (default)> select comm,nvl(comm, -1) from emp;

4）查询：如果员工的 comm 为 NULL，则用领导 id 代替

hive (default)> select comm, nvl(comm,mgr) from emp;

5.2.2 CASE WHEN THEN ELSE END(类似于java中的switch case)

1. 需求：求出不同部门男女各多少人，结果如下
   

2. 创建本地 emp_sex.txt，导入数据
   

3. 创建 hive 表并导入数据

   create table emp_sex(
   name string, 
   dept_id string, 
   sex string) 
   row format delimited fields terminated by " ";load data local inpath '/opt/module/hive/datas/emp_sex.txt' into table emp_sex;
   

4. 按需求查询数据

   selectdept_id,sum(case sex when '男' then 1 else 0 end) male_count,sum(case sex when '女' then 1 else 0 end) female_count
   from emp_sex
   group by dept_id;
   

5.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 类型字段。

2. 数据准备

   周嘉伟 白羊座 A
   吴文搏 射手座 A
   许龙 白羊座 B
   任逸飞 白羊座 A
   马骏 射手座 A
   肖智文 白羊座 B
   

3. 需求
   把星座和血型一样的人归类到一起。结果如下：
   

4. 创建本地 person_info.txt，导入数据：vim person_info.txt

5. 创建 hive 表并导入数据

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

6. 按需求查询数据

   SELECT
   t1.c_b,
   CONCAT_WS("|",collect_set(t1.name)) Name
   FROM (
   SELECT
   NAME,
   CONCAT_WS(',',constellation,blood_type) c_b
   FROM person_info
   )t1
   GROUP BY t1.c_b;
   

5.2.4 列转行（一行转成多行）

1. 函数说明：
   （1）EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
   （2）LATERAL VIEW
   用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
   解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

2. 数据准备
   

3. 需求
   将电影分类中的数组数据展开。结果如下：

4. 创建本地 movie.txt，导入数据

   vi movie_info.txt
   

   《疑犯追踪》	悬疑,动作,科幻,剧情
   《Lie to me》	悬疑,警匪,动作,心理,剧情
   《战狼 2》	战争,动作,灾难
   

5. 创建 hive 表并导入数据

   create table movie_info(movie string,category string)
   row format delimited fields terminated by "\t";load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
   

6. 按需求查询数据

   SELECT
   movie,
   category_name
   FROM
   movie_info
   lateral VIEW
   explode(split(category,",")) movie_info_tmp AS category_name;
   

5.2.4 窗口函数（开窗函数）重点

使用背景：
平常我们使用 hive或者 mysql时，一般聚合函数用的比较多。但对于某些偏分析的需求，group by可能很费力，子查询很多，这个时候就需要使用窗口分析函数了。
注：hive、oracle提供开窗函数，mysql8之前版本不提供，但Oracle发布的 MySQL 8.0版本支持窗口函数（over）和公用表表达式（with）这两个重要的功能！

1. 函数说明：
   分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是： 对于每个组返回多行，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
   基础结构：分析函数（如:sum(),max(),row_number()...） + 窗口子句（over函数）
   over函数写法： over（partition by cookieid order by createtime） 先根据cookieid字段分区，相同的cookieid分为一区，每个分区内根据createtime字段排序（默认升序）。
   注：加 partition by 的话则把整个数据集当作一个分区，不加 order by的话会对某些函数统计结果产生影响，如sum()

2. 测试数据
   
   建表语句：

    create table test1(cookieid string,createtime string,pv int) row format delimited fields terminated by ' ';
   

   数据：

   a 2017-12-01 3
   b 2017-12-00 3 
   cookie1 2017-12-10 1 
   cookie1 2017-12-11 5 
   cookie1 2017-12-12 7 
   cookie1 2017-12-13 3 
   cookie1 2017-12-14 2 
   cookie1 2017-12-15 4 
   cookie1 2017-12-16 4 
   cookie2 2017-12-12 7 
   cookie2 2017-12-16 6 
   cookie2 2017-12-24 1 
   cookie3 2017-12-22 5
   

3. 各种操作

   SELECT cookieid,createtime,pv,
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime) AS pv1, -- 默认为从起点到当前行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS pv2, --从起点到当前行，结果同pv1 
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv3,   --当前行+往前3行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS pv4,    --当前行+往前3行+往后1行
   SUM(pv) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS pv5   ---当前行+往后所有行  
   FROM test1;
   

   结果：

   关键是理解 ROWS BETWEEN 含义,也叫做window子句：
   PRECEDING：往前
   FOLLOWING：往后
   CURRENT ROW：当前行
   UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点
   –其他AVG，MIN，MAX，和SUM用法一样

   其它的关键字：
   LAG(col,n,default_val)：往前第 n 行数据
   LEAD(col,n, default_val)：往后第 n 行数据
   **NTILE(n)：**把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

   注：
   (1) 这些窗口的划分都是在分区内部！超过分区大小就无效了
   (2)如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;如果不指定ORDER BY，则将分组内所有值累加

5.2.5：over函数练习：

1. 数据准备：

   name，orderdate，costjack,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
   

2. 需求
   （1）查询在 2017 年 4 月份购买过的顾客及总人数
   （2）查询顾客的购买明细及月购买总额
   （3）上述的场景, 将每个顾客的 cost 按照日期进行累加
   （4）查询每个顾客上次的购买时间
   （5）查询前 20%时间的订单信息

3. 创建本地 business.txt，导入数据

   vi business.txt
   

4. 创建 hive 表并导入数据

   create table business(
   name string,
   orderdate string,
   cost int
   ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';load data local inpath "/opt/module/hive/datas/business.txt" into table business;
   

5. 按需求查询数据
   (1)查询在 2017 年 4 月份购买过的顾客及总人数

   select name,count(*) over () 
   from business
   where substring(orderdate,1,7) = '2017-04'
   group by name;
   

   (2)查询顾客的购买明细及月购买总额

   select name,orderdate,cost,sum(cost) over(partition by month (orderdate)  ) from business order by orderdate;
   

   (3)将每个顾客的 cost 按照日期进行累加

   select name,orderdate,cost,
   sum(cost) over() as sample1,--所有行相加
   sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
   sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据累加
   sum(cost) over(partition by name order by orderdate rows between 
   UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合
   sum(cost) over(partition by name order by orderdate rows between 1 
   PRECEDING and current row) as sample5, --当前行和前面一行做聚合
   sum(cost) over(partition by name order by orderdate rows between 1 
   PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
   sum(cost) over(partition by name order by orderdate rows between current 
   row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
   from business;
   

   rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分
   (4)查看顾客上次的购买时间

   select name,orderdate,cost,
   lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) 
   as time1, lag(orderdate,2) over (partition by name order by orderdate) as 
   time2 
   from business;
   

   lag()函数在前面有讲过
   (5)查询前 20%时间的订单信息

   select * from (select name,orderdate,cost, ntile(5) over(order by orderdate) sortedfrom business
   ) t
   where sorted = 1;
   

5.2.6 Rank

1. 函数说明
   RANK() 排序相同时会重复，总数不会变 (例如 两个并列第3，则下一个直接第5)
   DENSE_RANK() 排序相同时会重复，总数会减少 （例如 两个并列第3，下一个还是4）
   ROW_NUMBER() 会根据顺序计算

2. 需求
   计算每门学科成绩排名。

3. 数据准备

   a 语文 87
   a 数学 95
   a 英语 68
   b 语文 94
   b 数学 56
   b 英语 84
   c 语文 64
   c 数学 86
   c 英语 84
   d 语文 65
   d 数学 86
   d 英语 78
   

4. 创建本地 score.txt，导入数据:
   vi score.txt

5. 创建 hive 表并导入数据

   create table score(
   name string,
   subject string, 
   score int) 
   row format delimited fields terminated by " ";
   load data local inpath '/opt/module/hive/datas/score.txt' into table score;
   

6. 按需求查询数据

   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) rmp
   from score;
   

   扩展：求出每门学科前三名的学生？

5.3 自定义函数

Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

根据用户自定义函数类别分为以下三种：

1. UDF（User-Defined-Function）
   一进一出
2. UDAF（User-Defined Aggregation Function）
   聚集函数，多进一出
   类似于：count/max/min
3. UDTF（User-Defined Table-Generating Functions）
   一进多出
   如 lateral view explode()

编程步骤：
（1）继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法
（3）在 hive 的命令行窗口创建函数
添加 jar
add jar linux_jar_path
创建 function

create [temporary] function [dbname.]function_name AS class_name;

（4）在 hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

5.3.1 自定义 UDF 函数

1. 需求
   自定义一个 UDF 实现计算给定字符串的长度，例如:

   hive(default)> select my_len("abcd"); 4
   

2. 创建一个 Maven 工程 Hive

3. 导入依赖

   <dependencies>
   <dependency>
   <groupId>org.apache.hive</groupId>
   <artifactId>hive-exec</artifactId>
   <version>3.1.2</version>
   </dependency>
   </dependencies>
   

4. 创建一个类

   package com.zjw;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
   import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   /*** 自定义 UDF 函数，需要继承 GenericUDF 类* 需求: 计算指定字符串的长度*/
   public class MyStringLength extends GenericUDF {/**** @param arguments 输入参数类型的鉴别器对象* @return 返回值类型的鉴别器对象* @throws UDFArgumentException*/@Overridepublic ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {// 判断输入参数的个数if(arguments.length !=1){throw new UDFArgumentLengthException("Input Args Length Error!!!");}// 判断输入参数的类型if(!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){throw new UDFArgumentTypeException(0,"Input Args Type Error!!!");}//函数本身返回值为 int，需要返回 int 类型的鉴别器对象return PrimitiveObjectInspectorFactory.javaIntObjectInspector;}/*** 函数的逻辑处理* @param arguments 输入的参数* @return 返回值* @throws HiveException*/@Overridepublic Object evaluate(DeferredObject[] arguments) throwsHiveException {if(arguments[0].get() == null){return 0;}return arguments[0].get().toString().length();}@Overridepublic String getDisplayString(String[] children) {return "";}}
   

5. 打成 jar 包上传到服务器/opt/module/hive/datas/myudf.jar
   
   

6. 将 jar 包添加到 hive 的 classpath

   hive (default)> add jar /opt/module/hive/datas/myudf.jar;
   

7. 创建临时函数与开发好的 java class 关联

   hive (default)> create temporary function my_len as "com.zjw.MyStringLength";
   

8. 在 hql 中使用自定义的函数

   hive (default)> select ename,my_len(ename) ename_len from emp;
   

5.3.2 自定义 UDTF 函数

1. 需求
   自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

   hive(default)> select myudtf("hello,world,hadoop,hive", ",");
   hello
   world
   hadoop
   

2. 代码实现

   package com.zjw;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
   import org.apache.hadoop.hive.ql.metadata.HiveException;
   import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
   import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
   import java.util.ArrayList;
   import java.util.List;
   public class MyUDTF extends GenericUDTF {private ArrayList<String> outList = new ArrayList<>();@Overridepublic StructObjectInspector initialize(StructObjectInspector argOIs)throws UDFArgumentException {//1.定义输出数据的列名和类型List<String> fieldNames = new ArrayList<>();List<ObjectInspector> fieldOIs = new ArrayList<>();//2.添加输出数据的列名和类型fieldNames.add("lineToWord");fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);returnObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,fieldOIs);}@Overridepublic void process(Object[] args) throws HiveException {//1.获取原始数据String arg = args[0].toString();//2.获取数据传入的第二个参数，此处为分隔符String splitKey = args[1].toString();//3.将原始数据按照传入的分隔符进行切分String[] fields = arg.split(splitKey);//4.遍历切分后的结果，并写出for (String field : fields) {//集合为复用的，首先清空集合outList.clear();//将每一个单词添加至集合outList.add(field);//将集合内容写出forward(outList);}}@Overridepublic void close() throws HiveException {}
   }
   

其它步骤类似于UDF

Hive相关文章：
Hive学习笔记之一hive的介绍与安装
Hive学习笔记二之表操作
Hive学习笔记三之函数操作
Hive学习笔记四之Hive实战

本文标签： Hive学习笔记三之函数操作