Presto equivalent to StringTokenizerDelim (Vertica) / Explode(Python)

user_12057268 0
Viewed 80

I am migrating a query from Vertica to Presto(0.246). The query contains a Vertica function StringTokenizerDelim, which basically explodes and array (market column) into rows.

This is the mockup of the table:

+----+---------+-------------+
| id | product |   market    |
+----+---------+-------------+
|  1 | Cabinet | AU,GB,FR    |
|  2 | Chair   | US,GB       |
|  3 | Desk    | BE,GB,FR,US |
+----+---------+-------------+

The output should be:

+----+---------+--------+
| id | product | market |
+----+---------+--------+
|  1 | Cabinet | AU     |
|  1 | Cabinet | GB     |
|  1 | Cabinet | FR     |
|  2 | Chair   | US     |
|  2 | Chair   | GB     |
|  3 | Desk    | BE     |
|  3 | Desk    | GB     |
|  3 | Desk    | FR     |
|  3 | Desk    | US     |
+----+---------+--------+

What is the equivalent to that function in Presto (if there is any? couldn't find it on the documentation)?

Cheers,

presto sql
1 Answers

split with UNNEST should do the trick:

-- sample data
WITH dataset (id, product,   market ) AS (
    values (1, 'Cabinet', 'AU,GB,FR'),
    (2, 'Chair', 'US,GB'),
    (3, 'Desk', 'BE,GB,FR,US')
) 

--query
SELECT id, product, m as market
FROM dataset
CROSS JOIN UNNEST (split(market, ',')) as t(m)

Output:

id product market
1 Cabinet AU
1 Cabinet GB
1 Cabinet FR
2 Chair US
2 Chair GB
3 Desk BE
3 Desk GB
3 Desk FR
3 Desk US
user_25012790
Related