대규모 언어 모델 (Large Language Model, LLM) 이 사실에 입각하지 않고 그럴싸한 답변을 생성하는 것을 환각 (Hallucinations) 이라고 합니다. LLM의 환각을 방지하는 방법 중의 하나로 RAG (Retrieval-Agumented Generation) 이 많이 사용되고 있는데요, 이전에 소개했던 RAG 는 비정형 데이터인 텍스트를 임베딩하여 Semactic Search를 하여 Context로 사용할 문서를 LLM에 인풋으로 넣어주는 방법이었습니다. 

 

이번 포스팅에서는 LangChain과 ChatGTP를 이용해서 PostgreSQL, Greenplum Database의 테이블에 행, 열의 테이블에 저장되어 있는 정형 데이터 (Structured Data)에 대해서 SQL Query를 사용해서 질의를 해서 사실 (Fact) 데이터를 Query 해와서, 이를 기반으로 LLM 모델이 답변을 생성하는 방법을 소개하겠습니다. 자연어로 질문하고 자연어로 답변을 받게 되면 DB나 SQL Query를 모르는 일반 사용자도 DB에 저장되어 있는 사실에 기반한 최신의 내부 데이터까지도 활용해서 정확한 답변을 받을 수 있게 됩니다. 

 

 

먼저, PostgreSQL, Greenplum 데이터베이스에 대해서 간략히 소개하겠습니다. 

 

 

1. PostgreSQL 데이터베이스란? 

 

PostgreSQL, 종종 Postgres로 불리는 이 데이터베이스는 강력한 오픈 소스 객체-관계형 데이터베이스 시스템입니다. 이는 신뢰성, 기능의 견고함, 그리고 성능 면에서 좋은 평판을 가지고 있습니다.  

PostgreSQL은 객체-관계형 데이터베이스로서, 전통적인 관계형 데이터베이스 기능인 테이블 기반 데이터 저장 및 SQL(Structured Query Language) 쿼리와 더불어 객체 지향 데이터베이스 기능인 객체와 클래스에 데이터를 저장하는 것을 지원합니다. 

이는 무료로 자유롭게 사용할 수 있는 오픈 소스 소프트웨어입니다. 그 소스 코드는 누구나 열람하거나 개선하길 원하는 사람에게 공개되어 있습니다. 

PostgreSQL의 핵심 강점 중 하나는 확장성입니다. 사용자는 자신만의 데이터 타입, 인덱스 타입, 함수 언어 등을 정의할 수 있습니다. 

 

2. Greenplum 데이터베이스란? 

 

Greenplum 데이터베이스는 고급 기능을 갖춘 오픈 소스 데이터 웨어하우스입니다. 이는 페타바이트 규모의 빅데이터에 대해 강력하고 빠른 분석을 제공합니다. 원래 PostgreSQL에서 파생되었지만, Greenplum 데이터베이스는 그 능력과 성능을 크게 확장해 발전시켰습니다. 

Greenplum은 Shared-Nothing, 대규모 병렬 처리 아키텍처(Massively Parallel Processing Architecture, MPP)를 사용합니다. 이는 데이터가 여러 세그먼트로 나뉘고 각 세그먼트가 병렬로 처리되어 고성능 및 대규모 데이터 분석을 가능하게 한다는 의미입니다. 

확장성(Scalability)을 위해 설계된 Greenplum은 큰 데이터 볼륨을 처리할 수 있습니다. 이는 많은 서버에 걸쳐 확장되어 큰 데이터 볼륨과 복잡한 쿼리를 관리할 수 있습니다. 

Greenplum은 고급 분석 기능을 지원합니다. 이는 Python, R 및 기타 통계 및 머신 러닝 라이브러리와 통합되어 데이터베이스 내에서 고급 데이터 분석을 직접 수행할 수 있게 합니다. 

(Greenplum DB 소개 참고: https://rfriend.tistory.com/377)

 

 

[ LangChain과 ChatGPT를 사용하여 자연어로 PostgreSQL, Greenplum에 Query하는 Workflow ]

LangChain - ChatGPT - PostgreSQL, Greenplum DB에 자연어로 질의하여 Query 하기

 

 

 

3. LangChain, ChatGPT로 PostgreSQL에 자연어로 Query해서 답변 생성하기

 

(1) 사용자가 애플리케이션에서 자연어로 질의
(2) LangChain이 LLM 모델에 사용자 질문을 SQL Query로 변환 요청
(3) LLM 모델이 DB Schema 정보에 기반해 사용자 질문에 대한 SQL Query를 생성해서 반환
(4) LangChain이 PostgreSQL, Greenplum DB에 SQL Query 실행 요청
(5) PostgreSQL, Greenplum DB에서 SQL Query 실행하여 결과 반환
(6) LangChain이 사용자 질문과 SQL Query, Query 결과를 기반으로 LLM 모델에 답변 생성 요청 
(7) LLM 모델이 사용자 질문과 Query 결과를 기반으로 생성한 자연어 답변 반환
(8) LangChain이 애플리케이션의 사용자 UI에 LLM 모델이 생성한 자연어 답변 반환

 

 

 

(0) 준비사항: PostgreSQL에 예제 테이블 생성하고 데이터 집어넣기

 

먼저, 예제로 사용할 PostgreSQL DB에 iris 라는 테이블을 만들어보겠습니다. 그리고, 나중에 자연어로 질의할 내용을 미리 SQL Query로 결과를 조회해보았습니다. 

 

DROP TABLE IF EXISTS iris;
CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,
                    petal_length FLOAT, petal_width FLOAT,
                   class_name text);

INSERT INTO iris VALUES
(1,5.1,3.5,1.4,0.2,'Iris-setosa'),
(2,4.9,3.0,1.4,0.2,'Iris-setosa'),
(3,4.7,3.2,1.3,0.2,'Iris-setosa'),
(4,4.6,3.1,1.5,0.2,'Iris-setosa'),
(5,5.0,3.6,1.4,0.2,'Iris-setosa'),
(6,5.4,3.9,1.7,0.4,'Iris-setosa'),
(7,4.6,3.4,1.4,0.3,'Iris-setosa'),
(8,5.0,3.4,1.5,0.2,'Iris-setosa'),
(9,4.4,2.9,1.4,0.2,'Iris-setosa'),
(10,4.9,3.1,1.5,0.1,'Iris-setosa'),
(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),
(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),
(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),
(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),
(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),
(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),
(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),
(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),
(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),
(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),
(21,6.3,3.3,6.0,2.5,'Iris-virginica'),
(22,5.8,2.7,5.1,1.9,'Iris-virginica'),
(23,7.1,3.0,5.9,2.1,'Iris-virginica'),
(24,6.3,2.9,5.6,1.8,'Iris-virginica'),
(25,6.5,3.0,5.8,2.2,'Iris-virginica'),
(26,7.6,3.0,6.6,2.1,'Iris-virginica'),
(27,4.9,2.5,4.5,1.7,'Iris-virginica'),
(28,7.3,2.9,6.3,1.8,'Iris-virginica'),
(29,6.7,2.5,5.8,1.8,'Iris-virginica'),
(30,7.2,3.6,6.1,2.5,'Iris-virginica');

SELECT * FROM iris ORDER BY id LIMIT 5;
-- 5.1	3.5	1.4	0.2	"Iris-setosa"
-- 4.9	3	1.4	0.2	"Iris-setosa"
-- 4.7	3.2	1.3	0.2	"Iris-setosa"
-- 4.6	3.1	1.5	0.2	"Iris-setosa"
-- 5	3.6	1.4	0.2	"Iris-setosa"

SELECT 
    AVG(sepal_length) 
FROM iris 
WHERE class_name = 'Iris-setosa';
-- 4.859999999999999

SELECT 
	class_name,
	AVG(sepal_length) 
FROM iris 
GROUP BY class_name
ORDER BY class_name;

-- class_name         avg
-- "Iris-setosa"	  4.85
-- "Iris-versicolor"  6.10
-- "Iris-virginica"   6.57


SELECT 
	class_name
	, MAX(sepal_width) 
FROM iris 
GROUP BY class_name 
ORDER BY class_name;

-- class_name          max
-- "Iris-setosa"	   3.9
-- "Iris-versicolor"   3.3
-- "Iris-virginica"    3.6

 

 

 

(0) 준비사항: Python 모듈 설치

 

LLM 모델을 통한 답변 생성에 필요한 openai, langchain, 그리고 PostgreSQL, Greenplum DB access를 위해 필요한 psycopg2 모듈을 pip install을 사용해서 설치합니다. 

 

! pip install -q openai langchain psycopg2

 

 

(0) 준비사항: OpenAI API Key 설정

 

OpenAI의 ChatGPT를 사용하기 위해 필요한 OpenAI API Key를 등록합니다. 

(OpenAI API Key 발급 방법은 https://rfriend.tistory.com/794 를 참고하세요)

 

import os

# setup OpenAI API Key with yours
os.environ["OPENAI_API_KEY"]="sk-xxxx..." # set with yours

 

 

(0) 준비사항: PostgreSQL DB 연결

 

이제 LangChain의 SQLDatabase() 클래스를 사용해서 PostgreSQL DB에 연결하겠습니다. 

(DB credientials 는 사용 중인 걸로 바꿔주세요)

 

# Connect to the PostgreSQL DB
from langchain.utilities import SQLDatabase

# set with yours
username='postgres'
password='changeme'
host='localhost'
port='5432'
database='postgres'

pg_uri = f"postgresql+psycopg2://{username}:{password}@{host}:{port}/{database}"
        
db = SQLDatabase.from_uri(pg_uri)

 

 

(0) 준비사항: Query를 위한 Helper Function 정의

 

DB의 Table 정보 (테이블 이름, 칼럼 정보) 를 가져오고, SQL Query를 실행할 수 있는 Helper 함수를 정의합니다. 이제 준비가 다 되었네요. 

 

# Helper functions
def get_schema(_):
    return db.get_table_info()
    
def run_query(query):
    return db.run(query)

 

 

 

(1) 사용자가 애플리케이션에서 자연어로 질의

 

사용자가 자연어로 질의한 질문과 DB테이블 정보를 인풋으로 받아서, "DB 테이블 정보 ("scheam")와 사용자 질문 ("question")이 주어졌을 때 PostgreSQL Query를 생성하라"고 프롬프트에 지시문을 생성하였습니다. 

 

# Prompt for generating a SQL query
from langchain.prompts import ChatPromptTemplate

template_query = """
Based on the table schema below, \
Write a PostgreSQL query that answer the user's question:
{schema}

Question: {question}
SQL Query:"""

prompt = ChatPromptTemplate.from_template(template_query)

 

 

 

(2) LangChain이 LLM 모델에 사용자 질문을 SQL Query로 변환 요청

 

LLM에는 ChatGPT-4 모델을 사용하고, temperature=0 으로 해서 사실에 기반해서 일관성 있고 보수적인 답변을 생성하도록 설정했습니다. 

 

# Chaining prompt, LLM model, and Output Parser
from langchain.chat_models import ChatOpenAI
from langchain_core.output_parsers import StrOutputParser
from langchain_core.runnables import RunnablePassthrough

model = ChatOpenAI(temperature=0, model_name='gpt-4')

sql_response = (
    RunnablePassthrough.assign(schema=get_schema) 
    | prompt
    | model.bind(stop=["\nSQLResult:"])
    | StrOutputParser()
)

 

 

 

(3) LLM 모델이 DB Schema 정보에 기반해 사용자 질문에 대한 SQL Query를 생성해서 반환

 

sql_response.invoke({"question": "What is the average of sepal length for Iris-setosa?"})
# "SELECT AVG(sepal_length) \nFROM iris \nWHERE class_name = 'Iris-setosa';"

 

 

 

(4) LangChain이 PostgreSQL, Greenplum DB에 SQL Query 실행 요청

 

아래와 같이 "DB 테이블 정보 ("schema"), 사용자 질문 ("question"), SQL Query ("query"), SQL Query 결과 ("response") 가 주어졌을 때 자연어(natural language)로 답변을 생성하라"고 프롬프트 지시문을 만들었습니다. 

 

# Prompt for generating the final answer by running a SQL query on DB
template_response = """
Based on the table schema below, question, sql query, and sql response, \
write a natural language response:
{schema}

Question: {question}
SQL Query: {query}
SQL Response: {response}"""

prompt_response = ChatPromptTemplate.from_template(template_response)

 

 

 

(5) PostgreSQL, Greenplum DB에서 SQL Query 실행하여 결과 반환

 

'|' 를 사용해서 앞에서 정의한 SQL Query 생성하는 chain과 Query를 실행한 결과를 받아서 자연어로 답변을 생성하는 chain을 모두 엮어서 Chaining 하였습니다. 

 

full_chain = (
    RunnablePassthrough.assign(query=sql_response)
    | RunnablePassthrough.assign(
        schema=get_schema, 
        response=lambda x: db.run(x["query"]),
    )
    | prompt_response
    | model
    | StrOutputParser()
)

 

 

 

(6) LangChain이 사용자 질문과 SQL Query, Query 결과를 기반으로 LLM 모델에 답변 생성 요청 

(7) LLM 모델이 사용자 질문과 Query 결과를 기반으로 생성한 자연어 답변 반환

 

자연어로 3개의 질문을 해보았는데요, 기대했던 SQL Query 문이 생성되고 Query 질의 결과를 기반으로 답변이 모두 정확하게 생성되었습니다. 

 

Q1: What is the average of sepal length for Iris-setosa?

 

## Q: What is the average of sepal length for Iris-setosa?

sql_response.invoke({"question": "What is the average of sepal length for Iris-setosa?"})
# "SELECT AVG(sepal_length) \nFROM iris \nWHERE class_name = 'Iris-setosa';"

full_chain.invoke({"question": "What is the average of sepal length for Iris-setosa?"})
# 'The average sepal length for Iris-setosa is approximately 4.86.'

 

 

Q2: What is the average of sepal length by class name?

 

## Q: What is the average of sepal length by class name?

sql_response.invoke({"question": "What is the average of sepal length by class name?"})
# 'SELECT class_name, AVG(sepal_length) \nFROM iris \nGROUP BY class_name;'


full_chain.invoke({"question": "What is the average of sepal length by class name?"})
#'The average sepal length for the Iris-versicolor class is 6.1, 
#for the Iris-setosa class is approximately 4.86, 
#and for the Iris-virginica class is 6.57.'

 

 

Q3: What is the maximum value of sepal width by class name?

 

## Q: What is the maximum value of sepal width by class name?

sql_response.invoke({"question": "What is the maximum value of sepal width by class name?"})
# 'SELECT class_name, MAX(sepal_width) \nFROM iris \nGROUP BY class_name;'


full_chain.invoke({"question": "What is the maximum value of sepal width by class name?"})
# "The maximum sepal width for the class 'Iris-versicolor' is 3.3, 
# for 'Iris-setosa' is 3.9, 
# and for 'Iris-virginica' is 3.6."

 

 

위의 예는 매우 간단한 질문이어서 하나의 테이블 내에서 Join 없이도 모두 정확하게 Querying 이 되었는데요, 그래도 mission critical한 업무에서는 사용에 유의할 필요가 있습니다. 왜냐하면 실무에서 사용하는 SQL Query 문의 경우 여러개의 테이블을 Join 해야 될 수도 있고, Where 조건절에 Business Logic이 복잡하게 반영되어야 할 경우도 있고, 테이블 이름/설명이나 변수명/설명이 LLM 모델이 사용자의 질의와 매핑해서 사용하기에 부적절한 경우도 있을 수 있어서 SQL Query가 부정확할 수 있기 때문입니다.

 

따라서 반드시 사용자 질의에 대한 SQL Query 문을 같이 확인해보는 절차가 필요합니다. 

 

그리고 SQL Query Generator 를 잘하는 LLM 모델, 자사 회사 내 SQL Query 문으로 Fine-tuning한 LLM 모델에 대한 시도도 의미있어 보입니다. 

 

 

[ Reference ]

* LangChain - Querying a SQL DB:

https://python.langchain.com/docs/expression_language/cookbook/sql_db

* PostgreSQL 설치(OS 별 packages, installers): https://www.postgresql.org/download/

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,
[알림] * 본 포스팅 글은 Ahmed Rachid Hazourli (Greenplum Data Engineer in VMware) 가 medium.com 에 2023.5.29일에 "Building large-scale AI-powered search in Greenplum using pgvector and OpenAI"
 라는 제목으로 포스팅한 글을 저자의 동의를 얻어서 한국어로 번역한 것입니다.

 

 

Greenplum의 pgvector와 OpenAI를 이용하여 대규모 AI 기반 검색 구축하기
(Building large-scale AI-powered search in Greenplum using pgvector and OpenAI)

 

[들어가는 글]

지난 몇 년간 ChatGPT와 같은 AI 모델의 기하급수적인 발전은 많은 조직이 생성 AI(Generative AI) 및 LLM(Large Language Model)을 출시하여 사용자 경험을 향상시키고 텍스트에서 이미지, 비디오에 이르기까지 비정형 데이터의 잠재력을 최대한 활용하도록 영감을 주었습니다.

이 블로그 글에서는 Greenplum 데이터 웨어하우스 내에서 pgvector 확장의 벡터 유사성 검색(vector similarity search) 기능을 활용하고 이를 OpenAI 모델과 결합하여 페타바이트급 대규모 텍스트 데이터에서 귀중한 통찰력을 추출하고 Greenplum의 놀라운 MPP 아키텍처(Massively Parallel Processing Architecture)를 활용하는 방법에 대해 알아보겠습니다.

 

large-scale AI-powered search using Greenplum pgvector and OpenAI

 

 

 

도입 (Introduction): 


기업들은 AI를 위해 데이터 플랫폼을 확장하고 챗봇, 추천 시스템 또는 검색 엔진에 대용량 언어 모델을 사용할 수 있는 기술과 방법을 찾기 시작했습니다. 

그러나 한 가지 구체적인 과제는 이러한 AI 모델을 관리 및 배포하고 ML 생성 임베딩(ML-generated embeddings)을 규모있게 저장 및 쿼리하는 것이었습니다.

 


임베딩이란 무엇입니까? (What are embeddings?)


임베딩(Embeddings)은 데이터 또는 텍스트, 이미지 또는 오디오와 같은 복잡한 객체를 고차원 공간의 숫자들의 리스트로 변환하는 것을 말합니다.

Embedding model, 임베딩 모델

* 이미지 출처: OpenAI

 

 

 

 

이 기술은 데이터의 의미와 맥락(의미론적 관계, semantic relationships) 및 데이터 내의 복잡한 관계와 패턴(구문론적 관계, syntactic relationship)에 대한 지식을 캡처/이해할 수 있게 해주는 모든 기계학습(ML) 또는 딥러닝(DL) 알고리듬에 사용됩니다.

 

Embedding Model, 임베딩 모델

* 이미지 출처: https://www.pinecone.io/learn/vector-embeddings/

 

 

 

정보 검색, 이미지 분류, 자연어 처리 등 다양한 애플리케이션에 대해 벡터 표현(vector representations) 결과를 사용할 수 있습니다. 

Object - Vector - Task

* 이미지 출처: https://dev.to/josethz00/vector-databases-5df1

 

 

 

 

다음 다이어그램은 2D 공간에서 단어 임베딩(woed embeddings in 2D space)이 어떻게 보여지는지를 시각적으로 나타냅니다.

word embedding in 2D space

* 이미지 출처: https://neon.tech/blog/building-an-ai-powered-chatbot-using-vercel-openai-and-postgres


의미론적으로 유사한 단어들이 임베딩에서 서로 가까이 있다는 것을 알 수 있습니다. 예를 들어, "사과"라는 단어는 "개"나 "고양이"보다 "오렌지"에 더 가깝습니다.

임베딩을 생성한 후, 회사는 벡터 공간 내에서 유사성 검색(similarity searches)을 수행하고 제품 추천 시스템과 같은 AI 애플리케이션을 구축할 수 있습니다. 

 

 

 

pgvector를 사용하여 Greenplum에 임베딩 저장하기
(Storing embeddings in Greeplum using pgvector)

 

Greenplum 7은 pgvector 확장(pgvector extension) 덕분에 벡터 임베딩을 대규모로 저장하고 쿼리할 준비가 잘 되어 있습니다. 이를 통해 Greenplum 데이터 웨어하우스에 벡터 데이터베이스(vector database) 기능을 제공하여 사용자가 빠른 검색과 효율적인 유사성 검색을 수행할 수 있습니다. 

 

Greenplum의 pgvector 를 사용하여 ML 지원 응용프로그램에 대한 데이터베이스를 설정, 운영 및 확장할 수 있습니다.

예를 들어, 스트리밍 서비스는 pgvector를 사용하여 방금 본 것과 유사한 영화 추천 목록을 제공할 수 있습니다.

 

movie recommendations using embeddings

 

 

 

왜 Greenplum 이고 pgvector 인가? 

 

많은 기업이 다른 벡터 데이터베이스를 관리하지 않고도 엔터프라이즈 데이터 웨어하우스 내에서 벡터 의미 검색(vector semantic searches)을 저장, 쿼리 및 수행하려고 합니다.

다행히 Greenplum과 pgvector를 결합하면 AI 모델의 임베딩을 사용하여 빠르고 확장 가능한 애플리케이션을 구축하고 더 빨리 운영에 들어갈 수 있습니다. 

 

 

 

 

pgvector와 OpenAI를 사용하여 Greenplum에서 제품 설명서에 사용할 AI-Assistant를 구축하기.

 


문맥: 

우리 모두는 이전에 ChatGPT와 같은 챗봇을 사용한 적이 있으며 캐주얼하고 범용적인 질문에 적합하다는 것을 알았습니다. 하지만, 깊고 도메인별 지식이 필요할 때 ChatGPT는 부족하다는 것을 알아차렸을 수도 있습니다. 또한, 그것은 지식의 격차를 메우기 위해 답을 만들고 결코 출처를 언급하지 않습니다.

하지만 어떻게 이것을 개선할 수 있을까요? 적합한 데이터 소스를 정확하게 검색하고 질문에 답변하는 ChatGPT를 구축하려면 어떻게 해야 할까요?

 


답변:

이 질문에 대한 대답은 제품 설명서를 검색 가능하게 만들고 작업별 프롬프트를 OpenAI에 제공하면 결과가 더 신뢰할 수 있다는 것입니다. 즉, 사용자가 질문할 때 Greenplum 테이블에서 적합한 데이터 세트를 검색하도록 pgvector에게 요청합니다. 그런 다음 사용자의 질문에 답변하기 위한 참조 문서(reference document)로 OpenAI에 제공합니다. 

 

 

 

실제 임베딩 적용하기:

이 섹션에서는 임베딩을 실제 적용한 모습을 살펴보고, 임베딩 저장을 용이하게 하고 벡터의 가장 가까운 이웃에 대한 쿼리를 가능하게 하는 Greenplum에 대한 오픈 소스 pgvector 확장을 사용하는 방법을 배울 것입니다.

다음 그림과 같이 OpenAI를 사용하여 지능형 챗봇을 구축하고 시맨틱 텍스트 검색을 통해 Greenplum, RabbitMQ, Gemfire, VMware SQL 및 VMware Data Service Manager에 대한 자세한 기술적 질문에 답변할 수 있는 VMware 데이터 솔루션에 대한 도메인별 지식을 얻을 수 있도록 지원함으로써 이 기능을 시연합니다:

 

Greenplum Database, RabbitMQ, Gemfire, OpenAI ChatGPT

 

 

주요 절차는 다음과 같습니다. 

 

main steps of using Greenplum and OpenAI

 

 

 

1. pgvector extension 을 설치하고 활성화합니다.

 

pgvector 를 설치한 후에 Greenplum에서 벡터 임베딩의 저장을 시작하고 다음과 같이 pgvector 실행을 활성화하여 의미 검색(semantic searches)을 수행할 수 있습니다:

CREATE EXTENSION vector;

 

 

 

2. VECTOR 데이터 유형으로 제품 설명서 테이블 만들기

 

다음 SQL 쿼리로 제품 설명서와 임베딩을 저장할 테이블을 만들어 보겠습니다:

 

CREATE TABLE tanzu_documents (
  id bigserial primary key,
  content text,
  embedding vector(1536)
)
DISTRIBUTED BY (id)
;

 

pgvector는 벡터(VECTOR data-type)라고 불리는 새로운 데이터 유형을 도입합니다. 우리는 위의 쿼리 코드에서 벡터 데이터 유형으로 임베딩 열을 만들었습니다. 벡터의 크기는 벡터가 얼마나 많은 차원을 보유하는지 정의합니다. OpenAI의 text—embedding-ada-002 모델은 1,536개의 차원을 출력하므로 벡터 크기에 사용할 것입니다.

이 게시물에서 OpenAI API를 사용하고 있으므로 다음을 실행하는 모든 Greenplum 호스트에 openai 패키지를 설치합니다:

 

gpssh -f gphostsfile -e 'pip3 install -y openai'

 

또한 이 임베딩을 생성한 원본 제품 설명서 텍스트를 저장하기 위해 content 라는 text 열을 만듭니다.

 

참고: 위의 table은 Greenplum 세그먼트에 걸쳐 "id" 열을 기준으로 분산 저장(distributed by the “id”)되며, pgvector extension은 Greenplum 기능과 완벽하게 작동합니다. 따라서 분산저장에서 파티셔닝에 이르기까지 Greenplum의 MPP(Massiviely Parallel Processing) 기능에 대량의 데이터를 관리하고 검색하는 pgvector의 효율성을 추가하면 Greenplum 사용자는 확장 가능한 규모있는 AI 애플리케이션을 구축할 수 있습니다.

 

 

 

3. OpenAI 임베딩 가져오기 위한 Greenplum PL/Python 함수 

 

이제 문서에 대한 임베딩을 생성해야 합니다. 여기서는 OpenAI의 text-message-ada-002 모델 API를 사용하여 텍스트에서 임베딩을 생성합니다.

가장 좋은 방법은 Greenplum 데이터베이스 내에 PL/Python3u 절차적 언어(Procedural Language)를 사용하여 Python 함수를 생성하는 것입니다. 다음 Greenplum Python 함수는 각 입력 문서에 대한 벡터 임베딩(vector embeddings)을 반환합니다.

 

CREATE OR REPLACE FUNCTION get_embeddings(content text)
RETURNS VECTOR
AS
$$

  import openai
  import os
  text = content
  openai.api_key = os.getenv("OPENAI_API_KEY")
  response = openai.Embedding.create(
         model="text-embedding-ada-002",
         input = text.replace("\n"," ")
     )
  
  embedding = response['data'][0]['embedding']
  return embedding

$$ LANGUAGE PLPYTHON3U;

* 참고: OpenAI API key 생성하는 방법은 https://rfriend.tistory.com/794 를 참고하세요. 

 

 

 

4. Greenplum 테이블에 데이터 넣기


원본 텍스트를 tanzu_documents 테이블, 특히 content 열에 로딩한 다음, embedding 열을 업데이트하고 이전에 생성된 get_messages Python 함수를 사용하여 모든 컨텐츠에 대해 OpenAI 임베딩을 생성합니다:

 

UPDATE tanzu_documents SET embedding  = get_embeddings(content);

 

 

 

5. 첫 번째 의미론적 검색 (Semantic Search) 질의


pgvector의 코사인 거리를 사용하여 (<=> 연산자를 사용하여) 첫 번째 의미 검색 쿼리를 만들고, 질문과 가장 유사한 텍스트(즉, 최소 거리를 가진 텍스트)를 찾아보겠습니다: Greenplum 설치 방법? (How to install Greenplum? )

 

WITH cte_question_embedding AS 
  (
SELECT 
	get_embeddings(
    	'How to create an external table in Greenplum 
         using PXF to read from an Oracle database ?'
         ) 
        AS question_embeddings 
) 

SELECT 
	id
    , content
    , embedding <=> cte_question_embedding.question_embeddings AS distance 
FROM tanzu_documents, cte_question_embedding  
ORDER BY embedding <=> cte_question_embedding.question_embeddings ASC 
LIMIT 1;

 

pgvector는 유사성을 계산하는 데 사용할 수 있는 세 가지 새로운 연산자를 소개합니다: 
 - (1) 유클리드 거리 (Euclidean distance)(L2 거리): <->, 
 - (2) 음의 내적 (Negative inner product): <#>, 
 - (3) 코사인 거리 (Cosine distance): <=> 

* 참고: 유클리드 거리 (Euclidean distance)는 https://rfriend.tistory.com/199 를 참고하세요.

            코사인 거리 (Cosine distance)는 https://rfriend.tistory.com/319 를 참고하세요. 

 

 

 

SELECT 문은 다음 출력을 반환해야 합니다:

 

id       | 640
content  | title: Accessing External Data with PXF 
           -- Data managed by your organisation may already reside in external sources
           -- such as Hadoop, object stores, and other SQL databases. 
           -- The Greenplum Platform Extension Framework \(PXF\) provides access 
           -- to this external data via built-in connectors that map an external 
           -- data source to a Greenplum Database table definition. 
           -- PXF is installed with Hadoop and Object Storage connectors. 
           -- These connectors enable you to read external data stored in text, 
           -- Avro, JSON, RCFile, Parquet, SequenceFile, and ORC formats. 
           -- You can use the JDBC connector to access an external SQL database. 
           -- > **Note** In previous versions of the Greenplum Database, 
           -- you may have used the `gphdfs` external table protocol to access 
           -- data stored in Hadoop. Greenplum Database version 6.0.0 
           -- removes the `gphdfs` protocol. Use PXF and the `pxf` external table 
           -- protocol to access Hadoop in Greenplum Database version 6.x. 
           -- The Greenplum Platform Extension Framework includes 
           -- a C-language extension and a Java service. 
           -- After configuring and initialising PXF, you start a single 
           -- PXF JVM process on each Greenplum Database segment host. 
           -- This long-running process concurrently serves multiple query requests. 
           -- For detailed information about the architecture of and using PXF, 
           -- refer to the [Greenplum Platform Extension Framework \(PXF\)]
           -- (https://docs.vmware.com/en/VMware-Greenplum-Platform-Extension-Framework
           -- /6.6/greenplum-platform-extension-framework/overview_pxf.html) documentation. 
           -- **Parent topic:** [Working with External Data]
           -- (../external/g-working-with-file-based-ext-tables.html) **Parent topic:** 
           -- [Loading and Unloading Data](../load/topics/g-loading-and-unloading-data.html)
distance | 0.12006528354516588

 

 

 

6. 유사성 검색 SQL 함수:


많은 임베딩에 대해 유사성 검색을 수행할 예정이기 때문에, 이를 위한 SQL 사용자 정의 함수를 생성합니다:

CREATE OR REPLACE FUNCTION match_documents (
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)

RETURNS TABLE (
  id BIGINT,
  content TEXT,
  similarity FLOAT
)

AS $$

  SELECT
    documents.id,
    documents.content,
    1 - (documents.embedding <=> query_embedding) AS similarity
  FROM tanzu_documents documents
  WHERE 1 - (documents.embedding <=> query_embedding) > match_threshold
  ORDER BY similarity DESC
  LIMIT match_count;

$$ LANGUAGE SQL STABLE;

 

 


위에서 정의한 match_documents 함수를 사용하여 다음과 같이 가장 유사한 텍스트를 OpenAI 모델에 제공합니다:

 

SELECT t.id, t.content, t.similarity
  FROM match_documents(
      (select get_embeddings(
          'How to create an external table in Greenplum using PXF 
           to read from an Oracle database ?')) 
      , 0.8
      , 1) t
;
id         | 640
content    | title: Accessing External Data with PXF 
	-- Data managed by your organisation may already reside in external sources 
    -- such as Hadoop, object stores, and other SQL databases. 
    -- The Greenplum Platform Extension Framework \(PXF\) provides access 
    -- to this external data via built-in connectors 
    -- that map an external data source to a Greenplum Database table definition. 
    -- PXF is installed with Hadoop and Object Storage connectors. 
    -- These connectors enable you to read external data stored in text, Avro, 
    -- JSON, RCFile, Parquet, SequenceFile, and ORC formats. 
    -- You can use the JDBC connector to access an external SQL database. 
    -- > **Note** In previous versions of the Greenplum Database, 
    -- you may have used the `gphdfs` external table protocol to access data 
    -- stored in Hadoop. Greenplum Database version 6.0.0 removes the `gphdfs` protocol. 
    -- Use PXF and the `pxf` external table protocol to access Hadoop in 
    -- Greenplum Database version 6.x. 
    -- The Greenplum Platform Extension Framework includes a C-language extension 
    -- and a Java service. After configuring and initialising PXF, 
    -- you start a single PXF JVM process on each Greenplum Database segment host. 
    -- This long-running process concurrently serves multiple query requests. 
    -- For detailed information about the architecture of and using PXF, 
    -- refer to the [Greenplum Platform Extension Framework \(PXF\)]
    -- (https://docs.vmware.com/en/VMware-Greenplum-Platform-Extension-Framework/6.6/
    -- greenplum-platform-extension-framework/overview_pxf.html) documentation. 
    -- **Parent topic:** [Working with External Data](../external/g-working-with-file-based-ext-tables.html) 
    -- **Parent topic:** [Loading and Unloading Data](../load/topics/g-loading-and-unloading-data.html)
similarity | 0.8775289173395486

 

 

 

7. 벡터 인덱싱 (Vectors Indexing):

 

우리의 테이블은 임베딩과 함께 시간이 지남에 따라 커질 수 있으며, 수십억 개의 벡터에 걸쳐 의미 검색을 수행하기를 원할 것입니다.

pgvector의 뛰어난 점은 쿼리 속도를 높이고 검색 속도를 높일 수 있는 인덱싱(Indexing) 기능입니다.

벡터 인덱스는 정확한 최근접이웃 검색(ANN/KNN, Nearest Neighbour)을 수행합니다. 벡터는 유사성에 따라 그룹화되지 않으므로 순차적 검색(sequential scan)을 통해 가장 가까운 이웃을 찾는 작업은 느리며, 유사성에 따라 정렬을 빠르게 하는 것이 중요합니다(ORDER BY 절). 

각 거리 연산자에는 서로 다른 유형의 인덱스가 필요합니다. 시작할 때 적절한 수의 lists 는 1백만개 행까지는 1,000개, 1백만개 이상의 경우 sqrt(행) 개입니다. 코사인 거리로 정렬하기 때문에 vector_cosine_ops 인덱스를 사용합니다. 

 

-- Create a Vector Index 
CREATE INDEX ON tanzu_documents 
USING ivfflat (embedding vector_cosine_ops)
WITH
  (lists = 300);

-- Analyze table
ANALYZE tanzu_documents;

 

pgvector 인덱싱에 대한 자세한 내용은 여기에서 확인하십시오.
https://github.com/pgvector/pgvector#indexing

 

 

 

8. 관련 답변에 적합한 데이터 세트를 OpenAI 모델에 제공합니다. 


사용자의 인풋과 사용자 인풋에 가장 유사한 텍스트 둘 다를 인풋으로 사용해서 OPenAI 모델에게 답하도록 질문하는 PL/Python 함수를 정의합니다.  

CREATE FUNCTION ask_openai(user_input text, document text)
RETURNS TEXT
AS
$$

   import openai
   import os

   openai.api_key = os.getenv("OPENAI_API_KEY")
   search_string = user_input
   docs_text = document

   messages = [
   	{"role": "system",
    "content": "You concisely answer questions based on text provided to you."}
    ]

   prompt = """Answer the user's prompt or question:

   {search_string}

   by summarising the following text:

   {docs_text}

   Keep your answer direct and concise. Provide code snippets where applicable.
   The question is about a Greenplum / PostgreSQL database. 
   You can enrich the answer with other Greenplum or PostgreSQL-relevant details 
   if applicable.""".format(
   		search_string=search_string, 
        docs_text=docs_text
        )

   messages.append({"role": "user", "content": prompt})

   response = openai.ChatCompletion.create(
   		model="gpt-3.5-turbo", 
        messages=messages
        )
   
   return response.choices[0]["message"]["content"]

$$ LANGUAGE PLPYTHON3U;

 

 

 

9. 더 똑똑한 검색 기능 만들기

 

앞서 언급했듯이, ChatGPT는 기존의 문서만 반환하지 않습니다. ChatGPT는 다양한 정보를 이해해서 하나의 응집력있는 대답으로 만들 수 있습니다. 이를 위해 GPT에 관련 문서와 이 답변을 생성하는 데 사용할 수 있는 프롬프트를 제공해야 합니다.

마지막 단계로, 우리는 지능형 AI-Assistant 애플리케이션을 서비스하기 위해 이전 기능을 단일 프로세스로 결합해야 합니다. 

이전 기능과 임베딩은 프롬프트를 2단계 프로세스로 분할하여 이 문제를 해결할 수 있습니다: 

  1. 임베딩 데이터베이스에 질문과 가장 관련성이 높은 문서를 조회합니다.
  2. 이러한 문서를 OpenAI 모델이 답변에서 참조할 컨텍스트로 삽입합니다.

CREATE OR REPLACE FUNCTION intelligent_ai_assistant(
  user_input TEXT
)

RETURNS TABLE (
  content TEXT
)
LANGUAGE SQL STABLE
AS $$

  SELECT
    ask_openai(user_input, 
              (SELECT t.content 
                FROM match_documents(
                      (SELECT get_embeddings(user_input)) , 
                        0.8, 
                        1) t
                )
    );

$$;

 

위의 SQL 함수는 사용자 입력을 가져다가 임베딩으로 변환하고, tanzu_documents 테이블에 대해 pgvector를 사용하여 의미론적 텍스트 검색을 수행하여 가장 관련성이 높은 문서를 찾고, 마지막으로 이를 OpenAI API 호출에 대한 참조 텍스트로 제공하여 최종 답변을 반환합니다.

 

 

 

10. OpenAI 및 Streamlit 🎈를 활용한 시맨틱 텍스트 검색 기능으로 강화된 자체 챗봇 구축 🤖

 

마지막으로, 우리는 문서를 이해하고 pgvector 시맨틱 텍스트 검색과 함께 Greenplum 데이터 웨어하우스를 사용하는 Streamlit 🎈 챗봇 🤖를 개발했습니다.

챗봇 스트림릿 애플리케이션은 https://greenplum-pgvector-chatbot.streamlit.app/에서 이용할 수 있습니다. 

 

streamlit chatbot

 

소스 코드는 https://github.com/ahmedrachid/streamlit-chatbot-greenplum 에서 확인할 수 있습니다

 

🚀 결론

결론적으로, 확장 가능한 AI 애플리케이션을 구축하고자 하는 기업은 Greenplum 의 대규모 병렬 처리 기능 및 성능을 pgvector 연산과 결합함으로써, 방대한 양의 벡터 임베딩 및 비정형 데이터에 대해 빠른 검색, 유사성 및 의미 검색을 수행할 수 있습니다.



참조 (References): 

1. Open-source Greenplum data warehouse
   : https://greenplum.org/
2. VMware Greenplum data warehouse
   : https://docs.vmware.com/en/VMware-Tanzu-Greenplum/index.html
3. pgvector extension - Open-source vector similarity search for Postgres
   : https://github.com/pgvector/pgvector

 

 

읽어주셔서 감사합니다! 어떠한 의견이나 제안도 환영합니다!
여기에서 다른 Greenplum 기사를 확인하십시오.

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 Window 함수를 사용해서 그룹별로 요약 통계량을 구하고, 이 통계량을 이용해서 새로운 변수를 만드는 방법을 소개하겠습니다. 

 

(1) 전체 평균: MAX(value) OVER(PARTITION BY NULL) 을 계산해서 새로운 변수 만들기

(2) 그룹별 평균: MAX(value) OVER(PARTITION BY group_col) 을 계산해서 새로운 변수 만들기

 

 

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

 

-------------------------------------------------------------------
-- Aggregation by groupby using Window Function
-------------------------------------------------------------------

-- creating a sample table
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
	grp TEXT 
	, id INT
	, val INT
);

INSERT INTO tbl VALUES 
  ('a', 1, 4)
, ('a', 2, 1)
, ('a', 3, 3)
, ('a', 4, 5)
, ('a', 5, 2)
, ('b', 6, 7)
, ('b', 7, 5)
, ('b', 8, 8)
, ('b', 9, 10)
, ('b', 10, 9)
;

SELECT * FROM tbl ORDER BY 1, 2;
--grp|id|val|
-----+--+---+
--a  | 1|  4|
--a  | 2|  1|
--a  | 3|  3|
--a  | 4|  5|
--a  | 5|  2|
--b  | 6|  7|
--b  | 7|  5|
--b  | 8|  8|
--b  | 9| 10|
--b  |10|  9|

 

 

 

(1) 전체 평균: MAX(value) OVER (PARTITION BY NULL) 을 계산해서 새로운 변수 만들기

 

아래 예에서는 그룹별 구분없이 전체 최대값을 계산해서 MAX(val) OVER(PARTITION BY NULL) 해서 원래 값을 나누어주어서 새로운 변수 val_max_ration 를 만들어주었습니다. 

 

-- MAX value for all values using Window Function OVER(PARTITION BY NULL)
SELECT 
	a.*
	, val::NUMERIC / MAX(val) OVER(PARTITION BY NULL) 
		AS val_max_ratio 
FROM tbl AS a
ORDER BY 1, 2
;

--grp|id|val|val_max_ratio         |
-----+--+---+----------------------+
--a  | 1|  4|0.40000000000000000000|
--a  | 2|  1|0.10000000000000000000|
--a  | 3|  3|0.30000000000000000000|
--a  | 4|  5|0.50000000000000000000|
--a  | 5|  2|0.20000000000000000000|
--b  | 6|  7|0.70000000000000000000|
--b  | 7|  5|0.50000000000000000000|
--b  | 8|  8|0.80000000000000000000|
--b  | 9| 10|1.00000000000000000000|
--b  |10|  9|0.90000000000000000000|

 

 

 

(2) 그룹별 평균: MAX(value) OVER(PARTITION BY group_col) 을 계산해서 새로운 변수 만들기

 

아래의 예에서는 MAX(val) OVER(PARTITION BY grp)그룹별 최대값을 구해서 원래 값을 나누어줌으로써 그룹별 최대값 대비 값의 비율(val_grp_max_ration) 이라는 새로운 변수를 만들었습니다. 

 

-- MAX value by Group using Window Function OVER(PARTITION BY grp)
SELECT 
	a.*
	, val::NUMERIC / MAX(val) OVER(PARTITION BY grp) 
		AS val_grp_max_ratio 
FROM tbl AS a
ORDER BY 1, 2
;

--grp|id|val|val_grp_max_ratio         |
-----+--+---+----------------------+
--a  | 1|  4|0.80000000000000000000|
--a  | 2|  1|0.20000000000000000000|
--a  | 3|  3|0.60000000000000000000|
--a  | 4|  5|1.00000000000000000000|
--a  | 5|  2|0.40000000000000000000|
--b  | 6|  7|0.70000000000000000000|
--b  | 7|  5|0.50000000000000000000|
--b  | 8|  8|0.80000000000000000000|
--b  | 9| 10|1.00000000000000000000|
--b  |10|  9|0.90000000000000000000|

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

이동평균(Moving Average) 는 시계열 데이터를 분석할 때 이상치(Outlier), 특이값, 잡음(Noise) 의 영향을 줄이거나 제거하는 Smoothing 의 목적이나, 또는 미래 예측에 자주 사용됩니다.  개념이 이해하기 쉽고 직관적이기 때문에 실무에서 많이 사용됩니다. 주식 투자를 하는 분이라면 아마도 이동평균에 대해서 익숙할 것입니다. 

 

이동평균에는 가중치를 어떻게 부여하느냐에 따라서 단순이동평균(Simple Moving Average), 가중이동평균(Weighted Moving Average), 지수이동평균(Exponential Moving Average) 등이 있습니다. 

 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 Window Function 을 사용하여 가중치를 사용하지 않는 (혹은, 모든 값에 동일한 가중치 1을 부여한다고 볼 수도 있는) 

 

(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average) 

(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기

(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simple Moving Average)

 

하는 방법을 소개하겠습니다. 

 

 

PostgreSQL, Greenplum, Simple Moving Average using Window Function

 

 

먼저, 세일즈 날짜와 판매금액의 두 개 칼럼으로 구성된, 예제로 사용할 간단한 시계열 데이터(Time Series Data) 테이블을 만들어보겠습니다.  

 

-- creating a sample table
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
	sale_dt date
	, sale_amt int
) DISTRIBUTED RANDOMLY;

INSERT INTO sales VALUES 
('2021-06-01', 230)
, ('2021-06-02', 235)
, ('2021-06-03', 231)
, ('2021-06-04', 244)
, ('2021-06-05', 202)
, ('2021-06-06', 260)
, ('2021-06-07', 240)
, ('2021-06-08', 235)
, ('2021-06-09', 239)
, ('2021-06-10', 242)
, ('2021-06-11', 244)
, ('2021-06-12', 241)
, ('2021-06-13', 246)
, ('2021-06-14', 247)
, ('2021-06-15', 249)
, ('2021-06-16', 245)
, ('2021-06-17', 242)
, ('2021-06-18', 246)
, ('2021-06-19', 245)
, ('2021-06-20', 190)
, ('2021-06-21', 230)
, ('2021-06-22', 235)
, ('2021-06-23', 231)
, ('2021-06-24', 238)
, ('2021-06-25', 241)
, ('2021-06-26', 245)
, ('2021-06-27', 242)
, ('2021-06-28', 243)
, ('2021-06-29', 240)
, ('2021-06-30', 238);

SELECT * FROM sales ORDER BY sale_dt LIMIT 5;

--sale_dt        sale_amt
--2021-06-01	230
--2021-06-02	235
--2021-06-03	231
--2021-06-04	244
--2021-06-05	202

 

 

(1) 단순이동평균 계산하기 (Calculating a Simple Moving Average) 

 

현재 날짜를 기준으로 2일전~현재날짜 까지 총 3일 기간 동안의 값을 사용하여 단순 이동평균을 구해보겠습니다. 

 

moving average for last 3 days = (Xt + Xt-1 + Xt-2) / 3

 

PostgreSQL 의 9.0 이상의 버전에서는 AVG()와  OVER() 의 Window Function을 사용하여 매우 편리하게 단순이동평균 (Simple Moving Average)을 계산할 수 있습니다. 

 

시계열 데이터는 시간의 순서가 중요하므로 OVER(ORDER BY sale_dt)  에서 먼저 날짜를 기준으로 정렬을 해주어야 합니다.

 

OVER(ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 로 2일전~현재날짜 까지 총 3일 간의 Window 를 대상으로 평균을 계산하는 것을 지정해줍니다. 

 

가령, 아래의 '2021-06-03' 일의 3일 단순이동평균값은 아래와 같이 '2021-06-01', '2021-06-02', '2021-06-03' 일의 3일치 세일즈 판매금액의 평균이 되겠습니다. 

 

* 3일 단순이동평균('2021-06-03') = (230 + 235 + 231) / 3 = 232.0

 

ROUND(avg(), 1) 함수를 사용해서 단순이동평균값에 대해 소수점 첫째자리 반올림을 할 수 있습니다. 그리고 필요 시 단순이동평균 계산할 대상을 조회할 때 WHERE 조건절을 추가할 수도 있습니다. 

 

-- Calculating a Moving Average for last 3 days using Window Function

SELECT 
	sale_dt
	, sale_amt
	, ROUND(
		AVG(sale_amt) 
		OVER(
			ORDER BY sale_dt 
			ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1
		) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;

--sale_dt   sale_amt   avg_sale_amt
--2021-06-01	230	230.0
--2021-06-02	235	232.5
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

날짜를 X 축으로 놓고, Y 축에는 날짜별 (a) 세일즈 금액, (b) 3일 단순이동평균 세일즈 금액 을 시계열 그래프로 나타내서 비교해보면 아래와 같습니다. 예상했던대로 '3일 단순이동평균' 세일즈 금액이 스파이크(spike) 없이 smoothing 되어있음을 확인할 수 있습니다. 

 

아래 코드는 Jupyter Notebook에서 Python 으로 Greenplum DB에 연결(connect)하여, SQL query 를 해온 결과를 Python pandas의 DataFrame으로 만들어서, matplotlib 으로 시계열 그래프를 그려본 것입니다.

(* 참고: Jupyter Notebook에서 PostgreSQL, Greenplum DB connect 하여 데이터 가져오는 방법은 https://rfriend.tistory.com/577, https://rfriend.tistory.com/579 참조)

 

## --- Jupyter Notebook ---

import pandas as pd
import matplotlib.pyplot as plt

## loading ipython, sqlalchemy, spycopg2
%load_ext sql

## Greenplum DB connection
%sql postgresql://dsuser:changeme@localhost:5432/demo
#'Connected: dsuser@demo'


## getting data from Greenplum by DB connection from jupyter notebook
%%sql sam << SELECT 
sale_dt
, sale_amt
, ROUND(
AVG(sale_amt) 
OVER(
ORDER BY sale_dt 
ROWS BETWEEN 2 PRECEDING 
AND CURRENT ROW)
  , 1
  ) AS avg_sale_amt
FROM sales
ORDER BY sale_dt;

# * postgresql://dsuser:***@localhost:5432/demo
#30 rows affected.
#Returning data to local variable sam


## converting to pandas DataFrame
sam_df = sam.DataFrame()


sam_df.head()
#sale_dt	sale_amt	avg_sale_amt
#0	2021-06-01	230	230.0
#1	2021-06-02	235	232.5
#2	2021-06-03	231	232.0
#3	2021-06-04	244	236.7
#4	2021-06-05	202	225.7

## plotting time-series plot
import matplotlib.pyplot as plt

plt.rcParams['figure.figsize'] = [14, 10]
plt.plot(sam_df.sale_dt, sam_df.sale_amt, marker='s', color='r', label='original')
plt.plot(sam_df.sale_dt, sam_df.avg_sale_amt, marker='o', color='b', label='moving average')
plt.title('Simple Moving Average', fontsize=18)
plt.xlabel('Sale Date', fontsize=14)
plt.ylabel('Sale Amount', fontsize=14)
plt.legend(fontsize=12, loc='best')
plt.show()

 

original data vs. simple moving average

 

 

 

 

(2) 처음 이동평균 날짜 모자라는 부분은 NULL 처리하고 단순이동평균 계산하기

 

위의 (1)번에서 '3일 단순이동평균' 값을 계산할 때 시계열 데이터가 시작하는 첫번째와 두번째 날짜에 대해서는 이전 데이터가 존재하지 않기 때문에 '3일치' 데이터가 부족하게 됩니다. (만약 '10일 단순이동평균'을 계산한다고 하면 처음 시작하는 9일치 데이터의 경우 '10일치' 데이터에는 모자라게 되겠지요.) 

 

위의 (1)번에서는 이처럼 '3일치' 데이터가 모자라는 '2021-06-01', '2021-06-02' 일의 경우 '3일치'가 아니라 '1일치', '2일치' 단순이동평균으로 대체 계산해서 값을 채워넣었습니다. 

 

하지만, 필요에 따라서는 '3일치 단순이동평균'이라고 했을 때 이전 데이터가 '3일치'가 안되는 경우에는 단순이동평균을 계산하지 말고 그냥 'NULL' 값으로 처리하고 싶은 경우도 있을 것입니다. 이때 (2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하는 방법, (2-2) LAG(), OVER() window function 을 이용하는 방법의 두 가지를 소개하겠습니다. 

 

 

(2-1) CASE WHEH 과 AVG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법

 

SELECT 
	sale_dt
	, sale_amt
	, CASE WHEN 
		row_number() OVER(ORDER BY sale_dt) >= 3 
		THEN 
			ROUND(
				AVG(sale_amt) 
				OVER(
					ORDER BY sale_dt 
					ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
			, 1)
		ELSE NULL END 
		AS avg_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_sale_amt
--2021-06-01	230	NULL
--2021-06-02	235	NULL
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

 

(2-2) LAG(), OVER() window function을 사용하여 단순이동평균 계산하고, 이동평균계산 날짜 모자라면 NULL 처리하는 방법

 

아래 LAG() 함수를 사용한 방법은 이렇게도 가능하다는 예시를 보여준 것이구요, 위의 (2-1) 과 비교했을 때 'x일 단순이동평균'에서 'x일'이 숫자가 커질 경우 수작업으로 LAG() 함수를 'x일' 날짜만큼 모두 써줘야 하는 수고를 해줘야 하고, 그 와중에 휴먼 에러가 개입될 여지도 있어서 아무래도 위의 (2-1) 방법이 더 나아보입니다. 

 

-- Calculating a Simple Moving Average using LAG() Window Function

SELECT 
	sale_dt
	, sale_amt
	, ROUND(
			(sale_amt::NUMERIC 
				+ LAG(sale_amt::NUMERIC, 1) OVER(ORDER BY sale_dt) 
				+ LAG(sale_amt::NUMERIC, 2) OVER(ORDER BY sale_dt)
			)/3
			, 1) AS avg_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_sale_amt
--2021-06-01	230	NULL
--2021-06-02	235	NULL
--2021-06-03	231	232.0
--2021-06-04	244	236.7
--2021-06-05	202	225.7
--2021-06-06	260	235.3
--2021-06-07	240	234.0
--2021-06-08	235	245.0
--2021-06-09	239	238.0
--2021-06-10	242	238.7
--2021-06-11	244	241.7
--2021-06-12	241	242.3
--2021-06-13	246	243.7
--2021-06-14	247	244.7
--2021-06-15	249	247.3
--2021-06-16	245	247.0
--2021-06-17	242	245.3
--2021-06-18	246	244.3
--2021-06-19	245	244.3
--2021-06-20	190	227.0
--2021-06-21	230	221.7
--2021-06-22	235	218.3
--2021-06-23	231	232.0
--2021-06-24	238	234.7
--2021-06-25	241	236.7
--2021-06-26	245	241.3
--2021-06-27	242	242.7
--2021-06-28	243	243.3
--2021-06-29	240	241.7
--2021-06-30	238	240.3

 

 

 

(3) 누적 단순이동평균 계산하기 (Calculating a Cumulative Simpe Moving Average)

 

처음 시작하는 날짜부터 해서 누적으로 단순이동 평균 (Cumulative Moving Average) 을 계산하고 싶을 때는 아래처럼 AVG(sale_amt) OVER(ORDER BY sale_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 처럼 window 범위를 처음부터 현재까지로 설정해주면 됩니다. 

 

아래 예에서 '2021-06-05'일까지의 누적 단순이동평균 값은 아래와 같이 계산되었습니다. 

 

Cumulative simple moving average('2021-06-05') = (230 + 235 + 231 + 244 + 202) / 5 = 228.4

 

-- Calculating a Cumulative Moving Average
SELECT 
	sale_dt
	, sale_amt
	, ROUND(
		AVG(sale_amt) 
		OVER(
			ORDER BY sale_dt 
			ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
			, 1) AS avg_cum_sale_amt
FROM sales 
ORDER BY sale_dt;

--sale_dt  sale_amt  avg_cum_sale_amt
--2021-06-01	230	230.0
--2021-06-02	235	232.5
--2021-06-03	231	232.0
--2021-06-04	244	235.0
--2021-06-05	202	228.4
--2021-06-06	260	233.7
--2021-06-07	240	234.6
--2021-06-08	235	234.6
--2021-06-09	239	235.1
--2021-06-10	242	235.8
--2021-06-11	244	236.5
--2021-06-12	241	236.9
--2021-06-13	246	237.6
--2021-06-14	247	238.3
--2021-06-15	249	239.0
--2021-06-16	245	239.4
--2021-06-17	242	239.5
--2021-06-18	246	239.9
--2021-06-19	245	240.2
--2021-06-20	190	237.7
--2021-06-21	230	237.3
--2021-06-22	235	237.2
--2021-06-23	231	236.9
--2021-06-24	238	237.0
--2021-06-25	241	237.1
--2021-06-26	245	237.4
--2021-06-27	242	237.6
--2021-06-28	243	237.8
--2021-06-29	240	237.9
--2021-06-30	238	237.9

 

 

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

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

 

728x90
반응형
Posted by Rfriend
,

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

 fals

  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
반응형
Posted by Rfriend
,

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

----------

2

1

1


-- 분자를 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
반응형
Posted by Rfriend
,

지난번 포스팅에서는 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
반응형
Posted by Rfriend
,

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
반응형
Posted by Rfriend
,

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


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


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



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



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


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


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


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


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


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


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

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


[ R tryCatch() syntax ]



tryCatch(

    expr = {

        # Your code here...

        # ...

    },

    error = function(e)

        # (Optional)

        # Do this if an error is caught...

    },

    warning = function(w){

        # (Optional)

        # Do this if an warning is caught...

    },

    finally = {

        # (Optional)

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

    }

)


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



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

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

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

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

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


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



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

-- PL/R on Greenplum, PostgreSQL DB

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

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


drop table if exists tbl_sample;

create table tbl_sample (

grp varchar(6) not null

, id integer

, x1 integer

, x2 integer

);


insert into tbl_sample values 

('a', 1, 5, 10)

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

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

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

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

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


select * from tbl_sample order by id;


 




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


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


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

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

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

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

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

하도록 짠 코드입니다. 


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



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

-- define PL/R UDF

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

create or replace function plr_divide(

x1 int[]

, x2 int[]

) 

returns setof text  -- float8

as

$$

divide_tryCatch <- function(x_numerator, x_denominator){

  tryCatch(

    expr = {

      if (x_denominator == 0) {

        message("Denominator should not be Zero")

        return("Denominator should not be Zero")

        } else {

          result <- x_numerator / x_denominator

          return(result)

        }

      }, 

    error = function(e) {

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

      print(e)

      return(e[1])

      }, 

    warning = function(w){

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

          print(w)

          return(w[1])

          }, 

    finally = {

      message("All done, quitting.")

      }

    )

}

result <- divide_tryCatch(x1, x2)

return(result)

$$ language 'plr';



-- execute PL/R

select 

grp

, unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide(x1_arr, x2_arr) as divided 

from (

select 

grp

, array_agg(x1::int) as x1_arr

, array_agg(x2::int) as x2_arr

from tbl_sample

group by grp

) a;






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


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



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

-- execute PL/R UDF

select 

unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide(x1_arr, x2_arr) as divided 

from (

select 

array[1] as x1_arr

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

) a;






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


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


error = function(e) {

   # 실행할 코드

    return (e[1])

 } 


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



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

-- define PL/R UDF

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

create or replace function plr_divide2(

x1 int[]

, x2 text[]

) 

returns setof text

as

$$

divide_tryCatch <- function(x_numerator, x_denominator){

  tryCatch(

    expr = {

      if (x_denominator == 0) {

        message("Denominator should not be Zero")

        return("Denominator should not be Zero")

        } else {

          result <- x_numerator / x_denominator

          return(result)

          }

      }, 

    error = function(e) {

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

      #print(e)

      return(e[1])

      }

}

result <- divide_tryCatch(x1, x2)

return(result)

$$ language 'plr';



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

select 

unnest(x1_arr) as x1

, unnest(x2_arr) as x2

, plr_divide2(x1_arr, x2_arr) as divided 

from (

select 

array[1] as x1_arr

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

) a;





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

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



728x90
반응형
Posted by Rfriend
,

지난번 포스팅에서는 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
반응형
Posted by Rfriend
,