Hive QL to populate a sequence of numbers between limits

Question

Not sure how to put this in a straight forward manner but I'm trying to make something work in Hive SQL. I need to create a sequence of numbers from lower limit to upper limit.

Ex: select min(year) from table

Let's assume it results in 2010

select max(year) from table

Let's assume it results in 2015

I need to publish each year from 2010 to 2015 in a select query.

And I'm trying to put the min calculation & max calculation inside the same SQL which will/should create sequential years in the output.

Any ideas?

mangusta · Answer 1 · 2019-08-19T05:37:15.237

Well I have an idea but in order to use it, you will have to define the lowest possible and the largest possible values for the years that might be present in your table.

Let's say the smallest possible year is 1900 and the largest possible year is 2200.
Since the largest possible difference in this case is 2200-1900=300, you will have to use the following string: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 ... ... 298 299 300.

In the query, you split this string using space as a delimiter thus getting an array, and then you explode that array.
Have a look:

SELECT
  minval + delta

FROM
  (
    SELECT
       min(year) minval,
       max(year) maxval,
       split('0 1 2 3 4 5 6 7 8 9 10 11 12 13 ... ... ... 298 299 300', ' ') delta_list
    FROM
    table
  ) t
  LATERAL VIEW explode(delta_list) dlist AS delta

WHERE (maxval-minval) >= delta 
;

So you end up with 301 rows but you only need the rows with delta values not exceeding the difference between max year and min year, which is reflected in the where clause

score 2 · Answer 2 · answered Aug 19 '19 at 05:42

set hivevar:end_year=2019;
set hivevar:start_year=2010;

select ${hivevar:start_year}+i as year
from
(
select posexplode(split(space((${hivevar:end_year}-${hivevar:start_year})),' ')) as (i,x)
)s;

Result:

Have a look also at this answer about generating missing dates.

Hive QL to populate a sequence of numbers between limits

2 Answers2