Hive查询操作

Dear 丶 2023-10-13 11:12 31阅读 0赞

![fc80594448210d26c24ba7f095efb6e7.jpeg][]

### Hive基本查询语法 ###

#### 基本使用规则 ####

##### 基本查询语句组成 #####

select ..
    from ..
        join [tableName] on ..
        where ..
        group by ..
        having ..
        order by ..
        sort by ..
        limit ..
    union | union all ...

##### 执行顺序 #####

第一步: FROM <left_table>
    第二步: ON <join_condition>
    第三步: <join_type> JOIN <right_table>
    第四步: WHERE <where_condition>
    第五步: GROUP BY <group_by_list>
    第六步: HAVING <having_condition>
    第七步: SELECT
    第八步: DISTINCT <select_list>
    第九步: ORDER BY <order_by_condition>
    第十步: LIMIT <limit_number>
    
    标准sql语句的一些规则：
    -1. 列别名的使用，必须完全符合执行顺序，不能提前使用。（mysql除外）
    -2. 在分组查询时，select子句中只能含有分组字段和聚合函数，不能有其他普通字段。(mysql除外)

##### 查询原则 #####

1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代)
    2. 尽量避免join连接查询，但是通常避免不了
    3. 查询永远是小表驱动大表（小表作为驱动表）
      --注意：内连接时，默认是左表是驱动表，因此左表一定要是小表。
      --	 外连接看需求而定。

#### 常用子句回顾 ####

##### where语句特点 #####

where后不能使用聚合函数，可以使用子查询，也可以是普通函数。
    条件可以是：
    1. 关系表达式： =, >,>=,<,<=,!=,<>
    2. 连接符号：  or，and, between .. and ..
    3. 模糊查询： like   
                 %:通配符
                 _:占位符
    4. [not] in
        >all(set)  >any();
    
    注意事项：在hive的where中如果使用了子查询作为条件，等号“=”不好使，需要使用[not] in.
            换句话说，即使子查询返回的是唯一的一个值，也是集合形式。

group by语句特点

group by: 分组，通常和聚合函数搭配使用
    
    查询的字段要么出现在group by 后面，要么出现在聚合函数里面
    
    聚合函数：count(),sum(),max(),min(),avg()
    
    count的执行
    1. 执行效果上：
        - count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值
        - count(1)包括了所有列，用1代表行，在统计结果的时候也不会忽略null值
        - count(列名)只包括列名那一列，在统计结果时，会忽略null值
    
    2.执行效率上：
        - 列名为主键，count(列名)会比count(1)快
        - 列名不为主键，count(1)会比count(列名)快
        - 如果表中有多个列并且没有主键，count（1）的效率高于count(*)
        - 如果有主键count(主键)效率是最高的
        - 如果表中只有一个字段count(*)效率最高

##### having子句特点 #####

对分组以后的结果集进行过滤。可以使用聚合函数。

##### order by子句 #####

对查询的数据进行排序。
    desc 降序
    asc	 升序
    
    语法：
    order by colName [desc|asc][,colName [desc|asc]]

limit语句特点

limit ： 从结果集中取数据的条数
    将set hive.limit.optimize.enable=true 时，limit限制数据时就不会全盘扫描，而是根据限制的数量进行抽样。
    
    同时还有两个配置项需要注意：
    hive.limit.row.max.size=100000        这个是控制最大的抽样数量【默认值是100000】
    hive.limit.optimize.limit.file=10 这个是抽样的最大文件数量【默认值是10个】
    缺点：可能输入中有用的数据永远都不会被抽样到。
    
    注意：limit 在mysql中 可以有两个参数 limit [m,] n
               在hive中，只能有一个参数 limit n;  查询前n条。
          一般情况下，在使用limit时，都会先order by排序。

#### join连接 ####

##### join知识点回顾 #####

有的业务所需要的数据，不是在一张表中，通常会存在多张表中，而这些表中通常应该会存在"有关系"的字段。多表查询时，使用关联字段"连接"（join）在一起，组合成一个新的数据集，就是连接查询。

连接查询操作分为两大类：内连接和外连接，而外连接有细分为三种类型。参考下图

1. 内连接:  [inner] join
    2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
      - 左外连接:left [outer] join, 左表是驱动表
      - 右外连接:right [outer] join, 右表是驱动表
      - 全外连接:full [outer] join, hive支持，mysql不支持.两张表里的数据全部显示出来
    3. 注意： join连接只支持等值连接

![66c694515a3fdb374305168ef579bdd0.png][]

![a111a7b45d46e831d4d863204443d361.png][]

![0f5e3752af621e8711c9f415e834e963.png][]

需要大家注意的是，两张表的关联字段的值往往是不一致的。比如，表 A 包含张三和李四，表 B 包含李四和王五，匹配的只有李四这一条记录。从上图很容易看出，一共有四种处理方式和结果。下图就是四种连接的图示，这张图比上面的维恩图更易懂，也更准确。

![9d36d761dcc465cfb4c0e111a4cb4ed3.png][]

join

上图中，表 A 的记录是 123，表 B 的记录是 ABC，颜色表示匹配关系。返回结果中，如果另一张表没有匹配的记录，则用 null 填充，这四种连接，又可以分成两大类：内连接（inner join）表示只包含匹配的记录，外连接（outer join）表示还包含不匹配的记录。所以，左连接、右连接、全连接都属于外连接。

**笛卡尔积**

指的是表 A 和表 B 不存在关联字段，这时表 A（共有 n 条记录）与表 B （共有 m 条记录）连接后，会产生一张包含 n x m 条记录(笛卡尔积)的新表。

![ff1fb77b20c51b987274636974e4f200.png][]

cross

案例演示：

准备数据
    u1文件中的数据如下：
    1,a
    2,b
    3,c
    4,d
    7,y
    8,u
    
    u2文件中的数据如下：
    2,bb
    3,cc
    7,yy
    9,pp
    
    create table if not exists u1(
    id int,
    name string
    )
    row format delimited 
    fields terminated by ','
    ;
    
    create table if not exists u2(
    id int,
    name string
    )
    row format delimited fields terminated by ','
    ;
    
    load data local inpath './data/u1.txt' into table u1;
    load data local inpath './data/u2.txt' into table u2;

##### left semi join #####

在hive中，有一种专有的join操作,left semi join,我们称之为半开连接。它是left join的一种优化形式，只能查询左表的信息，主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

先回顾exists关键字的用法：exists关键字：满足条件返回true,不满足条件返回false

练习:  
    查询有领导的员工信息
          select * from emp where mgr is not null
          select * from emp A where exists (select 1 from emp B where B.empno = A.mgr )	
          select * from emp A left semi join emp B on A.mgr = B.empno;
          
    查询有下属的员工信息
          select * from emp A where exists (select 1 from emp B where B.mgr = A.empno )
          
    查看有部门的所有员工的信息
          select * from emp A where exists (select 1 from dept B where B.deptno = A.deptno )

**left semi join**的写法。

-- 左外连接，左表中的数据全部返回
    select * from u1 left join u2 on u1.id =u2.id;
    select * from u1 left outer join u2 on u1.id =u2.id;
    -- 左半开连接，只显示左表中满足条件的数据。和exists的逻辑是相同的
    select * from u1 left semi join u2 on u1.id =u2.id;
    -- exists的写法
    select * from u1 where exists (select 1 from u2 where u2.id =u1.id);
    
    --验证left semi join 是否可以显示第二张表的信息：错误写法。
    select A.*, B.* from u1 A left semi join u2 B on A.id =B.id;

**注意**： hive中不支持right semi join

### **更多大数据精彩内容欢迎B站搜索“[千锋教育][Link 1]”或者扫码领取全套资料** ###

【千锋教育】大数据开发全套教程，史上最全面的大数据学习视频

[fc80594448210d26c24ba7f095efb6e7.jpeg]: https://img-blog.csdnimg.cn/img_convert/fc80594448210d26c24ba7f095efb6e7.jpeg
[66c694515a3fdb374305168ef579bdd0.png]: https://img-blog.csdnimg.cn/img_convert/66c694515a3fdb374305168ef579bdd0.png
[a111a7b45d46e831d4d863204443d361.png]: https://img-blog.csdnimg.cn/img_convert/a111a7b45d46e831d4d863204443d361.png
[0f5e3752af621e8711c9f415e834e963.png]: https://img-blog.csdnimg.cn/img_convert/0f5e3752af621e8711c9f415e834e963.png
[9d36d761dcc465cfb4c0e111a4cb4ed3.png]: https://img-blog.csdnimg.cn/img_convert/9d36d761dcc465cfb4c0e111a4cb4ed3.png
[ff1fb77b20c51b987274636974e4f200.png]: https://img-blog.csdnimg.cn/img_convert/ff1fb77b20c51b987274636974e4f200.png
[Link 1]: https://space.bilibili.com/146668655?spm_id_from=333.337.0.0