How to create a data or calendar table within Dremio?

1

I’m looking to have a “Calendar” table created where it will give me the date between ranges ,say the past 5 years through today, or the past 5 years to the next 5 years. I’ve done something similar from data from a dataset but I need to ensure this table has dates that do not exist in the dataset I am querying from.

For example, here is what I’m doing today:

    SELECT 
    DISTINCT Date_YearMonthDay, 
    CONCAT(TO_CHAR(Date_YearMonthDay, 'yyyy'), '-', 
    TO_CHAR(Date_YearMonthDay, 'MM')) as CalYearMonth, 
    TO_CHAR(Date_YearMonthDay, 'yyyy') as CalYear, 
    TO_CHAR(Date_YearMonthDay, 'MON') as CalMonth
FROM 
    (
        SELECT DISTINCT CreatedDate as Date_YearMonthDay FROM TableWithDates
    )
ORDER BY DATE_YearMonthDay desc

However, I’d like Dremio to provide me with the TableWithDates based on the range I’m looking for. Similar to what is posted here: https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql.

Sure I can do this the physical database/datasource, but I’d rather do it in Dremio for a variety of reasons.

2

@rwetzeler

In the example you have provided, SQL Server has table called Calendar which they run a loop to do this. Dremio does not have one. Is your ask to have a table like SQL server?

Thanks
Bali

3

Well, I’d like to create this somehow, whether its a loop process, or something like in DAX where you can do CALENDARAUTO()

I am using this currently in Power BI and would like Dremio to perform this for me as a VDS so I can re-use in multiple places, rather than siloed in Power BI Model.

5

I know this thread is old, but I recently had the same problem and here’s what I came up with:

SELECT DATE_SUB(CURRENT_DATE, (ROW_NUMBER() OVER()) - 1) AS CalendarDate
FROM 
(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON'))),
(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON'))),
(SELECT FLATTEN(CONVERT_FROM('[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]' ,'JSON')))

This query uses the FLATTEN function to create 16x16x16 = 4096 rows, numbers them from 0 to 4095 and then uses the DATE_SUB function to create the dates.
If you want a range between the past 5 years and the next 5 years, you could just change CURRENT_DATE to DATE_ADD(CURRENT_DATE, CAST(5 AS INTERVAL YEAR)) and add a WHERE-clause.
If you want to have even more days, you could just add more comma-separated zeros to the json string.

6

Another possible way would be the following query:

WITH startDate AS (SELECT CURRENT_DATE),
     endDate AS (SELECT DATE_ADD(CURRENT_DATE, 365))
SELECT DATE_SUB((SELECT * FROM endDate), (ROW_NUMBER() OVER()) - 1) AS CalendarDate
FROM (SELECT FLATTEN(REGEXP_SPLIT(REPEATSTR(',', TIMESTAMPDIFF(day, (SELECT * FROM startDate), (SELECT * FROM endDate))), ',', 'ALL', 2147483647)))
1 Like