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 Data 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.

다음 비디오(3분 03초)에서는 SQL Server 및 R에 대한 간략한 개요를 제공합니다.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 쿼리 항목 추가

이 명령을 수행하면 전체 SQL용 IntelliSense 및 쿼리 실행 기능을 제공하는 Visual Studio의 Transact-SQL 편집기에서 파일이 열립니다.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. SQL Server 컴퓨터에서 R 코드를 실행하고, 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는 다음 섹션의 설명대로 SQL 및 R 코드를 단일 SQL 문으로 결합하는 불편하고 오류가 발생할 수 있는 프로세스를 간소화합니다.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분 09초)에서는 이러한 기능의 개요를 제공합니다.The following video (6m 09s) also provides an overview of these features:

데이터베이스 연결 추가Add a database connection

  1. R 도구 > 데이터 > 데이터베이스 연결 추가를 선택하여 연결 속성 대화 상자를 표시합니다.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. 올바른 연결과 함께 확인을 선택하면 새 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 Settings.R 파일

SQL 저장 프로시저 작성 및 테스트Write and test a SQL stored procedure

새 SQL 저장 프로시저를 추가하려면 추가 > 새 항목...을 선택하고, 템플릿 목록에서 SQL 저장 프로시저(R 사용)를 선택하고, 파일 이름(이 예제에서는 StoredProcedure.R)을 입력하고, 확인을 클릭합니다.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는 R 코드용 .R 파일, SQL 코드용 .Query.sql 파일, 두 파일을 결합하는 .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. 뒤의 두 파일은 솔루션 탐색기에 .R 파일의 자식으로 표시됩니다.They latter two appear in Solution Explorer as children of the .R file:

SQL 저장 프로시저(R 사용)의 솔루션 탐색기 확장 보기

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).

주석의 다른 생성된 코드는 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.

StoredProcedure.Query.sql에서는 InputDataSet에 대한 데이터를 생성하는 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 코드에 만족할 경우 .sql 파일을 .R 파일에 대해 열린 편집기로 끌어다 놓아 StoredProcedure.R에서 사용자 R 코드와 통합합니다.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.sqlsqlQuery(channel, )의 쉼표 뒤 지점으로 끌었습니다.In the image below, StoredProcedure.Query.sql has been dragged to the point after the comma in sqlQuery(channel, ):

SQL 파일을 R 문자열 값으로 읽기

여기에서 볼 수 있듯이 이 간단한 단계에서는 자동으로 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 도구 > 데이터 > 옵션과 함께 게시... 메뉴 명령을 선택합니다.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 도구 > 데이터 > 저장 프로시저 게시 명령을 사용할 수 있습니다. 이 명령은 솔루션 탐색기에서 프로젝트를 마우스 오른쪽 단추로 클릭할 때도 제공됩니다.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.

SQL Server 개체 탐색기가 Visual Studio에서 열린 경우 데이터베이스의 프로그래밍 기능 > 저장 프로시저 폴더에 게시한 저장 프로시저가 표시됩니다.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.