[Hive]Create Date Dim

create_date_dim.hql

CREATE DATABASE IF NOT EXISTS common_ods;
CREATE TABLE IF NOT EXISTS common_ods.dim_date AS WITH dates AS
  (SELECT date_add("${start_day}", a.pos) AS d
   FROM
     (SELECT posexplode(split(repeat("o", datediff("${end_day}", "${start_day}")), "o"))) a)
SELECT row_number() OVER (
                          ORDER BY d) date_sk,
                         d AS d,
                         year(d) AS YEAR,
                         month(d) AS MONTH,
                         day(d) AS DAY,
                         date_format(d,
                                     'u') AS daynumber_of_week,
                         date_format(d,
                                     'EEEE') AS dayname_of_week,
                         date_format(d,
                                     'D') AS daynumber_of_year
FROM dates
SORT BY d ;