0

I have a table in SQL Server that has a few columns: ViewName, ExternalTableDefinition_Synapse, ExternalTableDefinition_ADB , ViewDefinition_Synapse, ViewDefinition_ADB

There may be hundreds of thousands of records here.

The task is to iterate over this table and get the external tables and views created on Synapse (Serverless) and Azure DataBricks. However,

  • The process must be as fast as possible.
  • There must be logging in case any of failed scripts.

Solutions tried till now –

  • Looping thru the table in ADF and executing it via notebook (ADB) and Script Activity (Synapse) – ADF brings in parallelism but causes queueing. Also, expensive.
  • Copy this table into ADLS and create an external table over it. Have some logic to concatenate the scripts and execute it – it runs as an atomic operation, so if out of 1000, 1 script has issues, the rest of them also won’t run.

Right now, I have no option but to lean towards the first one as it performs logging, albeit being slow overall.

Looking for better solutions.

SouravA
  • 5,147
  • 2
  • 24
  • 49

1 Answers1

0

Considering a pyspark execution maybe it's worth implementing the run parallel method as shown in Douglas Moore's presentation (available here https://microsites.databricks.com/sites/default/files/2022-07/DBA-Perspective_Optimizing-Performance-Table-by-Table.pdf).

enter image description here

The general idea is implemented in his GitHub code available here https://github.com/dmoore247/DBA-Helper/blob/main/DAIS2022.py and maybe it could be executed in synapse with some minor tweaks.

Herivelton Andreassa
  • 214
  • 2
  • 6