外部分区表——头歌

任务描述

本关任务：根据相关知识内容实现 Hive 外部分区表的操作。

相关知识

为了完成本关任务，你需要掌握： 1.外部分区表的创建 2.数据加载 3.查询分区表

内外部分区表的区别

• Hive 创建内部表时（默认创建内部表），会将数据移动到数据仓库指向的路径；创建外部表（需要加关键字EXTERNAL），仅记录数据所在的路径，不对数据的位置做任何改变。

• 在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

创建外部分区表

通过EXTERNAL关键字指定为外部表，PARTITIONED BY子句指定为分区。

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

1. CREATE EXTERNAL TABLE IF NOT EXISTS dept_partition(
2. deptno int,
3. dname string,
4. loc string
5. )
6. PARTITIONED BY (day string) ## 分区
7. ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ## 列分隔符
8. STORED AS TEXTFILE; ## 存储类型

加载数据到分区表中

将本地/root路径下的日志数据根据时间导入到不同分区中。

1. load data local inpath '/root/dept_20220301.log' into table dept_partition partition(day='20220301');
2. load data local inpath '/root/dept_20220302.log' into table dept_partition partition(day='20220302');
3. load data local inpath '/root/dept_20220303.log' into table dept_partition partition(day='20220303');

查询分区表中数据

通过WHERE条件语句查询不同分区表中的数据。

• 单分区查询
  1. select * from dept_partition where day='20220301';
• 多分区联合查询
  1. select * from dept_partition where day='20220301'
  2. union
  3. select * from dept_partition where day='20220302'
  4. union
  5. select * from dept_partition where day='20220303';

查看分区表结构

• desc 查看表的简要结构。

1. desc dept_partition;

• desc formatted 查看表的详细结构。

1. desc formatted dept_partition;

编程要求

请根据右侧命令行内的提示，在Begin - End区域内进行sql语句代码补充，具体任务如下：

• 创建内部分区表：staff；设置分区字段为department，类型为string

• 将不同部门员工的数据加载到相应的部门分区中：/root/department/

• 查询staff表中sales和operations部门的员工数据

• 查看staff表简要结构

staff表结构：

INFO	TYPE
id	int
name	string
age	int

部分数据如下：

1. 20190101,小敏,21
2. 20190102,晓明,22
3. 20190103,小舟,20

数据切分方式：逗号（,）

销售部数据所在目录：/root/department/sales.txt 开发部数据所在目录：/root/department/development.txt 运营部数据所在目录：/root/department/operations.txt

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. 20190101 小敏 21 operations
2. 20190102 晓明 22 operations
3. 20190103 小舟 20 operations
4. 20190107 李四 23 sales
5. 20190108 王五 22 sales
6. 20190109 张三 21 sales
7. id int
8. name string
9. age int
10. department string
12. # Partition Information
13. # col_name data_type comment
15. department string

---

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

代码如下

---创建mydb数据库  
create database if not exists mydb;
---使用mydb数据库  
use mydb;
---------- Begin ----------
---创建staff外部分区表    
create external table if not exists staff(    
    id int,    
    name string,    
    age int     
)    
partitioned by (department string)    
row format delimited fields terminated by ','    
stored as textfile;
---将不同部门员工的数据加载到相应的部门分区中：/root/department/    
load data local inpath '/root/department/operations.txt' into table staff partition(department='operations');    
load data local inpath '/root/department/development.txt' into table staff partition(department='development');    
load data local inpath '/root/department/sales.txt' into table staff partition(department='sales');
---查询staff表中sales和operations部门的员工数据   
select * from staff where department='sales' or department='operations';
---查看staff表简要结构   
desc staff;
---------- End ----------
---删除student表  
drop table mydb.staff;