Hive数据操控、查询语言（DML、DQL）

1 DML-Load加载数据

1.1 Hive Load 功能与语法规则

概述：

• 在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
• 文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
• 也可以在建表的时候使用location语句指定任意路径。
• 不管路径在哪里，只有把数据文件移动到对应的表文件夹下面，Hive才能映射解析成功;
• 最原始暴力的方式就是使用hadoop fs –put|-mv等方式直接将数据移动到表文件夹下；
• 但是，Hive官方推荐使用Load命令将数据加载到表中。

Load功能：

• Load英文单词的含义为：加载、装载；
• 所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。
• 纯复制、移动指在数据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表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。

• filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：

  • 相对路径，例如：project/data1

  • 绝对路径，例如：/user/hive/project/data1

  • 具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

LOCAL：

• 指定LOCAL， 将在本地文件系统中查找文件路径。

  • 若指定相对路径，将相对于用户的当前工作目录进行解释；
  • 用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。

• 没有指定LOCAL关键字。

  • 如果filepath指向的是一个完整的URI，会直接使用这个URI；
  • 如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）

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

OVERWRITE：

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

示例：

-- 从本地加载数据  数据位于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 Hive 3.0 新特性

Hive3.0+，load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为INSERT AS SELECT。

Hive3.0+，还支持使用inputformat、SerDe指定输入格式，例如Text，ORC等。

比如，如果表具有分区，则load命令没有指定分区，则将load转换为INSERT AS SELECT，并假定最后一组列为分区列，如果文件不符合预期，则报错。

示例：

本来加载的时候没有指定分区，语句是报错的，但是文件的格式符合表的结构，前两个是col1,col2,最后一个是分区字段col3，则此时会将load语句转换成为insert as select语句。

-------hive 3.0 load命令新特性------------------
CREATE TABLE if not exists tab1 (col1 int, col2 int)
PARTITIONED BY (col3 int)
row format delimited fields terminated by ',';

LOAD DATA LOCAL INPATH '/root/hivedata/tab1.txt' INTO TABLE tab1;

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

2 DML-Insert插入数据

2.1 Hive Insert使用方式

RDBMS中使用insert：

• 在MySQL这样的RDBMS中，通常使用insert+values的方式来向表插入数据，并且执行速度很快。
• 这也是RDBMS中表插入数据的核心方式。
• 假如把Hive当成RDBMS，用insert+values的方式插入数据，会如何？
• 执行过程非常非常慢，原因在于底层是使用MapReduce把数据写入Hive表中
• 试想一下，如果在Hive中使用insert+values，对于大数据环境一条条插入数据，用时难以想象。

Hive官方推荐加载数据的方式：清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。但是并不意味insert语法在Hive中没有用武之地。

insert+select：

• 将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。
• 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
• 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为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;

2.2 Multiple Inserts多重插入

概述：

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

------------multiple inserts----------------------
--当前库下已有一张表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 Inserts动态分区插入

背景：

• 对于分区表的数据导入加载，最基础的是通过load命令加载数据。
• 在load过程中，分区值是手动指定写死的，叫做静态分区。

概述：

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

示例：

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

--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作为分区字段值

select *
from student_partition;

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事务表实现原理

概述：Hive的文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改，只能是采取另外的手段来完成。

• 用HDFS文件作为原始数据（基础数据），用delta保存事务操作的记录增量数据；

• 正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹。每次执行一次事务操作都会有这样的一个delta增量文件夹;

• 当访问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文件夹下，都有两个文件：

• _orc_acid_version的内容是2,即当前ACID版本号是2。和版本1的主要区别是UPDATE语句采用了split-update特性，即先删除、后插入。这个文件不是ORC文件，可以下载下来直接查看。

• bucket_00000文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以ORC格式存储，底层二级制，需要使用ORC TOOLS查看。

bucket_00000文件字段介绍：

• 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事务表使用设置与局限性

使用设置：

• client端：

可以使用set设置当前session生效 也可以配置在hive-site.xml中）
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实例上运行多少个合并程序工作线程。

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

1.尚不支持BEGIN，COMMIT和ROLLBACK，所有语言操作都是自动提交的；

2.表文件存储格式仅支持ORC（STORED AS ORC）；

3.需要配置参数开启事务使用；

4.外部表无法创建为事务表，因为Hive只能控制元数据，无法管理数据；

5.表属性参数transactional必须设置为true；

6.必须将Hive事务管理器设置为org.apache.hadoop.hive.ql.lockmgr.DbTxnManager才能使用ACID表；

7.事务表不支持LOAD DATA …语句。

4 DQL-Select查询数据

4.1 语法树

• 从哪里查询取决于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,] rows];

执行顺序：

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

4.2 select查询基础语法

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

--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 distinct county,state from t_usa_covid19_p;

select distinct sex from student;

WHERE

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

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

--注意：where条件中不能使用聚合函数
--报错 SemanticException:Not yet supported place for UDAF 'count'
--聚合函数要使用它的前提是结果集已经确定。
--而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。
select state,count(deaths)
from t_usa_covid19_p where count(deaths) >100 group by state;

--可以使用Having实现
select state,count(deaths)
from t_usa_covid19_p  group by state
having count(deaths) > 100;

分区查询、分区裁剪

• 针对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分组的字段；要么是被聚合函数应用的字段。

语法限制：

• 出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段**；**要么是被聚合函数应用的字段。

• 原因：避免出现一个字段多个值的歧义。

  • 分组字段出现select_expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同；

  • 被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果。

--5、GROUP BY
--根据state州进行分组
--SemanticException:Expression not in GROUP BY key 'deaths'
--deaths不是分组字段 报错
--state是分组字段 可以直接出现在select_expr中
select state,deaths
from t_usa_covid19_p where count_date = "2021-01-28" 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语句中不能使用聚合函数 语法报错
select state,sum(deaths)
from t_usa_covid19_p where count_date = "2021-01-28" and sum(deaths) >10000 group by state;

--先where分组前过滤（此处是分区裁剪），再进行group by分组， 分组后每个分组结果集确定 再使用having过滤
select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having 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
having cnts> 10000;

having和where的区别：

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

LIMIT

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

--7、limit
--没有限制返回2021.1.28 加州的所有记录
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California";

--返回结果集的前5条
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
limit 5;

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

4.3 select查询高阶语法

ORDER BY

• Hive SQL中的ORDER BY语法类似于标准SQL语言中的ORDER BY语法，会对输出的结果进行全局排序。因此当底层使用MapReduce引擎执行的时候，只会有一个reducetask执行。如果输出的行数太大，会导致需要很长的时间才能完成全局排序。
• 默认排序为升序（ASC），也可以指定为DESC降序。
• 在Hive 2.1.0和更高版本中，支持在ORDER BY子句中为每个列指定null类型结果排序顺序。ASC顺序的默认空排序顺序为NULLS FIRST，而DESC顺序的默认空排序顺序为NULLS LAST。

---1、order by
--根据字段进行排序
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths; ----默认asc, nulls first 也可以手动指定nulls last

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

--强烈建议将LIMIT与ORDER BY一起使用。避免数据集行数过大
--当hive.mapred.mode设置为strict严格模式时，使用不带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散列（分桶表规则一样）：Hash_Func(col_name) % reducetask个数
• 分为几组取决于reducetask的个数

--2、cluster by
select * from student;

--不指定reduce task个数
--日志显示：Number of reduce tasks not specified. Estimated from input data size: 1
select * from student cluster by num;

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

局限性：

• 需求：根据sex性别分为两个部分，每个分组内再根据age年龄的倒序排序。
• CLUSTER BY无法单独完成，因为分和排序的字段只能是同一个;
• ORDER BY更不能在这里使用，因为是全局排序，只有一个输出，无法满足分的需求。

DISTRIBUTE BY +SORT BY

• DISTRIBUTE BY +SORT BY就相当于把CLUSTER BY的功能一分为二：

• DISTRIBUTE BY负责根据指定字段分组；

• SORT BY负责分组内排序规则。

• 分组和排序的字段可以不同。

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

CLUSTER、 DISTRIBUTE、SORT、ORDER BY区别：

• order by全局排序，因此只有一个reducer，结果输出在一个文件中，当输入规模大时，需要较长的计算时间。
• distribute by根据指定字段将数据分组，算法是hash散列。sort by是在分组之后，每个组内局部排序。
• cluster by既有分组，又有排序，但是两个字段只能是同一个字段，且只能正序排序。
• 如果distribute和sort的字段是同一个时，此时，cluster by = distribute by + sort by

Union联合查询

概述：UNION用于将来自于多个SELECT语句的结果合并为一个结果集。

• 使用DISTINCT关键字与只使用UNION默认值效果一样，都会删除重复行。1.2.0之前的Hive版本仅支持UNION ALL，在这种情况下不会消除重复的行。
• 使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）。
• 每个select_statement返回的列的数量和名称必须相同。

语法：

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

--使用DISTINCT关键字与使用UNION默认值效果一样，都会删除重复行。
select num,name from student_local
UNION
select num,name from student_hdfs;

--和上面一样
select num,name from student_local
UNION DISTINCT
select num,name from student_hdfs;

--使用ALL关键字会保留重复行。
select num,name from student_local
UNION ALL
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

--如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句应用于整个UNION结果
--请将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一个之后。
select num,name from student_local
UNION
select num,name from student_hdfs
order by num desc;

from子句中的子查询（Subqueries）

• 在Hive0.12版本，仅在FROM子句中支持子查询。
• 必须要给子查询一个名称，因为FROM子句中的每个表都必须有一个名称。子查询返回结果中的列必须具有唯一的名称。子查询返回结果中的列在外部查询中可用，就像真实表的列一样。子查询也可以是带有UNION的查询表达式。
• Hive支持任意级别的子查询，也就是所谓的嵌套子查询。
• Hive 0.13.0和更高版本中的子查询名称之前可以包含可选关键字AS。

--from子句中子查询（Subqueries）
--子查询
SELECT num
FROM (
select num,name from student_local
) tmp;

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

where子句中子查询（Subqueries）

从Hive 0.13开始，WHERE子句支持下述类型的子查询：

• 不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为IN和NOT IN语句的常量；
• 相关子查询：子查询引用父查询中的列；

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

--相关子查询，指EXISTS和NOT EXISTS子查询
--子查询的WHERE子句中支持对父查询的引用
SELECT A
FROM T1
WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y);

CTE

概述：

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

-----------------Common Table Expressions（CTE）-----------------------------
--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 * 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 student 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;

5 Join连接操作

在Hive中，当下版本3.1.2总共支持6种join语法。分别是：inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join（交叉连接，也叫做笛卡尔乘积）。

5.1 Hive Join语法规则

语法：

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 Hive 0.10)

join_condition:
ON expression

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

join语法丰富化：

• Hive中join语法从面世开始其实并不丰富，不像在RDBMS中那么灵活。
• 从Hive 0.13.0开始，支持隐式联接表示法（请参阅HIVE-5558）。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。
• 从Hive 2.2.0开始，支持ON****子句中的复杂表达式，支持不相等连接（请参阅HIVE-15211和HIVE-15251）。在此之前，Hive不支持不是相等条件的联接条件。

5.2 Hive 6种Join方式详解

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

--等价于 隐式连接表示法
select e.id,e.name,e_a.city,e_a.streetfrom employee e , employee_address e_a
where e.id =e_a.id;

5.2.2 left join（左连接）

• left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。
• left join的核心就在于left左。左指的是join关键字左边的表，简称左表。
• 通俗解释：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;

5.2.3 right join（右连接）

• right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。
• right join的核心就在于Right右。右指的是join关键字右边的表，简称右表。
• 通俗解释：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回。
• 很明显，right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的问题。

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

--等价于 right 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;

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

5.2.5 left semi join（左半开连接）

• 左半开连接（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.id;

--相当于 inner join 只不过效率高一些
select e.*
from employee e inner join employee_address e_addr
on e.id =e_addr.id;

5.2.6 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_address 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条件从中选择。
--因此，如果两个需要求交集的表太大，将会非常非常慢，不建议使用。

5.3 Hive Join使用注意事项

a)允许使用复杂的联接表达式,支持非等值连接

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)

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

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

c) 如果每个表在联接子句中使用相同的列，则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)
--由于联接中仅涉及b的key1列，因此被转换为1个MR作业来执行
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
--会转换为两个MR作业，因为在第一个连接条件中使用了b中的key1列，而在第二个连接条件中使用了b中的key2列。
-- 第一个map / reduce作业将a与b联接在一起，然后将结果与c联接到第二个map / reduce作业中。

d) join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
--由于联接中仅涉及b的key1列，因此被转换为1个MR作业来执行，并且表a和b的键的特定值的值被缓冲在reducer的内存中。然后，对于从c中检索的每一行，将使用缓冲的行来计算联接。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
--计算涉及两个MR作业。其中的第一个将a与b连接起来，并缓冲a的值，同时在reducer中流式传输b的值。
-- 在第二个MR作业中，将缓冲第一个连接的结果，同时将c的值通过reducer流式传输。

e) 在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将流式传输最右边的表。

f) join在WHERE条件之前进行。

g) 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅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。