欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

hive DDL语法汇总

程序员文章站 2022-06-23 20:49:00
...

hive DDL语法汇总

1、对表重命名

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只是将列名往前移,数据并没有动,这个要小心