Lateral View Explode in Presto
New to Presto, Any Pointer How Can I Use Lateral View Explode in Presto for Below Table. I Need to Filter on Names in My Presto Query Create External Table...
New to presto, any pointer how can I use LATERAL VIEW EXPLODE in presto for below table.
I need to filter on names in my presto query
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'
;
sample names value :
{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}}
2 Answers
From the documentation:
Trino [formerly PrestoSQL] supports UNNEST for expanding arrays and maps. Use
UNNESTinstead ofLATERAL VIEW explode().
Hive query:
SELECT student, score
FROM tests
LATERAL VIEW explode(scores) t AS score;
Presto query:
SELECT student, score
FROM tests
CROSS JOIN UNNEST(scores) AS t (score);
I am able to run below query to get to the mapped data
select
id
,names['1033']['short'] as srt_nm
from id;
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
