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

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

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