[MSR] TSQL - sp_execute_external_script를 사용하여 R 코드 사용

/*******************************************************************************************************************
-- Title : [MSR] TSQL - sp_execute_external_script를 사용하여 R 코드 사용
-- Reference : microsoft.com
-- Key word : microsoft r sp_execute_external_script result sets outputdataset inputdataset 데이터프레임
                  data.frame dataframe glm utils rnorm serialize iris naivebayes 
*******************************************************************************************************************/

-- SQL

------------------------------
-- Create table & Insert data
------------------------------
CREATE TABLE #ttt 
( id int 
, nm nvarchar(10)
, age int
);
 
insert into #ttt
select 1, 'aaa', 11 union all
select 2, 'bbb', 22 union all
select 3, 'ccc', 33;
 
create table #mydata ([col1] int not null);
    
insert into #mydata   values (1);    
insert into #mydata   values (10);    
insert into #mydata   values (100);    
 
select * from #mydata;
select * from #ttt;
 
 
------------------------------
-- 기본 구조
------------------------------
-- @language : 호출할 언어 확장(이 경우 R)을 정의.
-- @script : R 런타임에 유니코드 텍스트로 전달할 명령을 정의.
--           nvarchar 형식의 변수에 텍스트를 추가한 다음 변수를 호출할 수도 있음.
exec sp_execute_external_script  
  @language =N'R'
, @script=N'OutputDataSet<-InputDataSet'
, @input_data_1 =N'select 1 as hello'    
with result sets (([hello] int not null));    
/*
hello
1
*/
 
------------------------------
-- OutputDataset, InputDataSet 이용
------------------------------
-- N'OutputDataSet <- InputDataSet;' : 기본 변수 이름 InputDataSet에 포함된 입력 데이터를 R에 전달한 다음 추가 작업 없이 결과로 다시 전달. 
--                                     R은 대/소문자를 구분하므로 입력 및 출력 변수 이름 둘 다에서 올바른 대/소문자를 사용(if not, 오류 발생).
exec sp_execute_external_script  
  @language =N'R'
, @script=N'OutputDataSet<-InputDataSet'
, @input_data_1 =N'select id, nm from #ttt'    
with result sets 
(([ID] int not null, [NAME] nvarchar(10) null)); 
/*
ID   NAME
1    aaa
2    bbb
3    ccc
*/
 
------------------------------
-- OutputDataset, InputDataSet의 대체
------------------------------
execute sp_execute_external_script    
  @language = N'R'    
, @script = N' SQLOut <- SQLIn2;'   
, @input_data_1 = N'SELECT id, nm from #ttt;'    
, @input_data_1_name  = N'SQLIn2'    
, @output_data_1_name =  N'SQLOut'    
WITH RESULT SETS 
(([ID] int not null, [NAME] nvarchar(10) null));  
/*
ID   NAME
1    aaa
2    bbb
3    ccc
*/
 
------------------------------
-- data.frame 활용
------------------------------
execute sp_execute_external_script    
  @language = N'R'    
, @script = N'mytextvariable <- c("hello", " ", "world");    
              OutputDataSet <- as.data.frame(mytextvariable);'    
, @input_data_1 = N' SELECT 1 as Temp1'    
WITH RESULT SETS 
(([COL] char(20) NOT NULL));
/*
COL
hello
     
world
*/
 
execute sp_execute_external_script    
  @language = N'R'    
, @script = N' OutputDataSet<- data.frame(c("hello", "hello2"), c("dd","dd2"), c("world", "world2"));'    
, @input_data_1 = N''    
WITH RESULT SETS 
(([COL1] varchar(20), [COL2] char(2), [COL3] varchar(20))); 
/*
COL1    COL2    COL3
hello   dd      world
hello2  dd      world2
*/
 
------------------------------
-- 행렬의 활용
------------------------------
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 #MyData;'    
WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int)); 
/*
Col1    Col2    Col3    Col4
12      13      14      15
120     130     140     150
1200    1300    1400    1500
*/
 
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 #MyData;'    
WITH RESULT SETS (([Col1] int ));  
/*
Col1
1542
*/
 
------------------------------
-- 길이가 다른 열 병합 또는 곱하기
------------------------------
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 #MyData;'    
WITH RESULT SETS (([Col2] int not null, [Col3] int not null));
/*
Col2   Col3
1      1
10     2
100    3
1      4
10     5
100    6
*/
 
------------------------------
-- 데이터 형식 식별 및 스키마 확인
------------------------------
execute sp_execute_external_script    
  @language = N'R'    
, @script = N'str(InputDataSet);'    
, @input_data_1 = N' SELECT * FROM #ttt;'    
WITH RESULT SETS undefined;   
/*
외부 스크립트의 STDOUT 메시지: 
'data.frame':    3 obs. of  3 variables:
 $ id : int  1 2 3
 $ nm : Factor w/ 3 levels "aaa","bbb","ccc": 1 2 3
 $ age: int  11 22 33
*/
 
------------------------------
-- 열 캐스트 또는 변환
------------------------------
execute sp_execute_external_script    
  @language = N'R'    
, @script = N'str(InputDataSet);
              print("-----------------");
              print(class(InputDataSet));'
, @input_data_1 = N'SELECT ReportingDate    
                         , CAST(ModelRegion as varchar(50)) as ProductSeries    
                         , Amount    
                    FROM [AdventureworksDW2016CTP3].[dbo].[vTimeSeries]     
                    WHERE [ModelRegion] = ''M200 Europe''    
                    ORDER BY ReportingDate ASC ;'    
WITH RESULT SETS undefined;  
/*
외부 스크립트의 STDOUT 메시지: 
'data.frame':    37 obs. of  3 variables:
 $ ReportingDate: POSIXct, format: "2010-12-25" "2011-01-25" ...
 $ ProductSeries: Factor w/ 1 level "M200 Europe": 1 1 1 1 1 1 1 1 1 1 ...
 $ Amount       : num  3400 16925 20350 16950 16950 ...
[1] "-----------------"
[1] "data.frame"
*/
 
------------------------------
-- 여러 입력 사용(에러 발생)
------------------------------
execute sp_execute_external_script    
  @language = N'R' 
, @script = N' 
    library(RODBC);
    conn_db_str <- odbcConnect("R_DSN", uid = "usr_dbrang", pwd = "elqlfkd");
    df_data = sqlQuery(conn_db_str, "SELECT TOP 10 ProductID, [Name], ProductNumber FROM [Production].[Product]");
    OutputDataSet <- df_data;    '
WITH RESULT SETS (([ID] int not null, [NAME] nvarchar(100) not null, [NUMBER] nvarchar(100) not null));
 
 
------------------------------
-- 난수 생성
------------------------------
EXEC sp_execute_external_script    
  @language = N'R'    
, @script = N'OutputDataSet <- as.data.frame(rnorm(20, mean = 100));'    
, @input_data_1 = N''    
WITH RESULT SETS (([Density] float NOT NULL));    
/*
Density
99.0307953248394
98.4721915458655
99.2037859880915
...
*/
 
------------------------------
-- 난수 프로시저 생성
------------------------------
CREATE PROC up_MyRNorm (@mynorm int, @mymean int)
AS
    EXEC sp_execute_external_script    
      @language = N'R'    
    , @script = N'OutputDataSet <- as.data.frame(rnorm(mynorm, mymean));'    
    , @input_data_1 = N'   ;' 
    , @params = N' @mynorm int, @mymean int'  
    , @mynorm = @mynorm
    , @mymean = @mymean
    WITH RESULT SETS (([Density] float NOT NULL));    
go
 
exec up_MyRNorm @mynorm = 20,@mymean = 100;
 
------------------------------
-- R 유틸리티 함수의 추가 용도
------------------------------
execute sp_execute_external_script    
  @language = N'R'    
, @script = N'library(utils);    
              mymemory <- memory.limit();    
              OutputDataSet <- as.data.frame(mymemory);'    
, @input_data_1 = N' ;'    
WITH RESULT SETS (([Col1] int not null));    
 
execute sp_execute_external_script
  @language = N'R'
, @script = N'localmax <- .Machine$integer.max;
              OutputDataSet <- as.data.frame(localmax);'
, @input_data_1 = N' ;'
WITH RESULT SETS (([MaxIntValue] int not null));
 
------------------------------
-- OUTPUT 변수 리턴
------------------------------
DECLARE @model varbinary(max);  
 
EXEC sp_execute_external_script  
  @language = N'R'  
, @script = N'  
  # build classification model to predict tipped or not  
  logitObj <- glm(tipped ~ passenger_count + trip_distance + trip_time_in_secs + direct_distance, 
                  data = featureDataSource, family = binomial(link=logit));  
  
  # First, serialize a model and put it into a database table  
  modelbin <- serialize(logitObj, NULL); '  
, @input_data_1 = N'  
  SELECT top 100 tipped, passenger_count, trip_time_in_secs, trip_distance
       , dbo.fnCalculateDistance(pickup_latitude, pickup_longitude, dropoff_latitude, dropoff_longitude) "direct_distance"
  FROM dbo.nyctaxi_sample TABLESAMPLE (1 PERCENT) REPEATABLE (98074);'  
, @input_data_1_name = N'featureDataSource'  
, @params = N'@modelbin varbinary(max) OUTPUT'  
, @modelbin = @model OUTPUT;  
 
select @model;
 
------------------------------
-- R Dataframe의 SQL 리턴
------------------------------
DROP PROC IF EXISTS up_get_iris_dataset;  
go
 
CREATE PROC up_get_iris_dataset  
AS  
BEGIN  
    EXEC sp_execute_external_script  
      @language = N'R'  
    , @script = N'iris_data <- iris;'  
    , @input_data_1 = N''  
    , @output_data_1_name = N'iris_data'  
    WITH RESULT SETS 
    (( "Sepal.Length" float not null
     , "Sepal.Width" float not null
     , "Petal.Length" float not null
     , "Petal.Width" float not null
     , "Species" varchar(100)
    ));  
END; 
 
exec  up_get_iris_dataset;
 
------------------------------
-- DB 데이터셋 기반 모델 생성(에러발생)
------------------------------
DROP PROC IF EXISTS up_generate_iris_model;  
go  
  
CREATE PROC up_generate_iris_model  
AS  
BEGIN  
    EXEC sp_execute_external_script  
          @language = N'R'  
        , @script = N'  
          library(e1071);  

          irismodel <- naiveBayes(iris_data[,1:4], iris_data[,5]);  
          trained_model <- data.frame(payload = as.raw(serialize(irismodel, connection=NULL)));'  
        , @input_data_1 = N'select Sepal_Length, Sepal_Width, Petal_Length, Petal_Width, Species from iris_data'  
        , @input_data_1_name = N'iris_data'  
        , @output_data_1_name = N'trained_model'  
    WITH RESULT SETS ((model varbinary(max)));  
END;  
 
exec up_generate_iris_model;
 
------------------------------
-- OUTPUT 활용한 결과 리턴-1
------------------------------
USE WideWorldImporters;
GO
 
DECLARE @F_Value VARCHAR(1000);
DECLARE @Signif VARCHAR(1000);
 
EXECUTE sp_execute_external_script
  @language = N'R'
, @script = N'
  mytable <- table(WWI_OrdersPerCustomer$CustomerID, WWI_OrdersPerCustomer$Nof_Orders) 
  data.frame(margin.table(mytable, 2))
  Ch <- unlist(chisq.test(mytable))
  F_Val <- as.character(Ch[1])
  Sig <- as.character(Ch[3])'
, @input_data_1 = N'
  select TOP 10 CustomerID, count(*) as Nof_Orders 
  from [Sales].[Orders] GROUP BY CustomerID'
, @input_data_1_name = N'WWI_OrdersPerCustomer'
, @params = N'@F_Val VARCHAR(1000) OUTPUT, @Sig VARCHAR(1000) OUTPUT'
, @F_Val = @F_Value OUTPUT
, @Sig = @Signif OUTPUT;
 
SELECT @F_Value AS CHI_Value
     , @Signif AS CHI_Square_SIGNIFICANCE;
GO
 
------------------------------
-- OUTPUT 활용한 결과 리턴-2
------------------------------
USE WideWorldImporters;
GO
DECLARE @F_Value VARCHAR(1000);
DECLARE @Signif VARCHAR(1000);
 
EXECUTE sp_execute_external_script
  @language = N'R'
, @script = N'
  mytable <- table(WWI_OrdersPerCustomer$CustomerID, WWI_OrdersPerCustomer$Nof_Orders) 
  data.frame(margin.table(mytable, 2))
  Ch <- unlist(chisq.test(mytable))
  F_Val <- as.character(Ch[1])
  Sig <- as.character(Ch[3])
  OutputDataSet<-data.frame(margin.table(mytable, 2))'
, @input_data_1 = N'
  select TOP 10 CustomerID, count(*) as Nof_Orders 
  from [Sales].[Orders] GROUP BY CustomerID'
, @input_data_1_name = N'WWI_OrdersPerCustomer'
, @params = N' @F_Val VARCHAR(1000) OUTPUT, @Sig VARCHAR(1000) OUTPUT'
, @F_Val = @F_Value OUTPUT 
, @Sig = @Signif OUTPUT
WITH RESULT SETS
((Cust_data INT, Freq INT));
 
SELECT @F_Value AS CHI_Value
     , @Signif AS CHI_Square_SIGNIFICANCE;