sql - 配置单元-展开数组列并使用带有选择语句错误的LEFT联接或子查询

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语句之类的子查询。

那就是我得到的错误:

Error while compiling statement: FAILED: SemanticException Invalid column reference 'location' in definition of SubQuery sq_1 [ tc.location in ( select fl.loc from filtered_locations fl ) ] used as sq_1

另外，我尝试使用 LEFT JOIN，但由于 explode调用而无法使用
第二次错误

Error while compiling statement: FAILED: SemanticException [Error 10085]: JOIN with a LATERAL VIEW is not supported '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)
;

FAILED: SemanticException [Error 10009]: Line 6:8 Invalid table alias '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)
;