hive 删除分区_Hive(四) DDL命令

HQL操作之-DDL命令

参考:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

Hive表结构.png

查询数据库信息

1-- 查看数据库信息
2desc database mydb2;
3desc database extended mydb2; 
4describe database extended mydb2;

删除数据库

1-- 删除一个空数据库
2drop database databasename;
3-- 如果数据库不为空，使用 cascade 强制删除 
4drop database databasename cascade;

内部表和外部表

• 默认创建内部表，如果创建外部表需要使用关键字external；create external table

• 删除内部表，会把数据和表结构都删除

• 删除外部表，只删除表结构

• 生产一般都是外部表

创建内部表：

 1-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
 2create table t1(
 3  id int,
 4  name string,
 5  hobby array<string>,
 6  addr map<string,string>
 7)
 8row format delimited
 9fields terminated by ";"
10collection items terminated by ","
11map keys terminated by ":";
12
13-- 查看表信息
14desc t1;
15
16desc formatted t1;
17
18--加载数据
19load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
20-- 查询数据：
21select * from t1;
22
23-- 查询数据位置：
24dfs -ls /user/hive/warehouse/mydb.db/t1
25dfs -cat /user/hive/warehouse/mydb.db/t1/t1.dat;
26
27-- 删除表：
28drop table t1;
29
30-- 再次查看：数据也没了
31dfs -ls /user/hive/warehouse/mydb.db;

创建外部表：

 1-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
 2create external table t1(
 3  id int,
 4  name string,
 5  hobby array<string>,
 6  addr map<string,string>
 7)
 8row format delimited
 9fields terminated by ";"
10collection items terminated by ","
11map keys terminated by ":";
12
13-- 查看表信息
14desc t1;
15
16desc formatted t1;
17
18--加载数据
19load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
20-- 查询数据：
21select * from t1;
22
23-- 查询数据位置：
24dfs -ls /user/hive/warehouse/mydb.db/t1
25dfs -cat /user/hive/warehouse/mydb.db/t1/t1.dat;
26
27-- 删除表：
28drop table t1;
29
30-- 再次查看：数据还在
31dfs -ls /user/hive/warehouse/mydb.db;

内部表外部表转换

 1-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
 2create table t1(
 3  id int,
 4  name string,
 5  hobby array<string>,
 6  addr map<string,string>
 7)
 8row format delimited
 9fields terminated by ";"
10collection items terminated by ","
11map keys terminated by ":";
12
13-- 查看表信息
14desc t1;
15
16desc formatted t1;
17
18--加载数据
19load data local inpath '/mnt/hadoop/data/t1.dat' into table t1;
20-- 查询数据：
21select * from t1;
22
23
24
25-- 内部表转外部表
26alter table t1 set tblproperties('EXTERNAL'='TRUE'); 
27-- 查询表信息，是否转换成功
28desc formatted t1;
29-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
30alter table t1 set tblproperties('EXTERNAL'='FALSE'); 
31-- 查询表信息，是否转换成功
32desc formatted t1;

分区表

作用：避免全表扫描，提高查询效率

分区表创建和数据加载：

 1-- 默认导入数据的时候，是使用^A，^B，^C分割。下面自定义为 ; , :。
 2create table if not exists t1(
 3  id int,
 4  name string,
 5  hobby array<string>,
 6  addr map<string,string>
 7)
 8partitioned by (dt string)
 9row format delimited
10fields terminated by ";"
11collection items terminated by ","
12map keys terminated by ":";
13-- 加载数据。
14load data local inpath "/mnt/hadoop/data/t1.dat" into table t1 partition(dt="2020-06-01");
15load data local inpath "/mnt/hadoop/data/t1.dat" into table t1 partition(dt="2020-06-02");
16
17-- 查看有几个分区
18show partitions t1;
19-- 增加分区
20alter table t1 add partition(dt="2020-06-03");
21
22-- 增加多个分区，不加载数据
23alter table t1;
24add partition(dt='2020-06-05') partition(dt='2020-06-06');
25-- 增加多个分区。准备数据
26hdfs dfs -cp /user/hive/warehouse/mydb.db/t1/dt=2020-06-01 /user/hive/warehouse/mydb.db/t1/dt=2020-06-07
27hdfs dfs -cp /user/hive/warehouse/mydb.db/t1/dt=2020-06-01 /user/hive/warehouse/mydb.db/t1/dt=2020-06-08
28-- 增加多个分区。加载数据
29alter table t1 add
30partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t1/dt=2020-06-07' partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t1/dt=2020-06-08';
31-- 查询数据
32select * from t1;
33
34-- 修改分区的hdfs路径：
35alter table t1 partition(dt='2020-06-01') set location '/user/hive/warehouse/t1/dt=2020-06-03';
36
37-- 可以删除一个或多个分区，用逗号隔开
38alter table t1 drop partition(dt='2020-06-03'), partition(dt='2020-06-04');
39-- 查看某个分区的数据：
40select * from t1 where dt='2020-06-01';

分区的字段，不是表中已经存在的数据，可以将分区字段看成伪列

分桶表

当单个分区或者表的数据量过大，分区不能更细的粒度划分数据，就需要分桶技术划分。

原理：

• MR：key.hashCode % reduceTask (默认分区)

• Hive中。分桶字段.hashCode % 分桶个数

 1-- 创建分桶表
 2create table course(
 3    id int,
 4    name string,
 5    score int
 6)
 7clustered by (id) into 3 buckets
 8row format delimited fields terminated by ",";
 9
10create table course_common(
11id int,
12name string,
13score int )
14row format delimited fields terminated by ",";
15-- 普通表加载数据
16load data local inpath '/mnt/hadoop/data/course.dat' into table course_common;
17-- 通过 insert ... select ... 给桶表加载数据
18insert into table course select * from course_common;
19-- 观察分桶数据。数据按照:(分区字段.hashCode) % (分桶数) 进行分区

• 分桶规则：分桶字段.hashCode % 分桶数

• 分桶表加载数据时，使用 insert……select…… 方式进行

• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive 1.x 以前的版本;Hive 2.x 中，删除了该参数，始终可以分桶;

修改表和删除表

 1--修改表名
 2alter table t1 rename to t2;
 3-- 修改列名
 4alter table t2 change column id cid int;
 5-- 修改字段类型 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是 string不能转为int
 6alter table t2 change column cid cid string;
 7-- 增加字段
 8alter table t2 add column (common string);
 9-- 删除字段
10alter table t2 replace columns (cid string,cname string,score int);
11-- 删除表
12drop table t2;

HQL DDL小结：

• 操作对象：库、表

• 表的分类：

• 内部表：表数据和元数据都删除

• 外部表：只删除元数据

• 分区表：按照指定字段分区，将数据放到不同的目录，提高SQL查询性能

• 分桶表：按照分桶字段，将表中字段分开。分桶字段.hashCode % 分桶数据

• 命令：create、alter、drop