How to migrate Mysql Cursors to dbt?

Help
,
1

How to migrate Mysql Cursors to Dbt?
share some references to migrate

2

Can you share what the cursor does?
Are you migrating to a new warehouse (away from MySQL)?
What type of database is the target?

If you need iterate over rows, potentially a Python Model would be the cleanest way.

Otherwise you could use macros + hooks to create procedures.

3

One of dbt’s solutions architects was working on migrating cursors to dbt statements. I don’t know if she’s succeeded yet or not though! If she has I’ll ask her for an update.

In general, cursors and stored procedures are not part of the dbt mindset. In general you should try to work out how to express your models as simple select statements that describe the way you want your data to be at the end, not worry about how exactly it gets there.

As an example, we have a blog post about migrating stored procedures - what was originally an enormous block of code with upsert logic backed in turns into a handful of tightly defined modules which are unioned together. This isn’t a 1:1 mapping with cursors but it has the same thought pattern. Cursors and loops generally reflect the fact that the warehouse couldn’t process everything at once - with modern tooling that can be less true.

It would be helpful if you could share an example query you’re trying to convert.

1 Like
4

is there any way to share you privately?

5

It would be better if you could share an example (even a cut-down example) publicly - I am not familiar with cursors myself so probably won’t be able to help (but am willing to try!), and it would be useful for anyone else who comes along with more experience to be able to see what you’re trying to do.

6 
USE [temp_Staging]
GO
  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [Process].[Set_Fact]
as
Begin
Begin try

DECLARE @min int = 0,
        @max int = 0,
		@DID int,
		@columnlength int,
		

Select @columnlength = Max(len([scid])) from [xyz].[kbo].[ref_cus] as cx (nolock)


declare @ref_cus TABLE(

[scid] varchar( 20 ) NOT NULL UNIQUE CLUSTERED,
[CIDP] int not NULL
)

Insert into @ref_cus ( scid, CIDP )
Select 
	cx.[scid],
	cx.[CIDP]

from [xyz].[kbo].[ref_cus] as cx (nolock)
order by 	cx.[scid]



--Select count(*) from @ref_cus


Select 
	@FSDID_Min = Min(DID),
	@FSDID_Max = Max(DID)
from [xyz].[kbo].[FSD] as s (nolock)

DECLARE @DCur as CURSOR;
SET @DCur = CURSOR FOR
Select DID
from [xyz].[kbo].[Dim_Date] as d (nolock)
where [DID] >= @FSDID_Min
and   [DID] <= @FSDID_Max

open @DCur

FETCH NEXT FROM @DCur INTO  @DID

	WHILE @@FETCH_STATUS = 0
	BEGIN
	    Select @DID as 'DEV'

		update s
		set s.CID = cx.[CIDP]
		-
		from       [xyz].[kbo].[FSD] as s
		inner join @ref_cus as cx
			 on ( s.[scid] = cx.[scid] )
		where s.[DID] =  @DID
       and s.[CID] <> cx.[CIDP]
	   FETCH NEXT FROM @DCur INTO  @DID
	end

	CLOSE @DCur;
	DEALLOCATE @DCur;
	

End try
Begin Catch

exec kbo.usp_GetErrorInfo

End Catch

End
7

Thanks for sharing - unfortunately I don’t have any specific tips to give as I’ve never worked with cursors and only have a rough grasp of what it’s doing there, but it looks like it’s updating a table and/or inserting blocks of rows into the table.

The closest equivalent in dbt is an incremental model, and again I think you might find some useful stuff in the stored proc migration guide to help you stop thinking about things in terms of updates and move to a more declarative approach.

If anyone with more specific context comes along, I’d love to read their take!