[PostgreSQL, Greenplum] PL/Python 사용자 정의 함수를 이용해서 Array의 여러개 위치에서 값 가져오기
이번 포스팅에서는 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} |
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
