[PostgreSQL/ Greenplum] 정규분포에서 난수를 생성하여 샘플 테이블 만들기
Database에서 가상의 샘플 데이터를 만들어서 SQL이 버그없이 잘 작동하는지 확인을 한다든지, DB의 성능을 테스트 해봐야 할 때가 있습니다.
이번 포스팅에서는 PostgreSQL, Greenplum DB를 사용해서 정규분포(Normal Distribution)로부터 난수 (random number)를 생성하여 샘플 테이블을 만들어보겠습니다.
(1) 테이블 생성 : create table
(2) 정규분포로 부터 난수 생성하는 사용자 정의 함수 정의 : random_normal(count, mean, stddev)
(3) 테이블에 정규분포로 부터 생성한 난수 추가하기 : generate_series(), to_char(), insert into
(4) Instance 별 데이터 개수 확인하기 : count() group by gp_segment_id
아래 SQL 예제 코드는 PostgreSQL 9.4.26 버전, Greenplum 6.19.2 버전에서, Greenplum Database 의 분산 저장, 분산병렬처리 고려해서 작성하였습니다.
-- version check
SELECT version();
-- PostgreSQL 9.4.26 (Greenplum Database 6.19.2 build commit:0e1f6307eb4e368b79cbf67a0dc6af53362d26c0) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Feb 14 2022 23:03:52
(1) 테이블 생성 : create table
CREATE TABLE 함수를 사용해서 분석의 대상 기준으로 사용할 문자열의 cell_id, param_id 와 숫자형의 측정값 meas_val 을 가지는 빈 껍데기 samp_tbl 테이블을 만들어보겠습니다. WITH 절에 압축 옵션을 설정하였으며, DISTRIBUTED BY (cell_id) 로 분산 저장하는 기준을 설정해주었습니다.
분산키를 잘 잡아주는 것이 향후 분산병렬처리의 성능을 좌지우지 합니다. 분석이나 데이터 처리(조인 등) 기준이 되고, 한쪽 노드로 쏠리지 않고 골고루 분산시킬 수 있는 분산키를 설정해주어야 합니다.
-- creating a sample table
DROP TABLE IF EXISTS samp_tbl;
CREATE TABLE samp_tbl (
cell_id varchar(10)
, param_id varchar(2)
, meas_val numeric
) WITH(appendonly=TRUE, compresslevel=7, compresstype=zstd)
DISTRIBUTED BY (cell_id);
(2) 정규분포로 부터 난수 생성하는 사용자 정의 함수 정의 : random_normal(count, mean, stddev)
PostgreSQL 버전 10 이상부터 정규분포(normal distribution)로 부터 난수를 생성(generating random numbers) 하는 함수 normal_rand() 를 쓸 수 있습니다.
https://www.postgresql.org/docs/current/tablefunc.html
-- over PostgreSQL version 10
-- Produces a set of normally distributed random values.
normal_rand ( numvals integer, mean float8, stddev float8 ) → setof float8
PostgreSQL 9.6 이전 버전에서는 PL/Python, PL/R, PL/SQL 로 정규분포로 부터 난수를 생성하는 사용자 정의함수를 정의해서 사용해야 합니다. (아래는 PL/SQL 이구요, PL/Python이나 PL/R 로도 가능해요)
-- UDF of random number generator from a normal distribution, X~N(mean, stddev)
-- random_normal() built-in function over PostgreSQL version 10.x
DROP FUNCTION IF EXISTS random_normal(INTEGER, DOUBLE PRECISION, DOUBLE PRECISION);
CREATE OR REPLACE FUNCTION random_normal(
count INTEGER DEFAULT 1,
mean DOUBLE PRECISION DEFAULT 0.0,
stddev DOUBLE PRECISION DEFAULT 1.0
) RETURNS SETOF DOUBLE PRECISION
RETURNS NULL ON NULL INPUT AS $$
DECLARE
u DOUBLE PRECISION;
v DOUBLE PRECISION;
s DOUBLE PRECISION;
BEGIN
WHILE count > 0 LOOP
u = RANDOM() * 2 - 1; -- range: -1.0 <= u < 1.0
v = RANDOM() * 2 - 1; -- range: -1.0 <= v < 1.0
s = u^2 + v^2;
IF s != 0.0 AND s < 1.0 THEN
s = SQRT(-2 * LN(s) / s);
RETURN NEXT mean + stddev * s * u;
count = count - 1;
IF count > 0 THEN
RETURN NEXT mean + stddev * s * v;
count = count - 1;
END IF;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- credit: https://bugfactory.io/blog/generating-random-numbers-according-to-a-continuous-probability-distribution-with-postgresql/
(3) 테이블에 정규분포로 부터 생성한 난수 추가하기 : generate_series(), to_char(), insert into
이제 위의 (1)번에서 생성한 samp_tbl 테이블에 insert into 구문을 사용해서 가상의 샘플 데이터 추가해보겠습니다. 이때 From 절에서 generate_series(from, to) 함수를 사용해서 정수의 수열을 생성해주고, SELECT 절의 TO_CHAR(a, '0000000000'), TO_CHAR(b, '00') 에서 generate_series()에서 생성한 정수를 자리수가 10자리, 2자리인 문자열로 바꾸어줍니다. (빈 자리는 '0'으로 자리수만큼 채워줍니다.) TRIP() 함수는 화이트 스페이스를 제거해줍니다.
-- inserting data
-- cell_id 1,000 * param_id 4 * meas_val 25 = 100,000 rows in total
-- good cases 99,999,000 vs. bad cases 1,000 (cell_id 10 * param_id 4 * meas_val 25 = 1,000 rows)
-- cell_id '000000001' will be used as a control group (good case) later.
-- it took 8 min. 4 sec.
TRUNCATE TABLE samp_tbl;
INSERT INTO samp_tbl
SELECT
trim(to_char(a, '0000000000')) AS cell_id
, trim(to_char(b, '00')) AS param_id
, random_normal(25, 0, 1) AS meas_val -- X~N(0, 1), from Normal distribution
FROM generate_series(1, 1000) AS a -- cell_id
, generate_series(1, 4) AS b -- param_id
;
(4) Instance 별 데이터 개수 확인하기 : count() group by gp_segment_id
위의 (1)~(3)번에서 테이블을 만들고, 가짜 데이터를 정규분포로 부터 난수를 발생시켜서 테이블에 추가를 하였으니, Greenplum의 각 nodes 에 골고루 잘 분산이 되었는지 확인을 해보겠습니다. (아래는 AWS에서 2개 노드, 노드별 6개 instance, 총 12개 instances 환경에서 테스트한 것임)
-- check segments in Greenplum
-- 2 nodes * 6 instances = 12 instances in total
SELECT gp_segment_id, count(1)
FROM samp_tbl
GROUP BY gp_segment_id
ORDER BY gp_segment_id;
--gp_segment_id|count
---------------+-----+
-- 0| 7400|
-- 1| 8300|
-- 2| 8200|
-- 3| 8500|
-- 4| 7800|
-- 5| 6600|
-- 6| 9400|
-- 7| 9400|
-- 8| 7600|
-- 9| 8900|
-- 10| 8200|
-- 11| 9700|
-- totoal number of rows: cell_id 1,000 * param_id 4 * measured_value 25 = 100,000
SELECT count(1) FROM samp_tbl;
--count |
--------+
--100000|
-- X ~ N(0, 1) approximately
SELECT avg(meas_val), stddev(meas_val) FROM samp_tbl;
--avg |stddev |
-------------------+-----------------+
--0.005474367|0.995105289|
SELECT * FROM samp_tbl ORDER BY cell_id, param_id LIMIT 25;
--cell_id |param_id|meas_val |
------------+--------+------------------+
--0000000001|01 |-0.531695967165547|
--0000000001|01 |-0.108739177377124|
--0000000001|01 | 0.568470878445752|
--0000000001|01 |0.0202499172346384|
--0000000001|01 | 0.733808732215974|
--0000000001|01 | 0.217977459614905|
--0000000001|01 |-0.819498864258696|
--0000000001|01 | -1.15053271252296|
--0000000001|01 | 0.27459170410016|
--0000000001|01 |-0.360160392758718|
--0000000001|01 | 0.180482978307365|
--0000000001|01 | 0.903190145608135|
--0000000001|01 |-0.546983465499866|
--0000000001|01 | 2.10019183187282|
--0000000001|01 | 0.500516025880425|
--0000000001|01 | -1.46928655599126|
--0000000001|01 |-0.224673782111734|
--0000000001|01 | 0.600268991904523|
--0000000001|01 |-0.233178028377569|
--0000000001|01 |0.0753960434547863|
--0000000001|01 | -2.86355579238885|
--0000000001|01 | -2.25814837725797|
--0000000001|01 | 1.4013348575359|
--0000000001|01 |-0.445684149707259|
--0000000001|01 | -1.03404850229361|
이번 포스팅이 많은 도움이 되었기를 바랍니다.
행복한 데이터 과학자 되세요! :-)
