question

JaredBagley-0328 avatar image
0 Votes"
JaredBagley-0328 asked JaredBagley-0328 answered

What syntax references Pipeline parameters in the where clause of a SQL query of 'Import Data' modules in Microsoft Azure Machine Learning designer?

I have created a pipeline in Microsoft Azure Machine Learning designer. I have added a Pipeline parameter myNumber in the pipeline settings, with a valid default value, to accept the unique ID of the asset in our DB so that the pipeline can return only the asset-specific data for use as our model input. Specifically I want to reference that pipeline parameter in the where clause of the SQL query in the 'Import Data' module that connects to our Azure SQL server.

I cannot find a reference in the documentation on how to do this. I have tried the methods specified for accomplishing this task in Azure Data Factory, using where RowId = @pipeline().parameters.myNumber or where RowId = @{variables('myNumber')} but the experiment fails with SqlException error code '137', variable not defined.

Can you please tell me the necessary syntax to reference Pipeline parameters in the where clause of a SQL query of 'Import Data' modules of Microsoft Azure Machine Learning designer?

azure-machine-learningdotnet-sqlite
· 1
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.

Hi, thanks for reaching out. We are inquiring on this and will share updates as soon as possible. Thanks.

1 Vote 1 ·
GiftA-MSFT avatar image
0 Votes"
GiftA-MSFT answered

Hi, designer does not support referencing pipeline parameters in sql query of "Import Data". However, you can write a one-line sql query and set the whole "database query" parameter as pipeline parameter as shown below. Hope this helps!

83320-image.png



image.png (63.1 KiB)
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.

JaredBagley-0328 avatar image
0 Votes"
JaredBagley-0328 answered

Thank you for replying and the suggestion, GiftA-MSFT.

I would not feel comfortable having an endpoint which accepts a wildcard query as a pipeline parameter as that sounds like a major security violation. Is there really no way at all to inject Pipeline parameters into the query? If that's the case, it sounds like the pipeline were only designed to use static data, which makes sense for training a model, but not for preprocessing input to be passed to a trained model that is deployed to an endpoint.

Is there a different Azure tool I should be using to create dynamic "Input Data" pipelines for preprocessing prior model scoring?

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.