이번 포스팅에서는 PostgreSQL, Greenplum Database를 이용해서 시계열 데이터를 특정 시간 단위 구간(예: 10분 단위, 1시간 단위, 1일 단위, 1달 단위, 1년 단위 등) 별로 집계/ 요약하는 방법을 소개하겠습니다. 


(* python pandas로 특정 시간 단위 구간별로 시계열 데이터를 집계하는 방법은 https://rfriend.tistory.com/495 참조)


-- 사전 준비 작업 --

(1) '년-월-일' date dimension table 만들기

(2) '시간:분' time dimension table 만들기

(3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기

(4) 예제 시계열 데이터(time series) table 만들기

    ==> '날짜-시간' 기준정보를 '시계열 거래 데이터'에 merge 하기


-- 10분 단위 구간별로 시계열 데이터 집계/ 요약하기 --

(5) 10분 단위 구간별로 첫번째 행 값(first row value), 마지막 행 값(last row value) 구하기

(6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기 

(7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중위수(median), 3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기

(8) 10분 단위 구간별로 평균(mean), 표본 분산(sample variance), 표본 표준편차(sample standard deviation) 구하기

(9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)



  (1) '년-월-일' date dimension table 만들기


날짜(년-월-일)를 기준으로 년(year), 분기(quarter), 월(month), 주(week), 일(day), 공휴일(holiday) 등의 다양한 관점의 group by 할 수 있는 구분자를 생성해놓은 테이블을 만들어보겠습니다. 날짜(년-월-일) 관련 기준 정보, 매핑 테이블이라고 보면 되겠습니다. 


아래 예제는 2019년 12월1일 ~ 2019년 12월 31일 까지의 31일 기간의 날짜 데이터 기준정보를 만들었습니다. 

만약 2019년 1월1일~2019년 12월 31일 까지의 365일 전체의 날짜 데이터 기준정보를 만들고 싶다면 from 절을 아래와 같이 수정해주면 됩니다. 


[ 2019년 1월 1일~2019년 12월 31일 (365일) 기간 일 별 기준정보 테이블 만드는 FROM 절 generate query ]


FROM (

SELECT '2019-01-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 364) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

     ) DQ



아래의 postgresql wiki 페이지의 sql query를 참고하였습니다. 

* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions



------------------------

-- date dimension table

------------------------

-- create 'dim_date' table

DROP TABLE IF EXISTS dim_date;

CREATE TABLE dim_date (

date DATE NOT NULL, 

year INT NOT NULL, 

month INT NOT NULL, 

month_nm VARCHAR(9) NOT NULL, 

day INT NOT NULL, 

day_of_year INT NOT NULL, 

weekday_nm VARCHAR(9) NOT NULL, 

calender_week INT NOT NULL, 

formatted_date CHAR(12) NOT NULL, 

quartal CHAR(2) NOT NULL, 

year_quartal CHAR(7) NOT NULL, 

year_month CHAR(7) NOT NULL, 

year_calendar_week CHAR(7) NOT NULL, 

weekend VARCHAR(7) NOT NULL, 

holiday_korea VARCHAR(20)     NOT NULL, 

period VARCHAR(20)     NOT NULL, 

cwstart DATE NOT NULL, 

cwend DATE NOT NULL, 

month_start DATE NOT NULL, 

month_end DATE NOT NULL

);


-- create index on 'date'

CREATE INDEX dim_date_date_idx ON dim_date(date);


-- insert generate date dimension data (from 2019-01-01 to 2019-12031) into 'dim_date' table

INSERT INTO dim_date (

SELECT

datum AS date,

EXTRACT(YEAR FROM datum)::INT AS year,

EXTRACT(MONTH FROM datum)::INT AS month,

-- Localized month name

to_char(datum, 'TMMonth') AS month_nm,

EXTRACT(DAY FROM datum)::INT AS day,

EXTRACT(doy FROM datum)::INT AS day_of_year,

-- Localized weekday

to_char(datum, 'TMDay') AS weekday_nm,

-- ISO calendar week

EXTRACT(week FROM datum)::INT AS calendar_week,

to_char(datum, 'dd. mm. yyyy') AS formatted_date,

'Q' || to_char(datum, 'Q') AS quartal,

to_char(datum, 'yyyy/"Q"Q') AS year_quartal,

to_char(datum, 'yyyy/mm') AS year_month,

-- ISO calendar year and week

to_char(datum, 'iyyy/IW') AS year_calendar_week,

-- Weekend

CASE WHEN EXTRACT(isodow FROM datum) IN (6, 7) THEN 'Weekend' 

ELSE 'Weekday' END 

AS weekend,

-- holidays for Korea in year 2019

    CASE WHEN to_char(datum, 'MMDD') IN ('0101', '0205', '0206', '0207', '0301', '0505', '0512', 

                                         '0606', '0815', '0912', '0913', '0914', '1003', '1009', '1225')

THEN 'Holiday' ELSE 'No holiday' END

AS holiday_korea,

-- Some periods of the year, adjust for your organisation and country

CASE WHEN to_char(datum, 'MMDD') BETWEEN '0701' AND '0831' THEN 'Summer break'

    WHEN to_char(datum, 'MMDD') BETWEEN '1115' AND '1225' THEN 'Christmas season'

    WHEN to_char(datum, 'MMDD') > '1225' OR to_char(datum, 'MMDD') <= '0106' THEN 'Winter break'

ELSE 'Normal' END

AS period,

-- ISO start and end of the week of this date : from monday to sunday

datum + (1 - EXTRACT(isodow FROM datum))::INTEGER AS CWStart, -- monday

datum + (7 - EXTRACT(isodow FROM datum))::INTEGER AS CWEnd,   -- sunday

-- Start and end of the month of this date

datum + (1 - EXTRACT(DAY FROM datum))::INTEGER AS month_start,

((datum + (1 - EXTRACT(DAY FROM datum))::INTEGER + '1 month'::INTERVAL)::DATE - '1 day'::INTERVAL)::DATE AS month_end

FROM (

SELECT '2019-12-01'::DATE + SEQUENCE.DAY AS datum

FROM generate_series(0, 30) AS SEQUENCE(DAY)

GROUP BY SEQUENCE.DAY

     ) DQ

ORDER BY 1);



SELECT * FROM dim_date ORDER BY date;






  (2) '시간:분' time dimension table 만들기


시간('시간:분')을 기준으로 시간(time), 분(minute), 10분 단위 구간(time_span_min_10), 1시간 단위 구간(time_span_hour_1), 일 구분 범주(day_category) 등으로 구분해서 집계/요약할 수 있는 구분자, 기준 정보, 매핑 테이블을 만들어보겠습니다. 


* reference: https://wiki.postgresql.org/wiki/Date_and_Time_dimensions



------------------------

-- time dimension table

------------------------

-- create dimension table 'dim_time'

DROP TABLE IF EXISTS dim_time;

CREATE TABLE dim_time (

time_of_day CHAR(6) NOT NULL,

hour INT NOT NULL, 

minute INT NOT NULL,

time_span_min_10 CHAR(15) NOT NULL, 

time_span_hour_1 CHAR(15) NOT NULL, 

day_category VARCHAR(15) NOT NULL

);


-- create index on 'time_of_day'

CREATE INDEX dim_time_time_of_day_idx ON dim_time(time_of_day);


-- insert generated time_of_day data into 'dim_time' table

INSERT INTO dim_time (

SELECT 

TO_CHAR(MINUTE, 'hh24:mi') AS time_of_day,

-- Hour of the day (0 - 23)

EXTRACT(HOUR FROM MINUTE)::INTEGER AS hour, 

-- Minute of the day (0 - 1439)

(EXTRACT(HOUR FROM MINUTE)*60 + EXTRACT(MINUTE FROM MINUTE))::INT AS minute,

-- Extract and format 10 minutes time span

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10 || 'minutes')::INTERVAL, 'hh24:mi') ||

' – ' ||

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 10 || 'minutes')::INTERVAL + '10 minutes'::INTERVAL, 'hh24:mi')

AS time_span_min_10, 

-- Extract and format 1 hour time span

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60 || 'minutes')::INTERVAL, 'hh24:mi') ||

' – ' ||

TO_CHAR(MINUTE - (EXTRACT(MINUTE FROM MINUTE)::INTEGER % 60 || 'minutes')::INTERVAL + '60 minutes'::INTERVAL, 'hh24:mi')

AS time_span_hour_1, 

-- Names of day periods

CASE WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '06:00' AND '08:59' THEN 'morning'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '09:00' AND '11:59' THEN 'AM'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '12:00' AND '17:59' THEN 'PM'

    WHEN to_char(MINUTE, 'hh24:mi') BETWEEN '18:00' AND '21:59' THEN 'evening'

    ELSE 'night'

END AS day_category

FROM (SELECT '00:00'::TIME + (SEQUENCE.MINUTE || ' minutes')::INTERVAL AS MINUTE

FROM generate_series(0,1439) AS SEQUENCE(MINUTE)

GROUP BY SEQUENCE.MINUTE

     ) DQ

ORDER BY 1

);


SELECT * FROM dim_time ORDER BY time_of_day LIMIT 60;

 

 



  (3) '년-월-일 시간:분' cross-join 한  date & time dimension table 만들기


(1)번에서 만든 dim_date 테이블과 (2)번에서 만든 dim_time 테이블을 cross join 하여 'dim_date_time' 테이블을 만들어보겠습니다. 


31일 (dim_date) * 24시간 * 60분 (dim_time) = 44,640 개의 전체 행 (total number of rows) 을 가지는 '년-월-일 시간:분' (dim_date_time) 기준정보 매핑 테이블을 가지고 이제 년 ~ 분 단위를 넘나들면서 원하는 시간 단위 구간별로 시계열 데이터를 집계/ 요약할 수 있습니다


앞으로 join 의 기준이 될 '년-월-일 시간:분'(dt) 칼럼에 대해서는 빠른 join을 위해서 index 를 생성해주었습니다. 



---------------------------------------

-- cross join b/w dim_date and dim_time

---------------------------------------

DROP TABLE IF EXISTS dim_date_time;

CREATE TABLE dim_date_time AS (

SELECT 

to_char((dim_date.date || ' ' || dim_time.time_of_day)::timestamp, 'yyyy-mm-dd hh24:mi') AS dt

, dim_date.*

, dim_time.*

FROM dim_date

CROSS JOIN dim_time

);


-- create index on 'dt'

CREATE INDEX dim_date_time_dt_idx ON dim_date_time(dt);



SELECT * FROM dim_date_time ORDER BY dt LIMIT 5;



SELECT COUNT(*) FROM dim_date_time;

44640





  (4) 예제 시계열 데이터(time series) table 만들기 ==> '날짜-시간' 기준 정보 merge 하기


간단한 예제로 '년-월-일 시간:분'(dt), 가격(price), 수량(amt) 의 3개 칼럼을 가지는 거래 시계열 데이터 테이블을 만들어보겠습니다. 



---------------------

-- Transaction table

---------------------

-- create ts table

DROP TABLE IF EXISTS ts;

CREATE TABLE ts (

dt  TEXT NOT NULL

, price NUMERIC NOT NULL 

, amt INT NOT NULL

);


-- insert transaction data into ts table

INSERT INTO ts VALUES 

('2019-12-19 00:00', 12, 4), 

('2019-12-19 00:02', 21, 2),

('2019-12-19 00:04', 41, 1),

('2019-12-19 00:06', 79, 4),

('2019-12-19 00:08', 61, 2),

('2019-12-19 00:10', 81, 1),

('2019-12-19 00:12', 24, 3),

('2019-12-19 00:14', 62, 1),

('2019-12-19 00:16', 76, 3),

('2019-12-19 00:18', 63, 1),

('2019-12-19 00:20', 95, 2),

('2019-12-19 00:22', 82, 1),

('2019-12-19 00:24', 82, 3),

('2019-12-19 00:26', 70, 1),

('2019-12-19 00:28', 30, 4),

('2019-12-19 00:30', 33, 1),

('2019-12-19 00:32', 22, 2),

('2019-12-19 00:34', 77, 3),

('2019-12-19 00:36', 58, 3),

('2019-12-19 00:38', 96, 3), 

('2019-12-19 09:02', 10, 2),

('2019-12-19 09:08', 50, 3),

('2019-12-19 14:04', 20, 1),

('2019-12-19 16:00', 10, 5),

('2019-12-19 21:00', 30, 4)

;


SELECT * FROM ts ORDER BY dt;





다음으로, '시계열 거래 데이터'(ts table)에 '날짜-시간 기준정보'(dim_date_time table) 를 '년-월-일 시간:분'(dt) 칼럼을 기준으로 병합(left outer join)해보겠습니다. 이제 특정 시간 단위 구간별로 집계, 요약할 수 있는 데이터 준비가 되었습니다. 


※ 만약 '시계열 거래 데이터'에 특정 날짜-시간대에 값이 존재하던지 말던지 간에 동일한 특정 시간 단위 구간별로 값을 집계, 요약하고자 한다면, (즉, '시계열 거래 데이터'가 없으면 NULL이나 '0'으로 집계) LEFT OUTER JOIN 할 때 왼쪽에 '날짜-시간 기준정보'(dim_date_time) 테이블을 놓고, 오른쪽에 '시계열 거래 데이터'(ts) 테이블을 놓고 left outer join 을 해주어야 합니다. 



------------------------------------

-- merge 'date-time' dimension table

------------------------------------

DROP TABLE IF EXISTS ts_dt;

CREATE TABLE ts_dt AS (

SELECT 

a.*

, b.date

, b.time_span_min_10

, b.time_span_hour_1

, b.day_category

FROM ts a 

LEFT OUTER JOIN dim_date_time b ON a.dt = b.dt

);


SELECT * FROM ts_dt ORDER BY dt LIMIT 5;





  (5) 10분 단위 구간별로 첫번째 행 값(first-row value), 마지막 행 값(last row value) 구하기


시계열 데이터를 시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별로 첫번째 행의 값(first row's value)을 구하는 방법에는 두가지가 있습니다. 


첫번째 방법은 ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) 의 window function을 사용하는 것입니다. (오름차순 정렬 order by dt)


두번째 방법은 DISTINCT ON (date, time_span_min_10) 함수를 사용하는 것입니다. 



-- (1) first, last by time_span_min_109

-- (1-1-1) way 1: first using ROW_NUMBER()

WITH time_span_min_10_tmp AS (

SELECT 

date

, time_span_min_10

, price

, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt) AS row_num

FROM ts_dt

)

SELECT 

date

, time_span_min_10

, price AS price_10m_first

FROM time_span_min_10_tmp

WHERE row_num = 1

;


-- (1-1-2) way 2: first using DISTINCT ON

SELECT DISTINCT ON (date, time_span_min_10)

date

, time_span_min_10

, price AS price_10m_first

FROM ts_dt

ORDER  BY date, time_span_min_10, dt, price;


 



시간 기준으로 정렬했을 때 10분 단위 구간(10 minutes time span)별 마지막 행의 값(last row's value)을 구하는 두가지 방법은 위에서 소개한 첫번째 행의 값을 구하는 방법에서 정렬 기준을 정반대로 해서, 내림차순 정렬(ORDER BY dt DESC) 을 해주면 됩니다. 



-- (1-2-1) way 1: last using ROW_NUMBER()

WITH time_span_min_10_tmp AS (

SELECT 

date

, time_span_min_10

, price

, ROW_NUMBER() OVER (PARTITION BY date, time_span_min_10 ORDER BY dt DESC) AS row_num

FROM ts_dt

)

SELECT 

date

, time_span_min_10

, price AS price_10m_last

FROM time_span_min_10_tmp

WHERE row_num = 1

;


-- (1-2-2) way 2: last using DISTINCT ON

SELECT 

DISTINCT ON (date, time_span_min_10)

date

, time_span_min_10

    , price AS price_10m_last

FROM ts_dt

ORDER  BY date, time_span_min_10, dt DESC, price;

 




  (6) 10분 단위 구간별로 합(sum), 누적합(cumulative sum) 구하기 


10분 단위 구간(10 minutes time span) 별로 가격(price) 칼럼의 합(sum)을 먼저 구하고, ==> 시간 순서대로 정렬된 상태에서 10분 단위 구간별로 SUM() OVER () window function을 사용해서 누적 합(cumulative sum)을 구해보겠습니다. 



-- (2) sum, cumulative sum

-- sum, cumulative sum by time_span_min_10

WITH price_10m_sum_tmp AS (

SELECT 

date

, time_span_min_10

, sum(price) AS price_10m_sum

FROM ts_dt

GROUP BY 1, 2

)

SELECT 

date

, time_span_min_10

, price_10m_sum

, SUM(price_10m_sum) 

OVER ( ORDER BY time_span_min_10 

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 

AS price_10m_cumsum 

FROM price_10m_sum_tmp

;

 




집계/ 요약을 하는 시간 단위 구간을 위의 10분에서 이번에는 1 시간 (1 hour time span) 단위 구간별 변경해서 가격 합(sum)과 가격 누적합(cumulative sum)을 구해보겠습니다. 위의 (3)번에서 '날짜-시간 기준정보 테이블'(dim_date_time table)에서 '1시간 단위 구간' 칼럼 (time_span_hour_1) 을 만들어놓았었기 때문에 group by 집계/ 요약하는 기준이 되는 칼럼을 'time_span_min_10'을 'time_span_hour_1'으로 변경해주기만 하면 됩니다. 



-- sum, cumulative sum by time_span_hour_1

WITH price_1h_sum_tmp AS (

SELECT 

date

, time_span_hour_1

, sum(price) AS price_1h_sum

FROM ts_dt

GROUP BY 1, 2

)

SELECT 

date

, time_span_hour_1

, price_1h_sum

, SUM(price_1h_sum) 

OVER ( ORDER BY time_span_hour_1

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

AS price_1h_cumsum

FROM price_1h_sum_tmp;





  (7) 10분 단위 구간별로 최소값(min), 1사분위수(1st quantile), 중앙값(median), 

       3사분위수(3rd quantile), 최대값(max), 범위(range) 구하기


최소값은 min(), 최대값은 max(), 1사분위수/중앙값/3사분위수는 percentile_disc(), 범위는 max() - min() 함수를 이용해서 time_span_min_10 으로 group by 집계, 요약하였습니다. 


1/2/3 사분위수(quantile) 구하는 percentile_disc(0.25 / 0.5 / 0.75) within group (order by price) 함수를 사용해서 IQR ( = q3 - q1) 구할 수도 있습니다. 



-- (3) min, q1, median, q3, max, range by time_span_min_10

SELECT 

date 

, time_span_min_10

, MIN(price) AS price_10m_min

, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY price) AS price_10m_q1

, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) AS price_10m_median

, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY price) AS price_10m_q3

, MAX(price) AS price_10m_max

, MAX(price) - MIN(price) AS price_10m_range

FROM ts_dt

GROUP BY 1, 2

ORDER BY 1, 2;


 




  (8) 10분 단위 구간별로 평균(mean), 분산(variance), 표준편차(standard deviation) 구하기


10분 단위 구간(10 minutes time span) 별로 표본 분산(sample variance)은 variance() 함수, 표본 표준편차(sample standard deviation)은 stddev() 함수를 이용하여 구하였습니다. 


10분 단위 구간별로 만약 행의 개수가 1개만 있는 경우 분산, 표준편차를 구할 수 없으므로 NULL 값을 반환하게 되는데요,  이런 경우 coalesce() 를 사용해서 '0'으로 채워주었습니다. 



-- (4) mean, variance, standard deviation by time_span_min_10

SELECT 

date 

, time_span_min_10

, AVG(price) AS price_10m_mean

, COALESCE(VARIANCE(price), 0) AS price_10m_var

, COALESCE(STDDEV(price), 0) AS price_10m_stddev

FROM ts_dt

GROUP BY 1, 2

ORDER BY 1, 2;






  (9) 10분 단위 구간별로 수량 가중 평균 가격 구하기 (amount-weighted average of price)




-- (5) amount-weighted average price

WITH price_mult_amt_tmp AS (

SELECT 

dt

, date 

, time_span_min_10

, amt

, (price * amt) AS price_mult_amt

FROM ts_dt

)

SELECT 

date 

, time_span_min_10

, ROUND((sum(price_mult_amt) / sum(amt)), 2) AS price_10m_amount_weighted_avg

FROM price_mult_amt_tmp

GROUP BY 1, 2

ORDER BY 1, 2;





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

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





Posted by R Friend R_Friend

댓글을 달아 주세요

Lag, Lead window function은 시계열 데이터를 처리할 때 많이 사용하는 매우 유용한 함수입니다. 


이번 포스팅에서는 PostgreSQL, Python (pandas), R (dplyr) 을 이용해서 그룹별로 행을 하나씩 내리기, 올리기 (lag or lead a row by group using PostgreSQL, Python, R) 하는 방법을 소개하겠습니다. 





  1. PostgreSQL로 그룹별로 특정 칼럼의 행을 하나씩 내리기, 올리기 

    (lag, lead a row by group using PostgreSQL lag(), lead() window function)


연월일(dt), 그룹ID(id), 측정값(val) 의 세 개 칼럼을 가진 시계열 데이터의 테이블을 PostgreSQL DB에 만들어보겠습니다. 



DROP TABLE IF EXISTS ts;

CREATE TABLE ts (

    dt date not null

    , id text not null  

    , val numeric not null

);


INSERT INTO ts VALUES 

  ('2019-12-01', 'a', 5)

, ('2019-12-02', 'a', 6)

, ('2019-12-03', 'a', 7)

, ('2019-12-04', 'a', 8)

, ('2019-12-01', 'b', 13)

, ('2019-12-02', 'b', 14)

, ('2019-12-03', 'b', 15)

, ('2019-12-04', 'b', 16);


SELECT * FROM ts ORDER BY id, dt;




PostgreSQL 의 LAG(value, offset, default), LEAD(value, offset, default) Window function을 이용해서 그룹ID('id') 별로 측정값('val')의 행을 하나씩 내리기(lag), 올리기(lead) 해보겠습니다. 행을 내리거나 올린 후에 빈 셀의 값은 'NULL'로 지정해주었습니다. 


LAG(), LEAD() 함수를 사용할 때 그룹ID('id')별로 년월일('dt') 을 기준으로 내림차순 정렬(OVER(PARTITIO BY id ORDER BY dt)) 을 해줍니다. 



-- lead() windows function

SELECT 

    *

    , LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

    , LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts;

 



lag(), lead() 함수를 사용해서 lag_1, lead_2 라는 새로운 칼럼을 추가한 'ts_lag_lead' 라는 이름의 테이블을 만들어보겠습니다. 



DROP TABLE IF EXISTS ts_lag_lead;

CREATE TABLE ts_lag_lead AS (

SELECT 

    *

    , LAG(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lag_1

    , LEAD(val, 1, NULL) OVER (PARTITION BY id ORDER BY dt) AS val_lead_2

FROM ts

);


SELECT * FROM ts_lag_lead ORDER BY id, dt;

 





  2. Python pandas 로 DataFrame 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기 

     (shift a row by group using Python pandas library)


위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 Python pandas 를 가지고 수행해보겠습니다. 


먼저 dt, id, val의 칼럼을 가진 pandas DataFrame 시계열 데이터를 만들어보겠습니다. 



import pandas as pd


ts = pd.DataFrame({'dt': ['2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04', 

                          '2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'], 

                  'id': ['a', 'a', 'a', 'a', 'b', 'b', 'b', 'b'], 

                  'val': [5, 6, 7, 8, 13, 14, 15, 16]})


ts

dtidval
02019-12-01a5
12019-12-02a6
22019-12-03a7
32019-12-04a8
42019-12-01b13
52019-12-02b14
62019-12-03b15
72019-12-04b16

 



shift() 함수를 쓰기 전에 sort_values() 함수로 정렬을 해주는데요, lag 는 내림차순 정렬, lead는 오름차순 정렬임에 주의해야 합니다. (PostgreSQL, R 대비 Python이 좀 불편하긴 하네요 -,-;)


(a) lagsort_values() 함수를 이용해서 년월일('dt')를 기준으로 내림차순 정렬 (ascending=True) 한 후, 'id' 그룹별로 'val' 값을 하나씩 내려기 groupby('id')['val'].shift(1)


(b) lead: sort_values() 함수를 이용해서 년월일('dt')를 기준으로 오름차순 정렬 (ascending=False) 한 후, 'id' 그룹별로 'val' 값을 하나씩 올리기 groupby('id')['val].shift(1)



# lag a row by group 'id'

ts['val_lag_1'] =  ts.sort_values(by='dt', ascending=True).groupby('id')['val'].shift(1)


# lead a row by group 'id'

ts['val_lead_1'] = ts.sort_values(by='dt', ascending=False).groupby('id')['val'].shift(1)


ts.sort_values(by=['id', 'dt'])

dtidvalval_lag_1val_lead_1
02019-12-01a5NaN6.0
12019-12-02a65.07.0
22019-12-03a76.08.0
32019-12-04a87.0NaN
42019-12-01b13NaN14.0
52019-12-02b1413.015.0
62019-12-03b1514.016.0
72019-12-04b1615.0NaN

 





  3. R dplyr 로 dataframe 내 그룹별 특정 칼럼의 행을 하나씩 내리기, 올리기 

     (lag, lead a row by group using R dplyr library)



위에서 PostgreSQL의 lag(), lead() window function과 똑같은 작업을 R dplyr library를 가지고 수행해보겠습니다. 


먼저 dt, id, val의 칼럼을 가진 R DataFrame 시계열 데이터를 만들어보겠습니다. 



#install.packages("dplyr")

library(dplyr)


dt <- c(rep(c('2019-12-01', '2019-12-02', '2019-12-03', '2019-12-04'), 2))

id <- c(rep('a', 4), rep('b', 4)) 

val <- c(5, 6, 7, 8, 13, 14, 15, 16)


ts <- data.frame(dt, id, val)

ts

A data.frame: 8 × 3
dtidval
<fct><fct><dbl>
2019-12-01a5
2019-12-02a6
2019-12-03a7
2019-12-04a8
2019-12-01b13
2019-12-02b14
2019-12-03b15
2019-12-04b16

 



R은 Postgresql 처럼 lag(), lead() window function을 가지고 있고 dplyr library의 chain operator를 써서 arrange() 함수로 'dt' 기준 내림차순 정렬하고, group_by(id)를 써서 그룹ID('id')별로 lag(), lead()를 무척 편리하게 적용해서 새로운 변수를 생성(mutate)할 수 있습니다. 



ts <- ts %>% 

    arrange(dt) %>%

    group_by(id)  %>% 

    mutate(val_lag_1 = lag(val, 1), 

          val_lead_1 = lead(val, 1))

 


arrange(ts, id, dt)

A grouped_df: 8 × 5
dtidvalval_lag_1val_lead_1
<fct><fct><dbl><dbl><dbl>
2019-12-01a5NA6
2019-12-02a657
2019-12-03a768
2019-12-04a87NA
2019-12-01b13NA14
2019-12-02b141315
2019-12-03b151416
2019-12-04b1615NA




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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

  1. 2019.12.09 13:32  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  2. 2019.12.09 19:36  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

    • R Friend R_Friend 2019.12.12 11:06 신고  댓글주소  수정/삭제

      안녕하세요.

      제가 지금 해외출장 나와있는데요, 어찌된 일인지 모바일 티스토리 앱으로 댓글 업데이트 알람을 받지 못해서요, 이제서야 (12월12일, 오전10시) 웹으로 블로그 로그인 한 후에야 추가로 댓글 달아주신것을 확인했습니다.

      제가 업무시간에는 시간을 낼 수 없어서요, 퇴근 후에 저녁에 답글 달도록 하겠습니다. 너무나 많이 늦어져서 죄송합니다.

    • 2019.12.12 20:35  댓글주소  수정/삭제

      비밀댓글입니다

    • R Friend R_Friend 2019.12.15 03:01 신고  댓글주소  수정/삭제

      안녕하세요 차차님,

      댓글에 남겨주신 말씀처럼, pandas 시계열 데이터 DataFrame 을 처리할 때 특정 시간 단위 구간별 집계/요약은 resample() 메소드를 사용하는 것이 가장 간편한 방법입니다.

      resample() 메소드를 사용해서 여러 통계량을 좀더 쓰기 쉽도록 사용자 정의 함수(UDF)를 만들어보았는데요, 아래의 포스팅을 참고하시기 바랍니다.

      https://rfriend.tistory.com/494


      만약, 대용량 시계열 데이터를 처리해야 하는 경우라면 관계형 Database를 사용하는 것이 빠르고 편리한 방법입니다.
      PostgreSQL DB를 사용해서 python pandas resample() 메소드와 동일한 결과를 얻기 위한 방법에 대한 소개는 아래 포스팅을 참고하세요.

      https://rfriend.tistory.com/495

      포스팅 2개 하다보니 주말이 훌쩍 가버리네요. ^^;

DB에서 데이터처리나 분석을 하다보면 실수를 한다든지, 데이터 크기가 너무 크다든지, index가 안걸려있는 상태에서 join을 하려고 했다든지, cross join이 되어 연산량이 기하급수적으로 늘었다든지, 여러가지 이유로 수행시간이 너무 오래걸리는 경우가 생길 수 있습니다. 이럴 경우 강제로 돌아가고 있는 쿼리를 강제 종료 (kill active query) 시켜야 하는 상황이 생길 수 있습니다. 


이번 포스팅에서는 PostgreSQL DB, Greenplum DB 이용 시에 터미널에서 psql 로 

(1) 터미널 창에서 psql 실행하여

(2) 현재 수행되고 있는 쿼리(current active query)를 조회하고, 

(3) 특정 쿼리를 강제 종료 (kill a specific query) 시키는 방법

을 소개하겠습니다. 



(1) 먼저, 터미널을 열고 $ psql postgres  을 입력해서 psql 을 사용할 수 있는 환경으로 들어갑니다. 




ihongdon-ui-MacBook-Pro:~ ihongdon$ psql postgres

psql (11.5)

Type "help" for help.


postgres=#

 




호스트 이름, DB 이름, 사용자 ID, Password 입력은 아래 psql을 참고하세요. 



$ psql -h host_name  -d db_name  -u user_id

$ Password ********

 




(2) 터미널에서 psql 로 pg_stat_activity 테이블에서 아래의 조회 쿼리를 사용하여 현재 Postgres에서 수행 중인 쿼리(active query)를 조회해보겠습니다. 


select pid, now() - query_start as "runtime", usename, datname, state, query 

from pg_stat_activity 

where now() - query_start > '2 minutes'::interval and state = 'active' 

order by runtime desc;


위의 쿼리로 조회를 해서 강제 종료시키고자 하는 쿼리의 'pid' 를 확인합니다. 아래의 예의 경우 pid = 4941 이네요. 



postgres=#

postgres=# select pid, now() - query_start as "runtime", usename, datname, state, query from pg_stat_activity where now() - query_start > '2 minutes'::interval and state = 'active' order by runtime desc;

 

 pid  |     runtime     | usename  | datname  | state  |                                      query

------+-----------------+----------+----------+--------+----------------------------------------

 4941 | 00:34:00.595933 | ihongdon | postgres | active |              +

   |          |          |        | DROP TABLE IF EXISTS my_table;              +      

   |          |          |        | CREATE TABLE my_table AS (              +     

   |          |          |        |     SELECT              +

   |          |          |        |         a.*              +

   |          |          |        |         , b.age             +

   |          |          |        |         , b.pch_amt              +

   |          |          |        |         , b.pch_cnt              +

   |          |          |        |         , b.age              +

   |          |          |        |     FROM              +

   |          |          |        |         cust_master AS a              +

   |          |          |        |         LEFT OUTER JOIN cust_pch AS b              +

   |          |          |        |             ON  a.cust_id = b.cust_id            +

   |          |          |        | );              +

   |          |          |        |

(1 row)


postgres=#

 




(3) SELECT pg_terminate_backend(put your pid)  로 강제 종료 시키고자 하는 쿼리의 pid 를 지정해주어서 kill 시킵니다. 



postgres=# SELECT pg_terminate_backend(4941); 


pg_terminate_backend

----------------------

 t

(1 row)


postgres=#

postgres=#

 




-----------------[ Greenplum DB (GPDB) 알아두면 요긴한 팁 ] -------------------------


  • Greenplum DB(GPDB)도 현재 수행 중이 쿼리 조회하고 강제 종료하는 방법 (Postgres와 동일)



-- ssh로 GPDB 접속

> ssh gpadmin@xxx.xxx.xxx.xxx
> password: xxxxxxxxx

-- 현재 수행 중인 쿼리 조회
> select datname, username, proceed, sess_id, current_query from pg_stat_activity;


-- 현재 수행중인 쿼리 강제 종료
> select pg_cancel_backend(**put pid here**)

 



  • GPDB에서 Segment 확인하는 방법


> select gp_segment_id, count(*) from mytable group by gp_segment_id;

 



  • GPDB master, Segment 확인


> select * from pg_catalog.gp_segment_configration;

 



  • gpperfrom db 에 접속하여 system history 테이블 조회하는 방법 (병렬처리 확인)


select

    ctime, 

    hostname, 

    avg(cpu_user + cpu_sys) cpu, 

    avg(mem_actual_used/1024/1024/1024) memogy_gigabyte, 

    avg(dist_rb_rate/1024/1024) dist_rb_megabyte, 

    avg(disk_wb_rate/1024/1024) disk_wb_megabyte, 

    avg(net_rb_rate/1024/1024) net_rb_megabyte, 

    avg(net_wb_rate/1024/1024) net_wb_megabyte

 from system_hitory

 where ctime between '2019-01-01 12:00:00' and '2019-01-01 23:59:59' 

and hostname not in ('dev1.gphd.local', 'dev2.gphd.local')

group by ctime, hostname

order by 1, 2 asc

 




http://localhost:28080/main/querymonitor/hostmetrics

 


 GPDB를 UI 상에서 편리하게 모니터링 하고 관리할 수 있게 해주는 GPDB Command Center 에 대한 자세한 설명은  gpcc.docs.pivotal.io 를 참고하세요. 



[ Pivotal Greenplum Command Center Images ((https://gpcc.docs.pivotal.io/600/welcome.html) ]








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


Posted by R Friend R_Friend

댓글을 달아 주세요

앞의 포스팅에서는 공간지리 형태의 데이터셋을 import 하는 방법들을 소개하였습니다. 

 

이번 포스팅에서는 PostgreSQL, Greenplum DB의 PostGIS 에서 테이블(Table) 형태로 있는 공간지리 데이터에 대해서 (1) pg_dump로 공간지리 테이블을 백업하기(Backup), (2) pg_restore 로백업한 공간지리 테이블을 다시 불러오기 (Restore) 를 해보겠습니다. 

(* Reference: https://github.com/PacktPublishing/Mastering-PostGIS)

 

create a backup table using pgrestore

 

(1) pg_dump로 공간지리 데이터 테이블 백업하기 (Create a Backup table)

명령 프롬프트 창에서 docker로 Greenplum DB를 실행한 후에, gpadmin 계정으로 들어가서 이미 geometry 포맷으로 만들어두었던 data_import.earthquakes_subset_with_geom 테이블을 pg_dump 를 사용하여 백업해보았습니다. (host, port, user 부분은 각자의 database 설정을 입력하면 됨)

 

 

[gpadmin@mdw tmp]$ pg_dump -h localhost -p 5432 -U gpadmin -t data_import.earthquakes_subset_with_geom -c -F c -v -b -f earthquakes_subset_with_geom.backup gpadmin

pg_dump: reading extensions

pg_dump: identifying extension members

20190417:04:24:25|pg_dump-[INFO]:-reading schemas

pg_dump: reading user-defined tables

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined types

20190417:04:24:25|pg_dump-[INFO]:-reading type storage options

20190417:04:24:25|pg_dump-[INFO]:-reading procedural languages

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined aggregate functions

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operators

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined external protocols

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator classes

20190417:04:24:25|pg_dump-[INFO]:-reading user-defined operator families

pg_dump: reading user-defined text search parsers

pg_dump: reading user-defined text search templates

pg_dump: reading user-defined text search dictionaries

pg_dump: reading user-defined text search configurations

20190417:04:24:26|pg_dump-[INFO]:-reading user-defined conversions

20190417:04:24:26|pg_dump-[INFO]:-reading type casts

20190417:04:24:26|pg_dump-[INFO]:-reading table inheritance information

pg_dump: finding extension tables

20190417:04:24:26|pg_dump-[INFO]:-reading rewrite rules

20190417:04:24:26|pg_dump-[INFO]:-finding inheritance relationships

20190417:04:24:26|pg_dump-[INFO]:-reading column info for interesting tables

pg_dump: finding the columns and types of table "earthquakes_subset_with_geom"

20190417:04:24:26|pg_dump-[INFO]:-flagging inherited columns in subtables

20190417:04:24:26|pg_dump-[INFO]:-reading indexes

20190417:04:24:26|pg_dump-[INFO]:-reading constraints

20190417:04:24:26|pg_dump-[INFO]:-reading triggers

pg_dump: reading dependency data

pg_dump: saving encoding = UTF8

pg_dump: saving standard_conforming_strings = on

pg_dump: dumping contents of table earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

 

 

(2) pg_restore 로 백업 테이블 다시 불러오기

이미 테이블로 만들어져 있는 data_import.earthquakes_subset_with_geom 테이블을 삭제한 후에, (1)번에서 백업해둔 데이터를 불러오겠습니다. 

 

테이블을 먼저 삭제해볼께요. 

-- (2) (DBeaver db tool 에서) drop table

DROP TABLE data_import.earthquakes_subset_with_geom;

 

테이블을 삭제하였으니, 이제 다시 (1)번에서 백업해두었던 데이터를 다시 불러와서 테이블을 생성(Restore a Backup table)해보겠습니다. 

-- (3) (명령 프롬프트 창에서) Restore using pg_restore

[gpadmin@mdw tmp]$ pg_restore -h localhost -p 5432 -U gpadmin -v -d gpadmin earthquakes_subset_with_geom.backup

pg_restore: connecting to database for restore

pg_restore: creating TABLE earthquakes_subset_with_geom

pg_restore: restoring data for table "earthquakes_subset_with_geom"

pg_restore: setting owner and privileges for TABLE earthquakes_subset_with_geom

[gpadmin@mdw tmp]$

 

DBeaver db tool에서 백업 테이블을 잘 불어와서 테이블이 생성이 되었는지 확인해보겠습니다. 

-- (4) (DBeaver db tool 에서) 백업 되었는지 조회 확인

SELECT * FROM data_import.earthquakes_subset_with_geom LIMIT 10;

 

백업 테이블 불어오기(restore)가 잘 되었네요. 

 

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

Posted by R Friend R_Friend

댓글을 달아 주세요

이번 포스팅에서는 PostgreSQL, Greenplum database에서 지리공간 데이터 분석 (Geo-Spatial data analysis) 을 할 수 있도록 해주는 외장 확장 오픈 소스 소프트웨어 프로그램인 PostGIS 를 Greenplum docker 위에 설치하는 방법을 소개하겠습니다. 



Greenplum DB에 PostGIS 를 설치하는 가이드는 https://gpdb.docs.pivotal.io/5100/ref_guide/extensions/postGIS.html 를 참고하였습니다. 

 

  0. (사전 준비 사항) Docker를 이용하여 Greenplum DB + MADlib + PL/x 설치

CentOS + Greenplum + MADlib + PL/R + PL/Python 이 설치된 Docker Image를 이용하여 분석환경을 구성하는 자세한 내용은 https://rfriend.tistory.com/379 포스팅을 참고하기 바랍니다. 

명령 프롬프트 창을 띄우고 아래 docker 명령어로 greenplum을 간편하게 설치해보세요. 


---------------------------------

-- GPDB w/MADlib, PL/x on Docker : https://hub.docker.com/r/hdlee2u/gpdb-analytics

---------------------------------

-- (1) Docker Image Pull

$ docker pull hdlee2u/gpdb-analytics

$ docker images


-- (2) Docker Image Run(port 5432) -> Docker Container Creation

$ docker run -i -d -p 5432:5432 -p 28080:28080 --name gpdb-ds --hostname mdw hdlee2u/gpdb-analytics /usr/sbin/sshd -D

$ docker ps -a


-- (3) To Start Greenplum Database and Use psql

$ docker exec -it gpdb-ds /bin/bash

[root@mdw /]# su - gpadmin

[gpadmin@mdw ~]$ gpstart -a

.... GPDB start

....

 


CnetOS와 GPDB 버전에 맞는 PostGIS 버전을 다운로드해서 설치를 해야 합니다. IP 확인, CentOS version 확인, MADlib, PL/R 버전 확인, R & Python Data Science Package version 확인하는 방법은 아래를 참고하세요. 

- CentOS : release 7.4

- Greenplum Database : ver 5.10.2

- MADlib : ver 1.15

- PL/R : 2.3.2

- DataScienceR : 1.0.1

- DataSciencePython : 1.1.1

-------------------------------------

-- IP check

[gpadmin@mdw ~]$ 

[root@mdw ~]# cd /home/gpadmin

[root@mdw gpadmin]# ifconfig -a

eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 172.17.0.2  netmask 255.255.0.0  broadcast 172.17.255.255

        ether 02:42:ac:11:00:02  txqueuelen 0  (Ethernet)

        RX packets 25395  bytes 10372326 (9.8 MiB)

        RX errors 0  dropped 0  overruns 0  frame 0

        TX packets 25074  bytes 79368842 (75.6 MiB)

        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

... (이하 생략)


--------------------------------------

-- MADlib, PL/R, Python Data Science Package, GP Command Center version check

--------------------------------------

[root@mdw gpadmin]# cd /setup

[root@mdw setup]# ls -al

total 810088

drwxr-xr-x 4 gpadmin gpadmin      4096 Aug 13  2018 .

drwxr-xr-x 1 root    root         4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13  2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13  2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin      4096 Jul 23  2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin  29040039 Aug 13  2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10  2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13  2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin         4 Aug 13  2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin      4096 Aug 11  2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin   3023537 Aug 13  2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin  39279994 Aug 13  2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg


--------------------------------------

-- CentOS version check

[gpadmin@mdw setup]$ cat /etc/os-release 

NAME="CentOS Linux"

VERSION="7 (Core)"

ID="centos"

ID_LIKE="rhel fedora"

VERSION_ID="7"

PRETTY_NAME="CentOS Linux 7 (Core)"

ANSI_COLOR="0;31"

CPE_NAME="cpe:/o:centos:centos:7"

HOME_URL="https://www.centos.org/"

BUG_REPORT_URL="https://bugs.centos.org/"


CENTOS_MANTISBT_PROJECT="CentOS-7"

CENTOS_MANTISBT_PROJECT_VERSION="7"

REDHAT_SUPPORT_PRODUCT="centos"

REDHAT_SUPPORT_PRODUCT_VERSION="7" 



  1. Pivotal Network에서 PostGIS 다운로드

(1) https://network.pivotal.io/ 접속 (다운로드를 위해서는 회원가입 필요)

> (2) 'Pivotal Greenplum Releases: 5.10.2' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026

> (3) 'Greenplum Adnvanced Analytics' : https://network.pivotal.io/products/pivotal-gpdb#/releases/158026/file_groups/1084

> (4) 'PostGIS 2.1.5+pivotal.1 for RHEL 7' file download

의 순서대로 경로를 찾아가서 PostGIS 2.1.5+pivotal.1 for RHEL 7 파일을 다운로드 합니다. 



 2. 다운로드한 PostGIS 압축파일을 Greenplum Docker 컨테이너 안으로 복사(copy)하기 

다른 명령 프롬프트 창을 띄우고, 아래처럼 Downloads 폴더로 경로 변경 후에 docker cp 명령문으로 1번에서 다운로드한 PostGIS 2.1.5 압축 파일을 Greenplum 도커 컨테이너 안의 'gpdb-ds:/setup' 경로로 복사해주세요. 

-- [At another terminal window] Copy PostGIS 2.1.5 to GPDB-DS Docker Container

ihongdon-ui-MacBook-Pro:~ ihongdon$ pwd

/Users/ihongdon

ihongdon-ui-MacBook-Pro:~ ihongdon$ cd Downloads/

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ ls -al

-rw-r--r--@  1 ihongdon  staff  19839907  3 22 16:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

ihongdon-ui-MacBook-Pro:Downloads ihongdon$ docker cp   postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg   gpdb-ds:/setup

ihongdon-ui-MacBook-Pro:Downloads ihongdon$  



  3. gpadmin 계정에게 postgis-2.1.5 파일에 대한 권한 부여 (chown)


(1) gpadmin 으로 들어와 있는 명령 프롬프트 창으로 와서 root 계정으로 로그인 후에 => (2) chown 명령어를 이용하여 gpadmin 에 PostGIS 파일에 대한 권한을 부여해줍니다. 

-- 파일 소유자나 소유 그룹 변경 : chown

[gpadmin@mdw setup]$ su -

Password: 

Last login: Fri Mar 22 07:01:35 UTC 2019 on pts/0

[root@mdw ~]# cd /setup

[root@mdw setup]# ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin      4096 Mar 22 07:33 .

drwxr-xr-x 1 root    root         4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13  2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13  2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin      4096 Jul 23  2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin  29040039 Aug 13  2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10  2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13  2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin         4 Aug 13  2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin      4096 Aug 11  2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin   3023537 Aug 13  2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin  39279994 Aug 13  2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1     501 games    19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

[root@mdw setup]# chown  gpadmin:gpadmin  postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg 




  4. gppkg로 각 Segment 노드에 PostGIS 설치하기

(1) 명령 프롬프트 창에서 root 계정에서 exit 후 => gpadmin 계정에서 gppkg -i 로 PostGIS 2.1.5를 설치합니다. 

(2) 그러면 로그 메시지에 'gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install'라는 메시지가 나옵니다. 이 메시지를 추가로 실행시킵니다. 

-- PostGIS 2.1.5 install

[root@mdw setup]# exit  

logout

[gpadmin@mdw setup]$ ls -al

total 829464

drwxr-xr-x 1 gpadmin gpadmin      4096 Mar 22 07:33 .

drwxr-xr-x 1 root    root         4096 Mar 11 05:08 ..

-rw-r--r-- 1 gpadmin gpadmin 218258940 Aug 13  2018 DataSciencePython-1.1.1-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin 146189713 Aug 13  2018 DataScienceR-1.0.1-gp5-rhel7-x86_64.gppkg

drwxr-xr-x 2 gpadmin gpadmin      4096 Jul 23  2018 greenplum-cc-web-4.3.0-LINUX-x86_64

-rw-r--r-- 1 gpadmin gpadmin  29040039 Aug 13  2018 greenplum-cc-web-4.3.0-LINUX-x86_64.zip

-rwxr-xr-x 1 gpadmin gpadmin 197905185 Aug 10  2018 greenplum-db-5.10.2-rhel7-x86_64.bin

-rw-r--r-- 1 gpadmin gpadmin 195802895 Aug 13  2018 greenplum-db-5.10.2-rhel7-x86_64.zip

-rw-r--r-- 1 gpadmin gpadmin         4 Aug 13  2018 hostfile

drwxr-xr-x 2 gpadmin gpadmin      4096 Aug 11  2018 madlib-1.15-gp5-rhel7-x86_64

-rw-r--r-- 1 gpadmin gpadmin   3023537 Aug 13  2018 madlib-1.15-gp5-rhel7-x86_64.tar.gz

-rw-r--r-- 1 gpadmin gpadmin  39279994 Aug 13  2018 plr-2.3.2-gp5-rhel7-x86_64.gppkg

-rw-r--r-- 1 gpadmin gpadmin  19839907 Mar 22 07:28 postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg


[gpadmin@mdw setup]$ gppkg -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg 

20190322:07:36:54:011243 gppkg:mdw:gpadmin-[INFO]:-Starting gppkg with args: -i postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing package postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:55:011243 gppkg:mdw:gpadmin-[INFO]:-Installing postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg locally

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix /usr/local/greenplum-db-5.10.2'

20190322:07:36:56:011243 gppkg:mdw:gpadmin-[INFO]:-Installing rpms cmdStr='rpm -i /usr/local/greenplum-db-5.10.2/.tmp/libexpat-2.1.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/gdal-1.11.1-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/proj-4.8.0-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/postgis-2.1.5-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/json-c-0.12-1.x86_64.rpm /usr/local/greenplum-db-5.10.2/.tmp/geos-3.4.2-1.x86_64.rpm --dbpath /usr/local/greenplum-db-5.10.2/share/packages/database --prefix=/usr/local/greenplum-db-5.10.2'

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Completed local installation of postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg.

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-Please run the following commands to enable the PostGIS package: $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh mydatabase install

20190322:07:37:01:011243 gppkg:mdw:gpadmin-[INFO]:-postgis-2.1.5+pivotal.1-gp5-rhel7-x86_64.gppkg successfully installed.


[gpadmin@mdw setup]$ cd $GPHOME

[gpadmin@mdw greenplum-db]$ cd share

[gpadmin@mdw share]$ ls

gdal  greenplum  packages  postgresql  proj

[gpadmin@mdw share]$ cd postgresql/

[gpadmin@mdw postgresql]$ cd contrib/

[gpadmin@mdw contrib]$ ls

citext.sql         gp_distribution_policy.sql  gp_svec_test.sql  oid2name.txt  postgis-2.1           uninstall_fuzzystrmatch.sql           uninstall_hstore.sql

dblink.sql         gp_session_state.sql        hstore.sql        orafunc.sql   uninstall_citext.sql  uninstall_gp_distribution_policy.sql  uninstall_orafunc.sql

fuzzystrmatch.sql  gp_sfv_test.sql             indexscan.sql     pgcrypto.sql  uninstall_dblink.sql  uninstall_gp_session_state.sql        uninstall_pgcrypto.sql

[gpadmin@mdw contrib]$ cd postgis-2.1/

[gpadmin@mdw postgis-2.1]$ ls

install  postgis_manager.sh  uninstall  upgrade

[gpadmin@mdw postgis-2.1]$ $GPHOME/share/postgresql/contrib/postgis-2.1/postgis_manager.sh gpadmin install

SET

BEGIN

DO

CREATE FUNCTION

CREATE FUNCTION

CREATE FUNCTION

CREATE TYPE

CREATE FUNCTION

:

:

INSERT 0 1

INSERT 0 1

COMMIT

ANALYZE

[gpadmin@mdw postgis-2.1]$ 


자, 이제 PostGIS가 Greenplum docker 컨테이너 안에 설치가 되었습니다. 


  5. PostGIS 샘플 Query 실행해서 테스트해보기

DBeaver DB tool로 아래의 PostGIS 테이블 생성해보고 select query 를 날려보겠습니다. 

 -- PostGIS sample query

CREATE TABLE geom_test ( gid int4, geom geometry, 

  name varchar(25) );

INSERT INTO geom_test ( gid, geom, name )

  VALUES ( 1, 'POLYGON((0 0 0,0 5 0,5 5 0,5 0 0,0 0 0))', '3D Square');

INSERT INTO geom_test ( gid, geom, name ) 

  VALUES ( 2, 'LINESTRING(1 1 1,5 5 5,7 7 5)', '3D Line' );

INSERT INTO geom_test ( gid, geom, name )

  VALUES ( 3, 'MULTIPOINT(3 4,8 9)', '2D Aggregate Point' );

 


SELECT * from geom_test WHERE geom &&

  Box3D(ST_GeomFromEWKT('LINESTRING(2 2 0, 3 3 0)'));




잘 작동하는군요. ^^

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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

이번 포스팅에서는 Greenplum DB, Postgresql 에서 테이블을 생성한 후에 SQL로 데이터 전처리하는 몇 가지 방법을 소개하겠습니다. 

예제로 사용할 간단한 고객 정보 테이블을 생성하고, 행 번호, 고객번호, 이름, 나이, 성별, 지역, 등록일 값을 입력해보겠습니다. 


DROP TABLE IF EXISTS public.cust;

CREATE TABLE public.cust (

seq_num integer

, cust_id text not null

, name text not null

, age integer

, gender text

, region text

, regist_date date

);


INSERT INTO public.cust VALUES 

(1, 'A001', 'choi', 25, 'M', 'seoul', '2018-01-25'), 

(2, 'A002', 'kang', 30, 'F', 'Busan', '2019-02-08'), 

(3, 'A003', 'lee', 29, NULL, 'seoul', '2018-05-30'), 

(4, 'B001', 'kim', 35, 'F', 'seoul', '2018-12-22'), 

(5, 'B002', 'sung', 34, 'M', 'busan', '2019-02-19'),

(6, 'B003', 'park', NULL, NULL, 'SEOUL', '2019-03-15');


SELECT * FROM public.cust ORDER BY seq_num;



위의 테이블에서 

(1) 고객ID(cust_id) 문자열의 첫 번째 문자열을 가져다가 group 칼럼 만들기
    : SUBSTRING(cust_id, 1, 1) AS group

(2) group별로 seq_num 순서에 따라 행 번호 부여하기
    : ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num

(3) 나이(age) 결측값을 전체 평균 값으로 채우기 
    : COALESCE(age, AVG(age) OVER())::INTEGER AS age

(4) 성별(gender) 결측값을 "Unknown" 값으로 채우기 
    : COALESCE(gender, 'Unknown') AS gender

(5) 지역(region) 대문자를 소문자로 바꾸기 
    : LOWER(region) AS region

(6) 이름(name)이 'choi', 'park', 'lee', 'kim'은 그대로 두고, 그 외는 'others'로 바꾸어서 name_2 칼럼 만들기
    : CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name ELSE 'others' END AS name_2

-- substring of id's first character
-- insert row number
-- fill missing value of 'age' with average
-- fill missing value of 'gender' with 'Unknown'
-- convert upper letter into lower letter
-- if name IN ('choi', 'park', 'lee', 'kim') then name, else 'other'


DROP TABLE IF EXISTS public.cust_preprocessed CASCADE;

CREATE TABLE public.cust_preprocessed AS 

(

SELECT 

seq_num, 

cust_id, 

SUBSTRING(cust_id, 1, 1) AS group, 

ROW_NUMBER() OVER(PARTITION BY SUBSTRING(cust_id, 1, 1) ORDER BY seq_num) AS grp_num, 

COALESCE("age", AVG(age) OVER())::INTEGER AS "age", 

COALESCE(gender, 'Unknown') AS gender,

LOWER(region) AS region, 

name, 

CASE WHEN name IN ('choi', 'park', 'lee', 'kim') THEN name

ELSE 'others' END AS name_2, 

regist_date

FROM public.cust

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT * FROM public.cust_preprocessed ORDER BY seq_num;



다음으로 날짜 형식의 데이터에서 년(year), 월(month), 일(day), 현재 날짜(now), 입력 날짜로 부터 현재 날짜까지의 소요 일(day until now)을 계산해보겠습니다. 

(7) 등록 날짜에서 년(year) 정보 추출
  : EXTRACT (YEAR FROM regist_date)::int AS year

(8) 등록 날짜에서 월(month) 정보 추출
  : EXTRACT (MONTH FROM regist_date)::int AS month

(9) 등록 날짜에서 일(day) 정보 추출
  : EXTRACT (DAY FROM regist_date)::int AS day

(10) 현재 날짜 자동 입력
  : now()::DATE

(11) 이전 등록 날짜에서 현재까지의 소요 일 계산
  : AGE(regist_date) AS time_from_regist

 -- extract year, month, day from regist_date

DROP TABLE IF EXISTS public.cust_date CASCADE;

CREATE TABLE public.cust_date AS 

(

SELECT *, 

EXTRACT (YEAR FROM regist_date)::int AS year, 

EXTRACT (MONTH FROM regist_date)::int AS month, 

EXTRACT (DAY FROM regist_date)::int AS day, 

now()::DATE,

AGE(regist_date) AS time_from_regist

FROM public.cust_preprocessed

ORDER BY cust_id

) DISTRIBUTED RANDOMLY;

SELECT 

seq_num, regist_date, year, month, day, 

now, time_from_regist 

FROM public.cust_date 

ORDER BY seq_num;




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

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


Posted by R Friend R_Friend

댓글을 달아 주세요