TPC-H 데이터베이스(DB) 데이터 활용법

TPC-H는 DB 성능 벤치마크라고 합니다. 빅데이터를 얼마나 효율적으로 처리하는 지에 대한 평가 측정이라고 생각하시면 되겠습니다. 실제 기업(real world)에서 다루는 데이터 양이 엄청나니 이를 어떻게 처리하는 것이 시스템적으로도 좋고, 속도 면에서도 좋은 모습을 보이는지가 중요하겠죠. 

본격적으로 TPC-H의 샘플 데이터를 사용하기 위해 우선 우분투 터미널에서 gcc(gnu compiler collection)를 설치해보겠습니다. 아래 회색 박스 내부에 있는 명령어는 모두 우분투 터미널에서 이루어짐을 알려드립니다. 

gcc: 리눅스 환경에서 사용하는 컴파일러(초기 C/C++을 지원하다가 현재는 자바, 포트란, 에이다 등 여러 프로그래밍 언어를 지원한다고 함) 

sudo apt install build-essential
gcc --version

cd dbgen
cp makefile.suite Makefile
vi Makefile

CC = gcc
DATABASE = SQLSERVER # <- Postgresql 용
MACHINE = LINUX
WORKLOAD =TPCH

make # 컴파일 진행

./dbgen # table 생성
ls |grep .tbl  # 생성된 table 보기

cat dds.ddl

---

이제부터는 Postgresql 코드입니다. 우분투 명령어 섹션은 끝났습니다. 

COPY customer
FROM 'C:\****\customer.csv' CSV DELIMITER '|';

(참고) 여기서 "Permission denied"라고 접근 오류가 날 수도 있습니다. 저는 pgAmin4 GUI 툴을 사용하고 있는데 로컬 서버에서 돌리고 있다고 생각했는데 로컬에 있는 파일에 접근을 하지 못하네요..

이때 간단히 해결할 수 있는 방법은 해당 csv 파일이 있는 폴더에 가서 "properties(속성)"을 누르고 "security(보안)" 탭에서 권한을 추가해주면 됩니다. 기존 사용자에게 권한을 추가로 부여하는 것이 아니라 새로운 사용자 "Everyone"을 추가하고 Read, Write에 체크 표시를 해줍니다. 그리고 OK를 누르면 완료입니다.

SELECT count(*) FROM customer;

SELECT
	L_RETURNFLAG,
	L_LINESTATUS,
	sum(L_QUANTITY) as sum_qty,
	sum(L_EXTENDEDPRICE) as sum_base_price,
	sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) as sum_disc_price,
	sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) as sum_charge,
	avg(L_QUANTITY) as avg_qty,
	avg(L_EXTENDEDPRICE) as avg_price,
	avg(L_DISCOUNT) as avg_disc,
	count(*) as count_order
FROM
	lineitem
WHERE
	l_shipdate <= date '1998-12-01' - interval '117' day
GROUP BY
	l_returnflag,
	l_linestatus
ORDER BY
	l_returnflag,
	l_linestatus
LIMIT 1;

# Date/Time 확인해보기
SELECT date '1998-12-01' - interval '117' day;

이외에도 Postgresql의 Date/Time 연산 함수를 보시려면 공식 홈페이지에 잘 나와있으니 참고하시기 바랍니다. 아래는 몇 개 긁어온 기능들입니다. 

Format	Example
date + integer → date	date '2001-09-28' + 7 → 2001-10-05
date + interval → timestamp	date '2001-09-28' + interval '1 hour' → 2001-09-28 01:00:00
date + time → timestamp	date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00
interval + interval → interval	interval '1 day' + interval '1 hour' → 1 day 01:00:00
timestamp + interval → timestamp	timestamp '2001-09-28 01:00' + interval '23 hours' → 2001-09-29 00:00:00
time + interval → time	time '01:00' + interval '3 hours' → 04:00:00
- interval → interval	- interval '23 hours' → -23:00:00
date - date → integer	date '2001-10-01' - date '2001-09-28' → 3
date - integer → date	date '2001-10-01' - 7 → 2001-09-24
date - interval → timestamp	date '2001-09-28' - interval '1 hour' → 2001-09-27 23:00:00

다음은 인덱스를 생성해보려고 합니다. 여기선 hash index를 사용했으며, 적용한 인덱스를 보려면 pg_indexes에서 불러오면 됩니다. 

CREATE INDEX orderkey_index ON lineitem USING hash(L_ORDERKEY);
SELECT * FROM pg_indexes WHERE schemaname='public';

Postgresql에서는 PRIMARY KEY로 지정하면 디폴트로 B-Tree 인덱스를 적용한다.

ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY); # 외래키 적용

이번에는 생성했던 인덱스를 삭제해보겠습니다. 옵션 관련 설명은 한국어 홈페이지를 참고해주세요. 

DROP INDEX orderkey_index CASCADE; # CASCADE는 옵션

default: RESTRICT
option1: CASCADE
다른 테이블과 relation을 가질 경우 삭제가 제한될 수 있는데 이를 무시하고 싹 다 지울 수 있다. 디폴트는 지우지 못하도록 하는 RESTRICT이며 굳이 써주지 않아도 된다.

(참고) Explain 사용법

아래 사각형 리스트 버튼을 누르거나 shift+F7을 누르면 Explain 기능을 사용할 수 있습니다. Postgresql에서 해당 쿼리문을 어떻게 처리한지 그 과정이 나오기 때문에 이해하는 데 도움이 될 것 같습니다.