Syntax Error When Attempting to run PREDICT function against table in dedicated pool in Synapse Studio

Question

I am attempting to complete the tutorial listed here : https://learn.microsoft.com/en-us/azure/synapse-analytics/machine-learning/tutorial-sql-pool-model-scoring-wizard

I am able to complete all of steps until I get to the final step, where I use Synapse Studio to generate either a stored procedure or view to run the PREDICT() statement against a table in a dedicated pool.

Here is the code Synapse generates :

<code>

-- Create a stored procedure for storing the scoring script.
CREATE PROCEDURE dbo.procPredictModel
AS
BEGIN
-- Select input scoring data and assign aliases.
WITH InputData AS
(
    SELECT
        CAST([fareAmount] AS [real]) AS [fareAmount],
        CAST([paymentType] AS [bigint]) AS [paymentType],
        CAST([passengerCount] AS [bigint]) AS [passengerCount],
        CAST([tripDistance] AS [real]) AS [tripDistance],
        [tripTimeSecs],
        CAST([pickupTimeBin] AS [varchar]) AS [pickupTimeBin]
    FROM [dbo].[nyc_taxi]
)
-- Using T-SQL Predict command to score machine learning models. 
SELECT *
FROM PREDICT (MODEL = (SELECT [model] FROM dbo.models WHERE [ID] = 'nyc_taxi_tip_predict:1'),
              DATA = InputData,
              RUNTIME = ONNX) WITH ([output_label] [bigint])
END
GO

-- Execute the above stored procedure.
EXEC dbo.procPredictModel

</code>

I consistently get the following error :

I have attempted multiple iterations of the code above, to no avail. Please help.

My Synapse workspace is currently in an Azure US Gov tenant, for further context.

Answer 1

@Andy

Thanks for using MS Q&A platform and posting your query

The error you're encountering when using the generated code in your Azure US Gov tenant likely stems from compatibility issues. ONNX runtime, which is specified in the code (RUNTIME = ONNX), might not be fully supported in your specific government cloud environment.

Here are some alternative approaches you can try:

1. Use SynapseML runtime:

• SynapseML, previously known as MMLSpark, is a library specifically designed for machine learning tasks within Azure Synapse Analytics. It offers better compatibility within the Azure US Gov cloud.
• In the Synapse Studio scoring wizard, during the "Runtime" selection, choose "SynapseML" instead of "ONNX". This should leverage SynapseML's capabilities for model scoring.

1. Leverage dedicated Spark pool:

• If SynapseML doesn't resolve the issue, consider using a dedicated Spark pool for scoring.
• Train your model using compatible libraries within the Spark pool environment. This approach might require additional coding but offers more flexibility for government cloud environments.

Please ensure below steps:

• Check Model Existence: Ensure that the model ‘nyc_taxi_tip_predict:1’ exists in the dbo.models table and that the ID is correct.
• Verify Data Types: Confirm that the data types in the CAST statements match the expected types in your machine learning model.
• Review Permissions: Make sure you have the necessary permissions to create procedures and execute the PREDICT statement in your Synapse workspace.

Hope this helps. Do let us know if you any further queries.