Подключение к SQL Server из скрипта Python или R с замыканием на себя

  • Статья

Применимо к:Управляемый экземпляр SQL Azure SQL Server 2019 (15.x)

Узнайте, как использовать подключение с замыканием на себя со Службами машинного обучения для обратного соединения с SQL Server через ODBC с целью чтения или записи данных из скрипта Python или R, выполняемого с помощью процедуры sp_execute_external_script. Его можно применять, если нельзя использовать аргументы InputDataSet и OutputDataSet процедуры sp_execute_external_script.

Строка подключения

Чтобы установить подключение с замыканием на себя, необходимо использовать правильную строку подключения. Обычно обязательными аргументами являются имя драйвера ODBC, адрес сервера и имя базы данных.

Строка подключения в Windows

Для проверки подлинности SQL Server в Windows скрипт Python или R может использовать атрибут строки подключения Trusted_Connection для прохождения проверки подлинности от имени того же пользователя, который запустил процедуру sp_execute_external_script.

Вот пример строки подключения с замыканием на себя в Windows:

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

Строка подключения в Linux

Для проверки подлинности SQL Server в Linux скрипт Python или R должен использовать атрибуты ClientCertificate и ClientKey драйвера ODBC, чтобы пройти проверку подлинности от имени пользователя, который выполнил процедуру 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.

Дополнительные сведения об атрибутах строки подключения см. в статье Ключевые слова и атрибуты строки подключения и имени DSN для Microsoft ODBC Driver for SQL Server.

Строка подключения для Управляемого экземпляра SQL Azure

Чтобы создать строку подключения для Управляемого экземпляра Azure SQL, см. примеры в следующих разделах. Используйте драйвер ODBC 11 для SQL Server в качестве драйвера ODBC для подключений с замыканием на себя.

Создание строки подключения с помощью пакета revoscalepy для Python

Вы можете использовать интерфейс API rx_get_sql_loopback_connection_string() из пакета revoscalepy, чтобы создать правильную строку подключения с замыканием на себя в скрипте Python.

Он принимает следующие аргументы:

Аргумент Описание
name_of_database Имя базы данных, к которой устанавливается подключение
odbc_driver Имя драйвера ODBC

Примеры

Пример для SQL Server в Windows:

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

Пример для SQL Server на Linux:

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

Пример для Управляемого экземпляра SQL Azure:

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

Вы можете использовать интерфейс API rxGetSqlLoopbackConnectionString() из пакета RevoScaleR, чтобы создать правильную строку подключения с замыканием на себя в скрипте R.

Он принимает следующие аргументы:

Аргумент Описание
nameOfDatabase Имя базы данных, к которой устанавливается подключение
odbcDriver Имя драйвера ODBC

Примеры

Пример для SQL Server в Windows:

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

Пример для SQL Server на Linux:

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

Пример для Управляемого экземпляра SQL Azure:

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

Дальнейшие действия