Hive 表的修改和删除
文章目录
表重命名
基本语法：
alter table old_table_name rename to new_table_name;
把表score4修改成score5
alter table score4 rename to score5;
hive (myhive)> show tables;
OK
tab_name
course
course_common
score
score2
score4
stu
stu2
stu3
stu4
student
teacher
test1
Time taken: 0.009 seconds, Fetched: 12 row(s)
hive (myhive)> alter table score4 rename to score5;
OK
Time taken: 0.082 seconds
hive (myhive)> show tables;
OK
tab_name
course
course_common
score
score2
score5
stu
stu2
stu3
stu4
student
teacher
test1
Time taken: 0.008 seconds, Fetched: 12 row(s)
hive (myhive)>
可以看到，score4的表名被换成score5了。
增加/修改列信息
（1）查询表结构
desc score5;
hive (myhive)> desc score5;
OK
col_name data_type comment
s_id string
c_id string
s_score int
month string
# Partition Information
# col_name data_type comment
month string
Time taken: 0.046 seconds, Fetched: 9 row(s)
hive (myhive)>
（2）添加列
alter table score5 add columns (mycol string, mysco string);
hive (myhive)> alter table score5 add columns (mycol string, mysco string);
OK
Time taken: 0.049 seconds
hive (myhive)> desc score5;
OK
col_name data_type comment
s_id string
c_id string
s_score int
mycol string
mysco string
month string
# Partition Information
# col_name data_type comment
month string
Time taken: 0.092 seconds, Fetched: 11 row(s)
hive (myhive)>
mycol的意思是my column 我的列
mysco的意思是my second column 我的第二个列
可以看到，多出了两个列。
（3）更新列
alter table score5 change column mysco mysconew int;
hive (myhive)> alter table score5 change column mysco mysconew int;
OK
Time taken: 0.163 seconds
hive (myhive)> desc score5;
OK
col_name data_type comment
s_id string
c_id string
s_score int
mycol string
mysconew int
month string
# Partition Information
# col_name data_type comment
month string
Time taken: 0.064 seconds, Fetched: 11 row(s)
hive (myhive)>
这里把string类型的mysco列，更改成了 int 类型的mysconew列。
同时修改了列名和列的类型。
删除表
drop table score5;
清空表数据
只能清空内部表
truncate table score5;
重命名分区
把score的201806分区改成201906
alter table score partition(month='201806') rename to partition(month='201906');
hive (myhive)> select * from score;
OK
score.s_id score.c_id score.s_score score.month
01 01 80 201806
01 02 90 201806
01 03 99 201806
02 01 70 201806
02 02 60 201806
02 03 80 201806
03 01 80 201806
03 02 80 201806
03 03 80 201806
04 01 50 201806
04 02 30 201806
04 03 20 201806
05 01 76 201806
05 02 87 201806
06 01 31 201806
06 03 34 201806
07 02 89 201806
07 03 98 201806
Time taken: 0.079 seconds, Fetched: 18 row(s)
hive (myhive)> alter table score partition(month='201806') rename to partition(month='201906');
OK
Time taken: 0.179 seconds
hive (myhive)> select * from score;
OK
score.s_id score.c_id score.s_score score.month
01 01 80 201906
01 02 90 201906
01 03 99 201906
02 01 70 201906
02 02 60 201906
02 03 80 201906
03 01 80 201906
03 02 80 201906
03 03 80 201906
04 01 50 201906
04 02 30 201906
04 03 20 201906
05 01 76 201906
05 02 87 201906
06 01 31 201906
06 03 34 201906
07 02 89 201906
07 03 98 201906
Time taken: 0.048 seconds, Fetched: 18 row(s)
hive (myhive)>
删除分区
删除分区的语句为：
alter table score11 drop partition(month='201806');
hive (myhive)> select * from score11;
OK
score11.s_id score11.c_id score11.s_score score11.month
01 01 80 201806
01 02 90 201806
01 03 99 201806
02 01 70 201806
02 02 60 201806
02 03 80 201806
03 01 80 201806
03 02 80 201806
03 03 80 201806
04 01 50 201806
04 02 30 201806
04 03 20 201806
05 01 76 201806
05 02 87 201806
06 01 31 201806
06 03 34 201806
07 02 89 201806
07 03 98 201806
Time taken: 0.145 seconds, Fetched: 18 row(s)
hive (myhive)> alter table score11 drop partition(month='201806');
Dropped the partition month=201806
OK
Time taken: 0.264 seconds
hive (myhive)> select * from score11;
OK
score11.s_id score11.c_id score11.s_score score11.month
Time taken: 0.047 seconds
hive (myhive)>
删除并不存在的分区时不会提示错误信息。
