Hive orc表 删除字段

说明

Unfortunately, you can't!
The only way you can delete column from existing table is by using REPLACE COLUMNS keyword.
But this can be done only for tables with a native SerDe (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe).

Your best bet is recreating the schema. Follows the steps.

1. Check if the table is external. If it isn't, use the following statement to make it external.

2. alter table alpha001 set tblproperties('EXTERNAL'='TRUE');
   Drop the table. Since the table is an external table, you can drop it without dropping the actual table.

3. Recreate the table with the new schema. You should be able to access the table with new schema.

例子

1. 原始表结构

create external table if not exists ods_mysql.ods_db_o2m__tb_time_period_dict ( 
    `id` bigint comment "自增ID",
    `service_type` string comment "类型",
    `begin_time` string comment "时间段开始时间",
    `end_time` string comment "时间段结束时间",
    `status` string comment "状态： ENABLE 可用，DISABLE 不可用",
    `version` bigint comment "版本号",
    `create_time` bigint comment "创建时间",
    `update_time` bigint comment "更新时间",
    `code` string COMMENT "code码，兼容原来TimeBucket中的枚举值"
)
comment "时间段字典"
partitioned by(p_day string)
stored as orc ;

2. 删除字段 code

hive> drop table ods_mysql.ods_db_o2m__tb_time_period_dict;
hive> 
create external table if not exists ods_mysql.ods_db_o2m__tb_time_period_dict ( 
    `id` bigint comment "自增ID",
    `service_type` string comment "类型",
    `begin_time` string comment "时间段开始时间",
    `end_time` string comment "时间段结束时间",
    `status` string comment "状态： ENABLE 可用，DISABLE 不可用",
    `version` bigint comment "版本号",
    `create_time` bigint comment "创建时间",
    `update_time` bigint comment "更新时间"
)
comment "时间段字典"
partitioned by(p_day string)
stored as orc ;

3. 重新映射

hive> alter table ods_mysql.ods_db_o2m__tb_time_period_dict add PARTITION (p_day='2018-07-27') location '/user/hive/warehouse/ods_mysql.db/ods_db_o2m__tb_time_period_dict/p_day=2018-07-27';

https://stackoverflow.com/questions/32068903/drop-column-of-hive-table-stored-as-orc