hive DDL语法汇总

hive DDL语法汇总

１、对表重命名

hive> ALTER TABLE table_name RENAME TO new_table_name;

 

2、修改表备注

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

 

3、添加表分区

hive> ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec 

  [LOCATION 'location1'] partition_spec [LOCATION 'location2'] ...;

 

hive> 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';

 

4、对表分区进行重命名

 

hive> ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec

 

5、交换分区（0.12版本以上支持）

两个表结构必需相同，

hive> ALTER TABLE table_name_1 EXCHANGE PARTITION (partition_spec) WITH TABLE table_name_2;

 

6、删除表分区

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]　[IGNORE PROTECTION] [PURGE];           

注意：PURGE 参数是在Hive 1.2.0及以后的版本才支持，加上该参数则直接删除表分区，而不是将数据放入垃圾回收站；如果没加purge参数，则删掉的分区进入回收站

 

7、恢复分区

MSCK REPAIR TABLE table_name;

 

8、归档分区

ALTER TABLE table_name ARCHIVE PARTITION partition_spec;

ALTER TABLE table_name UNARCHIVE PARTITION partition_spec;

 

9、修改列

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.

 

10、增加列

ALTER TABLE table_name [PARTITION partition_spec]

  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

  [CASCADE|RESTRICT]

 

hive> alter table table_name add columns(new_column_name column_type)

 

11、删除列

原表有三列(a int, b int ,c int);

hive> ALTER TABLE test_change REPLACE COLUMNS (a int, b int)

 

注：删除列时，如果是把前面的列删除，hive只是将列名往前移，数据并没有动，这个要小心