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

 

creating a sample table using random numbers in PostgreSQL, Greenplum

 

 

아래 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|

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,