4.HiveSQL 数据操作、查询语言（DML、DQL）

1.Hive SQL-DML-Load加载数据

功能
Load，加载是指将我呢见移动到与Hive表对应的位置，移动时是纯复制、移动操作

语法规则

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

语法规则之filepath
filepath表示待移动的路径。可以指向文件，也可以只想目录（移动目录中所有文件）
filepath文件路径支持相对路径，绝对路径和具有schema的完整URI，要结合LOCAL关键字考虑

语法规则之LOCAL

• 指定LOCAL，将在本地文件系统中查找文件路径
  • 若指向相对路径，将相对与用户的当前工作目录进行解释
  • 也可以是完整的URI——例如：file:///user/hive/project/data1
• 没有指定LOCAL
  • 如果filepath指向的是一个完整的URI，会直接使用这个URI
  • 如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定（不出意外，都是HDFS）

LOCAL文本文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统

语法规则之OVERWRITE
如果使用了OVERWRITE关键字，则目标表（或者分区）中的已经存在的数据会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中

1.1 练习：Load Data From Local FS or HDFS

1.练习Load Data From Local FS
2.练习Load Data From HDFS
3.理解Local关键字的汉字
4.练习Load Data To Partition Table

-- step1:建表
-- 建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ",";
-- 建表student_HDFS 用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ",";
-- 建表student_HDFS_p 用于演示从HDFS加载数据到分区表
create table student_HDFS_p(num int,name string,sex string,age int,dept string) partitioned by(country string) row format delimited fields terminated by ",";

-- 建议使用beeline客户端 可以显示出加载过程日志
-- step2:加载数据
-- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE student_local;

-- 从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作
-- 先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;

-- 从HDFS加载数据到分区表中并指定分区，数据位于HDFS文件系统根目录下
-- 先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt /
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS_p partition(country="China");

1.2 Hive3.0 新特性

• Hive3.0+,load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为INSERT AS SELECT
• Hive3.0+，还支持使用inputformat、SerDe指定输入格式，例如Text,ORC等
  比如，如果表具有分区，则load命令没有指定分区，则将load转换为INSERT AS SELECT，并假定最后一列为分区列，如果文件不菲和预期，则报错

例如

CREATE TABLE IF NOT EXISTS tab1(col1 int, col2 int)
PARTITIONED BY (col3 int)
row format delimited fields terminated by ',';

-- 在hive3.0之后 新特性可以帮助我们把load改写为insert as select
LOAD DATA LOCAL INPATH '/root/hivedata/tab1.txt' INTO TABLE tab1;

--tab1.txt内容如下
11,22,1
33,44,2

2.Hive SQL-DML insert

2.1 insert+select

insert+select表示：将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。

1. 选哟保证查询结果列的数目和需要插入数据表格的列数目一致
2. 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但时不能保证转换一定成功，转换失败的数据将会为NULL。

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

-- step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
-- 加载数据
load data local inpath '/root/hivedata/students.txt' into table student;

-- step2:创建一张目标表 只有两个字段
create table student_from_insert(sno int,sname string);
-- 使用insert+select插入数据到新表中
insert into table student_from_insert select num,name from student;

2.2 multiple inserts多重插入

• 翻译为多次charity，多重插入，其核心功能时：一次扫描，多次插入
• 语法目的就是减少扫描的次数，在一次扫描中。完成多次insert操作

-- 当库里有一张表student
select * from student;
-- 创建两张新表
create table student_insert1(sno int);
create table student_insert2(sname string);
-- 多重插入
from student
insert overwrite table student_insert1
select num
insert overwrite table student_insert2
select name;

2.3 dynamic partition insert 动态分区插入

概述

• 动态分区插入是指：分区的值是由后续的select查询语句的结果来动态确定的
• 根据查询结果自动分区

-- 1.首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrit;

-- 2.当前库下有一张表student
select * from student;

-- 3.创建分区表 以sdept作为分区字段
create table student_partition(Sno int, Sname string, Sex string, Sage int) partitioned by(Sdept string);

-- 4.执行动态分区插入操作
insert into table student_partition partition(Sdept)
select num,name,sex,age,dept from student;
-- 其中，num,name,sex,age作为表的字段内容插入表中
-- dept作为分区字段值

2.4 insert Directory导出数据

语法格式
Hive支持将select查询的结果导出成文件存放在文件系统中。语法格式如下：
注意：导出操作是一个OVERWRITE覆盖操作，慎重

-- 标准语法：
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
    [ROW FORMAT row_format] [STORED AS file_format]
SELECT ...FROM...

-- Hive extension(multiple inserts):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2]...

-- row_format
DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

• 目录可以是完整的URI。如果未指定scheme，则Hive将使用hadoop配置变量fs.default.name来决定导出位置
• 如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录
• 写入文件系统的数据被序列化为文本，列之间用\001隔开，行之间用换行符隔开。如果列都不是原始数据类型，那么这些列将序列化未JSON格式。也可以在导入的时候指定分隔符换行符和文件格式

-- 导出操作演示
-- 当前库下已有一张表student
select * from student;

-- 1.导出查询结果到HDFS指定目录下
insert overwrite directory '/tmp/hive_export/e1' select * from student;

-- 2.导出时指定分隔符和文件存储格式
insert overwrite directory '/tmp/hive_export/e2' row format delimited fields terminated by ','
stored as orc
select * from student;

-- 3.导出数据到本地文件系统指定目录下
insert overwrite local directory '/root/hive_export/e1' select * from student;

3.Hive Transaction事务表

3.1 实现原理

实现原理
Hive的文件时存储在HDFS上的，而HDFS上又不支持对文件的任意修改，只能时采取另外的手段来完成

1. 用HDFS文件作为原始数据，用delta保存事务操作的记录增量数据
2. 正在执行的事务，是以staging开头的文件夹维护的，执行结束就是delta文件夹。每次执行一次事务操作都会有这样的一个delta增量文件夹
3. 当访问Hive数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据

• INSERT语句会直接创建delta目录
• DELETE目录的前缀时delete——delta
• UPDATE语句财通了split-update特性，即先删除、后插入

delta文件夹命名格式

• delta_minWID_maxWID_stmtID，即delta前缀、写事务的ID方位、以及语句ID；删除时前缀是delete_delta，里面包含了要删除的文件
• Hive会为写事务（INSERT、DELETE等）创建一个写事务ID（Write ID），该ID在表范围内唯一
• 语句ID（Statement ID）则是当一个事务中有多条写入语句时使用的，用作唯一标识

每个事务的delta文件夹下，都有两个文件：

1. _orc_acid_version的内容时2，即当前ACID版本号是2.和1版本的主要区别是UPDATE语句采用split-update特性，即先删除、后插入。这个文件不是ORC文件，可以下载下来直接查看
2. bucket_00000文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以ORC格式存储，底层二进制，使用ORC TOOLS查看
   operation:0表示插入，1表示更新，2表示删除。由于使用了split-update，UPDATE是不会出现的，所以delta文件中的operation是0，delete_delta文件中的operation是2
   originalTransaction、currentTransaction：该条记录的原始写事务ID，当前的写事务ID
   rowId：一个自增的唯一ID，在写事务和分桶的组合中唯一
   row：具体数据。对于DELETE语句，则为null，对于INSERT就是插入的数据，对于UPDATE就是更新后的数据

合并器（Compactor）

• 随着表的修改操作，创建了越来越多的delta增量文件，就需要合并以保持足够的性能
• 和兵器Compactor是一套在Hive Metastore内运行，支持ACID系统的后台进程。所有合并都是在后台完成的，不会组织数据的并发读、写。合并后，系统将的等待所有旧文件的读操作完成后，删除旧文件。
• 合并操作分为两种，minor compaction（小合并）、major compaction（大合并）
  • 小合并会将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta文件
  • 大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为delta文件相应于基础文件占比10%

3.2 事务表使用设置与局限性

局限性
虽然Hive支持了具有ACID语义的事务，但是在使用起来，并没有像在MySQL中使用那么方便，有很多限制

1. 尚不支持BEGIN、COMMIT和ROLLBACK，所有语言操作都是自动提交的
2. 表文件存储格式仅支持ORC
3. 需要配置参数开启事务使用
4. 外部表无法创建为事务，因为Hive只能控制元数据，无法管理数据
5. 表属性参数transactional必须设置为true
6. 必须将Hive事务管理器设置为org.apache.hadoop.hive.ql.lockmgr.DbTxnManager才能使用ACID表
7. 事务不支持LOAD DATA…语句

设置参数
Client端

set hive.support.concurrency = true; -- Hive是否支持并发
set hive.enforce.bucketing = true; -- 从Hive2.0开始就不再需要 是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; -- 动态分区模式 非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; 

服务端：

set hive.compactor.initiator.on = true; -- 是否在Metastore实例上运行启动线程和清理线程
set hive.compactor.worker.threads = 1; -- 在此metastore实例上运行多个压缩程序工作线程

4.Hive SQL-DML-Update、Delete

概述

• Hive是基于Hadoop的数据仓库，是面向分析支持分析工具。将已有的结构话数据文件映射成为表，然后提供SQL分析数据的能力
• 因此在Hive中常见操作就是分析查询select操作
• Hive早期是不支持update和delete语法的，因为Hive所处理的数据都是已经存在的数据、历史数据
• 后续Hive支持了相关的update和delete操作，不过有很多约束

-- 创建Hive事务表
create table trans_student(
    id int,
    name string,
    age int
) stored as orc TBLPROPERTIES('transactional'='true');

-- 针对事务表进行insert update delete操作
insert into trans_student(id, name, age)
values(1,"Allen",18);

update trans_student
set age = 20
where id = 1;

delete from trans_student where id = 1;

5.Hive SQL-DQL-Select查询数据

语法树

• 从哪里查询取决于FROM关键字后面的table_reference。可以是普通物理表、视图、join结果或子查询结果
• 表名和列名不区分大小写

[WITH CommonTableExpression (, CommonTableExpression) *]
SELECT [ALL|DISTINCT] select_expr,select_expr,...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] row];

5.1 案例：美国Covid-19新冠数据之select查询

准备一下select语法测试环境，数据文件“us_covid19-counties.dat”，里面记录了2021-01-28美国各个县累计新冠确诊病例数和累计死亡病例数

数据示例：
2021-01-28,Coffee,Alabama,01031,4795,72

数据环境准备

-- step1:创建普通表t_usa_covid19
drop table if exists t_usa_covid19;
create table t_usa_covid19(
    count_date string,
    county string,
    state string,
    fips int,
    cases int,
    deaths int
) row format delimited fields terminated by ',';
-- 将源数据load加载到t_usa_covid19表对应的路径下
load data local inpath '/root/hivedata/us-covid19-counties.dat' into table t_usa_covid19;

-- step2:创建一张分区表 基于count_date日期，state州进行分区
create table if not exists t_usa_covid19_p(
    county string,
    fips int,
    deaths int
)partitioned by(count_date string,state string)
row format delimited fields terminated by ',';

-- step3:使用动态分区插入将数据导入t_usa_covid19_p中
set hive.exec.dynamic.partition.mode = nonstrict;

insert into table t_usa_covid19_p partion(count_date,state)
select county,fips,cases,deaths,count_date,state from t_usa_covid19;

基础语法练习
select_expr
select_expr表示检索查询返回的列，必须至少有一个select_expr

-- 1.select_expr
-- 查询所有字段或者指定字段
select * from t_usa_covid19_p;
select county, cases, deaths from t_usa_covid19_p;

-- 查询匹配正则表达式的所有字段
SET hive.support.quoted.identifiers =none;
select `^c.*` from t_usa_covid19_p; --反引号不在解释为其他含义，被解释为正则表达式
-- 查询当前数据库
select current_database(); -- 省去from关键字
-- 查询使用函数
select count(county) from t_usa_covid19_p;

ALL、DISTINCT
用于指定查询返回结果中重复的行如何处理
1.如果没有给出这些选项，则默认值为ALL
DISTINCT指定从结果集中删除重复的行

-- 2.ALL、DISTINCT
-- 返回所有匹配的行
select state from t_usa_covid19_p;
-- 相当于
select all state from t_usa_covid19_p;
-- 返回所有匹配的行 去除重复结果
select distinct state from t_usa_covid19_p;
-- 多个字段distinct 整体去重
select county,state from t_usa_covid19_p;
select distinct county,state from t_usa_covid19_p;
select distinct sex from student;

WHERE

• where后面是一个布尔表达式，用于查询过滤
• 在where表达式中，可以使用Hive支持的任何函数和运算符,但聚合函数除外（因为聚合函数要使用它的前提是结果集已经确定。而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数）
• 从Hive0.13开始，where子句支持某些类型的子查询

-- 3.WHERE CAUSE
select * from t_usa_covid19_p where 1 > 2;
select * from t_usa_covid19_p where 1 = 1;
-- where 条件中使用函数 找出州名字字母长度超过10位的有哪些
select * from t_usa_covid19_p where length(state) > 10;
-- where 子句支持子查询
select *
from A
where A.a in (select foo from B);

分区查询、分区裁剪

• 针对Hive分区表，在查询时可以指定分区查询，减少全表扫描，也叫做分区裁剪
• 所谓分区裁剪:对分区表进行查询时，会检查where子句或join中的on子句中是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没有访问条件的分区

-- 4.分区查询、分区裁剪
-- 找出来自加州，累计死亡人数大于1000的县 state字段就是分区字段 进行分区裁剪 避免全表扫描
select * from t_usa_covid19_p where state = "California" and deaths > 1000;
-- 多分区裁剪
select * from t_usa_covid19_p where count_date = "2021-01-28" and state = "California" and deaths > 1000;

GROUP BY

• group by语句用于结合聚合函数，根据一个或多个列对结果进行分组
  注意：出现group by中select_expr的字段：要么是group by分组的字段；要么是被聚合函数应用的字段（避免出现一个字段多个值的歧义）

-- 5.GROUP BY
-- 根据state州进行分组
-- 被聚合函数应用
select state,count(deaths)
from t_usa_covid19_p where count_date = "2021-01-28" group by state;

HAVING

• 在SQL中增加HAVING子句原因是，WHERE关键字无法与聚合函数一起使用
• HAVING子句可以让我们筛选分组后的各组数据，并且可以在Having中使用聚合函数，因为此时where，group by已经执行结束，结果集已经确定

-- 6.HAVING
-- 统计死亡病例数大于10000的州
-- 先where分组过滤（此处是分组裁剪），在进行group by分组，分组后每个结果集确定在使用having过滤
select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
hvaing sum(deaths) > 10000;

-- 在group by 的时候组合函数已经作用得到结果 having直接引用结果过滤 不需要再单独计算一次了
select state,sum(deaths) as cnts
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
hvaing cnts > 10000;

HAVING和WHERE区别

• having是在分组后对数据进行过滤
• where实在分组前对数据进行过滤、
• having后面可以使用聚合函数
• where后面不能使用聚合函数

LIMIT

• limit用于限制select语句返回的行数
• limit接受一个或两个数字参数，这两个参数都必须是负整数常量
• 第一个参数指定要返回的第一行偏移量（从Hive2.0.0开始），第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0.

-- 7.limit
-- 返回2021-1-28加州的前5条数据
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state = "California"
limit 5;

-- 返回结果从第行3开始，共3行
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state = "California"
limit 2,3; -- 注意：第一个参数偏移量是从0开始的

执行顺序

• 在查询过程中执行顺序：from > where > group(含聚合) > having > order > select
  • 聚合语句（sum,min,max,avg,count）要比having子句优先执行
  • where子句在查询过程中执行优先级别先于聚合语句（sum,min,max,avg,count）

5.2 查询高阶语法

ORDER BY

• Hive SQL中ORDER BY语法类似于标准SQL语言中的ORDER BY语法，会对输出的结果进行全局排序
• 默认排序为升序（ASC），也可以指定为DESC降序
• 在Hive 2.1.0和更高版本，支持在ORDER BY子句中为每个列指定null类型结果排序顺序（ASC默认null在前，DESC默认null在后）

-- 1.order by
-- 根据字段进行排序
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state = "California"
order by deaths;

select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state = "California"
order by deaths desc;

-- 强烈建议将LIMIT与ORDER BY一起使用，避免数据集行数过大
-- 当hive.mapred.mode设置为严格模式时，使用不带LIMIT的ORDER BY会引发异常
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state = "California"
order by deaths desc
limit 3;

CLUSTER BY

• 根据指定字段将数据分组，每个内再根据该字段正序排序（只能正序）
  • 分组规则hash散列
  • 分为几组取决于reducetask的个数

-- 2.cluster by
select * from student;
-- 不指定reducetask个数（自动设置为1）
select * from student cluster by num;

-- 手动设置reducetask个数
set mapreduce.job.reduces=2;
select * from student cluster by num;

CLUSTER BY局限性
需求：根据sex性别分为两个部分，每个分组内再根据age年龄的倒序排序

• cluster by 无法单独完成，因此分组和排序的字段只能是同一个
• order by更不能再这里使用，因为是全局排序，只有一个输出，无法满足需求

DISTRIBUTE BY + SORT BY
DISTRIBUTE BY + SORT BY就相当与吧CLUSTER BY的功能一分为二：
如果DISTRIBUTE BY + SORT BY的字段一样，则：CLUSTER BY = DISTRIBUTE BY + SORT BY
1.DISTRIBUTE BY负责根据指定字段分组
2.SORT BY负责分组内排序规则
分组和排序字段可以不同

-- 案例：把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序
select * from student distribute by sex sort by age desc;

Union联合查询
UNION用于将来自多个select语句的结果合并为一个结果集
1.使用DISTINCT关键字与只是用UNION默认效果一样，都会删除重复行。1.2.0之前的Hive版本仅支持UNION ALL，再这种情况下不会消除重复的行
2.使用ALL关键字，不会删除重复行，结果集包括所有select语句的匹配行（包括重复行）
3.每个select_statement返回的列数量和名称必须相同

select_statement
    UNION [ALL|DISTINCT]
select_statement
    UNION [ALL|DISTINCT]
select_statement...;

select num,name from student_local
union ALL
select num,name from student_hdfs;

select num,name from student_local
union DISTINCT
select num,name from student_hdfs;

-- 如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT应用于单个SELECT
-- 请将子句放在括住select的括号内
select num,name from (select num,name from student_local limit 2) subq1
union
select num,name from (select num.name from student_hdfs limit 3) subq2;
-- 如果放在最后，则是对联合后的表操作

from子句中子查询（Subqueries）

• 再Hive0.12版本，仅再FROM子句中支持子查询。必须给子查询一个名称，因为from子句中的每个表都必须有一个名称。子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列再外部查询中可用，就像真实表的列一样。子查询也可以是嗲有UNION的查询表达式
• Hive支持任意级别的子查询，也就是所谓的嵌套子查询
• Hive0.13.0和更高版本中的子查询名称之前可以包含可选关键字AS
• 从Hiv0.13开始，where子句支持下述类型的子查询
  • 不相关子查询：该子查询不引用父查询中的列，可以将查询结果是为IN和NOT IN语句的常量
  • 相关子查询：子查询引用父查询中的列

-- from子句中子查询
-- 子查询
select num
from(
    select num,name from student_local
) tmp;

-- 包含UNION ALL的子查询示例
select t3.name
from(
    select num,name from student_local
    union all
    select num,name from student_hdfs
) t3;

-- where 子句中子查询
-- 不相关子查询，相当于IN、NOT IN子查询中鞥选择一个列
-- （1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用
-- （2）执行外部查询，并显示整个结果
select *
from student_hdfs
where student_hdfs.num IN (select num from student_local limit 2);

-- 相关子查询，指EXISTS 和 NOT EXISTS子查询
select A
from T1
where exists (select B from T2 where T1.X = T2.Y);

5.3.Common Table Expressions(CTE)

CTE介绍

• 公用表达式（CTE）是一个临时结果集：该结果集从WITH子句中指定的简单查询派生而来的，紧接在select或insert关键字之前
• CTE仅在单个语句的执行范围内定义
• CTE可以在select、insert、create table as select或create view as select语句中使用

[WITH CommentTableExpression (, CommentTableExpression)*]
SELECT ...;

-- select语句中的CTE
with q1 as (select num,name,age from student where num = 95002)
select *
from q1;

-- from风格
with q1 as (select num,name,age from student where num = 95002)
from q1
select *;

-- chaining CTEs链式
with q1 as (select num,name,age from student where num = 95002)，
     q2 as (select num,name,age from q1)
select * from (select num from q2) a;

-- union
with q1 as (select * from student where num = 95002),
     q2 as (select * from student where num = 95004)
select * from q1 union all select * from q2;

-- 视图，CTAS和插入语句中的CTE
-- insert
create table s1 like student;

with q1 as (select * from where num=95002)
from q1
insert overwrite table s1
select *;

select * from s1;

-- ctas
create table s2 as
with q1 as (select * from student where num = 95002)
select * from q1;

-- view
create view v1 as
with q1 as (select * from student where num = 95002)
select * from q1;

select * from v1;

6.Hive SQL Join连接操作

join语法用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据
在Hive 3.1.2总共支持6种join语法：inner join(内连接)、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（做半开连接）、cross join （交叉连接，也叫笛卡尔积）

6.1 join语法规则

join语法规则

• table_reference：是join查询种使用的表名，也可以是子查询别名（查询结果当成表参与join）
• table_factor：与table_reference相同，是连接查询种使用的表名，也可以是子查询别名
• join_condition：join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字

join_table:
  table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table _reference [join_condition] (as of Hive1.0)

join_condition:
  ON expression

join语法丰富化

• Hive种join语法从面世开始其实并不丰富
• 从Hive 0.13.0开始，支持隐式联接表示法。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字
• 从Hive 2.2.0开始，支持ON子句种的复杂表达式，支持不相等连接。

6.2 6中join方式详解

join查询环境准备
准备3张表并加载数据到表种

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

-- table3:员工联系方式信息表
create table employee_connection(
    id int,
    phno string,
    email string
) row format delimited
fields terminated by ',';

inner join内连接

• 内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join == join
• 只有进行连接的两个表种都存在与连接条件相匹配的数据才会被留下

-- 1.inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id = e_a.id;

-- 等价于inner join = join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id = e_a.id;

-- 等价于 隐式连接表达法
-- 等价于inner join = join
select e.id,e.name,e_a.city,e_a.street
from employee e ,employee_address e_a
on e.id = e_a.id;

left join左连接

• left join中文名左外连接或者左连接
• left join在连接时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回

-- 2.left join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id = e_conn.id;

-- 等价于left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join employee_connection e_conn
on e.id = e_conn.id;

right join右连接

• right join中文叫做右外连接或者右连接
• right join在连接时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回

-- 2.right join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right join employee_connection e_conn
on e.id = e_conn.id;

-- 等价于left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right outer join employee_connection e_conn
on e.id = e_conn.id;

full outer join全外连接

• full outer join等价full join，中文叫做全外连接或外连接
• 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配行

-- 4.full outer join
select e.id,e.name,e_a.city,e_a.street
from employee e full outer join employee_address e_a
on e.id = e_a.id;

-- 等价于
select e.id,e.name,e_a.city,e_a.street
from employee e full join employee_address e_a
on e.id = e_a.id;

left semi join左半开连接

• 左半开连接会返回左边表的记录，前提时其记录对于右边的表满足on语句中的判定条件
• 从效果上来看有点像inner join之后只返回左表结果

-- 5.left semi join
select *
from employee e left semi join employee_address e_addr
on e.id = e.addr;

-- 相当于inner join,但是只返回左表全部数据，只不过效率高一些
select *
from employee e inner join employee_address e_addr
on e.id = e.addr;

cross join交叉连接

• 交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用
• 在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积，无需指定关联键。
• 在HiveSQL语法中，cross join后面可以跟where子句进行过滤，或者on条件过滤

-- 6.cross join
-- 下列A、B、C执行结果相同，但是效率不一样
-- A:
select a.*,b.* from employee a.employee_addtess b where a.id=b.id;
-- B:
select * from employee a cross join employee_address b on a.id=b.id;
select * from employee a cross join employee_address b where a.id=b.id;
-- C:
select * from employee a inner join employee_address b on a.id=b.id;
-- 一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后根据where条件从中选择
-- 因此，如果两个需要求交集的表太大，将会非常非常慢，不建议使用

6.3 join使用注意事项

1. 允许使用复杂的连接表达式，支持非等值连接

select a.* from a join b on (a.id = b.id);
select a.* from a join b on (a.id = b.id and a.department = b.department);
select a.* from a left outer join b on (a.id <> b.id);

2. 同一查询中可以连接2个以上的表

select a.val,b.val from a join b on (a.key = b.key1) join c on (c.key = b.key2);

3. 如果每个表在连接子句中使用相同的列，则Hive将多个表上的连接转换为单个MR作业

select a.val,b.val,c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1);

4. join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需的内存
5. 在join的时候，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表

select /*+ STREAMTABLE(a) */ a.val,b.val,c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1);
-- a,b,c三个表都在一个MR作业中连接，并且表b和c的键特定的值被缓冲在reducer的缓存中
-- 然后，对于从a中检索到的每一行，将使用缓冲的行来计算连接，如果省略STREAMTABLE提示，则Hive将流式传输最右边的表

6. join在WHERE条件之前进行
7. 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行（mapjoin）

select /*+ MAPJOIN(b)*/ a.key,a.value from a join b on a.key = b.key;
-- 不需要reducer，对于A的每个Mapper，B都会被完全读取，限制是不能执行FULL/RIGHT OUTER JOIN b