sqlite3 to hdfs with hive

sqlite3 에서 사용하던 이력 데이터의 용량이 너무 커져서 하둡 (hive)으로 옮기는 과정을 다룬다.
hive 저장 포맷에 대한 간략한 비교도 포함한다.

목차

• sqlite3 to hdfs ( hadoop file system )
• hdfs to hive
• performance as hive format

sqlite3 에서 hdfs 로 파일 저장하기

sqlite3 -csv big.db “select * from big;” | hadoop fs -put - /user/me/big.csv

**
결과를 local 에 csv 로 저장 할 공간이 없어서, pipe 형식으로 하둡으로 바로 저장했다.
질의 결과를 표준 출력 ( stdout ) 으로 보낼 수 있다면 응용 될 수 있는 방법이다.

hdfs 에서 hive 로 로딩 하기

hortonworks 의 amberi 도구를 사용하면, Hive View 를 만들어서 Upload Table 메뉴를 이용한다.
미리보기 모드에서 파일 포멧, 테이블/컬럼 이름 등을 지정하면 심플하다.

sql 기반의 명령어는 다음과 같다.
테이블 스키마를 먼저 생성하고, csv 파일을 로딩한다.
CREATE TABLE big_text (Column01 int, Column02 string…) STORED AS TEXT;
LOAD DATA INPATH ‘/user/me/big.csv’ [OVERWRITE] INTO TABLE big_text;

다른 형태의 포맷으로 테이블을 만드는 과정이다.
CREATE TABLE big_orc STORED AS ORC AS SELECT * FROM big_text;

**
HIVE는 다양한 포맷을 지원한다.
sequence, text, orc, rc, parquet ..
성능 관점에서 보면 질의 형태에 따라 row 와 column 기반의 포맷에 주의한다.

특정 컬럼  filter에 있어서 text와 orc 포맷 테이블의 질의하기
>> column 기반의 orc에 이점이 있다.

hive> select count(*) from big_orc where sdate = '20160104';
Query ID = root_20161107130507_53898e98-2f58-4ca4-a4c9-ca03a527c8de
Total jobs = 1
Launching Job 1 out of 1


Status: Running (Executing on YARN cluster with App id application_1478242837204_0017)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED     20         20        0        0       0       0
Reducer 2 ......   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 9.08 s  
--------------------------------------------------------------------------------
OK
673374
Time taken: 9.762 seconds, Fetched: 1 row(s)
hive> select count(*) from big_text where sdate = '20160104';
Query ID = root_20161107130524_c2dcdccb-63c1-4502-a305-6a681fa1ea9e
Total jobs = 1
Launching Job 1 out of 1


Status: Running (Executing on YARN cluster with App id application_1478242837204_0017)

--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED     20         20        0        0       0       0
Reducer 2 ......   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 02/02  [==========================>>] 100%  ELAPSED TIME: 38.58 s  
--------------------------------------------------------------------------------
OK
673374
Time taken: 39.234 seconds, Fetched: 1 row(s)

참고

HIVE 가이드 페이지

hive architecture figure - referred to apache hive homepage

Read More

맥OS <-> 오라클 접속

MacOS에서 오라클(Oracle)에 접속하는 방법을 소개한다.

• 파이썬 cx_Oracle 인터페이스를 통한 연결
• SQLPLUS 도구를 통한 연결

먼저 파이썬 cx_Oracle 인터페이스를 통한 접근 방법이다.

오라클에서 다음 두 가지 파일을 다운 받아서 압축을 푼다.
unzip instantclient-basic-macos.x64-11.2.0.4.0.zip
unzip instantclient-sdk-macos.x64-11.2.0.4.0.zip

압축을 풀면 다음 디랙토리가 생성된다.
instantclient_11_2

환경 변수를 등록한다.
export ORACLE_HOME=$(pwd)/instantclient_11_2
export DYLD_LIBRARY_PATH=$ORACLE_HOME:$DYLD_LIBRARY_PATH

cx_Oracle을 설치한다.
pip install cx_Oracle

**
ORACLE_HOME에 풀려 있는 파일을 참조하여 설치된다.
cx_Oracle이 호출 될때 필요한 라이브러리를 DYLD_LIBRARY_PATH에서 참조한다.

수행 예제 코드이다.
<code>
import cx_Oracle as cx

dsn = cx.makedsn(HOST, PORT, SID)
dbc = cx.connect('ecpadmin', 'ecpadmin', dsn)
print('ORACLE VERSION: ', dbc.version)
csr = dbc.cursor()
csr.execute('SELECT systimestamp FROM dual')
print('TIME: ', csr.fetchone())
dbc.close()

<output>
ORACLE VERSION:  11.2.0.2.0
TIME:  (datetime.datetime(2016, 10, 12, 12, 40, 46, 653846),)


다음은 SQLPLUS 도구를 통해서 접속하는 방법이다.

오라클에서 다음 파일을 다운 받아서 압축을 푼다.
unzip instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

압축을 풀면 다음 디랙토리가 생성된다.
instantclient_11_2

환경 변수를 등록한다.
export PATH=$ORACLE_HOME:$PATH

접속 명령 예제이다.
sqlplu <user>/<password>@<host>/<sid>


**
추가로 한글 데이터 비정상 출력 (ex, ????) 해결 방법이다.

먼저 서버의 문자셋을 확인한다.
<sql>
SELECT *
  FROM sys.props$
 WHERE name = 'NLS_CHARACTERSET';
<output>
NLS_CHARACTERSET
AL32UTF8
Character set

오라클 9i 부터는 UTF8 대신에 AL32UTF8를 사용하고 있고,
NLS_LANG 환경 변수 수정을 통해서 해결 할 수 있다.

터미널 환경에서 적용하는 방법이다.
export NLS_LANG=.AL32UTF8

파이썬 스크립트에서 적용하는 방법이다.
import os 
os.environ["NLS_LANG"] = ".AL32UTF8"

참조

OCI (Oracle Call Interface)
오라클에서 제공하는 C 언어로 만든 인터페이스다.
편리하고 높은 성능과 안정성을 제공한다.

라이브러리 경로
On UNIX platforms you must ensure that LIBPATH environment variable is set properly to pick up the shared libraries at runtime. (UNIX gurus will understand here that LIBPATH actually translates to LD_LIBRARY_PATH on Solaris and Linux, SHLIB_PATH on HP-UX, DYLD_LIBRARY_PATH on Mac OS X, and stays as LIBPATH on AIX).

테스트 환경
MacOS Sierra
Python 3.5.1

Read More

오라클 procedure 수행 로그 남기기 - 예제 코드

오라클(oracle) 환경에서 프로시저(procedure) 작업 수행 시 로그 남기는 코드이다.
로그를 남기기 위한 procedure와 수행 코드를 넣기 위한 procedure 폼 두 가지이다.

사용 방법은 다음과 같다.

작업 수행 procedure의 — start script 와 — end script 사이에 원하는 수행 스크립트를 작성한다.
작업 수행 procedure를 이름만 바꾸고 복제해서 사용하면 로그에서 자동으로 구분할 수 있도록 했다.

작업 수행 procedure 예제 코드,
CREATE OR REPLACE PROCEDURE job_proc01
IS
   err_code   VARCHAR (1024);
   err_msg    VARCHAR (1024);
   job_id     VARCHAR (1024);
   job_nm     VARCHAR (1024);
BEGIN
   job_id := TO_CHAR (SYSTIMESTAMP, 'YYYYMMDDHH24MISS.FF');
   job_nm := $$PLSQL_UNIT;
   etl_job_hist_logger (job_id,
                        job_nm,
                        'N/A',
                        'Started',
                        'N/A');

   -- START SCRIPT


   -- END SCRIPT
   etl_job_hist_logger (job_id,
                        job_nm,
                        'N/A',
                        'Ended',
                        'N/A');
EXCEPTION
   WHEN OTHERS
   THEN
      err_code := SQLCODE;
      err_msg := SUBSTR (SQLERRM, 1, 200);
      etl_job_hist_logger (job_id,
                           job_nm,
                           'N/A',
                           'Fail',
                           err_code || '::' || err_msg);
END;
/

로그 수행 procedure 예제 코드,
CREATE OR REPLACE PROCEDURE etl_job_hist_logger (xJobId        IN STRING,
                                                 xJobNm        IN STRING,
                                                 xJobDesc      IN STRING,
                                                 xJobStat      IN STRING,
                                                 xJobFailMsg   IN STRING)
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE ('--------------------------------------');
   DBMS_OUTPUT.PUT_LINE ('> Job ID: ' || xJobId);
   DBMS_OUTPUT.PUT_LINE ('> Job Name: ' || xJobNm);
   DBMS_OUTPUT.PUT_LINE ('> Job Description: ' || xJobDesc);
   DBMS_OUTPUT.PUT_LINE ('> Job Status: ' || xJobStat);

   IF xJobStat IN ('Fail')
   THEN
      DBMS_OUTPUT.PUT_LINE ('> Job Fail Message: ' || xJobFailMsg);
   END IF;

   DBMS_OUTPUT.PUT_LINE ('--------------------------------------');

   INSERT INTO etl_job_hist (job_id,
                             job_nm,
                             job_desc,
                             job_stat,
                             job_fail_msg)
        VALUES (xJobId,
                xJobNm,
                xJobDesc,
                xJobStat,
                xJobFailMsg);
END;
/


**
기타 참조 사항 정리

토드 SQL 코드 정리 단축 코드 (format)
format -> command + shift + F

토드 문장 수행 단축 코드
run one statement -> command + enter
run all statements -> command + shift + enter

맥북 환경에서는 SQL Developer 보다 Toad가 안정적이고 직관적이었다.
앱스토어에서 받은 토드는 MongoDB, MySQL, PostgreSQL도 기본으로 지원한다.

DBMS_OUTPUT 설정 환경 변수 (for sqlplus)
SET SERVEROUTPUT ON;

토드에서 DBMS_OUTPUT은 AutoCommit OFF에서 확인 할 수 있었다.

Read More

테이블 데이터 샘플링 방법

테이블에서 샘플을 추출하는 두 가지 쿼리를 다룬다.

가. ORDER BY random() LIMIT n

정확한 샘플 개수를 지정할 수 있다.
정렬(sort) 수행이 발생한다.

예상 비용
cook=> explain SELECT * FROM sample_1000 ORDER BY random() LIMIT 10;
                                 QUERY PLAN                                
----------------------------------------------------------------------------
 Limit  (cost=44.01..44.03 rows=10 width=686)
   ->  Sort  (cost=44.01..44.89 rows=352 width=686)
         Sort Key: (random())
         ->  Seq Scan on sample_1000  (cost=0.00..36.40 rows=352 width=686)
(4 rows)


나. WHERE  random() <= x (%) LIMIT n

샘플의 개수가 지정한 %를 기준으로 확률 분포를 가진다.
전체 카운트를 비교해서 적절한 샘플 x(%) 지정이 필요하다.
성능이 빠르다.

예상 비용
cook=> explain SELECT * FROM sample_1000 WHERE random() < 0.1 LIMIT 10;
                              QUERY PLAN                            
----------------------------------------------------------------------
 Limit  (cost=0.00..3.19 rows=10 width=686)
   ->  Seq Scan on sample_1000  (cost=0.00..37.28 rows=117 width=686)
         Filter: (random() < 0.1::double precision)
(3 rows)

Read More

MySQL 참조 가이드

본문은 MySQL 참조를 위한 메모이다.

도움말 보기
help
아이템(item) 보기
help <item>

**
포그리(postgresql)에서는  pg_hba.conf 파일에서 하는 접근 제어를
메타 테이블을 통해서 설정한다.
schema와 database를 혼용하여 사용한다.

테이블 명세서 뽑기
SELECT
    table_name '테이블이름',
    ordinal_position '속성순번',
    column_name '속성명',
    data_type '데이터타입',
    column_type '필드타입',
    column_key '키종류',
    is_nullable '널허용여부',
    extra '자동여부',
    column_default '속성기본값',
    column_comment '속성설명'
FROM
    information_schema.columns
WHERE
    table_schema = '<database>'
ORDER BY table_name , ordinal_position;

BINARY 코드 비교
binary는 blob 블럭으로 출력 됨으로, Hex 코드로 변환해서 비교하면 된다.
hex(<binary_column>)

Read More

piwik 데이터 모델 요약

오픈 소스 기반의 웹분석 도구인 PIWIK의 데이터 모델을 요약한다.

1. 데이터 생성 과정을 다룬다.
2. 테이블의 용도 및 필드명을 설명한다.
3. 테이블 명세서 시트를 기록한다.

첫번째 데이터 생성 과정이다.

가. 로그 데이터 수집 (Log Data)
나. 보관 처리 (Archiving Process)
다. 보관 데이터 생성 (Archive Data)

**
데이터 수집 대상은 4가지로 요약할 수 있다.
 - visits(방문), action types(행위), conversions(전환), ecommerce items(전자상거래)
조회 효율을 높이기 위해서 패턴 질의에 대한 보관(요약:summary) 데이터를 만든다.

Read More

파이썬 PostgreSQL 라이브러리 py-postgresql 퀵 가이드

PostgreSQL을 사용 위해서 psycopg2 라이브러리 구성시 pg_config가 필요한 이유로 패키지를 추가 설치하는 불편함이 있었다.

본문은 (신속하고 편리한 사용을 위해)  py-postgresql 라이브러리로 작업하는 샘플을 요약한다.

py-postgresql 사용

간단한 설치 방법이다.

pip install py-postgresql

문서에서 제공되는 메인 예제이다.

import postgresql



db = postgresql.open("pq://user:password@host/name_of_database")


db.execute("CREATE TABLE emp (emp_name text PRIMARY KEY, emp_salary numeric)")



# Create the statements.


make_emp = db.prepare("INSERT INTO emp VALUES ($1, $2)")


raise_emp = db.prepare("UPDATE emp SET emp_salary = emp_salary + $2 WHERE emp_name = $1")


get_emp_with_salary_lt = db.prepare("SELECT emp_name FROM emp WHERE emp_salay < $1")



# Create some employees, but do it in a transaction--all or nothing.


with db.xact():


    make_emp("John Doe", "150,000")


    make_emp("Jane Doe", "150,000")


    make_emp("Andrew Doe", "55,000")


    make_emp("Susan Doe", "60,000")



# Give some raises


with db.xact():


    for row in get_emp_with_salary_lt("125,000"):


    print(row["emp_name"])


    raise_emp(row["emp_name"], "10,000")

동적 쿼리가 필요한 경우는 prepare 대신에 다음을 사용할 수 있다.

for row in db.query.rows(<query>):

    #row는 dict 형태로 사용됨

    pass

Pandas와 함께 사용하는 방법이다.

from sqlalchemy import create_engine

db = create_engine(“postgresql+pypostgresql://user:password@host/name_of_database”)

참고 자료

SQLAlchemy ORM (Object Relational Mapper)

 - http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html

py-postgresql 라이브러리

 - http://pythonhosted.org/py-postgresql/index.html


커버 사진

Read More

SQLite3에 대한 이점 및 사용 예제

파이썬에 기본으로 탑재되어 있는 SQLite3 에 대해서 다룬다.

SQLite는 가벼운 디스크 기반의 데이터 베이스를 제공하는 C 라이브러리다.
어플리케이션 내부 데이터 저장소로 다양하게 활용되고, 사용이 간편하고 가벼워서 프로토타입용으로도 자주 쓰인다.

본문은 pandas + SQLite3에 대한 간략한 사용 예제를 담는다.

1. CSV 자료를 판다곰의 데이터프레임(DataFrame)으로 읽는다.

df = pd.read_csv('gov_loc.csv',skiprows=1,index_col=['lev1','lev2','lev3'],
                 names=['lev1','lev2','lev3','nx','ny','lon','lat'])

* 샘플 데이터는 기상청 주소/좌표 매핑 데이터를 이용했다.

lev1	lev2	lev3	nx	ny	lon	lat
서울특별시	None	None	60	127	126.980008	37.563569
서울특별시	종로구	None	60	127	126.981642	37.570378
서울특별시	종로구	청운효자동	60	127	126.970652	37.584137
서울특별시	종로구	사직동	60	127	126.970956	37.573269
서울특별시	종로구	삼청동	60	127	126.983978	37.582425
서울특별시	종로구	부암동	60	127	126.966444	37.589856

...

2. 데이터프레임을 SQLite DB로 저장한다.

conn = sqlite3.connect('gov.db')
df.to_sql(name='loc_map_book',con=conn,index=True,if_exists='replace')

3. 메타 데이터를 이용해 스키마를 확인 하자.

query_ddl = 'SELECT sql FROM sqlite_master WHERE tbl_name = "loc_map_book";'
for i in pd.read_sql(query_ddl ,con=conn).get_values():
    print (i[0])
OUT>>
CREATE TABLE "loc_map_book" (
"lev1" TEXT,
  "lev2" TEXT,
  "lev3" TEXT,
  "nx" INTEGER,
  "ny" INTEGER,
  "lon" REAL,
  "lat" REAL
)
CREATE INDEX "ix_loc_map_book_lev1_lev2_lev3"ON "loc_map_book" ("lev1","lev2","lev3")

4. 쿼리 플랜을 통해서 인덱스가 잘 동작하는지 알아 보자.

query = 'explain query plan ' + \
        'select * from loc_map_book where lev1="{0}";'.format('서울특별시')
for i in conn.execute(query):
    print(i)
OUT>>
(0, 0, 0, 'SEARCH TABLE loc_map_book USING INDEX ix_loc_map_book_lev1_lev2_lev3 (lev1=?)')


DB 업무를 먼저 시작한 이유로, 사소한 데이터도 대용량 DB와 연결을 지었는데,
로컬 파일 DB를 활용하는 것은 개발에 매우 큰 이점이 있었다.
단일 파일로 관리 됨으로 프로그램 복제가 쉬웠고, DB 구성/관리 소요비용이 없었다.

참조:
파이썬에서 대용량 자료를 핸들링 하는 방법에 대한 글
 - SQLite가 생각보다 큰 자료에 대한 핸들링도 가능한 것으로 보인다.
https://www.reddit.com/r/Python/comments/3wa22v/120gb_csv_is_this_something_i_can_handle_in_python/

Read More

데이터베이스(Postgresql)의 3가지 함수 형태 요약

Postgresql에서 정의할 수 있는 3가지 함수 형태이다.

• IMMUTABLE (불변의)
• 같은 입력값에 대해서는 항상 같은 결과값이 출력된다.
• anyway,anytime 같은 값을 보장하며 산술 연산들이 여기에 해당된다.
• 옵티마이저가 미리 계산해도 무방하다.
• STABLE (정적인)
• 같은 트랜잭션 내에서는 같은 입력값에 대해서 같은 결과를 출력한다.
• current_timestamp와 같이 같은 트랜잭션 내에서 결과가 동일한 경우를 포함한다.
• 옵티마이저 입장에서는 여러번의 콜을 한번으로 줄일 수 있다.
• VOLATILE (휘발성의)
• 매번 함수를 호출한다.
• random(), curval() 함수처럼 미리 확신할 수 없다.
• 대량의 row에 매번 적용되지 않도록 고민해야 한다.

트랜잭션이라는 논리적인 구성을 가지는 DB에서는 성능이나 편의성을 위해 최소한으로 필요한 요소들로 보인다.

Read More

데이터베이스 DEAD LOCK에 대한 간략한 시연

DW(Data Warehouse)를 위한 분산 데이터베이스인 그린플럼(Greenplum)을 다루던 시절에,
코끼리(pgadmin) 창 2개를 열고 시연하던, 트랜잭션(Transaction)과 락(Lock) 매커니즘으로 인한 DEAD LOCK 발생에 대해서 구현해 보았다.

테스트 과정을 간략히 설명하면,
* 시연 트랜잭션 - INSERT + TRUNCATE 수행

1. 2개의 트랜잭션이 들어 온다.
    => INSERT (ROW EXCLUSIVE)는 동시 수행이 수행된다.
2. 1개의 트랜잭션에 INSERT가 끝나고 TRUNCATE가 들어온다.
    => INSERT (ROW EXCLUSIVE) <-> TRUNCATE (ACCESS EXCLUSIVE)는 상호 배재 관계 임으로, 다른 트랜잭션에 수행중인 INSERT가 끝나길 기다린다.
3. 나머지 1개의 트랜잭션에 INSERT가 끝나고 TRUNCATE가 들어온다.
    => 서로 다른 트랜잭션이 끝나길 기다리지만, 영원히 기다려야한다.
4. 코끼리는 DEAD LOCK을 발견 후, 메세지를 남기고 끊어 버린다.

참고 : http://www.postgresql.org/docs/9.4/static/explicit-locking.html

테스트 코드는 다름과 같다.

준비 작업

In [1]:

# 필요한 모듈 로딩
from sqlalchemy import create_engine
from multiprocessing import Pool

In [2]:

def insert_truncate_query(i):
    # 하나의 트랜젝션에 상호배제되는 LOCK을 유도하기 위한 함수
    engine = create_engine('postgresql://chef:fork@cook:5432/cook')
    connection = engine.connect()
    # 트랜잭션 구문 시작
    trans = connection.begin()
    try:
        # 세션 프로세스 아이디 출력
        print(i,connection.execute("select pg_backend_pid() as pid;").fetchall()[0][0])
        # LOCK MODE - ROW EXCLUSIVE 
        connection.execute("insert into target select * from source limit 200000;")
        # LOCK MODE - ACCESS EXCLUSIVE
        connection.execute("truncate target;")
        trans.commit()
    except:
        trans.rollback()
        raise

테스트1 - 단일 수행에 대한 결과

In [3]:

L = ['job I']
with Pool(processes=2) as pool:
    out = pool.map(insert_truncate_query,L)

job I 23183

테스트2 - 동시 수행에 대한 결과

In [4]:

L = ['job I','job II']
with Pool(processes=2) as pool:
    out = pool.map(insert_truncate_query,L)

job II 23196
job I 23197

---------------------------------------------------------------------------
RemoteTraceback                           Traceback (most recent call last)
RemoteTraceback: 
"""
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.extensions.TransactionRollbackError: deadlock detected
DETAIL:  Process 23196 waits for AccessExclusiveLock on relation 6099669 of database 16384; blocked by process 23197.
Process 23197 waits for AccessExclusiveLock on relation 6099669 of database 16384; blocked by process 23196.
HINT:  See server log for query details.

번역하자면, Job II는 target 테이블에 Truncate를 하기 위해 기다리는데, Job I이 막고 있다. 반대로 Job I의 상황도 같다.

보통 분산 디비의 경우 기술적인 복잡성을 이유로 LOCK 모드와 같은 다양한 제약사항이 있다.
비 오라클 DBA 분들에게 제약사항이란, DB를 좀 더 깊이 이해할 수 있는 계기가 아닐까? 희망적인 메세지를 남겨본다.

Read More

PostgreSQL의 대형 속성 저장 기술 (TOAST) 요약

PostgreSQL의 데이터 용량 관리에 대해서,

최근에 작업한 80만건 정도의 웹크라울링 데이터의 작업 중에,

흥미로운 이야기가 있어서 소개 합니다.

PostgreSQL 9.4 버전에서 메타(META)과 함께 HTML 페이지를 보관 할때,

* DB 사이즈가 30G

* 덤프(pg_dump)의 압축레벨 1로 압축 파일을 생성 했을때 사이즈가 22GB

* 압축을 풀었을때 파일 사이즈가 90G

코끼리는 기본적으로 가변 타입에서 8k를 넘는 문자열을 압축해서 보관하고,

관련 기술은 TOAST라 불립니다.

TOAST(대형 속성 저장 기술:The Oversized-Attribute Storage Technique)

 - 참고자료 : http://www.postgresql.org/docs/9.4/static/storage-toast.html

 - 요약

 * 8kB가 넘는 데이터에 대해서 TOAST 적용됨
 * 성능에 주안을 둔 LZ 압축 기술 사용
 * 한개의 오브젝트가 논리적으로 1GB까지 지원
 * (주테이블과 TOAST 테이블이 분리되어 있음으로) TOAST 테이블 연관 속성을 조회 하지 않을때는 조회 성능에 이슈가 없음

백문이 불여일견이라, DB 관리자 분들을 위해서 조회 쿼리와 함께 내용을 정리해 봅니다.

순수하게 주테이블에 대한 사이즈를 조사합니다.

cook=> select pg_size_pretty(pg_relation_size('web_crawling_news'));

 pg_size_pretty 

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

 210 MB

(1 row)

=> TOAST에 해당되는 속성이 없을 때는, 매우 빠른 조회가 가능합니다.

INDEX나 TOAST 같은 연관 테이블을 합산해서 조사합니다.

cook=> select pg_size_pretty(pg_total_relation_size('web_crawling_news'));

 pg_size_pretty 

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

 30 GB

(1 row)

주테이블과 연관된 TOAST 테이블을 조사합니다.

cook=> select relname from pg_class where oid in ( select reltoastrelid from pg_class where relname = 'web_crawling_news');

     relname      

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

 pg_toast_3419091

(1 row)

TOAST 테이블의 사이즈를 조사합니다.

cook=# select pg_size_pretty(pg_relation_size('pg_toast.pg_toast_3419091'));

 pg_size_pretty 

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

 30 GB

(1 row)

TOAST 테이블의 내부 속성들 입니다.

cook=> \d+ pg_toast.pg_toast_3419091

TOAST table "pg_toast.pg_toast_3419091"

   Column   |  Type   | Storage 

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

 chunk_id   | oid     | plain

 chunk_seq  | integer | plain

 chunk_data | bytea   | plain

점심을 패스해서 그런지 TOAST 생각이 간절 하네요.

Read More

단어빈도사전으로 워드클라우드 그리기

요약

• 미리 준비한 단어 빈도 사전을 통해서, 워드클라우드를 그린다.
• 참고 출처 : http://spartanideas.msu.edu/2014/11/28/turn-your-twitter-timeline-into-a-word-cloud-using-python/

본문

In [1]:

# 노트에 그래프를 출력하기 위해서,
%pylab inline

Populating the interactive namespace from numpy and matplotlib

1. 단어 빈도 사전에서 특정 관련 단어 가지고 오기

몇 가지 패턴으로 뽑아낸 명사구에서 "자전거"가 포함된 빈도 사전을 가지고 작업

In [2]:

# 라이브러리 로딩
from sqlalchemy import create_engine
import pandas as pd

# 디비 조회
conn_info = 'postgresql://user:password@host:port/database'
e = create_engine(conn_info)
q = 'SELECT * FROM pandas.word_freq_bike;'
df = pd.read_sql(q,e)

# 빈도수가 높은 상위 150개의 자료만 뽑기
words = df.sort('freq',ascending=False).head(150)[ ['word','freq'] ].values

빈도 사전 구조

In [3]:

words[0:5]

Out[3]:

array([['자전거', 9956],
       ['자전거도로', 1008],
       ['자전거전용도로', 313],
       ['자전거길', 284],
       ['전기자전거', 239]], dtype=object)

2. 단어 빈도 사전으로 워드클라우드(WordCloud) 그리기

In [4]:

from wordcloud import WordCloud, STOPWORDS

apple_mask = imread('bikelogo.gif', 0)

figure(figsize=(12,8))
wordcloud = WordCloud(font_path='fonts/baedal.ttf',
                      stopwords=STOPWORDS,
                      background_color='white',
                      width=1800,
                      height=1400,
                      mask=apple_mask
            ).generate_from_frequencies(words)
 
imshow(wordcloud)
axis("off")
show()

Out[4]:

Read More

DB 샘플 데이터를 가지고 Elasticsearch Engine 기능 확인

요약

• DB 데이터에 대한 효과적인 Text searching을 위한 보조 도구로써 솔루션의 기능을 확인한다.

본문

1. Elasticsearch 환경 구성

• 사이트 : https://www.elastic.co
• CentOS 6.x에서 RPM 소스 설치 테스트함
• Single Node일 경우, replica 0으로 세팅해야, Yellow 알람 발생 방지 (아키 구조적으로 생각할 것)

**

단일 서버에서 테스트 할 때, elasticsearch.yml 파일을 수정 방법이다.

# 외부 접근을 위한 수정 항목

network.host: <네트워크 인터페이스 주소>

http.port: 9200

# 노드 1개로 운영 시, "경고" 방지를 위한 복제본 0 설정 항목

index.number_of_replicas: 0

2. Elasticsearch Monitoring Tool.

• 사이트 : http://www.elastichq.org
• 플러그인 설치 : $ELASTICSEARCH_BIN/plugin -install royrusso/elasticsearch-HQ
• 접속 방법 : http://domain:port/_plugin/hq/

3. Elasticsearch의 python API

• Ref. : https://www.elastic.co/guide/en/elasticsearch/client/python-api/current/index.html

4. Elasticsearch와 "Postgresql의 Like 검색"의 성능 차이 확인 I (준비 작업)

코끼리에서 샘플 데이터 가지고 오기

In [1]:

# 자주 사용됨으로 미리 정의
conn_info = 'postgresql://user:password@host:port/database'

In [2]:

# 모듈 로딩
import pandas as pd
from sqlalchemy import create_engine
import json

# 샘플로 만들어둔 뉴스 문서 포멧의 10,000개의 문서들
e = create_engine(conn_info)
query = """
SELECT row_number() over () as id,* FROM blogger.elasticsearch_sample_data LIMIT 10000;
"""
%time df = pd.read_sql(query,e)

CPU times: user 531 ms, sys: 132 ms, total: 663 ms
Wall time: 1.38 s

Elasticsearch로 넣기 위한 Json(with records type)으로 변환

In [3]:

# row_number()를 사용하지 않을때, Pandas에서 id 생성하기
#df['no'] = [ x+1 for x in df.index ]

# String to Json(Records Type) Format
%time json_rec = json.loads(df.to_json(orient = "records"))

CPU times: user 3.18 s, sys: 566 ms, total: 3.75 s
Wall time: 3.74 s

Elasticsearch에 인덱싱하기

In [4]:

# Elasticsearch 모듈 로딩
from elasticsearch import Elasticsearch
es = Elasticsearch()

# 사용할 이름 정의
index_name = 'psql_to_es'
doc_type_name = 'psql_entity'

# Json 형태의 문서(doc)를 Elasticsearch로 넣기.
def json_to_es(json_rec):
    for doc in json_rec:
        es.index(index=index_name, doc_type=doc_type_name, id=doc['id'], body=doc)
%time json_to_es(json_rec)

CPU times: user 16.5 s, sys: 842 ms, total: 17.3 s
Wall time: 54.8 s

In [5]:

# 샘플 데이터 확인 (추억의 조던 23번으로,)
es.get(index=index_name, doc_type=doc_type_name, id = 23)

Out[5]:

{'_id': '23',
 '_index': 'psql_to_es',
 '_source': {'comment': 152,
  'content': '연일 때아닌 폭염이 기승입니다. 오늘도 불볕더위가 기승을 부리겠는데요. 현재 강원과 전남, 영남 대부분지역으로 폭염주의보지역이 더 확대된 가운데 영월과 대구가 34도까지 오르겠고, 서울도 30도까지 올라서면서 올해들어 가장 덥겠습니다. 당분간 때 이른 여름 더위는 계속되겠는데요, 서울의 경우 이번 주 내내 30도 안팎의 기온을 보이면서 예년 기온을 크게 웃돌겠습니다. ...',
  'ddate': 1432598400000,
  'dtime': 1432616220000,
  'gdate': '20150526',
  'id': 23,
  'link': '-------------',
  'section': '문화',
  'source': 'YTNTV',
  'title': "[날씨] 오늘도 '폭염'...어제보다 더 더워"},
 '_type': 'psql_entity',
 '_version': 2,
 'found': True}

4. Elasticsearch와 "Postgresql의 Like 검색"의 성능 차이 확인 II (비교하기)

Elasticsearch에서 단어 조회 성능 확인

In [6]:

def find_terms_es():
    res = es.search(index=index_name, body={"query": {"term": {'content':'부동산'}}})
    return res
# 성능확인
%time out1 = find_terms_es()

CPU times: user 4 ms, sys: 3 ms, total: 7 ms
Wall time: 10.8 ms

In [7]:

# 수집 데이터 확인
print("Hits %d :" % out1['hits']['total'])
for hit in out1['hits']['hits'][0:2]:
    print("%(gdate)s %(source)s: %(title)s" % hit["_source"])

Hits 136 :
20150502 이데일리: `14년래 최악 경영난` 맥도날드, 회생전략 내놓는다
20150523 TV조선: [뉴스특보] 국내 금융회사들 해외 고가빌딩 매입나서

postgresql에서 Like 검색을 통한 성능 확인

In [8]:

def find_terms_sql():
    e = create_engine(conn_info)
    query = """
    SELECT gdate,source,title FROM blogger.elasticsearch_sample_data WHERE content like '%%부동산%%'
    """
    return pd.read_sql(query,e)
# 성능확인
%time out2 = find_terms_sql()

CPU times: user 12 ms, sys: 2 ms, total: 14 ms
Wall time: 655 ms

In [9]:

# 수집 데이터 확인
print('Hits %d :' % out2.count()[0])
out2.head(2)

Hits 238 :

Out[9]:

	gdate	source	title
0	20150526	연합뉴스	제일모직-삼성물산 합병결의…삼성그룹 재편 가속(종합2보)
1	20150526	아시아경제	제일모직-삼성물산, 9월1일자로 합병…합병사명은 '삼성물산' (상보)

덧 붙임말

• 결과 건수가 다른 부분은 있지만, 기대만큼의 성능 개선 부분이 확인 된다.
• 다음 번에는 자연어 처리 - 형태소 분석기를 붙여서, 완성도를 높인 자료를 만들어 보겠다.

비교 (사이즈, 응답시간) 결과 보기

	Postgresql Like 검색	Elasticsearch 검색
10,000건 데이터 사이즈	35 MB	94 MB
검색 응답 시간	655 ms	10.8 ms

로고 이미지

Read More

판다스에서 데이터베이스에 접근하는 방법 예제

코끼리와 판다곰

요약

• Pandas를 통해서, Postgresql의 테이블을 조회하거나 결과를 저장한다.

본문

1. 코끼리에서 데이터 가지고 오기

In [1]:

# 모듈 로딩하기
import pandas as pd
from sqlalchemy import create_engine

# 접속 유형 정의 하기
e = create_engine('postgresql://chef:fork@cook:5432/cook')

# id를 인덱스로 사용하는 샘플 데이터 생성 쿼리
query = """
SELECT row_number() over () as id,'seize the day.' as quote
"""

# 쿼리를 통해서 데이터 가지고 오기
df = pd.read_sql(query,e,index_col='id')

In [2]:

#데이터 확인
df

Out[2]:

	quote
id
1	seize the day.

2. 꼬끼리에게 데이터 보내기

In [3]:

# pandas라는 스키마에, elephant라는 테이블 생성
df.to_sql(schema='pandas',name='elephant',con=e,if_exists='append')

In [4]:

# 테이블 생성 및 인덱스 생성 확인 하기
!psql -c "\d+ pandas.elephant" cook

                       Table "pandas.elephant"
 Column |  Type  | Modifiers | Storage  | Stats target | Description 
--------+--------+-----------+----------+--------------+-------------
 id     | bigint |           | plain    |              | 
 quote  | text   |           | extended |              | 
Indexes:
    "ix_pandas_elephant_id" btree (id)

로고 이미지

Read More

for using Postgres-XL

INSTALL
-- init each instances
initgtm -Z gtm -D /var/lib/pgxl/9.2/data_gtm
initdb -D /var/lib/pgxl/9.2/coord01 --nodename coord01
initdb -D /var/lib/pgxl/9.2/data01 --nodename data01
initdb -D /var/lib/pgxl/9.2/data02 --nodename data02

-- start each instances

gtm_ctl -Z gtm start -D /var/lib/pgxl/9.2/data_gtm

pg_ctl start -D /var/lib/pgxl/9.2/data01 -Z datanode -l logfile

pg_ctl start -D /var/lib/pgxl/9.2/data02 -Z datanode -l logfile

pg_ctl start -D /var/lib/pgxl/9.2/coord01 -Z coordinator -l logfile

-- referred to http://files.postgres-xl.org/documentation/index.html

DEBUG

-- if there are running to single, modify this.

port = 5432 ~ X

pooler_port = 6668 ~ Y

-- define the relation of each instances ( There are missed on manual. )

psql -c "EXECUTE DIRECT ON (coord01) 'CREATE NODE data01 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5433)'" postgres

psql -c "EXECUTE DIRECT ON (coord01) 'CREATE NODE data02 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5434)'" postgres

psql -c "EXECUTE DIRECT ON (data01) 'ALTER NODE data01 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5433)'" postgres

psql -c "EXECUTE DIRECT ON (data01) 'CREATE NODE data02 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5434)'" postgres

psql -c "EXECUTE DIRECT ON (data01) 'SELECT pgxc_pool_reload()'" postgres

psql -c "EXECUTE DIRECT ON (data02) 'CREATE NODE data01 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5433)'" postgres

psql -c "EXECUTE DIRECT ON (data02) 'ALTER NODE data02 WITH (TYPE = ''datanode'', HOST = ''localhost'', PORT = 5434)'" postgres

psql -c "EXECUTE DIRECT ON (data02) 'SELECT pgxc_pool_reload()'" postgres

-- referred to http://sourceforge.net/p/postgres-xl/tickets/18/

로고 이미지

Read More

mongoDB usable scripts

# get an average of collection with some conditions.

db.POINT_TOTAL_OBS_STATION_DATA.group(

   { cond: { obs_item_id : "OBSCD00074" }

   , initial: {count: 0, total:0}

   , reduce: function(doc, out) { out.count++ ; out.total += doc.v1 }

   , finalize: function(out) { out.avg = out.total / out.count }

} )

# improved a performance , however I don't know exactly why... may be hash !!

db.POINT_TOTAL_OBS_STATION_DATA.aggregate( [ 

     { $match: { obs_item_id : "OBSCD00074" } }, 

     { $group: { _id : 0 , v1_avg : { $avg: "$v1"} } } ] )

# group by each values

db.POINT_TOTAL_OBS_STATION_DATA.aggregate( [ 

     { $group: { _id : { key : "$obs_item_id" },  v1_avg : { $avg: "$v1"} } } ] )

# join query for a special case
db.POINT_TOTAL_OBS_STATION_DATA.aggregate( [{ $group: { _id : "$obs_item_id"} } , { $out : "fox_out" } ] )
fox = db.fox_out.find().toArray()
for ( var i = 0 ; i < fox.length ; i ++ ) {   db.fox_result.insert (db.OBS_ITEM_CODE.find( { obs_item_id : fox[i]._id }, { obs_item_id : 1, item_name_kor : 1 } ).toArray() ) }
db.fox_result.find().sort( { item_name_kor : 1 } )


# to find some for
db.POINT_TOTAL_OBS_STATION_DATA.aggregate( [
     { $match: { tm : { $gte : '2011-01-01 00:00:00', $lt : '2012-01-01 00:00:00' } }},
     { $group: { _id : 0 , v1_avg : { $avg: "$v1"} } } ] )


# Create index
db.POINT_TOTAL_OBS_STATION_DATA.ensureIndex( { obs_item_id : 1 } )
db.POINT_TOTAL_OBS_STATION_DATA.ensureIndex( { obs_time : 1 } )
.. more
db.system.indexes.find()

# how to check a elapsed time
db.setProfilingLevel(0) -- disable
db.setProfilingLevel(1) -- enable to 1 level
db.system.profile.find().limit(10).sort( { ts : -1 } ).pretty()

Read More