topic Re: Recursion Strategy in Data Engineering

Recursion Strategy

smpa01 — Tue, 03 Sep 2024 14:12:44 GMT

How can I perform recursive queries currently?

I am getting Recursive CTEs are unsupported in this version of Synapse SQL.

Is Recursion on cards?

Re: Recursion Strategy

AndyDDC — Tue, 03 Sep 2024 16:45:49 GMT

Hi at the moment there is no support for recursion in CTEs in the roadmap. This was something that was requested in Synapse Serverless (which the lakehouse sql endpoint and warehouse is based on) but never happened. What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft Learn

In terms of what you can do for recursion, I write a query in which I join the same table back on itself. E.G here's an example of basic recursion in an employees table. This example can be used in the Warehouse, but can also be used in the lakehouse sql endpoint if the tables existed (eg created by spark).

create table employees
(
    empid int,
    empname varchar(50),
    managerid int
);

insert into employees
values (1,'andy',null),(2,'dave',1),(3,'alice',1),(4,'glenda',3)

select
    a.empname as EmployeeName,
    b.empname as ManagerName
from employees a
left join employees b on b.empid = a.managerid

---------------------------------------------------------------

If my reply has been useful please consider providing kudos

and marking as the solution to help others users

---------------------------------------------------------------

Re: Recursion Strategy

smpa01 — Tue, 03 Sep 2024 17:58:42 GMT

I don't think it is easy to come up with an alternative of conventional cte recursion for all scenarios. There are situations which needs both recursive ctes and cursors. The funny part is cte recursion is supported in datamart but not in Fabric artifacts.

I am yet to test if cursor is supported.

TBH they are integral part of data engineering and MS must consider adding support for them in the Fabric elemnets that have sql endpoints.

Re: Recursion Strategy

AndyDDC — Tue, 03 Sep 2024 18:10:48 GMT

The reason recursion is supported in Datamart is that its backended by Azure SQL Database while the Lakehouse SQL Endpoint and Warehouse are a new MPP engine (started with Synapse Serverless). With Fabric, MS have built a SQL engine from scratch with fundamentally different architecture to SQL Server/Azure SQL Database so not everything we expect will be there right now, if at all.

Re: Recursion Strategy

AndyDDC — Tue, 03 Sep 2024 18:15:40 GMT

Btw there is an existing Idea for this https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=be2ad68a-2c28-ef11-8ee8-6045bdb5b6f0

Re: Recursion Strategy

vicenteajm21212 — Thu, 27 Feb 2025 11:06:39 GMT

Hello @smpa01 and @AndyDDC muy buenos dias , puedes utilizar , esta opcion:

DECLARE @FechaInicio DATE = 'fechainicio';

DECLARE @FechaFin DATE = 'fechafin';

-- 3Insertar datos en la tabla de calendario usando una tabla de números eficiente

WITH Numeros AS (

SELECT TOP (DATEDIFF(DAY, @FechaInicio, @FechaFin) + 1)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS Num

FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(n) -- 10 filas

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Y(n) -- 10*10 = 100 filas

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS Z(n) -- 100*10 = 1.000 filas

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS W(n) -- 1.000*10 = 10.000 filas

CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS V(n) -- 10.000*10 = 100.000 filas (máx)

)

INSERT INTO tabladestinocalendario (FechaClave, Fecha, Año, Mes, NombreMes, Trimestre, SemanaAño)

SELECT

CAST(FORMAT(DATEADD(DAY, Num, @FechaInicio), 'yyyyMMdd') AS INT) AS FechaClave,

DATEADD(DAY, Num, @FechaInicio) AS Fecha,

YEAR(DATEADD(DAY, Num, @FechaInicio)) AS Año,

MONTH(DATEADD(DAY, Num, @FechaInicio)) AS Mes,

DATENAME(MONTH, DATEADD(DAY, Num, @FechaInicio)) AS NombreMes,

DATEPART(QUARTER, DATEADD(DAY, Num, @FechaInicio)) AS Trimestre,

DATEPART(WEEK, DATEADD(DAY, Num, @FechaInicio)) AS SemanaAño

FROM Numeros;

A mi me funciona de manera eficiente para crear la tabla calendario basandome en set-based