Hiveのposexplode関数をPrestoで書き換える

参考記事：

Goal

Hiveのposexplode関数をPrestoで書き換える

Version 1

よりシンプルなSQL文を考えました

SELECT KEY, val FROM
(
  SELECT map(split(n1, ','), split(n2, ',')) m
  FROM (
    VALUES
    ('1,4,7', '2,5,8')
  ) AS x (n1, n2)
) d
CROSS JOIN UNNEST(m) AS m2( KEY, val);

presto:sso_test> SELECT KEY, val FROM
              -> (
              -> SELECT map(split(n1, ','), split(n2, ',')) m
              -> FROM (
              -> VALUES
              -> ('1,4,7', '2,5,8')
              -> ) AS x (n1, n2)
              -> ) d
              -> CROSS JOIN UNNEST(m) AS m2( KEY, val);
 KEY | val
-----+-----
 1   | 2
 4   | 5
 7   | 8
(3 rows)

比較：

presto:sso_test> SELECT
              ->  n as num, a as ans
              -> FROM (
              ->   -- record 生成用
              ->   SELECT n1, n2
              ->   FROM (
              ->     VALUES
              ->       ('1,4,7', '2,5,8')
              ->   ) AS x (n1, n2)
              -> ) t1
              -> CROSS JOIN UNNEST(regexp_extract_all(n1, '[^,]+'), regexp_extract_all(n2, '[^,]+')) AS t (n, a)
              ->
              -> ;
 num | ans
-----+-----
 1   | 2
 4   | 5
 7   | 8
(3 rows)

Version 2

SELECT KEY, val FROM
(
  SELECT split(n1, ',') a1, split(n2, ',') a2
  FROM (
    VALUES
      ('1,4,7', '2,5,8')
  ) AS x (n1, n2)
) d
CROSS JOIN UNNEST(a1, a2) AS m2( KEY, val);

presto:dspf_public> SELECT KEY, val FROM
                 -> (
                 -> SELECT split(n1, ',') a1, split(n2, ',') a2
                 -> FROM (
                 -> VALUES
                 -> ('1,4,7', '2,5,8')
                 -> ) AS x (n1, n2)
                 -> ) d
                 -> CROSS JOIN UNNEST(a1, a2) AS m2( KEY, val);
 KEY | val
-----+-----
 1   | 2
 4   | 5
 7   | 8
(3 rows)