2013年建设工程发布网站/seo网站优化收藏

背景

在以前上线的分区表中新加一个字段，并且要求添加到指定的位置列。

模拟测试

加 cascade 操作

• 创建测试表

create table if not exists sqltest.table_add_column_test(org_col1 string comment '原始数据1',org_col2 string comment '原始数据2'
)
comment '增加分区表字段的测试表'
partitioned by (dt string comment '分区日期')
;

• 插入测试数据

insert into table sqltest.table_add_column_test partition(dt='20230313') values ('org_col1_0313','org_col2_0313');
insert into table sqltest.table_add_column_test partition(dt='20230314') values ('org_col1_0314','org_col2_0314');

• 查看现有数据

 select * from table_add_column_test;

+---------------------------------+---------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.org_col2  | table_add_column_test.dt  |
+---------------------------------+---------------------------------+---------------------------+--+
| org_col1_0313                   | org_col2_0313                   | 20230313                  |
| org_col1_0314                   | org_col2_0314                   | 20230314                  |
+---------------------------------+---------------------------------+---------------------------+--+

• 官网添加列的语法

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)

注意： Hive 1.1.0 中有 CASCADE|RESTRICT 子句。ALTER TABLE ADD|REPLACE COLUMNS CASCADE命令修改表元数据的列，并将相同的更改级联到所有分区元数据。RESTRICT 是默认值，即不修改元数据。

• 增加一列，指定增加到原始的两列中间
  先添加一列（注意： 必须添加 cascade 关键字，不然不会刷新旧分区数据，关键字 cascade 能修改元数据）

alter table sqltest.table_add_column_test add columns (added_column string comment '新添加的列') cascade;

查看数据

+---------------------------------+---------------------------------+-------------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.org_col2  | table_add_column_test.added_column  | table_add_column_test.dt  |
+---------------------------------+---------------------------------+-------------------------------------+---------------------------+--+
| org_col1_0313                   | org_col2_0313                   | NULL                                | 20230313                  |
| org_col1_0314                   | org_col2_0314                   | NULL                                | 20230314                  |
+---------------------------------+---------------------------------+-------------------------------------+---------------------------+--+

再对列进行排序（注意： 必须添加 cascade 关键字，不然不会刷新旧分区数据，关键字 cascade 能修改元数据）

alter table sqltest.table_add_column_test change column added_column added_column string after org_col1 cascade;

再查看数据（注意： 虽然列名顺序变了，但 HDFS 文件内容并没有变化，所以结果第二列还是有数据，第三列没数据）

+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.added_column  | table_add_column_test.org_col2  | table_add_column_test.dt  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| org_col1_0313                   | org_col2_0313                       | NULL                            | 20230313                  |
| org_col1_0314                   | org_col2_0314                       | NULL                            | 20230314                  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+

• 重刷旧分区数据（将以前第二列放到第三列位置，现第二列为新数据）

insert overwrite table sqltest.table_add_column_test partition(dt='20230313') select org_col1, 'added_col_0313', added_column from sqltest.table_add_column_test where dt = '20230313';
insert overwrite table sqltest.table_add_column_test partition(dt='20230314') select org_col1, 'added_col_0314', added_column from sqltest.table_add_column_test where dt = '20230314';

查看数据（旧分区数据有更新）

+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.added_column  | table_add_column_test.org_col2  | table_add_column_test.dt  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| org_col1_0313                   | added_col_0313                      | org_col2_0313                   | 20230313                  |
| org_col1_0314                   | added_col_0314                      | org_col2_0314                   | 20230314                  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+

不加 cascade 操作（针对已有分区数据）

• 删除表

drop table if exists sqltest.table_add_column_test;

• 创建测试表

create table if not exists sqltest.table_add_column_test(org_col1 string comment '原始数据1',org_col2 string comment '原始数据2'
)
comment '增加分区表字段的测试表'
partitioned by (dt string comment '分区日期')
;

• 插入测试数据

insert into table sqltest.table_add_column_test partition(dt='20230313') values ('org_col1_0313','org_col2_0313');
insert into table sqltest.table_add_column_test partition(dt='20230314') values ('org_col1_0314','org_col2_0314');

• 添加列（不加关键字 cascade）

alter table sqltest.table_add_column_test add columns (added_column string comment '新添加的列');alter table sqltest.table_add_column_test change column added_column added_column string after org_col1;

查看数据

+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.added_column  | table_add_column_test.org_col2  | table_add_column_test.dt  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| org_col1_0313                   | org_col2_0313                       | NULL                            | 20230313                  |
| org_col1_0314                   | org_col2_0314                       | NULL                            | 20230314                  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+

• 重刷旧分区数据

insert overwrite table sqltest.table_add_column_test partition(dt='20230313') select org_col1, 'added_col_0313', added_column from sqltest.table_add_column_test where dt = '20230313';
insert overwrite table sqltest.table_add_column_test partition(dt='20230314') select org_col1, 'added_col_0314', added_column from sqltest.table_add_column_test where dt = '20230314';

• 查看数据（旧分区没有变化）

+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| table_add_column_test.org_col1  | table_add_column_test.added_column  | table_add_column_test.org_col2  | table_add_column_test.dt  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+
| org_col1_0313                   | added_col_0313                      | NULL                            | 20230313                  |
| org_col1_0314                   | added_col_0314                      | NULL                            | 20230314                  |
+---------------------------------+-------------------------------------+---------------------------------+---------------------------+--+