侧面视图，预分解

Question

对于presto，任何指针，我如何在presto中使用下表的LATERAL VIEW EXPLODE。

我需要在我的预查询中过滤姓名

CREATE EXTERNAL TABLE `id`(
`id` string,
`names` map<string,map<string,string>>,
`tags` map<string,map<string,string>>)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
's3://test'

样本names值：

{3081={short=Abbazia 81427 - Milan}, 2057={short=Abbazia 81427 - Milan}, 1033={short=Abbazia 81427 - Milan}, 4105={short=Abbazia 81427 - Milan}, 5129={short=Abbazia 81427 - Milan}}

Answer 1

从文档中：https://trino.io/docs/current/appendix/from-hive.html

Trino[以前的PrestoSQL]支持UNNEST扩展阵列和映射。使用UNNEST而不是LATERAL VIEW explode()。

配置单元查询：

SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score;

预查询：

SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);

Answer 2

我可以运行下面的查询来获取映射的数据

select
id
,names['1033']['short'] as srt_nm
from id;