Hive: array cast <string> for array <int> in the request

Question

I have two tables:

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

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

and I want to put table a in table b (table b is empty):

insert into table b
select * from a;

while doing this 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 stringand int.

Is there a way to do a listing with arrays?

Answer 1

Reassemble the array using explode()and collect_list().

The original example of an array of strings:

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

Array conversion:

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 to your insert + select query, 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
;

Answer 2

?

. , , , . . .

---

: , .

, , . :

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]