'postgresql db' 태그의 글 목록

'postgresql db'에 해당되는 글 12건

[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching)

Greenplum and PostgreSQL Database 2020. 7. 12. 23:13

지난번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열형의 종류, 생성, 처리 연산자(Character Type Operators) 및 함수(Character Functions)에 대해서 소개(https://rfriend.tistory.com/543)하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 문자열 패턴 매칭(String Pattern Matching)하는 세가지 방법을 소개하겠습니다.

(1) 전통적인 SQL LIKE 연산자 (LIKE Operator)

(2) SIMILAR TO 연산자 (the SIMILAR TO Operator)

(3) 정규 표현식 (POSIX-Style Regular Expressions)

(1) SQL LIKE, ~~ 연산자 ('LIKE', or '~~' Operator)

'LIKE' 연산자는 전통적인 SQL에서 문자열 패턴 매칭할 때 가장 보편적으로 사용되며, 아래와 같은 syntax를 사용합니다.

string LIKE pattern [ESCAPE escape-character]

위의 LIKE 연산자는 LIKE 앞의 문자열이 LIKE 뒤의 패턴과 일치할 경우 TRUE를 반환하며, 일치하지 않을 경우는 FALSE를 반환합니다.

와일드카드 문자(wildcard character)로서 (1) 퍼센트 부호(%)는 그 위치부터 어떤 문자열(any string)이 와도 상관없으며, (2) 밑줄 부호(_)는 해당 위치에 하나의 문자가 일치하는지를 봐서 TRUE나 FALSE를 반환합니다. LIKE와 '%', '_' 와일드카드 문자를 사용한 패턴 매칭 예 몇개를 살펴보겠습니다.

pattern matching	returns
SELECT 'abc' LIKE 'abc';	true
SELECT 'abc' LIKE 'a%';	true
SELECT 'abc' LIKE '%b';	false
SELECT 'abc' LIKE '%b%';	true
SELECT 'abc' LIKE '%c';	true
SELECT 'abc' LIKE '_b_';	true
SELECT 'abc' LIKE 'c';	false
SELECT 'abc' LIKE '_c';	false
SELECT 'abc' LIKE '__c';	true

'~~' 연산자를 'LIKE' 연산자 대신 사용할 수 있습니다. 위에 예 중에서 한개만 'LIKE'를 '~~' 연산자로 바꾸어보면 아래와 같습니다. (Jupyter Notebook에서 sql_magic 라이브러리로 SQL문 사용할 때 LIKE 대신 ~~ 연산자를 사용했었습니다.)

SELECT 'abc' ~~ 'a%'; --> returns true

패턴 일치여부를 판단하는데 있어 대소문자를 구분합니다.

SELECT 'abc' LIKE 'a%'; --> returns true

SELECT 'abc' LIKE 'A%'; --> returns false

일치하지 않는 패턴을 찾고 싶으면 'NOT LIKE' 또는 '!~~' 연산자를 사용합니다.

SELECT 'abc' LIKE 'a%'; --> returns true

SELECT 'abc' NOT LIKE 'a%'; --> retruns false

SELECT 'abc' ~~ 'a%'; --> returns true

SELECT 'abc' !~~ 'a%'; --> returns false

(2) SIMILAR TO 연산자 (the SIMILAR TO Operator)

SELECT 'abc' SIMILAR TO 'abc'; -- true

SELECT 'abc' SIMILAR TO 'a'; -- false

SELECT 'abc' SIMILAR TO 'a__'; -- true

SELECT 'abc' SIMILAR TO '_b_'; -- true

SELECT 'abc' SIMILAR TO '_bc'; -- true

SELECT 'abc' SIMILAR TO 'a%'; -- true

SELECT 'abc' SIMILAR TO '%b'; -- false

SELECT 'abc' SIMILAR TO '%b%'; -- true

-- | denotes alternation (either of two alternatives).

-- Parentheses () can be used to group items into a single logical item.

SELECT 'abc' SIMILAR TO '%(b|d)%'; -- true

SELECT 'abc' SIMILAR TO '%(d|e)%'; -- false

-- {m} denotes repetition of the previous item exactly m times.

SELECT 'abbbc' SIMILAR TO '%b{3}%'; -- true

SELECT 'abbbc' SIMILAR TO '%b{4}%'; -- false

-- {m,} denotes repetition of the previous item m or more times.

SELECT 'abbbc' SIMILAR TO '%b{2,}%'; -- true

SELECT 'abbbc' SIMILAR TO '%b{4,}%'; -- false

-- {m,n} denotes repetition of the previous item at least m and not more than n times.

SELECT 'abbbc' SIMILAR TO '%b{2,3}%'; -- true

SELECT substring('foobar' from '%#"o_b#"%' for '#'); -- oob

-- It returns null if there is no match.

SELECT substring('foobar' from '#"o_b#"%' for '#'); -- NULL

SELECT substring('foobar' from 'o...r'); -- oobar

SELECT substring('foobar' from 'o(...)r'); -- oba

(3) 정규 표현식 (POSIX-Style Regular Expressions)

정규표현식을 이용하면 위의 (1) LIKE 연산자, (2) SIMILAR TO 연산자보다 더욱 강력한 패턴 매칭을 할 수 있습니다.

-- Regular Expressions Matching Operators: ~, ~*, !~, !~*

-- ~ operator: Matches regular expression, case sensitive

SELECT 'thomas' ~ '.*thomas.*'; -- true

SELECT 'thomas' ~ '.*Thomas.*'; -- False

-- ~* operator: Matches regular expression, case insensitive

SELECT 'thomas' ~* '.*Thomas.*'; -- true

-- !~ operator: Does not match regular expression, case sensitive

SELECT 'thomas' !~ '.*thomas.*'; -- False

SELECT 'thomas' !~ '.*Thomas.*'; -- True

-- !~* operator: Does not match regular expression, case insensitive

SELECT 'thomas' !~* '.*Thomas.*'; -- false

SELECT 'thomas' !~ '.*Thomas.*'; -- true

SELECT 'thomas' !~* '.*steve.*'; -- true

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

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

* Reference: https://www.postgresql.org/docs/9.5/functions-matching.html

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법 (0)	2020.11.15
[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기 (0)	2020.10.20
[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions) (0)	2020.07.04
[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators) (0)	2020.06.28
[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate (0)	2020.06.20

Posted by Rfriend

[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators)

Greenplum and PostgreSQL Database 2020. 6. 28. 20:27

이번 포스팅에서는 PostgreSQL, Greenplum DB의 4가지 연산자(Operators)에 대해서 알아보겠습니다.

산술 연산자 (Arithmetic Operators)
비교 연산자 (Comparison Operators)
논리 연산자 (Logical Operators)
비트 연산자 (Bitwise Operators)

(1) 산술 연산자 (Arithmetic Operators)

* Reference: https://www.postgresql.org/docs/9.4/functions-math.html

산술연산자는 어려운 것은 없으므로 추가 설명은 생략하겠으며, 다만 나눗셈(/)에 대해서만 조심해야하는 부분이 있어서 추가 설명을 하겠습니다.

나눗셈의 분자와 분모가 모두 정수(int)인 경우 나눗셈(/)을 하면 정수의 몫을 반환하며, 소수점 부분은 무시가 되므로 유의할 필요가 있습니다. 만약 소수점자리까지의 나눗셈 계산 결과가 모두 필요한 경우 분자나 혹은 분모를 NUMERIC 혹은 FLOAT 로 데이터 형태 변환을 해주어야 합니다. 아래에 간단한 예를 들어보겠습니다. (이걸 신경을 안쓰면 나중에 소수점 부분의 결과가 무시된걸 모르고서 원하는 값이 아니라면서 당황하는 수가 생깁니다.)

DROP TABLE IF EXISTS test;

CREATE TABLE test (

a int

, b int

);

INSERT INTO test VALUES (2, 4), (3, 5), (4, 7);

SELECT * FROM test;

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

a b

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

2 4

3 5

4 7

-- 나눗셈 결과의 소수점 자리 무시

SELECT b / a AS div FROM test;

----------

div

----------

-- 분자를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b::numeric / a AS div_1 FROM test;

----------

div_1

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000

-- 분모를 Numeric으로 형 변환하면 나눗셈 결과 소수점 자리 나옴

SELECT b / a::numeric AS div_2 FROM test;

----------

div_2

----------

2.0000000000000000

1.6666666666666667

1.7500000000000000

계승(factorial)의 경우 SELECT 5!, SELECT !!5 처럼 '!'가 한개냐, 두개냐에 따라서 정수를 써주는 위치가 달라집니다.

절대값(Absolute value)를 구할 때는 '@ col_nm' 혹은 '@ 숫자' 를 해주면 되는데요, 이때 '@' 다음에 스페이브 1칸을 띄워주어야 합니다. (만약 '@' 다음에 한 칸 띄우지 않으면 SQL Error [42883]: ERROR: operator does not exist: @- numeric 와 같은 ERROR가 발생합니다.)

산술 연산을 한 후에 'AS col_nm' 처럼 alias 별명 칼럼 이름을 부여할 수 있습니다.

(2) 비교 연산자 (Comparison Operators)

* Reference: https://www.postgresql.org/docs/9.4/functions-comparison.html

비교 연산자(comparison operators)도 어려운 것은 없으므로 길게 설명할 필요는 없어보입니다. 아래의 간단한 예를 살펴보시기 바랍니다.

비교 연산자는 WHERE 조건절에서 사용되어 두 값을 비교하게 되며, 비교 연산자의 조건을 만족하면 참(TRUE)을, 비교 연산자의 조건을 만족하지 않으면 거짓(FALSE)을 반환합니다. 이를 이용해서 비교 연산자에 대해 참(TRUE)인 조건을 만족하는 값만을 선택(SELECT)해 올 수 있습니다.

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

-- Comparison Operators

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

DROP TABLE IF EXISTS comparison;

CREATE TABLE comparison (

a int

, b int

);

INSERT INTO comparison VALUES (1, 1), (1, 2), (2, 1), (2, 2);

SELECT * FROM comparison;

----------

a b

----------

1 1

1 2

2 1

2 2

-- equal to

SELECT * FROM comparison WHERE a = b;

----------

a b

----------

1 1

2 2

-- not equal

SELECT * FROM comparison WHERE a != b;

SELECT * FROM comparison WHERE a <> b;

----------

a b

----------

1 2

2 1

-- greater than

SELECT * FROM comparison WHERE a > b;

----------

a b

----------

2 1

-- less than

SELECT * FROM comparison WHERE a < b;

----------

a b

----------

1 2

-- greater than or equal to

SELECT * FROM comparison WHERE a >= b;

----------

a b

----------

1 1

2 1

2 2

-- less than or equal to

SELECT * FROM comparison WHERE a <= b;

----------

a b

----------

1 1

1 2

2 2

다만 한가지 조심한 것이 있습니다. 비교 연산자 두개를 이어붙여서 사용하는 경우 순서(sequence)가 틀리면 ERROR가 발생합니다. 따라서 꼭 순서에 맞게 (가령, >= greater than or equal to) 비교 연산자를 써주어야 합니다.

-- SQL Error [42883]: ERROR: operator does not exist: integer =! integer

SELECT * FROM comparison WHERE a =! b;

-- SQL Error [42883]: ERROR: operator does not exist: integer >< integer

SELECT * FROM comparison WHERE a >< b;

-- SQL Error [42601]: ERROR: syntax error at or near "=>"

SELECT * FROM comparison WHERE a => b;

-- SQL Error [42883]: ERROR: operator does not exist: integer =< integer

SELECT * FROM comparison WHERE a =< b;

(3) 논리 연산자 (Logical Operators)

논리 연산자(Logical Operators)는 조건절에서 여러개의 조건을 AND, OR, NOT 으로 조합하여 사용할 수 있도록 해줍니다. 아래에 우측에 집합 벤다이어그램으로 그림을 그려놓았으니 참고하시기 바랍니다.

아래의 표는 WHERE 조건절에 a와 b의 두 조건이 참(TRUE), 거짓(FALSE) 여부의 조합별로 AND, OR, NOT 논리 연산자의 결과값이 참(TRUE)인지 또는 거짓(FALSE)인지를 정리한 표입니다. NULL 은 FALSE 로 간주된다는 점 유의하시기 바랍니다.

[ PostgreSQL Logical Operators Table by TRUE, FALSE combinations ]

* Reference: https://www.postgresql.org/docs/9.4/functions-logical.html

위의 (2)번에서 만들었던 comparison 테이블에 NULL 값을 포함한 행 두개를 추가해서 간단한 논리 연산자 예제를 만들어보겠습니다.

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

-- Logical Operators

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

INSERT INTO comparison VALUES (NULL, 5), (NULL, NULL);

SELECT * FROM comparison;

----------

a b

----------

1 1

1 2

2 1

2 2

[NULL] 5

[NULL] [NULL]

-- AND

SELECT * FROM comparison WHERE a = 1 AND b = 2;

----------

a b

----------

1 2

-- OR

SELECT * FROM comparison WHERE a = 1 OR b = 5;

----------

a b

----------

1 1

1 2

[NULL] 5

-- NOT

SELECT * FROM comparison WHERE NOT (a = 1);

2 1

2 2

-- NOT IN

SELECT * FROM comparison WHERE a NOT IN (1);

----------

a b

----------

2 1

2 2

-- IS NOT NULL

SELECT * FROM comparison WHERE b IS NOT NULL;

----------

a b

----------

1 1

1 2

2 1

2 2

[NULL] 5

-- IS NOT NULL AND IS NOT NULL

SELECT * FROM comparison WHERE a IS NOT NULL AND b IS NOT NULL;

----------

a b

----------

1 1

1 2

2 1

2 2

-- NOT BETWEEN

SELECT * FROM comparison WHERE b BETWEEN 1 AND 2;

----------

a b

----------

1 1

1 2

2 1

2 2

SELECT * FROM comparison WHERE b NOT BETWEEN 1 AND 2;

----------

a b

----------

[NULL] 5

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching) (0)	2020.07.12
[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions) (0)	2020.07.04
[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate (0)	2020.06.20
[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기 (0)	2020.06.14
[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally (4)	2020.04.16

Posted by Rfriend

[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate

Greenplum and PostgreSQL Database 2020. 6. 20. 21:35

지난번 포스팅에서는 PostgreSQL, Greenplum DB 에서 Database, Schema, Table 생성/삭제 및 Insert Into 를 사용한 값 입력을 소개(https://rfriend.tistory.com/537)하였습니다.

이번 포스팅에서는 데이터 조작 언어(DML: Data Manipulation Language) 중에서 데이터 수정(갱신) Update와 삭제 Detele 문에 대해서 소개하겠습니다. 그리고 테이블 전체를 비우는 Truncate 도 제일 뒤에 마저 소개하겠습니다. (단, 이들 Insert, Update, Delete, Truncate 의 경우 사용자가 권한(Privilege)을 부여받은 상태에서만 가능합니다.)

RDBMS 에 속하는 PostgreSQL, Greenplum DB와 NoSQL Hive 의 Insert, Update, Delete 별 기능에 대해 아래에 비교 정리를 해보았습니다. (셋 모두 오프 소스입니다.)

PostgreSQL DB 는 작은 규모의 데이터를 관리하는 OLTP (Online Transaction Processing) 성격의 RDBMS (Relational Database Management System) 로서 Insert, Update, Delete 를 모두 지원합니다.

Hive는 Big Data를 Batch 로 처리, 분석하는데 사용하는 Hadoop 기반의 NoSQL 의 하나로서, Insert 는 지원하지만 Update 와 Delete 는 지원하지 않습니다. (Hive version 0.14 부터 Delete 기능 추가).

Greenplum DB 는 PostgreSQL 엔진 기반에 Shared nothing 구조의 MPP (Massively Parallel Processing) 아키텍처의 DW, Analytics Platform 으로서, PostgreSQL과 Hive 의 양쪽의 장점은 취하고 단점은 피하는 기능상의 이점을 제공합니다.

(1) 데이터 수정 (갱신): UPDATE table_name SET column=value WHERE;

UPDATE Syntax

UPDATE table_name

SET column1 = value1, column2 = value2, ..., columnN = valueN

WHERE [condition];

먼저 예제로 사용할 cust_master 테이블을 생성(create)하고, 데이터를 입력(insert into) 해보겠습니다.

DROP TABLE IF EXISTS cust_master;

CREATE TABLE cust_master (

id INT PRIMARY KEY

, first_name VARCHAR(30) NOT NULL

, last_name VARCHAR(30) NOT NULL

, gender TEXT DEFAULT 'UNKNOWN'

, age INT CHECK (age > 0 AND age < 120)

, joindate DATE CHECK (joindate > '2020-01-03')

);

-- INSERT INTO VALUES

INSERT INTO cust_master (id, first_name, last_name, gender, age, joindate) VALUES

(1, 'kildong', 'hong', 'M', 30, '2020-05-15')

, (2, 'younghee', 'kim', 'F', 20, '2020-04-29')

, (3, 'chulsu', 'lee', 'M', 40, '2020-06-16');

SELECT * FROM cust_master;

(1-1) Where 조건에 해당하는 행의 특정 칼럼 값 수정하기

이제, 위의 cust_master 테이블에서 'id'가 '1'인 행의 'age' 칼럼 값을 '25'로 수정(갱신, update) 해보겠습니다.

-- Update with Where clause

UPDATE cust_master SET age = 25 WHERE id = 1;

SELECT * FROM cust_master ORDER BY id;

(1-2) 모든 행의 특정 칼럼 값을 동일한 값으로 모두 수정하기

아래처럼 Where 조건절이 없으면 모든 행의 값이 똑같은 값으로 수정되므로 Where 절을 빼먹지 않도록 유의하시기 바랍니다.

-- Update without Where clause

UPDATE cust_master SET age = 50;

SELECT * FROM cust_master ORDER BY id;

(1-3) Where 조건을 만족하는 복수개의 칼럼 값을 수정(갱신, Update) 하기

아래 예처럼, 콤마(',')로 수정하고자 하는 칼럼 이름을 구분해서 Update 문을 작성해주면 됩니다.

-- Update of multiple values using Comma

UPDATE cust_master SET age = 40, joindate = '2020-06-20' WHERE id = 3;

SELECT * FROM cust_master ORDER BY id;

(2) 데이터 삭제: DELETE FROM table_name WHERE;

DETELE Syntax

DELETE FROM table_name

WHERE [condition];

(2-1) Where 조건을 만족하는 행만 삭제하기

위에서 만들었던 cust_master 테이블에서 Where 조건절을 사용하여 id = 1 인 행 전체를 삭제(DELETE)해보겠습니다.

-- Delete row with Where clause

DELETE FROM cust_master WHERE id = 1;

SELECT * FROM cust_master ORDER BY id;

참고로, 테이블 내에 중복된 행이 있는 경우, DELETE 문을 사용해서 중복된 행들 중에서 첫번째 행 한개만 남겨놓고 나머지 중복된 행들은 삭제하는 방법은 https://rfriend.tistory.com/386 를 참고하세요.

(2-2) 모든 행의 데이터를 삭제하기

만약에 Where 조건절문을 포함하지 않고 DELETE 문을 사용하면 테이블 내 모든 행의 데이터가 삭제되므로 조심하시기 바랍니다.

-- Delete all rows

DELETE FROM cust_master;

SELECT * FROM cust_master;

(3) 테이블 비우기 (모든 데이터 삭제): TRUNCATE table_name;

TRUNCATE Syntax

TRUNCATE table_name1, table_name2, ..., table_nameN

[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

위의 (2-2)에서 Where 조건절 없이 DELETE 문을 사용할 경우 테이블 내 모든 행의 데이터가 삭제되었습니다.

만약에 데이터 크기가 매우 큰 테이블에서 모든 데이터를 빠르게 삭제하고 싶은 경우 DELETE 문보다는 TRUNCATE 함수를 사용할 수 있습니다. DELETE 와 다르게 TRUNCATE는 table scan 이 없어서 빠르며, VACUUM operation 없이 디스크 공간을 즉시 재생성 해줍니다.

(TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.)

대신에 TRUNCATE 는 ROLLBACK 정보를 만들지 않고 바로 COMMIT 을 하기 때문에 나중에 ROLLBACK을 할 수는 없으므로, 역시 조심해서 해야 하겠습니다.

여러개의 테이블을 한꺼번에 모든 데이터를 비우고 싶으면 TRUNCATE table_name1, table_name2, ..., table_nameN 처럼 콤마로 구분하여 테이블 이름을 여러개 써주면 됩니다.

-- Truncate 1 table

TRUNCATE cust_master;

-- Truncate multiple tables

TRUNCATE cust_master, cust_grade;

TRUNCATE 의 여러 Parameter 값들에 대한 설명은 아래 링크를 참조하세요.

https://www.postgresql.org/docs/9.5/sql-truncate.html

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions) (0)	2020.07.04
[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators) (0)	2020.06.28
[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기 (0)	2020.06.14
[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally (4)	2020.04.16
[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측 (0)	2020.04.12

Posted by Rfriend

[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기

Greenplum and PostgreSQL Database 2020. 6. 14. 23:51

SQL(Structured Query Language)은 데이터베이스로부터 원하는 정보를 가져오기 위해 고안된 구조화된 질의언어입니다. ("에스큐엘" 혹은 "시퀄" 이라고 발음).

SQL과 같은 데이터베이스 언어는 역할을 기준으로 했을 때 3가지로 구분할 수 있습니다.

(a) 데이터 정의 언어 (DDL: Data Definition Language): create, drop 등

(b) 데이터 조작 언어 (DML: Data Manipulation Language): insert, update, delete, select 등

(c) 데이터 제어 언어 (DCL: Data Control Language): commit, rollback 등

이번 포스팅에서는 데이터 정의 언어(DDL), 데이터 조작 언어(DML)를 사용하여 PostgreSQL DB, Greenplum DB에서

(1) Database, Schema, Table 를 만들고 삭제하는 방법: create, drop

(2) Table에 새로운 데이터를 등록하고 가져오는 방법: insert into, select from

(3) Index 를 생성하는 방법: create index

(4) VIEW 생성: create view

을 소개하겠습니다.

Table 은 행(rows)과 열(columns)로 데이터를 저장하고 있으며, Schema에 포함되어 있습니다.

Schema 는 논리적으로 테이블을 정의해놓은 것으로서, 여러개의 Table/ Procedure/ Function/ Index/ View 등을 포함하고 있습니다.

Database 는 데이터를 한 곳에 관리할 수 있도록 모아둔 것으로서, 여러개의 Schema를 가질 수 있습니다.

이를 집에 비유하자면

Database = House
Schema = Floor Plan (or Blueprint)
Table = Room

로 이해할 수 있겠네요.

회사에서는 보통의 경우 Database, Schema, Table 생성(create)과 삭제(drop) 의 경우 critical 한 영역의 권한에 속하므로 데이터베이스 관리자 (database administrator) 에게만 권한이 있고 일반의 사용자에게는 select 조회 권한만 줍니다. 따라서 일반 분석가라면 이번 포스팅의 내용을 사용할 일이 별로 없을 수도 있을 듯 합니다만, 알아두어서 손해볼 일은 없으니 한번 찬찬히 살펴보시기 바랍니다.

(교육 프로그램을 진행할 때 hands-on training 시 수강생 간의 테이블 사용에 대한 간섭이나 충돌을 방지하기 위해서 수강생 별로 Database나 Schema를 따로 따로 생성하여 진행하기도 합니다.)

(1) Database 만들기: CREATE DATABASE database_name;

'test' 라는 이름의 새로운 Database를 만들어 보겠습니다.

-- Create a Database

DROP DATABASE IF EXISTS test;

CREATE DATABASE test;

PGAdmin III 의 왼쪽 Object browser 를 refresh 하면 아래와 같이 'test' Database 가 새로 생겼음을 확인할 수 있습니다. (명령프롬프트 창에서 psql 로는 '\l' 을 하면 데이터베이스 리스트 볼 수 있음)

PGAdmin III 의 GUI 에서 새로 만든 'test' Database Server로 새로 연결(connect) 하려면 아래의 화면 캡쳐처럼 우측 상단의 '<new connection>' 에서 'test Database' 를 선택하고, username과 rolename, password를 차례대로 입력해주면 됩니다. (혹은 왼쪽의 Object browser에서 'test' DB를 선택 후 'SQL editor' 창을 새로 띄워도 됩니다).

cf. SQL Server 나 MySQL 의 경우 서버에서 다수의 Database를 동시에 사용할 수 있으며, 'USE dbname' statement를 사용해서 Database 를 Default Database 를 변경할 수 있습니다. 하지만, PostgreSQL 은 단 하나의 Database만 서버에서 사용할 수 있기 때문에 'USE dbname' statement는 없습니다. PostgreSQL에서는 사용하고 싶은 Database 를 변경하려면 현재 서버에서 연결되어 있는 Database를 닫고 (close the current connection), 다른 Database 에 연결(then connect to another database in server) 해주어야 합니다. (명령 프롬프트 창에서는 psql dbname 해주면 됨)

(2) Schema 만들기: CREATE SCHEMA schema_name;

이번에는 위의 (1)번에서 새로 만들었던 'test' Database 안에 'cust'라는 이름의 Schema를 만들어보겠습니다. (Customer 주제의 데이터를 다루는 사용자들이 사용할 데이터 저장 공간)

-- CREATE SCHEMA

DROP SCHEMA IF EXISTS cust;

CREATE SCHEMA cust;

PGAdmin III 의 왼쪽 Object browser 를 refresh 시켜서 보면 아래 화면캡쳐처럼 'cust' Schema가 새로 생겼음을 알 수 있습니다.

(3) Table 만들기: CREATE TABLE table_name;

이제 'test' Database 의 'cust' Schema 에 'cust_master'라는 이름의 Table을 만들어보겠습니다.

(생성은 CREATE TABLE table_name, 삭제는 DROP TABLE table_name)

-- CREATE TABLE W/ CONSTRAINTS

DROP TABLE IF EXISTS cust.cust_master;

CREATE TABLE cust.cust_master (

id INT PRIMARY KEY

, first_name VARCHAR(30) NOT NULL

, last_name VARCHAR(30) NOT NULL

, gender TEXT DEFAULT 'UNKNOWN'

, age int CHECK (age > 0 AND age < 120)

, joindate DATE CHECK (joindate > '2020-01-03')

);

테이블을 만들 때 각 칼럼 별로 제약조건(Constraints)을 설정할 수 있습니다.

제약 조건 (contraint)	설명
PRIMARY KEY	데이터 중복 금지 (UNIQUE) & NULL 값 금지 (NOT NULL)
UNIQUE	데이터 중복 금지 (한가지 값만 가져야 함)
CHECK (condition statements)	조건식에 맞지 않는 데이터 금지
NOT NULL	NULL 값 금지
DEFAULT = value	값이 없을 경우 미리 지정한 초기값(DEFAULT VALUE) 등록

* reference: https://www.postgresql.org/docs/9.5/ddl-constraints.html

pg_catalog.pg_tables 테이블에서 schema와 table 정보가 조회할 수 있습니다. 방금 위에서 만든 'cust' Schema 내의 'cust_master' 테이블을 한번 조회해볼까요?

-- How to list up the tables

SELECT *

FROM pg_catalog.pg_tables

WHERE

schemaname = 'cust'

AND tablename LIKE 'cust%';

MPP(Massively Parallel Processing) Architecture 의 Greenplum Database 의 경우 분산키(distribution key), 압축방식 등을 추가로 지정해줄 수 있습니다.

-- CREATE TABLE W/ CONSTRAINTS

DROP TABLE IF EXISTS cust.cust_master;

CREATE TABLE cust.cust_master (

id INT PRIMARY KEY

, first_name VARCHAR(30) NOT NULL

, last_name VARCHAR(30) NOT NULL

, gender TEXT DEFAULT 'UNKNOWN'

, age int CHECK (age > 0 AND age < 120)

, joindate DATE CHECK (joindate > '2020-01-03')

)

WITH (appendoptimize=true, compresslevel=5)

DISTRIBUTED BY (id);

* for more information: https://gpdb.docs.pivotal.io/6-8/ref_guide/sql_commands/CREATE_TABLE.html

(4) Table 에 값(values) 등록하기

: INSERT INTO schema.table (columns) VALUES (values);

'test' Database 의 'cust' Schema 내 'cust_master' Table 에 id, first_name, last_name, gender, age, joindate 칼럼 값을 등록해보겠습니다.

-- INSERT INTO VALUES

INSERT INTO cust.cust_master (id, first_name, last_name, gender, age, joindate)

VALUES (1, 'kildong', 'hong', 'M', 30, '2020-05-15');

SELECT * FROM cust.cust_master;

아래의 INSERT INTO 문에서는 'gender' 칼럼의 값이 누락되어 있습니다. 이처럼 등록할 값이 누락될 경우 CREATE TABLE 할 때 gender 칼럼에 DEFAULT VALUE로 지정했던 'UNKNOWN' 값이 초기값으로 등록이 됩니다.

-- default value for 'gender' column

INSERT INTO cust.cust_master (id, first_name, last_name, age, joindate)

VALUES (2, 'chulsu', 'kim', 20, '2020-04-29');

SELECT * FROM cust.cust_master;

만약 INSERT INTO로 등록할 값의 순서가 Table을 생성할 때 정의했던 칼럼의 순서와 동일하고 중간에 누락값이 없다면 칼럼 이름을 나열할 필요없이 그냥 VALUES () 에 바로 값을 등록해주어도 됩니다.

-- if the sequence of values is the same with the table definition,

-- column names can be ignored

INSERT INTO cust.cust_master VALUES (3, 'younghee', 'lee', 'F', 25, '2020-03-05');

SELECT * FROM cust.cust_master;

등록할 값이 여러개 있는 경우 아래처럼 VALUES 다음에 row 단위로 괄호로 묶어주고 콤마(',')로 구분해서 이어서 값(values)을 써주면 됩니다.

-- insert lots of values altogether

INSERT INTO cust.cust_master VALUES

(4, 'yongsu', 'choi', 'M', 22, '2020-04-01')

, (5, 'sohyun', 'park', 'F', 53, '2020-06-15')

, (6, 'sedong', 'lim', 'M', 48, '2020-02-09');

SELECT * FROM cust.cust_master;

외부의 text, csv 파일 등의 데이터셋을 PostgreSQL, Greenplum DB로 import 하는 방법은 https://rfriend.tistory.com/432 를 참고하세요.

만약 PRIMARY KEY 칼럼(중복 금지, NULL 값 금지)에 중복된 값을 등록하려고 하면 중복금지 제약조건(uniqur constraint) 을 위배했다면서 ERROR 가 발생합니다.

-- Error due to duplication of the primary key

INSERT INTO cust.cust_master VALUES

(6, 'hoysun', 'sung', 'F', 34, '2020-04-11')

ERROR: duplicate key value violates unique constraint "cust_master_pkey"

DETAIL: Key (id)=(6) already exists.

********** Error **********

ERROR: duplicate key value violates unique constraint "cust_master_pkey"

SQL state: 23505

Detail: Key (id)=(6) already exists.

위의 (3)번에서 Table을 만들 때 age int CHECK (age > 0 AND age < 120) 라는 CHECK Constraint 를 설정했었습니다. 아래처럼 만약 age = 130 값을 등록하려고 하면 제약조건 위배라서 ERROR가 발생합니다.

-- Error due to violating of 'age' check constraint

INSERT INTO cust.cust_master VALUES

(7, 'sedae', 'mun', 'M', 130, '2020-05-23');

ERROR: new row for relation "cust_master" violates check constraint "cust_master_age_check"

DETAIL: Failing row contains (7, sedae, mun, M, 130, 2020-05-23).

********** Error **********

ERROR: new row for relation "cust_master" violates check constraint "cust_master_age_check"

SQL state: 23514

Detail: Failing row contains (7, sedae, mun, M, 130, 2020-05-23).

(5) INDEX 생성하기

: CREATE INDEX index_name ON schema.table(column);

select 로 값을 조회해서 가져오거나 table을 join 하거나 할 때 주로 사용되는 칼럼에 INDEX를 걸어두면 속도를 많이 향상시킬 수 있습니다. (대신에 처음에 INDEX 생성 시간은 추가 소요)

-- CREATE INDEX [name] ON table(column)

DROP INDEX IF EXISTS id_idx;

CREATE INDEX id_idx ON cust.cust_master(id);

(6) VIEW 생성하기: CREATE VIEW view_name AS select ... from;

테이블에서 필요한 부분만 가져와서 가상의 VIEW 를 만들면 편리하게 select 문으로 조회를 할 수 있습니다. 여러개의 테이블을 join 하고 여러개의 where 조건을 사용하여 VIEW 를 만들 수도 있습니다.

(가령, 현업에서 자주, 꾸준하게 사용하는 select query 문이 있다고 했을 때 IT팀에서 VIEW 를 만들어주면 협업과 IT팀이 모두 시간을 절약할 수 있어서 좋습니다.)

-- View

DROP VIEW IF EXISTS cust.cust_master_view;

CREATE VIEW cust.cust_master_view AS

SELECT id, first_name, last_name, joindate

FROM cust.cust_master

WHERE joindate >= '2020-05-01';

SELECT * FROM cust.cust_master_view;

만약 VIEW 를 만들 때 사용한 source table을 drop table 하려고 하면 ERROR가 납니다.

-- Error: cannot drop table because view depends on it

DROP TABLE IF EXISTS cust.cust_master;

ERROR: cannot drop table cust.cust_master because other objects depend on it

DETAIL: view cust.cust_master_view depends on table cust.cust_master

HINT: Use DROP ... CASCADE to drop the dependent objects too.

VIEW 가 의존하고 있는 source table을 제거(drop)하고 싶을 때는 DROP TABLE table_name CASCADE; 처럼 뒤에 'CASCADE'를 써주면 삭제하려는 테이블에 의존하는 다른 객체들도 한꺼번에 같이 삭제를 해줍니다.

-- Use 'CASCADE' to drop the dependent objects too

DROP TABLE IF EXISTS cust.cust_master CASCADE;

NOTICE: drop cascades to view cust.cust_master_view

다음 포스팅에서는 테이블 내 데이터를 수정(UPDATE), 삭제(DELETE) 하는 방법(https://rfriend.tistory.com/538)을 소개하겠습니다.

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators) (0)	2020.06.28
[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate (0)	2020.06.20
[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally (4)	2020.04.16
[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측 (0)	2020.04.12
[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측 (0)	2020.04.12

Posted by Rfriend

[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally

Greenplum and PostgreSQL Database 2020. 4. 16. 18:15

로컬 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

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

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;

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate (0)	2020.06.20
[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기 (0)	2020.06.14
[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측 (0)	2020.04.12
[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측 (0)	2020.04.12
[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) (0)	2020.04.09

Posted by Rfriend

[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측

Greenplum and PostgreSQL Database 2020. 4. 12. 21:24

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

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기 (0)	2020.06.14
[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally (4)	2020.04.16
[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측 (0)	2020.04.12
[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) (0)	2020.04.09
[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법 (4)	2020.04.05

Posted by Rfriend

[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측

Greenplum and PostgreSQL Database 2020. 4. 12. 14:01

지난번 포스팅에서는 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 만들기

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

-- 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) 하는 방법을 소개하겠습니다.

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally (4)	2020.04.16
[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측 (0)	2020.04.12
[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) (0)	2020.04.09
[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법 (4)	2020.04.05
[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01

Posted by Rfriend

[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum)

Greenplum and PostgreSQL Database 2020. 4. 9. 23:23

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

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

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

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

)

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

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

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;

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측 (0)	2020.04.12
[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측 (0)	2020.04.12
[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법 (4)	2020.04.05
[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01
[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측 (0)	2020.03.29

Posted by Rfriend

[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법

Greenplum and PostgreSQL Database 2020. 4. 5. 23:03

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

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

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

# 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

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;

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측 (0)	2020.04.12
[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum) (0)	2020.04.09
[Greenplum] PL/R Error ERROR: Error on receive from seg0 slice1: server closed the connection unexpectedly 확인사항과 대처 방안 (0)	2020.04.01
[Greenplum] PL/R을 이용한 그룹별 선형회귀모형 분산 병렬 학습 및 예측 (0)	2020.03.29
[PostgreSQL, Greenplum] 10분 단위 구간별로 시계열 데이터를 집계/요약하기 (aggregating time series values by 10 minutes time span using PostgreSQL, Greenplum) (0)	2019.12.15

Posted by Rfriend

[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기

Greenplum and PostgreSQL Database 2019. 4. 17. 20:44

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

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

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

(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

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

(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

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

(1 row)

addoverviewconstraints

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

(1 row)

addoverviewconstraints

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

(1 row)

COMMIT

[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

Processing 3/4: gray_50m_partial_tl.tif

INSERT 0 1

Processing 4/4: gray_50m_partial_tr.tif

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

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

(1 row)

COMMIT

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

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

728x90

저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] GPDB에 PL/R Language Extension, R 패키지 수동 설치 방법 (0)	2019.05.16
[Greenplum DB] PostGIS - 공간지리 테이블 백업하기, 백업 다시 불러오기 (Backup and Restore geospatial table using pg_dump, pg_restore) (0)	2019.04.27
[Greenplum DB] PostGIS : ogr2ogr 을 사용해 공간지리 벡터 데이터 import 하기 (GML, MIF, KML 포맷) (0)	2019.04.11
[Greenplum DB] 공간지리 shape files (SHP, SHX, DBF, PRJ)를 PostgreSQL, Greenplum DB로 import 하는 방법 (0)	2019.04.11
[Greenplum DB] PostGIS : 위도, 경도가 있는 csv 파일을 import하고 공간정보 뽑아내기 (0)	2019.04.10

Posted by Rfriend

이전 1 2 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'postgresql db'에 해당되는 글 12건

[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum DB] 수정 Update, 삭제 Delete, 테이블 비우기 Truncate

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum DB] DATABASE, SCHEMA, TABLE, VIEW 만들고 데이터 등록(INSERT)하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL DB] 오류에 견고한 운영을 위한 PL/R 예외 처리 tryCatch(), error, warning, finally

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PivotalR 패키지를 이용한 그룹별 선형회귀모형 적합과 예측

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL] MADlib 을 활용한 그룹별 선형회귀모형 분산병렬 적합 및 예측

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum & PostgreSQL DB] PL/R 결과를 반환하는 5가지 방법 (5 ways to return PL/R result on Greenplum)

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum, PostgreSQL DB] PL/R 을 위한 데이터셋 array aggregation 준비하는 3가지 방법

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[Greenplum DB] PostGIS에 raster2pgsql 을 사용하여 raster data import 하기

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바