Ligação de loopback ao SQL Server a partir de um script em Python ou R

Aplica-se a: SQL Server 2019 (15.x) e versões posteriores Azure SQL Managed Instance

Aprenda a usar uma ligação de loopback com os Serviços de Aprendizagem Automática para voltar a ligar ao SQL Server via ODBC para ler ou escrever dados de um script Python ou R executado a partir da utilização de sp_execute_external_script. Pode usar isto quando os argumentos InputDataSet e OutputDataSet de sp_execute_external_script não são possíveis.

Cadeia de ligação

Para fazer uma ligação loopback, é necessário usar uma cadeia de ligação correta. Os argumentos obrigatórios mais comuns são o nome do driver ODBC, o endereço do servidor e o nome da base de dados.

Cadeia de ligação no Windows

Para autenticação no SQL Server no Windows, o script Python ou R pode usar o atributo da cadeia de conexão Trusted_Connection para autenticar como o mesmo utilizador que executou o sp_execute_external_script.

Aqui está um exemplo da cadeia de ligação loopback no Windows:

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

String de ligação no Linux

Para autenticação no SQL Server no Linux, o script Python ou R precisa de usar os atributos ClientCertificate e ClientKey do driver ODBC para autenticar como o mesmo utilizador que executou sp_execute_external_script. Isto requer a utilização da versão mais recente do driver ODBC 17.4.1.1.

Aqui está um exemplo da cadeia de ligação loopback no 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"

O endereço do servidor, a localização do ficheiro do certificado do cliente e a localização do ficheiro-chave do cliente são únicos para cada sp_execute_external_script um e podem ser obtidos através da API rx_get_sql_loopback_connection_string() para Python ou rxGetSqlLoopbackConnectionString() para R.

Para mais informações sobre os atributos das strings de conexão, consulte as Palavras-chave e Atributos DSN e Connection String para o Microsoft ODBC Driver for SQL Server.

String de conexão na Instância Gerida do Azure SQL

Para gerar a string de ligação para Azure SQL Managed Instance, consulte os exemplos nas secções seguintes. Use Driver ODBC 11 para SQL Server como driver ODBC para conexões em loopback.

Gerar string de ligação com revoscalepy para Python

Podes usar a API rx_get_sql_loopback_connection_string() em revoscalepy para gerar uma string de ligação correta para uma ligação loopback num script Python.

Aceita os seguintes argumentos:

Examples

Exemplo para SQL Server no 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

Exemplo para SQL Server no 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

Exemplo para Azure SQL Managed Instance:

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

Gerar cadeia de ligação com RevoScaleR para R

Podes usar a API rxGetSqlLoopbackConnectionString() no RevoScaleR para gerar uma string de ligação correta para uma ligação loopback num script R.

Aceita os seguintes argumentos:

Examples

Exemplo para SQL Server no 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

Exemplo para SQL Server no 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

Exemplo para Azure SQL Managed Instance:

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

Próximos passos

• driver ODBC da Microsoft para SQL Server
• Revoscalepy
• RevoScaleR