删除存储为 orc 的 hive 表的列 [英] Drop column of hive table stored as orc

问题描述

我创建的配置单元表如下:

创建表 alpha001(id int, name string) 由 (id) 聚类到 2 个存储为 orc TBLPROPERTIES ('transactional'='true') 的桶中

现在我想删除其中一列，比如名称".我尝试了以下方法:

ALTER TABLE alpha001 REPLACE COLUMNS (id int);

结果如下

异常抛出:失败:执行错误，从 org.apache.hadoop.hive.ql.exec.DDLTask 返回代码 1.表 default.alpha001 不支持替换列.SerDe 可能不兼容.

和关注

ALTER TABLE alpha001 DROP name;

抛出异常:FAILED:ParseException line 1:26 不匹配的输入name"期望在 drop partition 语句中的DROP"附近有 PARTITION

解决方案

很遗憾，您不能！从现有表中删除列的唯一方法是使用 REPLACE COLUMNS 关键字.但这只能用于具有本机 SerDe(DynamicSerDe、MetadataTypedColumnsetSerDe、LazySimpleSerDe 和 ColumnarSerDe)的表.

最好的办法是重新创建架构.按照步骤操作.

1. 检查表是否是外部的.如果不是，请使用以下语句将其设为外部.

   alter table alpha001 set tblproperties('EXTERNAL'='TRUE');

2. 放下桌子.由于该表是外部表，因此可以在不删除实际表的情况下删除它.

3. 使用新架构重新创建表.您应该能够使用新架构访问该表.

遵循快速示例.

create table alpha001(id int, name string) 由 (id) 聚集成 2 个存储为 orc TBLPROPERTIES ('transactional'='true');--假设你的表不是 EXTERNAL更改表 alpha001 设置 tblproperties('EXTERNAL'='TRUE');插入 alpha001 值(1,"A");从 alpha001 中选择 *；

<块引用>

好的1 安

删除表 alpha001;创建表 alpha001(id int) 由 (id) 聚集成 2 个存储为 orc TBLPROPERTIES ('transactional'='true') 的存储桶；从 alpha001 中选择 *；

<块引用>

好的1耗时

希望有帮助！

I have hive table created as below:

create table alpha001(id int, name string) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true')

Now i want to drop one of the columns, say 'name'. I tried the following:

ALTER TABLE alpha001 REPLACE COLUMNS (id int);

which results in below

Exception thrown: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replace columns is not supported for table default.alpha001. SerDe may be incompatible.

and following

ALTER TABLE alpha001 DROP name;

Exception thrown : FAILED: ParseException line 1:26 mismatched input 'name' expecting PARTITION near 'DROP' in drop partition statement

解决方案

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.

   alter table alpha001 set tblproperties('EXTERNAL'='TRUE');
   

2. 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.

Follows a quick sample.

create table alpha001(id int, name string) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true');

--assuming your table is not EXTERNAL already
alter table alpha001 set tblproperties('EXTERNAL'='TRUE');

insert into alpha001 values(1,"A");

select * from alpha001;

OK
1       A

drop table alpha001;

create table alpha001(id int) clustered by (id) into 2 buckets stored as orc TBLPROPERTIES ('transactional'='true');

select * from alpha001;

OK
1
Time tak

Hope that helps!

这篇关于删除存储为 orc 的 hive 表的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！