how to increase max iteration count in snowflake?

user_4845799 0
Viewed 2285

When I write the code below in snowflake, I've got the error.

set start_date = '2020-08-01'::TIMESTAMP_NTZ(9);
set end_date = '2020-11-30'::TIMESTAMP_NTZ(9);

WITH DateTable AS (
    SELECT $start_date AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate) FROM DateTable WHERE MyDate < $end_date
)
SELECT * FROM DateTable;

The error message is as below...

Recursion exceeded max iteration count (100)

It seems there is no option to increase max iteration count by my self.
How can I increase the limit of interation count?

snowflake-cloud-data-platform recursive-query common-table-expression datetime sql
2 Answers

Non recursive version using generator:

SET (start_date,end_date)=('2020-08-01'::TIMESTAMP_NTZ(9),'2020-11-30'::TIMESTAMP_NTZ(9));
SET c = (SELECT DATEDIFF(DAY, $start_date, $end_date));

SELECT DATEADD(DAY, c.n, $start_date) AS calc_date
FROM(SELECT ROW_NUMBER() OVER (ORDER BY 1) - 1 FROM TABLE(generator(rowcount=>$c))) c(n);

Explanation:

Variable c holds number of days between start/end date and is an input to generator. Generator returns specified number of rows. ROW_NUMBER() OVER() - 1 windowed function returns consecutive numbers starting from 0, which are added to start_date.

user_50708790

It doesn't seem like the maximum iterations is easily modifiable. The documentation says:

In theory, constructing a recursive CTE incorrectly can cause an infinite loop. In practice, Snowflake prevents this by limiting the number of iterations that the recursive clause will perform in a single query. The MAX_RECURSIONS parameter limits the number of iterations.

To change MAX_RECURSIONS for your account, please contact Snowflake Support.

For your particular use case, you can work around the limitation by changing the logic of the query. You don't really need recursion to achieve your goal; instead, you can use window functions and any table that as a sufficient number of rows - say bigtable(col):

select dateadd(day, x.rn - 1, $start_date) as mydate
from (select row_number() over (order by col) rn from bigtable) x
where dateadd(day, x.rn - 1, $start_date) <= $end_date
user_106767160
Related