Databricks Community

Sha_1890 · ‎07-25-2022

I am working in a migration project, where lift and shift method is used to migrate SQL server DB from onprem to AZure Cloud. There are a lot of stored procedures used for integration in On prem. Now here in On prem , to process the XMl file and execute the same procedures pointing to the cloud Db I need to write a code in Databricks. I have code to execute a single stored procedure using Scala, as I am new to coding in python/scala I couldn't find the right method to execute the procedures.

The below code is what I have used to execute one procedure- sample

%scala

val username = "xxxxx"

val pass = "xxxxx"

val url = "jdbc:sqlserver://***.database.windows.net:1433;databaseName=***"

val table = "SalesLT.Temp3"

val query = s"EXEC sp_truncate_table '${table}'"

val conn = DriverManager.getConnection(url, username, pass)

val rs = conn.createStatement.execute(query)

I have a requirement to execute some 10 stored procedures in series. Looking forward for your suggestions.

-werners- · ‎07-25-2022

So the above code works but you want to do this 10 times (for different SPs)?

Sha_1890 · ‎07-25-2022

yes , I want to execute the list of procedure in sequential order.

-werners- · ‎07-25-2022

In a databricks notebooks, cells are executed sequentially

So you could create a cell per SP and execute those.

But I don´t get why you need Databricks/scala for this.

Sha_1890 · ‎07-25-2022

The existing stored procedures in On prem has the integration part in Procedures which is then executed by .exe file to process the incremental data. so here in cloud instead of .exe file databricks is used to execute the procedures which is not possible through Python in a simple way. So I used Scala to execute the procedure.

-werners- · ‎07-25-2022

I see, but for that kind of use case I would use Data Factory.

As there is no data processing happening on Databricks itself, I see no added value in using it.

Calling SPs can be done in ADF, no scala needed.

Sha_1890 · ‎07-25-2022

yes right,but the only concern here in using ADF is cost. Databricks is likely to be cheap than the ADF is what business has agreed and they want this logic to be implemented in Databricks.