Hive - Разбить столбец массива и использовать левое соединение или подзапрос с оператором выбора. Ошибка

Question

Даны две таблицы:

filtered_locations содержащий небольшой набор данных (всего несколько строк)

|-------------|
| loc<String> | 
|-------------|
|     ...     |
|-------------|   

table_clients очень большая таблица (миллионы строк)

 |--------------------------------------------|
 | id  | name|  age |locations <array<String> | 
 |-----|--------------------------------------|
 |     |     |      | [a,b,c..]               |
 |--------------------------------------------|

Я хочу запросить таблицу table_clients для значения на filtered_locations, главная проблема заключается в том, что поле для запроса на table_clients является array тип.

Итак, я взорвал столбец, а затем попытался встроить подзапрос, чтобы включить только местоположения, перечисленные в filtered_locations,

Первая проблема, с которой я столкнулся, заключается в том, что Hive (по крайней мере, версия, на которой я работаю), кажется, не принимает подзапрос внутри in или же exists Постулаты.

Вот ошибка, которую я получаю:

Ошибка при компиляции оператора: СБОЙ: SemanticException Недопустимая ссылка на столбец 'location' в определении SubQuery sq_1 [ tc.location in (выберите fl.loc из Filter_locations fl) ], используемый в качестве sq_1

В качестве альтернативы я попытался использовать LEFT JOIN но также не работает из-за explode Вторая ошибка вызова

Ошибка при компиляции оператора: СБОЙ: SemanticException [Ошибка 10085]: СОЕДИНЕНИЕ с ПОРОГОВЫМ ВИДОМ не поддерживается 'location'

with filtered_locations as (
  SELECT
    'loc1' as loc
    union all
    'loc2' as loc
)

select 
  id, name, location
  max(age) as max_age 
from
  table_clients tc
  LATERAL VIEW EXPLODE(locations) l as location
-- Ideally this should work!
-- where
--  tc.location in (
--     select fl.loc from filtered_locations fl
--  )
left join filtered_locations fl
on fl.loc = tc.location

group by id, name, location

Каково тогда лучшее решение для моей проблемы? Обратите внимание, что table_clients имеет миллионы записей!

Спасибо

Answer 1

Теоретически это должно было сработать

select  *

from    table_clients c
        lateral view explode(location) e as loc

where   e.loc in (select l.loc from filtered_locations l)
;

СБОЙ: SemanticException [Ошибка 10009]: Строка 6:8 Недопустимый псевдоним таблицы 'e'

... но так как это не так, нужно немного обойти

select  *

from   (select  *

        from    table_clients c
                lateral view explode(location) e as loc
        ) c        

where   c.loc in (select l.loc from filtered_locations l)
;