本文介绍了配置单元:强制转换数组< string>排列< int>在查询中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表:
create table a (
`1` array<string>);
create table b (
`1` array<int>);
我想将表a放在表b中(表b为空):
and I want to put the table a in table b (table b is empty) :
insert into table b
select * from a;
这样做时,出现以下错误:
when doing so I get the following error :
FAILED: SemanticException [Error 10044]: Line 1:18 Cannot insert into
target table because column number/types are different 'b': Cannot
convert column 0 from array<string> to array<int>.
而如果这些字段仅是string
和int
类型,则不会出现此错误.
whereas I would not get this error if the fields were only of types string
and int
.
有没有办法对数组进行转换?
Is there a way to do the cast with arrays ?
推荐答案
使用explode()
和collect_list()
重新组装数组.
初始字符串数组示例:
hive> select array('1','2','3') string_array;
OK
string_array
["1","2","3"]
Time taken: 1.109 seconds, Fetched: 1 row(s)
转换数组:
hive> select collect_list(cast(array_element as int)) int_array --cast and collect array
from( select explode(string_array) array_element --explode array
from (select array('1','2','3') string_array --initial array
)s
)s;
结果:
OK
int_array
[1,2,3]
Time taken: 44.668 seconds, Fetched: 1 row(s)
如果要在插入+选择查询中添加更多列,请使用lateral view [outer]
:
And if you want to add more columns in your insert+select query then uselateral view [outer]
:
select col1, col2, collect_list(cast(array_element as int)) int_array
from
(
select col1, col2 , array_element
from table
lateral view outer explode(string_array) a as array_element
)s
group by col1, col2
;
这篇关于配置单元:强制转换数组< string>排列< int>在查询中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!