hive - 如何从 hive 中的 Struct Array 中获取值

Question

表结构

CREATE EXTERNAL TABLE IF NOT EXISTS LocationTable
(
USER_ID BIGINT,
NAME STRING,
STATE STRUCT < DISTRICT ARRAY < ID:BIGINT, NAME:STRING > >
)

我的查询是：

SELECT user_id, name,
district.pos as district_pos,
district.name as district_name,
FROM
locationtable,
locationtable.state.district district
WHERE user_id = '58'

它抛出以下错误。

Error while compiling statement: FAILED: ParseException line 6:20 cannot recognize input near '.' 'district' 'district' in table source

哪里做错了？

标签： hive

Answer 1

利用explode

select 
     user_id,
     name,
     state.district.ID as district_pos,
     state.district.NAME as district_name
from
   locationtable 
   lateral view explode(STATE) exploded_table as state;