'3 ways to return PL/R results on Greenplum and PostgreSQL DB'에 해당되는 글 1건

  1. 2020.04.09 [Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum)

지난번 포스팅에서는 PostgreSQL 혹은 Greenplum DB 에서 PL/R (Procedural Language R) 을 실행하기 위해 input 데이터 형태로 aggregation 하는 3가지 방법을 소개하였습니다. 


이번에는 PL/R 의 output 을 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) 을 소개하겠습니다. 


(1) returns float8[] : array 형태로 결과 반환 (그룹 당 1행)

(2) returns setof float8 : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)

(3) reterns setof composite_type : 행 단위로 composite type에 맞게 결과 반환 

                                               (관측치 당 1행, 그룹 당 여러 행)

(4) returns table : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)

(5) returns bytea : byte array 로 결과 반환 (그룹 당 1행)

    --> unserialize 하는 PL/R 함수 추가로 필요



[ PL/R on Greenplum & PostgreSQL DB (workflow 예시) ]




예제에 사용하기 위해 그룹('grp') 칼럼과 정수('x1'), 실수('x2') 칼럼을 가진 간단한 테이블을 생성해보겠습니다. 



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

-- PL/R on Greenplum

-- : 5 ways to return PL/R results

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


-- create an example dataset table

create schema test;


drop table if exists test.src_tbl;

create table test.src_tbl (

grp varchar(10) not null 

, x1 integer

, x2 float8

) distributed by (grp);


insert into test.src_tbl values 

('a', 1, 0.13)

, ('a', 2, 0.34)

, ('a', 3, 0.31)

, ('a', 4, 0.49)

, ('a', 5, 0.51)

, ('b', 1, 0.10)

, ('b', 2, 0.26)

, ('b', 3, 0.30)

, ('b', 4, 0.62)

, ('b', 5, 0.59);


select * from test.src_tbl;




예시에 사용할 PL/R 함수는 x1에 2를 곱하고 x2를 로그 변환 (log transformation) 하여 더한 값 (x_new = 2 * x1 + log(x2))을 계산하는 매우 간단한 것입니다. (물론 SQL로도 할 수 있는데요, PL/R 예시로 간단한 걸로 사용한 거예요)



  (1) returns float8[] : array 형태로 결과 반환 (그룹 당 1행)


returns float8[] 에서 꺽쇠 '[]' 가 array 로 반환하라는 의미의 기호입니다. 


코딩이 간단하고 그룹별로 1행으로 저장이 되므로 조회나 테이블 조인(table join) 할 때 빠르다는 장점이 있습니다. 하지만 조회를 했을 때 array 형태이므로 조회해서 보고 활용할 때 보통 unnest 를 해야 해서 불편한 점이 있습니다. 



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

-- (1) returns an array using 'returns float8[]'

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


-- Define PL/R UDF

drop function if exists test.plr_log_trans(integer[], float8[]);

create or replace function test.plr_log_trans(x1 integer[], x2 float8[])

returns float8[] -- returns as an array

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

return(x_new)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, a.x1_agg

, a.x2_agg

, test.plr_log_trans(a.x1_agg, a.x2_agg) as x_new_agg

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;




위의 PL/R 결과 테이블에서 보는 것처럼 array 형태로 PL/R 결과를 반환하기 때문에 { } 안에 옆으로 길게 늘어서 있어서 보기에 불편합니다. 이럴 경우 SQL의 unnest() 함수를 사용해서 세로로 길게 행 단위(by row)로 테이블 형태를 바꾸어서 조회할 수 있습니다. 



-- Display by rows using unnest() function

select 

a.grp

, unnest(a.x1_agg) as x1

, unnest(a.x2_agg) as x2

, unnest(test.plr_log_trans(a.x1_agg, a.x2_agg)) as x_new

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;





  (2) returns setof float8 : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)


위의 (1) 번과 지금 소개하는 (2)번이 다른 점은 (a) setof 추가, (b) [] 제거  의 두 가지입니다. 

  - (1)번 : returns float8[]

  - (2)번 : returns setof float8


(2)번 방법으로 하면 (1)번에서 처럼 unnest() 함수를 쓸 필요없이 바로 행 단위(by rows)로 PL/R 결과를 반환합니다. 



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

-- (2) returns rows using 'returns setof'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_2(int[], float8[]);

create or replace function test.plr_log_trans_2(x1 int[], x2 float8[])

returns setof float8 -- returns by each row

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

return(x_new)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, unnest(a.x1_agg) as x1

, unnest(a.x2_agg) as x2

, test.plr_log_trans_2(a.x1_agg, a.x2_agg) as x_new

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;






  (3) reterns setof composite_type : 행 단위로 composite type 에 맞게 결과 반환 

                                                (관측치 당 1행, 그룹 당 여러 행)


PL/R 함수의 결과로 반환받을 결과값이 여러개의 칼럼으로 구성되어 있는 경우 composite type을 정의해서 PL/R 함수 정의할 때 사용할 수 있습니다. 


위의 (2)번 예에서는 PL/R 실행 결과의 반환받는 값으로 x_new = 2*x1 + log(x2) 의 x_new 값 단 1개만 float8 형태로 반환했습니다. 이번 (3)번 예에서는 PL/R 결과값으로 x1 (integer), x2, (float8) x_new (float8) 의 3개 칼럼의 composite type 형태로 반환해보겠습니다. 


이러려면 create type 으로 반환받을 composite type 을 먼저 정의를 해줍니다. 그 다음으로 PL/R 함수를 정의할 때 returns setof composite_type_name 처럼 앞서 정의한 compositie type 이름을 returns setof 뒤에 써주면 됩니다. 


(2)번 예에서는 PL/R 을 실행(execution) 하면 x_new 계산 결과만 반환하므로 x_1, x_2 를 select 문에 별도로 써주었습니다. 하지만 (3)번 예에서는 PL/R 함수를 보면 x_1, x_2, x_new 를 하나의 DataFrame으로 묶고 이를 통채로 반환하도록 되어있습니다. 그리고 composite type을 x_1, x_2, x_new 각각의 데이터 유형에 맞게 정의해주었구요.  



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

-- (3) returns rows using 'returns setof' with a composite type

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


-- Define composite data type

drop type test.plr_log_trans_type cascade;

create type test.plr_log_trans_type as (

x1 integer

, x2 float8

, x_new float8

);



-- Define PL/R UDF

drop function if exists test.plr_log_trans_3(int[], float8[]);

create or replace function test.plr_log_trans_3(x1 int[], x2 float8[])

returns setof test.plr_log_trans_type

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

result_df <- data.frame(x1, x2, x_new)

return(result_df)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, (test.plr_log_trans_3(a.x1_agg, a.x2_agg)).*

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;


 




  (4) returns table : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)


PL/R 함수를 실행했을 때 반환받을 값의 칼럼이 여러개일 경우 위의 (3)번 처럼 composite type을 미리 정의해서 PL/R 함수를 정의할 때 returns setof composite_type_name 형식으로 쓸 수도 있구요, 이번의 (4)번처럼 바로 returns table (반환받을 칼럼 이름과 데이터 유형) 형식으로 바로 쓸 수도 있습니다. 


(3)번 처럼 composite type을 미리 정의해두면 나중에 똑같은 칼럼과 데이터 유형으로 PL/R에 input 넣거나 output 으로 반환받을 때 그냥 composite type name 을 써주면 되므로 재활용할 수 있는 장점이 있습니다. 


이번 (4)번처럼 composite type을 정의하는것 없이 그냥 바로 returns table () 처럼 하면 일단 편하고 또 직관적이어서 이해하기 쉬운 장점이 있으며, 대신 (3)번처럼 재활용은 못하므로 매번 써줘야 하는 단점이 있습니다. 



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

-- (4) returns rows using 'returns table'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_4(integer[], float8[]);

create or replace function test.plr_log_trans_4(x1 integer[], x2 float8[])

returns table (

x1 integer

, x2 float8

, x_new float8

)

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

result_df <- data.frame(x1, x2, x_new)

return(result_df)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, (test.plr_log_trans_4(a.x1_agg, a.x2_agg)).*

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;






  (5) returns bytea : byte array 로 결과 반환 (그룹 당 1행)

       --> unserialize 하는 PL/R 함수 추가로 필요


마지막으로, PL/R 결과를 byte array 형태로 반환하여 테이블에 저장하는 방법입니다. 


이 방법은 반환받는 PL/R 데이터셋의 형태, 데이터 유형을 신경쓸 필요없이 그냥 통째로 byte array 로 직렬화(serialize) 해서 반환하므로 PL/R 함수를 정의할 때 returns bytea 로 하고 나머지는 신경쓸 필요가 없어서 편리합니다. 


대신에, 공짜 점심은 없듯이, 나중에 byte array 로 저장된 PL/R 결과를 조회하려면 역직렬화(unserialize)를 해주는 PL/R 함수를 한번 더 짜줘야 하므로 편리성 면에서는 결국 조삼모사입니다. 


다만, PL/R 함수로 반환받으려는 객체가 그 무엇이던간에 (가령, 회귀모형의 적합된 모델 자체, 길이가 제 각각인 텍스트 등...) byte array 로 직렬화해서 반환받으면 되므로 byte array를 써야만 하는 경우도 있겠습니다. 



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

-- (5) returns serialized byte array using 'returns bytea'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_5(integer[], float8[]);

create or replace function test.plr_log_trans_5(x1 integer[], x2 float8[])

returns bytea -- serialized as a byte array

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

serialized_df <- serialize(data.frame(x1, x2, x_new), NULL)

return(serialized_df)

$$ language 'plr';



-- Execute PL/R UDF

drop table if exists test.tbl_plr_log_trans_5;

create table test.tbl_plr_log_trans_5 as (

select 

a.grp

, test.plr_log_trans_5(a.x1_agg, a.x2_agg) as serialized_df

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a

) distributed by (grp);


select * from test.tbl_plr_log_trans_5;


 



위의 직렬화되어서 반환된 PL/R 결과를 select 문으로 조회를 해보면 사람은 눈으로 읽을 수 없게 저장이 되어있음을 알 수 있습니다. 아래는 R의 unserialize() 함수를 사용해서 역직렬화(deserialize, decode)를 해서 미리 정의해둔 composite type 으로 반환하도록 해준 PL/R 코드입니다. 좀 복잡하게 느껴질 수도 있겠습니다. ^^; 



-- Unserialize

-- Define composite data type

drop type test.plr_log_trans_type cascade;

create type test.plr_log_trans_type as (

x1 integer

, x2 float8

, x_new float8

);


-- Define PL/R UDF for reading a serialized PL/R results

drop function if exists test.plr_unserialize(bytea);

create or replace function test.plr_unserialize(

serialized_obj bytea

) returns setof test.plr_log_trans_type

as 

$$

unserialized_df <- data.frame(unserialize(serialized_obj))

return (unserialized_df)

$$ language 'plr';



-- Execute 'plr_unserialize' UDF

select 

grp

, (test.plr_unserialize(a.serialized_df)).*

from test.tbl_plr_log_trans_5 a;


 



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

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


반응형
Posted by Rfriend

댓글을 달아 주세요