【五】hive DDL之Alter Table/Partition/Column

Alter Table

允许修改已有表的表结构，例如添加、删除列、改变SerDe、重命名表名。

重命名表明

ALTER TABLE table_name RENAME TO new_table_name;

修改表的属性

可以用这个命令增加表的元数据。

last_modified_user, last_modified_time properties这三个数据是Hive自动管理创建的。

 DESCRIBE EXTENDED TABLE 这个命令可以查看表的属性（元数据信息）

ALTER TABLE table_name SET TBLPROPERTIES table_properties;
 
table_properties:
  : (property_name = property_value, property_name = property_value, ... )

修改表的备注

ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);

添加SerDe属性

ALTER TABLE table_name [PARTITION partition_spec] SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties];
 
ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties;
 
serde_properties:
  : (property_name = property_value, property_name = property_value, ... )

例如

ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

修改表的存储属性

这个命名会改变表的物理存储属性。

它只是修改了元数据，不会重新组织或重新格式化现有数据，用户应确自己保实际数据布局符合元数据中的定义。

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)]
  INTO num_buckets BUCKETS;

修改表数据倾斜

STORED AS DIRECTORIES操作决定倾斜表是否使用list bucketing特征，为偏斜值创建子目录。

ALTER TABLE table_name SKEWED BY (col_name1, col_name2, ...)
  ON ([(col_name1_value, col_name2_value, ...) [, (col_name1_value, col_name2_value), ...]
  [STORED AS DIRECTORIES];

关闭数据倾斜，不使用list bucketing特征

ALTER TABLE table_name NOT SKEWED;

不使用list bucketing特征，但仍是数据倾斜表

ALTER TABLE table_name NOT STORED AS DIRECTORIES;

设置数据倾斜的location

ALTER TABLE table_name SET SKEWED LOCATION (col_name1="location1" [, col_name2="location2", ...] );

修改表约束条件

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column, ...) DISABLE NOVALIDATE;
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column, ...) REFERENCES table_name(column, ...) DISABLE NOVALIDATE RELY;
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Alter Partition

增加partition

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

例子

ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808'
                          PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';

动态分区

在insert语句中动态分区（工作中有用到，比如后来的统计报表都按某个表示日期的字段动态分区，下游系统或者其他同事使用我的结果表的时候会方便很多）。这个点会另外写篇文章介绍。

分区重命名

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

交换分区

表之间可以交换分区。没有把A表的1分区移动到B表的2分区，前提是A、B表有相同的schema 数据结构，切B表没有1分区。

即是B表没有A移动过来的分区的名字。

-- Move partition from table_name_1 to table_name_2
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_1;
-- multiple partitions
ALTER TABLE table_name_2 EXCHANGE PARTITION (partition_spec, partition_spec2, ...) WITH TABLE table_name_1;

Recover Partitions (MSCK REPAIR TABLE)

hive在元数据中保存着分区信息，如果直接用 hadoop fs -put 命名在HDFS上添加分区，元数据不会意识到。

需要用户在hive上为每个新分区执行ALTER TABLE table_name ADD PARTITION，元数据才会意识到。 

用户可以用元数据检查命令修复表，它会添加新分区的元数据信息到hive的元数据中。换句话说，这个命令会把HDFS上有的分区，但是元数据中没有的分区，补充到元数据信息中。

MSCK REPAIR TABLE table_name;

删除分区

这会删除该分区的数据和元数据。

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

例子

ALTER TABLE page_view DROP PARTITION (dt='2008-08-08', country='us');

Alter Column

修改列名、类型、位置、备注

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

例子

CREATE TABLE test_change (a int, b int, c int);
 
// First change column a's name to a1.
ALTER TABLE test_change CHANGE a a1 INT;
 
// Next change column a1's name to a2, its data type to string, and put it after column b.
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b;
// The new table's structure is:  b int, a2 string, c int.
  
// Then change column c's name to c1, and put it as the first column.
ALTER TABLE test_change CHANGE c c1 INT FIRST;
// The new table's structure is:  c1 int, b int, a2 string.
  
// Add a comment to column a1
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';

添加/替换 列

ALTER TABLE table_name 
  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)