Getting Error SQL Server 2019 machine learning service with Python

Question

*I am new to SQL Server (2019) Machine Learning Service.... I have a Table called, TBL_DRIVER_TEST_RESULT, which is having 3 columns, [TEST_DATE], [DRIVER_ID], [RED_POINT], over here I have data of past 1 year. Each driver (around 200) goes for daily test, and they get a RED Point score... this is basically a penalty score. i.e., Scoring 0 on any day is best, but if driver make mistake, then they get red points.... Now based on their past 1 year's performance data, I wanted to cluster them in 5 groups.... (Group1): Consistently Good Performing (Low Red Point) drivers (Group2): Consistently bad Performing (High Red point_) Drivers, (Group3): Drivers who are really improving their performance over time (Group4): Drivers whose performance getting worse. (Group5): Driver who performs random.... *
Now I tried using some machine learning here (not sure if there are better /easier ways to achieve above) and tried following Store Procedure... But when I am executing this: I am constantly getting error as:

"Error executing external script: Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'."

Please can someone help?

ALTER PROCEDURE [dbo].[usp_cluster_DRIVER]
AS
BEGIN
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @language nvarchar(20) = N'Python';
    DECLARE @script nvarchar(max) = N'
import pandas as pd
from sklearn.cluster import KMeans

# Get input data from SQL Server
df = input_data_1

# Remove any null values
df.dropna(inplace=True)

# Pivot the data to create a matrix with drivers as rows and days as columns
pivoted_df = df.pivot(index="DRIVER_ID", columns="TEST_DATE", values="RED_POINT")

# Use KMeans clustering to cluster the drivers based on their daily red points
kmeans = KMeans(n_clusters=5, random_state=42)
kmeans.fit(pivoted_df)

# Add the cluster labels to the input dataframe
df_clustered = pd.concat([pivoted_df.reset_index()["DRIVER_ID"], pd.DataFrame(kmeans.labels_, columns=["cluster"])], axis=1)

# Output the clustered data to SQL Server
output_data_1 = df_clustered
    ';

    DECLARE @input_data_1_name nvarchar(128) = N'input_data_1';
    DECLARE @input_data_1_query nvarchar(max) = N'SELECT [TEST_DATE], [DRIVER_ID], [RED_POINT] FROM [dbo].[TBL_DRIVER_TEST_RESULTS]';
    DECLARE @output_data_1_name nvarchar(128) = N'output_data_1';
	DECLARE @params nvarchar(1000) = cast('@input_data_1_query nvarchar(max), @output_data_1_name nvarchar(128)' as nvarchar(1000))


    -- Execute the external script
    BEGIN TRY
        EXEC sp_execute_external_script
            @language = @language,
            @script = @script,
            @input_data_1_query = @input_data_1_query,
            @input_data_1_name = @input_data_1_name,
            @output_data_1_name = @output_data_1_name,
            @params = @params,
            @output_data_1_table = N'result'
    END TRY
    BEGIN CATCH
        PRINT 'Error executing external script: ' + ERROR_MESSAGE()
        RETURN
    END CATCH

    -- Select the output data
    SELECT *
    INTO result
    FROM output_data_1
    WHERE 1 = 0;

Answer

The error message is not the best. The error is on this line:

@input_data_1_query = @input_data_1_query,

That should be:

            @input_data_1 = @input_data_1_query,

As for the actual @params parameter you have, I don't see that you need it. You are only listing existing parameters to sp_execute_external_script. @params is for parameters for your own data. For instance, you only wanted to run the Python script for a specific driver, you would have:

@params = N'@driverid int',
@driverid = 46

Answer

Hi @amjee84

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'

Normally, this error message indicates that you need to take NVARCHAR as parameter type not VARCHAR. Or in a quick way use unicode N prefix. But I didn't find such issues in your code. Did you modify it as Erland answered?

Best regards,

Cosmog Hong

Getting Error SQL Server 2019 machine learning service with Python

2 answers