如何在 Hive 中分解数组和创建视图

Question

我有以下数据，其中id是整数，vectors是数组：

id, vectors
1, [1,2,3]
2, [2,3,4]
3, [3,4,5]

我想用索引位置分解vectors列，使其看起来像这样：

+---+-----+------+
|id |index|vector|
+---+-----+------+
|1  |0    |1     |
|1  |1    |2     |
|1  |2    |3     |
|2  |0    |2     |
|2  |1    |3     |
|2  |2    |4     |
|3  |0    |3     |
|3  |1    |4     |
|3  |2    |5     |
+---+-----+------+

我想我可以使用Spark Scala来做到这一点，selectExpr

df.selectExpr("*", "posexplode(vectors) as (index, vector)")

但是，这是一项相对简单的任务，我想避免编写 ETL 脚本，并且正在考虑是否可以使用该表达式并创建一个视图以便通过 Presto 轻松访问。

Answer 1

这

很容易在Presto中使用标准SQL语法和UNNEST：

WITH data(id, vector) AS (
    VALUES
    (1, array[1,2,3]),
    (2, array[2,3,4]),
    (3, array[3,4,5])
)
SELECT id, index - 1 AS index, value
FROM data, UNNEST(vector) WITH ORDINALITY AS t(value, index)

请注意，WITH ORDINALITY生成的索引是从 1 开始的，所以我从中减去 1 以产生您在问题中包含的输出。

Answer 2

您可以使用

Hive Lateral view来explode数组数据。尝试以下查询 -

select
    id, (row_number() over (partition by id order by col)) -1 as `index`, col as vector
from (
    select 1 as id, array(1,2,3) as vectors from (select '1') t1 union all
    select 2 as id, array(2,3,4) as vectors from (select '1') t2 union all
    select 3 as id, array(3,4,5) as vectors from (select '1') t3
  ) t
LATERAL VIEW explode(vectors) v;