Connexion de bouclage à SQL Server à partir d’un script Python ou R

  • Article

S’applique à : SQL Server 2019 (15.x) Azure SQL Managed Instance

Découvrez comment utiliser une connexion de bouclage avec Machine Learning Services pour vous reconnecter à SQL Server sur ODBC afin de lire ou d’écrire des données à partir d’un script Python ou R exécuté à partir de sp_execute_external_script. Vous pouvez l’utiliser lorsque vous n’êtes pas en mesure d’utiliser les arguments InputDataSet et OutputDataSet de sp_execute_external_script.

Chaîne de connexion

Pour établir une connexion de bouclage, vous devez utiliser une chaîne de connexion correcte. Les arguments obligatoires courants sont le nom du pilote ODBC, l’adresse du serveur et le nom de la base de données.

Chaîne de connexion sur Windows

Pour l’authentification sur SQL Server sur Windows, le script Python ou R peut utiliser l’attribut de chaîne de connexion Trusted_Connection pour s’authentifier en tant qu’utilisateur identique à celui qui a exécuté le sp_execute_external_script.

Voici un exemple de chaîne de connexion de bouclage sur Windows :

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

Chaîne de connexion sur Linux

Pour l’authentification sur SQL Server sur Linux, le script Python ou R doit utiliser les attributs ClientCertificate et Clientkey du pilote ODBC pour s’authentifier en tant qu’utilisateur identique à celui ayant exécuté sp_execute_external_script. Cela nécessite l’utilisation de la dernière version du pilote ODBC, la version 17.4.1.1.

Voici un exemple de chaîne de connexion de bouclage sur 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"

L’adresse du serveur, l’emplacement du fichier de certificat client et l’emplacement du fichier de clé client sont uniques à chaque sp_execute_external_script et peuvent être obtenus à l’aide de l’API rx_get_sql_loopback_connection_string () pour Python ou rxGetSqlLoopbackConnectionString () pour R.

Pour plus d’informations sur les attributs de chaîne de connexion, consultez Attributs et mots clés de chaîne de connexion et DSN pour Microsoft ODBC Driver for SQL Server.

Chaîne de connexion sur Azure SQL Managed Instance

Pour générer la chaîne de connexion d’Azure SQL Managed Instance, consultez les exemples des sections suivantes. Utilisez ODBC Driver 11 for SQL Server comme pilote ODBC pour les connexions de bouclage.

Générer une chaîne de connexion avec revoscalepy pour Python

Vous pouvez utiliser l’API rx_get_sql_loopback_connection_string() dans revoscalepy pour générer une chaîne de connexion correcte pour une connexion de bouclage dans un script Python.

Les arguments suivants sont acceptés :

Argument Description
name_of_database Nom de la base de données vers laquelle la connexion doit être établie
odbc_driver Nom du pilote ODBC

Exemples

Exemple pour SQL Server sur 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

Exemple pour SQL Server sur 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

Exemple pour 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

Générer une chaîne de connexion avec revoscalepy pour R

Vous pouvez utiliser l’API rxGetSqlLoopbackConnectionString() dans RevoScaleR pour générer une chaîne de connexion correcte pour une connexion de bouclage dans un script R.

Les arguments suivants sont acceptés :

Argument Description
nameOfDatabase Nom de la base de données vers laquelle la connexion doit être établie
odbcDriver Nom du pilote ODBC

Exemples

Exemple pour SQL Server sur 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

Exemple pour SQL Server sur 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

Exemple pour 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

Étapes suivantes