Converting array of string into array of integer

Question

Right now I'm having a performance issue with this query :

select userid from table_x inner join table_y on array_contains(split(table_y.userids,','),cast(table_x.userid as string))

The userids on y is represented as a string of numbers "123, 134, 156" which actually means three userids, namely 123,134 and 156. Table_x has a userid columns which details the personal information of each user. I want to select the userid which is contained in the userids column in table_y.

Am I right in assuming that the reason for the perforamance issue is because I have to convert the userids in table_y to array of string using split(table_y.userids,',') and use array_contains for string. If so, is there anyone who knows how to convert the string of userids into array of integer?

Thank you!

Alex Libov · Accepted Answer · 2016-11-08 06:30:34Z

1

It seems that you are doing a Cartesian product join. Hive cannot join on array_contains - it is applied after hive generates all possible combinations.

To truly join, you need to use explode(split(table_y.userids,',')) and then have a regular equality join:

select x.uid from (select cast(table_x.userid as string) as uid from table_x) x 
inner join 
(select explode(split(table_y.userids,',')) as uid from table_y) y on 
x.uid=y.uid;

answered Nov 8, 2016 at 6:30

Alex Libov

1,4912 gold badges12 silver badges20 bronze badges

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

1 Comment

chrishendra93 Over a year ago

Thanks for the help and explanation! This actually helps me to achieve a much faster performance

Collectives™ on Stack Overflow

Converting array of string into array of integer

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related