Generating dates in Hive SQL

Question

I'm looking to be able to create a table that contains all of the dates (inclusive) between the min and max date from another table. See below the simple query to get these dates

-- Get the min and max dates from the table
select min(date(sale_date)) as min_date,
       max(date(sale_date)) as max_date
from TABLE;

I've spent the last hour googling this problem and have found attempts at doing this on MySQL and Oracle SQL but not on Hive SQL which I've been unable to convert to Hive SQL. If anyone has any idea on how to do this, please let me know. Thanking you in advance.

LorcanT LorcanT · Accepted Answer · 2017-10-10T12:13:19

Ok this isn't my answer. A colleague was able to answer it. Still I think its important that I show my colleague's solution for your future benefit. It assumes that you've created a table that contains the min date and max date.

CREATE TABLE TABLE_2
STORED AS AVRO
LOCATION 'xxxxxx'
AS
SELECT date_add (t.min_date,pe.i)   AS date_key
FROM    TABLE_1 t
LATERAL VIEW
posexplode(split(space(datediff(t.max_date,t.min_date)),' ')) pe AS i,x;

Generating dates in Hive SQL

1 Answers