-1

I am currently developing an Azure ML pipeline that is fed data and triggered using Power Automate and outputs to a couple of SQL Tables in Azure SQL. One of the tables that is generated by the pipeline needs to be refreshed each time the pipeline is run, and as such I need to be able to drop the entire table from the SQL database so that the only data present in the table after the run is the newly calculated data.

Now, at the moment I am dropping the table as part of the Power Automate flow that feeds the data into the pipeline initially. However, due to the size of the dataset, this means that there is a 2-6 hour period during which the analytics I am calculating are not available for the end user while the pipeline I created runs.

Hence, my question; is there any way to perform the "DROP TABLE" SQL command from within my Azure ML Pipeline? If this is possible, it would allow me to move the drop to immediately before the export, which would be a great improvement in performance.

EDIT: From discussions with Microsoft Support, it does appear that this is not possible due to how the current ML Platform is designed. Not answering this question in case someone does solve it, but adding this note so that people who come along with the same problem know.

Angus B
  • 129
  • 7

1 Answers1

0

Yes you can do anything you want inside an Azure ML Pipeline with a Python Script Step. I'd recommend using the pyodbc library, and you'd just have to pass the credentials to your script as environment variables or script arguments.

Anders Swanson
  • 3,637
  • 1
  • 18
  • 43
  • So the issue I am having is that the Execute Python Script module doesn't support using libraries like pyodbc, either natively or by installing them at runtime (which is gross but sometimes necessary). Do you know a way around this? Reference: [Execute Python Script Reference](https://learn.microsoft.com/en-us/azure/machine-learning/algorithm-module-reference/execute-python-script) – Angus B May 27 '21 at 23:17
  • I strongly consider you to start using the new Azure ML Studio Designer. The experience you are using has had no new development for 3+ years – Anders Swanson May 27 '21 at 23:37
  • I am using the new Azure ML Studio Designer, not the classic. That restriction is from the documentation for the new designer. – Angus B May 31 '21 at 00:56