Hive

hive

1. 查询

   1. 全表查询 selec * from table
   2. 在查询字段后使用 name as new_name可以重命名一个列

2. 算术运算符

   A+B	A 和 B 相加
   A-B	A 减去 B
   A*B	A 和 B 相乘
   A/B	A 除以 B
   A%B	A 对 B 取余
   A&B	A 和 B 按位取与
   A|B	A 和 B 按位取或
   A^B	A 和 B 按位取异或
   ~A	A 按位取反

3. 常用函数

   1. count():求总行数
   2. max():最大值
   3. min():最小值
   4. sum():总和
   5. avg():平均值

4. Limit ：查询数据限制返回的行数

5. where ： 数据过滤，将不符合条件的数据过滤掉

6. 比较运算符

   操作符	支持的数据类型	描述
   A=B	基本数据类型	如果 A 等于 B 则返回 TRUE，反之返回 FALSE
   A<=>B	基本数据类型	如果 A 和 B 都为 NULL，则返回 TRUE，如果一边为 NULL， 返回 False
   A<>B, A!=B	基本数据类型	A 或者 B 为 NULL 则返回 NULL；如果 A 不等于 B，则返回 TRUE，反之返回 FALSE
   A<B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 小于 B，则返回 TRUE，反之返回 FALSE
   A<=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 小于等于 B，则返 回 TRUE，反之返回 FALSE
   A>B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 大于 B，则返回 TRUE，反之返回 FALSE
   A>=B	基本数据类型	A 或者 B 为 NULL，则返回 NULL；如果 A 大于等于 B，则返 回 TRUE，反之返回 FALSE
   A [NOT] BETWEEN B AND C	基本数据类型	如果 A，B 或者 C 任一为 NULL，则结果为 NULL。如果 A 的 值大于等于 B 而且小于或等于 C，则结果为 TRUE，反之为 FALSE。 如果使用 NOT 关键字则可达到相反的效果。
   A IS NULL	所有数据类型	如果 A 等于 NULL，则返回 TRUE，反之返回 FALSE
   A IS NOT NULL	所有数据类型	如果 A 不等于 NULL，则返回 TRUE，反之返回 FALSE
   IN(数值 1, 数值 2)	所有数据类型	使用 IN 运算显示列表中的值
   A [NOT] LIKE B	STRING 类型	B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如 果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式 说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以 位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到 相反的效果。
   A RLIKE B, A REGEXP B	STRING 类型	B 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口

7. like和rlike

   1. like

      1. % 代表零个或多个字符(任意个字符)。

      2. _ 代表一个字符。

      3. 实例 ：查找名字以 A 开头的员工信息

         select * from emp where ename LIKE 'A%';

   2. rlike

      1. 实例：查找名字中带有 A 的员工信息

         select * from emp where ename RLIKE '[A]';

8. 逻辑运算符

   1. and ：逻辑并
   2. or ： 逻辑或
   3. not ： 逻辑非

9. 分组

   1. Group by

      1. 按一个或多个列对数据进行分组，然后对每个组进行聚合操作。

      2. 使用group by 语句，select语句中出现的字段，都必须在group by 语句中

      3. 实例：计算 emp 表每个部门的平均工资

         select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;

   2. Having

      1. having 和 where 的区别

         （1）where 后面不能写分组函数，而 having 后面可以使用分组函数。

         （2）having 只用于 group by 分组统计语句。

         （3）having可以对group by 后的聚合字段进行操作

      2. 实例：求每个部门的平均薪水大于 2000 的部门

         select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

10. join

    1. 等值join（内连接）

       只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来

    2. 左外连接

       JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

    3. 右外连接

       JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

    4. 满外连接

       将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字 段没有符合条件的值的话，那么就使用 NULL 值替代。

    5. 笛卡尔积

       1. 笛卡尔积的产生条件
          1. 省略连接条件
          2. 连接条件无效
          3. 所有表中的所有行互相连接

11. 排序

    1. 全局排序（order by）

       1. 全局排序只有一个reduce

       2. ASC（ascend）: 升序（默认）

          DESC（descend）: 降序

       3. 可按多个列排序

       4. 实例：查询员工信息按工资降序排列

          select * from emp order by sal desc

    2. reduce内部排序（sort by）

       1. Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集 来说不是排序。

       2. 实例：根据部门编号降序查看员工信息

          select* from emp sort by deptno desc

    3. 分区(distribute by)

       1. 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为 了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition

          （自定义分区），进行分区，结合 sort by 使用.

       2. 实例：先按照部门编号分区，再按照员工编号降序排序

          select * from emp distribute by deptno sort by empno desc

    4. cluster by

       1. 当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

       2. cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序 排序，不能指定排序规则为 ASC 或者 DESC。

       3. 实例：

          select* from emp cluster by deptno

12. 分区表

    1. 分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所 有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据 集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率 会提高很多。

    2. 创建语法：在创建表时加上 partitioned by（字段名 类型）。分区字段不能是表中已经存在的数据字段，可以将分区字段看作表的伪列

    3. 加载数据到分区表：分区表在加载数据时需在最后指定要添加数据的分区

    4. 查询分区表数据：将分区字段当作普通字段使用即可

    5. 增加分区：语法：

       alter table dept_partition add partition(day='20200405') partition(day='20200406');

       可同时添加多个分区， 分区间用空格隔开

    6. 删除分区：语法：

       alter table dept_partition drop partition (day='20200404'), partition(day='20200405');

       可同时删除多个分区，分区间用逗号隔开

    7. 查看表有多少个分区和分区表结构

       语法：show partitions dept_partition;

       desc formatted dept_partition;

    8. 二级分区

       1. 在创建表时加上 partitioned by（字段名1 类型，字段名2 类型，…）。

    9. 把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

       1. 上传数据后修复

          执行修复命令

          msck repair table dept_partition2;

       2. 上传数据后添加分区

          上传完数据后，使用添加分区的方法将上传目录添加成新的分区

       3. 创建文件夹后 load 数据到分区

          通过load命令对load数据进行指定分区操作

13. 分桶表

    1. 分桶是将数据集分解成更容易管理的若干部分的另一个技术。 分区针对的是数据的存储路径；分桶针对的是数据文件。

    2. 创建分桶表,在创建表示通过clustered by（字段）语句进行分桶表的创建，指定字段必须是表中已存在的数据字段

    3. 分桶规则

       Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方 式决定该条记录存放在哪个桶当中

14. 抽样查询

    1. 对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结 果。Hive 可以通过对表进行抽样来满足这个需求。

    2. 语法：tablesample（bucket x out of y）

       x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

       注意：x的值必须小于等于y的值，否则

       FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

15. 函数

    1. 系统内置函数

       1. 空字段赋值

          语法：NVL（ value，default_value）。它的功能是如 果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数 都为 NULL ，则返回 NULL。

       2. case when then else end

          case 字段 when 值 then 值 then 值 else 值 end

          case 选取字段 与when后的值进行判断，结果为true则返回then后的值否则返回else后的值，类似于switch case

          实例：

          select dept_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;
          

16. 行转列

    1. CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字 符串;

    2. CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参 数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将 为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接 的字符串之间;注意: CONCAT_WS must be "string or array

    3. COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重 汇总，产生 Array 类型字段。

    4. 实例：数据：

       name	constellation	blood_type
       孙悟空	白羊座	A
       大海	射手座	A
       宋宋	白羊座	B
       猪八戒	白羊座	A
       凤姐	射手座	A
       苍老师	白羊座	B

       需求：

       [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EYY3jVcA-1616401917030)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210205184719592.png)]

       代码：

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

17. 列转行

    1. EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

       LATERAL VIEW

       用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

       解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。

    2. 数据：

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

    3. 需求：

       [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gC66UxD2-1616401917038)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210205184840704.png)]

    4. 代码：

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

18. 窗口函数

    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 行数据

       NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对 于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

19. rank

    1. RANK() 排序相同时会重复，总数不会变
    2. DENSE_RANK() 排序相同时会重复，总数会减少
    3. ROW_NUMBER() 会根据顺序计算

20. 添加自定义函数

    hive命令行 add jar jar_path

    创建函数

    create [temporary] function [if exists] function_name as class_name

    删除函数drop [temporary] function [if exists] function_name

21. 自定义UDF

    1. 创建Maven工程
    2. 创建java类继承GenericUDF类，重写方法
    3. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-06f0ceMf-1616401917040)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180543975.png)]
    4. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1OSuSy5j-1616401917042)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180621338.png)]
    5. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HNdK2QMp-1616401917044)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180645184.png)]

22. 自定义UDTF

    1. 继承类GenericUDTF
    2. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-v6rUW10m-1616401917045)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180754853.png)]
    3. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4MYKAx21-1616401917046)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180822119.png)]
    4. [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gpQ3x4wG-1616401917047)(C:\Users\13918\AppData\Roaming\Typora\typora-user-images\image-20210207180834555.png)]

23. 练习

    1. 数据表结构

       视频表

       字段	备注	详细描述
       videoId	视频唯一 id（String）	11 位字符串
       uploader	视频上传者（String）	上传视频的用户名 String
       age	视频年龄（int）	视频在平台上的整数天
       category	视频类别（Array）	上传视频指定的视频分类
       length	视频长度（Int）	整形数字标识的视频长度
       views	观看次数（Int）	视频被浏览的次数
       rate	视频评分（Double）	满分 5 分
       Ratings	流量（Int）	视频的流量，整型数字
       conments	评论数（Int）	一个视频的整数评论数
       relatedId	相关视频 id（Array）	相关视频的 id，最多 20 个

       用户表

       字段	备注	字段类型
       uploader	上传者用户名	string
       videos	上传视频数	int
       friends	朋友数量	int

    2. 统计视频观看数Top10

       思路：使用 order by 按照 views 字段做一个全局排序即可，同时我们设置只显示前 10

       条。

       代码

       select * from gulivideo order by views limit 10;
       

    3. 统计视频类别热度 Top10

       思路：选出视频id和类型并将类型炸裂开

       select 
           videoId,
           videotype
       from gulivideo
       lateral view explode(category) type_tmp as videotype;t1
       
       

       再根据t1按类型分组统计再排序选出前十

       select 
           t1.videotype,
           count(*) as num
       from 
           (select 
           videoId,
           videotype
       from gulivideo
       lateral view explode(category) type_tmp as videotype)t1
       group by t1.videotype
       order by num desc
       limit 10;
       

    4. 统计出视频观看数最高的 20 个视频的所属类别以及类别包含Top20 视频的个数

       思路：先选出观看数最高的20个视频

       select 
          videoId,
          category,
          views
       from 
           gulivideo
       order by 
          views
          desc
       limit 20;t1
       

       一个视频对应多个类型，将类型炸裂开

       select 
           videoId,
           category_name
       from 
           (select 
          videoId,
          category,
          views
       from 
           gulivideo
       order by 
          views
          desc
       limit 20)t1
       lateral view explode(category) category_tmp 
       as category_name;t2
       

       按照类别进行统计

       select
           category_name,
           count(*) num
       from 
           (select 
           videoId,
           category_name
       from 
           (select 
          videoId,
          category,
          views
       from 
           gulivideo
       order by 
          views
          desc
       limit 20)t1
       lateral view explode(category) category_tmp 
       as category_name)t2
       group by 
           category_name;
       

    5. 统计视频观看数 Top50 所关联视频的所属类别排序

       思路：先选出观看数前50视频的关联视频

       select 
           views,
           relatedId
       from 
           gulivideo
       order by views desc
       limit 50;t1
       

       将关联视频id炸裂开

       select
           id
       from (select 
           views,
           relatedId
       from 
           gulivideo
       order by views desc
       limit 50)t1
       lateral view explode(t1.relatedId) relatedId_tmp 
       as id;t2
       

       将炸裂出来的id和原表进行join

       select
           g.videoId,
           g.category
       from
           (select
           id
       from (select 
           views,
           relatedId
       from 
           gulivideo
       order by views desc
       limit 50)t1
       lateral view explode(t1.relatedId) relatedId_tmp 
       as id)t2
       join
           gulivideo g
       on 
           t2.id = g.videoId;t3
       

       将关联后的表对类型进行炸裂

       select
           videoId,
           category_name
       from 
          (select
           g.videoId,
           g.category
       from
           (select
           id
       from (select 
           views,
           relatedId
       from 
           gulivideo
       order by views desc
       limit 50)t1
       lateral view explode(t1.relatedId) relatedId_tmp 
       as id)t2
       join
           gulivideo g
       on 
           t2.id = g.videoId)t3
       lateral view explode(category) category_tmp 
       as category_name;t4
       

       按炸裂后的类型进行统计排序

       select 
           category_name,
           count(*) num
       from 
           (select
           videoId,
           category_name
       from 
          (select
           g.videoId,
           g.category
       from
           (select
           id
       from (select 
           views,
           relatedId
       from 
           gulivideo
       order by views desc
       limit 50)t1
       lateral view explode(t1.relatedId) relatedId_tmp 
       as id)t2
       join
           gulivideo g
       on 
           t2.id = g.videoId)t3
       lateral view explode(category) category_tmp 
       as category_name)t4
       group by 
           category_name
       order by num;
       

    6. 统计每个类别中的视频热度 Top10，以 Music 为例

       SELECT
       t1.videoId, t1.views, t1.category_name
       FROM (
       SELECT
       videoId, views, category_name
       FROM gulivideo_orc
       lateral VIEW explode(category) gulivideo_orc_tmp AS category_name
       )t1 WHERE
       t1.category_name = "Music" ORDER BY
       t1.views DESC
       LIMIT 10;
       

    7. 统计每个类别视频观看数 Top10

       思路：取出视频id，观看数和类别并将类别炸开

       select
          videoId,
          views,
          category_name
       from 
          gulivideo
       lateral view explode(category) category_tmp
       as category_name;t1
       

       按照类别分组按视频播放量排序添加排名

       select 
          category_name,
          videoId,
          row_number()
          over(partition by category_name 
              order by views desc) as hot
       from 
          (select
          videoId,
          views,
          category_name
       from 
          gulivideo
       lateral view explode(category) category_tmp
       as category_name)t1;t2
       

       选取每个类别排名前十的视频

       select 
           category_name,
           videoId,
           t2.hot
       from
           (select 
          category_name,
          videoId,
          row_number()
          over(partition by category_name 
              order by views desc) as hot
       from 
          (select
          videoId,
          views,
          category_name
       from 
          gulivideo
       lateral view explode(category) category_tmp
       as category_name)t1)t2
       where
           t2.hot <= 10;
       

    8. 统计上传视频最多的用户 Top10 以及他们上传的视频 观看次数在前 20 的视频

       思路：先选取上传视频数前十的用户

       select
          uploader,
          videos
       from
          gulivideo_user_ori
       order by videos
       desc
       limit 10;t1
       

       将t1表和gulivideo表按用户名关联，按观看次数排序取前20

       select 
           t1.uploader,
           g.views,
           g.videoId
       from
           (select
          uploader,
          videos
       from
          gulivideo_user_ori
       order by videos
       desc
       limit 10)t1
       join
           gulivideo g
       on 
           t1.uploader = g.uploader
       order by g.views desc
       limit 20;