pythonsql-serverazure-machine-learning-servicemicrosoft-machine-learning-server

Remote Connection fails in setup of Python data-science client for SQL Server Machine Learning Services


I am trying to test the remote connection of a Python data-science client with SQL Server Machine Learning Services following this guide: https://learn.microsoft.com/en-us/sql/machine-learning/python/setup-python-client-tools-sql (section 6). Running the following script

def send_this_func_to_sql():
    from revoscalepy import RxSqlServerData, rx_import
    from pandas.tools.plotting import scatter_matrix
    import matplotlib.pyplot as plt
    import io
    
    # remember the scope of the variables in this func are within our SQL Server Python Runtime
    connection_string = "Driver=SQL Server;Server=localhost\instance02;Database=testmlsiris;Trusted_Connection=Yes;"
    
    # specify a query and load into pandas dataframe df
    sql_query = RxSqlServerData(connection_string=connection_string, sql_query = "select * from iris_data")
    df = rx_import(sql_query)
    
    scatter_matrix(df)
    
    # return bytestream of image created by scatter_matrix
    buf = io.BytesIO()
    plt.savefig(buf, format="png")
    buf.seek(0)
    
    return buf.getvalue()

new_db_name = "testmlsiris"
connection_string = "driver={sql server};server=sqlrzs\instance02;database=%s;trusted_connection=yes;" 

from revoscalepy import RxInSqlServer, rx_exec

# create a remote compute context with connection to SQL Server
sql_compute_context = RxInSqlServer(connection_string=connection_string%new_db_name)

# use rx_exec to send the function execution to SQL Server
image = rx_exec(send_this_func_to_sql, compute_context=sql_compute_context)[0]

yields the following error message returned by rx_exec (stored in the image variable)

connection_string: "driver={sql server};server=sqlrzs\instance02;database=testmlsiris;trusted_connection=yes;"
num_tasks: 1
execution_timeout_seconds: 0
wait: True
console_output: False
auto_cleanup: True
packages_to_load: []
description: "sqlserver"
version: "1.0"
XXX lineno: 2, opcode: 0
Traceback (most recent call last):
  File "<string>", line 3, in <module>
  File "E:\SQL\MSSQL15.INSTANCE02\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 664, in rx_sql_satellite_pool_call
    exec(inputfile.read())
  File "<string>", line 34, in <module>
  File "E:\SQL\MSSQL15.INSTANCE02\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 886, in rx_remote_call
    results = rx_resumeexecution(state_file = inputfile, patched_server_name=args["hostname"])
  File "E:\SQL\MSSQL15.INSTANCE02\PYTHON_SERVICES\lib\site-packages\revoscalepy\computecontext\RxInSqlServer.py", line 135, in rx_resumeexecution
    return _state["function"](**_state["args"])
  File "C:\Users\username\sendtosql.py", line 2, in send_this_func_to_sql
SystemError: unknown opcode
====== sqlrzs ( process 0 ) has started run at 2022-06-29 13:47:04 W. Europe Daylight Time ======
{'local_state': {}, 'args': {}, 'function': <function send_this_func_to_sql at 0x0000020F5810F1E0>}

What is going wrong here? Line 2 in the script is just an import (which works when testing Python scripts on SQL Server directly). Any help is appreciated - thanks.


Solution

  • I just figured out the reason. As of today, the Python versions for the data clients in https://learn.microsoft.com/de-de/sql/machine-learning/python/setup-python-client-tools-sql?view=sql-server-ver15 are not the newest (revoscalepy Version 9.3), while the version of Machine Learning Services that we have running in our SQL Server is already 9.4.7. However, the revoscalepy libraries for the client and server must be the same, otherwise the deserialization fails server-sided.