HIVE SQL 中 HQL 语句理解

简介:

     HQL DQL指的是 数据查询语句, 主要是对 表数据进行查询操作的.

和mysql对比:

MySQL中 单表查询语句 完整格式如下:
    select distinct 列1, 列2... from 表名
    where 组前筛选
    group by 分组字段
    having 组后筛选
    order by 排序字段 [asc/desc]
    limit 起始索引, 数据条数;

Hive中 单表查询语句 完整格式如下:
    [CTE表达式]
    select distinct | all 列1,列2,....  from 表名

    where 组前筛选

    group by 分组字段

    having  组后筛选

    order by 排序字段 [asc | desc] 

    cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段

    limit 起始字段, 数据条数; 

HQL 和 MySQl的不同:

        1.HQL 可以支持 CTE表达式.

        2.HQL 筛选的时候可以写 all 或者 distinct

        3.HQL 支持分桶查询

 注意:

        distribute by 表示分桶, sort by表示桶内排序, 如果 分桶字段 和 排序字段是同一个字段, 则可以用cluster by实现.
        即: cluster by 分桶排序字段 = distribute by 分桶字段 + sort by 桶内排序字段

聚合, 分组查询:

-- 统计未支付、已支付各自的人数
select
       -- 方式1: case when 标准写法.
       case
           when isPay=0 then '未支付'
           when isPay=1 then '已支付'
       end isPay1,

       -- 方式2: case when 简化写法, 适用于 等于的判断
        case isPay
           when 0 then '未支付'
           when 1 then '已支付'
       end isPay2,

       count(orderNo) total_cnt
from orders group by isPay;

例:

-- 统计每个用户的平均订单消费额，过滤大于10000的数据
-- 细节: 因为 round() 对 avg()做了处理, 已经不是纯聚合函数了, 所以该字段(别名)放到having后报错.
-- 解决方案1: 直接把处理聚合函数的动作, 再写一份放到 having后, 即: 不采用别名的方式.
select
       userId,
       round(avg(realTotalMoney), 2) realTotalMoney_avg
from
     orders
group by
    userId
having round(avg(realTotalMoney), 2) > 10000;

-- 方式2: 子查询.
select * from (
    select
       userId,
       round(avg(realTotalMoney), 2) realTotalMoney_avg
    from
         orders
    group by
        userId
) t1 where realTotalMoney_avg > 10000;

-- 方式3: CTE表达式, 把结果临时用CTE存储, 然后再次查询.
with t1 as (
     select
       userId,
       round(avg(realTotalMoney), 2) realTotalMoney_avg
    from
         orders
    group by
        userId
)
select * from t1 where realTotalMoney_avg > 10000;

hive 中连接查询:

-- 3. hive中的连接查询(join)
-- 3.1 连接查询, inner join, 结果: 表的交集.
-- 显示内连接
select * from employee e1 inner join employee_address e2 on e1.id = e2.id;
-- inner 可以省略不写.
select * from employee e1 join employee_address e2 on e1.id = e2.id;

-- 隐式内连接
select * from employee e1, employee_address e2 where e1.id = e2.id;

-- 3.2 左外连接查询, left outer join, 结果: 左表全集 + 表的交集
select * from employee e1 left outer join employee_address e2 on e1.id = e2.id;
-- outer可以省略不写
select * from employee e1 left join employee_address e2 on e1.id = e2.id;

-- 3.3 右外连接查询, right outer join, 结果: 右表全集 + 表的交集
select * from employee e1 right outer join employee_address e2 on e1.id = e2.id;
-- outer可以省略不写
select * from employee e1 right join employee_address e2 on e1.id = e2.id;

-- 3.4 交叉连接查询, cross join, 结果: 表的笛卡尔积, 无意义, 了解即可, 一般不用.         以上四种, 和MySQL一样.
-- 笛卡尔积: 表A的总条数 * 表B的总条数
select * from employee e1 cross join employee_address e2;
select * from employee e1, employee_address e2;         -- 交叉查询的, 简化写法

-- 3.5 满外连接, full outer join, 结果: 左表全集 + 右表全集 + 表交集, 即: 满外连接 等价于 左外连接 + 右外连接
select * from employee e1 full outer join employee_address e2 on e1.id = e2.id;
-- outer可以省略不写
select * from employee e1 full join employee_address e2 on e1.id = e2.id;

-- 3.6 左半连接, left semi join, 结果: 表的交集, 只保留左表部分.
select * from employee e1 left semi join employee_address e2 on e1.id = e2.id;

分桶查询

分桶查询相关:
    1. 分桶 = 分文件, 如果创建分桶表, 添加数据的时候, 数据会根据 哈希取模法, 放到不同的桶(文件)中.
    2. 如果是分桶查询, 类似于 分组查询, 就是按照一定的条件, 把相同的数据(同一个桶的数据)放到一起的过程.
    3. 分桶查询格式如下:
        cluster by 分桶排序字段 = distribute by 分桶字段 + sort by 排序字段
    4. mapreduce.job.reduces参数的值默认是-1, 即: MR程序会根据任务量自动决定分成多少个桶,
       如果要手动指定桶的个数, 必须通过 set mapreduce.job.reduces = n; 参数实现.  

-- 1. 查询表数据, 这里我们用的是普通表 stu, 一样可以达到 分桶查询的目录, 因为分桶查询 类似于 分组查询, 就是把相同的数据放到一起而已.
select * from stu;

-- 2. 根据性别分桶查询, 分成2个桶.
select * from stu cluster by gender;

-- 3. 根据id分桶查询, 分成3个桶.
select * from stu cluster by id;        -- 没有达到分桶效果, 需要手动设置 reduce个数, 因为: 1个MR的分区 = 1个Hive的分桶 = 1个ReduceTask任务 = 1个结果文件.

-- 4. 解决上述的问题.
-- 手动设置ReduceTask任务数, 相当于设置了 桶的数量.
set mapreduce.job.reduces = 3;
-- 分桶查询.
select * from stu cluster by id;    -- 根据id分桶, 根据id排序(升序)

-- 根据id分桶, 根据年龄降序
select * from stu distribute by id sort by age desc;

-- 根据id分桶, 根据id升序
select * from stu distribute by id sort by id;      -- 等价于: select * from stu cluster by id;
-- select * from stu distribute by id sort by id desc order by name ;      -- 报错, sort by 和 order by冲突, 不能一起使用.

思考:

cluster by, distribute by, sort by, order by 它们之间的区别是什么?

答:   分桶查询 类似于 分组查询, 就是把相同的数据放到一起而已.

        distribute by 分桶 ,sort by 排序, 要是分桶字段和排序字段相同, 就相当于cluster by.

        sort by 分桶后排序, 而order by 属于全局排序,所以sort by 和 order by 冲突,不能一直使用.

 联合查询

  

联合查询解释:
    概述:
        联合查询指的是 union 查询, 目的: 达到类似于拼接表的操作, 把多张表拼接到一起.
    格式:
        select ... from ...
        union all / distinct
        select ... from ...
注意:
        1. 如果直接写union, 后边啥都不写, 默认是: union distinct
        2. union all是合并, 但是不去重.
           union distinct是合并, 但是去重.
        3. 要进行合并的表, 字段个数, 对应的数据类型必须保持一致.

-- 1. union all  合并, 不去重.
use day07;
select * from stu          -- 22条
union all
select * from stu_bucket;   -- 22条

-- 2. union distinct  合并, 去重.
select * from stu          -- 22条
union distinct
select * from stu_bucket;   -- 22条

-- 3. union 后边啥都不写, 默认是 union distinct
select * from stu          -- 22条
union
select * from stu_bucket;   -- 22条

-- 4. 验证: 合并的时候, 列的个数, 对应列的数据类型必须保持一致, 至于列名, 无所谓.
select * from stu       -- int, string, string, int, string
union
select * from employee;  -- int, string, string, int, string

-- 5. 细节, 如果向order by, group by, limit语句写到最后, 则是作用于 整个语句.
select * from stu                   -- 22条
union all
select * from stu_bucket limit 3;   -- 22条      最终结果: 3条

-- 6. 细节, 如果向order by, group by, limit语句写到某个语句后, 则是单独作用于: 该语句.
-- select * from (select * from stu limit 3) t1    -- 复杂写法, 可以这样写, 但是不推荐.
(select * from stu limit 3)                   -- 3条
union all
select * from stu_bucket ;   -- 22条           最终结果: 25条

随机抽样：

概述:
    它表示我们通过 tablesample()函数实现, 从大表中抽取出一定的样本数据.
格式:
    tablesample(bucket x out of y on 列名 或者 rand());
注意：
    1. y表示分成几个桶, 即: 桶的个数.
    2. x表示从桶内抽取x份(条)
    3. 根据列名抽取, 相当于把该列当做了分桶字段抽取, 列名一致的情况下, 其它条件不变(桶的数据等...), 每次抽取到的数据都一样.
    4. rand()表示随机数, 即随机抽取, 每次采集到的数据都不一样.
    5. x 不能比 y 大.

-- 1. rand()函数演示
select rand();      -- 生成1个 0.0 ~ 1.0之间的随机数, 包左不包右, 也叫前闭后开, 即: [0.0, 1.0)

-- 2. 抽样查询, 根据列名抽取, 列名一致的情况下, 其它条件不变(桶的数据等...), 每次抽取到的数据都一样.
select * from stu tablesample ( bucket 1 out of 2 on gender);       -- 按照gender字段分成2个桶, 取第 1 份数据, 所有的女生
select * from stu tablesample ( bucket 2 out of 2 on gender);       -- 按照gender字段分成2个桶, 取第 2 份数据, 所有的男生
-- select * from stu tablesample ( bucket 3 out of 2 on gender);       -- 按照gender字段分成2个桶, 取第 3 份数据, 没有这样的数据, 报错.

-- 3. 随机采样, rand()表示随机数, 即随机抽取, 每次采集到的数据都不一样.
select * from stu tablesample ( bucket 1 out of 2 on rand());

虚拟列介绍：

概述:
    属于Hive内置的, 数据本身的参数, 辅助我们进行查询的.
分类:
    INPUT__FILE__NAME               显示数据行所在的 数据文件
    BLOCK__OFFSET__INSIDE__FILE     显示数据行所在的 数据文件中的 行偏移量(即: 起始索引)
        95001,李勇,男,20,CS    行偏移量(即: 起始索引): 0
        95002,刘晨,女,19,IS    行偏移量(即: 起始索引): 23
        95003,王敏,女,22,MA    行偏移量(即: 起始索引): 46
    ROW__OFFSET__INSIDE__BLOCK         显示数据所在的HDFS块的偏移量, 该虚拟列必须要设置才能用, 即:  set HIVE函数.exec.rowoffset=true。

        显示数据行 所在的 Block块的 编号(从 0 开始)
注意:
    1. 1个中文, gbk码表占2个字节, utf-8码表占3个字节
    2. row_offset_inside_block

select *, INPUT__FILE__NAME from stu;
select *, BLOCK__OFFSET__INSIDE__FILE from stu where BLOCK__OFFSET__INSIDE__FILE > 100;

SET hive.exec.rowoffset=true;      -- 开启器 hdfs块偏移量设置.
select *, ROW__OFFSET__INSIDE__BLOCK from stu;