0

I need to migrate a certain table using databricks notebooks. Problem is databricks SQL doesn't support the implementation of cursors.

What alternative could I use?

This is roughly what I need to do, for reference:

`

Declare @GrupoReg as varchar(10)
Declare @DescReg as varchar(50)
Declare @OrdenReg as varchar(10)

Declare RegistroGpo cursor for 
select distinct group, groupdesc, grouporder
from #table1
order by gruouporder

Open RegisterGpo

fetch next from RegistroGpo into @GrupoReg, @DescReg, @OrdenReg

while (@@fetch_status=0) 
begin
    update #b set groupconcentrado = case when groupconcentrado = '' then @GrupoReg else groupconcentrado + ',' + @GrupoReg end,
              groupconcentradodesc = case when groupconcentradodesc = '' then @DescReg else groupconcentradodesc + ',' + @DescReg end,
              groupconcentradoorder = case when groupconcentradoorder = '' then @OrdenReg else groupconcentradoorder + @OrdenReg end
    from #table2 a
        join #table1 b on a.suc = b.suc and a.cli = b.cli and b.group = @GrupoReg

    fetch next from RegisterGpo into @GrupoReg, @DescReg, @OrdenReg
end

`

Any help is appreciated

My knowledge of databricks sql and scala is pretty barebones. I don't really know what to try

Finatsu
  • 1
  • 1
  • I believe you'll have to rewrite the whole thing in Scala Dataset/Dataframe. You won't be able to do the same with Spark SQL. – Gaël J Oct 26 '22 at 19:58
  • But, why do this with Spark? These are regular SQL tables? – Gaël J Oct 26 '22 at 19:59
  • @GaëlJ I'm loading tables to synapse from azure datalake through databricks – Finatsu Oct 26 '22 at 20:02
  • I'll try creating 2 dataframes off tablaa and tableb. But how can I rewrite the fetch cursor in scala? Is there an equivalent? – Finatsu Oct 26 '22 at 20:09
  • @GaëlJ any lead on how can I replicate using dataframes? – Finatsu Oct 26 '22 at 22:35
  • Forget the whole cursor thing. What is it used for? Iterating over distinct values one by one and do something, right? Then you can do the same with Spark in a `df.distinct.foreachPartition()` way for example. – Gaël J Oct 27 '22 at 05:18

0 Answers0