빠른 시작: SQL 기계 학습과 함께 R을 사용하는 데이터 구조, 데이터 형식 및 개체

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL Managed Instance

이 빠른 시작에서는 SQL Server Machine Learning Services 또는 빅 데이터 클러스터에서 R을 사용할 때 데이터 구조와 데이터 형식을 사용하는 방법을 알아봅니다. R과 SQL Server 사이의 데이터 이동과 일반적으로 발생할 수 있는 문제에 대해 알아봅니다.

미리 알아야 할 일반적인 문제는 다음과 같습니다.

• 데이터 형식이 일치하지 않는 경우
• 암시적 변환이 발생하는 경우
• 캐스팅 및 변환 작업이 필요한 경우
• R 및 SQL이 서로 다른 데이터 개체 사용

필수 조건

이 빠른 시작을 실행하려면 다음과 같은 필수 구성 요소가 필요합니다.

• SQL Server Machine Learning Services. Machine Learning Services를 설치하려면 Windows 설치 가이드 또는 Linux 설치 가이드를 참조하세요. SQL Server 빅 데이터 클러스터에서 Machine Learning Services를 사용하도록 설정할 수도 있습니다.

• R 스크립트가 포함된 SQL 쿼리를 실행하기 위한 도구. 이 빠른 시작에서는 Azure Data Studio를 사용합니다.

항상 데이터 프레임 반환

스크립트가 R에서 SQL Server로 결과를 반환할 때 데이터를 data.frame으로 반환해야 합니다. 스크립트에서 생성하는 다른 형식의 개체는 목록, 요소, 벡터 또는 이진 데이터이든 관계없이 저장 프로시저 결과의 일부로 출력하려면 데이터 프레임으로 변환되어야 합니다. 다행히 다른 개체를 데이터 프레임으로 변경할 수 있도록 지원하는 여러 R 함수가 있습니다. 이진 모델을 serialize하고 데이터 프레임에서 반환할 수 있고 이 작업은 이 빠른 시작의 뒷부분에서 수행하게 됩니다.

먼저 벡터, 행렬 및 목록과 같은 일부 R 기본 R 개체를 실험하고 데이터 프레임으로 변환하면 SQL Server에 전달된 출력이 어떻게 변경되는지 살펴보겠습니다.

R에서 이러한 두 "Hello World" 스크립트를 비교합니다. 스크립트는 거의 동일하게 보이지만 첫 번째 열은 3개의 값으로 된 단일 열을 반환하는 반면, 두 번째 열은 각각 단일 값이 있는 세 개의 열을 반환합니다.

예제 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

예제 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

스키마 및 데이터 형식 식별

결과가 왜 그렇게 다른가요?

대답은 일반적으로 R str() 명령을 사용하여 찾을 수 있습니다. R 스크립트에 str(object_name) 함수를 추가하여 지정된 R 개체의 데이터 스키마가 정보 메시지로 반환되도록 합니다.

예제 1 및 예제 2의 결과가 다른 이유를 확인하려면 다음과 같이 각 문에서 @script 변수 정의의 끝 부분에 str(OutputDataSet) 줄을 삽입합니다.

str 함수가 추가된 예제 1

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

str 함수가 추가된 예제 2

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

이제 메시지의 텍스트를 검토하여 출력이 다른 이유를 확인합니다.

결과 - 예제 1

STDOUT message(s) from external script:
'data.frame':	3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

결과 - 예제 2

STDOUT message(s) from external script:
'data.frame':	1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

보시다시피 R 구문의 약간의 변경은 결과의 스키마에 큰 영향을 미쳤습니다. 그 이유에 대해서는 설명하지 않겠지만 R 데이터 유형의 차이점은 Hadley Wickham의 "Advanced R"에 있는 데이터 구조 섹션에 자세히 설명되어 있습니다.

우선은 R 개체를 데이터 프레임으로 강제 변환할 때 예상 결과를 확인해야 합니다.

팁

또한 is.matrix, is.vector와 같은 R ID 함수를 사용하여 내부 데이터 구조에 대한 정보를 반환할 수 있습니다.

데이터 개체의 암시적 변환

각 R 데이터 개체에는 두 개의 개체에 같은 수의 차원이 포함되거나 데이터 개체에 다른 데이터 형식이 포함될 경우 다른 데이터 개체와 결합될 때 값을 처리하는 방법에 대한 자체 규칙이 있습니다.

먼저 테스트 데이터의 작은 테이블을 만듭니다.

CREATE TABLE RTestData (col1 INT NOT NULL)

INSERT INTO RTestData
VALUES (1);

INSERT INTO RTestData
VALUES (10);

INSERT INTO RTestData
VALUES (100);
GO

예를 들어 다음 문을 실행하여 R을 사용한 행렬 곱셈을 수행한다고 가정합니다. 3개 값이 포함된 단일 열 행렬에 4개 값이 포함된 배열을 곱하고 결과로 4x3 행렬을 예상합니다.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

내부적으로 3개 열이 포함된 열이 단일 열 행렬로 변환됩니다. 행렬은 R에서 특별한 경우인 배열이므로 두 인수가 일치하도록 y는 암시적으로 단일 열 행렬로 강제 변환됩니다.

결과

Col1	Col2	Col3	Col4
12	13	14	15
120	130	140	150
1200	1300	1400	1500

하지만 y 배열의 크기를 변경할 경우 발생하는 상황을 확인하세요.

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

이제 R은 결과로 단일 값을 반환합니다.

결과

Col1
1542

이유는 무엇입니까? 이 경우 두 인수를 동일한 길이의 벡터로 처리할 수 있으므로 R은 내부 제품을 행렬로 반환합니다. 이는 선형 대수의 규칙에 따라 예상되는 동작입니다. 그러나 다운스트림 애플리케이션에서 출력 스키마가 변경되지 않을 것으로 예상되는 경우 문제가 발생할 수 있습니다!

팁

오류가 발생하나요? 마스터 또는 다른 데이터베이스가 아닌 테이블이 포함된 데이터베이스의 컨텍스트에서 저장 프로시저를 실행하고 있는지 확인합니다.

또한 이러한 예제에서는 임시 테이블을 사용하지 않는 것이 좋습니다. 일부 R 클라이언트는 일괄 처리 간의 연결을 종료하고 임시 테이블을 삭제합니다.

길이가 다른 열 병합 또는 곱하기

R은 다양한 크기의 벡터를 사용하고 이러한 열과 유사한 구조를 데이터 프레임에 결합할 수 있는 뛰어난 유연성을 제공합니다. 벡터 목록은 테이블처럼 보일 수 있지만 데이터베이스 테이블에 적용되는 모든 규칙을 따르지는 않습니다.

예를 들어, 다음 스크립트는 길이 6의 숫자 배열을 정의하고 이를 R 변수 df1에 저장합니다. 그런 다음 숫자 배열을 세(3) 개의 값을 포함하는 RTestData 테이블의 정수와 결합하여 새 데이터 프레임인 df2를 만듭니다.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

데이터 프레임을 채우기 위해 R은 배열 df1의 요소 수와 일치하는 데 필요한 만큼 RTestData에서 검색된 요소를 반복합니다.

결과

Col2	Col3
1	1
10	2
100	3
1	4
10	5
100	6

데이터 프레임은 테이블처럼 보일 뿐이며 실제로는 벡터 목록입니다.

데이터 캐스팅 또는 변환

R 및 SQL Server는 동일한 데이터 형식을 사용하지 않으므로 SQL Server에서 쿼리를 실행하여 데이터를 가져와 R 런타임에 전달하는 경우 일반적으로 일부 유형의 암시적 변환이 이루어집니다. R에서 SQL Server로 데이터를 반환할 때 또 다른 일련의 변환이 이루어집니다.

• SQL Server는 쿼리에서 실행 패드 서비스에서 관리하는 R 프로세스로 데이터를 푸시하고 내부 표현으로 변환하여 효율성을 높입니다.
• R 런타임은 데이터를 data.frame 변수에 로드하고 데이터에 대한 자체 작업을 수행합니다.
• 데이터베이스 엔진은 보안 설정된 내부 연결을 통해 데이터를 SQL Server로 반환하고 SQL Server 데이터 형식을 기준으로 데이터를 제공합니다.
• SQL 쿼리를 실행하고 테이블 형식 데이터 집합을 처리할 수 있는 클라이언트 또는 네트워크 라이브러리를 사용하여 SQL Server에 연결하여 데이터를 가져옵니다. 이 클라이언트 애플리케이션은 다른 방식으로 데이터에 영향을 미칠 수 있습니다.

작동 방식을 확인하려면 AdventureWorksDW 데이터 웨어하우스에서 이와 같은 쿼리를 실행합니다. 이 보기는 예측을 만드는 데 사용되는 판매 데이터를 반환합니다.

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

참고 항목

모든 AdventureWorks 버전을 사용하거나 고유 데이터베이스를 사용해서 다른 쿼리를 만들 수 있습니다. 요점은 텍스트, 날짜/시간 및 숫자 값이 포함된 일부 데이터를 처리하는 것입니다.

이제 저장 프로시저에 입력으로 이 쿼리를 붙여넣습니다.

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

오류가 발생하면 쿼리 텍스트를 일부 편집해야 할 수 있습니다. 예를 들어 WHERE 절의 문자열 조건자는 두 세트의 작은따옴표로 묶어야 합니다.

쿼리가 작동하면 str 함수의 결과를 검토하여 R이 입력 데이터를 처리하는 방법을 확인합니다.

결과

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950

• 날짜/시간 열은 R 데이터 형식인 POSIXct를 사용하여 처리되었습니다.
• 텍스트 열 "ProductSeries"는 범주 변수를 의미하는 요소로 식별되었습니다. 문자열 값은 기본적으로 요소로 처리됩니다. 문자열을 R에 전달하면 내부 사용을 위해 정수로 변환된 다음 출력 시 문자열에 다시 매핑됩니다.

요약

이러한 간단한 예제에서도 SQL 쿼리를 입력으로 전달할 때 데이터 변환의 영향을 확인해야 하는 필요성을 확인할 수 있습니다. 일부 SQL Server 데이터 형식은 R에서 지원되지 않으므로 오류를 방지하려면 다음 방법을 고려하세요.

• 데이터를 미리 테스트하고 R 코드에 전달될 때 문제가 될 수 있는 스키마의 열 또는 값을 확인합니다.
• 입력 데이터 원본에서 열을 SELECT *를 사용하지 않고 개별적으로 지정하고 각 열이 처리되는 방식을 파악하세요.
• 입력 데이터를 준비할 때 필요에 따라 명시적 캐스트를 수행하여 놀라움을 방지합니다.
• 오류를 일으키고 모델링에 유용하지 않은 데이터 열 전달(예: GUID 또는 rowguid)을 방지합니다.

지원되는 데이터 형식 및 지원되지 않는 데이터 형식에 대한 자세한 내용은 R 라이브러리 및 데이터 형식을 참조 하세요.

다음 단계

SQL 기계 학습으로 고급 R 함수를 작성하는 방법을 알아보려면 이 빠른 시작을 따르세요.

SQL 기계 학습에서 고급 R 함수 작성