hive-在值的范围内将一行拆分为多行

wmvff8tz  于 2021-05-31  发布在  Hadoop
关注(0)|答案(2)|浏览(497)

我在下面有一个表,我想按从开始列到结束列的范围拆分行。
i、 e id和value应该在start和end(包括两者)之间为每个值重复

--------------------------------------
id      | value   | start     | end
--------------------------------------
1       | 5       | 1         | 4
2       | 8       | 5         | 9
--------------------------------------

期望输出

--------------------------------------
id      | value   | current
--------------------------------------
1       | 5       | 1
1       | 5       | 2
1       | 5       | 3
1       | 5       | 4

2       | 8       | 5
2       | 8       | 6
2       | 8       | 7
2       | 8       | 8
2       | 8       | 9
--------------------------------------

我可以用java/python编写我自己的udf来获得这个结果,但是我想检查是否可以使用任何现有的hiveudf在hivesql中实现
提前谢谢。

gdx19jrr

gdx19jrr1#

您可以使用posexplode()自定义项来完成。

WITH
data AS (
  SELECT 1 AS id, 5 AS value, 1 AS start, 4 AS `end`
  UNION ALL
  SELECT 2 AS id, 8 AS value, 5 AS start, 9 AS `end`
)
SELECT distinct id, value, (zr.start+rge.diff) as `current`
   FROM data zr LATERAL VIEW posexplode(split(space(zr.`end`-zr.start),' ')) rge as diff, x

下面是它的输出:

+-----+--------+----------+--+
| id  | value  | current  |
+-----+--------+----------+--+
| 1   | 5      | 1        |
| 1   | 5      | 2        |
| 1   | 5      | 3        |
| 1   | 5      | 4        |
| 2   | 8      | 5        |
| 2   | 8      | 6        |
| 2   | 8      | 7        |
| 2   | 8      | 8        |
| 2   | 8      | 9        |
+-----+--------+----------+--+
yqlxgs2m

yqlxgs2m2#

这可以通过一个递归的公共表表达式来实现,而hive不支持这个表达式。
一种方法是创建一个数字表,并使用它在开始和结束之间生成行。

create table numbers
location 'hdfs_location' as 
select row_number() over(order by somecolumn) as num
from some_table --this can be any table with the desired number of rows 
;

--Join it with the existing table 
select t.id,t.value,n.num as current
from tbl t
join numbers n on n.num>=t.start and n.num<=t.end

相关问题