Hive : casting array<string> to array<int> in query

Question

I have two tables :

create table a (
`1` array<string>);

create table b (
`1` array<int>);

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

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 ?

leftjoin · Accepted Answer · 2018-05-14 15:26:24Z

3

Re-assemble array using explode() and collect_list().

Initial String array example:

hive> select array('1','2','3') string_array;
OK
string_array
["1","2","3"]
Time taken: 1.109 seconds, Fetched: 1 row(s)

Convert array:

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;

Result:

OK
int_array
[1,2,3]
Time taken: 44.668 seconds, Fetched: 1 row(s)

And if you want to add more columns in your insert+select query then use lateral 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
;

edited May 14, 2018 at 15:26

answered May 14, 2018 at 15:09

leftjoin

38.5k8 gold badges64 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dolphinZhang Over a year ago

`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 ;``` solve my problem

ningyuwhut Over a year ago

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

works. but I have a question that how the array element is kept the same order with the original array?

leftjoin Over a year ago

@ningyuwhut In this example the order is not preserved (not guarantied). To have the same order use posexplode instead of explode to get position and use distribute by col1, col2 sort by position in the inner subquery. , like in this answer: stackoverflow.com/a/61936243/2700344

Alexandr Trubetskov · Accepted Answer · 2021-06-12 20:13:04Z

3

Brickhouse jar will do this a lot faster than casting them and collecting it back as a list . Add this jar to a hdfs location.
Use the link below to download the brick house jar

add jar hdfs://hadoop-/pathtojar/brickhouse-0.7.1.jar;   
create temporary function cast_array as 'brickhouse.udf.collect.CastArrayUDF';   
select cast_array(columns, 'int') AS columname from table;  
select cast_array(columns, 'string') AS columname from table

edited Jun 12, 2021 at 20:13

Alexandr Trubetskov

534 bronze badges

answered Jun 11, 2020 at 20:39

Harsha Vardhan

1391 silver badge12 bronze badges

Comments

achompas · Accepted Answer · 2018-10-25 21:48:04Z

Is there a way to do the cast with arrays ?

Not easily. You can manually cast the arrays if you know their size, but if not you might need to use structs. See my answer to this similar question.

Also: I cannot downvote the other answer, but it fails for nested selects with more than one array.

Instead of casting array elements and reconstructing the original arrays, it casts and then combines all elements into a single array. Example:

hive> select id, my_array from array_table limit 3;
OK
10023307    ["0.20296966","0.17753501","-0.03543373"]
100308007   ["0.16155224","0.1945944","0.09167781"]
100384207   ["0.025892768","0.023214806","-0.003712816"]

hive> select
    >     collect_list(cast(array_element as double)) int_array
    > from (
    >     select
    >         explode(my_array) array_element
    >     from (
    >         select
    >             my_array
    >         from array_table limit 3
    >     ) X
    > ) s;
OK
[0.20296966,0.17753501,-0.03543373,0.16155224,0.1945944,0.09167781,0.025892768,0.023214806,-0.003712816]

Collectives™ on Stack Overflow

Hive : casting array<string> to array<int> in query

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related