로컬 PC 환경에서 공부, 토이 프로젝트 아니면 프로토타입핑을 위해서 작은 데이터셋을 가지고 R로 분석을 진행하는 경우라면 R 코드 상의 오류, 버그 등에 큰 관심을 가지지 않습니다. 왜냐하면 에러나 경고메시지가 났을 때 큰 비용을 들이지 않고 로그를 보고 수정을 하면 되기 때문입니다. 


하지만 R 코드를 활용해서 애플리케이션(application production)을 만들고, 이를 다수의 사용자를 대상으로 시스템 상에서 운영(operation) 을 하는 경우라면 얘기가 달라집니다. R 의 에러, 버그는 서비스를 하는 시스템 전체의 장애를 야기할 수도 있기 때문입니다. 


그래서 R을 위한 R코드가 아니라 서비스를 통한 ROI 창출을 위한 production, operation 이 최종 목표라면 장애, 오류, 예외에 견고한 R 코드를 짜는 것이 꼭 필요합니다. 



[ 오류에 견고한 R 코드를 위해 tryCatch() 를 사용한 예외 처리 ]



이번 포스팅에서는 Greenplum, PostgreSQL에서 PL/R (Procedural Language R) 함수 코드를 짤 때, 오류에 견고한 운영을 위해 tryCatch() 를 이용한 PL/R 예외 처리 방법을 소개하겠습니다. 


먼저 R tryCatch() 함수의 syntax를 살펴보겠습니다. tryCatch() 함수는 안에 expr, error (optional), warning (optional), finally (optional) 의 4개 인자를 원소로 가지는 구조입니다. 


expr 에는 실행할 코드를 써주는데요, required 사항이므로 꼭 써줘야 합니다. 이때 if, else if, else 등의 조건절을 추가해서 분기절을 사용하여 좀더 복잡한 코드를 수행할 수도 있습니다. 


error 에는 위의 expr 의 코드를 평가하는 중에 error가 발생할 경우에 수행할 코드를 써주며, optional 한 부분입니다. 


warning 에는 위의 expr 의 코드를 평가하는 중에 warning 이 발생할 경우에 수행할 코드를 써주며, optional 한 부분입니다. 


finally 에는 위의 expr, error, warning에 상관없이 tryCatch call을 종료하기 전에 항상 수행할 코드를 써줍니다. (가령, R의 temp 객체를 제거한다든지, DB connect 을 close 한다던지, R 코드 수행이 종료되는 날짜/시간을 로그로 남기고 싶다든지...) 


(* Python의 try, except, else, finally 절을 이용한 예외 처리와 비슷합니다. 

  참고 ==> https://rfriend.tistory.com/467 )


[ R tryCatch() syntax ]



tryCatch(

    expr = {

        # Your code here...

        # ...

    },

    error = function(e)

        # (Optional)

        # Do this if an error is caught...

    },

    warning = function(w){

        # (Optional)

        # Do this if an warning is caught...

    },

    finally = {

        # (Optional)

        # Do this at the end before quitting the tryCatch structure...

    }

)


* reference: https://rsangole.netlify.app/post/try-catch/



간단한 예를 들기 위해 두개의 숫자로 나누기를 했을 때 

  (1) 정상적으로 수행되는 경우

  (2) 분모에 '0' 이 있어 별도 메시지를 반환하는 경우

  (3) 분모에 '문자열'이 들어가서 error 가 발생한 경우

의 3가지 유형별로 R tryCatch() 함수를 사용하여 예외처리를 할 수 있도록 PL/R 코드를 짜는 방법을 소개하겠습니다.  (물론 SQL로 두 칼럼을 사용해 나눗셈('/')을 할 수 있습니다. 이 PL/R 코드는 tryCatch 를 소개하기 위한 예제일 뿐입니다)


먼저, 정상적으로 수행되는 경우에 사용할 예제 테이블을 만들어보겠습니다. 



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

-- PL/R on Greenplum, PostgreSQL DB

-- : robust PL/R codes using tryCatch(), handling error or warnings

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


drop table if exists tbl_sample;

create table tbl_sample (

grp varchar(6) not null

, id integer

, x1 integer

, x2 integer

);


insert into tbl_sample values 

('a', 1, 5, 10)

, ('a', 2, 10, 10)

, ('a', 3, 15, 10)

, ('b', 4, 20, 10)

, ('b', 5, 30, 10)

, ('b', 6, 40, 10);


select * from tbl_sample order by id;


 




  (1) 나눗셈을 하는 PL/R 코드 : 정상 수행되는 경우


원래 두 개의 정수를 input으로 받고 두 정수의 나눗셈 결과를 float8 로 반환하는 것이 맞는데요, 이번 예제에서는 warning message와 error 를 반환하는 PL/R 코드를 어거지로 만들다 보니 returns setof text 로 해서 텍스트를 반환하라고 PL/R 코드를 짰습니다. (혹시 왜 float8 이 아니라 text로 반환받는지 궁금해하시는 분이 계실까봐....) 


아래에 PL/R 코드의 $$ pure R codes block $$ 안에 tryCatch() 함수에 

 - expr : if, else 조건절을 사용하여 분모가 '0' 이면 "Denominator should not be Zero" 경고 메시지를 텍스트로 반환,  분모가 '0'이 아니면 나눗셈 결과를 텍스트로 반환

 - error : expr 코드 평가 중에 error 있으면 error 발생 시점을 메시지로 프린트하고, DB에 에러 메시지 텍스트로 반환

 - warning : expr 코드 평가 중에 warning 있으면 warning 발생 시점을 메시지로 프린트하고, DB에 에러 메시지 텍스트로 반환

 - finally : expr, error, warning 에 상관없이 tryCatch() call 을 종료하기 전에 마지막으로 "All done, quitting." 메시지 프린트

하도록 짠 코드입니다. 


위에서 작성한 public.tbl_sample 테이블에서 정수형인 x1과 x2 칼럼을 가져다가 array_agg() 해서 plr_divide() PL/R 함수를 실행했으므로 아무런 error나 warning 없이 정상 작동하였습니다. 



-- (case 1) PL/R works well without error or warning

-- define PL/R UDF

drop function if exists plr_divide(int[], int[]);

create or replace function plr_divide(

x1 int[]

, x2 int[]

) 

returns setof text  -- float8

as

$$

divide_tryCatch <- function(x_numerator, x_denominator){

  tryCatch(

    expr = {

      if (x_denominator == 0) {

        message("Denominator should not be Zero")

        return("Denominator should not be Zero")

        } else {

          result <- x_numerator / x_denominator

          return(result)

        }

      }, 

    error = function(e) {

      message("** Error at ", Sys.time(), " **")

      print(e)

      return(e[1])

      }, 

    warning = function(w){

      message("** Warning at ", Sys.time(), " **")

          print(w)

          return(w[1])

          }, 

    finally = {

      message("All done, quitting.")

      }

    )

}

result <- divide_tryCatch(x1, x2)

return(result)

$$ language 'plr';



-- execute PL/R

select 

grp

, unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide(x1_arr, x2_arr) as divided 

from (

select 

grp

, array_agg(x1::int) as x1_arr

, array_agg(x2::int) as x2_arr

from tbl_sample

group by grp

) a;






  (2) 나눗셈을 하는 PL/R 코드 : 분모에 '0' 이 들어있어 별도 메시지를 반환하는 경우


다음으로 분모에 '0'이 들어간 경우에 위의 (1)번에서 정의한 plr_divide() PL/R 사용자 정의 함수를 실행시켰을 때 if else 조건절의 '분모가 '0'인 경우 "Denominator should not be Zero" 텍스트 메시지를 DB에 반환하라고 한 사례입니다. 



-- (case 2) PL/R returns a pre-defined message: Non-Zero error

-- execute PL/R UDF

select 

unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide(x1_arr, x2_arr) as divided 

from (

select 

array[1] as x1_arr

, array[0] as x2_arr -- '0' in denominator

) a;






  (3) 나눗셈을 하는 PL/R 코드 : 분모에 '문자열'이 들어가서 error 가 발생한 경우


아래 코드는 강제로 error를 발생시키기 위해서 억지로 분모(denominator)에 텍스트 array를 받아서 R로 나눗셈 시 "non-numeric argument to binary operator" 에러 메시지를 반환하도록 한 PL/R 코드입니다. 위의 (1)번에서 짰던 정상적인 경우와는 달리 plr_divide2() PL/R UDF의 'x2' 가 text[] 인 점이 다릅니다. 


error = function(e) {

   # 실행할 코드

    return (e[1])

 } 


에서 에러 객체 'e' 가 리스트 형태이므로 return (e[1]) 처럼 리스트의 [1] 번째 객체를 반환하라고 명시적으로 인덱싱 해올 수 있게끔 [1] 을 e 뒤에 꼭 붙여줘야 합니다. (PL/R 결과 반환 시 text 를 DB에 반환하라고 정의했으므로 return(e[1]) 이 아니라 return(e) 라고 하면 PL/R 실행 시 SQL 에러 발생합니다. 꼼꼼히 안보면 실수하기 쉽습니다.)



-- (case 3) PL/R raises an error and tryCatch runs 'error' part

-- define PL/R UDF

drop function if exists plr_divide2(int[], text[]);

create or replace function plr_divide2(

x1 int[]

, x2 text[]

) 

returns setof text

as

$$

divide_tryCatch <- function(x_numerator, x_denominator){

  tryCatch(

    expr = {

      if (x_denominator == 0) {

        message("Denominator should not be Zero")

        return("Denominator should not be Zero")

        } else {

          result <- x_numerator / x_denominator

          return(result)

          }

      }, 

    error = function(e) {

      message("** Error at ", Sys.time(), " **")

      #print(e)

      return(e[1])

      }

}

result <- divide_tryCatch(x1, x2)

return(result)

$$ language 'plr';



-- execute PL/R : tryCatch() runs 'error' part

select 

unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide2(x1_arr, x2_arr) as divided 

from (

select 

array[1] as x1_arr

, array['ggg'] as x2_arr -- it raises an error

) a;





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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

  1. flydodo 2020.04.16 21:32  댓글주소  수정/삭제  댓글쓰기

    500번째 포스팅~! 대단해요~♥

  2. 매드립 2020.04.17 16:53  댓글주소  수정/삭제  댓글쓰기

    이사님, 500포스팅 축하 드립니다.
    항상 잘 보고 있습니다!

지난번 포스팅에서는 Greenplum & PostgreSQL DB에서 MADlib을 활용하여 그룹별 선형회귀모형을 적합 및 예측하는 방법(https://rfriend.tistory.com/533)을 소개하였습니다. 


이번 포스팅에서는 Greenplum & PostgreSQL DB에서 PivotalR 을 사용하여 그룹별 선형회귀모형을 적합 및 예측(fitting and prediction of linear regression models by sex groups with big data using PivotalR on Greenplum DB in parallel)하는 방법을 소개하겠습니다. 


[ Agenda ]

1. PivotalR package 소개

2. web site에서 abalone 데이터셋 가져와서 table 만들기

3. MADlib을 사용하여 DB에서 훈련, 검증 데이터셋 테이블 분

4. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합

5. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합결과 조회

6. PivotalR을 사용하여 성별 그룹별 선형회귀모형을 이용한 예측 및 모델 성능 평가



  1. PivotalR package 소개


[ PivotalR 아키텍처 구조 ]


* soruce: https://cran.r-project.org/web/packages/PivotalR/vignettes/pivotalr.pdf



PivotalR 은 PostgreSQL, Greenplum Database, Apache HAWQ에서 쉽고 빠르게 데이터를 처리하고 분석할 수 있도록 해주는 Apache MADlib Wrapper 툴로서, Pivotal Inc.의 Data Science Team 에서 기여하여 만든 오픈소스 R 패키지입니다.  (PivotalR is a Fast, Easy-to-Use Tool for Manipulating Tables in Databases and a Wrapper of MADlib with contributions from Data Science Team at Pivotal Inc.)


PivotalR 은 PostgreSQL, Greenplum DB, HAWQ 에 접속해서 데이터를 처리하고 통계, 기계학습 알고리즘을 분석할 수 있는 R interface 를 제공하는데요, 이때 사용하는 함수가 native R 함수와 거의 유사하므로 기존 R 사용자 중에서 SQL이나 MADlib에 익숙하지 않는 경우에 존에 익숙한 R 함수(문법)를 사용할 수 있으므로 쉽게 사용할 수 있는 장점이 있습니다. 


더나아가서, 사용자는 메모리 크기의 한계에 대해서 걱정할 필요없이 분석에만 집중할 수 있습니다. 이는 PivotalR이 PostgreSQL, Greenplum DB로 부터 데이터 이동을 최소화하기 때문에 가능합니다 (매우 중요!!!). 특히 Greenplum DB는 MPP (Massively Parallel Processing) 아키텍처의 DB로서 수 테라 ~ 페타바이트급의 데이터를 분산병렬처리/ 분석하는데 R 사용자가 PivotalR을 사용하면 유용합니다. PivotalR 에서 R 함수로 통계, 기계학습 함수 코드를 짜서 실행하면 Greenplum DB 안에서는 SQL로 코드가 변환되어 MADlib 함수가 실행되고, 결과가 DB에 테이블로 저장이 됩니다. 






  2. web site에서 abalone 데이터셋 가져와서 table 만들기


UC Irvine Machine Learning Repository 에 공개된 abalone 데이터셋을 가져와서 public schema에 external table을 만들고, 이로부터 성(sex)별 칼럼을 기준으로 분산해서 저장하여 테이블을 만들어보겠습니다 (Greenplum DB 기준). 별로 어렵거나 특별한 것은 없으므로 추가 설명은 생략합니다.


아래 코드는 DBeaver 에서 SQL 로 작성한 것이며, 맥북에 Docker image로 만든 Greenplum DB 를 실행시켜 수행한 것입니다. (분석 환경 설정 참고: https://rfriend.tistory.com/379


-- 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;




SELECT sex, COUNT(*) FROM abalone GROUP BY sex;







  3. MADlib을 사용하여 DB에서 훈련, 검증 데이터셋 테이블 분


이번 예제에서는 PivotalR 을 사용하여 간단하게 성(sex)별로 shucked_weight와 diameter 설명변수를 사용하여 rings 를 예측하는 다중 선형회귀모형을 적합하고, 예측하는 것입니다. (PivotalR은 MADlib wrapper 로서 GPDB와 MADlib을 interface 해주며, 실제 분석 수행은 MADlib이 수행함)


이를 위해 먼저 training set : test set = 0.8 : 0.2 의 비율로 데이터셋을 분할하겠습니다. madlib.train_test_split() 함수를 사용하며, 아래처럼 SQL의 select 문 안에 순서대로 인자를 써주면 됩니다. 이때 '성(sex)' 별을 기준으로 층화임의추출(stratified random sampling)을 해주었으며, 비복원 추출 (sample with replacement = FALSE 로 설정) 을 하려고 합니다. Output table 이름에 'out'이라고 해주었으며, Separate output tables = TRUE 로 설정하여 train과 test 테이블을 별도로 구분해서 만들어주라고 함에 따라 'out_train', 'out_test' 라는 이름으로 자동으로 naming 되어 두개의 테이블이 생성이 됩니다. 


out_train, out_test 의 각 테이블별로 성별(sex)로 관측치 개수를 세어보니 0.8 : 0.2 의 비율로 성(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



-- Check

SELECT * FROM out_train LIMIT 5;




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




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





  4. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합


4번부터 6번까지는 RStudio에서 R 언어로 코드를 짠 것이며, 데이터는 Greenplum DB에 있고 데이터 처리 및 분석도 Greenplum DB에서 MADlib 으로 실제 수행이 됩니다. (Greenplum DB로 부터 로컬 PC로의 데이터 이동 없음. R을 알고 있으면 여기서부터는 SQL은 몰라도 됨)


만약 PivotalR 패키지를 설치하지 않은 사용자라면 install.packages("PivotalR") 명령문을 실행해서 설치해주기 바랍니다. (Greenplum, PostgreSQL DB에도 미리 설치가 되어 있어야 합니다)


먼저 로컬 PC의 RStudio에서 PivotalR 패키지를 로딩하고, Greenplum (혹은 PostgreSQL) DB에 db.connect() 함수를 이용해서 연결해보겠습니다. (dbname, host, port, user, password 는 각자의 정보를 입력해주세요. DB는 미리 start 해놓은 상태여야 합니다.). 



# install and load PivotalR package

install.packages("PivotalR")

library(PivotalR)


# Connect to Greenplum DB

# -- set with yours

db.connect(dbname = "gpadmin", 

           host = "localhost", 

           port = 5432, 

           user = "gpadmin", 

           password = "pivotal")


Created a connection to database with ID 1 

[1] 1




DB connection 정보는 db.list() 함수로 알 수 있습니다. [Connection ID 1] 처럼 DB 연결 정보는 나중에 db.data.frame() 함수로 테이블에 접근할 때 필요하므로 알아두어야 합니다. 


Greenplum 테이블 중에 특정 단어 (이번 예에서는 'abalone' 단어)가 들어간 모든 테이블(tables)이나 뷰(views)를 db.objects('abalone') 함수를 사용해서 나열해볼 수 있습니다. (SQL 로 information_schema 에서 테이블 이름 조회하는 것과 유사)



# List Database connection infomation

db.list()


Database Connection Info


## -------------------------------

[Connection ID 1]

Host     :    localhost

User     :    gpadmin

Database :    gpadmin

DBMS     :    Greenplum 5 

MADlib   :    installed in schema madlib 



# List all tables/views that has 'abalone' in the name

db.objects('abalone')


[1] "public.abalone"                 "public.abalone_external"




PivotalR의 db.data.frame() 함수를 이용하면 데이터는 Greenplum DB에 있고 로컬 PC로 이동하지 않은 상태에서 DB connect하여 마치 R의 DataFrame 을 만든 것처럼 R의 함수를 사용할 수 있습니다. 이때 위의 db.list() 함수로 조회해서 알았던 conn.id 를 지정해주어야 합니다 (이번 예에서는 conn.id = 1)


R DataFrame에 사용할 수 있는 간단한 함수로 dimension을 조회하는 R dim() 함수, 칼럼 이름을 조회하는 names() 함수를 예로 들어보았습니다. 



# Connect to a table via db.data.frame function

# --** Note that the data remains in the database and is not loaded into memory **--

abalone <- db.data.frame("public.abalone", # table name

                         conn.id = 1) # refer to the results of 'db.list()' above


Counting and caching the data table dimension ... 0.067 sec ... done.

An R object pointing to "public"."abalone" in connection 1 is created !


# dimension (4177, 9)

dim(abalone)

[1] 4177    9


# column names

names(abalone) 

[1] "sex"            "length"         "diameter"       "height"         "whole_weight"   "shucked_weight"

[7] "viscera_weight" "shell_weight"   "rings"





특정 변수의 값을 조회하는 lookat() 함수도 데이터 구조, 형태를 파악하는데 전체 데이터를 다 안불러오고 일부만 가져와서 RStudio 화면에 뿌려주므로 초반에 탐색적 데이터 분석할 때 종종 사용합니다. (select * form abalone order by sex limit 8;)



lookat(abalone$rings, 8)

[1] 15  7 10  7  8  9 10 11


> lookat(sort(abalone, decreasing = FALSE, c(abalone$sex, abalone$rings)), 8)

  sex length diameter height whole_weight shucked_weight viscera_weight shell_weight rings

1   F  0.290    0.225  0.075       0.1400         0.0515         0.0235       0.0400     5

2   F  0.360    0.270  0.090       0.1885         0.0845         0.0385       0.0550     5

3   F  0.275    0.195  0.070       0.0800         0.0310         0.0215       0.0250     5

4   F  0.370    0.275  0.085       0.2405         0.1040         0.0535       0.0700     5

5   F  0.445    0.335  0.110       0.4355         0.2025         0.1095       0.1195     6

6   F  0.595    0.475  0.160       1.1405         0.5470         0.2310       0.2710     6

7   F  0.475    0.360  0.120       0.5915         0.3245         0.1100       0.1270     6

8   F  0.345    0.250  0.090       0.2030         0.0780         0.0590       0.0550     6

 



이제 이 포스팅의 주인공인 '성별('sex') 그룹별로 shucked_weight와 diameter 변수를 사용해서 rings를 추정하는 다중 선형회귀모형을 적합하는 모형을 PivotalR의 madlib.lm() 함수를 사용하여 Greenplum DB에서 분산병렬처리로 적합시켜 보겠습니다. (Greenplum DB에서는 실제는 MADlib의 madlib.linregr_train() 함수가 수행됨) 


아래 코드에서 처럼 madlib.lm(rings ~ shucked_weight + diameter | sex, data = out_train) 함수 안의 syntax 가 R의 선형회귀모형을 적합할 때 사용하는 lm() 함수와 문법이 똑같습니다!  R을 이미 능숙하게 사용하는 사용자라면 아주 익숙한 syntax 일 것입니다. 


성별('sex') 그룹별로 선형회귀모형을 구분해서 적합하고 싶을 때는 '| sex' 처럼 madlib.lm() 함수 안에서 수직바('|')로 group operator 변수를 지정해주면 되므로 무척 편리합니다. (만약 120개 국가가 있는 country group 별로 선형회귀모형을 각각 적합시켜야 할 경우, 120개 국가별 madlib.lm() 함수를 나열하는 것이 아니라 코드 1줄로 하고 '| country_code' 만 추가시켜주면 됨)  Greenplum DB 에서는 MADlib이 알아서 최적화해서 분산병렬처리해서 학습을 시켜줍니다.(대용량 데이터에 대해 1개 모형을 적합하든, 아니면 여러개의 그룹별로 구분해서 모형을 적합하든 분산병렬처리 해줌)



# DB connect to training set table

out_train <- db.data.frame("public.out_train", # table name

                         conn.id = 1) # refer to the results of 'db.list()' above


Counting and caching the data table dimension ... 0.073 sec ... done.

An R object pointing to "public"."out_train" in connection 1 is created !



# Fit one different model to each group('grp')

model_1 <- madlib.lm(rings ~ shucked_weight + diameter | sex

                           data = out_train)



만약 설명변수 중 특정 변수 (예: 'id') 변수만 빼고 나머지 설명변수는 모두 사용해서 선형회귀모형을 적합하고 싶다면 model_2 <- madlib.lm(rings ~ . - id | sex, data = out_train) 처럼 해주면 됩니다. 기존 R과 함수가 매우 유사함을 알 수 있습니다. 




  5. PivotalR을 사용하여 성별 그룹별 선형회귀모형 적합결과 조회


summary(model name) 함수를 사용해서 'sex' 그룹 ('F', 'M', 'I') 별로 적합된 결과 (회귀계수, 표준오차, t통계량, P-value 등) 를 확인해볼 수 있습니다. 너무 쉽지 않나요?!!!



# Display the fitted results

summary(model_1)


MADlib Linear Regression Result


Call:

madlib.lm(formula = rings ~ shucked_weight + diameter | sex, 

    data = out_train)


The data is divided into 3 groups


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


Group 1 when

sex: I


Coefficients:

               Estimate Std. Error t value Pr(>|t|)    

(Intercept)      1.2629     0.3181    3.97 7.67e-05 ***

shucked_weight  -0.4197     0.9989   -0.42    0.674    

diameter        20.5929     1.4472   14.23  < 2e-16 ***

---

Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

R-squared: 0.4811355 

Condition Number: 33.77605 


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


Group 2 when

sex: F


Coefficients:

               Estimate Std. Error t value Pr(>|t|)    

(Intercept)     -0.7974     0.8372  -0.952    0.341    

shucked_weight  -9.8574     0.9393 -10.495   <2e-16 ***

diameter        35.8450     2.5893  13.844   <2e-16 ***

---

Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

R-squared: 0.1642253 

Condition Number: 38.90999 


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


Group 3 when

sex: M


Coefficients:

               Estimate Std. Error t value Pr(>|t|)    

(Intercept)      0.5520     0.5748   0.960    0.337    

shucked_weight  -6.6548     0.7085  -9.393   <2e-16 ***

diameter        29.6759     1.8586  15.966   <2e-16 ***

---

Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

R-squared: 0.2166009 

Condition Number: 30.90036




적합된 모형 객체에 대해서 groups() 함수를 사용하면 모형을 적합할 때 사용했던 '그룹 변수'와 '요인 수준 (factor level)'을 조회할 수 있습니다. 


그리고 적합된 모델 객체는 리스트 (list) 형태로 되어 있으므로 리스트의 indexing 하는 방법대로 특정 그룹의 모델 적합 정보만 선별해서 조회할 수도 있습니다. (아래 예는 두번째 그룹인 'F'(암컷) 회귀모형 적합 결과 indexing 했음)




# Groups information

groups(model_1)


$sex

[1] "I" "F" "M"


# Select the 2nd Group's Model

model_1[[2]]


MADlib Linear Regression Result


Call:

madlib.lm(formula = rings ~ shucked_weight + diameter | sex, 

    data = out_train)


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


sex: F


Coefficients:

               Estimate Std. Error t value Pr(>|t|)    

(Intercept)     -0.7974     0.8372  -0.952    0.341    

shucked_weight  -9.8574     0.9393 -10.495   <2e-16 ***

diameter        35.8450     2.5893  13.844   <2e-16 ***

---

Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1

R-squared: 0.1642253 

Condition Number: 38.90999

 




  6. PivotalR을 사용하여 성별 그룹별 선형회귀모형을 이용한 예측 및 모델 성능 평가


5번에서 적합한 성별('sex') 그룹별 선형회귀모형을 사용하여 3번에서 분할한 test-set table ('out_test') 을 대상으로 rings를 예측(추정)해보겠습니다. 그냥 간단하게 predict(model_name, newdata) 함수를 사용하면 끝입니다! 


아래 예에서는 db.data.frame() 함수를 써서 먼저 "public.out_test" 의 test-set 테이블을 먼저 out_test 로 연결해놓고 (데이터는 Greenplum DB에 있으며, 데이터 이동 없음), 그 다음에 predict() 함수로 그룹별로 예측을 하는데요, 결과를 실제와 예측값을 비교해서 보기에 편하도록 cbind() 로 묶은 다음에 as.db.data.frame() 으로 DataFrame 처럼 만든 다음에 (데이터는 DB에 있음), lookat() 함수로 비로서 DB 테이블 안의 앞의 10개 데이터만 가져와서 RStudio에 결과를 보여주었습니다



# DB connect to test-set table

out_test <- db.data.frame("public.out_test", # table name

                           conn.id = 1) # connection ID


# Prediction on test set

pred_test <- as.db.data.frame(cbind(out_test$rings, predict(model_1, newdata=out_test)))


Counting and caching the data table dimension ... 0.075 sec ... done.

An R object pointing to "madlib_temp_1ef5b482_2a87_a0afaa_5b83c16e4d72" in connection 1 is created !


The data created by cbind(out_test$rings, predict(model_1, out_test)) is stored into pg_temp_8."madlib_temp_1ef5b482_2a87_a0afaa_5b83c16e4d72" in database gpadmin on localhost !



lookat(pred_test, 10)


   rings madlib_predict

1      7       8.660894

2     18      11.476131

3     12      12.156485

4     14      12.366110

5     15      12.000829

6     17      10.023336

7      4       6.978186

8     15      12.026095

9      9      10.541055

10    15      11.709994

 



이번에는 성별('sex') 그룹 중에서 'F' (암컷) 전복에 대해서만 예측하고, 무작위로 100개만 표본 추출하여 '실제값("rings") vs. 예측값("madlib_predict")' 을 산점도로 시각화하여 비교를 해보겠습니다. (이 예에서는 DB에 수백만~수억개의 row 를 가진 대용량 데이터가 Greenplum DB에 들어있고, RStudio 로는 이중에서 일부인 100개만 임의추출하여 가져와서 시각화하는 상황이라고 가정하였음) 



# Prediction result on 'F' sex group only

pred_test_F <- cbind(out_test$rings[out_test$sex == "F"]

                     predict(model_1[[2]], # model for sex 'F' group

                             out_test[out_test$sex == "F",]) # newdata for sex 'F' group

                     )


lookat(pred_test_F, 10)


   rings madlib_predict

1      3       3.115230

2     11       8.997374

3      4       4.344511

4      6       6.880858

5      7       6.790274

6      7       7.093502

7      7       6.590010

8      9       7.395052

9      5       5.161094

10     7       7.389806



# plot a random sample

plot(lookat(sort(pred_test_F, FALSE, NULL), 100), 

     main="Actual vs. Predicted on 'F' sex group (100 samples)")



마지막으로, 선형회귀모형의 적합도(goodness of fit) 평가할 수 있는 여러개의 통계량 지표들 중에서 Mean Squared Error ( = mean((actual_value - predicted_value)^2)) 를 (a) 3개 그룹 모두에 대해서 계산 (예측은 각 그룹별 모델로 적용, MSE 계산에는 그룹 구분 없이 모두 합쳐서 계산), (b) 'F' 성별 그룹에 대해서만 MSE 를 계산해보겠습니다. 



# Mean Squared Error

lookat(mean((out_test$rings - predict(model_1, newdata = out_test))^2))


[1] 6.431776


# Mean Squared Error of 'F' sex group
lookat(mean((out_test$rings[out_test$sex == "F"] # actual
             - predict(model_1[[2]], out_test[out_test$sex == "F",]) # predicted
             )^2)) # mean of squared error


[1] 9.275403

 



지금까지 PivotalR 패키지를 사용하여 Greenplum, PostgreSQL DB에서 선형회귀모형을 적합, 예측하는 예를 들어보았습니다. MADlib 1.8.x 버전을 기준으로 했을 때 PivotalR은 현재 아래와 같이 선형회귀모델 외에 다양한 통계, 기계학습 알고리즘을 지원하고 있습니다. 





[ PivotalR references ]

  • Github

    https://github.com/pivotalsoftware/PivotalR


  • CRAN package

    https://cran.r-project.org/web/packages/PivotalR/index.html

    http://cran.r-project.org/web/packages/PivotalR/PivotalR.pdf


  • Pivotal blog posts

    http://blog.pivotal.io/pivotal/products/introducing-r-for-big-data-with-pivotalr

    http://blog.pivotal.io/pivotal/products/how-to-20-minute-guide-to-get-started-with-pivotalr


  • Technical paper

    http://cran.r-project.org/web/packages/PivotalR/vignettes/pivotalr.pdf


  • Confluence Wiki

    https://cwiki.apache.org/confluence/display/MADLIB/PivotalR



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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

지난번 포스팅에서는 Greenplum, PostgreSQL DB에서 PL/R을 활용하여 In-DB 에서 그룹별 회귀모형 (분산 병렬) 적합 및 예측하는 방법(https://rfriend.tistory.com/529)을 소개하였습니다. 


이번 포스팅에서는 동일한 abalone 데이터셋으로 Apache MADlib (https://madlib.apache.org/) 을 사용하여 그룹별 회귀모형을 분산 병렬 적합 및 예측하는 방법을 소개하겠습니다. 


1. Apache MADlib 소개

2. web site에서 abalone 데이터셋 가져와서 table 만들기

3. 훈련, 검증 데이터 분할 (split abalone dataset into training and test set)

4. 성별 그룹별 선형회귀모형 적합 (training linear regression models by 'sex' groups')

5. 성별 그룹별 선형회귀모형 회귀계수 조회 (select coefficients per variables by 'sex' groups)

6. 성별 그룹별 선형회귀모형을 이용하여 예측 (prediction by 'sex' groups)



  1. Apache MADlib 소개


[ Apache MADlib: Big Data Machine Learning in SQL for PostgreSQL and Greenplum DB ]


Apache MADlib 은 PostgreSQL, Greenplum Database 에서 SQL 언어로 대용량 빅데이터에 대해 In-DB 기계학습, 통계분석, 그래프 분석을 할 수 있는 Apache project 의 top level 오픈 소스 라이브러리입니다. 


 Apache MADlib은 2011년 EMC/Greenplum 아키텍트와 캘리포니아 버클리 대학교(university of California, Berkeley)의 Joe Hellerstein 교수가 같이 오픈소스 프로젝트로 시작하였으며, Berkeley 대학교 외에 Stanfoard 대학교, Wisconsin 대학교, Florida 대학교 등이 같이 Apache MADlib Project에 참여하고 있습니다. 


Apache MADlib은 아래와 같이 지도학습, 비지도학습, 그래프, 통계, 시계열분석, 샘플링 및 모델 선택, 데이터 유형 변환 등의 다양한 기능의 함수를 제공합니다. 


[ Apache MADlib Functions ]


Apache MADlib은 core engine이 C++로 되어있어서 굉장히 빠릅니다. 추상적인 고수준 언어는 Python으로 되어 있고, 사용자는 SQL로 함수를 실행시키므로 SQL을 알고 있는 사용자라면 쉽고 빠르게 사용할 수 있습니다. 


모든 데이터 전처리 및 분석이 In-DB에서 이루어지므로 데이터의 In/Out이 없으며, 수백테라~페타바이트급의 대용량도  (Greenplum의 경우) 분산 병렬처리할 수 있으므로 빅데이터를 다루어서 모델링을 신속하게 해야 하는 경우에 적합합니다. 


[ Reference of Apache MADlib ]

- Open source: https://github.com/apache/madlib

- Downloads and Documents: http://madlib.apache.org

- Wiki: https://cwiki.apache.org/confluence/display/MADLIB

- Greenplum DB에 MADlib 설치https://gpdb.docs.pivotal.io/550/ref_guide/extensions/madlib.html




  2. web site에서 abalone 데이터셋 가져와서 table 만들기


UC Irvine Machine Learning Repository 에 공개된 abalone 데이터셋을 가져와서 public schema에 external table을 만들고, 이로부터 성(sex)별 칼럼을 기준으로 분산해서 저장하여 테이블을 만들어보겠습니다 (Greenplum DB 기준). 별로 어렵거나 특별한 것은 없으므로 추가 설명은 생략합니다. 



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

-- Linear Regression in Parallel 

-- using Apache MADlib

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


-- 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;




SELECT sex, COUNT(*) FROM abalone GROUP BY sex; 






  3. 훈련, 검증 데이터 분할 (split abalone dataset into training and test set)


이번 예제에서는 MADlib을 사용하여 간단하게 성(sex)별로 shucked_weight와 diameter 설명변수를 사용하여 rings 를 예측하는 다중 선형회귀모형을 적합하고, 예측하는 것입니다. 


이를 위해 먼저 training set : test set = 0.8 : 0.2 의 비율로 데이터셋을 분할하겠습니다. madlib.train_test_split() 함수를 사용하며, 아래처럼 SQL의 select 문 안에 순서대로 인자를 써주면 됩니다. 이때 '성(sex)' 별을 기준으로 층화임의추출(stratified random sampling)을 해주었으며, 비복원 추출 (sample with replacement = FALSE 로 설정) 을 하려고 합니다. Output table 이름에 'out'이라고 해주었으며, Separate output tables = TRUE 로 설정하여 train과 test 테이블을 별도로 구분해서 만들어주라고 함에 따라 'out_train', 'out_test' 라는 이름으로 자동으로 naming 되어 두개의 테이블이 생성이 됩니다. 


out_train, out_test 의 각 테이블별로 성별(sex)로 관측치 개수를 세어보니 0.8 : 0.2 의 비율로 성(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



-- Check

SELECT * FROM out_train LIMIT 5;




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




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






  4. 성별 그룹별 선형회귀모형 적합 

     (training linear regression models by 'sex' groups')


위의 3번에서 분할(split)한 훈련 데이터셋(training set)인 'out_train' 테이블을 대상으로   의 다중 선형회귀모형을 madlib.linregr_train() 함수를 사용하여 성별('sex') 그룹별로 나누어서 적합(fit)시켜 보겠습니다. 

MADlib에는 그룹별로 모형을 각각 적합시킬 때 아래의 예처럼 GroupBy 칼럼 이름을 넣어주면 알고리즘 내부적으로 Group별로 (이 예제에서는 'sex' 별로) 분산병렬처리하여 복수의 모델을 적합시켜 줍니다! 수 테라바이트의 대용량 데이터라도 전수로 분산병렬처리해서 신속하게 모델을 적합시킬 수 있으니 대단히 아주 유용합니다. (로컬 싱글 머신에서 R이나 Python 사용할 때처럼 메모리 full 나서 다운되거나, 몇 시간씩 걸리는 일 없습니다)



-- Linear Regression using MADlib

-- Train a regression model. 

DROP TABLE IF EXISTS abalone_linregr, abalone_linregr_summary;

SELECT madlib.linregr_train(

    'out_train'         -- table containing training data

    , 'abalone_linregr' -- table in which to save results

    , 'rings'           -- column containing dependent variable

    , 'ARRAY[1, shucked_weight, diameter]' -- features included in the model

    , 'sex'); -- create multiple output models (one for each value of sex)



* MADlib linear regression: https://madlib.apache.org/docs/latest/group__grp__linreg.html




  5. 성별 그룹별 선형회귀모형 회귀계수 조회 

     (select coefficients per variables by 'sex' groups)


위의 4번에서 성별('sex') 그룹별로 각각 분산병렬처리해서 훈련한 선형회귀모형의 적합 결과를 조회해보겠습니다. select 문의 from 절에 위의 4번에서 설정한 output table 이름인 "abalone_linregr" 테이블을 써주면 됩니다. 


그런데 다중 선형회귀모형이다보니 Y절편 intercept 와 'shucked_weight', 'diameter' 의 두개의 설명변수가 사용되어 성별로 각각 적합된 모델의 회귀계수(regression coefficients), 결정계수(), 표준오차(standard error), T 통계량(t-statistics), P 값 (P-values) 의 칼럼에 'intercept', 'shucked_weight', 'diameter' 의 순서대로 3개 값들이 콤마로 구분되어 array 형태로 들어가 있기에 읽기에 좀 힘듭니다. 



-- Examine the resulting models

SELECT * FROM abalone_linregr ORDER BY sex;





사람이 눈으로 보기에 좀더 가독성이 있도록 unnest() 함수를 사용해서 array를 세로로 긴 형태로 풀어서 다시 한번 조회를 해보겠습니다. 아래에 예제 결과를 보는 것처럼 한결 보기에 좋습니다. 


-- unnest format

SELECT sex

, unnest(ARRAY['intercept', 'rings', 'diameter']) as attribute

, unnest(coef) as coefficient 

, unnest(std_err) as standard_error

, unnest(t_stats) as t_stat

, unnest(p_values) as pvalue

FROM abalone_linregr

ORDER BY sex;



위에 Apache MADlib으로 성별('sex')로 각각 적합한 선형회귀모형의 회귀계수는 이전 포스팅에서 Greenplum에서 PL/R로 성별로 분산병렬처리해서 적합한 선형회귀모형(https://rfriend.tistory.com/529)의 회귀계수와 정확하게 일치합니다. 




  6. 성별 그룹별 선형회귀모형을 이용하여 예측 (prediction by 'sex' groups)


위의 5번에서 training set을 이용해 성별로 각각 선형회귀모형을 적합하였으니, 이번에는 3번에서 분할하여 따로 떼어놓았던 test set을 대상으로 예측(prediction)하여 보고, 실제값과 예측값의 차이를 비교해서 모델의 성능을 평가해보겠습니다. 


예측에는 madlig.linregr_predict() 라는 함수를 이용하며, input은 array[] 형태로 데이터를 변화해주어야 합니다. 아래 예에서 ARRAY[1, shucked_weight, diameter] 에서 '1'은 intercept 항을 의미합니다. 


Greenplum DB에서 MADlib으로 훈련한 모델을 사용하여 MADlib으로 대용량 데이터어 대해 예측/스코어링을 하면 역시 분산병령처리가 되어 대단히 빠르게 결과값을 반환합니다!



-- compare predicted value with actual with grouping

DROP TABLE IF EXISTS abalone_pred;

CREATE TABLE abalone_pred AS (

SELECT a.sex, a.shucked_weight, a.diameter, a.rings, 

madlib.linregr_predict(m.coef

, ARRAY[1, shucked_weight, diameter]

) as predict_val

, rings - madlib.linregr_predict(m.coef

, ARRAY[1, shucked_weight, diameter]

) as residual

FROM out_test a, abalone_linregr m

WHERE a.sex = m.sex) DISTRIBUTED BY (sex);



SELECT * FROM abalone_pred WHERE sex = 'F' LIMIT 10;





위의 6번에서 만든 실제값과 예측값 테이블 'abalone_pred' 을 이용해서 다양한 통계량 지표로 선형회귀모형의 적합도 평가해보겠습니다. 이중에서 실제값과 예측값이 차이의 제곱을 평균한 Mean Squared Error를 성별('sex') 그룹별로 madlib.mean_squared_error() 함수를 사용하여 계산해보겠습니다. (함수의 위치에 각 인자값을 넣어주면 됩니다.)



-- Model Performance Evaluation: Mean Squared Error

DROP TABLE IF EXISTS abalone_mse;

SELECT madlib.mean_squared_error(

'abalone_pred'   -- table_in

, 'abalone_mse'  -- table_out

, 'predict_val'  -- prediction_col

, 'rings'        -- observed_col

, 'sex');        -- grouping_cols

SELECT * FROM abalone_mse;





위에서 소개한 MSE를 계산하는 방식으로 MAE (Mean Absolute Error), MAPE (Mean Absolute Percentage Error), MPE (Mean Percentage Error), R-squared, Adjusted R-squared 등을 계산할 수 있습니다. (아래의 함수별 인자 위치를 참고해서  select madlib.함수(인자1, 인자2, ... ) 이런식으로 써주면 됩니다. 위의 MSE 계산하는 예제 참고하세요). 



-- Mean absolute error

madlib.mean_abs_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean absolute percentage error

madlib.mean_abs_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean percentage error

madlib.mean_perc_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Mean squared error

madlib.mean_squared_error(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- R-squared

madlib.r2_score(table_in, table_out, prediction_col, observed_col, grouping_cols)


-- Adjusted R-squared

madlib.adjusted_r2_score(table_in, table_out, prediction_col, observed_col, num_predictors, training_size, grouping_cols)

 


* MADlib Model Selection - Prediction Metrics: https://madlib.apache.org/docs/latest/group__grp__pred.html


다음 포스팅에서는 PivotalR을 활용하여 Greenplum, PostgreSQL DB에서 그룹별 선형회귀모형 적합 및 예측(https://rfriend.tistory.com/534) 하는 방법을 소개하겠습니다. 


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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

지난번 포스팅에서는 PostgreSQL 혹은 Greenplum DB 에서 PL/R (Procedural Language R) 을 실행하기 위해 input 데이터 형태로 aggregation 하는 3가지 방법을 소개하였습니다. 


이번에는 PL/R 의 output 을 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) 을 소개하겠습니다. 


(1) returns float8[] : array 형태로 결과 반환 (그룹 당 1행)

(2) returns setof float8 : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)

(3) reterns setof composite_type : 행 단위로 composite type에 맞게 결과 반환 

                                               (관측치 당 1행, 그룹 당 여러 행)

(4) returns table : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)

(5) returns bytea : byte array 로 결과 반환 (그룹 당 1행)

    --> unserialize 하는 PL/R 함수 추가로 필요



[ PL/R on Greenplum & PostgreSQL DB (workflow 예시) ]




예제에 사용하기 위해 그룹('grp') 칼럼과 정수('x1'), 실수('x2') 칼럼을 가진 간단한 테이블을 생성해보겠습니다. 



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

-- PL/R on Greenplum

-- : 5 ways to return PL/R results

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


-- create an example dataset table

create schema test;


drop table if exists test.src_tbl;

create table test.src_tbl (

grp varchar(10) not null 

, x1 integer

, x2 float8

) distributed by (grp);


insert into test.src_tbl values 

('a', 1, 0.13)

, ('a', 2, 0.34)

, ('a', 3, 0.31)

, ('a', 4, 0.49)

, ('a', 5, 0.51)

, ('b', 1, 0.10)

, ('b', 2, 0.26)

, ('b', 3, 0.30)

, ('b', 4, 0.62)

, ('b', 5, 0.59);


select * from test.src_tbl;




예시에 사용할 PL/R 함수는 x1에 2를 곱하고 x2를 로그 변환 (log transformation) 하여 더한 값 (x_new = 2 * x1 + log(x2))을 계산하는 매우 간단한 것입니다. (물론 SQL로도 할 수 있는데요, PL/R 예시로 간단한 걸로 사용한 거예요)



  (1) returns float8[] : array 형태로 결과 반환 (그룹 당 1행)


returns float8[] 에서 꺽쇠 '[]' 가 array 로 반환하라는 의미의 기호입니다. 


코딩이 간단하고 그룹별로 1행으로 저장이 되므로 조회나 테이블 조인(table join) 할 때 빠르다는 장점이 있습니다. 하지만 조회를 했을 때 array 형태이므로 조회해서 보고 활용할 때 보통 unnest 를 해야 해서 불편한 점이 있습니다. 



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

-- (1) returns an array using 'returns float8[]'

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


-- Define PL/R UDF

drop function if exists test.plr_log_trans(integer[], float8[]);

create or replace function test.plr_log_trans(x1 integer[], x2 float8[])

returns float8[] -- returns as an array

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

return(x_new)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, a.x1_agg

, a.x2_agg

, test.plr_log_trans(a.x1_agg, a.x2_agg) as x_new_agg

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;




위의 PL/R 결과 테이블에서 보는 것처럼 array 형태로 PL/R 결과를 반환하기 때문에 { } 안에 옆으로 길게 늘어서 있어서 보기에 불편합니다. 이럴 경우 SQL의 unnest() 함수를 사용해서 세로로 길게 행 단위(by row)로 테이블 형태를 바꾸어서 조회할 수 있습니다. 



-- Display by rows using unnest() function

select 

a.grp

, unnest(a.x1_agg) as x1

, unnest(a.x2_agg) as x2

, unnest(test.plr_log_trans(a.x1_agg, a.x2_agg)) as x_new

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;





  (2) returns setof float8 : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)


위의 (1) 번과 지금 소개하는 (2)번이 다른 점은 (a) setof 추가, (b) [] 제거  의 두 가지입니다. 

  - (1)번 : returns float8[]

  - (2)번 : returns setof float8


(2)번 방법으로 하면 (1)번에서 처럼 unnest() 함수를 쓸 필요없이 바로 행 단위(by rows)로 PL/R 결과를 반환합니다. 



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

-- (2) returns rows using 'returns setof'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_2(int[], float8[]);

create or replace function test.plr_log_trans_2(x1 int[], x2 float8[])

returns setof float8 -- returns by each row

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

return(x_new)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, unnest(a.x1_agg) as x1

, unnest(a.x2_agg) as x2

, test.plr_log_trans_2(a.x1_agg, a.x2_agg) as x_new

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;






  (3) reterns setof composite_type : 행 단위로 composite type 에 맞게 결과 반환 

                                                (관측치 당 1행, 그룹 당 여러 행)


PL/R 함수의 결과로 반환받을 결과값이 여러개의 칼럼으로 구성되어 있는 경우 composite type을 정의해서 PL/R 함수 정의할 때 사용할 수 있습니다. 


위의 (2)번 예에서는 PL/R 실행 결과의 반환받는 값으로 x_new = 2*x1 + log(x2) 의 x_new 값 단 1개만 float8 형태로 반환했습니다. 이번 (3)번 예에서는 PL/R 결과값으로 x1 (integer), x2, (float8) x_new (float8) 의 3개 칼럼의 composite type 형태로 반환해보겠습니다. 


이러려면 create type 으로 반환받을 composite type 을 먼저 정의를 해줍니다. 그 다음으로 PL/R 함수를 정의할 때 returns setof composite_type_name 처럼 앞서 정의한 compositie type 이름을 returns setof 뒤에 써주면 됩니다. 


(2)번 예에서는 PL/R 을 실행(execution) 하면 x_new 계산 결과만 반환하므로 x_1, x_2 를 select 문에 별도로 써주었습니다. 하지만 (3)번 예에서는 PL/R 함수를 보면 x_1, x_2, x_new 를 하나의 DataFrame으로 묶고 이를 통채로 반환하도록 되어있습니다. 그리고 composite type을 x_1, x_2, x_new 각각의 데이터 유형에 맞게 정의해주었구요.  



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

-- (3) returns rows using 'returns setof' with a composite type

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


-- Define composite data type

drop type test.plr_log_trans_type cascade;

create type test.plr_log_trans_type as (

x1 integer

, x2 float8

, x_new float8

);



-- Define PL/R UDF

drop function if exists test.plr_log_trans_3(int[], float8[]);

create or replace function test.plr_log_trans_3(x1 int[], x2 float8[])

returns setof test.plr_log_trans_type

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

result_df <- data.frame(x1, x2, x_new)

return(result_df)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, (test.plr_log_trans_3(a.x1_agg, a.x2_agg)).*

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;


 




  (4) returns table : 행 단위로 결과 반환 (관측치 당 1행, 그룹 당 여러 행)


PL/R 함수를 실행했을 때 반환받을 값의 칼럼이 여러개일 경우 위의 (3)번 처럼 composite type을 미리 정의해서 PL/R 함수를 정의할 때 returns setof composite_type_name 형식으로 쓸 수도 있구요, 이번의 (4)번처럼 바로 returns table (반환받을 칼럼 이름과 데이터 유형) 형식으로 바로 쓸 수도 있습니다. 


(3)번 처럼 composite type을 미리 정의해두면 나중에 똑같은 칼럼과 데이터 유형으로 PL/R에 input 넣거나 output 으로 반환받을 때 그냥 composite type name 을 써주면 되므로 재활용할 수 있는 장점이 있습니다. 


이번 (4)번처럼 composite type을 정의하는것 없이 그냥 바로 returns table () 처럼 하면 일단 편하고 또 직관적이어서 이해하기 쉬운 장점이 있으며, 대신 (3)번처럼 재활용은 못하므로 매번 써줘야 하는 단점이 있습니다. 



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

-- (4) returns rows using 'returns table'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_4(integer[], float8[]);

create or replace function test.plr_log_trans_4(x1 integer[], x2 float8[])

returns table (

x1 integer

, x2 float8

, x_new float8

)

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

result_df <- data.frame(x1, x2, x_new)

return(result_df)

$$ language 'plr';



-- Execute PL/R UDF

select 

a.grp

, (test.plr_log_trans_4(a.x1_agg, a.x2_agg)).*

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a;






  (5) returns bytea : byte array 로 결과 반환 (그룹 당 1행)

       --> unserialize 하는 PL/R 함수 추가로 필요


마지막으로, PL/R 결과를 byte array 형태로 반환하여 테이블에 저장하는 방법입니다. 


이 방법은 반환받는 PL/R 데이터셋의 형태, 데이터 유형을 신경쓸 필요없이 그냥 통째로 byte array 로 직렬화(serialize) 해서 반환하므로 PL/R 함수를 정의할 때 returns bytea 로 하고 나머지는 신경쓸 필요가 없어서 편리합니다. 


대신에, 공짜 점심은 없듯이, 나중에 byte array 로 저장된 PL/R 결과를 조회하려면 역직렬화(unserialize)를 해주는 PL/R 함수를 한번 더 짜줘야 하므로 편리성 면에서는 결국 조삼모사입니다. 


다만, PL/R 함수로 반환받으려는 객체가 그 무엇이던간에 (가령, 회귀모형의 적합된 모델 자체, 길이가 제 각각인 텍스트 등...) byte array 로 직렬화해서 반환받으면 되므로 byte array를 써야만 하는 경우도 있겠습니다. 



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

-- (5) returns serialized byte array using 'returns bytea'

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

-- Define PL/R UDF

drop function if exists test.plr_log_trans_5(integer[], float8[]);

create or replace function test.plr_log_trans_5(x1 integer[], x2 float8[])

returns bytea -- serialized as a byte array

as 

$$

x_new <- 2 * x1 + log(x2)

x_new <- round(x_new, 3)

serialized_df <- serialize(data.frame(x1, x2, x_new), NULL)

return(serialized_df)

$$ language 'plr';



-- Execute PL/R UDF

drop table if exists test.tbl_plr_log_trans_5;

create table test.tbl_plr_log_trans_5 as (

select 

a.grp

, test.plr_log_trans_5(a.x1_agg, a.x2_agg) as serialized_df

from (

select 

grp

, array_agg(x1::int) as x1_agg

, array_agg(x2::float8) as x2_agg

from test.src_tbl

group by grp

) a

) distributed by (grp);


select * from test.tbl_plr_log_trans_5;


 



위의 직렬화되어서 반환된 PL/R 결과를 select 문으로 조회를 해보면 사람은 눈으로 읽을 수 없게 저장이 되어있음을 알 수 있습니다. 아래는 R의 unserialize() 함수를 사용해서 역직렬화(deserialize, decode)를 해서 미리 정의해둔 composite type 으로 반환하도록 해준 PL/R 코드입니다. 좀 복잡하게 느껴질 수도 있겠습니다. ^^; 



-- Unserialize

-- Define composite data type

drop type test.plr_log_trans_type cascade;

create type test.plr_log_trans_type as (

x1 integer

, x2 float8

, x_new float8

);


-- Define PL/R UDF for reading a serialized PL/R results

drop function if exists test.plr_unserialize(bytea);

create or replace function test.plr_unserialize(

serialized_obj bytea

) returns setof test.plr_log_trans_type

as 

$$

unserialized_df <- data.frame(unserialize(serialized_obj))

return (unserialized_df)

$$ language 'plr';



-- Execute 'plr_unserialize' UDF

select 

grp

, (test.plr_unserialize(a.serialized_df)).*

from test.tbl_plr_log_trans_5 a;


 



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

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


Posted by R Friend R_Friend

댓글을 달아 주세요

Greenplum 혹은 PostgreSQL DB에서 PL/R (Procudural Language R Extension) 분석을 위해서는 (1) PL/R 사용자 정의 함수 정의 (define PL/R UDF), (2) array aggregation 하여 데이터 준비 (Preparation of data by array aggregation), (3) PL/R 사용자 정의 함수를 호출하여 실행 (execute PL/R UDF) 의 순서로 진행이 됩니다. 


이번 포스팅에서는 Greenplum 혹은 PostgreSQL DB에서 PL/R (Procedural Language R)을 사용해서 In-DB analytics 를 하기 위해서 array 형태로 데이터를 준비하는 3가지 방법을 소개하겠습니다. 


1. 열(column)을 기준으로 여러개의 행(row)을 그룹별로 array aggregation

2. 행과 열을 기준으로 그룹별로 2D (2-dimensional) array aggregation 

3. 문자열로 string aggregation 하고 PL/R 코드 안에서 R로 데이터셋 변환하기



[ Workflow and Output Image of PL/R on Greenplum, PostgreSQL DB ]




예제로 사용할 PL/R 분석은 'a'와 'b' 두 개의 그룹 별로 x1, x2 두 숫자형 변수 간의 상관계수(correlation coefficients)를 계산하는 업무입니다. 


예제로 사용할 간단한 테이블을 먼저 만들어보겠습니다. 



--create schema and sample table

create schema test;


drop table if exists test.tbl;

create table test.tbl (

grp text not null

, x1 int

, x2 int

);


insert into test.tbl (grp, x1, x2) values 

('a', 1, 2)

, ('a', 2, 5)

, ('a', 3, 4)

, ('b', 1, 8)

, ('b', 2, 7)

, ('b', 3, 3);



select * from test.tbl;

 






  1. 열(column)을 기준으로 여러개의 행(row)을 그룹별로 array aggregation


첫번째는 SQL 의 array_agg() 함수를 사용해서 그룹('grp') 별로 x1, x2 각 칼럼을 기준으로 여러개의 행을 array 형태로 aggregation 하는 방법입니다. 칼럼 기준으로 array aggregation 을 하기 때문에 PL/R 사용자 정의 함수 안에서 각 칼럼을 인자로 받아서 정의하기에 직관적으로 이해하기 쉽고 사용이 편리한 장점이 있습니다. 또 각 칼럼 별로 데이터 유형 (data type)이 서로 다를 경우 (가령, 칼럼이 텍스트, 정수형, 부동소수형 등으로 서로 다른 경우) 각 칼럼 별로 array aggregation 을 하기 때문에 각자 데이터 유형에 맞추어서 해주면 되는 점도 편리합니다. 


다만, 칼럼의 개수가 많을 경우에는 일일이 array aggregation 하고, 또 PL/R 사용자 정의 함수 안에서 이들 칼럼을 다시 인자로 받아서 data frame 으로 만들거나 할 때 손이 많이 가서 번거로울 수 있습니다. 그리고, 그룹 별로 array aggregation 을 했을 때 만약 데이터 크기가 크다면 PL/R을 실행할 때 데이터 I/O 에 다소 시간이 소요될 수 있습니다. 



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

-- (1) data preparation : array_agg() by column

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

drop table if exists test.array_agg_by_col;

create table test.array_agg_by_col as (

select 

grp

, array_agg(x1) as x1_agg

, array_agg(x2) as x2_agg

from test.tbl 

group by grp

) distributed by (grp);


select * from test.array_agg_by_col order by grp;





-- define PL/R UDF

drop function if exists test.plr_cor(int[], int[]);

create or replace function test.plr_cor(x1 int[], x2 int[]) 

returns float8

as

$$ 

corr_coef <- cor(x1, x2)

return (corr_coef)

$$ language 'plr';




-- execute PL/R UDF

select 

grp

, test.plr_cor(x1_agg, x2_agg) as corr_coef

from test.array_agg_by_col;







  2. 행과 열을 기준으로 그룹별로 2D (2-dimensional) array aggregation  


두번째 방법은 Apache MADlib 의 madlib.matrix_agg() 함수를 사용해서 2차원 배열의 행렬을 만드는 것입니다. 만약 칼럼별 데이터 유형이 모두 숫자형이고 또 칼럼의 개수가 많아서(가령, 수십~수백개) 일일이 array_agg() 를 하기가 번거롭다면 madlib.matrix_agg() 함수를 사용하는 것이 상대적으로 2D array aggregation 하기도 쉽고 또 PL/R 사용자 정의 함수 안에서 데이터 변환을 해서 이용하기도 편리합니다. 


반면에, 만약 각 칼럼 별 데이터 유형이 서로 다르고 숫자형이 아닌 텍스트 등이 들어있다면 사용할 수가 없습니다


MADlib 의 함수를 사용하는 것이므로 Greenplum DB에 MADlib을 미리 설치해두어야 합니다. 

* Apache MADlib : https://madlib.apache.org/



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

-- (2) data preparation : 2D array MADlib matrix_agg()

--     : only with same data types

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


drop table if exists test.tbl_matrix_agg;

create table test.tbl_matrix_agg as (

select 

grp

, madlib.matrix_agg(array[x1, x2]) as mat_agg

from test.tbl 

group by grp

) distributed by (grp);


select * from test.tbl_matrix_agg;




-- define PL/R UDF

drop function if exists test.plr_cor_2(float8[]);

create or replace function test.plr_cor_2(mat_agg float8[]) 

returns float8

as

$$

df <- data.frame(mat_agg)

colnames(df) <- c("x1", "x2")

corr_coef <- with(df, cor(x1, x2))

return (corr_coef)

$$ language 'plr';




-- execute PL/R UDF

select 

grp

, test.plr_cor_2(mat_agg) as corr_coef

from test.tbl_matrix_agg;





 

  3. 문자열로 string aggregation 한 후 PL/R 코드 안에서 R 로 데이터셋 변환하기


PL/R 함수에 input으로 들어갈 데이터를 준비하는 세번째 방법은 데이터를 텍스트로 변환해서 SQL의 string_agg() 함수를 사용하여 구분자(delimiter, 가령 ',' 나 '|' 등)를 값 사이에 추가하여 그룹별로 aggregation 하는 것입니다. 


string aggregation을 사용하면 다양한 데이터 유형 (가령, 텍스트, 정수, 부동소수형 등)이 섞여 있는 다수의 칼럼을 그룹 별 & 행(row) 별로 aggregation 할 수 있고, 또 array aggregation 대비 상대적으로 데이터 크기를 줄여서 PL/R 실행 시 데이터 I/O 시간을 다소 줄일 수 있는 장점이 있습니다. 


반면에, PL/R 함수 안에서 R로 string aggregation 되어 있는 데이터 덩어리를 구분자(delimiter)를 기준으로 분리(split) 하고 transpose 해서 R에서 분석하기에 적합한 형태로 데이터 전처리를 해주어야 하는 번거로움이 있습니다.  아래에 (3-1) base 패키지의 strsplit() 함수를 이용한 전처리와, (3-2) data.table 패키지의 tstrsplit() 함수를 이용한 전처리로 나누어서 각각 예시를 들어보았습니다. 


PL/R 함수를 SQL editor 에서 짜면서 디버깅을 하려면 input, return type 을 정의해주면서 해야하기 때문에 무척 고달플 수 있습니다. 따라서 제일 빠르고 또 정확한 방법은 RStudio 같은 R 전용 IDE에서 샘플 데이터로 R code 에 에러, 버그가 없도록 clean R codes block 을 작성한 후에, 이를 PL/R 코드의 $$ R codes block $$ 안에 추가하는 방식입니다. 노파심에 다시 한번 말씀드리자면, DB 에서 PL/R 코드 돌려가면서 디버깅 하는 것은 고통스러울 수 있으니 R codes 가 정확하게 작동하는 bug-free codes 인지 먼저 명확하게 확인한 후에 PL/R 코드의 $$ ~ $$ 사이에 넣고 실행하기 바랍니다. 



3-1. base 패키지의 strsplit() 함수를 이용하여 텍스트 파싱
     (text parsing using base package's strsplit() function)


base 패키지 안의 strsplit() 함수를 사용해서 텍스트를 구분자(delimiter)를 기준으로 분리(split) 하고, 이를 do.call 로 "cbind" 함수를 여러번 호출해서 세로로 묶어서 데이터 프레임을 만드는 방식입니다. 아래의 예시처럼 코드가 좀 복잡하고 어렵게 보일 수 있습니다. ㅜ_ㅜ


DB에서 SQL로 string_agg() 함수를 사용하려면 대상이 되는 칼럼을 text로 데이터 유형 변환 (type casting)을 먼저 해주어야 합니다. (아래 예시에서는 integer 형태인 x1, x2 를 x1::text, x2::text 를 사용해 text 형태로 변환 후 string_agg() 적용함)


R strsplit() 함수의 구분자는 DB에서 string_agg() 함수로 aggregation 할 때 사용했던 구분자로 설정해줍니다. (아래 예시에서는 구분자로 수직막대기 '|' 를 사용하였음)


*  Base R 패키지의 문자열 처리 함수 참고 : https://rfriend.tistory.com/37



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

-- (3-a) data preparation : string_agg() by column

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

drop table if exists test.string_agg_by_col;

create table test.string_agg_by_col as (

select 

grp

, string_agg(x1::text, '|') as x1_str_agg

, string_agg(x2::text, '|') as x2_str_agg

from test.tbl 

group by grp

) distributed by (grp);


select * from test.string_agg_by_col order by grp;



-- define PL/R UDF : (3-a) using DataFrame

drop function if exists test.plr_cor_3(text, text);

create or replace function test.plr_cor_3(x1 text, x2 text) 

returns float8

as

$$ 

# make a temp DataFrame

df_tmp <- data.frame(x1, x2)

# split by delimiter and reshape it in a long format

split_func <- function(x){

options(stringsAsFactors = FALSE) # Not to read strings as factors

df_split <- as.data.frame(

do.call('cbind'

, strsplit(as.character(x)

                                     set delimiter with yours

    , split="|"

    , fixed=T)))

return (df_split)

}

df <- data.frame(lapply(df_tmp, split_func))

colnames(df) <- c("x1", "x2") # set column names

# convert a data type from text to numeric

df <- data.frame(sapply(df, as.numeric))

# calculate correlation coefficients

corr_coef <- with(df, cor(x1, x2))

return (corr_coef)

$$ language 'plr';



-- execute PL/R UDF

select 

grp

, test.plr_cor_3(x1_str_agg, x2_str_agg) as corr_coef

from test.string_agg_by_col

order by grp asc;






3-2. data.table 패키지의 tstrsplit() 함수를 이용하여 텍스트 파싱 

      (text parsing using data.table package's tstrsplit() function)


data.table 패키지의 tstrsplit() 함수는 strsplit() 함수와 transpose 를 하나로 합쳐놓은 역할을 하는 함수로서, 위의 base 패키지를 사용한 파싱 대비 상대적으로 간편하고 깔끔하며 또 빠릅니다. 


data.table 패키지 안의 tstrsplit() 함수를 사용한다고 했으므로 사전에 Greenplum, PostgreSQL DB에 R data.table 패키지를 설치해두어야 합니다(Greenplum의 경우 각 segment node에 모두 설치 필요). 그리고 PL/R 함수 안에서는 library(data.table) 로 패키지를 로딩해주어야 합니다. 


R tstrsplit() 함수의 구분자는 DB에서 string_agg() 함수로 aggregation 할 때 사용했던 구분자로 설정해줍니다. (아래 예시에서는 구분자로 수직막대기 '|' 를 사용하였음)


* R data.table package's tstrsplit() function : https://www.rdocumentation.org/packages/data.table/versions/1.12.8/topics/tstrsplit 



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

-- (3-b) data preparation : string_agg() by column

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

drop table if exists test.string_agg_by_col;

create table test.string_agg_by_col as (

select 

grp

string_agg(x1::text'|'as x1_str_agg

string_agg(x2::text'|'as x2_str_agg

from test.tbl 

group by grp

) distributed by (grp);


select * from test.string_agg_by_col order by grp;




-- define PL/R UDF : (3-b) using data.table tstrsplit() function

drop function if exists test.plr_cor_4(text, text);

create or replace function test.plr_cor_4(x1 text, x2 text) 

returns float8

as

$$ 

library(data.table)

# make a temp DataTable

dt_tmp <- data.table(x1, x2)

# split by delimiter and reshape it in a long format

dt_split_func <- function(x){

dt_split <- data.table(tstrsplit(x, split="|", fixed=T))

return(dt_split)

}

df <- data.frame(lapply(dt_tmp, dt_split_func))

colnames(df) <- c("x1", "x2") # set column names

# convert a data type from text to numeric

df <- data.frame(sapply(df, as.numeric))

# calculate correlation coefficients

corr_coef <- with(df, cor(x1, x2))

return (corr_coef)

$$ language 'plr';



-- execute PL/R UDF

select 

grp

, test.plr_cor_4(x1_str_agg, x2_str_agg) as corr_coef

from test.string_agg_by_col;





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

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



Posted by R Friend R_Friend

댓글을 달아 주세요

  1. 성장하는청년 2020.04.09 23:24 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 잘 보고갑니다^^

  2. 휘게라이프 Gwho 2020.04.10 16:36 신고  댓글주소  수정/삭제  댓글쓰기

    오늘도 출첵 완료~ :-)
    항상 정성스러운 좋은글 보고 감동하고 가요 .. ㅎㅎ
    오늘도 행복 하세요! =)

이번 포스팅에서는 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 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

댓글을 달아 주세요