How to resolve Athena Recursive CTEs (WITH RECURSIVE Clause) with error "Recursion depth limit exceeded (10)"

In Athena engine version 3, recursive queries using the WITH clause are supported, but they have a default maximum recursion depth limit of 10. When you exceed this limit, you'll receive the error "Recursion depth limit exceeded (10). Use 'max_recursion_depth' session property to modify the limit."

To resolve this issue, you need to set the 'max_recursion_depth' session property. While the exact method to set this property in the Athena console isn't explicitly documented in the available information, you have two options:

1. Try adding the session property at the beginning of your query:

SET max_recursion_depth = 20;

WITH RECURSIVE your_cte AS (
-- your recursive query
)
SELECT * FROM your_cte;

2. Alternatively, you could restructure your query to reduce the recursion depth needed, perhaps by breaking it into multiple steps or using a different approach.

When working with recursive CTEs in Athena, it's important to always include a proper termination condition in your WHERE clause to prevent infinite recursion, which could lead to errors like "Recursive CTE out of working buffers" or exceeding the maximum rows limit.
Sources
Athena recursive cte NOT_SUPPORTED: Recursion depth limit exceeded (10). Use 'max_recursion_depth' | AWS re:Post
WITH clause - Amazon Redshift