CODEWITHSUNDEEP
sqlhivedb2hiveql
Mons2us
Mons2us

Reputation: 332

How to generate a sequence of numbers in Hiveql in recursive way?

I've recently moved from DB2 to hiveql. There's a query that I used to generate a sequence of numbers recursively, like 1 to 1000. Under is the used code;

WITH TB (N) AS
(VALUES (100)
UNION ALL
SELECT N + 100
FROM TB
WHERE N + 1 <= 5000)
SELECT * FROM TB;

I could make whatever sequence I wanted with this code,

but now in Hiveql things like VALUES() or WITH TB (N) doesn't seem to work as done in DB2. Is there a way to generate such sequences in hiveql?

Upvotes: 2

Views: 1759

Answers (1)

leftjoin
leftjoin

Reputation: 38290

You can generate sequence in Hive using this query. space() returns string of spaces of given length, split creates an array of spaces, posexplode explodes an array (generates rows), you can use index for sequence generation:

set hivevar:start=1; --Define sequence start and end
set hivevar:end=20;

with seq as(
select posexplode(split(space(${end}-${start}),' ')) as (i,x)
)
select ${start}+i from seq;

Upvotes: 5

Related Questions