Hive SQL 的 DQL操作

  首先我们先知道HSQL的建表格式:

-- 建表格式
create [external] table [if not exists] 表名
[(列名 数据类型 [comment 列描述信息], ......)]  
-- 默认是gbk码表,中文会乱码,需要改成utf8码表 
[comment 表的描述信息] 
[partitioned by (分区字段1 数据类型 [comment 描述信息],...... )]  
-- 分区=分文件夹 作用是避免全表扫描,降低扫描次数,提高查询效率
[clustered by (分桶字段1,分桶字段2....) sorted by (排序字段1 asc|desc, 排序字段2 asc|desc, ....) into 桶的个数 buckets] 
-- 分桶=分文件 作用是减少笛卡尔积的数量,提高查询效率,方便数据采集 
[row format delimited|SerDe '指定其他的SerDe 类']
-- 行格式切割符
[stored as TextFile|Orc]
-- 存储方式 行存储|列存储
[location HDFS文件路径]
-- 存储位置
[tblproperties('属性名'='属性值')]
-- 表属性信息 (内外部表,创建者信息,压缩协议.....)

一. HSQL 基本查询

1.MySQL 和 Hive 单表查询对比

  MySQL 单表查询语法结构:

-- MySQL单表查询语法
select 
  [distinct] 列名
from 表名
where 组前筛选
group by 分组字段
having 组后筛选
order by 排序字段 asc| desc 
limit 起始索引,数据条数;

  Hive 单表查询语法结构:

-- Hive 单表查询语法结构
[CTE 表达式]
select 
  [distinct | all] 列名
from 表名
where 组前筛选
group by 分组字段
having 组后筛选
order by 排序字段 asc|desc
cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc| desc 
limit 起始索引,数据条数;

  通过对比我们可以发现MySQL 和 Hive 单表查询有几点不同:

1. Hive 支持 CTE 表达式,可以临时存储某些语句的执行结果,方便查询.

2. Hive 除了有 distinct 关键字还支持 all 关键字写法.

3. Hive 支持分桶查询, cluster by + 分桶排序字段 (分桶排序必须是同一字段,且排序只能是升序),如果只分桶用 distribute by + 分桶字段 ,如果分桶和排序不是同一字段就用 distribute by + 分桶字段 sort by + 排序字段 asc | desc.

2. 基本查询

  2.1 首先建表,上传数据,查询表中所有数据.

-- 1. 建表.
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. 上传源文件, 或者 load data方式加载数据.
-- 3. 查看表数据.
select * from orders; 

 2.2  查询前100条数据

-- 查询前100条数据
select * from orders limit 100;

  2.3 查询单列数据

-- 查询 userName, orderId, totalMoney 列的数据
select userName, orderId, totalMoney from orders;

  2.4 查询该数据的数据量

-- 查询数据的数据量
select count(orderId) from orders;
-- orderId为主键列

  2.5 过滤广东省的订单以及单笔营业额最大的订单

-- 过滤广东省订单
select * from orders where userAddress like '广东省%';
 
--找出广东省单笔营业额最大的订单
-- 此题可以有两种方法 可以进行排序或者子查询
 
-- 方法1: 排序.
select * from orders where userAddress like '广东省%' order by realTotalMoney desc limit 1;
-- 此方法如果有并列的最大订单不能全部展示出来
-- 方法2: 子查询.
select *
from
    orders
where
      userAddress like '广东省%'
  and realTotalMoney = (select max(realTotalMoney) from orders where userAddress like '广东省%');

2.6 统计未支付和已支付的人各自多少人

-- 统计未支付、已支付各自的人数
-- 可以用两个函数来实现 分别是 case when 函数 和 if() 函数
 
-- 方式1: 使用case when 函数
select
    case
        when isPay = 0 then '未支付'
        when isPay = 1 then '已支付'
    end as isPay,
    case isPay
        when 0 then '未支付'
        when 1 then '已支付'
    end as isPay2,
    count(orderId)
from
    orders
group by
    isPay;
 
-- 方式2: 使用 if() 函数
select
    count(if(isPay = 0, isPay, null)) as `未支付`,
    count(if(isPay = 1, isPay, null)) as `已支付`
from orders;

2.7 统计每个用户平均订单消费额且结果保留2位小数

-- 统计每个用户平均订单消费额结果保留两位小数
select userId, userName, round(avg(realTotalMoney), 2) as avg_money from orders  group by userId, userName;

二. HSQL 的 join 连接查询

  join 连接查询是多表查询的方式, 它具体有 6种查询方式, 分别是: 交叉连接, 内连接, 左外连接, 右外连接, 满外连接(全外连接), 左半连接.

交叉连接查询

  cross join 交叉连接查询 , 查询结果是: 两张表的笛卡尔积 也就是 表A总条数 * 表B总条数, 会有大量的脏数据, 一般不用.

-- 交叉连接 cross join
select * from 表A cross join 表B;

    

内连接查询

  (inner)join 内连接查询, 其中inner可以省略不写, 查询结果是: 表的交集.

-- 内连接查询 (inner)join
 
-- 方式1:显示内连接
select * from 表A inner join 表B on 条件; 
select * from 表A join 表B on 条件;
 
-- 方式2:隐式内连接 语法糖
select * from 表A, 表B where 条件;

 

左外连接查询

  left (outer) join 左外连接查询, 其中outer可以省略不写, 查询结果是: 左表的全集 + 表的交集.

-- 左外连接 left (outer) join
select * from 表A left outer join 表B on 条件; 
select * from 表A left join 表B on 条件;

右外连接查询

  right (outer) join 右外连接查询, 其中outer可以省略不写, 查询结果是: 右表的全集 + 表的交集.

-- 右外连接 right (outer) join
select * from 表A right outer join 表B on 条件; 
select * from 表A right join 表B on 条件;

满外连接查询

   full (outer) join 满外连接(全外连接)查询, 其中outer可以省略不写, 查询结果是: 左表全集 + 右表全集 + 表的交集 也就是 左外连接 + 右外连接.

-- 满外连接 full (outer) join
select * from 表A full outer join 表B on 条件; 
select * from 表A full join 表B on 条件;

左半连接查询

  left semi join 左半连接, 查询结果为: 表的交集 也就是 内连接的查询结果只要左表部分.

-- 左半连接 left semi join
select * from 表A left semi join 表B on 条件; 

三. 分桶查询

  分桶查询就是根据分桶字段, 把表数据分成n份.

-- 格式
 cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc | desc

  分桶查询需要用到 set mapreduce.job.reduces = n; 这个n就是ReduceTask任务的数量 也就是: 分几个桶.
  mapreduce.job.reduces 参数的值默认是 -1, 即: 程序会按照数据量, 任务量自动分配ReduceTask的个数, 一般是1个也就是 1个桶.
  distribute by 表示分桶, sort by表示对桶内的数据进行排序(局部排序), 如果分桶字段和排序字段是同1个, 则可以简写为: cluster by.

 1. 建表 上传源文件 查看数据

-- 建表, 上传源文件, 查看表数据.
create table student(
    sid int,
    name string,
    gender string,
    age int,
    major string
) comment '学生信息表'
row format delimited fields terminated by ',';
select * from student;   

  2. 按照性别进行分桶, 分成2个桶

-- 按照性别进行分桶, 分成2个桶
set mapreduce.job.reduces = 2;
select * from student distribute by gender;   

  3. 按照id分成3个桶, 然后按照年龄降序排列

-- 按照id分成3个桶, 然后按照年龄降序排列
select * from student distribute by sid sort by age desc;

  4. 根据sid分成3个桶, 且根据sid升序排列

-- 根据sid分成3个桶, 且根据sid升序排列
select * from student distribute by sid sort by sid;    
select * from student cluster by sid;    -- cluster by 只能升序排列

四. 随机采样

   随机采样指的是 tablesample()函数,类似于分桶的思路, 对数据进行采样.

-- 格式
select 列名 from 表名 tablesample(bucket x out of y on 列名 | rand());
-- y表示: 把数据分成y个桶 
-- x表示: 从y个分桶中, 获取第x份数据
-- x 不能大于 y

  1. 根据上表 按照性别分成2个桶, 取第1份数据

-- 按照性别分成2个桶, 取第1份数据
select * from student tablesample ( bucket 1 out of 2 on gender);

  2. 随机采样, 分成3个桶, 取第1份数据

-- 随机采样, 分成3个桶, 取第1份数据
select * from student tablesample (bucket 1 out of 3 on rand());
-- 随机采样每次获取的数据都是不一样的

五. 正则查询

  正则表达式不独属于任意的一种语言, 市场上绝大多数的语言都支持它.

  正则的规则是通用的, 但是正则表达式的校验格式(语法)稍有不同.

   HiveSQL中, 正则校验格式为: 字符串 rlike '正则表达式'

  例:校验手机号是否合法

-- 校验手机号是否合法
-- 规则: 1. 纯数字组成.  2.长度必须是11位.  3.第1位数字必须是1.  4.第2位数字可以是: 3-9
select '13112345678' rlike '^1[3-9]\\d{9}$';

六. union联合查询

  union 联合查询就是对表数据做纵向拼接.

     union all 和 distinct 作用类似, 都是纵向合并, 区别就是: 去不去重的问题.
     直接写union 默认是 union distinct, 即: 去重合并.
     要进行union联合查询的数据, 列的个数, 对应的数据类型都要一致.

     如果条件写到上边的 查询语句后, 则: 单独作用于该SQL.
     如果条件写到下边的 查询语句后, 则: 作用于全局.

  1. union all 演示

-- union all演示
select * from 表A
union all
select * from 表B;

   2. union distinct演示

-- union distinct演示
select * from 表A
union distinct
select * from 表B; 

七. 虚拟列查询

  虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数.

 Hive目前可用3个虚拟列：
        INPUT__FILE__NAME           显示数据行 所在的具体文件 
        BLOCK__OFFSET__INSIDE__FILE 显示数据行 所在文件的偏移量, 偏移量从0开始计数.              
        ROW__OFFSET__INSIDE__BLOCK  显示数据所在HDFS块的偏移量, 即: 该行数据在HDFS文件的那个Block块.       此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用

八. CTE表达式 

  CTE 表达式也称公共表表达式, 就是用于临时存储某些结果数据的.

-- 格式
with CTE表达式的名字 as ( select 语句 )
select ... from CTE表达式的名字;

  1. 入门写法

-- 入门写法
with t1 as (
    select * from student
)
select * from t1;

  2. from 风格

-- from 风格
with t1 as (
    select * from student
)
from t1 select sid, name;

 3. 链式风格

-- 链式风格
with t1 as ( select * from student),          
     t2 as ( select * from t1 limit 10),        
     t3 as ( select sid, name, age from t2)     
select * from t3;

  4. CTE结合union语句一起使用, 联合查询 

-- CTE结合union语句一起使用
with t1 as ( select * from student),          
     t2 as ( select * from t1 limit 10)       
select * from t1
union all          
select * from t2; 

  5. 用数据表把 CTE 结果永久存储

-- 用数据表把 CTE 结果永久存储
create table tab1 as
with t1 as (
    select * from student
)
select sid, name, age from t1 limit 10;

  6. 用视图把 CTE 结果永久存储

-- 用视图把 CTE 结果永久存储
create view vi1 as     
with t1 as (
    select * from student
)
select sid, name, age from t1 limit 5;
 

  今天的分享就到这里啦!有不同的意见可以私信哦!