Hive表操作----HQL

---

一、HQL DDL语句–完整建表格式

-- ---------- 案例1: HiveSQL语句 DDL语句之 操作数据表 完整建表格式 ---------------
HQL DDL语句-之 (完整的)建表语法:
    create [external] table 表名(
        列名1 数据类型 comment '字段的描述信息',
        列名2 数据类型 comment '字段的描述信息',
        列名3 数据类型 comment '字段的描述信息',
        ......
    ) comment '表的描述信息'
    分区          partitioned by(分区字段1 数据类型 comment '字段的描述信息', 分区字段2...)
    分桶          clustered by(分桶字段1, 分桶字段2...) sorted by (排序字段1 asc | desc, 排序字段2...) into 桶的个数 buckets
    行格式切割符   row format delimited | SerDe '指定其它的SerDe类, 即: 不同的切割方式'
    存储方式      stored as TextFile | Orc      行存储或者列存储
    存储位置      location hdfs的文件路径
    表属性信息    tblproperties('属性名'='属性值') 例如: 内外部表, 创建者信息, 压缩协议...
    ;

HQL 常用的数据类型:
    原生类型:
        int         整数
        double      小数
        string      字符串
        timestamp   时间戳, 单位: 毫秒
        date        日期
    复杂类型;
        array   列表(序列)
        map     映射
        struct  结构体
        union   联合体

二、内部表和外部表区别

1. 内部表和外部表的区别:

1. 建表格式不同.
   内部表: 直接创建即可, 默认就是内部表.
   外部表: 建表是需要加 external关键字.
2. 权限不同, 是否会删除源文件.
   内部表: 也叫受管理表, 删除内部表时, 不仅会删除元数据(Hive中查不到了), 还会删除源文件(HDFS也查不到了)
   外部表: 只会删除元数据(Hive中查不到了), 不会删除源文件(HDFS中还在)

2. 注意点

细节:
1. 如果某个文件想持久存在(除了Hive用, 其它的框架 例如HBase, Spark也要用), 建Hive表时就选择外部表.
2. 如果某个HDFS文件是随着Hive表的删除而删除的, 就用内部表.

思考:
如果我把外部表删了, 又重新的把外部表创建了出来, 请问: 里边还有之前的数据吗?
答案:
如果表结构, 切割方式, 表的存储位置, 表名等信息不发生改变, 则: 创建出该表后, 默认就有之前的数据.

3. 案例–内部表和外部表

-- --------- 案例2: HiveSQL语句 DDL语句之建表: 内部表 和 外部表 ---------------
-- 1. 创建数据库, 切库.
create database day06;
use day06;
show tables;

-- 2. 创建内部表, 射手表.
create table t_archer_inner(
    id            int comment 'ID',
    name          string comment '英雄',
    hp_max        int comment '最大生命',
    mp_max        int comment '最大法力',
    attack_max    int comment '最高物攻',
    defense_max   int comment '最大物防',
    attack_range  string comment '攻击范围',
    role_main     string comment '主要定位',
    role_assist   string comment '次要定位'
) comment '射手表'
row format delimited fields terminated by '\t';

-- 3. 上传源文件到上述Hive表的HDFS路径下, 然后就可以查看表数据了.
select * from t_archer_inner;

-- 4. 查看表的字段(发现中文注释乱码)
desc t_archer_inner;        -- 查看表结构(简单信息, 列名, 数据类型, 描述信息)
desc formatted t_archer_inner;  -- 查看表的详细信息, 其中: Table type表示表的类型: MANAGEN_TABLE(受管理表, 内部表)  EXTERNAL_TABLE(外部表)

-- 5. 删除内部表
drop table t_archer_inner;      -- 不仅会删除元数据, 还会删除源文件.

-- 6. 创建外部表
create external table t_archer_outer(
    id            int comment 'ID',
    name          string comment '英雄',
    hp_max        int comment '最大生命',
    mp_max        int comment '最大法力',
    attack_max    int comment '最高物攻',
    defense_max   int comment '最大物防',
    attack_range  string comment '攻击范围',
    role_main     string comment '主要定位',
    role_assist   string comment '次要定位'
) comment '射手表'
row format delimited fields terminated by '\t';

-- 7. 上传源文件到上述Hive表的HDFS路径下, 然后就可以查看表数据了.
select * from t_archer_outer;

-- 8. 查看外部表的详细信息
desc formatted t_archer_outer;

-- 9. 删除外部表
drop table t_archer_outer;      -- 只会删除元数据, 不会删除源文件.

-- 10. 思考: 如果我把外部表删了, 又重新的把外部表创建了出来, 请问: 里边还有之前的数据吗?
-- 答案: 如果表结构, 切割方式, 表的存储位置, 表名等信息不发生改变, 则: 创建出该表后, 默认就有之前的数据.
select * from t_archer_outer;

三、扩展_解决hive中文注释乱码问题

具体步骤如下:
    1. 去MySQL的hive3数据库中, 修改Hive的码表信息.    因为Hive的元数据(表名, 列名, 数据类型, 描述信息等)都是在MySQL中存储的.
       即: 执行如下的代码
        -- （1）修改表字段注解和表注解
        use hive3;
        alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;
        alter table TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

        -- （2）修改分区字段注解
        alter table PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8 ;
        alter table PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

        -- （3）修改索引注解
        alter table INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

    2. 去Linux中(即: node1机器), 修改hive软件的配置信息, 配置文件是: /export/server/hive/conf/hive-site.xml 文件.
       把如下的内容, 添加到 <configuration>  </configuration>标签中即可.
        <!-- 存储元数据mysql相关配置 -->
        <property>
            <name>javax.jdo.option.ConnectionURL</name>
            <value>jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true&amp;useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8</value>
        </property>

    3. 在node1机器中, 关闭metastore服务, hiveserver2服务, 然后重启这两个服务.
        kill -9 pid值  pid值
        nohup hive --service metastore &
        nohup hive --service hiveserver2 &

    4. 删除刚才创建的hive表, 然后重新创建即可, 此时: 注释乱码问题已经解决了.

四、HQL DDL操作数据库

1. 准备工作

在正式使用Hive之前, 务必确保你的环境是OK, 即: 以后你每天开启虚拟机之后, 需要做的事情是:

1. 在node1中启动 hadoop环境, 历史任务服务, metastore服务, hiveserver2服务.
   start-all.sh
   mapred --daemon start historyserver
   nohup hive --service metastore &
   nohup hive --service hiveserver2 &
2. 查看hiveserver2服务是否成功启动, 如果OK, 则用DataGrip直连Hive即可, 这样就能写HiveSQL语句了.
   lsof -i:10000

2. 案例–操作数据库

-- ----------------------------------- 案例3: HiveSQL语句 DDL语句之 操作数据库 -----------------------------------
-- 1. 查看所有的数据库.
show databases ;

-- 2. 切换数据库.
use day05;

-- 3. 创建数据库, 默认是存储到: HDFS的 /user/hive/warehouse/数据库名.db 这里.
create database day06;      -- 如果重复执行会报错.
create database if not exists day06;      -- 如果day06数据库不存在, 我们再创建. 存在就什么都不操作.

-- 4. 创建数据库的时候, 指定数据库在HDFS上的存储位置.
create database day07 location '/aa';          -- 引号中写的是 HDFS的路径

-- 5. 查看具体的数据库.
show create database day07;     -- 查看数据库的简单信息.
desc database day07;            -- 查看数据库的详细详细.

-- 6. 切换到day07数据库, 创建1个表, 我们看看.
use day07;
create table stu(id int, name string);

-- 7. 删除数据库.
drop database day06;            -- 因为是空库, 所以会直接删除.
drop database day07 cascade;   -- 如果要删除的数据库不是空库, 则后续加上cascade关键字, 会连同数据库, 表一起删除.

五、HQL DDL语句–快速映射表

-- ---------------- 案例4: HQL DDL语句--快速映射表 --------------------------
-- Hive的本质就是: 把HDFS文件映射成Hive表, 然后就可以写HQL来操作它了, 底层会被解析成MR任务, 交由Yarn调度执行, 所需的数据源及执行结果会保存到HDFS上...
-- 1. 创建表.
create table products(
    id int comment '商品id',
    name string comment '商品名',
    price int comment '商品价格',
    cid string comment '分类id'
) comment '商品表'
row format delimited fields terminated by ',';
-- 2. 上传源文件到Hive表的HDFS路径下.
-- 3. 查看表结果.
select * from products;

六、HQL DDL–备份表和删除表数据

1. 备份表

  /*
  问题1: 如何备份表?
  答案:
      方式1: 只复制表结构, 包括: 行切割方式.
          create table 备份表名 like 原表名;        -- 底层不会转成MR任务, 而是直接执行.
      方式2: 复制表结构及表数据, 不包括: 行切割方式.
          create table 备份表名 as select语句;      -- 底层会转MR任务, 来执行.
  */
  -- 1. 切库, 查表.
  use day06;
  show tables;
  
  -- 2. 备份表, 方式1: 只复制表结构.
  drop table t1;
  create table t1 like t_archer_inner;        -- 底层不会转成MR任务, 而是直接执行.
  -- 2.1 查看表结构
  desc t1;                  -- 查看表结构信息, 简单信息
  desc formatted t1;        -- 查看表结构信息, 详细信息
  show create table t1;     -- 查看表结构信息, 建表信息
  
  -- 2.2 查看备份表数据
  select * from t1;       -- 没有数据
  
  -- 2.3 思考: 把 t_archer.txt源文件上传到该表的HDFS路径下, 表中有数据吗, 能成功映射吗?
  select * from t1;   -- 备份的时候, 行切割方式也同步备份了.
  
  
  -- 3. 备份表, 方式2: 复制表结构及表数据
  drop table t2;
  create table t2 as select * from t_archer_inner limit 3;        -- 会转MR
  
  -- 3.1 查看表结构
  desc t2;                  -- 查看表结构信息, 简单信息
  desc formatted t2;        -- 查看表结构信息, 详细信息
  show create table t2;     -- 查看表结构信息, 建表信息
  
  -- 3.2 查看表数据.
  select * from t2;
  
  -- 3.3 思考: 把 t_archer.txt源文件上传到该表的HDFS路径下, 能成功解析吗?
  select * from t2;   -- 备份表只备份结构(列名, 数据类型, 数据), 不会备份表的 切割方式.

2. 删除表数据

  /*
  问题2: 如何删除hive的表数据?
  答案:
      1. hive不支持 delete from 这种方式删除数据, 只支持truncate table清空所有表数据.
      2. hive的外部表是无法删除HDFS源文件的, 所以truncate table语法只针对于内部表有效, 针对于外部表无效.
  */
  
  -- 4. truncate table 删除表数据, 只针对内部表有效, 针对于外部表无效.
  desc formatted t1; -- MANAGED_TABLE, 内部表.
  -- 4.1 删除内部表数据.
  select * from t1;
  delete from t1;         -- 报错, hive不支持delete from
  truncate table t1;      -- 清空表数据, 相当于把HDFS中, 该表对应的源文件给删除了.
  
  -- 4.2 尝试删除外部表数据, 发现: 报错.
  select * from t_archer_outer;
  desc formatted t_archer_outer;  -- EXTERNAL_TABLE, 外部表.
  truncate table t_archer_outer;  -- 报错, truncate table只针对于内部表有效, 针对于外部表无效.

七、HQL DDL–查看及修改表信息

-- ----------- 案例5: HQL DDL--查看及修改表信息 -----------------
-- 1. 查看所有的数据表
show tables;
-- 2. 查看表信息.
desc t_archer_inner;                -- 查看简单信息, 列名, 数据类型, 描述信息
desc formatted  t_archer_inner;     -- 查看详细信息.
show create table t_archer_inner;   -- 查看建表信息

-- 3. 修改表名, 格式: alter table 旧表名 rename to 新表名
alter table t_archer_inner rename to t_archer_i;
-- alter table t_archer_i rename t_archer_inner;   -- 不能省略to, 报错.
-- 4. 修改表的存储路径, 看看就行了, 不建议做, 建议: 统一存储, 统一管理.
select * from t_archer_i;       -- 如果是修改hive表在HDFS的存储路径了, 则hive表数据可能会丢失.
show create table t_archer_i;
-- 格式: alter table 表名 set location 'hdfs文件的路径'
alter table t_archer_i set location '/aa';
-- 5. 修改表属性信息, 内外部表切换.
desc formatted t_archer_i;
alter table t_archer_i set tblproperties('EXTERNAL'='true');    -- 设置为: 外部表, EXTERNAL必须大写, 后边的true或者false大小写均可(建议大写)
alter table t_archer_i set tblproperties('EXTERNAL'='FALSE');   -- 设置为: 内部表, EXTERNAL必须大写, 后边的true或者false大小写均可(建议大写)

-- 6. 修改列的相关操作.
-- 6.0 查看表结构(列名, 数据类型)
desc t_archer_i;
select * from t_archer_i;
-- 6.1 给表新增列.
alter table t_archer_i add columns (kongfu string comment '功夫');
-- 6.2 单独修改指定的列.  注意: string不能直接转成int类型, 反之可以.
-- 格式: alter table 表名 change 旧列名 新列名 新数据类型;
alter table t_archer_i change attack_max a_m string;
alter table t_archer_i change kongfu kf string;     -- 修改kongfu列名为 kf
alter table t_archer_i change kongfu kf int;        -- 报错, string不能直接转int
-- 6.3 修改表中给所有的列.
-- 格式: alter table 表名 replace columns(列1 数据类型, 列2 数据类型)
alter table t_archer_i replace columns (new_id int, new_name string);   -- 用新的2列, 替换之前所有的列(10列)

八、HQL DML语句–数据导入-load data方式

1. DML语句详解

HQL DML语句详解:
    1. 你要分清楚 数据导入 和 数据导出分别指的是什么.
        数据导入:
            Linux, Windows => Hive表中
        数据导出:
            Hive表 => windows, Linux
    2. 数据导入相关语法如下:
        方式1: load data 方式
        方式2: insert into方式
    3. 数据导出相关语法如下:
        insert overwrite

2. load data语法详解

数据导入之 load data语法详解:
    格式:
        load data [local] inpath '源文件路径' [overwrite] into table 表名 [partition by(分区字段1, 分区字段2...)];
    格式详解:
        load data       固定格式, 表示: 数据导入
        local           如果不写, 表示从HDFS路径导入到Hive表, 如果写了, 代表从Linux路径导入到Hive表.
                        Linux系统是本地文件系统, 文件路径前缀为:  file:///     而HDFS文件路径的前缀为: hdfs://node1:8020/
        inpath          后边跟的是具体的(源)文件路径
        overwrite       如果写了, 就是覆盖写入, 不过不写, 就是追加写入.
        into table      表示具体导入数据到哪个表中.
        partition by    表示具体的分区, 即: 把数据导入到哪个文件夹中.   回顾: 分区 = 分文件夹

    核心细节:
        如果是从Linux文件系统, 导入数据到Hive表, 是从Linux系统中 拷贝一份 上传到HDFS中的.
        如果是从HDFS文件系统, 导入数据到Hive表, 是从HDFS系统中 剪切该文件 到该Hive表的HDFS路径下.
    简单记忆:
        load data导入的时候, Linux是拷贝, HDFS是剪切.

3. 案例–load方式加载数据

-- --------------- 案例1: HQL DML语句 之 load方式加载数据 ----------------------
-- 0. 切库
use day06;
-- 1. 创建王者英雄表, 存储所有英雄的信息.
create table t_all_hero(
    id            int comment 'ID',
    name          string comment '英雄',
    hp_max        int comment '最大生命',
    mp_max        int comment '最大法力',
    attack_max    int comment '最高物攻',
    defense_max   int comment '最大物防',
    attack_range  string comment '攻击范围',
    role_main     string comment '主要定位',
    role_assist   string comment '次要定位'
) comment '王者荣耀英雄表'
row format delimited fields terminated by '\t';

-- 2. load方式加载数据到上述的表中.
-- 方式1: 从Linux路径 -> 导入到Hive表中.
-- 加载 射手数据, Linux路径, 文件前缀是: file:/// 还可以省略不写.
load data local inpath 'file:///export/hivedata/archer.txt' into table t_all_hero;      -- 底层不转MR, 相当于直接把源文件上传到HDFS中.
-- 加载 刺客数据
load data local inpath '/export/hivedata/assassin.txt' into table t_all_hero;
-- 加载 战士数据, 写了overwrite, 表示: 覆盖写入.
load data local inpath '/export/hivedata/warrior.txt' overwrite into table t_all_hero;

-- 方式2: 从HDFS路径 -> 导入到Hive表中.
-- 加载 法师数据到表中.   overwrite: 不写就是追加, 写了就是覆盖.   local不写就是HDFS路径, 写了就是Linux路径.
-- 细节: hdfs://node1:8020 前缀可以省略不写
load data inpath 'hdfs://node1:8020/hivedata/mage.txt' into table t_all_hero;
-- 细节: hdfs://node1:8020 前缀可以省略不写,
load data inpath '/hivedata/tank.txt' overwrite into table t_all_hero;

-- 3. 查看表数据.
select * from t_all_hero;

九、HQL DML语句–数据导入-insert方式

1. insert方式语法格式

格式:
insert into | overwrite table 表名 [partition by(分区字段1, 分区字段2…)] select 语句;

格式解释:
1. into是追加, overwrite是覆盖.
2. into的时候, table关键字可以省略不写, overwrite的时候, table关键字必须写.
3. 上述的语句, 底层会转成MR来执行.

2. insert into方式案例

-- ----------------- 案例2: HQL DML语句 之 insert into方式加载数据 -------------
-- 1. 创建t_all_hero_tmp, 临时的王者荣耀英雄表.
create table t_all_hero_tmp(
    id            int comment 'ID',
    name          string comment '英雄',
    hp_max        int comment '最大生命',
    mp_max        int comment '最大法力',
    attack_max    int comment '最高物攻',
    defense_max   int comment '最大物防',
    attack_range  string comment '攻击范围',
    role_main     string comment '主要定位',
    role_assist   string comment '次要定位'
) comment '王者荣耀英雄表'
row format delimited fields terminated by '\t';

-- 2. 往上述的表中添加数据.
insert into table t_all_hero_tmp select * from t_all_hero limit 5;
insert into t_all_hero_tmp select * from t_all_hero limit 5; -- 细节: into的时候, table可以省略不写.

-- overwrite: 表示 覆盖写入
insert overwrite table t_all_hero_tmp select * from t_all_hero limit 3;     -- table不能省略

-- 3. 查看结果.
select * from t_all_hero_tmp;

十、HQL DML语句–导出数据

1. insert overwrite方式格式

格式:
insert overwrite [local] directory ‘存储该文件的路径’ [row format delimited fields terminated by ‘行格式分隔符’] select 语句;

细节:
1. 不写local就是HDFS路径, 写了就是Linux路径.
2. 行格式分隔符, 表示导出文件后, 内容之间的 分隔符.
3. 导出的时候, 是覆盖导出的, 建议要导出到的目录, 内容为空, 否则啥都没了.

2. 案例–insert overwrite方式

-- ------------- 案例3: HQL DML语句 之 insert overwrite方式 导出数据 -----------------
-- 1. 查看数据.
select * from t_all_hero;       -- 10条数据

-- 2. 导出 t_all_hero表的数据到 hdfs的 /hivedata 目录下, 该目录下是有3个文件的.
insert overwrite directory '/hivedata' select * from t_all_hero;        -- 默认分隔符.

-- 3. 路径同上, 指定 分隔符
insert overwrite directory '/hivedata' row format delimited fields terminated by ':'
select * from t_all_hero;

-- 4. 导出 t_all_hero表的数据到 linux的 /hivedata 目录下, 该目录下是有3个文件的.
insert overwrite local directory '/export/hivedata' row format delimited fields terminated by '$'
select * from t_all_hero;

十一、HQL DQL语句–建表-默认切割符

1. 建表-默认切割符

在创建Hive表的时候, 我们可以直接指定 行格式切割符, 即: 按照什么规则来切割HDFS源文件的每行数据.

如果我们没有指定行格式切割符, 则hive表会用默认的行格式切割符, 即: ‘\001’, 它是1个特殊的字符.
1.在Linu文件x系统中, 显示为: ^A 快捷键: ctrl + v, ctrl + a
2.在windows文件系统中, 显示为: SOH
3.在HDFS文件系统中, 显示为: 口

2. 案例

-- ------------------- 案例1: HQL DQL语句--建表-默认切割符 -----------------------
-- 场景1: 建表, 手动插入数据.
-- 1. 建表.
create table test(
    id int comment '编号',
    name string comment '姓名'
) ;     -- 如果没有指定行格式分隔符, 其实相当于是: row format delimited fields terminated by '\001'

-- 2. 手动往表中添加数据.
insert into test values(1, '乔峰');

-- 3.查看表数据.
-- show create table test;
select * from test;

-- 场景2: 建表, 用于映射指定的文本数据.
drop table team_ace_player;
-- 1. 建表.
create table team_ace_player(
    id int comment '战队编号',
    team_name string comment '战队名',
    ace_name string comment '明星玩家名'
);   -- 默认的切割符是'\001', 不写, 其实相当于写了 row format delimited fields terminated by '\001';

-- 2. 上传源文件.
-- 3. 查看表结果.
select * from team_ace_player;

十二、MySQL和Hive单表查询语法对比

-- ---------------------- 案例2: MySQL 和 Hive 单表查询语法对比 ----------------------
/*
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 排序字段 asc | desc
    limit 起始索引, 数据条数;

对比上述的 MySQL 和 Hive的单表语法, 会发现, 有如下3点不同:
    1. Hive支持CTE表达式的写法, 可以临时的存储某些语句的执行结果, 方便查询.
    2. hive中除了distinct关键字之外, 还支持all关键字写法.
    3. hive支持分桶查询, 即: cluster by 分桶排序字段
    4. 如果只分桶就写 distribute by 分桶字段, 如果还要排序就写 sort by 排序字段 asc | desc
       如果分桶和排序字段是同一个字段, 则可以直接写 cluster by, 即: cluster by = distribute by + sort by
*/

十三、HQL DQL–基本查询

-- ---------------------- 案例3: HQL DQL语句--基本查询 ----------------------
-- 1. 建库, 切库, 查表.
create database day08;
use day08;
show tables;

-- 2. 建表.
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';

-- 3. 上传源文件, 或者 load data方式加载数据.
-- 4. 查看表数据.
select * from orders;   -- 1000条

-- 5. 完成如下的需求.
-- 5.1 查询所有
select * from orders;   -- 1000条
select * from orders limit 100;   -- 10条, 实际开发中写法
-- 5.2 查询单列
select userName, orderId, totalMoney from orders;
-- 5.3 查询数据量
select count(orderId) from orders;        -- 1000条
-- 5.4 过滤广东省订单
select * from orders where userAddress like '广东省%';
-- 5.5 找出广东省单笔营业额最大的订单
-- 思路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 '广东省%');

-- 5.6 统计未支付、已支付各自的人数
-- 方式1: 分组统计.
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;

-- 思路1: if()函数直接实现.     if(判断条件, 值1, 值2);  条件成立返回值1, 不成立返回值2
select
    count(if(isPay = 0, isPay, null)) as `未支付`,
    count(if(isPay = 1, isPay, null)) as `已支付`
from orders;

-- 5.7 在已付款订单中，统计每个用户最高的一笔消费金额
select userId, max(realTotalMoney) from orders where isPay = 1 group by userId;
-- 5.8 统计每个用户的平均订单消费额
select userId, userName, avg(realTotalMoney) from orders  group by userId, userName;
-- 升级需求: 保留两位小数.
select userId, userName, round(avg(realTotalMoney), 2) as avg_money from orders  group by userId, userName;
-- 5.9 统计每个用户的平均订单消费额，过滤大于10000的数据
-- 细节: having后边可以跟聚合函数, 不能直接写其它的 数字函数.
select userId, userName, avg(realTotalMoney) as avg_money from orders  group by userId, userName having avg_money > 10000;
-- 四舍五入, 保留两位小数, 组后筛选, 写法如下.
select
    userId,
    userName,
    round(avg(realTotalMoney), 2) as avg_money
from
    orders
group by
    userId, userName
having
    round(avg(realTotalMoney), 2) > 10000;


-- 细节: 保留两位小数.
select round(10.123, 2);    -- 10.12
select round(10.125, 2);    -- 10.13

十四、HQL DQL语句–join连接查询

-- ---------------------- 案例4: HQL DQL语句--join连接查询 ----------------------
-- join连接查询是多表查询的方式, 它具体有 6种查询方式, 分别是: 交叉连接, 内连接, 左外连接, 右外连接, 满外连接(全外连接), 左半连接, 具体如下:
-- 1. 建表, 上传源文件.
--table1: 员工表
CREATE TABLE employee(
   id int,      -- 员工id
   name string,
   deg string,
   salary int,
   dept string
 ) row format delimited fields terminated by ',';

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

-- 2. 查看表数据.
select * from employee;
select * from employee_address;

-- 3. 演示各种连接查询.
-- 3.1 交叉连接查询 cross join, 查询结果是: 两张表的笛卡尔积, 即: 表A总条数 * 表B总条数, 会有大量的脏数据, 一般不用.
-- 写法1: 标准写法.
select * from employee cross join employee_address;     -- 25条
-- 写法2: 语法糖
select * from employee, employee_address;     -- 25条

-- 3.2 内连接查询, inner join, 其中inner可以省略不写, 查询结果为: 表的交集.
-- 写法1: 标准写法.  显式内连接
select * from employee e1 inner join employee_address e2 on e1.id = e2.id;  -- 4条
select * from employee e1 join employee_address e2 on e1.id = e2.id;  -- 4条

-- 写法2: 语法糖, 隐式内连接.
select * from employee e1, employee_address e2 where e1.id = e2.id;  -- 4条

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

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

-- 3.5 满外连接(全外连接)查询, full outer join, 其中outer可以省略不写, 查询结果为: 左表全集 + 右表全集 + 表的交集.
-- 细节: 满外连接 = 左外连接 + 右外连接 查询结果.
select * from employee e1 full outer join employee_address e2 on e1.id = e2.id; -- 6条
select * from employee e1 full join employee_address e2 on e1.id = e2.id; -- 6条

-- 3.6 左半连接, left semi join, , 查询结果为: 表的交集.
-- 细节: 左半连接 相当于 内连接的查询结果, 只要左表部分.
select * from employee e1 left semi join employee_address e2 on e1.id = e2.id; -- 4条, 只有左表的数据.

十五、HQL DQL语句–分桶查询

1. 分桶查询

分桶查询介绍:
    概述:
        分桶查询就是根据分桶字段, 把表数据分成n份, 但是: 是逻辑划分, 不是物理划分.
        逻辑划分: 类似于分组, 就是根据分桶字段值 进行分组, HDFS上文件还是1个.
        物理划分: 就是昨天的分桶建表, HDFS上存储数据的时候, 已经变成了N个文件.
    格式:
        cluster by 分桶排序字段 | distribute by 分桶字段 sort by 排序字段 asc | desc
    细节:
        1. 分桶查询是 逻辑分桶, 把数据分成n组进行查询, 但是HDFS上还是1个文件.
           分桶建表是 物理分桶, 数据在HDFS上会被存储到N个文件几种.
        2. 分桶查询需要用到 set mapreduce.job.reduces = n; 这个n就是ReduceTask任务的数量, 即: 分几个桶.
        3. mapreduce.job.reduces 参数的值默认是 -1, 即: 程序会按照数据量, 任务量自动分配ReduceTask的个数, 一般是1个, 即: 1个桶.
        4. distribute by 表示分桶, sort by表示对桶内的数据进行排序(局部排序), 如果分桶字段和排序字段是同1个, 则可以简写为: cluster by
           即: cluster by = distribute by + sort by
        5. 面试题: cluster by, distribute by, sort by, order by这四组关键字的区别是什么?
            cluster by:     分桶且(桶内局部)排序的, 分桶字段和排序字段必须是同1个字段, 且是升序排列.
            distribute by:  仅分桶的, 一般要结合 set mapreduce.job.reduces = n;参数一起使用.
            sort by:        桶内排序(即: 局部排序), 一般要结合 distribute by 一起使用.
            order by:       全局排序的, 对全表数据进行排序, 和cluster by冲突, 不能共用.

2. 案例–分桶查询

-- ---------------------- 案例5: HQL DQL语句--分桶查询 ----------------------
-- 1. 建表, 上传源文件, 查看表数据.
create table student(
    sid int,
    name string,
    gender string,
    age int,
    major string
) comment '学生信息表'
row format delimited fields terminated by ',';
select * from student;      -- 22条

-- 2. 按照性别进行分桶, 分成2个桶...
-- 核心细节, 分桶查询是, 需要设置ReduceTask的任务数, 有几个ReduceTask, 就有几个桶.
set mapreduce.job.reduces = 2;  -- 默认是-1, 即: 程序会根据数据量, 任务量, 自动给出 ReduceTask任务数, 一般是: 1
select * from student distribute by gender;        -- 根据gender分桶, 且根据gender升序排列.

-- 3. 按照id分成3个桶, 注意: 不排序.
set mapreduce.job.reduces = 3;
select * from student distribute by sid;

-- 4. 上述的数据, 虽然分成了3个桶, 但是不方便查看数据, 因此, 加入: 排序.
-- 按照id分成3个桶, 然后按照年龄降序排列.
select * from student distribute by sid sort by age;    -- 默认是升序.
select * from student distribute by sid sort by age desc;    -- 降序

-- 5. 如果分桶字段和排序字段是同1个, 则可以简写为: cluster by
-- 根据sid分成3个桶, 且根据sid升序排列.
select * from student distribute by sid sort by sid;    -- 默认是升序.
select * from student cluster by sid;       -- 效果同上.
-- select * from student cluster by sid desc;       -- 报错, cluster by默认升序.

十六、HQL DQL–随机抽样

1. 随机抽样(采样)

随机抽样解释:
    概述:
        随机采样指的是 tablesample()函数, 通过它, 我们可以用 类似于分桶的思路, 对数据进行采样.
    格式:
        select .. from 表名 tablesample(bucket x out of y on 列名 | rand());
    格式解释:
        1. y表示: 把数据分成y个桶.   逻辑分桶.
        2. x表示: 从y个分桶中, 获取第x份数据.
        3. 如果是列名方式, 列名采样, 在列名等其它条件不发生改变的情况下, 每次采样获取的数据都是一样的.
        4. 如果是rand()函数, 随机采样, 则: 每次获取的数据都是不一样的.
        5. x 不能大于 y

2. 案例–随机抽样(采样)

-- ---------------------- 案例6: HQL DQL语句--随机抽样(采样) ----------------------
-- 1. 查看原表数据.
select * from student;      -- 22条, 12条男, 10条女

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

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

-- 5. 随机采样, 分成3个桶, 取第1份数据.
select * from student tablesample ( bucket 1 out of 3 on rand());   -- 每次获取的数据条数都不一样.
select * from student tablesample ( bucket 4 out of 3 on rand());   -- 报错, x不能大于y

-- 也可以换成其它的表进行采样.
select * from orders tablesample ( bucket 1 out of 3 on rand());

十七、HQL DQL–正则查询

1. 正则查询

正则查询介绍:
    概述:
        正则表达式不独属于任意的一种语言, 市场上绝大多数的语言都支持它, 例如: Java, Python, JavaScript, HiveSQL...
        正则的规则是通用的, 但是正则表达式的校验格式(语法)稍有不同.
        正则表达式: 正确的, 符合特定规则的字符串. (RegExp, 全称: regular expression)
    常用的正则规则(通用):
        .       代表任意的1个字符
        \.      代表1个普通的. 没有任何的特殊含义.
        [abc]   代表a,b,c中任意的1个字符.
        [^abc]  代表除了a,b,c以外, 任意的1个字符
        \d      代表任意的1个数字, 等价于 [0-9]
        \w      代表1个单词字符, 即: 数字, 字母, 下划线, 等价于: [a-zA-Z0-9_]
        \S      代表任意的1个非空字符
        \\      代表1个\
        ^       代表开头
        $       代表结尾
        ?       数量词, 代表前边的内容出现0次或者1次.
        *       数量词, 代表前边的内容出现0次或多次.
        +       数量词, 代表前边的内容出现1次或者多次.
        a{n}    数量词, 代表a恰好出现n次, 多一次少一次都不行.
        a{n,}   数量词, 代表a至少出现n次, 至多出现无数次.
        a{n,m}  数量词, 代表a至少出现n次, 至多出现m次, 包括n和m
    HiveSQL中, 正则校验格式如下:
        字符串 rlike '正则表达式'

2. 案例–正则查询

-- ---------------------- 案例7: HQL DQL语句--正则查询 ----------------------
-- 1. 校验字符串是否以a开头.
select '1abc' rlike '^a.*';   -- false
select 'a' rlike '^a.*';   -- true

-- 2. 校验字符串以x结尾.
select 'abcx1' rlike '.*x$';    -- false
select 'abcx' rlike '.*x$';     -- true

-- 3. 校验字符串中是否包含连续的3个a
select '11aaaabc' rlike '^.*a{3}.*$';   -- true
select '11aa' rlike '^.*a{3}.*$';       -- false

-- 4. 校验字符串是否不以a,b,c任意的1个字母开头.
select 'a123' rlike '^[^abc].*';     -- false
select 'bcd' rlike '^[^abc].*';     -- false
select 'c123cd' rlike '^[^abc].*';     -- false
select 'd123' rlike '^[^abc].*';     -- true

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

-- 6. 完成如下的需求
-- 6.1 查找广东省的数据
select * from orders where userAddress like '广东省%';        -- 模糊查询
select * from orders where userAddress rlike '^广东省.*';     -- 正则查询

-- 6.2 查找用户地址是：xx省 xx市 xx区的数据
select * from orders where userAddress rlike '.*省.*市.*区';     -- 正则查询

-- 6.3 查找用户姓为张、王、邓
select * from orders where userName rlike '^[张王邓]\\S+';     -- \S: 代表任意的1个非空白字符
select * from orders where userName rlike '^[张王邓].+';

-- 6.4 查找手机号符合：188****0*** 规则
select * from orders where userPhone rlike '^188\\*{4}0\\d{3}$';

十八、HQL DQL–union联合查询

1. union 联合查询

union 联合查询介绍:
    概述:
        就是对表数据做纵向拼接的, 类似于书本的 上, 下册, 有两种拼接方式.
    分类:
        union distinct      -- distinct可以省略不写, 合并 并去重.
        union all           -- 合并 不去重.
    细节:
        1. union all 和 distinct 作用类似, 都是纵向合并, 区别就是: 去不去重的问题.
        2. 直接写union 默认是 union distinct, 即: 去重合并.
        3. 要进行union联合查询的数据, 列的个数, 对应的数据类型都要一致.
        4. 如果条件写到上边的 查询语句后, 则: 单独作用于该SQL.
           如果条件写到下边的 查询语句后, 则: 作用于全局.
        5. full outer join 和 union all的区别?
            1. 作用不同.
                full join: 横向合并.
                union all: 纵向合并.
            2. 条件要求不一致.
                full join: 对表的列数, 数据类型无要求.
                union all: 要求联合查询的表的列数, 数据类型都要保持一致.

2. 案例–union 联合查询

-- ---------------------- 案例8: HQL DQL语句--union联合查询 ----------------------
-- 1. 查看数据源表.
use day07;      -- 为了方便演示.
show tables;

select * from day07.student;              -- 22条;
select * from day07.student_buckets;      -- 22条;

-- 2. union all演示, 合并 不去重.
select * from student
union all
select * from student_buckets;      -- 44条;

-- 3. union distinct演示, 合并,去重.
select * from student
union distinct
select * from student_buckets;      -- 22条;

-- 4. union distinct演示, 合并,去重.
select * from student
union   -- distinct 可以省略不写.
select * from student_buckets;      -- 22条;

-- 5. 如果条件写到上边的 查询语句后, 则: 单独作用于该SQL.
(select * from student limit 3)     -- 3条
union all
select * from student_buckets;      -- 25条;

-- 6. 如果条件写到下边的 查询语句后, 则: 作用于全局.
select * from student                       -- 22条
union all
select * from student_buckets limit 3;      -- 3条;

-- 6. 如果仅仅是单独作用于某1个SQL, 则可以用括号括起来.
select * from student                         -- 22条
union all
(select * from student_buckets limit 3);      -- 25条;

十九、HQL DQL语句–虚拟列查询

1. 虚拟列查询

虚拟列查询介绍:
    概述:
        虚拟列是Hive内置的可以在查询语句中使用的特殊标记，可以查询数据本身的详细参数。
    大白话:
        虚拟列是Hive内置的, 可以直接用的参数, 辅助我们进行查询的.
    Hive目前可用3个虚拟列：
        INPUT__FILE__NAME           显示数据行 所在的具体文件                                      类似于: 你在哪个班.
        BLOCK__OFFSET__INSIDE__FILE 显示数据行 所在文件的偏移量, 偏移量从0开始计数.                   类似于: 你是班级的第几个人.
            文件内容如下:             对应的 行偏移量:
            95001,李勇,男,20,CS         0
            95002,刘晨,女,19,IS         23
            95003,王敏,女,22,MA         46
        ROW__OFFSET__INSIDE__BLOCK  显示数据所在HDFS块的偏移量, 即: 该行数据在HDFS文件的那个Block块中. 类似于: 你在班级的哪个组.
            此虚拟列需要设置：SET hive.exec.rowoffset=true 才可使用
    细节:
        1. 中文在utf-8码表中占 3 个字节, 在gbk码表中占2个字节.
        2. 数字, 字母, 特殊符号无论在什么码表, 都只占 1个字节.
        3. HDFS文件和Linux文件类似, 默认的换行符都是: \n

2. 案例–虚拟列查询

-- ------------------ 案例9: HQL DQL语句--虚拟列查询(Virtual Columns) ------------------
-- 1. 查看数据源表.
use day08;
select * from student;

-- 2. 查看 day08#student 数据表映射的是哪个文件.
select *, INPUT__FILE__NAME from student;       -- hdfs://node1.itcast.cn:8020/user/hive/warehouse/day08.db/student/students.txt

-- 3. 查看 day08#student 数据表每行数据 在映射文件中的 起始索引(行偏移量)
select *, BLOCK__OFFSET__INSIDE__FILE from student;     -- 行偏移量从: 0开始.
-- 细节: 上述方式可以帮助我们筛选出中间的某些数据.
select *, BLOCK__OFFSET__INSIDE__FILE from student where BLOCK__OFFSET__INSIDE__FILE > 100; -- 获取行偏移量大于100的数据.

-- 4. 查看 day08#student 数据表每行数据 在HDFS的文件的哪个Block块中.  因为HDFS文件是分块存储的, 每个块的大小是128MB.
-- 核心细节: 设置HDFS的块偏移量, block块从 0 开始计数.
SET hive.exec.rowoffset=true;
select *, ROW__OFFSET__INSIDE__BLOCK from student;      -- 因为映射的students.txt文件较小, 达不到128MB, 所以该文件只有1个Block块.

-- 5. 汇总查看
select *,
       INPUT__FILE__NAME,               -- 该行数据在 那个HDFS文件中.             类似于: 你在哪个班.
       BLOCK__OFFSET__INSIDE__FILE,     -- 该行数据在 HDFS文件中的 行偏移量        类似于: 你是咱们班的第几个学生
       ROW__OFFSET__INSIDE__BLOCK       -- 该行数据在 HDFS文件的 哪个Block块.      类似于: 你在班级的哪个组.
from
    student;

二十、HQL DQL语句–CTE表达式

1. CTE表达式

CTE表达式介绍:
    概述:
        全称叫 common table expression, 公共表表达式, 就是用于 临时存储某些结果数据 的.
    格式:
        with cte表达式的名字 as (
            select 语句, 即: 要被临时存储的内容
        )
        select ... from cte表达式的名字;
    写法:
        1. 标准写法.
        2. from风格.
        3. 链式编程.
        4. 结合union all 或者 join查询.
        5. 用数据表永久存储结果.
        6. 用视图临时存储结果.

2. 案例–CTE表达式

-- ---------------------- 案例10: HQL DQL语句--CTE表达式 ----------------------
/*
CTE表达式介绍:
    概述:
        全称叫 common table expression, 公共表表达式, 就是用于 临时存储某些结果数据 的.
    格式:
        with cte表达式的名字 as (
            select 语句, 即: 要被临时存储的内容
        )
        select ... from cte表达式的名字;
    写法:
        1. 标准写法.
        2. from风格.
        3. 链式编程.
        4. 结合union all 或者 join查询.
        5. 用数据表永久存储结果.
        6. 用视图临时存储结果.
*/
-- 1. cte表达式入门写法.
with t1 as (
    select * from student
)
select * from t1;

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

-- 3. 链式编程(链式风格)
with t1 as ( select * from student),            -- 22条, 全列
     t2 as ( select * from t1 limit 10),        -- 10条, 全列
     t3 as ( select sid, name, age from t2)     -- 10条, 3列
select * from t3;

-- 4. cte结合union语句一起使用, 联合查询.
-- 3. 链式编程(链式风格)
with t1 as ( select * from student),            -- 22条, 全列
     t2 as ( select * from t1 limit 10)         -- 10条, 全列
select * from t1
union all           -- 如果不写, 默认是 distinct
select * from t2;       -- 32条, 5列

-- 扩展, 演示下 full outer join 全外连接.
with t1 as ( select * from student),            -- 22条, 全列
     t2 as ( select * from t1 limit 10)         -- 10条, 全列
select * from t1
full join t2
on t1.sid = t2.sid;     -- full join: 满外连接, 横向拼接.  22条, 10列,  10行全列, 12行(5列值, 5列空)

-- 5. 我们可以用 数据表 把cte结果 永久存储.
show tables;

create table hg1 as
with t1 as (
    select * from student
)
select sid, name, age from t1 limit 10;

-- 查看备份结果.
select * from hg1;

-- 6. 我们可以用 视图 把cte结果 永久存储.
show tables;

create view hg2 as      -- 视图是存储在内存中的, 临时存储.
with t1 as (
    select * from student
)
select sid, name, age from t1 limit 5;

select * from hg2;  -- 从视图中查询数据.