sql - Very slow select query on array column on Impala table

Question

Query on array column Impala table, works very slow. These queries are memory and CPU hungry. Is any optimized way/technique to speedup these query?

We had sample below table

Owner Pets
Sam cat
Sam dog
Sam parrot
Tom elephant
Tom horse
Tom pig
Tom cat

And used to query the data as :

Select owner, count(1)
from people
group by owner;

We moved to new Impala table with array column.

Owner Pets
Sam cat,dog,parrot
Tom elephant,horse,pig,cat

And used to query the data as :

Select owner, count(1) 
from people, people.pets
group by owner

I am querying approximate 10 GBs of data at a time.

Select owner, count(1) from people group by owner =>Working good

Select owner, count(1) from people, people.pets group by owner => performance is very bad.

Any optimized way/technique to speedup array column query, it helps lot, thanks.

question from:https://stackoverflow.com/questions/66051037/very-slow-select-query-on-array-column-on-impala-table

Answer 1

Waitting for answers