drop table alter_table_test;
CREATE TABLE db_test.alter_table_test (
id STRING COMMENT '主键',
NAME STRING COMMENT '姓名',
hight int COMMENT '身高',
address STRING )
PARTITIONED BY ( ds STRING COMMENT '分区' )
COMMENT '测试demo' ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS PARQUET;
insert into alter_table_test partition(ds='20200716') values('123','zhangsan',175,'beijing');
select * from alter_table_test;
insert into alter_table_test partition(ds='20200717') values('124','lisi',179,'shanghai');
select * from alter_table_test;
--在最后添加一列string
alter table alter_table_test add columns(age string);
select * from alter_table_test;
--在最后添加一列int
alter table alter_table_test add columns(grade int);
select * from alter_table_test;
--插入数据
insert into alter_table_test partition(ds='20200718') values('125','wangwu',189,'nanjing','75',30);
select * from alter_table_test;
--删除最后一列
alter table alter_table_test drop column grade;
select * from alter_table_test;
--删除非最后一列
alter table alter_table_test drop column name;
select * from alter_table_test; --执行报错
set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;
select * from alter_table_test; --执行成功
--更改非末尾表字段类型
alter table alter_table_test change column id id int;
select * from alter_table_test; --执行报错
set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;
select * from alter_table_test; --执行失败
--修改回来
alter table alter_table_test change column id id string;
select * from alter_table_test; --执行成功 --
--更改末尾表字段类型,string->int
alter table alter_table_test change column age age int;
select * from alter_table_test; --执行报错
set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;
select * from alter_table_test; --执行失败
--更改末尾表字段类型,string->int
alter table alter_table_test change column age age string;
select * from alter_table_test; --执行报错
sPARQUET_FALLBACK_SCHEMA_RESOLUTION=name;et
select * from alter_table_test; --执行失败
--更改hight(int)--string
alter table alter_table_test change column hight hight string;
select * from alter_table_test; --执行报错
set PARQUET_FALLBACK_SCHEMA_RESOLUTION=name;
select * from alter_table_test; --执行失败
impala更改表结构测试案例
最新推荐文章于 2023-08-07 09:46:15 发布