Create a date table in hive

Question

How to create a table in hive which should have all the dates from 1st Jan 2016 till today (01-01-2016 to 12-10-2016)?

The table would have only one column i.e. the date column.

Thanks.

Answer 1

You can generate this data yourself.

---

Go to Hive shell and execute :

CREATE TABLE tbl1 (col1 date)

Default format for Date type in hive : YYYY-­MM-­DD. So we will generate data in this format.

Now generate data using shell script. Open terminal and fire :

gedit /tmp/test.sh

Copy this code :

#!/bin/bash
DATE=2016-01-01
for i in {0..285}
do
   NEXT_DATE=$(date +%Y-%m-%d -d "$DATE + $i day")
   echo "$NEXT_DATE"
done

You don't have execute permission by default, use :

chmod 777 /tmp/test.sh 

Now fire :

/tmp/test.sh >/tmp/test.csv

You got data in test.csv

2016-01-01
2016-01-02
2016-01-03
2016-01-04
........

Now go back to hive shell and fire :

load data local inpath '/tmp/test.csv' into table tbl1;

Your table with data is ready.

Answer 2

You can download Date dimension in excel format from the Kimball Group

Save Excel as csv, put in HDFS, create an external table on top of it.

I suggest you to create date_dim and keep all the columns in it. Date dimension should be in the warehouse. You can select only date column or create a view with necessary columns.

Also you can generate date range in Hive, see this answer: https://stackoverflow.com/a/55440454/2700344