蜂巢如何选择除一列以外的所有列?

本文介绍了蜂巢如何选择除一列以外的所有列?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

假设我的桌子看起来像这样:

Suppose my table looks something like:

Col1 Col2 Col3.....Col20 Col21

现在，我想选择除Col21之外的所有内容.在插入其他表之前，我想将其更改为unix_timestamp().因此，简单的方法是执行以下操作:

Now I want to select all but Col21. I want to change it to unix_timestamp() before I insert into some other table. So the trivial approach is to do something like:

INSERT INTO newtable partition(Col21) SELECT Col1, Col2, Col3.....Col20, unix_timestamp() AS Col21 FROM oldTable

有什么办法可以在蜂巢中实现这一目标吗?非常感谢你的帮助！

Is there a way I can achieve this in hive? Thanks a lot for your help!

推荐答案

尝试设置以下属性

set hive.support.quoted.identifiers=none;

然后选择除 col_21:

Then select all columns except col_21:

select `(col_21)?+.+` from <table_name>;

有关更多信息，请参考此链接.

For more info refer to this link.

然后插入语句将为

insert into <tablename> partition (col21) select `(col_21)?+.+` from ( --select all columns from subquery except col21 select *, unix_timestamp() AS alias_col21 from table_name --select *, create new col based on col21 )a;

使用这种方法，您将把 alias_col21 作为select语句的最后一列，以便您可以基于该列进行分区.

By using this approach you are going to have alias_col21 as last column in your select statement so that you can partition based on that column.

如果要加入:

我们无法引用每个表中的单个列((t1.id)?+.+ .. etc)，因此请在选择语句中删除不必要的列.

We cannot refer individual columns((t1.id)?+.+..etc) from each table, so drop the unnecessary columns in select statement.

hive>insert into <tablename> partition (col21) select * from ( select t1.* from (--drop col21 and create new alias_col21 by using col21 select `(col21)?+.+`, unix_timestamp() AS alias_col21 from table1 ) t1 join table2 t2 on t1.<col-name>=t2.<col-name>)a;