[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측

지난번 포스팅에서는 Greenplum & PostgreSQL DB에서 MADlib을 활용하여 그룹별 선형회귀모형을 적합 및 예측하는 방법(https://rfriend.tistory.com/533)을 소개하였습니다. 

이번 포스팅에서는 Greenplum & PostgreSQL DB에서 PivotalR 을 사용하여 그룹별 선형회귀모형을 적합 및 예측(fitting and prediction of linear regression models by sex groups with big data using PivotalR on Greenplum DB in parallel)하는 방법을 소개하겠습니다. 

[ Agenda ]

1. PivotalR package 소개

2. web site에서 abalone 데이터셋 가져와서 table 만들기

3. MADlib을 사용하여 DB에서 훈련, 검증 데이터셋 테이블 분할

4. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합

5. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합결과 조회

6. PivotalR을 사용하여 성별 그룹별 선형회귀모형을 이용한 예측 및 모델 성능 평가

1. PivotalR package 소개

[ PivotalR 아키텍처 구조 ]

* soruce: https://cran.r-project.org/web/packages/PivotalR/vignettes/pivotalr.pdf

PivotalR 은 PostgreSQL, Greenplum Database, Apache HAWQ에서 쉽고 빠르게 데이터를 처리하고 분석할 수 있도록 해주는 Apache MADlib Wrapper 툴로서, Pivotal Inc.의 Data Science Team 에서 기여하여 만든 오픈소스 R 패키지입니다.  (PivotalR is a Fast, Easy-to-Use Tool for Manipulating Tables in Databases and a Wrapper of MADlib with contributions from Data Science Team at Pivotal Inc.)

PivotalR 은 PostgreSQL, Greenplum DB, HAWQ 에 접속해서 데이터를 처리하고 통계, 기계학습 알고리즘을 분석할 수 있는 R interface 를 제공하는데요, 이때 사용하는 함수가 native R 함수와 거의 유사하므로 기존 R 사용자 중에서 SQL이나 MADlib에 익숙하지 않는 경우에 기존에 익숙한 R 함수(문법)를 사용할 수 있으므로 쉽게 사용할 수 있는 장점이 있습니다. 

더나아가서, 사용자는 메모리 크기의 한계에 대해서 걱정할 필요없이 분석에만 집중할 수 있습니다. 이는 PivotalR이 PostgreSQL, Greenplum DB로 부터 데이터 이동을 최소화하기 때문에 가능합니다 (매우 중요!!!). 특히 Greenplum DB는 MPP (Massively Parallel Processing) 아키텍처의 DB로서 수 테라 ~ 페타바이트급의 데이터를 분산병렬처리/ 분석하는데 R 사용자가 PivotalR을 사용하면 유용합니다. PivotalR 에서 R 함수로 통계, 기계학습 함수 코드를 짜서 실행하면 Greenplum DB 안에서는 SQL로 코드가 변환되어 MADlib 함수가 실행되고, 결과가 DB에 테이블로 저장이 됩니다. 

2. web site에서 abalone 데이터셋 가져와서 table 만들기

UC Irvine Machine Learning Repository 에 공개된 abalone 데이터셋을 가져와서 public schema에 external table을 만들고, 이로부터 성(sex)별 칼럼을 기준으로 분산해서 저장하여 테이블을 만들어보겠습니다 (Greenplum DB 기준). 별로 어렵거나 특별한 것은 없으므로 추가 설명은 생략합니다.

아래 코드는 DBeaver 에서 SQL 로 작성한 것이며, 맥북에 Docker image로 만든 Greenplum DB 를 실행시켜 수행한 것입니다. (분석 환경 설정 참고: https://rfriend.tistory.com/379) 

-- Dataset for example: abalone dataset from the UC Irvine Machine Learning Repository -- url: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data -- Create an external web table DROP EXTERNAL TABLE IF EXISTS abalone_external; CREATE EXTERNAL WEB TABLE abalone_external( sex text  , length float8 , diameter float8 , height float8 , whole_weight float8 , shucked_weight float8 , viscera_weight float8 , shell_weight float8 , rings integer -- target variable to predict ) LOCATION('http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data')  FORMAT 'CSV'  (null as '?'); -- Create a table of abalone DROP TABLE IF EXISTS abalone; CREATE TABLE abalone AS  SELECT * FROM abalone_external DISTRIBUTED BY (sex); -- Viewing data distribution SELECT gp_segment_id, COUNT(*) AS row_cnt FROM abalone GROUP BY gp_segment_id; -- Check data SELECT * FROM abalone LIMIT 5; SELECT sex, COUNT(*) FROM abalone GROUP BY sex;

3. MADlib을 사용하여 DB에서 훈련, 검증 데이터셋 테이블 분할

이번 예제에서는 PivotalR 을 사용하여 간단하게 성(sex)별로 shucked_weight와 diameter 설명변수를 사용하여 rings 를 예측하는 다중 선형회귀모형을 적합하고, 예측하는 것입니다. (PivotalR은 MADlib wrapper 로서 GPDB와 MADlib을 interface 해주며, 실제 분석 수행은 MADlib이 수행함)

이를 위해 먼저 training set : test set = 0.8 : 0.2 의 비율로 데이터셋을 분할하겠습니다. madlib.train_test_split() 함수를 사용하며, 아래처럼 SQL의 select 문 안에 순서대로 인자를 써주면 됩니다. 이때 '성(sex)' 별을 기준으로 층화임의추출(stratified random sampling)을 해주었으며, 비복원 추출 (sample with replacement = FALSE 로 설정) 을 하려고 합니다. Output table 이름에 'out'이라고 해주었으며, Separate output tables = TRUE 로 설정하여 train과 test 테이블을 별도로 구분해서 만들어주라고 함에 따라 'out_train', 'out_test' 라는 이름으로 자동으로 naming 되어 두개의 테이블이 생성이 됩니다. 

out_train, out_test 의 각 테이블별로 성별(sex)로 관측치 개수를 세어보니 0.8 : 0.2 의 비율로 성(sex) 별 층화추출이 잘 되었네요. 

-- Train, Test set split DROP TABLE IF EXISTS out_train, out_test; SELECT madlib.train_test_split( 'abalone',    -- Source table 'out',         -- Output table         0.8,           -- train_proportion         NULL,        -- Default = 1 - train_proportion = 0.5         'sex',          -- Strata definition         'rings, shucked_weight, diameter', -- Columns to output         FALSE,       -- Sample with replacement         TRUE);       -- Separate output tables -- Check SELECT * FROM out_train LIMIT 5; SELECT sex, count(*) FROM out_train GROUP BY sex; SELECT sex, count(*) FROM out_test GROUP BY sex;

4. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합

4번부터 6번까지는 RStudio에서 R 언어로 코드를 짠 것이며, 데이터는 Greenplum DB에 있고 데이터 처리 및 분석도 Greenplum DB에서 MADlib 으로 실제 수행이 됩니다. (Greenplum DB로 부터 로컬 PC로의 데이터 이동 없음. R을 알고 있으면 여기서부터는 SQL은 몰라도 됨)

만약 PivotalR 패키지를 설치하지 않은 사용자라면 install.packages("PivotalR") 명령문을 실행해서 설치해주기 바랍니다. (Greenplum, PostgreSQL DB에도 미리 설치가 되어 있어야 합니다)

먼저 로컬 PC의 RStudio에서 PivotalR 패키지를 로딩하고, Greenplum (혹은 PostgreSQL) DB에 db.connect() 함수를 이용해서 연결해보겠습니다. (dbname, host, port, user, password 는 각자의 정보를 입력해주세요. DB는 미리 start 해놓은 상태여야 합니다.). 

# install and load PivotalR package install.packages("PivotalR") library(PivotalR) # Connect to Greenplum DB # -- set with yours db.connect(dbname = "gpadmin",             host = "localhost",             port = 5432,             user = "gpadmin",             password = "pivotal") Created a connection to database with ID 1  [1] 1

DB connection 정보는 db.list() 함수로 알 수 있습니다. [Connection ID 1] 처럼 DB 연결 정보는 나중에 db.data.frame() 함수로 테이블에 접근할 때 필요하므로 알아두어야 합니다. 

Greenplum 테이블 중에 특정 단어 (이번 예에서는 'abalone' 단어)가 들어간 모든 테이블(tables)이나 뷰(views)를 db.objects('abalone') 함수를 사용해서 나열해볼 수 있습니다. (SQL 로 information_schema 에서 테이블 이름 조회하는 것과 유사)

# List Database connection infomation db.list() Database Connection Info ## ------------------------------- [Connection ID 1] Host     :    localhost User     :    gpadmin Database :    gpadmin DBMS     :    Greenplum 5  MADlib   :    installed in schema madlib  # List all tables/views that has 'abalone' in the name db.objects('abalone') [1] "public.abalone"                 "public.abalone_external"

PivotalR의 db.data.frame() 함수를 이용하면 데이터는 Greenplum DB에 있고 로컬 PC로 이동하지 않은 상태에서 DB connect하여 마치 R의 DataFrame 을 만든 것처럼 R의 함수를 사용할 수 있습니다. 이때 위의 db.list() 함수로 조회해서 알았던 conn.id 를 지정해주어야 합니다 (이번 예에서는 conn.id = 1). 

R DataFrame에 사용할 수 있는 간단한 함수로 dimension을 조회하는 R dim() 함수, 칼럼 이름을 조회하는 names() 함수를 예로 들어보았습니다. 

# Connect to a table via db.data.frame function # --** Note that the data remains in the database and is not loaded into memory **-- abalone <- db.data.frame("public.abalone", # table name                          conn.id = 1) # refer to the results of 'db.list()' above Counting and caching the data table dimension ... 0.067 sec ... done. An R object pointing to "public"."abalone" in connection 1 is created ! # dimension (4177, 9) dim(abalone) [1] 4177    9 # column names names(abalone)  [1] "sex"            "length"         "diameter"       "height"         "whole_weight"   "shucked_weight" [7] "viscera_weight" "shell_weight"   "rings"

특정 변수의 값을 조회하는 lookat() 함수도 데이터 구조, 형태를 파악하는데 전체 데이터를 다 안불러오고 일부만 가져와서 RStudio 화면에 뿌려주므로 초반에 탐색적 데이터 분석할 때 종종 사용합니다. (select * form abalone order by sex limit 8;)

lookat(abalone$rings, 8) [1] 15  7 10  7  8  9 10 11 > lookat(sort(abalone, decreasing = FALSE, c(abalone$sex, abalone$rings)), 8)   sex length diameter height whole_weight shucked_weight viscera_weight shell_weight rings 1   F  0.290    0.225  0.075       0.1400         0.0515         0.0235       0.0400     5 2   F  0.360    0.270  0.090       0.1885         0.0845         0.0385       0.0550     5 3   F  0.275    0.195  0.070       0.0800         0.0310         0.0215       0.0250     5 4   F  0.370    0.275  0.085       0.2405         0.1040         0.0535       0.0700     5 5   F  0.445    0.335  0.110       0.4355         0.2025         0.1095       0.1195     6 6   F  0.595    0.475  0.160       1.1405         0.5470         0.2310       0.2710     6 7   F  0.475    0.360  0.120       0.5915         0.3245         0.1100       0.1270     6 8   F  0.345    0.250  0.090       0.2030         0.0780         0.0590       0.0550     6

이제 이 포스팅의 주인공인 '성별('sex') 그룹별로 shucked_weight와 diameter 변수를 사용해서 rings를 추정하는 다중 선형회귀모형을 적합하는 모형을 PivotalR의 madlib.lm() 함수를 사용하여 Greenplum DB에서 분산병렬처리로 적합시켜 보겠습니다. (Greenplum DB에서는 실제는 MADlib의 madlib.linregr_train() 함수가 수행됨) 

아래 코드에서 처럼 madlib.lm(rings ~ shucked_weight + diameter | sex, data = out_train) 함수 안의 syntax 가 R의 선형회귀모형을 적합할 때 사용하는 lm() 함수와 문법이 똑같습니다!  R을 이미 능숙하게 사용하는 사용자라면 아주 익숙한 syntax 일 것입니다. 

성별('sex') 그룹별로 선형회귀모형을 구분해서 적합하고 싶을 때는 '| sex' 처럼 madlib.lm() 함수 안에서 수직바('|')로 group operator 변수를 지정해주면 되므로 무척 편리합니다. (만약 120개 국가가 있는 country group 별로 선형회귀모형을 각각 적합시켜야 할 경우, 120개 국가별 madlib.lm() 함수를 나열하는 것이 아니라 코드 1줄로 하고 '| country_code' 만 추가시켜주면 됨)  Greenplum DB 에서는 MADlib이 알아서 최적화해서 분산병렬처리해서 학습을 시켜줍니다.(대용량 데이터에 대해 1개 모형을 적합하든, 아니면 여러개의 그룹별로 구분해서 모형을 적합하든 분산병렬처리 해줌)

# DB connect to training set table out_train <- db.data.frame("public.out_train", # table name                          conn.id = 1) # refer to the results of 'db.list()' above Counting and caching the data table dimension ... 0.073 sec ... done. An R object pointing to "public"."out_train" in connection 1 is created ! # Fit one different model to each group('grp') model_1 <- madlib.lm(rings ~ shucked_weight + diameter | sex,                             data = out_train)

만약 설명변수 중 특정 변수 (예: 'id') 변수만 빼고 나머지 설명변수는 모두 사용해서 선형회귀모형을 적합하고 싶다면 model_2 <- madlib.lm(rings ~ . - id | sex, data = out_train) 처럼 해주면 됩니다. 기존 R과 함수가 매우 유사함을 알 수 있습니다. 

5. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합결과 조회

summary(model name) 함수를 사용해서 'sex' 그룹 ('F', 'M', 'I') 별로 적합된 결과 (회귀계수, 표준오차, t통계량, P-value 등) 를 확인해볼 수 있습니다. 너무 쉽지 않나요?!!!

# Display the fitted results summary(model_1) MADlib Linear Regression Result Call: madlib.lm(formula = rings ~ shucked_weight + diameter | sex,      data = out_train) The data is divided into 3 groups --------------------------------------- Group 1 when sex: I Coefficients:                Estimate Std. Error t value Pr(>|t|)     (Intercept)      1.2629     0.3181    3.97 7.67e-05 *** shucked_weight  -0.4197     0.9989   -0.42    0.674     diameter        20.5929     1.4472   14.23  < 2e-16 *** --- Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1 R-squared: 0.4811355  Condition Number: 33.77605  --------------------------------------- Group 2 when sex: F Coefficients:                Estimate Std. Error t value Pr(>|t|)     (Intercept)     -0.7974     0.8372  -0.952    0.341     shucked_weight  -9.8574     0.9393 -10.495   <2e-16 *** diameter        35.8450     2.5893  13.844   <2e-16 *** --- Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1 R-squared: 0.1642253  Condition Number: 38.90999  --------------------------------------- Group 3 when sex: M Coefficients:                Estimate Std. Error t value Pr(>|t|)     (Intercept)      0.5520     0.5748   0.960    0.337     shucked_weight  -6.6548     0.7085  -9.393   <2e-16 *** diameter        29.6759     1.8586  15.966   <2e-16 *** --- Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1 R-squared: 0.2166009  Condition Number: 30.90036

적합된 모형 객체에 대해서 groups() 함수를 사용하면 모형을 적합할 때 사용했던 '그룹 변수'와 '요인 수준 (factor level)'을 조회할 수 있습니다. 

그리고 적합된 모델 객체는 리스트 (list) 형태로 되어 있으므로 리스트의 indexing 하는 방법대로 특정 그룹의 모델 적합 정보만 선별해서 조회할 수도 있습니다. (아래 예는 두번째 그룹인 'F'(암컷) 회귀모형 적합 결과 indexing 했음)

# Groups information groups(model_1) $sex [1] "I" "F" "M" # Select the 2nd Group's Model model_1[[2]] MADlib Linear Regression Result Call: madlib.lm(formula = rings ~ shucked_weight + diameter | sex,      data = out_train) --------------------------------------- sex: F Coefficients:                Estimate Std. Error t value Pr(>|t|)     (Intercept)     -0.7974     0.8372  -0.952    0.341     shucked_weight  -9.8574     0.9393 -10.495   <2e-16 *** diameter        35.8450     2.5893  13.844   <2e-16 *** --- Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1 R-squared: 0.1642253  Condition Number: 38.90999

6. PivotalR을 사용하여 성별 그룹별 선형회귀모형을 이용한 예측 및 모델 성능 평가

5번에서 적합한 성별('sex') 그룹별 선형회귀모형을 사용하여 3번에서 분할한 test-set table ('out_test') 을 대상으로 rings를 예측(추정)해보겠습니다. 그냥 간단하게 predict(model_name, newdata) 함수를 사용하면 끝입니다! 

아래 예에서는 db.data.frame() 함수를 써서 먼저 "public.out_test" 의 test-set 테이블을 먼저 out_test 로 연결해놓고 (데이터는 Greenplum DB에 있으며, 데이터 이동 없음), 그 다음에 predict() 함수로 그룹별로 예측을 하는데요, 결과를 실제와 예측값을 비교해서 보기에 편하도록 cbind() 로 묶은 다음에 as.db.data.frame() 으로 DataFrame 처럼 만든 다음에 (데이터는 DB에 있음), lookat() 함수로 비로서 DB 테이블 안의 앞의 10개 데이터만 가져와서 RStudio에 결과를 보여주었습니다. 

# DB connect to test-set table out_test <- db.data.frame("public.out_test", # table name                            conn.id = 1) # connection ID # Prediction on test set pred_test <- as.db.data.frame(cbind(out_test$rings, predict(model_1, newdata=out_test))) Counting and caching the data table dimension ... 0.075 sec ... done. An R object pointing to "madlib_temp_1ef5b482_2a87_a0afaa_5b83c16e4d72" in connection 1 is created ! The data created by cbind(out_test$rings, predict(model_1, out_test)) is stored into pg_temp_8."madlib_temp_1ef5b482_2a87_a0afaa_5b83c16e4d72" in database gpadmin on localhost ! lookat(pred_test, 10)    rings madlib_predict 1      7       8.660894 2     18      11.476131 3     12      12.156485 4     14      12.366110 5     15      12.000829 6     17      10.023336 7      4       6.978186 8     15      12.026095 9      9      10.541055 10    15      11.709994

이번에는 성별('sex') 그룹 중에서 'F' (암컷) 전복에 대해서만 예측하고, 무작위로 100개만 표본 추출하여 '실제값("rings") vs. 예측값("madlib_predict")' 을 산점도로 시각화하여 비교를 해보겠습니다. (이 예에서는 DB에 수백만~수억개의 row 를 가진 대용량 데이터가 Greenplum DB에 들어있고, RStudio 로는 이중에서 일부인 100개만 임의추출하여 가져와서 시각화하는 상황이라고 가정하였음) 

# Prediction result on 'F' sex group only pred_test_F <- cbind(out_test$rings[out_test$sex == "F"],                       predict(model_1[[2]], # model for sex 'F' group                              out_test[out_test$sex == "F",]) # newdata for sex 'F' group                      ) lookat(pred_test_F, 10)    rings madlib_predict 1      3       3.115230 2     11       8.997374 3      4       4.344511 4      6       6.880858 5      7       6.790274 6      7       7.093502 7      7       6.590010 8      9       7.395052 9      5       5.161094 10     7       7.389806 # plot a random sample plot(lookat(sort(pred_test_F, FALSE, NULL), 100),       main="Actual vs. Predicted on 'F' sex group (100 samples)")

마지막으로, 선형회귀모형의 적합도(goodness of fit)를 평가할 수 있는 여러개의 통계량 지표들 중에서 Mean Squared Error ( = mean((actual_value - predicted_value)^2)) 를 (a) 3개 그룹 모두에 대해서 계산 (예측은 각 그룹별 모델로 적용, MSE 계산에는 그룹 구분 없이 모두 합쳐서 계산), (b) 'F' 성별 그룹에 대해서만 MSE 를 계산해보겠습니다. 

# Mean Squared Error lookat(mean((out_test$rings - predict(model_1, newdata = out_test))^2)) [1] 6.431776 # Mean Squared Error of 'F' sex group lookat(mean((out_test$rings[out_test$sex == "F"] # actual              - predict(model_1[[2]], out_test[out_test$sex == "F",]) # predicted              )^2)) # mean of squared error [1] 9.275403

지금까지 PivotalR 패키지를 사용하여 Greenplum, PostgreSQL DB에서 선형회귀모형을 적합, 예측하는 예를 들어보았습니다. MADlib 1.8.x 버전을 기준으로 했을 때 PivotalR은 현재 아래와 같이 선형회귀모델 외에 다양한 통계, 기계학습 알고리즘을 지원하고 있습니다. 

[ PivotalR references ]

• Github

    https://github.com/pivotalsoftware/PivotalR

• CRAN package

    https://cran.r-project.org/web/packages/PivotalR/index.html

    http://cran.r-project.org/web/packages/PivotalR/PivotalR.pdf

• Pivotal blog posts

    http://blog.pivotal.io/pivotal/products/introducing-r-for-big-data-with-pivotalr

    http://blog.pivotal.io/pivotal/products/how-to-20-minute-guide-to-get-started-with-pivotalr

• Technical paper

    http://cran.r-project.org/web/packages/PivotalR/vignettes/pivotalr.pdf

• Confluence Wiki

    https://cwiki.apache.org/confluence/display/MADLIB/PivotalR

많은 도움이 되었기를 바랍니다. 

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요.