이번 포스팅에서는 PostgreSQL, Greenplum 에서 Apahe MADlib 의 함수를 사용하여 

(1) 2D array 를 1D array 로 unnest 하기

    (Unnest 2D array into 1D array in PostgreSQL using madlib.array_unnest_2d_to_1d() function)

(2) 1D array 에서 순서대로 원소 값을 indexing 하기

하는 방법을 소개하겠습니다. 

 

 

how to unnest 2D array into 1D array and indexing in PostgreSQL, Greenplum DB

 

먼저, 예제로 사용할 간단한 2D array를 포함하는 테이블을 만들어 보겠습니다. 

 

--------------------------------------------------------------------------------
-- How to unnest a 2D array into a 1D array in PostgreSQL?
-- [reference] http://madlib.incubator.apache.org/docs/latest/array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70
--------------------------------------------------------------------------------

-- Creating a sample 2D array table
DROP TABLE IF EXISTS mat_2d_arr;
CREATE TABLE mat_2d_arr (id int, var_2d int[]);
INSERT INTO mat_2d_arr VALUES 
(1,  '{{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}'),
(2,  '{{10, 11, 12}, {13, 14, 15}, {16, 17, 18}}'),
(3,  '{{19, 20, 21}, {22, 23, 24}, {25, 26, 27}}'), 
(4,  '{{28, 29, 30}, {31, 32, 33}, {34, 35, 36}}');


SELECT * FROM mat_2d_arr ORDER BY id;

--id|var_2d                            |
----+----------------------------------+
-- 1|{{1,2,3},{4,5,6},{7,8,9}}         |
-- 2|{{10,11,12},{13,14,15},{16,17,18}}|
-- 3|{{19,20,21},{22,23,24},{25,26,27}}|
-- 4|{{28,29,30},{31,32,33},{34,35,36}}|

 

 

(1) 2D array 를 1D array 로 unnest 하기

    (Unnest 2D array into 1D array in PostgreSQL using madlib.array_unnest_2d_to_1d() function)

 

Apache MADlib 의 madlib.array_unnest_2d_to_1d() 함수를 사용하면 쉽게 PostgreSQL, Greenplum의 2D array를 1D array 로 unnest 할 수 있습니다. madlib.array_unnest_2d_to_1d() 함수는 'unnest_row_id' 와 'unnest_result' 의 2개 칼럼을 반환하므로, 이들 "2개 칼럼 모두"를 반환하라는 의미로 (madlib.array_unnest_2d_to_1d(var_2d)).* 함수의 처음과 끝부분에 괄호 ()로 묶고 마지막에 아스타리스크(.*) 부호를 붙여주었습니다. ().* 를 빼먹지 않도록 주의하세요. 

 

MADlib 함수를 사용하지 않는다면 직접 PL/SQL 사용자 정의 함수나 또는 PL/Python 이나 PL/R 사용자 정의 함수를 정의하고 실행해야 하는데요, 좀 번거롭고 어렵습니다. 

 

-- (1) Unnest 2D array into a 1D array using madlib.array_unnest_2d_to_1d() function
SELECT 
	id
	, (madlib.array_unnest_2d_to_1d(var_2d)).* 
FROM mat_2d_arr 
ORDER BY id, unnest_row_id;

--id|unnest_row_id|unnest_result|
----+-------------+-------------+
-- 1|            1|{1,2,3}      |
-- 1|            2|{4,5,6}      |
-- 1|            3|{7,8,9}      |
-- 2|            1|{10,11,12}   |
-- 2|            2|{13,14,15}   |
-- 2|            3|{16,17,18}   |
-- 3|            1|{19,20,21}   |
-- 3|            2|{22,23,24}   |
-- 3|            3|{25,26,27}   |
-- 4|            1|{28,29,30}   |
-- 4|            2|{31,32,33}   |
-- 4|            3|{34,35,36}   |

 

 

 

(2) 1D array 에서 순서대로 원소 값을 indexing 하기

 

일단 2D array를 1D array 로 unnest 하고 나면, 그 다음에 1D array에서 순서대로 각 원소를 inndexing 해오는 것은 기본 SQL 구문을 사용하면 됩니다. 1D array 안에 각 3개의 원소들이 들어 있으므로, 순서대로 unnest_result[1], unnest_result[2], unnest_result[3] 으로 해서 indexing 을 해오면 아래 예제와 같습니다. 

 

-- (2) Indexing an unnested 1D array
SELECT 
	a.id 
	, unnest_row_id
	, unnest_result[1] AS x1
	, unnest_result[2] AS x2
	, unnest_result[3] AS x3
FROM (
	SELECT 
		id
		, (madlib.array_unnest_2d_to_1d(var_2d)).* 
	FROM mat_2d_arr 
) AS a
ORDER BY id, unnest_row_id;

--id|unnest_row_id|x1|x2|x3|
----+-------------+--+--+--+
-- 1|            1| 1| 2| 3|
-- 1|            2| 4| 5| 6|
-- 1|            3| 7| 8| 9|
-- 2|            1|10|11|12|
-- 2|            2|13|14|15|
-- 2|            3|16|17|18|
-- 3|            1|19|20|21|
-- 3|            2|22|23|24|
-- 3|            3|25|26|27|
-- 4|            1|28|29|30|
-- 4|            2|31|32|33|
-- 4|            3|34|35|36|

 

 

 

[Reference]

- Apache MADlib's madlib.array_unnest_2d_to_1d() function: http://madlib.incubator.apache.org/docs/latest/array__ops_8sql__in.html#af057b589f2a2cb1095caa99feaeb3d70

 

 

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

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

 

반응형
Posted by Rfriend

댓글을 달아 주세요

지난번 포스팅에서는 Greenplum, PostgreSQL DB에서 PL/R을 활용하여 In-DB 에서 그룹별 회귀모형 (분산 병렬) 적합 및 예측하는 방법(https://rfriend.tistory.com/529)을 소개하였습니다. 


이번 포스팅에서는 동일한 abalone 데이터셋으로 Apache MADlib (https://madlib.apache.org/) 을 사용하여 그룹별 회귀모형을 분산 병렬 적합 및 예측하는 방법을 소개하겠습니다. 


1. Apache MADlib 소개

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

3. 훈련, 검증 데이터 분할 (split abalone dataset into training and test set)

4. 성별 그룹별 선형회귀모형 적합 (training linear regression models by 'sex' groups')

5. 성별 그룹별 선형회귀모형 회귀계수 조회 (select coefficients per variables by 'sex' groups)

6. 성별 그룹별 선형회귀모형을 이용하여 예측 (prediction by 'sex' groups)



  1. Apache MADlib 소개


[ Apache MADlib: Big Data Machine Learning in SQL for PostgreSQL and Greenplum DB ]


Apache MADlib 은 PostgreSQL, Greenplum Database 에서 SQL 언어로 대용량 빅데이터에 대해 In-DB 기계학습, 통계분석, 그래프 분석을 할 수 있는 Apache project 의 top level 오픈 소스 라이브러리입니다. 


 Apache MADlib은 2011년 EMC/Greenplum 아키텍트와 캘리포니아 버클리 대학교(university of California, Berkeley)의 Joe Hellerstein 교수가 같이 오픈소스 프로젝트로 시작하였으며, Berkeley 대학교 외에 Stanfoard 대학교, Wisconsin 대학교, Florida 대학교 등이 같이 Apache MADlib Project에 참여하고 있습니다. 


Apache MADlib은 아래와 같이 지도학습, 비지도학습, 그래프, 통계, 시계열분석, 샘플링 및 모델 선택, 데이터 유형 변환 등의 다양한 기능의 함수를 제공합니다. 


[ Apache MADlib Functions ]


Apache MADlib은 core engine이 C++로 되어있어서 굉장히 빠릅니다. 추상적인 고수준 언어는 Python으로 되어 있고, 사용자는 SQL로 함수를 실행시키므로 SQL을 알고 있는 사용자라면 쉽고 빠르게 사용할 수 있습니다. 


모든 데이터 전처리 및 분석이 In-DB에서 이루어지므로 데이터의 In/Out이 없으며, 수백테라~페타바이트급의 대용량도  (Greenplum의 경우) 분산 병렬처리할 수 있으므로 빅데이터를 다루어서 모델링을 신속하게 해야 하는 경우에 적합합니다. 


[ Reference of Apache MADlib ]

- Open source: https://github.com/apache/madlib

- Downloads and Documents: http://madlib.apache.org

- Wiki: https://cwiki.apache.org/confluence/display/MADLIB

- Greenplum DB에 MADlib 설치https://gpdb.docs.pivotal.io/550/ref_guide/extensions/madlib.html




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


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



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

-- Linear Regression in Parallel 

-- using Apache MADlib

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


-- Dataset for example: abalone dataset from the UC Irvine Machine Learning Repository

-- URL: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data

-- Create an external web table

DROP EXTERNAL TABLE IF EXISTS abalone_external;

CREATE EXTERNAL WEB TABLE abalone_external(

sex text 

, length float8

, diameter float8

, height float8

, whole_weight float8

, shucked_weight float8

, viscera_weight float8

, shell_weight float8

, rings integer -- target variable to predict

) LOCATION('http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data') 

FORMAT 'CSV' 

(null as '?');



-- Create a table of abalone

DROP TABLE IF EXISTS abalone;

CREATE TABLE abalone AS 

SELECT * FROM abalone_external

DISTRIBUTED BY (sex);


-- Viewing data distribution

SELECT gp_segment_id, COUNT(*) AS row_cnt

FROM abalone

GROUP BY gp_segment_id;



-- Check data

SELECT * FROM abalone LIMIT 5;




SELECT sex, COUNT(*) FROM abalone GROUP BY sex; 






  3. 훈련, 검증 데이터 분할 (split abalone dataset into training and test set)


이번 예제에서는 MADlib을 사용하여 간단하게 성(sex)별로 shucked_weight와 diameter 설명변수를 사용하여 rings 를 예측하는 다중 선형회귀모형을 적합하고, 예측하는 것입니다. 


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


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



-- Train, Test set split

DROP TABLE IF EXISTS out_train, out_test;

SELECT madlib.train_test_split(

'abalone',  -- Source table

'out',      -- Output table

        0.8,        -- train_proportion

        NULL,       -- Default = 1 - train_proportion = 0.5

        'sex',      -- Strata definition

        'rings, shucked_weight, diameter', -- Columns to output

        FALSE,      -- Sample with replacement

        TRUE);      -- Separate output tables



-- Check

SELECT * FROM out_train LIMIT 5;




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




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






  4. 성별 그룹별 선형회귀모형 적합 

     (training linear regression models by 'sex' groups')


위의 3번에서 분할(split)한 훈련 데이터셋(training set)인 'out_train' 테이블을 대상으로   의 다중 선형회귀모형을 madlib.linregr_train() 함수를 사용하여 성별('sex') 그룹별로 나누어서 적합(fit)시켜 보겠습니다. 

MADlib에는 그룹별로 모형을 각각 적합시킬 때 아래의 예처럼 GroupBy 칼럼 이름을 넣어주면 알고리즘 내부적으로 Group별로 (이 예제에서는 'sex' 별로) 분산병렬처리하여 복수의 모델을 적합시켜 줍니다! 수 테라바이트의 대용량 데이터라도 전수로 분산병렬처리해서 신속하게 모델을 적합시킬 수 있으니 대단히 아주 유용합니다. (로컬 싱글 머신에서 R이나 Python 사용할 때처럼 메모리 full 나서 다운되거나, 몇 시간씩 걸리는 일 없습니다)



-- Linear Regression using MADlib

-- Train a regression model. 

DROP TABLE IF EXISTS abalone_linregr, abalone_linregr_summary;

SELECT madlib.linregr_train(

    'out_train'         -- table containing training data

    , 'abalone_linregr' -- table in which to save results

    , 'rings'           -- column containing dependent variable

    , 'ARRAY[1, shucked_weight, diameter]' -- features included in the model

    , 'sex'); -- create multiple output models (one for each value of sex)



* MADlib linear regression: https://madlib.apache.org/docs/latest/group__grp__linreg.html




  5. 성별 그룹별 선형회귀모형 회귀계수 조회 

     (select coefficients per variables by 'sex' groups)


위의 4번에서 성별('sex') 그룹별로 각각 분산병렬처리해서 훈련한 선형회귀모형의 적합 결과를 조회해보겠습니다. select 문의 from 절에 위의 4번에서 설정한 output table 이름인 "abalone_linregr" 테이블을 써주면 됩니다. 


그런데 다중 선형회귀모형이다보니 Y절편 intercept 와 'shucked_weight', 'diameter' 의 두개의 설명변수가 사용되어 성별로 각각 적합된 모델의 회귀계수(regression coefficients), 결정계수(), 표준오차(standard error), T 통계량(t-statistics), P 값 (P-values) 의 칼럼에 'intercept', 'shucked_weight', 'diameter' 의 순서대로 3개 값들이 콤마로 구분되어 array 형태로 들어가 있기에 읽기에 좀 힘듭니다. 



-- Examine the resulting models

SELECT * FROM abalone_linregr ORDER BY sex;





사람이 눈으로 보기에 좀더 가독성이 있도록 unnest() 함수를 사용해서 array를 세로로 긴 형태로 풀어서 다시 한번 조회를 해보겠습니다. 아래에 예제 결과를 보는 것처럼 한결 보기에 좋습니다. 


-- unnest format

SELECT sex

, unnest(ARRAY['intercept', 'rings', 'diameter']) as attribute

, unnest(coef) as coefficient 

, unnest(std_err) as standard_error

, unnest(t_stats) as t_stat

, unnest(p_values) as pvalue

FROM abalone_linregr

ORDER BY sex;



위에 Apache MADlib으로 성별('sex')로 각각 적합한 선형회귀모형의 회귀계수는 이전 포스팅에서 Greenplum에서 PL/R로 성별로 분산병렬처리해서 적합한 선형회귀모형(https://rfriend.tistory.com/529)의 회귀계수와 정확하게 일치합니다. 




  6. 성별 그룹별 선형회귀모형을 이용하여 예측 (prediction by 'sex' groups)


위의 5번에서 training set을 이용해 성별로 각각 선형회귀모형을 적합하였으니, 이번에는 3번에서 분할하여 따로 떼어놓았던 test set을 대상으로 예측(prediction)하여 보고, 실제값과 예측값의 차이를 비교해서 모델의 성능을 평가해보겠습니다. 


예측에는 madlig.linregr_predict() 라는 함수를 이용하며, input은 array[] 형태로 데이터를 변화해주어야 합니다. 아래 예에서 ARRAY[1, shucked_weight, diameter] 에서 '1'은 intercept 항을 의미합니다. 


Greenplum DB에서 MADlib으로 훈련한 모델을 사용하여 MADlib으로 대용량 데이터어 대해 예측/스코어링을 하면 역시 분산병령처리가 되어 대단히 빠르게 결과값을 반환합니다!



-- compare predicted value with actual with grouping

DROP TABLE IF EXISTS abalone_pred;

CREATE TABLE abalone_pred AS (

SELECT a.sex, a.shucked_weight, a.diameter, a.rings, 

madlib.linregr_predict(m.coef

, ARRAY[1, shucked_weight, diameter]

) as predict_val

, rings - madlib.linregr_predict(m.coef

, ARRAY[1, shucked_weight, diameter]

) as residual

FROM out_test a, abalone_linregr m

WHERE a.sex = m.sex) DISTRIBUTED BY (sex);



SELECT * FROM abalone_pred WHERE sex = 'F' LIMIT 10;





위의 6번에서 만든 실제값과 예측값 테이블 'abalone_pred' 을 이용해서 다양한 통계량 지표로 선형회귀모형의 적합도 평가해보겠습니다. 이중에서 실제값과 예측값이 차이의 제곱을 평균한 Mean Squared Error를 성별('sex') 그룹별로 madlib.mean_squared_error() 함수를 사용하여 계산해보겠습니다. (함수의 위치에 각 인자값을 넣어주면 됩니다.)



-- Model Performance Evaluation: Mean Squared Error

DROP TABLE IF EXISTS abalone_mse;

SELECT madlib.mean_squared_error(

'abalone_pred'   -- table_in

, 'abalone_mse'  -- table_out

, 'predict_val'  -- prediction_col

, 'rings'        -- observed_col

, 'sex');        -- grouping_cols

SELECT * FROM abalone_mse;





위에서 소개한 MSE를 계산하는 방식으로 MAE (Mean Absolute Error), MAPE (Mean Absolute Percentage Error), MPE (Mean Percentage Error), R-squared, Adjusted R-squared 등을 계산할 수 있습니다. (아래의 함수별 인자 위치를 참고해서  select madlib.함수(인자1, 인자2, ... ) 이런식으로 써주면 됩니다. 위의 MSE 계산하는 예제 참고하세요). 



-- Mean absolute error

madlib.mean_abs_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean absolute percentage error

madlib.mean_abs_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean percentage error

madlib.mean_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean squared error

madlib.mean_squared_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- R-squared

madlib.r2_score(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Adjusted R-squared

madlib.adjusted_r2_score(table_in, table_out, prediction_col, observed_col, num_predictors, training_size, grouping_cols)

 


* MADlib Model Selection - Prediction Metrics: https://madlib.apache.org/docs/latest/group__grp__pred.html


다음 포스팅에서는 PivotalR을 활용하여 Greenplum, PostgreSQL DB에서 그룹별 선형회귀모형 적합 및 예측(https://rfriend.tistory.com/534) 하는 방법을 소개하겠습니다. 


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

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



반응형
Posted by 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 Rfriend

댓글을 달아 주세요