Greenplum DB는 여려개의 PostgreSQL DB를 합쳐놓은 shared-nothing architecture 의 MPP (Massively Parallel Processing) Database 입니다.  손과 발이 되는 여러개의 cluster nodes에 머리가 되는 Master host 가 조율/조정/지시해가면서 분산하여 병렬로 일을 시키고, 각 cluster nodes의 연산처리 결과를 master host 가 모아서 취합하여 최종 결과를 반환하는 방식으로 일을 하기 때문에 (1) 대용량 데이터도 (2) 매우 빠르게 처리할 수 있습니다. 

 

이번 포스팅에서는 여기에서 한발 더 나아가서, Procedural Language Extension (PL/X) 을 사용하여 Python, R, Java, C, Perl, SQL 등의 프로그래밍 언어를 Greenplum DB 내에서 사용하여 데이터의 이동 없이 분산 병렬처리하는 방법을 소개하겠습니다. 

 

Massively Parallel Processing through PL/Python on Greenplum DB

 

난수를 발생시켜서 만든 가상의 데이터셋을 사용하여 PL/Python으로 Random Forest의 Feature Importance를 그룹별로 분산병렬처리하는 간단한 예를 들어보겠습니다. 

 

(1) 가상의 데이터셋 만들기
   - group: 2개의 그룹

   - X: 각 그룹별 100개의 관측치별 200개의 숫자형 변수를 가지는 가상의 데이터셋

   - y:  y = x1*5.0 + x2*4.5 - x3*4.0 + x4*3.5 + 0.001*random() 의 함수로 부터 만듦. 

(2) PL/Python 함수 정의하기
   : 각 그룹별 Random Forest Regressor를 분산병렬로 훈련시킨 후,
   : 각 그룹별 Random Forest Regressor 모델별 200개의 숫자형 변수별 Feature Importance를 반환

(3) PL/Python 함수 실행하기 

(4) 각 그룹별 변수별 Random Forest 의 Feature Importance 를 조회하기

 

를 해 보겠습니다. 

 

 

 

(1) 가상의 예제 데이터셋 만들기

   - group: 2개의 그룹

   - X: 각 그룹별 100개의 관측치별 200개의 숫자형 변수를 가지는 가상의 데이터셋

   - y:  y = x1*5.0 + x2*4.5 - x3*4.0 + x4*3.5 + 0.001*random() 의 함수로 부터 만듦. 

 

아래의 예제는 In-DB 처리를 염두에 두고, 200개의 숫자형 X 변수들과 한개의 숫자형 y 변수를 DB의 테이블에 "col_nm"이라는 칼럼에는 변수 이름을, "col_val"에는  변수 값을 long form 으로 생성해서 저장해 놓은 것입니다. 

 

나중에 PL/Python의 함수 안에서 pandas 의 pivot_table() 함수를 사용해서 wide form 으로 DataFrame의 형태를 재구조화해서 random forest 를 분석하게 됩니다. 

 

제 맥북에서 도커로 만든 Greenplum 에는 1개의 master node와 2개의 segment nodes 가 있는데요, 편의상 cross join 으로 하나의 데이터셋을 2의 segment nodes에 replication 해서, gp_segment_id 를 group id ('grp')로 부여해서 2개 그룹의 데이터셋(여기 예제에서는 동일한 데이터를 편의상 단순 복제함)을 만들었습니다. 

 

200개의 숫자형 X 변수 중에서 'x1', 'x2', 'x3', 'x4'의 4개 변수만 목표변수(target variable) y와 관련이 있고, 나머지 194개의 설명변수는 관련이 없게끔 y = x1*5.0 + x2*4.5 - x3*4.0 + x4*3.5 + 0.001*random() 함수를 사용해서 y 를 만들었습니다.  뒤에서  Random Forest 의 분석 결과에서 'x1', 'x2', 'x3', 'x4' 변수의 의 Feature Importance 값이 높게 나오는지 살펴보겠습니다. 

 

------------------------------------------------------------------
-- Random Forest's Feature Importance using PL/Python on Greenplum
------------------------------------------------------------------

-- (1) Generate sample data 
-- 2 groups
-- 100 observations(ID) per group
-- X: 200 numeric input variables per observation(ID)
-- y : a numeric target variable by a function of y = x1*5.0 + x2*4.5 - x3*4.0 + x4*3.5 + 0.001*random()
-- distributed by 'grp' (group)

-- (1-1) 100 IDs of observations
drop table if exists id_tmp;
create table id_tmp (
	id integer
);

insert into id_tmp (select * from generate_series(1, 100, 1));


-- (1-2) 4,100 X variables
drop table if exists x_tmp;
create table x_tmp (
	x integer
);

insert into x_tmp (select * from generate_series(1, 200, 1));


-- (1-3) Cross join of ID and Xs
drop table if exists id_x_tmp;
create table id_x_tmp as (
	select * from id_tmp 
	cross join x_tmp 
);

select count(1) from id_x_tmp; -- 20,000



-- (1-4) Generate X values randomly
drop table if exists long_tbl;
create table long_tbl as (
	select 
		a.id as id
		, x
		, 'x'||a.x::text as x_col
		, round(random()::numeric, 3) as x_val 
	from id_x_tmp a
);

select count(1) from long_tbl; -- 20,000



-- (1-5) create y values
drop table if exists y_tmp;
create table y_tmp as (
	select 
		f.*
		, (f.x1*5.0 + f.x2*4.5 - f.x3*4.0 + f.x4*3.5 + 0.001*random()) as y_val
	from (
		select distinct(a.id) as id, x1, x2, x3, x4 from long_tbl as a
		left join (select id, x_val as x1 from long_tbl where x_col = 'x1') b 
            on a.id = b.id
		left join (select id, x_val as x2 from long_tbl where x_col = 'x2') c 
            on a.id = c.id 
		left join (select id, x_val as x3 from long_tbl where x_col = 'x3') d 
            on a.id = d.id 
		left join (select id, x_val as x4 from long_tbl where x_col = 'x4') e 
            on a.id = e.id
	) f
) distributed randomly;



-- (1-6) create table in long format with x and y 
drop table if exists long_x_y;
create table long_x_y as (
	select 
		x.id as id
		, x.x_col as col_nm
		, x.x_val as col_val
	from long_tbl as x
	union all 
		select 	
			y.id as id 
			, 'y' as col_nm 
			, y_val as col_val
		from y_tmp as y 
) distributed randomly;

select count(1) from long_x_y; -- 20,100 (x 20,000 + y 100)
select * from long_x_y order by id, col_nm desc limit 5;
--id  col_nm  col_val
--1	  y	      2.371987572518643
--1	  x99	  0.314
--1	  x98	  0.926
--1	  x97	  0.693
--1	  x96	  0.719

select gp_segment_id, count(1) from long_x_y group by 1 order by 1;
--gp_segment_id  count
--0	             10050
--1	             10050


--  (1-7) replicate the table to all clusters 
--        to make several groups of dataset at each cluster nodes quickly (just for an example)
drop table if exists long_x_y_rep;
create table long_x_y_rep as (
	select a.*, b.grp 
	from long_x_y as a
	cross join (
		select generate_series(1, c.clust_num) as grp
		from (select count(distinct gp_segment_id) as clust_num from long_x_y) c
		) as b -- number of clusters
) distributed by (grp);

select grp, count(1) from long_x_y_rep group by 1 order by 1;
--grp  count
--1	   20100
--2	   20100
select * from long_x_y_rep order by grp, id, col_nm desc limit 5;
--id  col_nm  col_val  grp
--1	  y	      2.371	   1
--1	  x99	  0.314	   1
--1	  x98	  0.926	   1
--1	  x97	  0.693	   1
--1	  x96	  0.719	   1

 

 

 

(2) PL/Python 사용자 정의함수 정의하기

 

- (2-1) composite return type 정의하기

 

PL/Python로 분산병렬로 연산한 Random Forest의 feature importance (또는 variable importance) 결과를 반환할 때 텍스트 데이터 유형의 '변수 이름(col_nm)'과 foat8 데이터 유형의 '변수 중요도(var_impo)' 의 array 형태로 반환하게 됩니다. 반환하는 데이터가 '텍스트'와 'float8' 로 서로 다른 데이터 유형이 섞여 있으므로 composite type 의 return type 을 만들어줍니다. 그리고 PL/Python은 array 형태로 반환하므로 text[], float8[] 과 같이 '[]' 로서 array 형태로 반환함을 명시합니다.  

-- define composite return type
drop type if exists plpy_rf_var_impo_type cascade;
create type plpy_rf_var_impo_type as (
	col_nm text[]
	, var_impo float8[]
);

 

 

 

- (2-2) Random Forest feature importance 결과를 반환하는 PL/Python 함수 정의하기

 

PL/Python 사용자 정의 함수를 정의할 때는 아래와 같이 PostgreSQL의 Procedural Language 함수 정의하는 표준 SQL 문을 사용합니다. 

input data 는 array 형태이므로 칼럼 이름 뒤에 데이터 유형에는 '[]'를 붙여줍니다. 

중간의  $$ ... python code block ... $$ 부분에 pure python code 를 넣어줍니다. 

제일 마지막에 PL/X 언어로서 language 'plpythonu' 으로 PL/Python 임을 명시적으로 지정해줍니다. 

 

create or replace function function_name(column1  data_type1[], column2 data_type2[], ...) 
returns return_type as $$
    ... python code block ...
$$ language 'plpythonu';

 

 

아래 코드에서는 array 형태의 'id', 'col_nm', 'col_val'의 3개 칼럼을 input 으로 받아서 먼저 pandas DataFrame으로 만들어 준 후에, 이를 pandas pivot_table() 함수를 사용해서 long form --> wide form 으로 데이터를 재구조화 해주었습니다. 

 

다음으로, sklearn 패키지의 RandomForestRegressor 클래스를 사용해서 Random Forest 모델을 훈련하고, 'rf_reg_fit.feature_importances_' 를 사용해서 200개의 각 변수별 feature importance 를 리스트로 가져왔습니다. 

 

마지막에 return {'col_nm': col_nm_list, 'var_impo': var_impo} 에서 전체 변수 리스트와 변수 중요도 연산 결과를 array 형태로 반환하게 했습니다. 

 

-- define PL/Python function
drop function if exists plpy_rf_var_impo_func(int[], text[], float8[]);
create or replace function plpy_rf_var_impo_func(
	id_arr int[]
	, col_nm_arr text[]
	, col_val_arr float8[]
) returns plpy_rf_var_impo_type as 
$$
import numpy as np 
import pandas as pd

# making a DataFrame
xy_df = pd.DataFrame({
    'id': id_arr
    , 'col_nm': col_nm_arr
    , 'col_val': col_val_arr
})

# pivoting a table
xy_pvt = pd.pivot_table(xy_df
                        , index = ['id']
                        , columns = 'col_nm'
                        , values = 'col_val'
                        , aggfunc = 'first'
                        , fill_value = 0)

# training RandomForest Regressor
from sklearn.ensemble import RandomForestRegressor

X = xy_pvt[xy_pvt.columns.difference(['y'])]
y = xy_pvt['y']
col_nm_list = X.columns

rf_reg = RandomForestRegressor(n_estimators = 100)
rf_reg_fit = rf_reg.fit(X, y)

# getting feature importances
var_impo = rf_reg_fit.feature_importances_

#var_impo_df = pd.DataFrame(
#    zip(col_nm_list, var_impo)
#    , columns = ['col_nm', 'var_impo'])

# returning the results of feature importances
return {'col_nm': col_nm_list, 'var_impo': var_impo}
    
$$ language 'plpythonu';

 

 

 

(3) PL/Python 함수 실행하기 

 

PL/Python 함수를 실행할 때는 표준 SQL Query 문의 "SELECT group_name, pl_python_function() FROM table_name" 처럼 함수를 SELECT 문으로 직접 호출해서 사용합니다. 

 

PL/Python의 input 으로 array 형태의 데이터를 넣어주므로, 아래처럼 FROM 절의 sub query 에 array_agg() 함수로 먼저 데이터를 'grp' 그룹 별로 array aggregation 하였습니다. 

 

PL/Python 함수의 전체 결과를 모두 반환할 것이므로 (plpy_rf_var_impo_func()).* 처럼 함수를 모두 감싼 후에 ().* 를 사용하였습니다. (실수해서 빼먹기 쉬우므로 유의하시기 바랍니다.)

 

'grp' 그룹별로 분산병렬로 Random Forest 분석이 진행되며 (본 예제에서는 편의상 똑같은 데이터를 cluster nodes별로 복제해서 gp_segment_id를 'grp' ID로 부여했었음), Variable importance 결과를 'grp' 그룹 ID를 기준으로 분산해서 저장(distributed by (grp);)하게끔 해주었습니다.  

 

-- execute PL/Python function
drop table if exists rf_var_impo_result;
create table rf_var_impo_result as (
	select 
		a.grp 
		, (plpy_rf_var_impo_func(
			a.id_arr
			, a.col_nm_arr
			, a.col_val_arr
		)).* 
		from (
			select 
				c.grp 
				, array_agg(c.id::int) as id_arr
				, array_agg(c.col_nm::text) as col_nm_arr
				, array_agg(c.col_val::float) as col_val_arr
			from long_x_y_rep as c
			group by grp
			) a
) distributed by (grp);

 

 

 

(4) 각 그룹별 변수별 Random Forest 의 Feature Importance 조회하기

 

위의 (3)번을 실행해서 나온 결과를 조회하면 아래와 같이 'grp=1', 'grp=2' 별로 각 칼럼별로 Random Forest에 의해 계산된 변수 중요도(variable importance) 가 array 형태로 저장되어 있음을 알 수 있습니다. 

 

select count(1) from rf_var_impo_result; -- 2
select * from rf_var_impo_result order by grp;

 

 

위의 array 형태의 결과는 사람이 눈으로 보기에 불편하므로, unnest() 함수를 써서 long form 으로 길게 풀어서 결과를 조회해 보겠습니다.  이번 예제에서는 두개의 cluster nodes에 똑같은 데이터를 복제해서 2개의 'grp' 그룹을 만들었기 때문에 'x1', 'x2', 'x3', 'x4' 의 순서대로 변수가 중요하고, 각 변수의 중요도도 서로 매우 비슷하게 나왔습니다. (동일한 데이터셋을 이용했고, Random Forest 의 random_state 의 난수가 달라서 결과가 미세하게 차이가 나는 것임)

 

select grp, unnest(col_nm) as col_nm, unnest(var_impo) as var_impo 
from rf_var_impo_result
where grp = 1
order by var_impo desc 
limit 10;
--grp  col_nm  var_impo
--1	   x1	   0.3459452983296161
--1	   x2	   0.30119299843288966
--1	   x3	   0.08850018835034681
--1	   x4	   0.030057941075911896
--1	   x58	   0.007468098458633788
--1	   x23	   0.004818337962023839
--1	   x163	   0.0046182424947942
--1	   x82	   0.004238329094336079
--1	   x16	   0.004224521264320319
--1	   x44	   0.004042401470146082


select grp, unnest(col_nm) as col_nm, unnest(var_impo) as var_impo 
from rf_var_impo_result
where grp = 2
order by var_impo desc
limit 10;
--grp  col_nm  var_impo
--2	   x1	   0.33429700896571224
--2	   x2	   0.3103236270694511
--2	   x3	   0.07724857706838176
--2	   x4	   0.023959240753680774
--2	   x58	   0.005772182598477449
--2	   x158	   0.005009384824710416
--2	   x30	   0.004909571576954872
--2	   x131	   0.004516415319636524
--2	   x44	   0.004308400599289139
--2	   x113	  0.0041422546101633805

 

 

 

만약 여러개 그룹별로 분산병렬로 계산된 동일한 칼럼에 대한 변수 중요도의 평균(average of variable importance)을 보고 싶다면 unnest() 한 후의 결과에 대해서 avg() 함수를 사용해서 평균을 구해주면 되겠습니다. 

 

-- average of variable importances from several clusters
select
	col_nm
	, avg(var_impo) as var_impo
from (
	select 
		grp
		, unnest(col_nm) as col_nm
		, unnest(var_impo) as var_impo 
	from rf_var_impo_result
	) a
group by col_nm
order by var_impo desc
limit 10;

 

 

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

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

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

댓글을 달아 주세요

이번 포스팅에서는 도커 허브(Docker Hub)에서 Greenplum Database(이하 GPDB)에 MADlib, PL/R, PL/Python이 설치된 Docker Image를 내려받아 분석 환경을 구성하는 방법을 소개하겠습니다. 


이번 포스팅에서 소개하는 gpdb-analytics 도커 이미지는 개인이 집이나 회사에서 GPDB로 MADlib, PL/R, PL/Python 사용하면서 테스트해보고 공부할 때 간편하게 사용할 수 있는 용도로 만든 것입니다. 



[사전 준비] Dokcer Install


Docker Image를 이용하여 GPDB+분석툴을 설치할 것이므로, 먼저 Docker Hub (https://hub.docker.com/)에서 회원가입을 하고, https://www.docker.com/products/docker-desktop 사이트에서 자신의 OS에 맞는 Docker를 다운로드 받아 설치하시기 바랍니다. 




단, Windows OS 사용자의 경우는 (1) Windows 10 Professional or Enterprise 64-bit 의 경우 'Docker CE for Windows'를 다운받아 설치하시구요, 그 이전 버전 혹은 Home Edition 버전 Windows OS 이용하시는 분의 경우는 'Docker Toolbox'를 다운로드 받아서 설치하시기 바랍니다. 





[ Docker Hub에서 gpdb-analytics 도커 이미지 내려받아서 GPDB 분석 환경 구성하기 ]


1. Docker Hub에서 gpdb-analytics 도커 이미지 내려받기 (docker pull)





(터미널 사용)

## Docker 이미지 내려 받기

$ docker pull hdlee2u/gpdb-analytics

## Docker 이미지 확인 하기

$ docker images

REPOSITORY               TAG                 IMAGE ID            CREATED             SIZE
centos                     7                   d123f4e55e12        9 months ago        197MB
hdlee2u/gpdb-base        latest              bfe4e63b8e81         2 years ago           1.17GB

hdlee2u/gpdb-analytics  latest           3be773a1a7e1        About a minute ago   4.93GB

 




2. 도커 이미지를 실행하여 Docker Container 를 생성하고, GPDB 분석 환경 시작하기



## Docker 이미지를 실행/ 5432 기본 포트로, ssh 2022포트를 사용하여 접근 가능하도록 Docker 컨테이너 생성

docker run -i -d -p 5432:5432 -p 28080:28080 --name gpdb-ds --hostname mdw hdlee2u/gpdb-analytics /usr/sbin/sshd -D


## Docker 컨테이너 목록 확인

$ docker ps

CONTAINER ID    IMAGE                   COMMAND                   CREATED             STATUS              PORTS                   NAMES

7518fd48450a        575a7d45999d        "/bin/bash"              1 minute ago         Up 6 hours                  0.0.0.0:5432->5432/tcp, 0.0.0.0:28080->28080/tcp   gpdb-ds



## Start GPDB and Use psql


$ docker exec -it gpdb-ds /bin/bash

[root@mdw /]# su - gpadmin
[gpadmin@mdw ~]$ gpstart -a

20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Starting gpstart with args: -a
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.10.2 build commit:b3c02f3acd880e2d676dacea36be015e4a3826d4'
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[WARNING]:-postmaster.pid file exists on Master, checking if recovery startup required
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing recovery startup checks
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Have lock file /tmp/.s.PGSQL.5432 but no process running on port 5432
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-No Master instance process, entering recovery startup mode
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Clearing Master instance lock files
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Clearing Master instance pid file
20180821:04:45:08:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance in admin mode
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Setting new master era
20180821:04:45:10:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing forced instance shutdown
20180821:04:45:12:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance in admin mode
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Obtaining Segment details from master...
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Setting new master era
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Master Started...
20180821:04:45:13:000043 gpstart:mdw:gpadmin-[INFO]:-Shutting down master
20180821:04:45:14:000043 gpstart:mdw:gpadmin-[INFO]:-Commencing parallel segment instance startup, please wait...
...
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Process results...
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Successful segment starts                                            = 2
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Failed segment starts                                                = 0
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-   Skipped segment starts (segments are marked down in configuration)   = 0
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Successfully started 2 of 2 segment instances
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-----------------------------------------------------
20180821:04:45:17:000043 gpstart:mdw:gpadmin-[INFO]:-Starting Master instance mdw directory /data/master/gpseg-1
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-Command pg_ctl reports Master mdw instance active
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-No standby master configured.  skipping...
20180821:04:45:18:000043 gpstart:mdw:gpadmin-[INFO]:-Database successfully started
[gpadmin@mdw ~]$
[gpadmin@mdw ~]$

[gpadmin@mdw ~]$ psql
psql (8.3.23)
Type "help" for help.

gpadmin=# \dn
List of schemas
Name | Owner
--------------------+---------
gp_toolkit | gpadmin
information_schema | gpadmin
madlib | gpadmin
pg_aoseg | gpadmin
pg_bitmapindex | gpadmin
pg_catalog | gpadmin
pg_toast | gpadmin
public | gpadmin
(8 rows)

gpadmin=# \q




3. PGAdmin IV 로 GPDB 연결하기


GPDB 5.x 버전에서 SQL Query를 할 때 PGAdmin IV 를 사용합니다. (GPDB 5.x 버전에서는 PGAdmin III 는 작동하지 않으며, 반대로 GPDB 4.x 버전에서는 PGAdmin IV가 작동하지 않고 대신 PGAdmin III만 작동합니다)


PGAdmin IV 는 https://www.pgadmin.org/download/ 에서 다운로드 하여 설치하면 됩니다. 


  • Host : localhost
  • Port : 5432
  • Maintenance DB : gpadmin
  • Username : gpadmin
  • Password : pivotal
  • Group: Servers
  • Ternel port: 22






4. Jupyter Notebook으로 GPDB 연결하기


4-0. Python 2.7 version의 Anaconda 설치


https://www.anaconda.com/download 에서 자신의 OS에 맞는 Python 2.7 version의 Anaconda를 설치합니다. GPDB 5.x 버전은 Python 2.7 version을 지원합니다. 




Anaconda를 설치하였으면, Anaconda Navigator를 실행한 후 base(root) 환경(즉, python 2.7)에서 'Jupyter Notebook'의 Launch 단추를 눌러서 Jupyter Notebook을 실행합니다. 





터미널을 이용해서 가상환경을 조회, 선택하고 Jupyter Notebook을 실행할 수도 있습니다. 




# 가상 환경 리스트 조회

$ conda env list

 

# 가상 환경 선택 (가상환경 리스트에서 python 2.7 버전 선택, windows의 경우: activate env_name)

$ source activate env_name


# Jupyter Notebook 실행

$ jupyter notebook






4-1. pip install 로 추가로 필요한 Python 패키지 설치하기



(터미널에서)

$ pip install --upgrade pip


$ pip install psycopg2

$ pip install sqlalchemy

$ pip install sql_magic

$ pip install math

$ pip install textwrap

$ pip install os

$ pip install Ipython


$ pip install ipywidgets

$ jupyter nbextension enable --py widgetsnbextension

$ pip install pygraphviz





4-2.  Jupyter Notebook에서 DB Connection 설정하기

4-2-1. Python packages importing



# Common modules

import numpy as np

import pandas as pd

from pandas import DataFrame

from pandas import Series

import sklearn

import math

import textwrap as tw


# For DB Connecton

import psycopg2

from sqlalchemy import create_engine

import sql_magic


# For reproducibility

np.random.seed(2622)


# Directory

import os

 



4-2-2. Visualization Parms Setup



import matplotlib as mpl

import matplotlib.pyplot as plt

import seaborn as sns


# To draw plots in jupyter notebook

#%matplotlib inline

%pylab inline


from pylab import rcParams

rcParams['figure.figsize'] = 12, 8

rcParams['axes.labelsize'] = 14

rcParams['xtick.labelsize'] = 12

rcParams['ytick.labelsize'] = 12


pd.set_option('display.max_columns', None)

pd.set_option('display.max_colwidth', 1000)


# Display

import ipywidgets as widgets

import IPython.display as ipd

from IPython.display import display


# interpret string as markdown

def printmd(string):

    ipd.display(ipd.Markdown(string))


# seaborn style

sns.set(style="darkgrid")




4-2-3. Greenplum Database Connection Setup



# put your own GPDB information

user = 'gpadmin'

password = 'pivotal'

host = 'localhost'

db = 'gpadmin'


connection_string = "postgresql://{user}:{password}@{host}/{db}".\

    format(user=user, 

           password=password, 

           host=host, 

           db=db)

    

conn = psycopg2.connect(connection_string)

cur = conn.cursor()

conn.autocommit = True




# helper function

def query_gpdb(query): 


    cur.execute(query)


    colnames = [desc[0] for desc in cur.description]

    return DataFrame(cur.fetchall(), columns=colnames)

 




4-2-4. sql_magic Setup

https://github.com/pivotal-legacy/sql_magic

sql_magic is Jupyter magic for writing SQL to interact with Greenplum/PostgreSQL database, Hive and Spark. Query results are saved directly to a Pandas dataframe.



# sql_magic package and ext sql_magic to query GPDB

%load_ext sql_magic

#%reload_ext sql_magic


# sql_magic

postgres_engine = create_engine(connection_string)


%config SQL.conn_name = 'postgres_engine'


# '%execsql' for sql execution, 

# '%read_sql' for reading table as a DataFrame format

from IPython.core.magic import (register_line_magic, register_cell_magic, register_line_cell_magic)

@register_cell_magic

def execsql(line, cell):

       _ = postgres_engine.execute(cell)

       return

 




드디어 GPDB를 개인 컴퓨터에서 테스트, 공부용으로 간편하게(? ^^;) 설치하여 보았습니다. 수고 많으셨습니다. 




Jupyter Notebook 에서 sql 매직 언어로 DB를 조회할 수 있습니다. 


# GPDB 버전 확인

%read_sql select version();



# GPDB instance 확인 (하나의 서버에 1개 master, 2개 segment가 설치된 경우임)

%read_sql select * from pg_catalog.gp_segment_configuration 



# MADlib version 확인

%read_sql select madlib.version();



# PL/Languages 확인


sql query가 두 줄 이상일 경우 %%read_sql 처럼 % 두개를 앞에 써줍니다. (sql query 가 한 줄일 경우 %read_sql)

%%read_sql 

select * 

    from pg_catalog.pg_language;



# Table 생성

%%execsql

drop table if exists tmp;

create table tmp (

    id int, 

    var1 varchar(10)

    );




5. Docker Container 중단, 재시작, 작동 중인 컨테이너 목록 확인



## Docker  컨테이너 중단, 재시작, 목록 확인

$ docker stop gpdb-ds

$ docker start gpdb-ds

$ docker ps

CONTAINER ID    IMAGE                   COMMAND                   CREATED             STATUS              PORTS                   NAMES
7518fd48450a        575a7d45999d        "/bin/bash"              2 minutes ago         Up 6 hours                  0.0.0.0:5432->5432/tcp, 0.0.0.0:28080->28080/tcp   gpdb-ds

 



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



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

혹시 아래와 같은 에러가 발생하는 경우 http://rfriend.tistory.com/396 포스팅을 참고하세요. 



Error response from daemon: driver failed programming external connectivity on endpoint gpdb-ds (d519c381360008f0ac0e8d756e97aeb0538075ee1b7e35862a0eaedf887181f1): Error starting userland proxy: Bind for 0.0.0.0:5432 failed: port is already allocated

Error: failed to start containers: gpdb-ds 





Posted by R Friend Rfriend

댓글을 달아 주세요