Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

How do you generate dates using HiveQL? Is there a DUAL table in HIVE?

avatar
Contributor

In SQL , for example, we can use the following:

SELECT (to_date('01-12-2016','DD-MM-YYYY') - ROWNUM) AS DT FROM DUAL CONNECT BY ROWNUM <= 366

I tried uing the following in HiveQL which didnt work:

SELECT (to_date('01-12-2016','DD-MM-YYYY') - ROW_NUMBER() OVER () AS row_num) AS DT FROM DUAL CONNECT BY row_num <= 366;

Is there a way around this?

1 ACCEPTED SOLUTION

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
9 REPLIES 9

avatar

@Nandini Bhattacharjee

In lower versions of Hive, the following worked for me:

select TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP()));

In higher versions, you can use :

select CURRENT_DATE;

avatar
Contributor

Hi @Dinesh Chitlangia . Yes I do use this. But i'm looking for a way to generate a series of dates using HiveQL

avatar

@Nandini Bhattacharjee do you mean you want to generate dates given a range. say x days from the past till today, or x days in future from today ?

avatar
Contributor
@Dinesh Chitlangia

Exactly what i'm looking for....any ideas....

avatar
Super Collaborator
@Nandini Bhattacharjee

Can you try writing Hive UDF?

https://svn.apache.org/repos/asf/hive/trunk/contrib/src/java/org/apache/hadoop/hive/contrib/udf/UDFR... describes creating UDF for generating sequence of numbers.

You can follow the same approach to generate series of dates.

Also refer : https://community.hortonworks.com/questions/20168/sequence-number-generation-in-hive.html

avatar

@Nandini Bhattacharjee

1)Best way is to create UDF to generate sequence of date.

2)If you are SQL guy then create a stage table which loads row_number()over() as row_num. Then use this table to generate date_add(current_date,row_num) which will give you the date in sequence. Make sure you create rows as per your need in the stage table.

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
Contributor

@Dinesh Chitlangia brilliant! i will try this out. Thanks a lot!!

avatar
Contributor

Amazing answer!