R, Python 분석과 프로그래밍의 친구 (by R Friend)

이번 포스팅에서는 PostgreSQL, Greenplum database에서 Array의 특정 위치의 값을 가져오는 방법, 특히 PL/Python 사용자 정의 함수를 이용해서 Array의 여러개 위치에서 값을 가져오는 방법을 소개하겠습니다. 

PostgreSQL, Greenplum database 에서 

(1) Array 에서 특정 위치의 하나의 원소 값 가져오기

(2) Array 에서 시작~끝 위치의 원소 값을 Slicing 해서 가져오기

(3) Array 에서 여러개 위치의 원소 값을 PL/Python 사용자 정의함수를 사용해서 가져오기

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

-- creating a sample table with array column
DROP TABLE IF EXISTS arr_sample_tbl;
CREATE TABLE arr_sample_tbl (
	grp TEXT
	, x_arr iNTEGER[]
);

INSERT INTO arr_sample_tbl VALUES 
('a',  ARRAY[1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
, ('b', ARRAY[11, 12, 13, 14, 15, 16, 17, 18, 19, 20])
, ('c', ARRAY[21, 22, 23, 24, 25, 26, 27, 28, 29, 30])
, ('d', ARRAY[31, 32, 33, 34, 35, 36, 37, 38, 39, 40])
;

SELECT * FROM arr_sample_tbl ORDER BY grp;
--grp|x_arr                          |
-----+-------------------------------+
--a  |{1,2,3,4,5,6,7,8,9,10}         |
--b  |{11,12,13,14,15,16,17,18,19,20}|
--c  |{21,22,23,24,25,26,27,28,29,30}|
--d  |{31,32,33,34,35,36,37,38,39,40}|

(1) Array 에서 특정 위치의 하나의 원소 값 가져오기

PostgreSQL/ Greenplum Array 칼럼에 array_column[position::int] 처럼 [ ] 안에 위치 값을 하나의 정수로 넣어주면 됩니다. 아래 예에서는 x_arr Array 칼럼의 3번째 위치한 값을 가져와 봤습니다. 

-- getting a value in an array using postion
SELECT 
	grp
	, x_arr[3] -- POSITION INDEX ok
FROM arr_sample_tbl 
ORDER BY grp
;
--grp|x_arr|
-----+-----+
--a  |    3|
--b  |   13|
--c  |   23|
--d  |   33|

(2) Array 에서 시작~끝 위치의 원소 값을 Slicing 해서 가져오기

PostgreSQL/ Greenplum Array 칼럼에서 array_column[start_position:end_position] 구문으로 "시작 위치 ~ 끝 위치" 까지의 연속된 원소 값들을 Slicing 해올 수 있습니다. 아래 예제에서는 x_arr 의 Array 칼럼에서 1번째부터 3번째 위치까지의 연속된 원소값들을 Slicing 해왔습니다. 

-- slicing from start position to end position
SELECT 
	grp
	, x_arr[1:3] -- SLICING ok
FROM arr_sample_tbl 
ORDER BY grp
;
--grp|x_arr     |
-----+----------+
--a  |{1,2,3}   |
--b  |{11,12,13}|
--c  |{21,22,23}|
--d  |{31,32,33}|

(3) Array 에서 여러개 위치의 원소 값을 PL/Python 사용자 정의함수를 사용해서 가져오기

위의 (2)번에서는 Array에서 연속된 (시작 위치 ~ 끝 위치) 까지의 값을 Slicing 해왔는데요, 만약 연속된 위치의 여러개 값이 아니라 Array 안에 띄엄띄엄 있는 여러개의 원소 값들을 위치로 가져오고 싶을 때는 SQL syntax error 가 발생합니다. 

-- SQL Error [42601]: ERROR: syntax error at or near ","
SELECT 
	grp
	, x_arr[1, 4, 6, 9]  -- SQL Error -_-;;;
FROM arr_sample_tbl 
ORDER BY grp
;
--SQL Error [42601]: ERROR: syntax error at or near ","
--  Position: 24
--
--Error position: line: 528 pos: 23

PostgreSQL, Greenplum DB에서 Array 내에 띄엄띄엄 있는 여러개 위치의 원소값을 가져오고 싶을 때 아래의 PL/Python 사용자 정의함수를 사용해보세요. 참고로, Input으로 집어넣는 DB col_position 위치 값은 1부터 시작하는 반면, PL/Python의 내부 코드블록에서는 Python array의 위치 index가 0 부터 시작하기 때문에 np.array(col_postion) - 1 처럼 1을 빼줬습니다. 

아래 예제에서는 x_arr 칼럼의 1, 4, 6, 9 번째 위치에 있는 Array 복수개 원소들을 복수의 위치값을 사용해서 가져왔습니다. Greenplum 을 사용하면 분산병렬처리가 되기 때문에 PL/Python 사용자 정의함수의 처리 속도도 무척 빠르답니다! 

-- UDF: selecting multiple elements using PL/Python in PostgreSQL, Greenplum
DROP FUNCTION IF EXISTS plpy_arr_multi_idx_select(int[], int[]);
CREATE OR REPLACE FUNCTION plpy_arr_multi_idx_select(
          x_arr int[], col_position int[]
          ) 
RETURNS int[]
AS $$
    import numpy as np
    
    # INPUT ARRAY
    arr = np.array(x_arr)
    
    # COLUMN POSITION 
    # Python starts from 0 index. (vs. SQL starts from 1)
    # , so -1 from col_position
    col_position_arr = np.array(col_position) - 1
    
    # getting data by positional indexing
    arr_selected = arr[col_position_arr]
    
    return arr_selected

$$ LANGUAGE 'plpythonu';


-- executing the PL/Python UDF above
-- getting multiple values in an array 
-- using PL/Python UDF in PostgreSQL, Greenplum DB
SELECT 
	grp
	, plpy_arr_multi_idx_select( -- PL/Python USER DEFINED Funtion
		x_arr                -- INPUT ARRAY
		, ARRAY[1, 4, 6, 9]  -- COLUMN POSITION
	) AS selected_values
FROM arr_sample_tbl 
ORDER BY grp
;

--grp|selected_values|
-----+---------------+
--a  |{1,4,6,9}      |
--b  |{11,14,16,19}  |
--c  |{21,24,26,29}  |
--d  |{31,34,36,39}  |

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

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