HiveSQL DQL语句

北念乄
已于 2023-09-08 11:43:56 修改
阅读量147
点赞数
文章标签：大数据 hive sql
于 2023-09-06 16:19:05 首次发布
本文链接：https://blog.csdn.net/weixin_62138105/article/details/132717997
版权
/*
HQL DQL语句简介:
    概述:
        HQL DQL指的是 数据查询语句, 主要是对 表数据进行查询操作的.
    对比:
        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 起始索引, 数据条数;
    细节:
        1. HQL的代表查询格式 较之于 MySQL的单表查询格式, 有3处不同.
            A. HQL 可以支持 CTE表达式.
            B. HQL 筛选列的时候可以写 all 或者 distinct
            C. HQL 支持分桶查询.
        2. distribute by 表示分桶, sort by表示桶内排序, 如果 分桶字段 和 排序字段是同一个字段, 则可以用cluster by实现.
           即: cluster by 分桶排序字段 = distribute by 分桶字段 + sort by 桶内排序字段
*/
-- --------------------------------------- 案例1: 简单查询 ---------------------------------------
-- 1. 准备数据源, 即: 建表, 添加表数据.
use day07;
CREATE TABLE orders (
    orderId bigint COMMENT '订单id',
    orderNo string COMMENT '订单编号',
    shopId bigint COMMENT '门店id',
    userId bigint COMMENT '用户id',
    orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',
    goodsMoney double COMMENT '商品金额',
    deliverMoney double COMMENT '运费',
    totalMoney double COMMENT '订单金额（包括运费）',
    realTotalMoney double COMMENT '实际订单金额（折扣后金额）',
    payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',
    isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',
    userName string COMMENT '收件人姓名',
    userAddress string COMMENT '收件人地址',
    userPhone string COMMENT '收件人电话',
    createTime timestamp COMMENT '下单时间',
    payTime timestamp COMMENT '支付时间',
    totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 2. 查询表数据.
-- 2.1 查询所有
select * from orders ;      -- 实际开发中, 建议加上 limit 条数;
-- 2.2 查询单列
select orderId, orderNo, shopId, userId, orderStatus, goodsMoney from orders;
select distinct orderStatus from orders;        -- 去重查询, 6条
select all orderStatus from orders;        -- 不去重查询, 跟不写all的效果是一样的.

-- 2.3 查询数据量
select count(orderId) from orders;      -- 1000条

-- 2.4 过滤出广东省订单
select * from orders where userAddress like '%广东%';

-- 2.5 找出广东省单笔营业额最大的订单
select * from orders where userAddress like '%广东%' order by totalMoney desc limit 1;


-- --------------------------------------- 案例2: 聚合, 分组查询 ---------------------------------------
-- 2.6 统计未支付、已支付各自的人数
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;

-- 2.7 在已付款订单中，统计每个用户最高的一笔消费金额
select userId, max(realTotalMoney) realTotalMoney_max  from orders where isPay=1 group by userId;

-- 2.8 统计每个用户的平均订单消费额
-- 扩展: 保留n位小数.
select round(3.135, 2);     -- 四舍五入, 保留2位小数.

select userId, round(avg(realTotalMoney), 2) realTotalMoney_avg  from orders group by userId;

-- 2.9 统计每个用户的平均订单消费额，过滤大于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;

-- --------------------------------------- 案例3: join连接查询 ---------------------------------------
-- 1. 准备数据源, 建表, 添加表数据.
-- table1: 员工表
CREATE TABLE employee(
   id int,
   name string,
   deg string,
   salary int,
   dept string
 ) row format delimited
fields terminated by ',';

-- table2: 员工家庭住址信息表
CREATE TABLE employee_address (
    id int,
    hno string,
    street string,
    city string
) row format delimited
fields terminated by ',';

-- 2. 查询表数据
select * from employee;             -- 员工信息表
select * from employee_address;     -- 员工住址表

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

-- --------------------------------------- 案例4: 分桶查询 ---------------------------------------
/*
分桶查询相关:
    1. 分桶 = 分文件, 如果创建分桶表, 添加数据的时候, 数据会根据 哈希取模法, 放到不同的桶(文件)中.
    2. 如果是分桶查询, 类似于 分组查询, 就是按照一定的条件, 把相同的数据(同一个桶的数据)放到一起的过程.
    3. 分桶查询格式如下:
        cluster by 分桶排序字段 = distribute by 分桶字段 + sort by 排序字段
    4. mapreduce.job.reduces参数的值默认是-1, 即: MR程序会根据任务量自动决定分成多少个桶,
       如果要手动指定桶的个数, 必须通过 set mapreduce.job.reduces = n; 参数实现.
    5. 思考: cluster by, distribute by, sort by, order by 它们之间的区别是什么?
*/
-- 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冲突, 不能一起使用.

-- --------------------------------------- 案例5: 正则匹配(查询) ---------------------------------------
/*
正则表达式(Regexp)介绍:
    概述:
        正确的, 符合特定规则的字符串.
    细节:
        1. 正则表达式不独属于任意的一种语言, 市场上大多数的语言都支持正则, 例如: Java, Python, HiveSQL, JavaScript等...
        2. 要求: 能用我们讲的规则, 看懂别人写的 正则表达式(式子)即可.
    正则规则:
        ^           代表: 正则开头
        $           代表: 正则结尾
        a           代表: 1个字符a
        .           代表: 任意的1个字符
        \.          代表: 取消.的特殊含义, 即只把它当做1个普通的 点(.)
        \\          代表: 一个 \
        [abc]       代表: a,b,c中的任意1个字符
        [^abc]      代表: 除了a,b,c外的的任意1个字符
        \d          代表: 任意的1个整数, 等价于 [0-9]
        \w          代表: 任意的1个单词字符, 即: 数字, 字母, 下划线, 等价于 [0-9a-zA-Z_]
        \S          代表: 任意的1个非空字符

        ?           代表: 至少0次, 至多1次
        *           代表: 至少0次, 至多n次(无所谓)
        +           代表: 至少1次, 至多n次(无所谓)
        x{n}        代表: x恰好出现 n次(多一次, 少一次都不行)
        x{n,}       代表: x至少出现n次, 至多无所谓.
        x{n,m}      代表: x至少出现n次, 至多m次, 包括n和m
    HQL DQL语句, 正则格式:
        字符串值 rlike '正则表达式'          -- 返回结果为: true(匹配成功), false(匹配失败)
*/
-- 0. 切库, 查表.
use day07;
show tables;
select * from orders limit 30;

-- 1. 查找广东省的数据
select * from orders where userAddress like '广东%';      -- 模糊查询
select * from orders where userAddress rlike '^广东.*';      -- 正则查询

-- 2. 查找用户地址是：xx省 xx市 xx区的数据
select * from orders where userAddress rlike '..省 ..市 ..区';
select * from orders where userAddress rlike '.{2}省 .{2}市 .{2}区';

-- 3. 查找用户姓为张、王、邓
select * from orders where userName rlike '^[张王邓].{1,3}';  -- 例如: 张* ~ 张***

-- 4. 查找手机号符合：188****0*** 规则
select * from orders where userPhone rlike '^188\\*{4}0[0-9][0-9][0-9]$';       -- 47条
select * from orders where userPhone rlike '^188\\*{4}0[0-9]{3}$';
select * from orders where userPhone rlike '^188\\*{4}0\\d{3}$';
select * from orders where userPhone rlike '^188\\S{4}0\\d{3}$';

-- 5. 正则校验手机号
-- 规则: 1.必须以1开头    2.第二位必须是3-9任意1位  3.长度必须是11位.  4.必须是纯数字.
select '15112345678' rlike '^1[3-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$';
select '15112345678' rlike '^1[3-9][0-9]{9}$';
select '15112345678' rlike '^1[3-9]\\d{9}$';

-- 校验是否以a开头
select '1abc1' rlike 'a.*';      -- 错误写法
select '1abc1' rlike '^a.*';     -- 正确写法, false
select 'abc1' rlike '^a.*';      -- 正确写法, true

-- 校验是否以c结尾
select '1abc1' rlike '.*c';     -- 错误写法
select '1abc1' rlike '.*c$';    -- false
select '1abc' rlike '.*c$';     -- true


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

-- --------------------------------------- 案例7: 随机抽样 ---------------------------------------
/*
随机抽样解释:
    概述:
        它表示我们通过 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 2 out of 2 on gender);       -- 按照gender字段分成2个桶, 取第 3 份数据, 没有这样的数据, 报错.

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

-- --------------------------------------- 案例8: 虚拟列介绍 ---------------------------------------
/*
虚拟列介绍:
    概述:
        属于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;
-- todo 数据集链接：https://pan.baidu.com/s/110AqmU6iyhRxM_p9A-3fyg?pwd=x6mf 
提取码：x6mf