从 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