将存储为orc的配置单元表的列删除 [英] Drop column of hive table stored as orc

问题描述

我有一个hive表，创建如下：

创建表（由id）聚类的alpha001（id int，名称字符串）存储为2个存储为orc的存储区TBLPROPERTIES（' transactional'='true'）

现在我想放下其中一列，说'名字'。我尝试了以下方法：

1. ALTER TABLE alpha001 REPLACE COLUMNS（id int）
   

引发的异常：FAILED：执行错误，从org.apache.hadoop.hive.ql.exec.DDLTask返回代码1。表default.alpha001不支持替换列。 SerDe可能不兼容。

2. ALTER TABLE alpha001 DROP name

抛出的异常：FAILED：ParseException行1:26不匹配的输入'name'期望在放置分区语句中'DROP'附近的PARTITION

有人可以帮我弄这个吗 ？请

解决方案

不幸的是，你不能！您可以从现有表中删除列的唯一方法是使用 REPLACE COLUMNS 关键字。但是，只能对具有本地SerDe（DynamicSerDe，MetadataTypedColumnsetSerDe，LazySimpleSerDe和ColumnarSerDe）的表执行此操作。

最好的办法是重新创建模式。按照步骤操作。

1. 检查表是否是外部的。
   
   

     alter table alpha001 set tblproperties（'EXTERNAL'='真正'）; 
     




2. 删除表格。由于该表是一个外部表，因此可以在不删除实际表的情况下将其删除。 使用新模式重新创建表。您应该可以使用新模式访问表。

请看快速示例。 $ b

  create table alpha001（id int，name string）由（id）聚类到2个存储为orc的桶中TBLPROPERTIES（'transactional'='true'）; 
 $ b $  - 你的表不是EXTERNAL已经
 alter table alpha001 set tblproperties（'EXTERNAL'='TRUE'）; 
 
插入到alpha001值（1，A）; 
 
 select * from alpha001; 
  

 确定
 1 
  

 下拉表alpha001; 
 
将表alpha001（id int）按（id）聚类到2个存储为orc的桶中TBLPROPERTIES（'transactional'='true'）; 
 
 select * from 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:

1. ALTER TABLE alpha001 REPLACE COLUMNS (id int)

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.

2. ALTER TABLE alpha001 DROP name

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

Can someone help me with this ? please

解决方案

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的配置单元表的列删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！