SQL Server と R の使用Working with SQL Server and R

Visual Studio の優れた SQL Server のサポートにより、データ サイエンティストは、SQL クエリを作成して実行する機能およびストアド プロシージャを処理する機能を利用して、R と SQL データベースを使用することができます。Visual Studio's excellent support for SQL Server helps data scientists work with R and SQL databases through the ability to create and run SQL queries and to work with stored procedures.

注意

SQL と R を一緒に使用するためには、SQL Server Tools がインストールされている必要があります。To work with SQL and R together, you must have the SQL Server Data Tools installed:

  • Visual Studio 2017: Visual Studio のインストーラーを実行し、データの保存と処理のワークロードを選択して、SQL Server Data Tools を追加します。Visual Studio 2017: run the Visual Studio installer and select the Data storage and processing workload, which includes SQL Server Data tools.
  • Visual Studio 2015: 「Download SQL Server Data Tools」 (SQL Server Data Tools のダウンロード) にある手順に従ってください。Visual Studio 2015: follow the instructions on Download SQL Server Data Tools.

SQL Server と R の概要については、次のビデオ (3 分 3 秒) をご覧ください。The following video (3m 03s) provides a brief overview of SQL Server and R:

SQL クエリの作成と実行Creating and running SQL Queries

RTVS では R プロジェクトへの SQL クエリの追加がサポートされており、求める結果が得られるまで、別のコンテキストで SQL クエリの反復開発を行うことができます。RTVS supports adding SQL queries into R projects, allowing you to iteratively develop SQL queries in a separate context until you get the results you're looking for.

SQL クエリ ファイルを追加するには、ソリューション エクスプローラーでプロジェクトを右クリックし、[追加] > [新しい項目] を選択して、ファイルの種類として [SQL クエリ] を選択します。To add a SQL query file, right-click the project in Solution Explorer, select Add > New Item..., and select the SQL Query file type:

SQL クエリ項目をプロジェクトに追加

このコマンドにより、Visual Studio の Transact-SQL エディターでファイルが開き、SQL 用のフル機能 IntelliSense とクエリ実行機能を使用できます。This command opens the file in Visual Studio's Transact-SQL editor, which provides full IntelliSense for SQL and the ability to run queries. ただし、これらの機能が動作するためには、エディターのツール バーにある接続ボタンを使用してデータベースに接続するか、クエリの実行を試みる必要があります (Ctrl + Shift + E キーでも選択項目を操作できます)。For these features to work, you need to connect to a database using the connect button in the editor's toolbar or try to run a query (Ctrl+Shift+E, which also works on a selection). どちらの方法でも、次のような接続ダイアログが表示されます。Either way brings up the connection dialog:

SQL 接続ダイアログ ボックス

接続できたら、クエリを実行して結果を確認できます。Once a connection is established, you can run queries and see results:

SQL ウィンドウのクエリ結果

Transact-SQL エディターは他のさまざまな機能をサポートしており、たとえばクエリやクエリ デバッガーの実行プランを表示することなどができます。The Transact-SQL editor supports a variety of other features, such as viewing the execution plan for the query and a query debugger. 詳しくは、「Transact-SQL エディターを使用したスクリプトの編集と実行」をご覧ください。For more information, see Use Transact-SQL Editor to Edit and Execute Scripts.

SQL Server ストアド プロシージャの使用Working with SQL Server stored procedures

SQL Server R Services (SQL Server 2016 以降) では、T-SQL ストアド プロシージャから R コードを埋め込んで実行できます。SQL Server R Services (SQL Server 2016 and later) lets you embed and run R code from a T-SQL stored procedure. R コードを SQL Server コンピューターで実行し、SQL クエリから返されたデータを操作して、SQL 結果セットを生成できます。結果セットは、さらに SQL で処理するか、クライアントに返すことができます。You can run R code on a SQL Server computer, operate on data returned from a SQL query, and generate a SQL result set that can be processed by further SQL or returned to the client.

以下のセクションで説明するように、RTVS は、1 つの SQL ステートメント内で SQL と R コードを組み合わせるという面倒でエラーが発生しやすいプロセスを単純化します。RTVS simplifies the otherwise unwieldy and error-prone process of combining SQL and R code inside a single SQL statement, as described in the following sections:

次のビデオ (6 分 9 秒) でも、これらの機能の概要について説明しています。The following video (6m 09s) also provides an overview of these features:

データベース接続の追加Add a database connection

  1. [R Tools] > [データ] > [データベース接続の追加] を選び、[接続のプロパティ] ダイアログを表示します。Select R Tools > Data > Add Database Connection to bring up the Connection Properties dialog. ここで、データ ソース (この場合は SQL Server) の名前、サーバーの名前、認証モード、データベースの名前を指定します。Here you specify the name of the data source (SQL Server in this case), the name of the server, the authentication mode, and the name of the database. ダイアログ ボックスを閉じる前に [テスト接続] を選んで入力を確認します。Select Test Connection to verify your input before closing the dialog.

    SQL 接続ダイアログ ボックス

  2. 有効な接続で [OK] を選択すると、Visual Studio で新しい settings.R ファイル内に dbConnection という有効な接続が生成されます。Once you select OK with a valid connection, Visual Studio generates a connection string named dbConnection in a new settings.R file. このファイルが RTVS で自動的にソースとして実行され、R スクリプトから接続をすぐに使用できるようになります。RTVS automatically sources (runs) this file, so you can immediately use the connection from R scripts:

SQL 設定の R ファイル

SQL ストアド プロシージャの作成とテストWrite and test a SQL stored procedure

新しい SQL ストアド プロシージャを追加するには、プロジェクトを右クリックして [追加] > [新しい項目] を選択し、テンプレートの一覧から [R を使用した SQL ストアド プロシージャ] を選択して、ファイルに名前 (この例では StoredProcedure.R) を付けた後、[OK] を選択します。To add a new SQL Stored Procedure, right-click your project, select Add > New Item..., select SQL Stored Procedure with R from the list of templates, give the file a name (StoredProcedure.R in this example), and select OK.

RTVS ではストアド プロシージャとして 3 種類のファイルが作成されます。R コードの .R ファイル、SQL コードの .Query.sql ファイル、2 つを組み合わせた .Template.sql ファイルの 3 つです。RTVS creates three files for the stored procedure: a .R file for your R code, a .Query.sql file for the SQL code, and a .Template.sql file that combines the two. 後の 2 つはソリューション エクスプローラーで .R ファイルの子として表示されます。They latter two appear in Solution Explorer as children of the .R file:

R と SQL ストアド プロシージャが表示されたソリューション エクスプローラーの展開ビュー

StoredProcedure.R (この例の場合) に R コードを記述します。StoredProcedure.R (in this example) is where you write R code. 既定の内容は次のとおりです。The default contents are:

# @InputDataSet: input data frame, result of SQL query execution
# @OutputDataSet: data frame to pass back to SQL

# Test code
# library(RODBC)
# channel <- odbcDriverConnect(dbConnection)
# InputDataSet <- sqlQuery(channel, )
# odbcClose(channel)

OutputDataSet <- InputDataSet

簡単に言うと、このコードは InputDataSet という R データフレームを受け取り、その結果を OutputDataSet で返します。テンプレート コードは入力を出力にコピーするだけです。Simply said, the code receives an R dataframe called InputDataSet and returns its results in OutputDataSet, with the template code merely copying the input to the output.

注意

これらのデータフレームの名前は、sp_execute_external_script システム ストアド プロシージャ呼び出しの @input_data_1_name パラメーターと @output_data_1_name パラメーターによって制御されます。The names of these dataframes are controlled by the @input_data_1_name and @output_data_1_name parameters in the call to the sp_execute_external_script system stored procedure. この呼び出し規則の詳細と使用例について詳しくは、「sp_execute_external_script (Transact-SQL)」をご覧ください。For more details on the design of this calling convention and some examples of its usage, see sp_execute_external_script (Transact-SQL).

コメント内に生成されたもう 1 つのコードは、短いテスト スクリプトです。RODBC パッケージを使用して SQL ステートメントを SQL Server に送信し、実行した後、結果セットを R データフレームとして取得します。The other generated code (in comments) provides a small test script that uses the RODBC package to transmit a SQL statement to SQL Server, run it, and retrieve its result set as an R dataframe. このテスト コードをコメント解除し、SQL Server から返された結果セットに作用する R コードを対話形式で記述できます。You can uncomment this test code to interactively write your R code against the result set that you get from SQL Server.

InputDataSet のデータを生成する SQL クエリの作成とテストは、StoredProcedure.Query.sql で行います。StoredProcedure.Query.sql is where you write and test the SQL query that generates the data for InputDataSet. この .sql ファイルでは、エディターが普通の Transact-SQL 機能をすべて提供します。With this .sql file, the editor provides all the usual Transact-SQL features to you.

SQL コードに問題がないことを確認したら、そのコードを StoredProcedure.R の R コードと統合できます。.R ファイルを開いているエディターに .sql ファイルをドラッグします。Once you're happy with your SQL code, integrate it with your R code in StoredProcedure.R by dragging the .sql file onto the open editor for the .R file. 以下の画像は、StoredProcedure.Query.sql を、sqlQuery(channel, )のコンマの後ろの位置にドラッグしたところです。In the image below, StoredProcedure.Query.sql has been dragged to the point after the comma in sqlQuery(channel, ):

R 文字列変数への SQL ファイルの読み込み

このように、この簡単なステップによって R コードが自動的に生成されて .sql ファイルが開きます。このファイルの内容を文字列に読み込み、その文字列を RODBC パッケージに渡して SQL Server に送信します。As you can see, this simple step automatically generates R code to open the .sql file, read its content into a string, and pass it to the RODBC package to send it to SQL Server.

InputDataSet データフレームを必要に応じて処理する R コードを対話形式で記述できます。You can now interactively write R code that manipulates the InputDataSet dataframe as desired. エディター内で R コードを選択し、Ctrl + Enter キーを押すだけで、そのコードを対話型ウィンドウに送信できます。Remember that you can just select R code in the editor and send it to the interactive window by pressing Ctrl+Enter.

StoredProcedure.Template.sql の内容は、最終的に、SQL ストアド プロシージャを生成するためのテンプレートになります。StoredProcedure.Template.sql, finally, contains the template for generating your SQL Stored Procedure:

CREATE PROCEDURE [StoredProcedure]
AS
BEGIN
EXEC sp_execute_external_script @language = N'R'
    , @script = N'_RCODE_'
    , @input_data_1 = N'_INPUT_QUERY_'
--- Edit this line to handle the output data frame.
    WITH RESULT SETS (([MYNEWCOLUMN] NVARCHAR(max)));
END;
  • _RCODE_ プレースホルダーは、StoredProcedure.R の内容によって置き換えられます。The _RCODE_ placeholder is replaced by the contents of StoredProcedure.R.
  • _INPUT_QUERY_ プレースホルダーは、StoredProcedure.Query.sql の内容によって置き換えられます。The _INPUT_QUERY_ placeholder is replaced by the contents of StoredProcedure.Query.sql.
  • ストアド プロシージャから返された結果セットのスキーマを記述するために、WITH RESULT SETS 句を編集します。Edit the WITH RESULT SETS clause to describe the schema of the result set returned from the stored procedure. OutputDataSet データフレームから、ストアド プロシージャの呼び出し元に返す列を明示的に特定します。Specifically identify the columns from the OutputDataSet dataframe that you want to return to the caller of the stored procedure.

たとえば、次のようなクエリがあるとします。For example, for the following query:

SELECT TOP 100 medallion, hack_license FROM nyctaxi_sample

この場合は、次のような WITH RESULT SETS 句を使用して、戻り値のデータ型を指定します。You'd use the following WITH RESULT SETS clause to specify the data types of the return values:

WITH RESULT SETS ((medallion NVARCHAR(max), hack_license NVARCHAR(max)));

SQL ストアド プロシージャの公開Publish a SQL stored procedure

  1. [R Tools] > [データ] > [Publish With Options](オプションを使用してパブリッシュ) メニュー コマンドを選択します。Select the R Tools > Data > Publish With Options... menu command.
  2. 表示されるダイアログ ボックスで、[パブリッシュ先][データベース] に変更し、ターゲットを指定して、[パブリッシュ] を選択します。RTVS でストアド プロシージャがビルドされ、パブリッシュされます。In the dialog that appears, change Publish to: to Database, specify the target, select Publish, and RTVS builds and publishes the stored procedure:

    ストアド プロシージャの公開ダイアログ ボックス

  3. プロジェクト内のすべてのストアド プロシージャを公開する方法として、[R Tools] > [データ] > [ストアド プロシージャの公開] コマンドを使うことができます。このコマンドは、ソリューション エクスプローラーでプロジェクトを右クリックして選ぶこともできます。To publish all stored procedures in a project, you can use the R Tools > Data > Publish Stored Procedures command, which is also available when you right-click the project in Solution Explorer.

ヒント

Visual Studio で SQL Server オブジェクト エクスプローラーを開いている場合、公開したストアド プロシージャはデータベースの [プログラミング] > [ストアド プロシージャ] フォルダーに表示されます。If you have the SQL Server Object Explorer open in Visual Studio, your published stored procedure appears in the Programmability > Stored Procedures folder of your database. また、オブジェクト エクスプローラーから実行することもできます。そのためには、右クリックして [プロシージャの実行] を選ぶか、.sql クエリ ウィンドウから対話形式でプロシージャを呼び出します。You can also run it from the Object Explorer by right-clicking and selecting Execute Procedure, or by calling it interactively from a .sql query window.