hive之路8-修改表和分区

本文中主要介绍了对表、分区和字段的各种操作

修改表

修改表主要是对表的结构和属性进行操作，包含：

• 重命名

alter table oldname rename to new_table;

• 修改表属性

alter table table_name set tblproperties (property_name=property_value);

• 修改表注释

alter table table_name set tblproperties('comment'=new_comment);

• 修改存储属性

alter table table_name clustered by (col_name, col_name,...) [sorted by (col_name,...)] into number buckets;

• 修改表的目录

修改分区

• 添加分区

alter table tablename add [if not exists] partition partition_spec [location 'location1'] partion_spec [location 'location2']...

• 重命名分区

alter table tablename partition partition_spec rename to partition partition_spec;

• 交换分区

alter table table_name1 exchange partition (partition-spec1, partition-spec2) with table table_name2;

• 修复分区

msck repair table tablename;

• 删除分区

alter table tablename drop [if exists] parition partition-spec [ignore protection] [purge];

• 解档和归档

alter table tablename archive partition partition-spec;  -- 归档
alter table tablename unarchive partition partition-spec;  -- 解档

• 修改文件格式、路径、保护

alter table tablename [partition partition-spec] set fileformat file_format;

alter table tablename [partition partition-spec] set location "new-location";

alter table tablename [partition partition-spec] enable|disable no-drop [cascade];  -- 保护：不能删除

alter table tablename [partition partition-spec] enable|disable offline;  -- 下线状态

修改字段

• 修改属性、位置、注释等

alter table tablename [partition partition-spec] change [column] col_old_name col_new_name column_type [comment col_comment] [first|after column_name] [cascade|restrict];

• 增加和替换列

alter table tablename [partition partition-spec] add|replace columns(col_name data_type [comment col_comment],...)

-- demo：增加sex字段，指定注释
alter table student add columns (sex string comment 'sex of student')