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

Jared Bagley 1 Reputation point
2021-03-26T19:37:36.817+00:00

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 Learning
Azure Machine Learning
An Azure machine learning service for building and deploying models.
2,508 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,488 questions
{count} votes

2 answers

Sort by: Most helpful
  1. GiftA-MSFT 11,146 Reputation points
    2021-03-31T17:14:50.84+00:00

    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

    0 comments No comments

  2. Jared Bagley 1 Reputation point
    2021-03-31T17:32:04.787+00:00

    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?

    0 comments No comments