만약 한개 당 1분 걸리는 동일한 프로세스의 100개의 일을 한 명이서 한다면 100분이 걸릴텐데요, 이것을 100명에게 일을 1개씩 나누어서 동시에 분산해서 시킨다면 1분(+취합하는 시간 약간) 밖에 안걸릴 것입니다. 1명이 100명을 이길 수는 없기 때문입니다. 


대용량 데이터에 대해서 빠른 성능으로 통계나 기계학습의 고급 분석을 처리해야 하는 경우라면 Greenplum 과 같은 MPP (Massively Parallel Processing) 아키텍처 기반의 DB에서 R 이나 Python 언어로 작성한 알고리즘을 In-DB에서 PL/R, PL/Python을 사용해서 분산 병렬 처리할 수 있습니다. 


이번 포스팅에서는 Greenplum DB에서 PL/R (Procedural Language R) 을 사용해서 분산 병렬처리(distributed parallel processing하여 그룹별로 선형회귀모형을 각각 적합하고 예측하는 방법을 소개하겠습니다. 모든 연산이 In-DB 에서 일어나기 때문에 데이터 I/O 가 없으므로 I/O 시간을 절약하고 architecture 와  workflow를 간단하게 가져갈 수 있는 장점도 있습니다. (vs. DB 에서 local R 로 데이터 말아서 내리고, local R로 모형 적합 / 예측 후, 이 결과를 다시 DB에 insert 하고 하는 복잡한 절차가 필요 없음)



이번에 소개할 간단한 예제로 사용할 데이터셋은 abalone 공개 데이터셋으로서, 성 (sex) 별로 구분하여 무게(shucked_weight)와 지름(diameter) 설명변수를 input으로 하여 껍질의 고리 개수(rings)를 추정하는 선형회귀모형을 적합하고, 예측하는 문제입니다. 


이러한 일을 성별 F, M, I 별로 순차적으로 하는 것이 아니라, Greenplum DB 에서 성별 F, M, I 별로 PL/R로 분산 병렬처리하여 동시에 수행하는 방법입니다. 



  (1) abalone 데이터셋으로 테이블 만들기


먼저, abalone 데이터셋을 공개 데이터셋 웹사이트에서 가져와서 External table을 만들고, 이로부터 abalone table 을 생성해보겠습니다. 



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

-- Linear Regression in Parallel 

-- using PL/R

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


-- Dataset for example: abalone dataset from the UC Irvine Machine Learning Repository

-- url: http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data

-- Create an external web table

DROP EXTERNAL TABLE IF EXISTS abalone_external;

CREATE EXTERNAL WEB TABLE abalone_external(

sex text 

, length float8

, diameter float8

, height float8

, whole_weight float8

, shucked_weight float8

, viscera_weight float8

, shell_weight float8

, rings integer -- target variable to predict

) LOCATION('http://archive.ics.uci.edu/ml/machine-learning-databases/abalone/abalone.data') 

FORMAT 'CSV' 

(null as '?');



-- Create a table of abalone

DROP TABLE IF EXISTS abalone;

CREATE TABLE abalone AS 

SELECT * FROM abalone_external

DISTRIBUTED BY (sex);



-- Viewing data distribution

SELECT gp_segment_id, COUNT(*) AS row_cnt

FROM abalone

GROUP BY gp_segment_id;




-- Check data

SELECT * FROM abalone LIMIT 5;






  (2) Train, Test set 테이블 분할 (train, test set split)


다음으로 MADlib(https://madlib.apache.org/) 의 madlib.train_test_split() 함수를 사용해서 abalone 원 테이블을 train set, test set 테이블로 분할(split into train and test set) 해보겠습니다. 



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

-- Train, Test set split

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

-- Check the number of observations per sex group(F, I, M)
SELECT
 sex, COUNT(*) FROM abalone GROUP BY sex;




-- Train, Test set split

DROP TABLE IF EXISTS out_train, out_test;

SELECT madlib.train_test_split(

'abalone',    -- Source table

'out',     -- Output table

    0.8,       -- train_proportion

    NULL,      -- Default = 1 - train_proportion = 0.5

    'sex', -- Strata definition

    'rings, shucked_weight, diameter', -- Columns to output

    FALSE,      -- Sample with replacement

    TRUE);     -- Separate output tables



SELECT * FROM out_train LIMIT 5;




SELECT sex, count(*) FROM out_train GROUP BY sex;




SELECT sex, count(*) FROM out_test GROUP BY sex;






  (3) array aggregation 하여 PL/R에서 사용할 데이터셋 준비하기


좀 낯설을 수도 있는데요, PL/R 에서는 array 를 input으로 받으므로 array_agg() 함수를 사용해서 설명변수를 칼럼별로 array aggregation 해줍니다. 이때 성별(sex) 로 모형을 각각 병렬로 적합할 것이므로 group by sex 로 해서 성별로 따로 따로 array aggregation 을 해줍니다. 이렇게 해주면 long format으로 여러개의 열(row)에 들어있던 값들이 성별로 구분이 되어서 하나의 array( { } )에 모두 들어가게 됩니다. (아래 이미지 참조)


 

-- Data Preparation

-- : array aggregation using array_agg()

DROP TABLE IF EXISTS abalone_array;

CREATE TABLE abalone_array AS 

SELECT

sex::text -- group

, array_agg(rings::float8) as rings             -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter       -- x2

FROM out_train

GROUP BY sex

DISTRIBUTED BY (sex);


SELECT * FROM abalone_array;



-- : 2D array aggregation using MADlib.matrix_agg()

CREATE TABLE mat_agg AS (

    SELECT 

sex

, madlib.matrix_agg(array[rings, shucked_weight, diameter]) AS mat_agg

    FROM abalone

    GROUP BY sex

) DISTRIBUTED BY (sex);


SELECT * FROM mat_agg;





  (4) 선형회귀모형 적합하는 PL/R 사용자 정의 함수 정의하기 (Define PL/R UDF)


선형회귀모형 PL/R 의 반환받는 값을 두가지 유형으로 나누어서 각각 소개하겠습니다.


(4-1) 적합된 회귀모형의 회귀계수 (coefficients) 를 반환하기

(4-2) 적합된 회귀모형 자체(fitted model itself)를 반환하기 



먼저, (4-1) 적합된 회귀모형의 회귀계수를 반환하는 PL/R 함수를 정의하는 방법을 소개하겠습니다. 


R의 lm() 함수를 사용하여 다중 선형회귀모형을 적합(fit a model)하면, summary(fitted_model)$coef 는 추정된 회귀계수(coef_est), 표준오차(std_error), T통계량(t_stat), P-값 (p_value) 를 반환합니다. 


CREATE OR REPLAE FUNCTION pl_r_funtion_name(column_name data_type[], ...) 으로 PL/R 함수 이름과 인자로 받는 칼럼 이름, 데이터 유형을 정의해주고, 


이들 모형 적합 후의 추정된 회귀계수와 통계량을 Greenplum DB에 반환하려면 데이터 유형이 여러개이므로 composit type 을 별도로 정의('lm_abalone_type')해주어고, PL/R 사용자 정의함수에서 returns setof lm_abalone_type 처럼 써주면 됩니다.


그 다음에, $$ pure R codes block $$ LANGUAGE 'plr' 형식으로 R codes 를 통째로 $$ ~~~~ $$ 안에 넣어주면 됩니다. 



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

-- (4-1) PL/R : Linear Regression Model's Coefficients --> Predict

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


-- Return Types

DROP TYPE IF EXISTS lm_abalone_type CASCADE;

CREATE TYPE lm_abalone_type AS (

variable text

, coef_est float

, std_error float

, t_stat float

, p_value float

);


-- PL/R User Defined Function

DROP FUNCTION IF EXISTS plr_lm_train(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_train(

rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS SETOF lm_abalone_type AS

$$

m1 <- lm(rings ~ s_weight + diameter)

m1_s <- summary(m1)$coef

temp_m1 <- data.frame(rownames(m1_s), m1_s)

return(temp_m1)

$$

LANGUAGE 'plr';

 





 (5) PL/R 실행하기 (execute PL/R UDF in Greenplum DB)


위의 (4)에서 정의한 성별(per sex) 선형회귀모형을 적합하여 회귀계수와 통계량을 반환하는 PL/R 사용자 정의함수를 Greenplum DB 안에서 병렬처리하여 실행해보겠습니다. 


select sex, (plr_lm_train(rings, s_weight, diameter)).* from abalone_array 처럼 위의 (4)번에서 정의한 PL/R 함수에 (3)번에서 준비한 array 가 들어있는 테이블의 칼럼을 써주고, from 절에 array 테이블 이름을 써주면 됩니다. 


이때 테이블에 return 받는 값들이 composit type의 여러개 칼럼들이므로 (plr_udf(column, ...)).* 처럼 PL/R 함수를 괄호 ( ) 로 싸주고 끝에 '*' (asterisk) 를 붙여줍니다. ( * 를 빼먹으면 여러개의 칼럼별로 나누어지지 않고 한개의 칼럼에 튜플로 모든 칼럼이 뭉쳐서 들어갑니다)



-- Execution of Linear Regression PL/R 

DROP TABLE IF EXISTS lm_abalone_model_coef;

CREATE TABLE lm_abalone_model_coef AS (

SELECT sex, (plr_lm_train(rings, s_weight, diameter)).* 

FROM abalone_array

) DISTRIBUTED BY (sex);


SELECT * FROM lm_abalone_model_coef;







  (6) 적합한 선형회귀모형을 사용해 test set에 대해 예측하기 (prediction on test set)


위의 (5)번에서 적합한 성별 선형회귀모형들의 회귀계수 (coefficients per sex groups) 를 사용해서 test set 의 데이터셋에 대해 PL/R 함수로 분산 병렬처리하여 rings 값을 예측해보겠습니다. (training 도 분산병렬처리, prediction/ scoring 도 역시 분산병렬처리!)


먼저, 예측하는 PL/R 함수에 넣어줄 test set을 array aggregation 해줍니다. 


다음으로, ID별로 실제값(actual rings)과 예측한 값(predicted rings)을 반환받을 수 있도록 composite type 을 정의해줍니다. 


그 다음엔 추정된 회귀계수를 사용해서 예측할 수 있도록 행렬 곱 연산을 하는 PL/R 함수(plr_lm_coef_predict())를 정의해줍니다. 


마지막으로 예측하는 PL/R 함수를 실행해줍니다. 



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

-- Prediction and Model Evaluation for Test set

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


-- Preparation of test set in aggregated array

DROP TABLE IF EXISTS test_array;

CREATE TABLE test_array AS 

SELECT

sex::text

, array_agg(rings::float8) as rings             -- y

, array_agg(shucked_weight::float8) as s_weight -- x1

, array_agg(diameter::float8) as diameter       --x2

FROM out_test

GROUP BY sex

DISTRIBUTED BY (sex);


SELECT * FROM test_array;




-- Define composite data type for predicted return values

DROP TYPE IF EXISTS lm_predicted_type CASCADE;

CREATE TYPE lm_predicted_type AS (

id int

, actual float

, pred float

);



-- Define PL/R UDF of prediction per groups using linear regression coefficients

DROP FUNCTION IF EXISTS plr_lm_coef_predict(float8[], float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_coef_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, coef_est float8[]

) RETURNS SETOF lm_predicted_type AS

$$

actual <- rings # y

intercept <- 1

X <- cbind(intercept, s_weight, diameter) # X matrix

coef_est <- matrix(coef_est) # coefficients matrix

predicted <- X %*% coef_est  # matrix multiplication

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return(data.frame(id, df_actual_pred))

$$

LANGUAGE 'plr';



-- Execute PL/R Prediction UDF

DROP TABLE IF EXISTS out_coef_predict;

CREATE TABLE out_coef_predict AS (

SELECT sex, (plr_lm_coef_predict(c.rings, c.s_weight, c.diameter, c.coef_est)).*

FROM (

SELECT a.*, b.coef_est

FROM test_array a, 

(SELECT sex, array_agg(coef_est) AS coef_est FROM lm_abalone_model_coef GROUP BY sex) b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);



-- Compare 'actual' vs. 'predicted'

SELECT * FROM out_coef_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;







  (7) 회귀모형 자체를 Serialize 해서 DB에 저장하고, Unserialize 해서 예측하기


위의 (4)번~(6번) 까지는 적합된 회귀모형의 회귀계수와 통계량을 반환하고, 이를 이용해 예측을 해보았다면, 이번에는 


- (4-2) 적합된 회귀모형 자체(model itself)를 Serialize 하여 DB에 저장하고 (인코딩)

- 이를 DB에서 읽어와서 Unserialize 한 후 (디코딩), 예측하기

- 단, 이때 예측값의 95% 신뢰구간 (95% confidence interval) 도 같이 반환하기


를 해보겠습니다. 



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

-- (2) PL/R : Linear Model --> Serialize --> Deserialize --> Predict

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


-- PL/R User Defined Function Definition

DROP FUNCTION IF EXISTS plr_lm_model(float8[], float8[], float8[]);

CREATE OR REPLACE FUNCTION plr_lm_model(

    rings float8[]

, s_weight float8[]

, diameter float8[]

) RETURNS bytea -- serialized model as a byte array

AS

$$

lr_model <- lm(rings ~ s_weight + diameter)

return (serialize(lr_model, NULL))

$$

LANGUAGE 'plr';


-- Execution of Linear Regression PL/R 

DROP TABLE IF EXISTS lm_abalone_model;

CREATE TABLE lm_abalone_model AS (

SELECT sex, plr_lm_model(rings, s_weight, diameter) AS serialized_model

FROM abalone_array

) DISTRIBUTED BY (sex);



-- We can not read serialized model

SELECT * FROM lm_abalone_model;





DROP TYPE IF EXISTS lm_predicted_interval_type CASCADE;

CREATE TYPE lm_predicted_interval_type AS (

id int

, actual float

, pred float

, lwr float

, upr float

);


-- PL/R function to read a serialized PL/R model

DROP FUNCTION IF EXISTS plr_lm_model_predict(float8[], float8[], float8[], bytea);

CREATE OR REPLACE FUNCTION plr_lm_model_predict(

rings float8[]

, s_weight float8[]

, diameter float8[]

, serialized_model bytea

) RETURNS SETOF lm_predicted_interval_type 

AS

$$

model <- unserialize(serialized_model)

actual <- rings # y

X <- data.frame(s_weight, diameter) # new data X

predicted <- predict(model, newdata = X, interval = "confidence")

df_actual_pred <- data.frame(actual, predicted)

id <- as.numeric(rownames(df_actual_pred))

return (data.frame(id, df_actual_pred))

$$

LANGUAGE 'plr';



-- Predict

DROP TABLE IF EXISTS out_model_predict;

CREATE TABLE out_model_predict AS (

SELECT sex, (plr_lm_model_predict(c.rings, c.s_weight, c.diameter, c.serialized_model)).*

FROM (

SELECT a.*, b.serialized_model

FROM test_array a, lm_abalone_model b

WHERE a.sex = b.sex

) c

) DISTRIBUTED BY (sex);


SELECT * FROM out_model_predict WHERE sex = 'F' ORDER BY sex, id LIMIT 10;



  • PL/R Reference: https://pivotalsoftware.github.io/gp-r/


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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

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;

 



  • GPDB, Postgres DB 칼럼 이름 및 데이터 유형 확인 (column name and data type)


select column_name, data_type

from information_schema.columns
where table_name ='table_name'

 



  • 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

 



  • PostgreSQL DB 서버 시작, 종료 (How to Start and Stop PostgreSQL Database Server)
- Mac OS (명령 프롬프트 창에서 Command 실행)


-- Mac OS 에서 Homebrew 사용하여 PostgreSQL 시작 (Start PostgreSQL using Homebrew on Mac OS)

$ brew services start postgresql


-- Mac OS 에서 Homebrew 사용하여 PostgreSQL 종료 (Stop PostgreSQL using Homebrew on Mac OS)

$ brew services stop postgresql

 



- Windows OS (명령 프롬프트 창에서 command 실행)


-- Windows OS 에서 PostgreSQL 시작 (Start PostgreSQL on Windows OS)

$ pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start


-- Windows OS 에서 PostgreSQL 종료 (Stop PostgreSQL on Windows OS)

pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" stop





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 DB의 PostGIS에 raster2pgsql 유틸리티를 사용하여 raster data를 import하는 방법을 소개하겠습니다. 

 

 

예제로 사용할 데이터는 'Mastering PostGIS' (by Domink 외) 에서 소개된 raster format의 TIFF(Tagged Image File Format) 데이터인 'GRAY_50M_SR_OB.tif' 파일입니다.

 

데이터 형태는 왼쪽에 보는 바와 같이 4자리의 숫자와 알파벳의 조합으로 되어 있습니다. 

 

 

 

 

 

 

 

 

 

 

이 tif 파일을 탐색기에서 미리보기 해보면 왼쪽에 보는 바와 같이 회색의 세계지도 이미지 이네요. 

 

 

 

 

 

 

그럼 먼저, 명령 프롬프트 창에서 다운로드한 'GRAY_50M_SR_OB.tif' 파일을 docker cp 명령어로 Greenplum docker 의 tmp 폴더로 복사하겠습니다. 

-- (명령 프롬프트 창에서) copy 'GRAY_50M_SR_OB.tif' file to GPDB docker

docker cp /Users/ihongdon/Documents/PostGIS/data/GRAY_50M_SR_OB/GRAY_50M_SR_OB.tif  gpdb-ds:/tmp

 

다른 명령 프롬프트 창에서 Docker GPDB 의 gpadmin 계정으로 들어가서 파일이 잘 복사되었는지 확인해보겠습니다. 

-- (docker gpdb 명령 프롬프트 창에서) raster matadata 읽기

[gpadmin@mdw tmp]$ ls -la

total 123532

drwxrwxrwt  1 root    root        4096 Apr 10 13:13 .

drwxr-xr-x  1 root    root        4096 Apr  9 07:11 ..

-rw-r--r--  1     501 games   58405694 Apr  8 06:30 GRAY_50M_SR_OB.tif

[gpadmin@mdw tmp]$

 

GPDB gpadmin 명령 프롬프트 창에서 gdalinfo 명령어로 TIFF raster 파일의 메타정보를 조회해보겠습니다. 

-- (명령 프롬프트 창에서) raster 파일의 메타정보 조회 : gdalinfo

[gpadmin@mdw tmp]$ gdalinfo GRAY_50M_SR_OB.tif

Driver: GTiff/GeoTIFF

Files: GRAY_50M_SR_OB.tif

Size is 10800, 5400

Coordinate System is:

GEOGCS["WGS 84",

    DATUM["WGS_1984",

        SPHEROID["WGS 84",6378137,298.257223563,

            AUTHORITY["EPSG","7030"]],

        AUTHORITY["EPSG","6326"]],

    PRIMEM["Greenwich",0],

    UNIT["degree",0.0174532925199433],

    AUTHORITY["EPSG","4326"]]

Origin = (-179.999999999999972,90.000000000000000)

Pixel Size = (0.033333333333330,-0.033333333333330)

Metadata:

  AREA_OR_POINT=Area

  TIFFTAG_DATETIME=2014:10:18 09:28:20

  TIFFTAG_RESOLUTIONUNIT=2 (pixels/inch)

  TIFFTAG_SOFTWARE=Adobe Photoshop CC 2014 (Macintosh)

  TIFFTAG_XRESOLUTION=342.85699

  TIFFTAG_YRESOLUTION=342.85699

Image Structure Metadata:

  INTERLEAVE=BAND

Corner Coordinates:

Upper Left  (-180.0000000,  90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"N)

Lower Left  (-180.0000000, -90.0000000) (180d 0' 0.00"W, 90d 0' 0.00"S)

Upper Right ( 180.0000000,  90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"N)

Lower Right ( 180.0000000, -90.0000000) (180d 0' 0.00"E, 90d 0' 0.00"S)

Center      (  -0.0000000,   0.0000000) (  0d 0' 0.00"W,  0d 0' 0.00"N)

Band 1 Block=10800x1 Type=Byte, ColorInterp=Gray

[gpadmin@mdw tmp]$

 

raster2pgsql 유틸리티를 사용하여 (1) 한개의 Raster 데이터셋을 import 하는 방법과, (2) 여러개의 Raster 데이터셋들을 한꺼번에 import 하는 방법으로 나누어서 소개하겠습니다. 

(1) 한개의 Raster 데이터셋을 raster2pgsql 유틸리티로 import 하기

아래처럼 명령 프롬프트 창에서 raster2pgsql 유틸리티로 'GRAY_50M_SR_OB.tif' 파일을 import 하면 'gray_50m_sr_ob' 테이블이 생성됩니다. 더불어서, 'o_2_gray_50m_sr_ob', 'o_4_gray_50m_sr_ob'라는 미리보기 테이블이 같이 생성됩니다. (아래 소개된 SQL 문이 생성, 실행됩니다). 

 

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

[ raster2pgsql 인자 설명 ]
- G: 유틸리티에 의해 지원되는 GDAL 포맷 리스트 인쇄
- s: import한 raster 데이터의 SRID 설정
-t: 타일(tile)의 폭 x 높이 크기 
-P: 타일(tile)이 같은 차원을 가지도록 오른쪽/ 아래쪽의 모자란 차원만큼을 채워줌(pad) 
-d: 테이블 삭제 및 생성(Drops and creates a table)
-a: 기존 테이블에 이어서 데이터 추가(Appends data to an existing table)
-c: 새로운 테이블 생성(Creates a new table)
-p: 준비 모드 켜기. (단지 테이블만 생성되고, 데이터 importing은 안됨)
-F: raster이름의 칼럼 추가
-l: 콤마로 구분된 overview 테이블 생성 (o__raster_table_name 이름)
-I: raster 칼럼에 GIST 공간 인덱스 생성
-C: raster 데이터 importing 후에 raster 칼럼에 표준 제약 설정

Sets the standard constraints on the raster column after the raster is imported.

* reference:  https://postgis.net/docs/using_raster_dataman.html 
----------------------------------------------------------------------------------------

 

('| psql' 뒤에 host, port, user, database name 부분에는 각자의 DB환경정보 입력)

-- (명령 프롬프트에서) Import a single raster dataset using raster2pgsql

[gpadmin@mdw tmp]$ raster2pgsql -s 4326 -C -l 2,4 -F -t 2700x2700 GRAY_50M_SR_OB.tif data_import.gray_50m_sr_ob | psql -h localhost -p 5432 -U gpadmin -d gpadmin

 

Processing 1/1: GRAY_50M_SR_OB.tif

BEGIN

NOTICE:  CREATE TABLE will create implicit sequence "gray_50m_sr_ob_rid_seq" for serial column "gray_50m_sr_ob.rid"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "gray_50m_sr_ob_pkey" for table "gray_50m_sr_ob"

CREATE TABLE

NOTICE:  CREATE TABLE will create implicit sequence "o_2_gray_50m_sr_ob_rid_seq" for serial column "o_2_gray_50m_sr_ob.rid"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "o_2_gray_50m_sr_ob_pkey" for table "o_2_gray_50m_sr_ob"

CREATE TABLE

NOTICE:  CREATE TABLE will create implicit sequence "o_4_gray_50m_sr_ob_rid_seq" for serial column "o_4_gray_50m_sr_ob.rid"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "o_4_gray_50m_sr_ob_pkey" for table "o_4_gray_50m_sr_ob"

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

NOTICE:  Adding SRID constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding alignment constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding number of bands constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding pixel type constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding nodata value constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding out-of-database constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding maximum extent constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

 addrasterconstraints

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

 t

(1 row)

NOTICE:  Adding SRID constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding alignment constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding number of bands constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding pixel type constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding nodata value constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding out-of-database constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding maximum extent constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

 addrasterconstraints

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

 t

(1 row)

NOTICE:  Adding SRID constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding alignment constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding number of bands constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding pixel type constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding nodata value constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding out-of-database constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding maximum extent constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

 addrasterconstraints

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

 t

(1 row)

 addoverviewconstraints

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

 t

(1 row)

 addoverviewconstraints

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

 t

(1 row)

COMMIT

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

 

DBeaver db tool에서 data_import.gray_50m_sr_ob 테이블을 조회해보면 아래와 같습니다. 

SELECT * FROM data_import.gray_50m_sr_ob LIMIT 10;

 

 

(2) 여러개의 Raster 데이터셋들을 한꺼번에 raster2pgsql 로 importing 하기

예제로 사용하기 위해 gdalwarp 문을 사용하여 원래의 'GRAY_50M_SR_OB.tif' raster 데이터셋을 4개의 raster 데이터셋으로 분할해보겠습니다. 

-- split into four parts using gdalwarp utility

[gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 -90 0 0 GRAY_50M_SR_OB.tif gray_50m_partial_bl.tif

Creating output file that is 5400P x 2700L.

Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done.

[gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te -180 0 0 90 GRAY_50M_SR_OB.tif gray_50m_partial_tl.tif

Creating output file that is 5400P x 2700L.

Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done.

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 -90 180 0 GRAY_50M_SR_OB.tif gray_50m_partial_br.tif

Creating output file that is 5400P x 2700L.

Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done.

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ gdalwarp -s_srs EPSG:4326 -t_srs EPSG:4326 -te 0 0 180 90 GRAY_50M_SR_OB.tif gray_50m_partial_tr.tif

Creating output file that is 5400P x 2700L.

Processing GRAY_50M_SR_OB.tif [1/1] : 0...10...20...30...40...50...60...70...80...90...100 - done.

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ ls -la

total 180572

drwxrwxrwt  1 root    root        4096 Apr 16 10:35 .

drwxr-xr-x  1 root    root        4096 Apr  9 07:11 ..

-rw-r--r--  1     501 games   58405694 Apr  8 06:30 GRAY_50M_SR_OB.tif

-rw-rw-r--  1 gpadmin gpadmin 14602098 Apr 16 10:34 gray_50m_partial_bl.tif

-rw-rw-r--  1 gpadmin gpadmin 14602098 Apr 16 10:35 gray_50m_partial_br.tif

-rw-rw-r--  1 gpadmin gpadmin 14602098 Apr 16 10:34 gray_50m_partial_tl.tif

-rw-rw-r--  1 gpadmin gpadmin 14602098 Apr 16 10:35 gray_50m_partial_tr.tif

[gpadmin@mdw tmp]$

 

이제 raster2pgsql 유틸리티로 'gray_50m_partial*.tif' 처럼 파일 이름에 '*'를 사용하여 '*' 부분에 무엇이 들어있든지 간에 '*' 이외의 파일 이름이 같다면 전부 한꺼번에 importing 해보겠습니다. ('| psql' 뒤에 host, port, user, database name 부분에는 각자의 DB환경정보 입력)

-- (명령 프롬프트에서) Importing multiple rasters at once

[gpadmin@mdw tmp]$ raster2pgsql -s 4326 -C -F -t 2700x2700 gray_50m_partial*.tif  data_import.gray_50m_partial | psql -h localhost -p 5432 -U gpadmin -d gpadmin

 

Processing 1/4: gray_50m_partial_bl.tif

BEGIN

NOTICE:  CREATE TABLE will create implicit sequence "gray_50m_partial_rid_seq" for serial column "gray_50m_partial.rid"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "gray_50m_partial_pkey" for table "gray_50m_partial"

CREATE TABLE

INSERT 0 1

Processing 2/4: gray_50m_partial_br.tif

INSERT 0 1

INSERT 0 1

Processing 3/4: gray_50m_partial_tl.tif

INSERT 0 1

INSERT 0 1

Processing 4/4: gray_50m_partial_tr.tif

INSERT 0 1

INSERT 0 1

INSERT 0 1

NOTICE:  Adding SRID constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding scale-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-X constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding blocksize-Y constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding alignment constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding number of bands constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding pixel type constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding nodata value constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding out-of-database constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

NOTICE:  Adding maximum extent constraint

CONTEXT:  SQL statement "SELECT  AddRasterConstraints( $1 ,  $2 ,  $3 , VARIADIC  $4 )"

PL/pgSQL function "addrasterconstraints" line 52 at RETURN

 addrasterconstraints

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

 t

(1 row)

COMMIT

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

 

DBeaver db tool에서 'data_import.gray_50m_partial' 테이블을 조회해 보겠습니다. 제일 마지막의 'filename' 칼럼을 보면 'gray_50m_partial_bl.tif', 'gray_50m_partial_br.tif', 'gray_50m_partial_tl.tif', 'gray_50m_partial_tr.tif' 의 4개 부분의 파일들이 들어가 있음을 알 수 있습니다. 

-- (DBeaver db tool 에서) raster file 조회
SELECT * FROM data_import.gray_50m_partial LIMIT 10;

 

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

Posted by R Friend R_Friend

댓글을 달아 주세요

이번 포스팅에서는 공간지리 데이터 포맷 중에서도 GML format, MapInfo MIF & TAB format, KML format 등의 벡터 데이터 (vector data)를 GDAL의 ogr2ogr 툴을 사용하여 PostgreSQL, Greenplum DB에 import하는 방법을 소개하겠습니다. 

 

 

 

ogr2ogr 은 GDAL(Geospatial Data Abstraction Library)의 벡터 변환 유틸리티이며, 소스파일 다운로드 및 설치는 아래 링크된 사이트를 참조하세요. 

참고로, 저는 처음에 GDAL 1.x 버전으로 깔았더니 아래처럼 importing 에 필요한 driver 를 찾을 수 없다는 에러가 나더군요. 그래서 GDAL2.4.1 최신 버전으로 새로 설치를 했더니 문제가 해결되었습니다. (Thanks Jack!)

ERROR 1: Unable to find driver PostgreSQL'.
  The following drivers are available:
    ->PCIDSK' -> JP2OpenJPEG'
    ->PDF' -> ESRI Shapefile'
    ->MapInfo File' -> UK .NTF'
    ->OGR_SDTS' -> S57'
    ->DGN' -> OGR_VRT'
    ->REC' -> Memory'
    ->BNA' -> CSV'
    ->GML' -> GPX'
    ->KML' -> GeoJSON'

         :

 

(1) GML 포맷의 공간지리 벡터 데이터 Import 하기

 

포스팅에 사용한 샘플 데이터(sx9090.gml 주소 데이터)와 예제 코드는 'Mastering PostGIS' (by Dominikwicz 외) 을 참고하였습니다. 

 

docker로 Greenplum DB 설치하고 PostGIS 설치, 시작하는 방법은 https://rfriend.tistory.com/435 를 참고하세요. 

 

자, 샘플 데이터를 다운로드 했다면 이제 시작해볼까요?

먼저 명령 프롬프트 창에서 sx9090.gml 파일을 docker cp로 Greenplum DB w/PostGIS 의 tmp 경로로 복사를 하겠습니다. 

-- (명령 프롬프트에서) sx9090.gml 파일을 docker gpdb에 복사

ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-addressbase-gml-sample-data/sx9090.gml gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:data ihongdon$

 

다른 명령 프롬프트 창에서 Docker의 Greenplum DB의 gpadmin 계정으로 tmp 폴더를 확인해보면 sx9090.gml 파일이 잘 복사되었음을 확인할 수 있습니다. 

-- (docker gpdb 명령 프롬프트에서) importing GML data

[gpadmin@mdw gdal-2.4.1]$ cd /tmp

[gpadmin@mdw tmp]$ ls

2.5_day_age.kml     a.sql                         gdal-2.4.1                 hsperfdata_root   ne_110m_coastline.dbf  ne_110m_coastline.shx  sx9090.gml

[gpadmin@mdw tmp]$

 

명령 프롬프트 창에서 ogrinfo 유틸리티로 sx9090.gml 데이터셋의 메타데이터(metadata) 정보를 알아보겠습니다. 2015년에 GeoPlace가 만든 Ordnance Survey의 주소(address) 공간지리 데이터셋이네요. 

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml

INFO: Open of `sx9090.gml'

      using driver `GML' successful.

Metadata:

  DESCRIPTION=Ordnance Survey (c) Crown Copyright. All rights reserved, 2015 and produced by GeoPlace

1: Address (Point)

[gpadmin@mdw tmp]$

 

ogrinfo 유틸리티로 '1: Address (Point)' layer 정보를 더 자세히 살펴보겠습니다. (ogrinfo의 -so 파라미터는 요약 정보만 보여달라는 의미임) 

gml_id를 key로 하고, 총 22개의 칼럼을 가진 공간지리 데이터셋이네요. 

[gpadmin@mdw tmp]$ ogrinfo sx9090.gml Address -so

INFO: Open of `sx9090.gml'

      using driver `GML' successful.

Metadata:

  DESCRIPTION=Ordnance Survey (c) Crown Copyright. All rights reserved, 2015 and produced by GeoPlace

Layer name: Address

Geometry: Point

Feature Count: 42861

Extent: (-3.560100, 50.699470) - (-3.488340, 50.744770)

Layer SRS WKT:

GEOGCS["ETRS89",

    DATUM["European_Terrestrial_Reference_System_1989",

        SPHEROID["GRS 1980",6378137,298.257222101,

            AUTHORITY["EPSG","7019"]],

        TOWGS84[0,0,0,0,0,0,0],

        AUTHORITY["EPSG","6258"]],

    PRIMEM["Greenwich",0,

        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.0174532925199433,

        AUTHORITY["EPSG","9122"]],

    AUTHORITY["EPSG","4258"]]

gml_id: String (0.0) NOT NULL

uprn: Real (0.0)

osAddressTOID: String (20.0)

udprn: Integer (0.0)

subBuildingName: String (25.0)

buildingName: String (36.0)

thoroughfare: String (27.0)

postTown: String (6.0)

postcode: String (7.0)

postcodeType: String (1.0)

rpc: Integer (0.0)

country: String (1.0)

changeType: String (1.0)

laStartDate: String (10.0)

rmStartDate: String (10.0)

lastUpdateDate: String (10.0)

class: String (1.0)

buildingNumber: Integer (0.0)

dependentLocality: String (27.0)

organisationName: String (55.0)

dependentThoroughfare: String (27.0)

poBoxNumber: Integer (0.0)

doubleDependentLocality: String (21.0)

departmentName: String (37.0)

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

ogr2ogr 로 sm9090.gml 데이터셋을 PostgreSQL, Greenplum DB에 import 해보겠습니다. 아래 ogr2ogr에서 사용한 파라미터들의 기능은 아래와 같으며, 이 외에 ogr2ogr 의 여러 파라미터 기능은 https://www.gdal.org/ogr2ogr.html 를 참고하세요. DB접속 정보는 각자 자신의 host, port, user, dbname 을 설정해주시면 됩니다. 

  • -f : 아웃풋의 포맷이며, PostGIS로 importing할 경우 -f "PostgreSQL" 이라고 해주면 됩니다. 

  • -nln : Importing 할 DB 스키마와 테이블 이름 (예: data_import 스키마의 osgb_address_base_gml 테이블 이름)

  • geomfield : 공간 필터가 동작하는 geometry field의 이름

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" sx9090.gml -nln data_import.osgb_address_base_gml -geomfield geom

[gpadmin@mdw tmp]$

 

이제 DBeaver query tool에서 data_import.osgb_address_base_gml 테이블을 조회해보겠습니다. 

SELECT * FROM data_import.osgb_address_base_gml ORDER BY gml_id LIMIT 10;

 

SELECT gml_id, uprn, osaddresstoid, wkb_geometry 

FROM data_import.osgb_address_base_gml 

ORDER BY gml_id 

LIMIT 10;

 

 

 (2) MIF 포맷 (MapInfo formats) 데이터셋을 ogr2ogr 유틸리티로 PostGIS에 Import 하기

다음으로 MIF 포맷(MapInfo formats)데이터셋을 import 하는 방법을 소개할텐데요, 위에서 GML 포맷 데이터 import하는 방법과 동일합니다. 먼저 명령 프롬프트 창에서 docker cp 를 사용해서 EX_sample.mif 이름의 MIF 파일을 docker GPDB로 복사해서 넣겠습니다. (VM 환경에서 GPDB 사용 시 scp 로 파일 복사)

-- (명령 프롬프트 창에서) MIF 파일을 docker gpdb로 복사해서 넣기

ihongdon-ui-MacBook-Pro:data ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/os-code-point-polygons-mif-sample-data/EX_sample.mif  gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:data ihongdon$

 

다른 명령 프롬프트 창에서 docker GPDB의 gpadmin 계정으로 들어가서 /tmp 경로에 들어있는 파일을 조회해보면 EX_sample.mif 파일이 잘 복사되었음을 알 수 있습니다. 

-- 

[gpadmin@mdw]$ cd /tmp

[gpadmin@mdw tmp]$ ls -la

total 50844

drwxrwxrwt 1 root    root        4096 Apr 10 01:42 .

drwxr-xr-x 1 root    root        4096 Apr  9 07:11 ..

drwxrwxrwt 2 root    root        4096 Sep 11  2017 .ICE-unix

drwxrwxrwt 2 root    root        4096 Sep 11  2017 .Test-unix

drwxrwxrwt 2 root    root        4096 Sep 11  2017 .X11-unix

drwxrwxrwt 2 root    root        4096 Sep 11  2017 .XIM-unix

drwxrwxrwt 2 root    root        4096 Sep 11  2017 .font-unix

srwxrwxr-x 1 gpadmin gpadmin        0 Mar 22 07:19 .s.GPMC.sock

srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.40000

-rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.40000.lock

srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.40001

-rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.40001.lock

srwxrwxrwx 1 gpadmin gpadmin        0 Apr 10 01:20 .s.PGSQL.5432

-rw------- 1 gpadmin gpadmin       25 Apr 10 01:20 .s.PGSQL.5432.lock

-rw-r--r-- 1     501 games    3624013 Apr  8 06:12 EX_sample.mif

[gpadmin@mdw tmp]$

 

명령 프롬프트 창에서 ogrinfo 유틸리티로 EX_sample.mif의 메타 데이터와 요약 설명을 알아보겠습니다. 

[gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif

INFO: Open of `./EX_sample.mif'

      using driver `MapInfo File' successful.

1: EX_sample

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$ ogrinfo ./EX_sample.mif EX_Sample -so

INFO: Open of `./EX_sample.mif'

      using driver `MapInfo File' successful.

Layer name: EX_sample

Geometry: Unknown (any)

Feature Count: 4142

Extent: (281282.800000, 85614.570000) - (300012.000000, 100272.000000)

Layer SRS WKT:

PROJCS["unnamed",

    GEOGCS["unnamed",

        DATUM["OSGB_1936",

            SPHEROID["Airy 1930",6377563.396,299.3249646],

            TOWGS84[375,-111,431,0,0,0,0]],

        PRIMEM["Greenwich",0],

        UNIT["degree",0.0174532925199433]],

    PROJECTION["Transverse_Mercator"],

    PARAMETER["latitude_of_origin",49],

    PARAMETER["central_meridian",-2],

    PARAMETER["scale_factor",0.9996012717],

    PARAMETER["false_easting",400000],

    PARAMETER["false_northing",-100000],

    UNIT["Meter",1.0]]

POSTCODE: String (8.0)

UPP: String (20.0)

PC_AREA: String (2.0)

[gpadmin@mdw tmp]$

 

준비가 되었으니 ogr2ogr 로 EX_sample.mif 데이터셋을 data_import.osgb_code_point_polygons_mif 라는 이름으로 Greenplum DB에 import 하겠습니다. (아래 PG: "xxxx" 안의 DB 설정 정보는 각자 자신의 것으로 입력해주면 됨)

  • -lco GEOMETRY_NAME : 레이어 생성 옵션 (디폴트 wkb_geometry)
  • -s_srs : input SRID
  • -a_srs : output SRID

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" EX_sample.mif -nln data_import.osgb_code_point_polygons_mif -lco GEOMETRY_NAME=geom -a_srs EPSG:27700

[gpadmin@mdw tmp]$

 

DB query tool에서 data_import.osgb_code_point_polygons_mif 테이블을 조회해보면 아래와 같이 POLYGON 공간지리 정보가 들어있는 테이블이 잘 생성되었음을 알 수 있습니다. 

-- DBeaver에서 조회

SELECT * FROM data_import.osgb_code_point_polygons_mif ORDER BY ogc_fid LIMIT 10;

 

 

 (3) KML(Keyhole Markup Language) 데이터셋을 ogr2ogr 유틸리티로 PostgreSQL, Greenplum DB에 import 하기

KML (Keyhole Markup Language) 데이터셋은 Google Earth에서 2D 혹은 3D로 웹브라우저 상에서 시각화할 수 있는 XML 기반의 공간지리 데이터 포맷입니다. 

 

PostgreSQL, Greenplum DB에 KML 포맷 데이터를 Import 할 때도 GDAL의 ogr2ogr 유틸리티를 사용합니다. 

 

먼저, 명령 프롬프트 창에서 docker cp 로 '2.5_day_age.kml' 데이터셋을 Greenplum DB docker container로 복사하겠습니다. 

-- (1) Copy '2.5_day_age.kml' file to GPDB

ihongdon-ui-MacBook-Pro:~ ihongdon$ docker cp /Users/ihongdon/Documents/PostGIS/data/usgs-earthquakes/2.5_day_age.kml  gpdb-ds:/tmp

ihongdon-ui-MacBook-Pro:~ ihongdon$

 

 

다음으로, 다른 명령 프롬프트에서 Greenplum gpadmin 계정으로 들어가서 파일이 잘 복사가 되었는지 확인해보겠습니다. 

-- (2) (GPDB 명령 프롬프트 창에서) orginfo => 4개의 layer가 있음

[gpadmin@mdw tmp]$ ls -la

total 123532

drwxrwxrwt  1 root    root        4096 Apr 10 13:13 .

drwxr-xr-x  1 root    root        4096 Apr  9 07:11 ..

drwxrwxrwt  2 root    root        4096 Sep 11  2017 .ICE-unix

drwxrwxrwt  2 root    root        4096 Sep 11  2017 .Test-unix

drwxrwxrwt  2 root    root        4096 Sep 11  2017 .X11-unix

drwxrwxrwt  2 root    root        4096 Sep 11  2017 .XIM-unix

drwxrwxrwt  2 root    root        4096 Sep 11  2017 .font-unix

srwxrwxr-x  1 gpadmin gpadmin        0 Mar 22 07:19 .s.GPMC.sock

srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.40000

-rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.40000.lock

srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.40001

-rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.40001.lock

srwxrwxrwx  1 gpadmin gpadmin        0 Apr 16 05:36 .s.PGSQL.5432

-rw-------  1 gpadmin gpadmin       27 Apr 16 05:36 .s.PGSQL.5432.lock

-rw-r--r--  1 gpadmin gpadmin     4787 Apr  8 06:21 2.5_day.csv

-rw-r--r--  1     501 games      30548 Apr  8 06:21 2.5_day_age.kml

[gpadmin@mdw tmp]$

 

 

ogrinfo 명령어로 '2.5_day_age.kml' 데이터의 메타정보를 확인해보겠습니다. Layer가 총 4개 있고, 3D Point 정보가 들어있는 KML 포맷을 공간지리 데이터셋임을 알 수 있습니다. 

-- (3) metadata info.

[gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml

INFO: Open of `2.5_day_age.kml'

      using driver `KML' successful.

1: Magnitude 5 (3D Point)

2: Magnitude 4 (3D Point)

3: Magnitude 3 (3D Point)

4: Magnitude 2 (3D Point)

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

 

ogrinfo 2.5_day_age.kml -al -so메타정보의 4개 Layer에 대한 상세 정보를 확인해보겠습니다. 

-- (4) review metadata for each layer at once in depth

[gpadmin@mdw tmp]$ ogrinfo 2.5_day_age.kml -al -so

INFO: Open of `2.5_day_age.kml'

      using driver `KML' successful.

Layer name: Magnitude 5

Geometry: 3D Point

Feature Count: 2

Extent: (-101.000100, -36.056300) - (120.706400, 13.588200)

Layer SRS WKT:

GEOGCS["WGS 84",

    DATUM["WGS_1984",

        SPHEROID["WGS 84",6378137,298.257223563,

            AUTHORITY["EPSG","7030"]],

        AUTHORITY["EPSG","6326"]],

    PRIMEM["Greenwich",0,

        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.0174532925199433,

        AUTHORITY["EPSG","9122"]],

    AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 4

Geometry: 3D Point

Feature Count: 8

Extent: (-93.869400, -30.966800) - (127.154100, 41.012000)

Layer SRS WKT:

GEOGCS["WGS 84",

    DATUM["WGS_1984",

        SPHEROID["WGS 84",6378137,298.257223563,

            AUTHORITY["EPSG","7030"]],

        AUTHORITY["EPSG","6326"]],

    PRIMEM["Greenwich",0,

        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.0174532925199433,

        AUTHORITY["EPSG","9122"]],

    AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 3

Geometry: 3D Point

Feature Count: 6

Extent: (-155.372167, 18.242700) - (-64.691100, 36.431400)

Layer SRS WKT:

GEOGCS["WGS 84",

    DATUM["WGS_1984",

        SPHEROID["WGS 84",6378137,298.257223563,

            AUTHORITY["EPSG","7030"]],

        AUTHORITY["EPSG","6326"]],

    PRIMEM["Greenwich",0,

        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.0174532925199433,

        AUTHORITY["EPSG","9122"]],

    AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

Layer name: Magnitude 2

Geometry: 3D Point

Feature Count: 9

Extent: (-154.990005, 17.871900) - (-65.022300, 63.207400)

Layer SRS WKT:

GEOGCS["WGS 84",

    DATUM["WGS_1984",

        SPHEROID["WGS 84",6378137,298.257223563,

            AUTHORITY["EPSG","7030"]],

        AUTHORITY["EPSG","6326"]],

    PRIMEM["Greenwich",0,

        AUTHORITY["EPSG","8901"]],

    UNIT["degree",0.0174532925199433,

        AUTHORITY["EPSG","9122"]],

    AUTHORITY["EPSG","4326"]]

Name: String (0.0)

Description: String (0.0)

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

 

마지막으로, 명령 프롬프트 창에서 ogr2ogr 유틸리티로 PosgreSQL, Greenplum DB에 KML 파일을 Import 해보겠습니다. (사용하고 있는 DB의 host, port, user, DBname 으로 설정 변경해주세요.) 

제일 마지막에 '-append' 인자는 '2.5_day_age.kml' 데이터셋의 4개 Layer를 하나씩 순차적으로 읽어서 먼저 읽은 데이터셋 뒤에 붙여넣기로 Import 하라는 뜻입니다. ('-append' 인자를 추가하지 않으면 기존에 테이블이 존재한다는 에러 메시지가 뜹니다).  아래처럼 Warning 메시지가 나왔으면 잘 Import 가 된 것입니다. 

-- (5) Import KML dataset to GPDB

[gpadmin@mdw tmp]$ ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 user=gpadmin dbname=gpadmin" 2.5_day_age.kml -nln data_import.usgs_earthquakes_kml -lco GEOMETRY_NAME=geom -append

Warning 1: Layer creation options ignored since an existing layer is

         being appended to.

Warning 1: Layer creation options ignored since an existing layer is

         being appended to.

Warning 1: Layer creation options ignored since an existing layer is

         being appended to.

[gpadmin@mdw tmp]$

[gpadmin@mdw tmp]$

 

데이터가 잘 Import 되었으니 DBeaver DB tool에서 SQL query로 데이터를 조회해 보겠습니다. 

-- (DBeaver tool에서) Select KML dataset
SELECT * FROM data_import.usgs_earthquakes_kml LIMIT 10;

 

서두에 KML 데이터 포맷이 Google Earth 에서 2D, 3D로 시각화해볼 수 있다고 소개하였습니다. 실제로 Google Earth 애플리케이션에서 '2.5_day_age.kml' 데이터셋을 시각화해보면 아래와 같습니다. 

 

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

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

댓글을 달아 주세요

이번 포스팅에서는 Greenplum DB, Postgresql DB에서 중복된 관측치(duplicated observations, duplicated rows)가 있을 경우에 제일 처음나 제일 마지막의 관측치 하나만 남겨놓고 나머지 중복 관측치들은 삭제하여 유일한 관측치만 남기는 2가지 방법을 소개하겠습니다. 


(방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


(방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거(drop)하기

            : CREATE TABLE new_table & DROP TABLE original_table






  (방법 1) 원래의 테이블에서 중복된 관측치들 중에서 하나만 남기고 나머지 중복된 관측치들은 삭제하기

            : DELETE FROM original_table


Greenplum Database에 중복된 관측치가 들어있는 간단한 예제 테이블을 만들어보겠습니다. 'name'과 'price'의 두 개 변수를 기준으로 해서 중복 여부를 판단하여 중복된 관측치를 제거하는 예제입니다. 



drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


select * from prod_master;

 





이제 DELETE query를 사용하여 중복된 관측치 중에서 첫번째 것만 남기고 나머지 중복된 관측치들은 제거해보겠습니다. DELETE 문은 'DELETE FROM table_name WHERE [conditio];' 의 형태로 사용합니다. 


이때 주의할 점은 sub query로 row_number() over (partition by ) 라는 window function을 사용해야만 중복된 관측치들 중에서 각각의 "첫번째 관측치"를 남겨놓을 수 있다는 것입니다 (아래 query의 빨간색 부분).  자칫 잘못하면 중복이 된 값은 하나도 남김없이 모조리 삭제하는 실수를 범할 수 있으니 조심하시기 바랍니다. 



delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1

);

 


[Messages]

DELETE 4 Query returned successfully in 177 msec



select * from prod_master;






혹시 중복된 관측치들 중에서 "가장 앞에 있는"(위의 예시) 관측치 대신에 "가장 뒤에 있는" 관측치를 남기고 나머지 중복된 관측치는 제거하고 싶다면 row_number() over() 의 window function 에서 order by id desc 를 사용해주면 됩니다. 



--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

) distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);



---- keep the last observation in case of duplication

delete from prod_master where id in (

select id 

from 

(select id, 

row_number() over (partition by name, price order by id desc) as row_num 

from prod_master) a

where a.row_num > 1

);


select * from prod_master;


 



위의 방법 1은 원래의 테이블을 그대로 유지한 상태에서 중복된 관측치를 삭제하므로, 새로운 테이블을 만들거나 기존 테이블을 삭제할 필요가 없습니다만, 대용량 데이터를 대상으로 다수의 중복된 관측치를 제거해야 하는 경우 (아래의 방법2 대비 상대적으로) 속도가 느리다는 단점이 있습니다.  대용량 데이터의 경우 빠른 속도로 중복처리하려면 아래의 '방법2'를 고려해보길 권합니다. ('Messages'에 나오는 실행 속도를 비교해보면 아래의 '방법2'가 빠른 것을 알 수 있습니다. 지금 예제야 관측치 7개짜리의 간단한 예제인지라 177 msec vs. 118 msec로 밀리세컨 단위 차이라고 무시할 수도 있겠지만, 데이터가 대용량이 되면 차이가 무시할 수 없게 커질 수 있습니다.)




  (방법 2) 중복된 관측치들중에서 하나씩만 가져와서 새로운 테이블 만들고, 원래의 테이블은 제거하기

            : CREATE TABLE new_table & DROP TABLE original_table


 

--- Create a sample table

drop table if exists prod_master;

create table prod_master (

id int not null

, name text not null

, price real not null

distributed randomly;


insert into prod_master values 

(1, 'a', 1000)

, (2, 'a', 1000)

, (3, 'a', 1000)

, (4, 'b', 2000)

, (5, 'b', 2000)

, (6, 'c', 3000)

, (7, 'c', 3000);


---- keep the first observation in case of duplication by creating a new table

drop table if exists prod_master_unique;

create table prod_master_unique as (

select * from prod_master 

where id NOT IN (

select id

from 

(select id, 

row_number() over (partition by name, price order by id) as row_num 

from prod_master) a

where a.row_num > 1)

) distributed randomly;




[Messages]

SELECT 3 Query returned successfully in 118 msec.

 



select * from prod_master_unique order by id;





-- Drop the original table to save disk storage

drop table prod_master;

 




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


Posted by R Friend R_Friend

댓글을 달아 주세요