*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;