修改表
具体用法请查看官网:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
继续沿用DDL之创建表中的t_worker表进行操作。
Rename Table
ALTER TABLE table_name RENAME TO new_table_name;
Alter Table Properties
-
ALTER TABLE table_name SET TBLPROPERTIES table_properties;
table_properties:
- (property_name = property_value, property_name = property_value, … )
未修改表为外部表前:此时表为内部表
将表属性改为外部表:
若需要将外部表改为内表:alter table table_name set tblproperties (‘EXTERNAL’ = ‘FALSE’);
分区相关
创建分区
create table if not exists t_emp( name string, age int) partitioned by (country string, city string) row format delimited fields terminated by '\t' stored as textfile;
Add Partitions
-
ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec
[LOCATION ‘location1’] partition_spec [LOCATION ‘location2’] …;
partition_spec:
- (partition_column = partition_col_value, partition_column = partition_col_value, …)
注意:当我们手动在hdfs上创建分区时,hive是不会显示出分区的
Drop Partitions
ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, …]
example:alter table t_emp drop partition(country=’France’);
保护分区表数据不被删除
alter table table_name partition(partition_spec, …) enable no_drop;
将受保护的分区变为可删除
alter table table_name partition(partition_spec, …) disable no_drop;
使某分区数据不被查询(这会使得全表扫描,和聚合函数都不可执行)
alter table table_name partition(partition_spec, …) enable offline;
解除不能被查询的分区
alter table table_name partition(partition_spec, …) disable offline;
Add/Replace Columns
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)
注:ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段。