지난번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL과 Apache MADlib을 활용해서 대용량의 연속형 데이터에 대한 In-DB 상관관계 분석(Correlation Analysis in Database)에 대해서 알아보았습니다.

이번 포스팅에서는 상관관계 분석에서 한발 더 나아가서, 설명변수(독립변수) X와 목표변수(종속변수) Y 간의 선형/인과관계를 모델링하는 선형 회귀모형(Linear Regression)을 훈련(train)하고 예측(predict)하는 방법을 소개하겠습니다.

PostgreSQL, Greenplum database에서 대용량 데이터에 대해
(1) SQL로 선형 회귀모형 적합하고 모수 확인하기
(2) SQL로 그룹별로 선형 회귀모형 적합하고 예측하기
(3) Apache MADlib으로 다중 선형 회귀모형 적합하기
(4) Apache MADlib으로 그룹별로 다중 선형 회귀모형 적합하고 예측하기




먼저, 예제로 사용할 데이터로 4개의 연속형 데이터('sepal_length', 'sepal_width', 'petal_length', 'petal_width')와 1개의 범주형 데이터('class_name')를 가진 iris 데이터셋으로 테이블을 만들어보겠습니다.



-- Iris data table
DROP TABLE IF EXISTS iris;
CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,
                    petal_length FLOAT, petal_width FLOAT,
                   class_name text);
INSERT INTO iris VALUES
(1,5.1,3.5,1.4,0.2,'Iris-setosa'),
(2,4.9,3.0,1.4,0.2,'Iris-setosa'),
(3,4.7,3.2,1.3,0.2,'Iris-setosa'),
(4,4.6,3.1,1.5,0.2,'Iris-setosa'),
(5,5.0,3.6,1.4,0.2,'Iris-setosa'),
(6,5.4,3.9,1.7,0.4,'Iris-setosa'),
(7,4.6,3.4,1.4,0.3,'Iris-setosa'),
(8,5.0,3.4,1.5,0.2,'Iris-setosa'),
(9,4.4,2.9,1.4,0.2,'Iris-setosa'),
(10,4.9,3.1,1.5,0.1,'Iris-setosa'),
(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),
(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),
(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),
(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),
(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),
(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),
(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),
(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),
(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),
(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),
(21,6.3,3.3,6.0,2.5,'Iris-virginica'),
(22,5.8,2.7,5.1,1.9,'Iris-virginica'),
(23,7.1,3.0,5.9,2.1,'Iris-virginica'),
(24,6.3,2.9,5.6,1.8,'Iris-virginica'),
(25,6.5,3.0,5.8,2.2,'Iris-virginica'),
(26,7.6,3.0,6.6,2.1,'Iris-virginica'),
(27,4.9,2.5,4.5,1.7,'Iris-virginica'),
(28,7.3,2.9,6.3,1.8,'Iris-virginica'),
(29,6.7,2.5,5.8,1.8,'Iris-virginica'),
(30,7.2,3.6,6.1,2.5,'Iris-virginica');

SELECT * FROM iris ORDER BY id LIMIT 5;





  (1) SQL로 선형 회귀모형 적합하고 모수 확인하기


PostgreSQL 에서 설명변수(독립변수) X 1개와 목표변수(종속변수) Y 와의 선형 회귀모형을 적합할 수 있습니다. 대신에 하나의 함수로 한번에 선형 회귀모형을 적합하는 것은 아니구요, REGR_SLOPE(Y, X) 함수로 기울기(slope)를 구하고, REGR_INTERCEPT(Y, X)로 Y절편을 구할 수 있습니다.



-- Python으로 산점도와 선형회귀선을 겹쳐서 그래보면 아래와 같습니다.



## Scatter Plot using Python seaborn package


import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
plt.rcParams['figure.figsize'] = [12, 8]


iris = sns.load_dataset('iris')


sns.regplot(x=iris['petal_length'],
            y=iris['petal_width'],
            fit_reg=True)

plt.title('Scatter Plot with Regression Line', fontsize=16)
plt.show()

 




REGR_COUNT(Y, X)는 관측치의 개수, REGR_AVGY(Y, X) 는 Y의 평균 값, REGR_AVGX(Y, X) 는 X의 평균 값을 구해줍니다.


그리고 REGR_R2(Y, X)는 적합된 선형회귀모형의 설명력을 보여주는 결정계수(coefficient of determination)를 구해줍니다.



----------------------------
-- (1) PostgreSQL functions
----------------------------
-- Training a Regression using PostgreSQL regr_slope(Y, X), regr_intercept(Y, X) function
DROP TABLE IF EXISTS iris_regr_postgres;
CREATE TABLE iris_regr_postgres AS (
SELECT
    'petal_width' AS y_var_nm
    , 'petal_length' AS x_var_nm
    , REGR_SLOPE(petal_width, petal_length)
    , REGR_INTERCEPT(petal_width, petal_length)
    , REGR_R2(petal_width, petal_length)
    , REGR_AVGY(petal_width, petal_length)
    , REGR_AVGX(petal_width, petal_length)
    , REGR_COUNT(petal_width, petal_length)
FROM  iris
);



SELECT * FROM iris_regr_postgres;







  (2) SQL로 그룹별로 선형 회귀모형 적합하고 예측하기


다음으로 'class_name' 범주('iris_setosa', 'iris_versicolor', 'iris_virginica') 그룹별로 1개 설명변수 'petal_length'와 종속변수 'petal_width'의 관계를 모형화하는 선형 회귀모형을 적합해보겠습니다.


위의 (1)번 SQL query에 SELECT 문에 그룹 칼럼('class_name')을 넣어주고, FROM 절 다음에 GROUP BY 그룹 칼럼('class_name') 을 넣어주면 됩니다. 모델 3개가 잘 적합되었습니다.



-- Regression by Groups
DROP TABLE IF EXISTS iris_regr_grp_postgres;
CREATE TABLE iris_regr_grp_postgres AS (
SELECT
    class_name AS group_nm
    , 'petal_width' AS y_var_nm
    , 'petal_length' AS x_var_nm
    , REGR_SLOPE(petal_width, petal_length)
    , REGR_INTERCEPT(petal_width, petal_length)
    , REGR_R2(petal_width, petal_length)
    , REGR_AVGY(petal_width, petal_length)
    , REGR_AVGX(petal_width, petal_length)
    , REGR_COUNT(petal_width, petal_length)
FROM  iris
GROUP BY class_name
ORDER BY class_name
);



SELECT * FROM iris_regr_grp_postgres ORDER BY group_nm;





이제 위에서 적합한 class_name별 3개 모델(기울기 slope, Y절편 intercept)의 모수를 활용해서 아래의 수식을 사용해서 예측을 해보겠습니다.



-- Prediction
SELECT
    iris.class_name
    , iris.id
    , iris.petal_width AS y_petal_width
    , (iris.petal_length * m.regr_slope + m.regr_intercept) AS pred_petal_width
FROM iris, iris_regr_grp_postgres m
WHERE iris.class_name = m.group_nm
ORDER BY id;







  (3) Apache MADlib으로 다중 선형 회귀모형(Multiple Linear Regression) 적합하기


SQL 기반의 오픈소스 Apache MADlib의 madlib.linregr_train() 함수를 사용하여 PostgreSQL, Greenplum database에서 여러개의 설명변수를 사용하는 다중 선형회귀모형 (multiple linear regression)을 적합할 수 있습니다.


위의 (1)번 PostgreSQL 의 기울기, Y절편 함수에서는 설명변수 X로 1개의 칼럼만을 사용하는 한계가 있었습니다. 그리고 기울기와 Y절편, R^2 등을 구하기 위해 개별 함수를 사용해야 하는 불편함이 있었습니다.


반면에, MADlib의 madlib.linregr_train() 함수는 source table, output table, dependent variable, ARRAY[1, independent variables] 의 순서대로 칼럼 이름을 넣어주면 됩니다. 그러면 회귀계수(coef), 결정계수(r2), 설명변수별 표준화오차(std_err)와 t통계량(t_stats), p값(p_values), condition_no, 관측치 개수(num_rows_processed), 결측치 개수(num_missing_rows_skipped), 분산공분산(variance_covariance) 의 결과를 반환합니다.



----------------
-- (2) MADlib
----------------
-- Multivariate Regression using MADlib
DROP TABLE IF EXISTS iris_regr, iris_regr_summary;
SELECT madlib.linregr_train(
    'iris'              -- source table
    , 'iris_regr'      -- output table
    , 'petal_width' -- dependent variable
    , 'ARRAY[1, petal_length, sepal_length]' -- independent variables
);

SELECT * FROM iris_regr;





위의 선형 회귀모형 적합 결과를 좀더 보기 좋도록 UNNEST() 를 사용해서 설명변수별로 구분해서 풀어서 제시해보겠습니다.



SELECT
    UNNEST(ARRAY['intercept', 'petal_length', 'sepal_length']) AS var_nm
    , UNNEST(coef) AS coef
    , UNNEST(std_err) AS std_err
    , UNNEST(t_stats) AS t_stats
    , UNNEST(p_values) AS p_values
FROM iris_regr;






  (4) Apache MADlib으로 그룹별로 다중 선형 회귀모형 적합하고 예측하기


이번에는 class_name 범주의 그룹별로 다중 선형회귀모형을 적합해 보겠습니다.


madlib.linregr_tarin() 함수의 5번째 인자에 Grouping Column으로서 'class_name' 을 넣어주면 됩니다.



-- Multiple Regression by Group using MADlib
DROP TABLE IF EXISTS iris_regr_grp, iris_regr_grp_summary;
SELECT madlib.linregr_train(
    'iris'          -- source table
    , 'iris_regr_grp'   -- output table
    , 'petal_width' -- dependent variable
    , 'ARRAY[1, petal_length, sepal_length]' -- indepent variables
    , 'class_name'  -- grouping column
);

--SELECT * FROM iris_regr_grp;

SELECT
    class_name
    , UNNEST(ARRAY['intercept', 'petal_length', 'sepal_length']) AS var_nm
    , UNNEST(coef) AS coef
    , UNNEST(std_err) AS std_err
    , UNNEST(t_stats) AS t_stats
    , UNNEST(p_values) AS p_values
FROM iris_regr_grp;





위에서 3개 범주 그룹별로 적합한 모델을 사용해서 madlib.linregr_predict() 함수로 예측을 해보겠습니다. 이때 WHERE 조건절에 input dataset의 class_name 범주와 모델 테이블의 class_name 이 같아야 한다는 조건을 추가해줍니다.



-- Prediction
SELECT iris.*,
       madlib.linregr_predict( m.coef,
                               ARRAY[1,petal_length,sepal_length]
                             ) AS predict
FROM iris, iris_regr_grp m
WHERE  iris.class_name = m.class_name
ORDER BY id
LIMIT 10;





[Reference]
* PostgreSQL 9.4: https://www.postgresql.org/docs/9.4/functions-aggregate.html
* Apache MADlib : https://madlib.apache.org/docs/latest/group__grp__linreg.html


이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)



Posted by R Friend Rfriend

댓글을 달아 주세요

이번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 함수, PL/R, PL/Python을 사용해서 연속형 데이터에 대한 요약통계량을 구하는 방법을 소개하겠습니다.  무척 쉬운 내용이므로 쉬어가는 코너 정도로 가볍게 생각해주시면 좋겠습니다. ^^


PostgreSQL, Greenplum database 에서 연속형 데이터에 대해 그룹별로, 

(1) SQL 로 요약통계량 구하기

(2) Apache MADlib 으로 요약통계량 구하기





참고로, 이번 포스팅에서는 PostgreSQL 9.4, Greenplum 6.10.1 버전을 사용하였으며, PostgreSQL 9.4 버전보다 낮은 버전을 사용하면 최빈값(mode), 사분위부(percentile) 구하는 함수를 사용할 수 없습니다. 


먼저, 예제로 사용하기 위해 '나이'의 연속형 데이터와 '성별'의 범주형 데이터 (그룹)를 가진 간단한 테이블을 만들어보겠습니다. 결측값(missing value)도 성별 그룹별로 몇 개 넣어봤습니다. 



DROP TABLE IF EXISTS cust;

CREATE TABLE cust (id INT, age INT, gender TEXT);

INSERT INTO cust VALUES

(1,NULL,'M'),

(2,NULL,'M'),

(3,25,'M'),

(4,28,'M'),

(5,27,'M'),

(6,25,'M'),

(7,26,'M'),

(8,29,'M'),

(9,25,'M'),

(10,27,'M'),

(11,NULL,'F'),

(12,23,'F'),

(13,25,'F'),

(14,23,'F'),

(15,24,'F'),

(16,26,'F'),

(17,23,'F'),

(18,24,'F'),

(19,22,'F'),

(20,23,'F');

 




 (1) SQL로 연속형 데이터의 그룹별 요약통계량 구하기


함수가 굳이 설명을 안해도 될 정도로 간단하므로 길게 설명하지는 않겠습니다. 


표준편차 STDDEV() 와 분산 VARIANCE() 함수는 표본표준편차(sample standard deviation), 표본분산(sample variance) 를 계산해줍니다. 만약 모표준편차(population standard deviation), 모분산(population variance)를 구하고 싶으면 STDDEV_POP(), VAR_POP() 함수를 사용하면 됩니다. 


PostgreSQL 9.4 버전 이상부터 최빈값(MODE), 백분위수(Percentile) 함수가 생겨서 정렬한 후에 집계하는 기능이 매우 편리해졌습니다. (MODE(), PERCENTILE_DISC() 함수를 사용하지 않고 pure SQL로 최빈값과 백분위수를 구하려면 query 가 꽤 길어집니다.)



SELECT

    gender AS group_by_value

    , 'age' AS target_column

    , COUNT(*) AS row_count

    , COUNT(DISTINCT age) AS distinct_values

    , AVG(age)

    , VARIANCE(age)

    , STDDEV(age)

    , MIN(age)

    , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile

    , MEDIAN(age)

    , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile

    , MAX(age)

    , MODE() WITHIN GROUP (ORDER BY age) -- over PostgreSQL 9.4

FROM cust

WHERE age IS NOT NULL

GROUP BY gender

ORDER BY gender;





성별 그룹별로 연령(age) 칼럼의 결측값 개수를 구해보겠습니다. 

결측값 개수는 WHERE age IS NULL 로 조건절을 주고 COUNT(*)로 행의 개수를 세어주면 됩니다. 



SELECT 

    gender

    , COUNT(*) AS missing_count

FROM cust

WHERE age IS NULL

GROUP BY gender

ORDER BY gender;


Out[5]:
gendermissing_count
F1
M2





위의 집계/ 요약통계량과 결측값 개수를 하나의 조회 결과로 보려면 아래처럼 Join 을 해주면 됩니다.



WITH summary_tbl AS (
    SELECT
        gender AS group_by_value
        , 'age' AS target_column
        , COUNT(*) AS row_count
        , COUNT(DISTINCT age) AS distinct_values
        , AVG(age)
        , VARIANCE(age)
        , STDDEV(age)
        , MIN(age)
        , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile
        , MEDIAN(age)
        , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile
        , MAX(age)
        , MODE() WITHIN GROUP (ORDER BY age)
    FROM cust
    WHERE age IS NOT NULL
    GROUP BY gender
    ORDER BY gender
), missing_tbl AS (
    SELECT
        gender AS group_by_value
        , COUNT(*) AS missing_count
    FROM cust
    WHERE age IS NULL
    GROUP BY gender
)
SELECT a.*, b.missing_count
FROM summary_tbl a LEFT JOIN missing_tbl b USING(group_by_value)
;

 




  (2) Apache MADlib으로 연속형 데이터의 그룹별 요약통계량 구하기


Apache MADlib의 madlib.summary() 함수를 사용하면 단 몇 줄의 코드만으로 위의 (1)번에서 SQL 집계 함수를 사용해서 길게 짠 코드를 대신해서 매우 깔끔하고 간단하게 구할 수 있습니다. 


아래는 (1)번의 결과를 얻기위해 성별(gender) 연령(age) 칼럼의 집계/요약데이터를 구하는 madlib.summary() 함수 예시입니다. 


Target columns 위치에는 1 개 이상의 분석을 원하는 연속형 데이터 칼럼을 추가로 넣어주기만 하면 되므로 (1) 번의 pure SQL 대비 훨씬 편리한 측면이 있습니다! 


그리고 그룹별로 구분해서 집계/요약하고 싶으면 Grouping columns 위치에 기준 칼럼 이름을 넣어주기만 하면 되므로 역시 (1)번의 pure SQL 대비 훨씬 편리합니다!



DROP TABLE IF EXISTS cust_summary;

SELECT madlib.summary('cust'     -- Source table

                      ,'cust_summary'   -- Output table

                      , 'age'                -- Target columns

                      , 'gender'            -- Grouping columns

);






madlib.summary() 함수의 결과 테이블에서 조회할 수 있는 집계/요약통계량 칼럼 리스트는 아래와 같습니다. 



SELECT column_name

FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_SCHEMA = 'public'

        AND TABLE_NAME    = 'cust_summary'

    ORDER BY ORDINAL_POSITION;

Out[7]:
column_name
group_by
group_by_value
target_column
column_number
data_type
row_count
distinct_values
missing_values
blank_values
fraction_missing
fraction_blank
positive_values
negative_values
zero_values
mean
variance
confidence_interval
min
max
first_quartile
median
third_quartile
most_frequent_values
mfv_frequencies

 



[Reference]

* PostgreSQL aggregate functions: https://www.postgresql.org/docs/9.4/functions-aggregate.html

* Apache MADlib summary function: https://madlib.apache.org/docs/v1.11/group__grp__summary.html



다음번 포스팅에서는 PostgreSQL, Greenplum에서 SQL과 Apache MADlib을 이용하여 상관계수, 상관계수 행렬을 구하는 방법(https://rfriend.tistory.com/581)을 소개하겠습니다.


이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database에 접속할 수 있는 4가지 방법(https://rfriend.tistory.com/577)을 소개하였습니다.


이번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 PostgreSQL, Greenplum database에 SQL query를 할 때 Jupyter Notebook의 로컬 변수를 SQL query에 대입하여 변수값을 동적으로 대체해가면서 query 할 수 있는 3가지 방법을 소개하겠습니다.


Python의 로컬 변수를 SQL query 문에 사용할 수 있으므로 Python과 PostgreSQL, Greenplum DB를 서로 연동해서 데이터분석과 프로그래밍을 하는 경우 매우 강력하고 유용하게 사용할 수 있습니다.


(방법 1) Variable Substitution:  %sql SELECT :variable_name

(방법 2) Variable Substitution:  %sql SELECT {variable_name}

(방법 3) Variable Substitution:  %sql SELECT $variable_name





  (0) 필요 Python 패키지 사전 설치


아래의 SQLAlchemy, psycopg2, ipython-sql, pgspecial, sql_magic 중에서 아직 설치가 안된 패키지가 있다면 아래처럼 명령 프롬프트 창에서 Python의 패키지를 설치해줍니다.



-- (명령 프롬프트 창에서 pip 로 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

 



ipython-sql 패키지로 Jupyter Notebook에서 Greenplum database에 접속한 후에, 예제로 사용할 간단한 houses 테이블을 만들어보겠습니다.



%load_ext sql


%sql postgresql://gpadmin:changeme@localhost/demo

[Out] 'Connected: gpadmin@demo'


%sql select version();

 * postgresql://gpadmin:***@localhost/demo
1 rows affected.
Out[3]:
version
PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29


%%sql
DROP TABLE IF EXISTS houses;
CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
            size INT, lot INT, region TEXT);
INSERT INTO houses VALUES
  (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100, 'seoul'),
  (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000, 'seoul'),
  (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500, 'busan'),
  (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500, 'seoul'),
  (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000, 'bundang'),
  (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700, 'bundang'),
  (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000, 'busan'),
  (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000, 'busan'),
  (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000, 'inchon'),
 (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000, 'seoul'),
 (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500, 'inchon'),
 (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000, 'busan'),
 (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000, 'bundang'),
 (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000, 'bundang'),
 (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000, 'inchon');





  (방법 1) Variable Substitution:   %sql SELECT :variable_name


첫번째 방법은 :variable_name 과 같은 named style 을 사용해서 Jupyter Notebook에서 local namespace에 생성한 변수 이름을 SQL query에 넣어서 동적으로 값을 바꾸어 가면서 query 를 해보겠습니다.


(1-1) :variable_name 으로 SQL의 정수(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN :id_var;

[Out]

* postgresql://gpadmin:***@localhost/demo 3 rows affected.

Out[123]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(1-2) :variable_name 으로 SQL의 문자형(character) 변수값 대체



region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = :region_var;

[Out]

* postgresql://gpadmin:***@localhost/demo 4 rows affected.

Out[125]:
idtaxbedroombathpricesizelotregion
10368042.0240000279020000seoul
159021.05000077022100seoul
2105032.085000141012000seoul
487022.090000130017500seoul


 




  (방법 2) Variable Substitution:  %sql SELECT {variable_name}


(2-1) {variable_name} 으로 SQL의 정수(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN {id_var};

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[126]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(2-2) '{variable_name}' 으로 SQL의 문자형(character) 변수값 대체



region_var = 'seoul'

%sql SELECT * FROM houses WHERE region = '{region_var}';

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 4 rows affected.

Out[127]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
10368042.0240000279020000seoul
2105032.085000141012000seoul
487022.090000130017500

seoul




  (방법 3) Variable Substitution:  %sql SELECT $variable_name


(3-1) $variable_name 으로 SQL의 정수형(integer) 변수값 대체



id_var = (1, 5, 15)

%sql SELECT * FROM houses WHERE id IN $id_var;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[128]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(3-2) $variable_name 으로 SQL의 문자형(character) 변수값 대체


localname space에 문자형의 bind parameter 값 입력해줄 때 큰따옴표(" ")로 감싸주고, 그 안에 작은따옴표(' ')로 값 입력해주도록 하세요. 그냥 작은따옴표(' ')만 했더니 칼럼으로 인식을 해서 에러가 나네요.



region_var = "'seoul'"

%sql SELECT * FROM houses WHERE region = $region_var;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[171]:
idtaxbedroombathpricesizelotregion
2105032.085000141012000seoul
487022.090000130017500seoul
10368042.0240000279020000seoul

 



* Reference: https://pypi.org/project/ipython-sql/



이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요!



Posted by R Friend Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Python의 ipython-sql, pgspecial 패키지를 사용하여 Jupyter Notebook 에서 PostgreSQL, Greenplum database 에 접속(access)하고 SQL query, meta-commands 를 하는 방법(https://rfriend.tistory.com/572)을 간략하게 소개하였습니다.


이번 포스팅에서는 psycopg2 와 ipython-sql 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database 에 접속하는 4가지 방법을 추가로 소개하겠습니다.


특히, 사용자이름(username), 비밀번호(password), 호스트(host), 포트(port), 데이터베이스(database) 등과 같이 보안이 요구되고 다른 사용자에게 노출이나 공유가 되면 곤란한 정보들(DB Credentials)을 Jupyter Notebook에서 표기/노출하지 않고 별도의 파일로 보관하면서, 이를 불러와서 DB access 할 수 있는 방법들에 주안점을 두고 소개하겠습니다.


-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력 (* 외부 노출되므로 권장하지 않음)

(1) %sql postgresql://Username:Password@Host:Port/Database

(2) %sql $connection_string


-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)

(3) config.py 별도 파일 & %sql $connection_string

(4) db_credentions 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path





(0) Python 패키지 사전 설치


먼저, 명령 프롬프트 창에서 아래의 PostgreSQL, Greenplum database에 접속하고 SQL query 를 하기 위해 필요한 Python 패키지들을 pip로 설치해줍니다.



-- (명령 프롬프트 창에서 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 



-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력

   (* 편리하기는 하지만, DB 접속 정보가 외부에 노출되므로 권장하지 않음. )


 (1) %sql postgresql://Username:Password@Host:Port/Database


가장 편리한 방법은 SQLAlchemy 표준 URL (database-driver://Username:Password@Host:Port/Database) 에 따라 PostgreSQL, Greenplum database에 connection engine을 생성하여 접속하는 방법입니다.


하지만, 이 방법은 Jupyter Notebook에 DB 접속정보가 고스란히 노출되기 때문에 만약 다른 조직, 팀원 간에 협업을 하고 notebook 파일을 공유해야할 일이 생길 경우 보안 방침에 위배가 되므로 권장하는 방법은 아닙니다.



%load_ext sql


# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@demo'







 (2) %sql $connection_string


%sql $connection_string 문으로 Jupyter Notebook에서 동적으로 DB credentials 를 Python string format 으로 입력받아서 PostgreSQL, Greenplum database에 접속할 수 있습니다. 


다만, 아래처럼 DB credentials 를 Jupyter notebook 의 Cell 안에서 직접 입력하면 DB 접속 정보가 외부로 노출되는 문제가 있습니다.



%load_ext sql


# DB credentials

username = "gpadmin"
password = "changeme"
host = "localhost"
port = "5432"
database = "demo"


# connection strings using Python string format
connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)


# dynamic access dredentials
%sql $connection_string

 





-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

    (* 보안유지 되므로 권장함)


 (3) config.py 별도 파일 & %sql $connection_string


세번째 방법은 DB Credentials 정보를 별도의 파일에 분리해서 만들어놓고, 이를 불러와서 DB connect 하는 방법입니다. 아래에 예를 들어보면, (폴더, 파일 이름은 각자 알아서 정해주면 됨)


(a) HOME directory 밑에 DB credentials 파일을 넣어둘 'db_cred' 라는 이름의 폴더 만들고,

(b) 'db_cred' 폴더 안에 'gpdb_credentials.py', '__init__.py' 라는 이름의 2개의 Python 파일을 생성함.

     'gpdb_credentials.py' 파일에는 Dictionary (Key : Value 짝) 형태로 Username, Password, Host, Port, Database 정보를 입력해줌. 여러개의 Database 별로 credentials 정보를 각각 다른 이름의 Dictionary 로 하나의 파일 안에 생성해놓을 수 있음.

     '__init__.py' 파일은 내용은 비어있으며, 해당 폴더의 Python 파일을 패키지로 만들기 위해 생성해줌.

(c) Jupyter Notebook 을 작업하는 Directory 에서도 HOME directory 밑의 'db_cred' 폴더에 접근해서 'gpdb_credentials.py' 파일에 접근할 수 있도록 sys.path.append(cred_path) 로 Python 의 Path 에 추가해줌. (sys.path.append(cred_path))

(d) 작업을 하는 Jupyter Notebook 에서 'from gpdb_credentials import demo_db' 문으로 gpdb_credentials.py 파일에서 'demo_db' Dictionary 를 불러옴.

(e) 'demo_db' Dictionary 에서 DB connection에 필요한 정보를 파싱해옴. (dict['key'] 인덱싱)

(f) %sql $ 문 뒤에 (e)에서 파싱해서 만든 connection_string을 입력해서 DB connect 함.



%load_ext sql


# put a folder and DB credential files at HOME directory

import os
homedir = os.getenv('HOME')
cred_path = os.path.join(homedir, 'db_cred')


# add a 'cred_path' for interpreter to search
import sys
sys.path.append(cred_path)


# import DB credentials from 'gpdb_credentials.py' dictionary file.

from gpdb_credentials import demo_db


# parsing DB credentials and connect to Greenplum using %sql $connection_string

username = demo_db['Username']
password = demo_db['Password']
host = demo_db['Host']
port = demo_db['Port']
database = demo_db['Database']

connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

%sql $connection_string






(4) db_credentials 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path


명령 프롬프트 창에서 아래처럼 0.3.9 버전의 ipython-sql을 설치해줍니다. (최신 버전은 0.4.0 이지만 Python 3.x. 버전의 ipython-sql 0.4.0 버전에 DSN connections 를 하는데 있어 config 를 반환하지 않는 bug가 있습니다. config bug fix 되기 전까지는 0.3.9 버전으로 사용하기 바랍니다.)


-- 명령 프롬프트 창에서 ipython-sql 0.3.9 버전 설치

pip install ipython-sql==0.3.9


(a) PostgreSQL, Greenplum database 접속 정보(connection info.)를 별도의 configuration file 에 저장하여 HOME directory 밑에 보관합니다. 이때 2개 이상의 복수의 DB credentials 정보를 [DB alias] 로 구분해서 하나의 configuration file에 저장해서 사용할 수 있습니다.


파일 이름을 ".odbc.ini", ".dsn.ini" 처럼 "."으로 시작하면 '숨김 파일(hidden file)'이 되어 평상시에는 탐색기, Finder에서는 볼 수가 없으므로 DB 접속정보를 관리하는데 좀더 보안에 유리합니다.

(참고로, Windows OS에서 숨김파일을 보려면, Windows 탐색기에서 [구성] > [폴더 및 검색 옵션] > [폴더 옵션] 대화상자에서 [보기] 탭을 클릭 > [고급 설정]에서 "숨김 파일 밒 폴더 표시"를 선택하면 됩니다.

Mac OS 에서는 Finder에서 "Shift + Command + ." 동시에 눌러주면 숨김 파일이 표시됩니다.)


(b) Jupyter Notebook에서 ipython-sql 로 DSN connections 을 할 수 있습니다.

    %config SqlMagic.dsn_filename = "$homedir/.odbc.ini"


(c) DB connect 된 이후에 제일 처음으로 %sql 로 SQL query 할 때 DB credentions 의 DB alias 를 [ ] 안에 넣어서 명시를 해주고(예: %sql [demo_db] SELECT version();), 그 다음부터 %sql 문으로 SQL query 할 때는 DB alias 를 안써주고 바로 SQL query 를 하면 됩니다.


(d) Jupyter Notebook의 중간 Cell 에서 사용(connect)하려는 DB를 바꾸고 싶으면 %sql [DB_alias2] SELECT .... 처럼 [DB_alias] 부분에 다른 DB alias 이름을 명시해주고 SQL query 를 하면, 그 이후 Cell 부터는 새로운 DB 를 connect 해서 query를 할 수 있습니다.

(예: %sql [dev_db] SELECT COUNT(*) FROM tbl;)



%load_ext sql


import os
homedir = os.getenv('HOME')

# parse and configure gpdb credentials and access to GPDB
%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

# put [alias_name] after %sql in the first line
%sql [demo_db] SELECT version();

[Out] * postgresql://gpadmin:***@localhost:5432/demo

1 rows affected.



* Reference: https://pypi.org/project/ipython-sql/


다음 포스팅에서는 ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법(https://rfriend.tistory.com/578)을 소개하겠습니다.


이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요!



Posted by R Friend Rfriend

댓글을 달아 주세요

Python의 SQLAlchemy (모든 DB), psycopg2 (PostgreSQL, Greenplum) 패키지를 이용하여 Spyder 나 Pycharm 과 같은 IDE에서 PostgreSQL, Greenplum database에 접속(access) 하고 SQL query 를 할 수 있습니다.




그렇다면 Jupyter Notebook 에서도 DB access, SQL query를 할 수 있으면 편하겠지요?


이번 포스팅에서는 Python의 ipython-sql 과 pgspecial 패키지를 이용하여 PostgreSQL, Greenplum database 에 Jupyter Notebook으로 접속(access DB)하여 SQL query 를 하는 방법을 소개하겠습니다.



(1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

(2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

(3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands query 하기 (\l, \dn, \dt)






(0) 사전 설치가 필요한 Python 패키지 리스트


명령 프롬프트 창에서 아래의 5개 패키지에 대해서 pip 로 설치해주시기 바랍니다.


sqlalchemy, psycopg2는 PostgreSQL, Greenplum DB 접속(access, connection)를 위해서 필요한 Python 패키지 입니다.


ipython-sql, sql_magic은 IPython으로 Jupyter Notebook에서 DB access, SQL query 를 하기 위해 필요한 Python 패키지입니다.


pgspecial은 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 meta-commands (역슬래쉬 \ 로 시작하는, psql 에서 사용하는 \l, \dn, \dt 명령문) 를 위해 필요한 Python 패키지입니다.


ipython-sql 의 경우 2020.12월 현재 0.4.0 버전 (python 3.x) 이 최신인데요, %config로 DB access 하는 명령문의 bug가 아직 fix가 안되어 있어서, 아래처럼 ipython-sql==0.3.9 로 한단계 낮은 버전으로 설치해주세요.



-- 명령 프롬프트 창에서 pip 로 python 패키지 설치


$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

 




 (1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기


%load_ext sql 로 IPython의 sql 을 로딩하여 %sql 또는 %%sql magic 명령문을 사용할 수 있습니다.

PostgreSQL, Greenplum database에 접속할 때는 SQLAlchemy 의 표준 URL connect strings 를 사용합니다.


[ SQLAlchemy 의 표준 Database URL]


 dialect+driver://username:password@hoat:port/database


아래의 db credentials 로 Greenplum database에 접속할 때의 예입니다.

- driver: postgresql

- username: gpadmin

- password: changeme

- host: localhost

- port: 5432

- database: demo



%load_ext sql


# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@testdb'





* 위의 %sql SQLAlchemy 표준 URL 방법 외에 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 접속하는 다른 3가지 추가 방법은 https://rfriend.tistory.com/577 를 참고하세요.




 (2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)


Jupyter Notebook의 Cell 안에 1줄 SQL query일 경우는 %sql 로 시작하고, 2줄 이상 SQL query 일 경우에는 %%sql 로 시작합니다.


(2-1) %sql : 1줄의 SQL query


1줄짜리 SELECT 문으로 PostgreSQL의 버전을 확인해보겠습니다.



%sql SELECT version();


[Out]

version
PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29

 





(2-1) %%sql : 2줄 이상의 SQL query


아래 예제는 pg_catalog.pg_tables 테이블에서 스키마 이름이 pg_catalog, information_schema 가 아닌 테이블을 조회하여 1개만 반환해보는 query 입니다.



%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema'
LIMIT 1;

 





 (3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands (\l, \dn, \dt)


meta-commands 는 psql 에서 역슬래쉬(\)와 함께 사용해서 데이터베이스, 스키마, 테이블 등을 조회할 때 사용하는 명령어를 말합니다. pgspecial 패키지는 Jupyter Notebook에서 meta-commands 를 사용할 수 있게 해줍니다.


(3-1) database 조회 : %sql \l  (역슬래쉬 + L)



%sql \l


[Out]

NameOwnerEncodingCollateCtypeAccess privileges
demogpadminUTF8en_US.utf8en_US.utf8=Tc/gpadmin
gpadmin=CTc/gpadmin
dsuser=CTc/gpadmin
gpperfmongpadminUTF8en_US.utf8en_US.utf8None
postgresgpadminUTF8en_US.utf8en_US.utf8None
template0gpadminUTF8en_US.utf8en_US.utf8=c/gpadmin
gpadmin=CTc/gpadmin
template1gpadminUTF8en_US.utf8en_US.utf8

=c/gpadmin
gpadmin=CTc/gpadmin




(3-2) Schema 조회 : %sql \dn


%sql \dn


[Out]

NameOwner
gp_toolkitgpadmin
madlibdsuser14
publicgpadmin






(3-3) Table 조회 : %sql \dt

아래 예제는 public 스키마에서 "ab" 로 시작하는 모든 테이블(public.ab*)을 조회한 것입니다.

 

%sql \dt public.ab*


[Out]

SchemaNameTypeOwner
publicabalonetable

gpadmin

publicabalone_corrtable

gpadmin

publicabalone_corr_summarytable

gpadmin

publicabalone_correlationstable

gpadmin




이상으로 ipython-sql, pgspecial 패키지를 사용해서 PostgreSQL, Greenplum database에 접속하고 SQL query, meta-commands 하는 방법에 대한 가장 기본적이고 개략적인 소개를 마치겠습니다.

* 다음번 포스팅에서는 SQLAlchemy, psycopg2, ipython-sql 로 Jupyter Notebook 에서 PostgreSQL, Greenplum database에 접속하는 4가지 방법(https://rfriend.tistory.com/577)에 대한 소소한 팁을 추가로 소개하겠습니다.

* ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법https://rfriend.tistory.com/578 를 참고하세요.

* ipython-sql로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법https://rfriend.tistory.com/579 를 참고하세요.

이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요!



Posted by R Friend Rfriend

댓글을 달아 주세요

만약 한개 당 1분 걸리는 동일한 프로세스의 100개의 일을 한 명이서 한다면 100분이 걸릴텐데요, 이것을 100명에게 일을 1개씩 나누어서 동시에 분산해서 시킨다면 1분(+취합하는 시간 약간) 밖에 안걸릴 것입니다. 1명이 100명을 이길 수는 없기 때문입니다. 


대용량 데이터에 대해서 빠른 성능으로 통계나 기계학습의 고급 분석을 처리해야 하는 경우라면 Greenplum 과 같은 MPP (Massively Parallel Processing) 아키텍처 기반의 DB에서 R 이나 Python 언어로 작성한 알고리즘을 In-DB에서 PL/R, PL/Python을 사용해서 분산 병렬 처리할 수 있습니다. 


이번 포스팅에서는 Greenplum DB에서 PL/R (Procedural Language R) 을 사용해서 분산 병렬처리(distributed parallel processing하여 그룹별로 선형회귀모형을 각각 적합하고 예측하는 방법을 소개하겠습니다. 모든 연산이 In-DB 에서 일어나기 때문에 데이터 I/O 가 없으므로 I/O 시간을 절약하고 architecture 와  workflow를 간단하게 가져갈 수 있는 장점도 있습니다. (vs. DB 에서 local R 로 데이터 말아서 내리고, local R로 모형 적합 / 예측 후, 이 결과를 다시 DB에 insert 하고 하는 복잡한 절차가 필요 없음)



이번에 소개할 간단한 예제로 사용할 데이터셋은 abalone 공개 데이터셋으로서, 성 (sex) 별로 구분하여 무게(shucked_weight)와 지름(diameter) 설명변수를 input으로 하여 껍질의 고리 개수(rings)를 추정하는 선형회귀모형을 적합하고, 예측하는 문제입니다. 


이러한 일을 성별 F, M, I 별로 순차적으로 하는 것이 아니라, Greenplum DB 에서 성별 F, M, I 별로 PL/R로 분산 병렬처리하여 동시에 수행하는 방법입니다. 



  (1) abalone 데이터셋으로 테이블 만들기


먼저, abalone 데이터셋을 공개 데이터셋 웹사이트에서 가져와서 External table을 만들고, 이로부터 abalone table 을 생성해보겠습니다. 



---------------------------------

-- Linear Regression in Parallel 

-- using PL/R

---------------------------------


-- 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;






  (2) Train, Test set 테이블 분할 (train, test set split)


다음으로 MADlib(https://madlib.apache.org/) 의 madlib.train_test_split() 함수를 사용해서 abalone 원 테이블을 train set, test set 테이블로 분할(split into train and test set) 해보겠습니다. 



---------------------------

-- Train, Test set split

---------------------------

-- Check the number of observations per sex group(F, I, M)
SELECT
 sex, COUNT(*) FROM abalone GROUP BY 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



SELECT * FROM out_train LIMIT 5;




SELECT sex, count(*) FROM out_train GROUP BY sex;




SELECT sex, count(*) FROM out_test GROUP BY sex;






  (3) array aggregation 하여 PL/R에서 사용할 데이터셋 준비하기


좀 낯설을 수도 있는데요, PL/R 에서는 array 를 input으로 받으므로 array_agg() 함수를 사용해서 설명변수를 칼럼별로 array aggregation 해줍니다. 이때 성별(sex) 로 모형을 각각 병렬로 적합할 것이므로 group by sex 로 해서 성별로 따로 따로 array aggregation 을 해줍니다. 이렇게 해주면 long format으로 여러개의 열(row)에 들어있던 값들이 성별로 구분이 되어서 하나의 array( { } )에 모두 들어가게 됩니다. (아래 이미지 참조)


 

-- Data Preparation

-- : array aggregation using array_agg()

DROP TABLE IF EXISTS abalone_array;

CREATE TABLE abalone_array AS 

SELECT

sex::text -- group

, array_agg(rings::float8) as rings             -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter       -- x2

FROM out_train

GROUP BY sex

DISTRIBUTED BY (sex);


SELECT * FROM abalone_array;






  (4) 선형회귀모형 적합하는 PL/R 사용자 정의 함수 정의하기 (Define PL/R UDF)


선형회귀모형 PL/R 의 반환받는 값을 두가지 유형으로 나누어서 각각 소개하겠습니다.


(4-1) 적합된 회귀모형의 회귀계수 (coefficients) 를 반환하기

(4-2) 적합된 회귀모형 자체(fitted model itself)를 반환하기 



먼저, (4-1) 적합된 회귀모형의 회귀계수를 반환하는 PL/R 함수를 정의하는 방법을 소개하겠습니다. 


R의 lm() 함수를 사용하여 다중 선형회귀모형을 적합(fit a model)하면, summary(fitted_model)$coef 는 추정된 회귀계수(coef_est), 표준오차(std_error), T통계량(t_stat), P-값 (p_value) 를 반환합니다. 


CREATE OR REPLAE FUNCTION pl_r_funtion_name(column_name data_type[], ...) 으로 PL/R 함수 이름과 인자로 받는 칼럼 이름, 데이터 유형을 정의해주고, 


이들 모형 적합 후의 추정된 회귀계수와 통계량을 Greenplum DB에 반환하려면 데이터 유형이 여러개이므로 composit type 을 별도로 정의('lm_abalone_type')해주어고, PL/R 사용자 정의함수에서 returns setof lm_abalone_type 처럼 써주면 됩니다.


그 다음에, $$ pure R codes block $$ LANGUAGE 'plr' 형식으로 R codes 를 통째로 $$ ~~~~ $$ 안에 넣어주면 됩니다. 



----------------------------------------------------------------

-- (4-1) PL/R : Linear Regression Model's Coefficients --> Predict

----------------------------------------------------------------


-- Return Types

DROP TYPE IF EXISTS lm_abalone_type CASCADE;

CREATE TYPE lm_abalone_type AS (

variable text

, coef_est float

, std_error float

, t_stat float

, p_value float

);


-- PL/R User Defined Function

DROP FUNCTION IF EXISTS plr_lm_train(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_train(

rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS SETOF lm_abalone_type AS

$$

m1 <- lm(rings ~ s_weight + diameter)

m1_s <- summary(m1)$coef

temp_m1 <- data.frame(rownames(m1_s), m1_s)

return(temp_m1)

$$

LANGUAGE 'plr';

 





 (5) PL/R 실행하기 (execute PL/R UDF in Greenplum DB)


위의 (4)에서 정의한 성별(per sex) 선형회귀모형을 적합하여 회귀계수와 통계량을 반환하는 PL/R 사용자 정의함수를 Greenplum DB 안에서 병렬처리하여 실행해보겠습니다. 


select sex, (plr_lm_train(rings, s_weight, diameter)).* from abalone_array 처럼 위의 (4)번에서 정의한 PL/R 함수에 (3)번에서 준비한 array 가 들어있는 테이블의 칼럼을 써주고, from 절에 array 테이블 이름을 써주면 됩니다. 


이때 테이블에 return 받는 값들이 composit type의 여러개 칼럼들이므로 (plr_udf(column, ...)).* 처럼 PL/R 함수를 괄호 ( ) 로 싸주고 끝에 '*' (asterisk) 를 붙여줍니다. ( * 를 빼먹으면 여러개의 칼럼별로 나누어지지 않고 한개의 칼럼에 튜플로 모든 칼럼이 뭉쳐서 들어갑니다)



-- Execution of Linear Regression PL/R 

DROP TABLE IF EXISTS lm_abalone_model_coef;

CREATE TABLE lm_abalone_model_coef AS (

SELECT sex, (plr_lm_train(rings, s_weight, diameter)).* 

FROM abalone_array

) DISTRIBUTED BY (sex);


SELECT * FROM lm_abalone_model_coef;







  (6) 적합한 선형회귀모형을 사용해 test set에 대해 예측하기 (prediction on test set)


위의 (5)번에서 적합한 성별 선형회귀모형들의 회귀계수 (coefficients per sex groups) 를 사용해서 test set 의 데이터셋에 대해 PL/R 함수로 분산 병렬처리하여 rings 값을 예측해보겠습니다. (training 도 분산병렬처리, prediction/ scoring 도 역시 분산병렬처리!)


먼저, 예측하는 PL/R 함수에 넣어줄 test set을 array aggregation 해줍니다. 


다음으로, ID별로 실제값(actual rings)과 예측한 값(predicted rings)을 반환받을 수 있도록 composite type 을 정의해줍니다. 


그 다음엔 추정된 회귀계수를 사용해서 예측할 수 있도록 행렬 곱 연산을 하는 PL/R 함수(plr_lm_coef_predict())를 정의해줍니다. 


마지막으로 예측하는 PL/R 함수를 실행해줍니다. 



------------------------------------------------

-- Prediction and Model Evaluation for Test set

------------------------------------------------


-- Preparation of test set in aggregated array

DROP TABLE IF EXISTS test_array;

CREATE TABLE test_array AS 

SELECT

sex::text

, array_agg(rings::float8) as rings             -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter       --x2

FROM out_test

GROUP BY sex

DISTRIBUTED BY (sex);


SELECT * FROM test_array;




-- Define composite data type for predicted return values

DROP TYPE IF EXISTS lm_predicted_type CASCADE;

CREATE TYPE lm_predicted_type AS (

id int

, actual float

, pred float

);



-- Define PL/R UDF of prediction per groups using linear regression coefficients

DROP FUNCTION IF EXISTS plr_lm_coef_predict(float8[], float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_coef_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, coef_est float8[]

) RETURNS SETOF lm_predicted_type AS

$$

actual <- rings # y

intercept <- 1

X <- cbind(intercept, s_weight, diameter) # X matrix

coef_est <- matrix(coef_est) # coefficients matrix

predicted <- X %*% coef_est  # matrix multiplication

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return(data.frame(id, df_actual_pred))

$$

LANGUAGE 'plr';



-- Execute PL/R Prediction UDF

DROP TABLE IF EXISTS out_coef_predict;

CREATE TABLE out_coef_predict AS (

SELECT sex, (plr_lm_coef_predict(c.rings, c.s_weight, c.diameter, c.coef_est)).*

FROM (

SELECT a.*, b.coef_est

FROM test_array a, 

(SELECT sex, array_agg(coef_est) AS coef_est FROM lm_abalone_model_coef GROUP BY sex) b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);



-- Compare 'actual' vs. 'predicted'

SELECT * FROM out_coef_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;







  (7) 회귀모형 자체를 Serialize 해서 DB에 저장하고, Unserialize 해서 예측하기


위의 (4)번~(6번) 까지는 적합된 회귀모형의 회귀계수와 통계량을 반환하고, 이를 이용해 예측을 해보았다면, 이번에는 


- (4-2) 적합된 회귀모형 자체(model itself)를 Serialize 하여 DB에 저장하고 (인코딩)

- 이를 DB에서 읽어와서 Unserialize 한 후 (디코딩), 예측하기

- 단, 이때 예측값의 95% 신뢰구간 (95% confidence interval) 도 같이 반환하기


를 해보겠습니다. 



--------------------------------------------------------------------

-- (2) PL/R : Linear Model --> Serialize --> Deserialize --> Predict

--------------------------------------------------------------------


-- PL/R User Defined Function Definition

DROP FUNCTION IF EXISTS plr_lm_model(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_model(

    rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS bytea -- serialized model as a byte array

AS

$$

lr_model <- lm(rings ~ s_weight + diameter)

return (serialize(lr_model, NULL))

$$

LANGUAGE 'plr';


-- Execution of Linear Regression PL/R 

DROP TABLE IF EXISTS lm_abalone_model;

CREATE TABLE lm_abalone_model AS (

SELECT sex, plr_lm_model(rings, s_weight, diameter) AS serialized_model

FROM abalone_array

) DISTRIBUTED BY (sex);



-- We can not read serialized model

SELECT * FROM lm_abalone_model;





DROP TYPE IF EXISTS lm_predicted_interval_type CASCADE;

CREATE TYPE lm_predicted_interval_type AS (

id int

, actual float

, pred float

, lwr float

, upr float

);


-- PL/R function to read a serialized PL/R model

DROP FUNCTION IF EXISTS plr_lm_model_predict(float8[], float8[], float8[], bytea);

CREATE OR REPLACE FUNCTION plr_lm_model_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, serialized_model bytea

) RETURNS SETOF lm_predicted_interval_type 

AS

$$

model <- unserialize(serialized_model)

actual <- rings # y

X <- data.frame(s_weight, diameter) # new data X

predicted <- predict(model, newdata = X, interval = "confidence")

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return (data.frame(id, df_actual_pred))

$$

LANGUAGE 'plr';



-- Predict

DROP TABLE IF EXISTS out_model_predict;

CREATE TABLE out_model_predict AS (

SELECT sex, (plr_lm_model_predict(c.rings, c.s_weight, c.diameter, c.serialized_model)).*

FROM (

SELECT a.*, b.serialized_model

FROM test_array a, lm_abalone_model b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);


SELECT * FROM out_model_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;




[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측은 https://rfriend.tistory.com/533 를 참고하세요. 



[References]


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

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



Posted by R Friend Rfriend

댓글을 달아 주세요

이번 포스팅에서는 PostgreSQL, Greenplum Database를 이용해서 시계열 데이터를 특정 시간 단위 구간(예: 10분 단위, 1시간 단위, 1일 단위, 1달 단위, 1년 단위 등) 별로 집계/ 요약하는 방법을 소개하겠습니다. 


(* python pandas로 특정 시간 단위 구간별로 시계열 데이터를 집계하는 방법은 https://rfriend.tistory.com/495 참조)


-- 사전 준비 작업 --

(1) '년-월-일' date dimension table 만들기

(2) '시간:분' time dimension table 만들기

(3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기

(4) 예제 시계열 데이터(time series) table 만들기

    ==> '날짜-시간' 기준정보를 '시계열 거래 데이터'에 merge 하기


-- 10분 단위 구간별로 시계열 데이터 집계/ 요약하기 --

(5) 10분 단위 구간별로 첫번째 행 값(first row value), 마지막 행 값(last row value) 구하기

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기 

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중위수(median), 3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

(8) 10분 단위 구간별로 평균(mean), 표본 분산(sample variance), 표본 표준편차(sample standard deviation) 구하기

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)



  (1) '년-월-일' date dimension table 만들기


날짜(년-월-일)를 기준으로 년(year), 분기(quarter), 월(month), 주(week), 일(day), 공휴일(holiday) 등의 다양한 관점의 group by 할 수 있는 구분자를 생성해놓은 테이블을 만들어보겠습니다. 날짜(년-월-일) 관련 기준 정보, 매핑 테이블이라고 보면 되겠습니다. 


아래 예제는 2019년 12월1일 ~ 2019년 12월 31일 까지의 31일 기간의 날짜 데이터 기준정보를 만들었습니다. 

만약 2019년 1월1일~2019년 12월 31일 까지의 365일 전체의 날짜 데이터 기준정보를 만들고 싶다면 from 절을 아래와 같이 수정해주면 됩니다. 


[ 2019년 1월 1일~2019년 12월 31일 (365일) 기간 일 별 기준정보 테이블 만드는 FROM 절 generate query ]


FROM (

SELECT '2019-01-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 364) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

     ) DQ



아래의 postgresql wiki 페이지의 sql query를 참고하였습니다. 

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions



------------------------

-- date dimension table

------------------------

-- create 'dim_date' table

DROP TABLE IF EXISTS dim_date;

CREATE TABLE dim_date (

date DATE NOT NULL, 

year INT NOT NULL, 

month INT NOT NULL, 

month_nm VARCHAR(9) NOT NULL, 

day INT NOT NULL, 

day_of_year INT NOT NULL, 

weekday_nm VARCHAR(9) NOT NULL, 

calender_week INT NOT NULL, 

formatted_date CHAR(12) NOT NULL, 

quartal CHAR(2) NOT NULL, 

year_quartal CHAR(7) NOT NULL, 

year_month CHAR(7) NOT NULL, 

year_calendar_week CHAR(7) NOT NULL, 

weekend VARCHAR(7) NOT NULL, 

holiday_korea VARCHAR(20)     NOT NULL, 

period VARCHAR(20)     NOT NULL, 

cwstart DATE NOT NULL, 

cwend DATE NOT NULL, 

month_start DATE NOT NULL, 

month_end DATE NOT NULL

);


-- create index on 'date'

CREATE INDEX dim_date_date_idx ON dim_date(date);


-- insert generate date dimension data (from 2019-01-01 to 2019-12031) into 'dim_date' table

INSERT INTO dim_date (

SELECT

datum AS date,

EXTRACT(YEAR FROM datum)::INT AS year,

EXTRACT(MONTH FROM datum)::INT AS month,

-- Localized month name

to_char(datum, 'TMMonth') AS month_nm,

EXTRACT(DAY FROM datum)::INT AS day,

EXTRACT(doy FROM datum)::INT AS day_of_year,

-- Localized weekday

to_char(datum, 'TMDay') AS weekday_nm,

-- ISO calendar week

EXTRACT(week FROM datum)::INT AS calendar_week,

to_char(datum, 'dd. mm. yyyy') AS formatted_date,

'Q' || to_char(datum, 'Q') AS quartal,

to_char(datum, 'yyyy/"Q"Q') AS year_quartal,

to_char(datum, 'yyyy/mm') AS year_month,

-- ISO calendar year and week

to_char(datum, 'iyyy/IW') AS year_calendar_week,

-- Weekend

CASE WHEN EXTRACT(isodow FROM datum) IN (6, 7) THEN 'Weekend' 

ELSE 'Weekday' END 

AS weekend,

-- holidays for Korea in year 2019

    CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0205', '0206', '0207', '0301', '0505', '0512', 

                                         '0606', '0815', '0912', '0913', '0914', '1003', '1009', '1225')

THEN 'Holiday' ELSE 'No holiday' END

AS holiday_korea,

-- Some periods of the year, adjust for your organisation and country

CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831' THEN 'Summer break'

    WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225' THEN 'Christmas season'

    WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106' THEN 'Winter break'

ELSE 'Normal' END

AS period,

-- ISO start and end of the week of this date : from monday to sunday

datum + (1 - EXTRACT(isodow FROM datum))::INTEGER AS CWStart, -- monday

datum + (7 - EXTRACT(isodow FROM datum))::INTEGER AS CWEnd,   -- sunday

-- Start and end of the month of this date

datum + (1 - EXTRACT(DAY FROM datum))::INTEGER AS month_start,

((datum + (1 - EXTRACT(DAY FROM datum))::INTEGER + '1 month'::INTERVAL)::DATE - '1 day'::INTERVAL)::DATE AS month_end

FROM (

SELECT '2019-12-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 30) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

     ) DQ

ORDER BY 1);



SELECT * FROM dim_date ORDER BY date;






  (2) '시간:분' time dimension table 만들기


시간('시간:분')을 기준으로 시간(time), 분(minute), 10분 단위 구간(time_span_min_10), 1시간 단위 구간(time_span_hour_1), 일 구분 범주(day_category) 등으로 구분해서 집계/요약할 수 있는 구분자, 기준 정보, 매핑 테이블을 만들어보겠습니다. 


* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions



------------------------

-- time dimension table

------------------------

-- create dimension table 'dim_time'

DROP TABLE IF EXISTS dim_time;

CREATE TABLE dim_time (

time_of_day CHAR(6) NOT NULL,

hour INT NOT NULL, 

minute INT NOT NULL,

time_span_min_10 CHAR(15) NOT NULL, 

time_span_hour_1 CHAR(15) NOT NULL, 

day_category VARCHAR(15) NOT NULL

);


-- create index on 'time_of_day'

CREATE INDEX dim_time_time_of_day_idx ON dim_time(time_of_day);


-- insert generated time_of_day data into 'dim_time' table

INSERT INTO dim_time (

SELECT 

TO_CHAR(MINUTE, 'hh24:mi') AS time_of_day,

-- Hour of the day (0 - 23)

EXTRACT(HOUR FROM MINUTE)::INTEGER AS hour, 

-- Minute of the day (0 - 1439)

(EXTRACT(HOUR FROM MINUTE)*60 + EXTRACT(MINUTE FROM MINUTE))::INT AS minute,

-- Extract and format 10 minutes time span

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10 || 'minutes')::INTERVAL, 'hh24:mi') ||

' – ' ||

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10 || 'minutes')::INTERVAL + '10 minutes'::INTERVAL, 'hh24:mi')

AS time_span_min_10, 

-- Extract and format 1 hour time span

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60 || 'minutes')::INTERVAL, 'hh24:mi') ||

' – ' ||

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60 || 'minutes')::INTERVAL + '60 minutes'::INTERVAL, 'hh24:mi')

AS time_span_hour_1, 

-- Names of day periods

CASE WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '06:00' AND '08:59' THEN 'morning'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '09:00' AND '11:59' THEN 'AM'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '12:00' AND '17:59' THEN 'PM'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '18:00' AND '21:59' THEN 'evening'

    ELSE 'night'

END AS day_category

FROM (SELECT '00:00'::TIME + (SEQUENCE.MINUTE || ' minutes')::INTERVAL AS MINUTE

FROM generate_series(0,1439) AS SEQUENCE(MINUTE)

GROUP BY SEQUENCE.MINUTE

     ) DQ

ORDER BY 1

);


SELECT * FROM dim_time ORDER BY time_of_day LIMIT 60;

 

 



  (3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기


(1)번에서 만든 dim_date 테이블과 (2)번에서 만든 dim_time 테이블을 cross join 하여 'dim_date_time' 테이블을 만들어보겠습니다. 


31일 (dim_date) * 24시간 * 60분 (dim_time) = 44,640 개의 전체 행 (total number of rows) 을 가지는 '년-월-일 시간:분' (dim_date_time) 기준정보 매핑 테이블을 가지고 이제 년 ~ 분 단위를 넘나들면서 원하는 시간 단위 구간별로 시계열 데이터를 집계/ 요약할 수 있습니다


앞으로 join 의 기준이 될 '년-월-일 시간:분'(dt) 칼럼에 대해서는 빠른 join을 위해서 index 를 생성해주었습니다. 



---------------------------------------

-- cross join b/w dim_date and dim_time

---------------------------------------

DROP TABLE IF EXISTS dim_date_time;

CREATE TABLE dim_date_time AS (

SELECT 

to_char((dim_date.date || ' ' || dim_time.time_of_day)::timestamp, 'yyyy-mm-dd hh24:mi') AS dt

, dim_date.*

, dim_time.*

FROM dim_date

CROSS JOIN dim_time

);


-- create index on 'dt'

CREATE INDEX dim_date_time_dt_idx ON dim_date_time(dt);



SELECT * FROM dim_date_time ORDER BY dt LIMIT 5;



SELECT COUNT(*) FROM dim_date_time;

44640





  (4) 예제 시계열 데이터(time series) table 만들기 ==> '날짜-시간' 기준 정보 merge 하기


간단한 예제로 '년-월-일 시간:분'(dt), 가격(price), 수량(amt) 의 3개 칼럼을 가지는 거래 시계열 데이터 테이블을 만들어보겠습니다. 



---------------------

-- Transaction table

---------------------

-- create ts table

DROP TABLE IF EXISTS ts;

CREATE TABLE ts (

dt  TEXT NOT NULL

, price NUMERIC NOT NULL 

, amt INT NOT NULL

);


-- insert transaction data into ts table

INSERT INTO ts VALUES 

('2019-12-19 00:00', 12, 4), 

('2019-12-19 00:02', 21, 2),

('2019-12-19 00:04', 41, 1),

('2019-12-19 00:06', 79, 4),

('2019-12-19 00:08', 61, 2),

('2019-12-19 00:10', 81, 1),

('2019-12-19 00:12', 24, 3),

('2019-12-19 00:14', 62, 1),

('2019-12-19 00:16', 76, 3),

('2019-12-19 00:18', 63, 1),

('2019-12-19 00:20', 95, 2),

('2019-12-19 00:22', 82, 1),

('2019-12-19 00:24', 82, 3),

('2019-12-19 00:26', 70, 1),

('2019-12-19 00:28', 30, 4),

('2019-12-19 00:30', 33, 1),

('2019-12-19 00:32', 22, 2),

('2019-12-19 00:34', 77, 3),

('2019-12-19 00:36', 58, 3),

('2019-12-19 00:38', 96, 3), 

('2019-12-19 09:02', 10, 2),

('2019-12-19 09:08', 50, 3),

('2019-12-19 14:04', 20, 1),

('2019-12-19 16:00', 10, 5),

('2019-12-19 21:00', 30, 4)

;


SELECT * FROM ts ORDER BY dt;





다음으로, '시계열 거래 데이터'(ts table)에 '날짜-시간 기준정보'(dim_date_time table) 를 '년-월-일 시간:분'(dt) 칼럼을 기준으로 병합(left outer join)해보겠습니다. 이제 특정 시간 단위 구간별로 집계, 요약할 수 있는 데이터 준비가 되었습니다. 


※ 만약 '시계열 거래 데이터'에 특정 날짜-시간대에 값이 존재하던지 말던지 간에 동일한 특정 시간 단위 구간별로 값을 집계, 요약하고자 한다면, (즉, '시계열 거래 데이터'가 없으면 NULL이나 '0'으로 집계) LEFT OUTER JOIN 할 때 왼쪽에 '날짜-시간 기준정보'(dim_date_time) 테이블을 놓고, 오른쪽에 '시계열 거래 데이터'(ts) 테이블을 놓고 left outer join 을 해주어야 합니다. 



------------------------------------

-- merge 'date-time' dimension table

------------------------------------

DROP TABLE IF EXISTS ts_dt;

CREATE TABLE ts_dt AS (

SELECT 

a.*

, b.date

, b.time_span_min_10

, b.time_span_hour_1

, b.day_category

FROM ts a 

LEFT OUTER JOIN dim_date_time b ON a.dt = b.dt

);


SELECT * FROM ts_dt ORDER BY dt LIMIT 5;





  (5) 10분 단위 구간별로 첫번째 행 값(first-row value), 마지막 행 값(last row value) 구하기


시계열 데이터를 시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별로 첫번째 행의 값(first row's value)을 구하는 방법에는 두가지가 있습니다. 


첫번째 방법은 ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) 의 window function을 사용하는 것입니다. (오름차순 정렬 order by dt)


두번째 방법은 DISTINCT ON (date, time_span_min_10) 함수를 사용하는 것입니다. 



-- (1) first, last by time_span_min_109

-- (1-1-1) way 1: first using ROW_NUMBER()

WITH time_span_min_10_tmp AS (

SELECT 

date

, time_span_min_10

, price

, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) AS row_num

FROM ts_dt

)

SELECT 

date

, time_span_min_10

, price AS price_10m_first

FROM time_span_min_10_tmp

WHERE row_num = 1

;


-- (1-1-2) way 2: first using DISTINCT ON

SELECT DISTINCT ON (date, time_span_min_10)

date

, time_span_min_10

, price AS price_10m_first

FROM ts_dt

ORDER  BY date, time_span_min_10, dt, price;


 



시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별 마지막 행의 값(last row's value)을 구하는 두가지 방법은 위에서 소개한 첫번째 행의 값을 구하는 방법에서 정렬 기준을 정반대로 해서, 내림차순 정렬(ORDER BY dt DESC) 을 해주면 됩니다. 



-- (1-2-1) way 1: last using ROW_NUMBER()

WITH time_span_min_10_tmp AS (

SELECT 

date

, time_span_min_10

, price

, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt DESC) AS row_num

FROM ts_dt

)

SELECT 

date

, time_span_min_10

, price AS price_10m_last

FROM time_span_min_10_tmp

WHERE row_num = 1

;


-- (1-2-2) way 2: last using DISTINCT ON

SELECT 

DISTINCT ON (date, time_span_min_10)

date

, time_span_min_10

    , price AS price_10m_last

FROM ts_dt

ORDER  BY date, time_span_min_10, dt DESC, price;

 




  (6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기 


10분 단위 구간(10 minutes time span) 별로 가격(price) 칼럼의 합(sum)을 먼저 구하고, ==> 시간 순서대로 정렬된 상태에서 10분 단위 구간별로 SUM() OVER () window function을 사용해서 누적 합(cumulative sum)을 구해보겠습니다. 



-- (2) sum, cumulative sum

-- sum, cumulative sum by time_span_min_10

WITH price_10m_sum_tmp AS (

SELECT 

date

, time_span_min_10

, sum(price) AS price_10m_sum

FROM ts_dt

GROUP BY 1, 2

)

SELECT 

date

, time_span_min_10

, price_10m_sum

, SUM(price_10m_sum) 

OVER ( ORDER BY time_span_min_10 

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 

AS price_10m_cumsum 

FROM price_10m_sum_tmp

;

 




집계/ 요약을 하는 시간 단위 구간을 위의 10분에서 이번에는 1 시간 (1 hour time span) 단위 구간별 변경해서 가격 합(sum)과 가격 누적합(cumulative sum)을 구해보겠습니다. 위의 (3)번에서 '날짜-시간 기준정보 테이블'(dim_date_time table)에서 '1시간 단위 구간' 칼럼 (time_span_hour_1) 을 만들어놓았었기 때문에 group by 집계/ 요약하는 기준이 되는 칼럼을 'time_span_min_10'을 'time_span_hour_1'으로 변경해주기만 하면 됩니다. 



-- sum, cumulative sum by time_span_hour_1

WITH price_1h_sum_tmp AS (

SELECT 

date

, time_span_hour_1

, sum(price) AS price_1h_sum

FROM ts_dt

GROUP BY 1, 2

)

SELECT 

date

, time_span_hour_1

, price_1h_sum

, SUM(price_1h_sum) 

OVER ( ORDER BY time_span_hour_1

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

AS price_1h_cumsum

FROM price_1h_sum_tmp;





  (7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중앙값(median), 

       3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기


최소값은 min(), 최대값은 max(), 1사분위수/중앙값/3사분위수는 percentile_disc(), 범위는 max() - min() 함수를 이용해서 time_span_min_10 으로 group by 집계, 요약하였습니다. 


1/2/3 사분위수(quantile) 구하는 percentile_disc(0.25 / 0.5 / 0.75) within group (order by price) 함수를 사용해서 IQR ( = q3 - q1) 구할 수도 있습니다. 



-- (3) min, q1, median, q3, max, range by time_span_min_10

SELECT 

date 

, time_span_min_10

, MIN(price) AS price_10m_min

, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY price) AS price_10m_q1

, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_10m_median

, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY price) AS price_10m_q3

, MAX(price) AS price_10m_max

, MAX(price) - MIN(price) AS price_10m_range

FROM ts_dt

GROUP BY 1, 2

ORDER BY 1, 2;


 




  (8) 10분 단위 구간별로 평균(mean), 분산(variance), 표준편차(standard deviation) 구하기


10분 단위 구간(10 minutes time span) 별로 표본 분산(sample variance)은 variance() 함수, 표본 표준편차(sample standard deviation)은 stddev() 함수를 이용하여 구하였습니다. 


10분 단위 구간별로 만약 행의 개수가 1개만 있는 경우 분산, 표준편차를 구할 수 없으므로 NULL 값을 반환하게 되는데요,  이런 경우 coalesce() 를 사용해서 '0'으로 채워주었습니다. 



-- (4) mean, variance, standard deviation by time_span_min_10

SELECT 

date 

, time_span_min_10

, AVG(price) AS price_10m_mean

, COALESCE(VARIANCE(price), 0) AS price_10m_var

, COALESCE(STDDEV(price), 0) AS price_10m_stddev

FROM ts_dt

GROUP BY 1, 2

ORDER BY 1, 2;






  (9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)




-- (5) amount-weighted average price

WITH price_mult_amt_tmp AS (

SELECT 

dt

, date 

, time_span_min_10

, amt

, (price * amt) AS price_mult_amt

FROM ts_dt

)

SELECT 

date 

, time_span_min_10

, ROUND((sum(price_mult_amt) / sum(amt)), 2) AS price_10m_amount_weighted_avg

FROM price_mult_amt_tmp

GROUP BY 1, 2

ORDER BY 1, 2;





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

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





Posted by R Friend Rfriend

댓글을 달아 주세요

제가 2019년 1월 30일에 Pivotal 의 Data Science Webinar 에서 발표했었던 "오픈소스 그림플럼 DB와 아파치 MADlib을 활용한 그래프 분석, 네트워크 분석 (Graph analytics with Greenplum and Apache MADlib)" 자료를 공유합니다. 


[ Agenda ]

1. Why Graph Analytics?

2. What is Graph Analytics?

3. Graph Analytics with Apache MADlib on Greenplum in parallel


presented by Hongdon Lee, Pivotal Senior Data Scientist


[ 파일 첨부: Graph Analytics with Greenplum and Apache MADlib ]

Pivotal_Graph_Analytics_w_MADlib_GPDB_20190130.pdf




개별 단위별 객체, 대상만을 놓고 나누어 분석하는 환원주의(Reductionism: Divide and conquer) 대비 전체를 하나로 놓고 관계와 연결에 주목해서 분석하면 새로운 인사이트를 얻을 수 있다는 전체론 관점(Holism: Everything has to be understood in relation to the whole)의 분석 기법으로 그래프 이론(graph theory)네트워크 분석(network analysis)을 소개하는 자료입니다. 


그래프 분석 시 도전 사항으로 복잡도가 매우 높아 연산 부하가 크고 분석이 불가능하거나 시간이 오래 걸린다는 점입니다. 이를 해결할 수 있는 대안으로 MPP(Massively Parallel Processing) 아키텍처 기반의 오픈소스 Greenplum DBApache MADlib을 이용한 그래프, 네트워크 분석의 In-Database 병렬 처리 분석을 소개하였습니다. 


Webinar 발표할 때 재미를 더하기 위해서 슬라이드 중간 중간에 제 사진도 좀 넣어보았는데요, 이렇게 파일 공유하려니 좀 쑥스럽기도 하네요. ^^;  Webinar 발표할 때는 우리나라말로 했었는데요, 글로벌 데이터 팀 구성원들한테도 공유할 생각으로 슬라이드는 영어로 만들었습니다. 중간에 수식도 많은데 영어라서 눈에 잘 안들어온다는 피드백이 있었는데요, 양해 바랍니다. ^^;;; 


알고리즘 세부 소개하는 부분은 위키피디아를 많이 참고하였습니다. 


저는 실제 프로젝트 하면서 그래프 / 네트워크 분석이 제공할 수 있는 인사이트의 유용함을 경험하기도 했구요, Greenplum DB 에서 MADlib으로 분석하면서 In-DB parallel processing의 강력함을 경험했던 지라 기회되면 꼭 한번 소개를 하고 싶었던 주제였습니다. R이나 Python으로 노트북에서 network 분석 공부할 땐 아무 문제 없다가도, 실제 기업이나 공공기관의 수 terabyte, petabyte 급 데이터를 마주하게 되면 당황하기 마련인데요, 이럴 때 사용할 수 있는 방법, 툴입니다. 


주요 장표 몇 장만 아래에 화면 캡쳐한거 소개하자면요, 



  • Everything is connected!




  • Network: Everywhere with Everything, All the time



  • What is Graph Theory?



  • Graph algorithms and measures


  • Tools for Graph Analytics



  • Apache MADlib: Scaleable, In-Database Machine Learning in SQL



  • Apache MADlib: Graph Analytics Functions




그래프/ 네트워크 분석 주제 중에서 Page Rank 에 대한 간단한 예제 SQL 코드와 Graphviz, PyGraphviz를 활용한 네트워크 시각화 코드도 공유합니다. 



[ 그래프 분석 MADlib SQL codes ]

graph_pagerank_madlib.sql


----------------------------------------------

-- Graph Analytics with Greenplum and MADlib

----------------------------------------------


-- CREATE VERTEX TABLE

DROP TABLE IF EXISTS vertex;

CREATE TABLE vertex(

id INTEGER

) DISTRIBUTED RANDOMLY;


INSERT INTO vertex 

VALUES

(0), 

(1), 

(2), 

(3), 

(4),

(5),

(6);


select * from vertex


-- CREATE EDGE TABLE

DROP TABLE IF EXISTS edge;

CREATE TABLE edge(

src INTEGER,

dest INTEGER,

user_id INTEGER

)

DISTRIBUTED BY (user_id);


INSERT INTO edge 

VALUES 

(0, 1, 1), (0, 2, 1), -- user id 1

(0, 4, 1), (1, 2, 1),

(1, 3, 1), (2, 3, 1),

(2, 5, 1), (2, 6, 1),

(3, 0, 1), (4, 0, 1),

(5, 6, 1), (6, 3, 1),

(0, 1, 2), (0, 2, 2), -- user id 2

(0, 4, 2), (1, 2, 2),

(1, 3, 2), (2, 3, 2),

(3, 0, 2), (4, 0, 2),

(5, 6, 2), (6, 3, 2);


select * from edge;



-- (1) Compute the PageRank with All IDs

DROP TABLE IF EXISTS pagerank_out, pagerank_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_out' -- Output table of RageRank

, NULL -- Default damping factor (0.85)

); 


SELECT * FROM pagerank_out ORDER BY pagerank DESC;



-- (2) Compute the PageRank of vertices associated with each user using the grouping feature

DROP TABLE IF EXISTS pagerank_gr_out, pagerank_gr_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_gr_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL -- Default max iterations (100)

, 0.00000001 -- Threshold

, 'user_id'); -- Grouping column name


SELECT * FROM pagerank_gr_out ORDER BY user_id, pagerank DESC;



-- (3) Personalized PageRank of vertices {2, 4}

DROP TABLE IF EXISTS pagerank_pers_out, pagerank_pers_out_summary;

SELECT madlib.pagerank(

'vertex' -- Vertex table

, 'id' -- Vertex id column

, 'edge' -- Edge table

, 'src=src, dest=dest' -- Comma delimited string of edge arguments

, 'pagerank_pers_out' -- Output table of PageRank

, NULL -- Default damping factor (0.85)

, NULL


-- Default max iterations (100)

, NULL -- Default Threshold (1/number of vertices*1000)

, NULL -- No Grouping

, '{2, 4}' -- Personalization vertices

);


SELECT * FROM pagerank_pers_out ORDER BY pagerank DESC;


SELECT * FROM pagerank_pers_out_summary;


 




[ Graphviz 활용한 네트워크 시각화 Python codes ]

NW_visualization.py





#!/usr/bin/env python2

# -*- coding: utf-8 -*-

"""

Created on Tuesday Jau 29 2019

@author: Hongdon Lee

"""

#%% network visualization using Graphviz

import numpy as np

import pandas as pd

import pygraphviz as pgv


def run_query(query):

     import pandas as pd

     import psycopg2 as pg


    # DB Connection

     conn = pg.connect(host='localhost',

                       port='5432', 

                       dbname='gpadmin', 

                       user='gpadmin', 

                       password='pivotal')


     # Get a DataFrame

     query_result = pd.read_sql(query, conn)

     conn.close()

     return query_result



#%% Network Edge Table

query = """

select a.*, b.pagerank 

    from edge a

    left outer join pagerank_out b on a.src = b.id

;

"""


edge_pagerank = run_query(query)


#%% PageRank Values

query = """

select * from pagerank_out;

"""


pagerank_out  = run_query(query)



#%% NW Visualization using Graphviz with different size proportional to PageRank


import pygraphviz as pgv


# Generating the output flow_graph with PyGraphviz

flow_graph = pgv.AGraph(strict=False, directed=True) # directed graph


# Flow Direction(Left to Right, or Top to Bottom)

flow_graph.graph_attr['rankdir'] = 'LR' # from Left to Right


# Node Shape

flow_graph.node_attr['shape'] = 'circle'


# Making node with different size proportional to PageRank

for i in range(len(pagerank_out)):

    label_text = str(pagerank_out.id[i]) + '\n(' + str(pagerank_out.pagerank[i].round(decimals=2)) + ')'

    node_width = pagerank_out.pagerank[i]*10

    node_height = pagerank_out.pagerank[i]*10

    

    flow_graph.add_node(str(pagerank_out.id[i]), 

                        label=label_text, 

                        **{'width': str(node_width), 

                           'height': str(node_height)})


# Adding edge with different color by user_id

colors = ['blue', 'red']

 

for i in range(len(edge_pagerank)):

    if edge_pagerank.user_id[i] == 1:

        color_text = colors[0]

    else:

        color_text = colors[1]

    

    flow_graph.add_edge(str(edge_pagerank.src[i])

                        , str(edge_pagerank.dest[i])

                        , color = color_text)


#----- Finally, Draw the Network Diagram using dot program :-)

flow_graph.draw("/Users/ihongdon/Documents/nw_diagram.png", prog='dot')

 



[ Docker image를 이용해서 Greenplum, MADlib, PL/R, PL/Python 분석 환경 구성하는 방법 (싱글 노드의 로컬 기능 테스트, 공부 용도로)]

https://rfriend.tistory.com/379 

https://hub.docker.com/r/hdlee2u/gpdb-analytics



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


ps. 앤 해서웨이(ANNE HATHAWAY)와 연결해주실 분 계신가요? 6 degrees of separation 의 기적이 저에게도 일어날 수 있으려나요? ^^" 



Posted by R Friend Rfriend

댓글을 달아 주세요

Greenplum DB에 R이나 Python, Perl, Java 등의 Procedural Language Extention을 설치해서 대용량 데이터를 In-Database 분산 병렬 처리, 분석할 수 있습니다. 

 

이번 포스팅에서는 인터넷이 되는 환경에서 다운로드한 R 패키지들을 회사/ 기관 정책상 폐쇄망으로 운영하는 환경에서 Greenplum DB에 설치하는 방법을 소개하겠습니다. 

 

1. Greenplum PL/R Extention (Procedural Language R) 설치 방법

2. Greenplum DB에 R 패키지 설치 방법

 

PL/R on Greenplum Database

 

1. Greenplum PL/R Extention 설치 방법

PL/R 은 procedural language 로서, PL/R Extension을 설치하면 Greenplum DB에서 R 프로그래밍 언어, R 패키지의 함수와 데이터셋을 사용할 수 있습니다. 

 

Greenplum DB에 PL/R 확장 언어 설치 방법은 https://gpdb.docs.pivotal.io/5180/ref_guide/extensions/pl_r.html 를 참고하였습니다. 웹 페이지의 상단에서 사용 중인 Greenplum DB version을 선택해주세요. (아래 예는 GPDB v5.18 선택 시 화면)

 

PL/R 은 패키지 형태로 되어 있으며, Pivotal Network(https://network.pivotal.io/products/pivotal-gpdb)에서 다운로드 할 수 있고, Greenplum Package Manager (gppkg) 를 사용해서 쉽게 설치할 수 있습니다. 

 

Greenplum Package Manager (gppkg) 유틸리티는 Host와 Cluster에 PL/R 과 의존성있는 패키지들을 한꺼번에 설치를 해줍니다. 또한 gppkg는 시스템 확장이나 세그먼트 복구 시에 자동으로 PL/R extension을 설치해줍니다. 

 

Greenplum PL/R Extention 설치 순서는 아래와 같습니다. 

 

(0) 먼저, Greenplum DB 작동 중이고, source greenplum_path.sh 실행,  $MASTER_DATA_DIRECTORY, $GPHOME variables 설정 완료 필요합니다. 

psql에서 Greenplum DB 버전을 확인합니다. 

psql # sql -c “select version;”

 

master host에서 gpadmin 계정으로 작업 디렉토리를 만듭니다.

(예: /home/gpadmin/packages)

 

(1) Pivotal Network에서 사용 중인 Greenplum DB version에 맞는  PL/R Extension을 다운로드 합니다. 

(예: plr-2.3.3-gp5-rhel7-x86_64.gppkg)

 

(2) 다운로드 한 PL/R Extension Package를  scp 나 sftp 를 이용해서 Greenplum DB master host로 복사합니다. (아마 회사 정책 상 DBA만 root 권한에 접근 가능한 경우가 대부분일 것이므로, 그런 경우에는 DBA에게 복사/설치 요청을 하셔야 합니다). 

$ scp plr-2.3.3-gp5-rhel7-x86_64.gppkg root@mdw:~/packages

 

(3) PL/R Extension Package를 gppkg 커맨드를 실행하여 설치합니다. (아래 예는 Linux에서 실행한 예)

$ gppkg -i plr-2.3.3-gp5-rhel7-x86_64.gppkg

 

(4) Greenplum DB를 재실행 합니다.

(GPDB를 껐다가 켜는 것이므로 DBA에게 반드시 사전 통보, 허락 받고 실행 필요합니다!)

$ gpstop -r

 

(5) Source the file $GPHOME/greenplum_path.sh

# source /usr/local/greenplum-db/greenplum_path.sh

 

R extension과 R 환경은 아래 경로에 설치되어 있습니다. 

$ GPHOME/ext/R-2.3.3/

 

(6) 각 데이터베이스가 PL/R 언어를 사용하기 위해서는 SQL 문으로 CREATE LANGUAGE  또는 createlang 유틸리티로 PL/R을 등록해주어야 합니다. (아래는 testdb 데이터베이스에 등록하는 예)

$ createlang plr -d testdb

이렇게 하면 PL/R이 untrusted language 로 등록이 되었습니다. 

 

 

참고로, Database 확인은 psql 로 \l 해주면 됩니다. 

psql # \l

 

 

 

2. Greenplum DB에 R 패키지 설치 방법 (Installing external R packages)

 

(0) 필요한 R 패키지, 그리고 이에 의존성이 있는 R 패키지를 한꺼번에 다운로드 합니다. (=> https://rfriend.tistory.com/441 참조)

 

(1) 다운로드한 R 패키지들을 압축하여 Greenplum DB 서버로 복사합니다. 

 

다운로드한 R 패키지들 조회해보겠습니다. 

[root@mdw /]# find . | grep sp_1.3-1.tar.gz
./home/gpadmin/r-pkg/sp_1.3-1.tar.gz
[root@mdw /]# exit
logout
[gpadmin@mdw tmp]$ cd ~
[gpadmin@mdw ~]$ cd r-pkg
[gpadmin@mdw r-pkg]$ ls -la
total 47032
drwxrwxr-x 2 gpadmin gpadmin    4096 Apr 23 13:17 .
drwx------ 1 gpadmin gpadmin    4096 Apr 23 13:14 ..
-rw-rw-r-- 1 gpadmin gpadmin  931812 Apr 23 12:55 DBI_1.0.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  794622 Apr 23 12:55 LearnBayes_2.15.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  487225 Apr 23 12:55 MASS_7.3-51.3.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1860456 Apr 23 12:55 Matrix_1.2-17.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   31545 Apr 23 12:55 R6_2.4.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 3661123 Apr 23 12:55 Rcpp_1.0.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   21810 Apr 23 12:55 abind_1.4-5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  231855 Apr 23 12:55 boot_1.3-20.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   17320 Apr 23 12:55 classInt_0.3-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   19757 Apr 23 12:55 class_7.3-15.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   73530 Apr 23 12:55 coda_0.19-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  658694 Apr 23 12:55 crayon_1.3.4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   80772 Apr 23 12:55 deldir_0.1-16.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  128553 Apr 23 12:55 digest_0.6.18.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  582415 Apr 23 12:55 e1071_1.7-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  137075 Apr 23 12:55 expm_0.999-4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  347295 Apr 23 12:55 foreign_0.8-71.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1058430 Apr 23 12:55 gdata_2.18.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  758133 Apr 23 12:55 geosphere_1.5-7.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   33783 Apr 23 12:55 gmodels_2.18.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   12577 Apr 23 12:55 goftest_1.1-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  187516 Apr 23 12:55 gtools_3.8.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   45408 Apr 23 12:55 htmltools_0.3.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1758514 Apr 23 12:55 httpuv_1.5.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1052728 Apr 23 12:55 jsonlite_1.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   40293 Apr 23 12:55 later_0.8.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  359031 Apr 23 12:55 lattice_0.20-38.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  200504 Apr 23 12:55 magrittr_1.5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1581592 Apr 23 12:55 maptools_0.9-5.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  915991 Apr 23 12:55 mgcv_1.8-28.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   12960 Apr 23 12:55 mime_0.6.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   79619 Apr 23 12:55 polyclip_1.10-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  106866 Apr 23 12:55 promises_1.0.1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  255244 Apr 23 12:55 rgeos_0.4-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  858992 Apr 23 12:55 rlang_0.3.4.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  639286 Apr 23 12:55 rpart_4.1-15.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 8166770 Apr 23 12:55 sf_0.7-3.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 2991469 Apr 23 12:55 shiny_1.3.2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   24155 Apr 23 12:55 sourcetools_0.1.7.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 3485268 Apr 23 12:55 spData_0.3.0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1133621 Apr 23 12:55 sp_1.3-1.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 2861828 Apr 23 12:55 spatstat.data_1.4-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin   65106 Apr 23 12:55 spatstat.utils_1.13-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 6598638 Apr 23 12:55 spatstat_1.59-0.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin 1227625 Apr 23 12:55 spdep_1.1-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin    2518 Apr 23 12:55 tensor_1.5.tar.gz
-rwxr-xr-x 1 gpadmin gpadmin    2326 Apr 23 13:17 test.sh
-rw-rw-r-- 1 gpadmin gpadmin  917316 Apr 23 12:55 units_0.6-2.tar.gz
-rw-rw-r-- 1 gpadmin gpadmin  564589 Apr 23 12:55 xtable_1.8-4.tar.gz

 

R 패키지들이 들어있는 폴더를 r-pkg.tar 이름으로 압축해보겠습니다. 

[gpadmin@mdw r-pkg]$ pwd
/home/gpadmin/r-pkg
[gpadmin@mdw r-pkg]$ cd ..
[gpadmin@mdw ~]$ tar cf r-pkg.tar r-pkg
[gpadmin@mdw ~]$ ls -lrt
total 47000
drwxr-xr-x 2 gpadmin gpadmin     4096 Aug 13  2018 gpconfigs
drwxr-xr-x 2 root    root        4096 Mar 22 07:02 gppkgs
drwxrwxr-x 1 gpadmin gpadmin     4096 Apr 23 12:48 gpAdminLogs
-rw-rw-r-- 1 gpadmin gpadmin      983 Apr 23 13:14 pkg.r
drwxrwxr-x 2 gpadmin gpadmin     4096 Apr 23 13:17 r-pkg
-rw-rw-r-- 1 gpadmin gpadmin 48107520 Apr 25 01:52 r-pkg.tar

 

명령 프롬프트 창에서 GPDB Docker 에서 압축한 파일을 로커로 복사 후에 ==> 다른 GPDB 서버로 복사하고 압축을 풀어줍니다. (저는 Docker 환경에서 하다보니 좀 복잡해졌는데요, 만약 로컬에서 R 패키지 다운받았으면 로컬에서 바로 GPDB 서버로 복사하면 됩니다. 압축한 R패키지 파일을 scp로 복사하거나 sftp로 업로드할 수 있으며, 권한이 없는 경우 DBA에게 요청하시구요.) 아래는 mdw에서 root 계정으로 시작해서 다운로드해서 압축한 R 패키지 파일을 scp로  /root/packages 경로에 복사하는 스크립트입니다. 

-- GPDB Docker에서 압축한 파일을 로컬로 복사하기
-- 다른 명령 프롬프트 창에서 복사해오고 확인하기

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ docker cp gpdb-ds:/home/gpadmin/r-pkg.tar /Users/ihongdon/Downloads/r-pkg.tar
ihongdon-ui-MacBook-Pro:Downloads ihongdon$
ihongdon-ui-MacBook-Pro:Downloads ihongdon$
ihongdon-ui-MacBook-Pro:Downloads ihongdon$ ls -lrt
-rw-rw-r--   1 ihongdon  staff  48107520  4 25 10:52 r-pkg.tar

-- 다른 GPDB 서버로 복사하기
ihongdon-ui-MacBook-Pro:Downloads ihongdon$ scp r-pkg.tar root@mdw:~/package

-- 압축 해제
$ tar -xvf r-pkg.tar

 

Greenplum DB에 R 패키지를 설치하려면 모든 Greenplum 서버에 R이 이미 설치되어 있어야 합니다. 

여러개의 Segments 에 동시에 R 패키지들을 설치해주기 위해서 배포하고자 하는 host list를 작성해줍니다. 

# source /usr/local/greenplum-db/greenplum_path.sh
# vi hostfile_packages

 

vi editor 창이 열리면 아래처럼 R을 설치하고자 하는 host 이름을 등록해줍니다. (1개 master, 3개 segments 예시)

-- vi 편집창에서 --
smdw
sdw1
sdw2
sdw3
~
~
~
esc 누르고 :wq!

 

명령 프롬프트 창에서 mdw로 부터 root 계정으로 각 노드에 package directory 를 복사해줍니다. 

# gpscp -f hostfile_packages -r packages =:/root

 

hostfile_packages를 복사해서 hostfile_all 을 만들고, mdw를 추가해줍니다. 

-- copy
$ cp hostfile_packages  hostfile_all

-- insert mdw
$ vi hostfile_all
-- vi 편집창에서 --
mdw
smdw
sdw1
sdw2
sdw3
~
~
~
esc 누르고 :wq!

 

mdw를 포함한 모든 서버에 R packages 를 설치하는 'R CMD INSTALL r_package_name' 명령문을 mdw에서 실행합니다. (hostfile_all 에 mdw, smdw, sdw1, sdw2, sdw3 등록해놓았으므로 R이 모든 host에 설치됨)

$ pssh -f hostfile_all -v -e 'R CMD INSTALL ./DBI_1.0.0.tar.gz 
LearnBayes_2.15.1.tar.gz MASS_7.3-51.3.tar.gz Matrix_1.2-17.tar.gz 
R6_2.4.0.tar.gz Rcpp_1.0.1.tar.gz 
abind_1.4-5.tar.gz boot_1.3-20.tar.gz classInt_0.3-1.tar.gz
class_7.3-15.tar.gz coda_0.19-2.tar.gz crayon_1.3.4.tar.gz
deldir_0.1-16.tar.gz digest_0.6.18.tar.gz e1071_1.7-1.tar.gz
expm_0.999-4.tar.gz foreign_0.8-71.tar.gz gdata_2.18.0.tar.gz
geosphere_1.5-7.tar.gz gmodels_2.18.1.tar.gz goftest_1.1-1.tar.gz
gtools_3.8.1.tar.gz htmltools_0.3.6.tar.gz httpuv_1.5.1.tar.gz
jsonlite_1.6.tar.gz later_0.8.0.tar.gz lattice_0.20-38.tar.gz
magrittr_1.5.tar.gz maptools_0.9-5.tar.gz mgcv_1.8-28.tar.gz
mime_0.6.tar.gz polyclip_1.10-0.tar.gz promises_1.0.1.tar.gz
rgeos_0.4-2.tar.gz rlang_0.3.4.tar.gz rpart_4.1-15.tar.gz
sf_0.7-3.tar.gz shiny_1.3.2.tar.gz sourcetools_0.1.7.tar.gz
spData_0.3.0.tar.gz sp_1.3-1.tar.gz spatstat.data_1.4-0.tar.gz
spatstat.utils_1.13-0.tar.gz spatstat_1.59-0.tar.gz spdep_1.1-2.tar.gz
tensor_1.5.tar.gz units_0.6-2.tar.gz xtable_1.8-4.tar.gz'

 

특정 R 패키지를 설치하려고 할 때, 만약 의존성 있는 패키지 (dependencies packages) 가 이미 설치되어 있지 않다면 특정 R 패키지는 설치가 되지 않습니다. 따라서 위의 'R CMD INSTALL r-package-names' 명령문을 실행하면 설치가 되는게 있고, 안되는 것(<- 의존성 있는 패키지가 먼저 설치된 이후에나 설치 가능)도 있게 됩니다. 따라서 이 설치 작업을 수작업으로 반복해서 여러번 돌려줘야 합니다. loop 돌리다보면 의존성 있는 패키지가 설치가 먼저 설치가 될거고, 그 다음에 이전에는 설치가 안되었던게 의존성 있는 패키지가 바로 전에 설치가 되었으므로 이제는 설치가 되고, ...., ....., 다 설치 될때까지 몇 번 더 실행해 줍니다. 

 

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

Posted by R Friend Rfriend

댓글을 달아 주세요

앞의 포스팅에서는 공간지리 형태의 데이터셋을 import 하는 방법들을 소개하였습니다. 

 

이번 포스팅에서는 PostgreSQL, Greenplum DB의 PostGIS 에서 테이블(Table) 형태로 있는 공간지리 데이터에 대해서 (1) pg_dump로 공간지리 테이블을 백업하기(Backup), (2) pg_restore 로백업한 공간지리 테이블을 다시 불러오기 (Restore) 를 해보겠습니다. 

(* Reference: https://github.com/PacktPublishing/Mastering-PostGIS)

 

create a backup table using pgrestore

 

(1) pg_dump로 공간지리 데이터 테이블 백업하기 (Create a Backup table)

명령 프롬프트 창에서 docker로 Greenplum DB를 실행한 후에, gpadmin 계정으로 들어가서 이미 geometry 포맷으로 만들어두었던 data_import.earthquakes_subset_with_geom 테이블을 pg_dump 를 사용하여 백업해보았습니다. (host, port, user 부분은 각자의 database 설정을 입력하면 됨)

 

 

[gpadmin@mdw tmp]$ pg_dump -h localhost -p 5432 -U gpadmin -t data_import.earthquakes_subset_with_geom -c -F c -v -b -f earthquakes_subset_with_geom.backup gpadmin

pg_dump: reading extensions

pg_dump: identifying extension members

20190417:04:24:25|pg_dump-[INFO]:-reading schemas

pg_dump: reading user-defined tables

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined types

20190417:04:24:25|pg_dump-[INFO]:-reading type storage options

20190417:04:24:25|pg_dump-[INFO]:-reading procedural languages

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined aggregate functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operators

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined external protocols

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator classes

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator families

pg_dump: reading user-defined text search parsers

pg_dump: reading user-defined text search templates

pg_dump: reading user-defined text search dictionaries

pg_dump: reading user-defined text search configurations

20190417:04:24:26|pg_dump-[INFO]:-reading user-defined conversions

20190417:04:24:26|pg_dump-[INFO]:-reading type casts

20190417:04:24:26|pg_dump-[INFO]:-reading table inheritance information

pg_dump: finding extension tables

20190417:04:24:26|pg_dump-[INFO]:-reading rewrite rules

20190417:04:24:26|pg_dump-[INFO]:-finding inheritance relationships

20190417:04:24:26|pg_dump-[INFO]:-reading column info for interesting tables

pg_dump: finding the columns and types of table "earthquakes_subset_with_geom"

20190417:04:24:26|pg_dump-[INFO]:-flagging inherited columns in subtables

20190417:04:24:26|pg_dump-[INFO]:-reading indexes

20190417:04:24:26|pg_dump-[INFO]:-reading constraints

20190417:04:24:26|pg_dump-[INFO]:-reading triggers

pg_dump: reading dependency data

pg_dump: saving encoding = UTF8

pg_dump: saving standard_conforming_strings = on

pg_dump: dumping contents of table earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

 

 

(2) pg_restore 로 백업 테이블 다시 불러오기

이미 테이블로 만들어져 있는 data_import.earthquakes_subset_with_geom 테이블을 삭제한 후에, (1)번에서 백업해둔 데이터를 불러오겠습니다. 

 

테이블을 먼저 삭제해볼께요. 

-- (2) (DBeaver db tool 에서) drop table

DROP TABLE data_import.earthquakes_subset_with_geom;

 

테이블을 삭제하였으니, 이제 다시 (1)번에서 백업해두었던 데이터를 다시 불러와서 테이블을 생성(Restore a Backup table)해보겠습니다. 

-- (3) (명령 프롬프트 창에서) Restore using pg_restore

[gpadmin@mdw tmp]$ pg_restore -h localhost -p 5432 -U gpadmin -v -d gpadmin earthquakes_subset_with_geom.backup

pg_restore: connecting to database for restore

pg_restore: creating TABLE earthquakes_subset_with_geom

pg_restore: restoring data for table "earthquakes_subset_with_geom"

pg_restore: setting owner and privileges for TABLE earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

 

DBeaver db tool에서 백업 테이블을 잘 불어와서 테이블이 생성이 되었는지 확인해보겠습니다. 

-- (4) (DBeaver db tool 에서) 백업 되었는지 조회 확인

SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

 

백업 테이블 불어오기(restore)가 잘 되었네요. 

 

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

Posted by R Friend Rfriend

댓글을 달아 주세요