第3关：将 select 查询结果插入 hive 表中

• 任务描述
• 相关知识
  • 单表插入
  • 多表插入
• 编程要求
• 测试说明

---

任务描述

本关任务：根据编程要求将select查询结果插入hive表中。

相关知识

为了完成本关任务，你需要掌握：1. 单表插入，2. 多表插入。

通过使用查询子句从其他表中获得查询结果，然后使用INSERT命令把数据插入到Hive新表中（Hive会根据MapReduce中的reduce任务个数在HDFS上的hive新表目录下创建相应的数据文件000000_0，若有多个reduce任务，依次以000001_0、000002_0、…… 类推）。

该操作包括表单插入（一次性向一个hive表插入数据）和多表插入（一次性向多个hive表插入数据）。INSERT命令可以操作在表和特定的分区上，如果属于分区表，必须指明所有分区列和其对应的分区列属性值。

单表插入

• 单表插入语法：

1. INSERT OVERWRITE TABLE tablename [PARTITION (partcol1=val1,partcol2=val2,……) [IF NOT EXISTS]] SELECT select_statement FROM from_statement;

该方法会 覆盖 表或分区中的数据（若对特定分区指定IF NOT EXISTS将不执行覆盖操作）。

如查询items_info表，把查询结果放到items_info2表中：

1. hive> insert overwrite table items_info2
2. > partition(p_category='clothes',p_brand='playboy')
3. > select * from items_info ii
4. > where ii.categroy

• 单表插入语法（ 追加 方式）

1. INSERT INTO TABLE tablename [PARTITION (partcol1=val1,partcol2=val2,……) ] SELECT select_statement FROM from_statement;

该方法以追加的方式把SELECT子句返回的结果添加到表或分区中。

多表插入

1. FROM from_statement
2. INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1,partcol2=val2…) [IF NOT EXISTS]] SELECT select_statement1
3. [INSERT OVERWRITE TABLE tablename2 [PARTITION … [IF NOT EXISTS]] SELECT select_statement2]
4. [INSERT INTO TABLE tablename2 [PARTITION … ] SELECT select_statement2]…;

多表插入操作的开始第一条命令指定所有表执行的SELECT命令所对应的FROM 子句，针对同一个表，既可以执行INSERT OVERWRITE操作，也可以执行 INSERT INTO操作（如表tablename2）。

多表插入操作可以降低源表的扫描次数，Hive可以通过仅扫描一次数据源表，然后针对不同的Hive表应用不同的查询规则从扫描结果中获取目标数据插入到不同的Hive表中。

如把从items_info中扫描的结果根据不同的查询规则插入到表的不同分区中：

1. hive> FROM items_info ii
2. > INSERT INTO TABLE items_info2
3. > PARTITION (p_category='clothes',p_brand='playboy')
4. > SELECT * WHERE ii.category='clothes' AND ii.brand='playboy'
5. > INSERT OVERWRITE TABLE items_info2
6. > PARTITION (p_category='shoes',p_brand='playboy')
7. > SELECT * WHERE ii.category='shoes' AND ii.brand='playboy'

编程要求

在test3数据库中有student表，表中数据如下：

Sno	name	age	sex	score(Chinese-Math-English)
001	Xiaohong	18	female	96-88-90.5
002	Xiaoliang	17	male	95-88-93.5
003	Xiaoming	19	male	86.5-98-91
004	Xiaoguang	18	male	88-80-94
005	Xiaohua	16	female	97-58.5-88

• 复制student表两份，分别名为：student2、student3 （只复制表结构不复制数据，可参考：Hive表DDL操作（一）第二关）

• 以覆盖插入的方式把student表中前两条数据插入到student2中

• 以追加插入的方式把student表中前两条数据插入到student2中

• 以覆盖插入的方式把student表中年龄大于17岁的数据插入到student2、student3中

• 以追加插入的方式把student表中的男生数据插入到student2，以覆盖插入的方式把女生数据插入到student3中

测试说明

平台会对你编写的命令进行测试：

若操作成功，会显示如下信息：

1. 2 Xiaoliang 17 male {"chinese":95.0,"math":88.0,"english":93.5}
2. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
3. 2 Xiaoliang 17 male {"chinese":95.0,"math":88.0,"english":93.5}
4. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
5. 2 Xiaoliang 17 male {"chinese":95.0,"math":88.0,"english":93.5}
6. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
7. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
8. 3 Xiaoming 19 male {"chinese":86.5,"math":98.0,"english":91.0}
9. 4 Xiaoguang 18 male {"chinese":88.0,"math":80.0,"english":94.0}
10. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
11. 3 Xiaoming 19 male {"chinese":86.5,"math":98.0,"english":91.0}
12. 4 Xiaoguang 18 male {"chinese":88.0,"math":80.0,"english":94.0}
13. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
14. 3 Xiaoming 19 male {"chinese":86.5,"math":98.0,"english":91.0}
15. 4 Xiaoguang 18 male {"chinese":88.0,"math":80.0,"english":94.0}
16. 2 Xiaoliang 17 male {"chinese":95.0,"math":88.0,"english":93.5}
17. 3 Xiaoming 19 male {"chinese":86.5,"math":98.0,"english":91.0}
18. 4 Xiaoguang 18 male {"chinese":88.0,"math":80.0,"english":94.0}
19. 1 Xiaohong 18 female {"chinese":96.0,"math":88.0,"english":90.5}
20. 5 Xiaohua 16 female {"chinese":97.0,"math":58.5,"english":88.0}

---

开始你的任务吧！祝你成功。

--Begin
--使用test3数据库
use test3;
--复制student表两份，分别名为：student2、student3
create table if not exists student2 
like student;

create table if not exists student3 
like student;
--以覆盖插入的方式把student表中前两条数据插入到student2中
insert overwrite table student2 
select * from student limit 2;

--评测代码，勿删
select * from student2;

--以追加插入的方式把student表中前两条数据插入到student2中
insert into table student2 
select * from student limit 2;

--评测代码，勿删
select * from student2;

--以覆盖插入的方式把student表中年龄大于17岁的数据插入到student2、student3中
from student
insert overwrite table student2
select * where student.age > 17
insert overwrite table student3
select * where student.age > 17;


--评测代码，勿删
select * from student2;
select * from student3;

--以追加插入的方式把student表中的男生数据插入到student2，以覆盖插入的方式把女生数据插入到student3中
from student
insert into table student2
select * where student.sex = 'male'
insert overwrite table student3
select * where student.sex = 'female';

--评测代码，勿删
select * from student2;
select * from student3;
--End

实现结果如图所示：