LATERAL VIEW EXPLODE in presto

Question

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}}

score 58 · Answer 1 · edited Dec 15 '21 at 18:58

58

From the documentation: https://trino.io/docs/current/appendix/from-hive.html

Trino [formerly PrestoSQL] supports UNNEST for expanding arrays and maps. Use UNNEST instead of LATERAL 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);

edited Dec 15 '21 at 18:58

jirassimok

3,850
2
14
23

answered Jul 13 '18 at 01:44

Dain Sundstrom

2,699
15
14

1

Works great! In `CROSS JOIN UNNEST(scores) AS t (score);`, what does the `t` mean? – Matt Fenwick Apr 04 '19 at 14:23
1

@MattFenwick `t` is an alias for the table implicitly created by `CROSS JOIN UNNEST(scores)`. So you can refer to `score` as `t.score` in the `SELECT` statement. – MichaelChirico Jul 30 '19 at 05:13

score -8 · Accepted Answer · answered Jul 17 '18 at 19:11

-8

I am able to run below query to get to the mapped data

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

answered Jul 17 '18 at 19:11

rkj

671
3
14
25

LATERAL VIEW EXPLODE in presto

2 Answers2