hive 添加字段_Hive添加或删除字段

官方文档关于Add/ReplaceColumns操作的说明

// 创建测试表

CREATE TABLE IF NOT EXISTS test (id BIGINT, name STRING);

// 插入一条数据

INSERT INTO TABLE test VALUES(2341344423,"lisi");

// 添加字段

ALTER TABLE test ADD COLUMNS(age Int);

// 插入一条数据

INSERT INTO TABLE test VALUES(2341344422,"zhangsan",18);

原有数据在新增一个或多个字段后，会将新增字段的值设置为null

在删除一个或多个字段后，原始数据的原始字段的值不会随之丢失

// 删除字段(使用新schema替换原有的)

ALTER TABLE test REPLACE COLUMNS(id BIGINT, name STRING);

修改字段(名称/类型/位置/注释)

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