question

javier-8889 avatar image
0 Votes"
javier-8889 asked ramr-msft commented

azure ml designer: how to use parameters with sql transformation?

Hi, I would like to apply an SQL Query Transformation to my data in Azure ML Designer but the transformation needs to take two parameters as inputs. How do I reference them

The idea is to have something like this:

 select * from customers where account = [param1] and cuType = [param2] 


I have defined param1 and param2 as pipeline parameters but not sure how to reference them in the sql script

azure-machine-learning
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ramr-msft avatar image
0 Votes"
ramr-msft answered ramr-msft commented

@javier-8889 Thanks for the question, When designing a pipeline in Azure ML Designer, each step or module creates intermediate datasets that can be seen using the UI. Those datasets are persisted in the blob storage. To filter data, you can use Apply SQL Transformation to write SQL query or Split Data using regular expression. You can also use Execute Python Script and Execute R script module to write your own data processing logic.

Designer Pipeline Documentation:https://github.com/Azure/AzureMachineLearningGallery/blob/main/pipelines/README.md


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks @ramr-msft for pointing out to the different ways in which we can filter data in Designer!

However my question was specifically about how to reference pipeline parameters in a SQL Transformation module. Unfortunately I was unable to find any reference to this in the links that you provided or during my web search for other sources of information.

Do you know whether is it possible to accomplish using SQL Transformation? If yes, would you mind telling me the right syntax for this?

thanks again

ps: I did notice though that the Split module does allow using pipeline parameters as part of a regular expression in a straightforward way. Thanks for pointing that out! but still wondering whether how to do that with a SQL Transformation

0 Votes 0 ·

@javier-8889 unfortunatly, that is not supported parameters right now. But we believe custom component would be some possible work around where you could define your own inputs. So either you could put your whole SQL string as a parameter or you could fix most of your SQL processing logic in py code and only expose some parameters to that custom module. Please check the tutorial about how to use custom component at here: AzureMachineLearningGallery/tutorial at main · Azure/AzureMachineLearningGallery (github.com).


0 Votes 0 ·