从 Python 或 R 脚本到 SQL Server 的环回连接

  • 项目

适用于： SQL Server 2019 (15.x) Azure SQL 托管实例

了解如何结合使用环回连接与机器学习服务通过 ODBC 连接回 SQL Server，以从基于 sp_execute_external_script 执行的 Python 或 R 脚本读取或写入数据。 当无法使用 sp_execute_external_script 的 InputDataSet 和 OutputDataSet 参数时，可以使用此选项 。

连接字符串

若要建立环回连接，需要使用正确的连接字符串。 常用的强制参数是 ODBC 驱动程序的名称、服务器地址和数据库名称。

Windows 上的连接字符串

若要在 Windows 的 SQL Server 上进行身份验证，Python 或 R 脚本可以使用 Trusted_Connection 连接字符串属性作为执行 sp_execute_external_script 的同一用户进行身份验证。

以下是 Windows 上的环回连接字符串示例：

"Driver=SQL Server;Server=.;Database=nameOfDatabase;Trusted_Connection=Yes;"

Linux 上的连接字符串

若要在 Linux 的 SQL Server 上进行身份验证，Python 或 R 脚本需要使用 ODBC 驱动程序的 ClientCertificate 和 ClientKey 属性来作为执行 sp_execute_external_script 的同一用户进行身份验证。 这需要使用最新的 ODBC 驱动程序版本 17.4.1.1。

以下是 Linux 上的环回连接字符串示例：

"Driver=ODBC Driver 17 for SQL Server;Server=fe80::8012:3df5:0:5db1%eth0;Database=nameOfDatabase;ClientCertificate=file:/var/opt/mssql-extensibility/data/baeaac72-60b3-4fae-acfd-c50eff5d34a2/sqlsatellitecert.pem;ClientKey=file:/var/opt/mssql-extensibility/data/baeaac72-60b3-4fae-acfd-c50eff5d34a2/sqlsatellitekey.pem;TrustServerCertificate=Yes;Trusted_Connection=no;Encrypt=Yes"

服务器地址、客户端证书文件位置和客户端密钥文件位置对于每个 sp_execute_external_script 都是唯一的，可通过使用 API“rx_get_sql_loopback_connection_string()”（适用于 Python）或“rxGetSqlLoopbackConnectionString()”（适用于 R）来获取这些信息。

有关连接字符串属性的详细信息，请参阅 Microsoft ODBC Driver for SQL Server 的 DSN 和连接字符串关键字和属性。

Azure SQL 托管实例上的连接字符串

若要为 Azure SQL 托管实例生成连接字符串，请参阅后续部分中的示例。 使用 ODBC Driver 11 for SQL Server 作为环回连接的 ODBC 驱动程序。

使用 revoscalepy 为 Python 生成连接字符串

可使用 revoscalepy 中的 API“rx_get_sql_loopback_connection_string()”来为 Python 脚本中的环回连接生成正确的连接字符串。

它接受以下参数：

参数 描述
name_of_database 要建立连接的数据库的名称
odbc_driver odbc 驱动程序的名称

示例

Windows 上的 SQL Server 的示例：

EXECUTE sp_execute_external_script
@language = N'Python',
@script = N'
from revoscalepy import rx_get_sql_loopback_connection_string, RxSqlServerData, rx_data_step
loopback_connection_string = rx_get_sql_loopback_connection_string(odbc_driver="SQL Server", name_of_database="DBName")
print("Connection String:{0}".format(loopback_connection_string))
data_set = RxSqlServerData(sql_query = "select col1, col2 from tableName",
                           connection_string = loopback_connection_string)
OutputDataSet = rx_data_step(data_set)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

Linux 上的 SQL Server 的示例：

EXECUTE sp_execute_external_script
@language = N'Python',
@script = N'
from revoscalepy import rx_get_sql_loopback_connection_string, RxSqlServerData, rx_data_step
loopback_connection_string = rx_get_sql_loopback_connection_string(odbc_driver="ODBC Driver 17 for SQL Server",
                                                                   name_of_database="DBName")
print("Loopback Connection String:{0}".format(loopback_connection_string))
data_set = RxSqlServerData(sql_query = "select col1, col2 from tableName",
                           connection_string = loopback_connection_string)
OutputDataSet = rx_data_step(data_set)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

Azure SQL 托管实例的示例：

EXECUTE sp_execute_external_script
@language = N'Python',
@script = N'
from revoscalepy import rx_get_sql_loopback_connection_string, RxSqlServerData, rx_data_step
loopback_connection_string = rx_get_sql_loopback_connection_string(odbc_driver="ODBC Driver 11 for SQL Server", name_of_database="DBName")
print("Connection String:{0}".format(loopback_connection_string))
data_set = RxSqlServerData(sql_query = "select col1, col2 from tableName",
                           connection_string = loopback_connection_string)
OutputDataSet = rx_data_step(data_set)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

使用 RevoScaleR 为 R 生成连接字符串

可使用 RevoScaleR 中的 API“rxGetSqlLoopbackConnectionString()”来为 R 脚本中的环回连接生成正确的连接字符串。

它接受以下参数：

参数 描述
nameOfDatabase 要建立连接的数据库的名称
odbcDriver odbc 驱动程序的名称

示例

Windows 上的 SQL Server 的示例：

EXECUTE sp_execute_external_script
@language = N'R',
@script = N'
    loopbackConnectionString <- rxGetSqlLoopbackConnectionString(nameOfDatabase="DBName", odbcDriver ="SQL Server")
    print(paste("Connection String:", loopbackConnectionString))
    dataSet <- RxSqlServerData(sqlQuery = "select col1, col2 from tableName",
                               connectionString = loopbackConnectionString)
    OutputDataSet <- rxDataStep(dataSet)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

Linux 上的 SQL Server 的示例：

EXECUTE sp_execute_external_script
@language = N'R',
@script = N'
    loopbackConnectionString <-  rxGetSqlLoopbackConnectionString(nameOfDatabase="DBName", 
                                                                  odbcDriver ="ODBC Driver 17 for SQL Server")
    print(paste("Connection String:", loopbackConnectionString))
    dataSet <- RxSqlServerData(sqlQuery = "select col1, col2 from tableName", 
                               connectionString = loopbackConnectionString)
    OutputDataSet <- rxDataStep(dataSet)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

Azure SQL 托管实例的示例：

EXECUTE sp_execute_external_script
@language = N'R',
@script = N'
    loopbackConnectionString <- rxGetSqlLoopbackConnectionString(nameOfDatabase="DBName", odbcDriver ="ODBC Driver 11 for SQL Server")
    print(paste("Connection String:", loopbackConnectionString))
    dataSet <- RxSqlServerData(sqlQuery = "select col1, col2 from tableName",
                               connectionString = loopbackConnectionString)
    OutputDataSet <- rxDataStep(dataSet)
'
WITH RESULT SETS ((col1 int, col2 int))
GO

后续步骤