Python または R スクリプトからの SQL Server へのループバック接続

  • [アーティクル]

適用対象: SQL Server 2019 (15.x) Azure SQL Managed Instance

sp_execute_external_script から実行される Python または R スクリプトからデータの読み取りまたは書き込みを行うために、ループバック接続と Machine Learning Services を使用して ODBC 経由で SQL Server に接続する方法について説明します。 この方法は、sp_execute_external_script の InputDataSet 引数と OutputDataSet 引数を使用できない場合に使用できます。

接続文字列

ループバック接続を行うには、正しい接続文字列を使用する必要があります。 共通の必須引数として、ODBC ドライバーの名前、サーバーのアドレス、およびデータベースの名前を指定します。

Windows の接続文字列

SQL Server on Windows での認証の場合、Python または R スクリプトで、sp_execute_external_script を実行したのと同じユーザーとして認証するために、Trusted_Connection 接続文字列属性を使用できます。

Windows でのループバック接続文字列の例を次に示します。

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

Linux の接続文字列

SQL Server on Linux での認証の場合、Python または R スクリプトで、sp_execute_external_script を実行したのと同じユーザーとして認証するために、ODBC ドライバーの ClientCertificate 属性と ClientKey 属性を使用する必要があります。 このために、最新の 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 でも固有であり、Python の場合は API rx_get_sql_loopback_connection_string()、R の場合は API rxGetSqlLoopbackConnectionString() を使用して取得できます。

接続文字列の属性について詳しくは、Microsoft ODBC Driver for SQL Server の「DSN と接続文字列のキーワードと属性」を参照してください。

Azure SQL Managed Instance での接続文字列

Azure SQL Managed Instance の接続文字列を生成するには、次のセクションの例を参照してください。 ODBC Driver 11 for SQL Server をループバック接続用の ODBC ドライバーとして使用します。

Python の revoscalepy を使用して接続文字列を生成する

Python スクリプトでループバック接続用の正しい接続文字列を生成するために、revoscalepy の API rx_get_sql_loopback_connection_string () を使用できます。

これは、次の引数を受け取ります。

引数 説明
name_of_database 接続する先のデータベースの名前
odbc_driver ODBC ドライバーの名前

例

SQL Server on 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 on 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

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

R の RevoScaleR を使用して接続文字列を生成する

R スクリプトでループバック接続用の正しい接続文字列を生成するために、RevoScaleR の API rxGetSqlLoopbackConnectionString() を使用できます。

これは、次の引数を受け取ります。

引数 説明
nameOfDatabase 接続する先のデータベースの名前
odbcDriver ODBC ドライバーの名前

例

SQL Server on 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 on 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

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

次のステップ