TPC-H #1 . 준비

The TPC Benchmark™H (TPC-H) is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications.
라고 한다.
동일한 구조 동일한 데이터를 가진 테이블들을 대상으로 쿼리를 실행하여 하드웨어 성능을 비교할 때 사용한다. 1..고 한다. TPC 홈페이지에 가면 다양한 시험 방법을 제공한다.

설치
여타 소프트웨어의 설치와는 다르게 DBMS에 맞는 테이블 생성 쿼리와 샘플 데이터를 만드는 툴을 이용해 DDL과 샘플 데이터를 생성하는 것이 목적이다.
TPC 홈페이지에서 필요한 도구를 다운로드한다. 2예제는 tpc-h를 기준으로 한다. 계정,권한 관련 제한이 없으므로 아무 계정으로 다운로드 해서 진행 해도 무방하다.

PostgresQL에 테이블을 생성하고 데이터를 넣을 것이다.

dbgen(샘플데이터 생성기) 컴파일
압축 해제

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[root@tpc tpc]$ ls
tpc-ds-tool.zip tpc-h-tool.zip v2.13.0rc1
[root@tpc tpc]# unzip tpc-h-tool.zip
...생략...
inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.19998
inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.29997
inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.30000
inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.9999
inflating: 2.18.0_rc2/specification.docx
inflating: 2.18.0_rc2/specification.pdf
[root@tpc tpc]$ ls
2.18.0_rc2 tpc-ds-tool.zip tpc-h-tool.zip v2.13.0rc1
[root@tpc tpc]$ cd 2.18.0_rc2
[root@tpc tpc]$ ls
EULA.txt dbgen dev-tools ref_data specification.docx specification.pdf
[root@tpc 2.18.0_rc2]$ cd dbgen
[root@tpc 2.18.0_rc2]$ ls
BUGS README bcd2.h check_answers dbgen.dsp dss.ddl dsstypes.h permute.c qgen.c reference rnd.h shared.h text.c tpch.sln variants
HISTORY answers bm_utils.c column_split.sh dists.dss dss.h load_stub.c permute.h qgen.vcproj release.h rng64.c speed_seed.c tpcd.h tpch.vcproj varsub.c
PORTING.NOTES bcd2.c build.c config.h driver.c dss.ri makefile.suite print.c queries rnd.c rng64.h tests tpch.dsw update_release.sh
[root@tpc tpc]$ ls tpc-ds-tool.zip tpc-h-tool.zip v2.13.0rc1 [root@tpc tpc]# unzip tpc-h-tool.zip ...생략... inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.19998 inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.29997 inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.30000 inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.9999 inflating: 2.18.0_rc2/specification.docx inflating: 2.18.0_rc2/specification.pdf [root@tpc tpc]$ ls 2.18.0_rc2 tpc-ds-tool.zip tpc-h-tool.zip v2.13.0rc1 [root@tpc tpc]$ cd 2.18.0_rc2 [root@tpc tpc]$ ls EULA.txt dbgen dev-tools ref_data specification.docx specification.pdf [root@tpc 2.18.0_rc2]$ cd dbgen [root@tpc 2.18.0_rc2]$ ls BUGS README bcd2.h check_answers dbgen.dsp dss.ddl dsstypes.h permute.c qgen.c reference rnd.h shared.h text.c tpch.sln variants HISTORY answers bm_utils.c column_split.sh dists.dss dss.h load_stub.c permute.h qgen.vcproj release.h rng64.c speed_seed.c tpcd.h tpch.vcproj varsub.c PORTING.NOTES bcd2.c build.c config.h driver.c dss.ri makefile.suite print.c queries rnd.c rng64.h tests tpch.dsw update_release.sh
[root@tpc tpc]$ ls
tpc-ds-tool.zip  tpc-h-tool.zip  v2.13.0rc1
[root@tpc tpc]# unzip tpc-h-tool.zip
...생략...
  inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.19998
  inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.29997
  inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.30000
  inflating: 2.18.0_rc2/ref_data/300/supplier.tbl.9999
  inflating: 2.18.0_rc2/specification.docx
  inflating: 2.18.0_rc2/specification.pdf
[root@tpc tpc]$ ls
2.18.0_rc2  tpc-ds-tool.zip  tpc-h-tool.zip  v2.13.0rc1
[root@tpc tpc]$ cd 2.18.0_rc2
[root@tpc tpc]$ ls
EULA.txt  dbgen  dev-tools  ref_data  specification.docx  specification.pdf
[root@tpc 2.18.0_rc2]$ cd dbgen
[root@tpc 2.18.0_rc2]$ ls
BUGS           README   bcd2.h      check_answers    dbgen.dsp  dss.ddl  dsstypes.h      permute.c  qgen.c       reference  rnd.h    shared.h      text.c    tpch.sln           variants
HISTORY        answers  bm_utils.c  column_split.sh  dists.dss  dss.h    load_stub.c     permute.h  qgen.vcproj  release.h  rng64.c  speed_seed.c  tpcd.h    tpch.vcproj        varsub.c
PORTING.NOTES  bcd2.c   build.c     config.h         driver.c   dss.ri   makefile.suite  print.c    queries      rnd.c      rng64.h  tests         tpch.dsw  update_release.sh

dbgen 컴파일을 위한 설정 파일 수정

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# makefile 생성
[root@tpc dbgen]$ cp makefile.suite makefile
[root@tpc dbgen]$ vi makefile
# 컴파일러, 대상 DBMS종류, 시스템 종류, workload 정보를 변경한다.
# CC = , DATABASE = , MACHINE = , WORKLOAD = 항목을 찾는다.
# 리눅스에 설치된 ORACLE 을 기준으로 다음과 같이 변경. (아래 주석 참고)
CC = gcc
DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
# SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS,
# SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are: TPCH
# makefile 생성 [root@tpc dbgen]$ cp makefile.suite makefile [root@tpc dbgen]$ vi makefile # 컴파일러, 대상 DBMS종류, 시스템 종류, workload 정보를 변경한다. # CC = , DATABASE = , MACHINE = , WORKLOAD = 항목을 찾는다. # 리눅스에 설치된 ORACLE 을 기준으로 다음과 같이 변경. (아래 주석 참고) CC = gcc DATABASE= ORACLE MACHINE = LINUX WORKLOAD = TPCH # Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata) # SQLSERVER, SYBASE, ORACLE, VECTORWISE # Current values for MACHINE are: ATT, DOS, HP, IBM, ICL, MVS, # SGI, SUN, U2200, VMS, LINUX, WIN32 # Current values for WORKLOAD are: TPCH
 # makefile 생성
[root@tpc dbgen]$ cp    makefile.suite      makefile
[root@tpc dbgen]$ vi     makefile
 # 컴파일러,  대상 DBMS종류, 시스템 종류, workload 정보를 변경한다.
 # CC = , DATABASE = , MACHINE = , WORKLOAD = 항목을 찾는다.
 # 리눅스에 설치된 ORACLE 을 기준으로 다음과 같이 변경. (아래 주석 참고)
CC      = gcc
DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH

컴파일 및 확인
dbgen 파일이 생성되면 정상적으로 컴파일이 된 것이다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[root@tpc dbgen]$ make
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o build.o build.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o driver.o driver.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bm_utils.o bm_utils.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rnd.o rnd.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o print.o print.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o load_stub.o load_stub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bcd2.o bcd2.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o speed_seed.o speed_seed.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o text.o text.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o permute.o permute.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rng64.o rng64.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o qgen.o qgen.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o varsub.o varsub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm
[root@tpc dbgen]$ ll
total 920
...중략...
-rwxr-xr-x 1 root root 111910 Aug 2 13:01 dbgen
-rw-r--r-- 1 root root 5154 Dec 5 2018 dbgen.dsp
...중략...
-rwxr-xr-x 1 root root 103060 Aug 2 13:01 qgen
-rw-r--r-- 1 root root 14404 Dec 5 2018 qgen.c
-rw-r--r-- 1 root root 48984 Aug 2 13:01 qgen.o
-rw-r--r-- 1 root root 4916 Dec 5 2018 qgen.vcproj
drwxr-xr-x 2 root root 4096 Feb 18 12:03 queries
-rw-r--r-- 1 root root 17617 Dec 5 2018 README
[root@tpc dbgen]$ make gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o build.o build.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o driver.o driver.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bm_utils.o bm_utils.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rnd.o rnd.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o print.o print.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o load_stub.o load_stub.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o bcd2.o bcd2.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o speed_seed.o speed_seed.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o text.o text.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o permute.o permute.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o rng64.o rng64.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o qgen.o qgen.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -c -o varsub.o varsub.c gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64 -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm [root@tpc dbgen]$ ll total 920 ...중략... -rwxr-xr-x 1 root root 111910 Aug 2 13:01 dbgen -rw-r--r-- 1 root root 5154 Dec 5 2018 dbgen.dsp ...중략... -rwxr-xr-x 1 root root 103060 Aug 2 13:01 qgen -rw-r--r-- 1 root root 14404 Dec 5 2018 qgen.c -rw-r--r-- 1 root root 48984 Aug 2 13:01 qgen.o -rw-r--r-- 1 root root 4916 Dec 5 2018 qgen.vcproj drwxr-xr-x 2 root root 4096 Feb 18 12:03 queries -rw-r--r-- 1 root root 17617 Dec 5 2018 README
[root@tpc dbgen]$  make
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o driver.o driver.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bm_utils.o bm_utils.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rnd.o rnd.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o print.o print.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o load_stub.o load_stub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bcd2.o bcd2.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o speed_seed.o speed_seed.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o text.o text.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o permute.o permute.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rng64.o rng64.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o qgen.o qgen.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o varsub.o varsub.c
gcc -g -DDBNAME=\"dss\" -DLINUX -DORACLE -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm
[root@tpc dbgen]$  ll
total 920
...중략...
-rwxr-xr-x 1 root root 111910 Aug  2 13:01 dbgen
-rw-r--r-- 1 root root   5154 Dec  5  2018 dbgen.dsp
...중략...
-rwxr-xr-x 1 root root 103060 Aug  2 13:01 qgen
-rw-r--r-- 1 root root  14404 Dec  5  2018 qgen.c
-rw-r--r-- 1 root root  48984 Aug  2 13:01 qgen.o
-rw-r--r-- 1 root root   4916 Dec  5  2018 qgen.vcproj
drwxr-xr-x 2 root root   4096 Feb 18 12:03 queries
-rw-r--r-- 1 root root  17617 Dec  5  2018 README

※ TPC-H는 정확하게 PostgresQL용 쿼리를 제공하지 않는다. ORACLE용 쿼리와 데이터를 생성하고 이를 이용해 PostgresQL에 데이터를 넣을 것이다.

데이터 및 테스트 쿼리 생성
dbgen을 실행하면 실험용 쿼리와 데이터가 자동으로 생성된다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 데이터 및 쿼리 생성
[root@tpc dbgen]$ ./dbgen
TPC-H Population Generator (Version 3.0.0)
Copyright Transaction Processing Performance Council 1994 - 2010
# 데이터 및 쿼리 생성 [root@tpc dbgen]$ ./dbgen TPC-H Population Generator (Version 3.0.0) Copyright Transaction Processing Performance Council 1994 - 2010
# 데이터 및 쿼리 생성
[root@tpc dbgen]$  ./dbgen
TPC-H Population Generator (Version 3.0.0)
Copyright Transaction Processing Performance Council 1994 - 2010
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 편의를 위해 쿼리파일과 샘플 데이터를 한곳으로 옮긴다.
[root@tpc dbgen]# mkdir data
[root@tpc dbgen]# cp *.ddl data/
[root@tpc dbgen]# cp *.tbl data/
# 편의를 위해 쿼리파일과 샘플 데이터를 한곳으로 옮긴다. [root@tpc dbgen]# mkdir data [root@tpc dbgen]# cp *.ddl data/ [root@tpc dbgen]# cp *.tbl data/
 # 편의를 위해 쿼리파일과 샘플 데이터를 한곳으로 옮긴다.
[root@tpc dbgen]# mkdir data
[root@tpc dbgen]# cp *.ddl data/
[root@tpc dbgen]# cp *.tbl data/
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 확인 - 중간에 dss.ddl 파일에 테이블 생성 스크립트가 존재한다.
[root@tpc dbgen]$ ll data
total 1074924
-rw-r--r-- 1 root root 24346144 Aug 2 13:07 customer.tbl
-rw-r--r-- 1 root root 3814 Dec 5 2018 dss.ddl
-rw-r--r-- 1 root root 759863287 Aug 2 13:07 lineitem.tbl
-rw-r--r-- 1 root root 2224 Aug 2 13:07 nation.tbl
-rw-r--r-- 1 root root 171952161 Aug 2 13:07 orders.tbl
-rw-r--r-- 1 root root 118984616 Aug 2 13:07 partsupp.tbl
-rw-r--r-- 1 root root 24135125 Aug 2 13:07 part.tbl
-rw-r--r-- 1 root root 389 Aug 2 13:07 region.tbl
-rw-r--r-- 1 root root 1409184 Aug 2 13:07 supplier.tbl
[root@tpc dbgen]$ ll queries/
total 88
-rw-r--r-- 1 root root 646 Dec 5 2018 10.sql
-rw-r--r-- 1 root root 631 Dec 5 2018 11.sql
-rw-r--r-- 1 root root 720 Dec 5 2018 12.sql
-rw-r--r-- 1 root root 470 Dec 5 2018 13.sql
-rw-r--r-- 1 root root 442 Dec 5 2018 14.sql
-rw-r--r-- 1 root root 641 Dec 5 2018 15.sql
-rw-r--r-- 1 root root 609 Dec 5 2018 16.sql
-rw-r--r-- 1 root root 411 Dec 5 2018 17.sql
-rw-r--r-- 1 root root 581 Dec 5 2018 18.sql
-rw-r--r-- 1 root root 1090 Dec 5 2018 19.sql
-rw-r--r-- 1 root root 666 Dec 5 2018 1.sql
-rw-r--r-- 1 root root 711 Dec 5 2018 20.sql
-rw-r--r-- 1 root root 804 Dec 5 2018 21.sql
-rw-r--r-- 1 root root 797 Dec 5 2018 22.sql
-rw-r--r-- 1 root root 815 Dec 5 2018 2.sql
-rw-r--r-- 1 root root 518 Dec 5 2018 3.sql
-rw-r--r-- 1 root root 474 Dec 5 2018 4.sql
-rw-r--r-- 1 root root 605 Dec 5 2018 5.sql
-rw-r--r-- 1 root root 363 Dec 5 2018 6.sql
-rw-r--r-- 1 root root 926 Dec 5 2018 7.sql
-rw-r--r-- 1 root root 900 Dec 5 2018 8.sql
-rw-r--r-- 1 root root 741 Dec 5 2018 9.sql
# 확인 - 중간에 dss.ddl 파일에 테이블 생성 스크립트가 존재한다. [root@tpc dbgen]$ ll data total 1074924 -rw-r--r-- 1 root root 24346144 Aug 2 13:07 customer.tbl -rw-r--r-- 1 root root 3814 Dec 5 2018 dss.ddl -rw-r--r-- 1 root root 759863287 Aug 2 13:07 lineitem.tbl -rw-r--r-- 1 root root 2224 Aug 2 13:07 nation.tbl -rw-r--r-- 1 root root 171952161 Aug 2 13:07 orders.tbl -rw-r--r-- 1 root root 118984616 Aug 2 13:07 partsupp.tbl -rw-r--r-- 1 root root 24135125 Aug 2 13:07 part.tbl -rw-r--r-- 1 root root 389 Aug 2 13:07 region.tbl -rw-r--r-- 1 root root 1409184 Aug 2 13:07 supplier.tbl [root@tpc dbgen]$ ll queries/ total 88 -rw-r--r-- 1 root root 646 Dec 5 2018 10.sql -rw-r--r-- 1 root root 631 Dec 5 2018 11.sql -rw-r--r-- 1 root root 720 Dec 5 2018 12.sql -rw-r--r-- 1 root root 470 Dec 5 2018 13.sql -rw-r--r-- 1 root root 442 Dec 5 2018 14.sql -rw-r--r-- 1 root root 641 Dec 5 2018 15.sql -rw-r--r-- 1 root root 609 Dec 5 2018 16.sql -rw-r--r-- 1 root root 411 Dec 5 2018 17.sql -rw-r--r-- 1 root root 581 Dec 5 2018 18.sql -rw-r--r-- 1 root root 1090 Dec 5 2018 19.sql -rw-r--r-- 1 root root 666 Dec 5 2018 1.sql -rw-r--r-- 1 root root 711 Dec 5 2018 20.sql -rw-r--r-- 1 root root 804 Dec 5 2018 21.sql -rw-r--r-- 1 root root 797 Dec 5 2018 22.sql -rw-r--r-- 1 root root 815 Dec 5 2018 2.sql -rw-r--r-- 1 root root 518 Dec 5 2018 3.sql -rw-r--r-- 1 root root 474 Dec 5 2018 4.sql -rw-r--r-- 1 root root 605 Dec 5 2018 5.sql -rw-r--r-- 1 root root 363 Dec 5 2018 6.sql -rw-r--r-- 1 root root 926 Dec 5 2018 7.sql -rw-r--r-- 1 root root 900 Dec 5 2018 8.sql -rw-r--r-- 1 root root 741 Dec 5 2018 9.sql
# 확인 - 중간에 dss.ddl 파일에 테이블 생성 스크립트가 존재한다.
[root@tpc dbgen]$ ll data
total 1074924
-rw-r--r-- 1 root root  24346144 Aug  2 13:07 customer.tbl
-rw-r--r-- 1 root root      3814 Dec  5  2018 dss.ddl
-rw-r--r-- 1 root root 759863287 Aug  2 13:07 lineitem.tbl
-rw-r--r-- 1 root root      2224 Aug  2 13:07 nation.tbl
-rw-r--r-- 1 root root 171952161 Aug  2 13:07 orders.tbl
-rw-r--r-- 1 root root 118984616 Aug  2 13:07 partsupp.tbl
-rw-r--r-- 1 root root  24135125 Aug  2 13:07 part.tbl
-rw-r--r-- 1 root root       389 Aug  2 13:07 region.tbl
-rw-r--r-- 1 root root   1409184 Aug  2 13:07 supplier.tbl
[root@tpc dbgen]$ ll queries/
total 88
-rw-r--r-- 1 root root  646 Dec  5  2018 10.sql
-rw-r--r-- 1 root root  631 Dec  5  2018 11.sql
-rw-r--r-- 1 root root  720 Dec  5  2018 12.sql
-rw-r--r-- 1 root root  470 Dec  5  2018 13.sql
-rw-r--r-- 1 root root  442 Dec  5  2018 14.sql
-rw-r--r-- 1 root root  641 Dec  5  2018 15.sql
-rw-r--r-- 1 root root  609 Dec  5  2018 16.sql
-rw-r--r-- 1 root root  411 Dec  5  2018 17.sql
-rw-r--r-- 1 root root  581 Dec  5  2018 18.sql
-rw-r--r-- 1 root root 1090 Dec  5  2018 19.sql
-rw-r--r-- 1 root root  666 Dec  5  2018 1.sql
-rw-r--r-- 1 root root  711 Dec  5  2018 20.sql
-rw-r--r-- 1 root root  804 Dec  5  2018 21.sql
-rw-r--r-- 1 root root  797 Dec  5  2018 22.sql
-rw-r--r-- 1 root root  815 Dec  5  2018 2.sql
-rw-r--r-- 1 root root  518 Dec  5  2018 3.sql
-rw-r--r-- 1 root root  474 Dec  5  2018 4.sql
-rw-r--r-- 1 root root  605 Dec  5  2018 5.sql
-rw-r--r-- 1 root root  363 Dec  5  2018 6.sql
-rw-r--r-- 1 root root  926 Dec  5  2018 7.sql
-rw-r--r-- 1 root root  900 Dec  5  2018 8.sql
-rw-r--r-- 1 root root  741 Dec  5  2018 9.sql

테이블 생성

PostgresQL은 텍스트파일3i.e. csv 파일을 import 할 때 문자열 끝에 구분자가 있으면 컬럼이 하나 더 있는 것으로 인식한다. 따라서 이를 위한 dummy column 을 추가한다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- 더미 컬럼에는 데이터가 들어가지 않는다. 따라서 붙여넣기 하다가 not null 도 따라 붙여 넣지 않도록 주의한다.
CREATE TABLE public.NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152),
N_DUMMY VARCHAR(1));
CREATE TABLE public.REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152),
R_DUMMY VARCHAR(1));
CREATE TABLE public.PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT null,
P_DUMMY VARCHAR(1));
CREATE TABLE public.SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL,
S_DUMMY VARCHAR(1));
CREATE TABLE public.PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL, );
CREATE TABLE public.CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL,
C_DUMMY VARCHAR(1));
CREATE TABLE public.ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL,
O_DUMMY VARCHAR(1));
CREATE TABLE public.LINEITEM ( L_ORDERKEY INTEGER NOT NULL,
L_PARTKEY INTEGER NOT NULL,
L_SUPPKEY INTEGER NOT NULL,
L_LINENUMBER INTEGER NOT NULL,
L_QUANTITY DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL,
L_DUMMY VARCHAR(1));
-- 더미 컬럼에는 데이터가 들어가지 않는다. 따라서 붙여넣기 하다가 not null 도 따라 붙여 넣지 않도록 주의한다. CREATE TABLE public.NATION ( N_NATIONKEY INTEGER NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INTEGER NOT NULL, N_COMMENT VARCHAR(152), N_DUMMY VARCHAR(1)); CREATE TABLE public.REGION ( R_REGIONKEY INTEGER NOT NULL, R_NAME CHAR(25) NOT NULL, R_COMMENT VARCHAR(152), R_DUMMY VARCHAR(1)); CREATE TABLE public.PART ( P_PARTKEY INTEGER NOT NULL, P_NAME VARCHAR(55) NOT NULL, P_MFGR CHAR(25) NOT NULL, P_BRAND CHAR(10) NOT NULL, P_TYPE VARCHAR(25) NOT NULL, P_SIZE INTEGER NOT NULL, P_CONTAINER CHAR(10) NOT NULL, P_RETAILPRICE DECIMAL(15,2) NOT NULL, P_COMMENT VARCHAR(23) NOT null, P_DUMMY VARCHAR(1)); CREATE TABLE public.SUPPLIER ( S_SUPPKEY INTEGER NOT NULL, S_NAME CHAR(25) NOT NULL, S_ADDRESS VARCHAR(40) NOT NULL, S_NATIONKEY INTEGER NOT NULL, S_PHONE CHAR(15) NOT NULL, S_ACCTBAL DECIMAL(15,2) NOT NULL, S_COMMENT VARCHAR(101) NOT NULL, S_DUMMY VARCHAR(1)); CREATE TABLE public.PARTSUPP ( PS_PARTKEY INTEGER NOT NULL, PS_SUPPKEY INTEGER NOT NULL, PS_AVAILQTY INTEGER NOT NULL, PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL, ); CREATE TABLE public.CUSTOMER ( C_CUSTKEY INTEGER NOT NULL, C_NAME VARCHAR(25) NOT NULL, C_ADDRESS VARCHAR(40) NOT NULL, C_NATIONKEY INTEGER NOT NULL, C_PHONE CHAR(15) NOT NULL, C_ACCTBAL DECIMAL(15,2) NOT NULL, C_MKTSEGMENT CHAR(10) NOT NULL, C_COMMENT VARCHAR(117) NOT NULL, C_DUMMY VARCHAR(1)); CREATE TABLE public.ORDERS ( O_ORDERKEY INTEGER NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15,2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ORDERPRIORITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY INTEGER NOT NULL, O_COMMENT VARCHAR(79) NOT NULL, O_DUMMY VARCHAR(1)); CREATE TABLE public.LINEITEM ( L_ORDERKEY INTEGER NOT NULL, L_PARTKEY INTEGER NOT NULL, L_SUPPKEY INTEGER NOT NULL, L_LINENUMBER INTEGER NOT NULL, L_QUANTITY DECIMAL(15,2) NOT NULL, L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL, L_DISCOUNT DECIMAL(15,2) NOT NULL, L_TAX DECIMAL(15,2) NOT NULL, L_RETURNFLAG CHAR(1) NOT NULL, L_LINESTATUS CHAR(1) NOT NULL, L_SHIPDATE DATE NOT NULL, L_COMMITDATE DATE NOT NULL, L_RECEIPTDATE DATE NOT NULL, L_SHIPINSTRUCT CHAR(25) NOT NULL, L_SHIPMODE CHAR(10) NOT NULL, L_COMMENT VARCHAR(44) NOT NULL, L_DUMMY VARCHAR(1));
-- 더미 컬럼에는 데이터가 들어가지 않는다. 따라서 붙여넣기 하다가 not null 도 따라 붙여 넣지 않도록 주의한다.

CREATE TABLE public.NATION  ( N_NATIONKEY  INTEGER NOT NULL,
                            N_NAME       CHAR(25) NOT NULL,
                            N_REGIONKEY  INTEGER NOT NULL,
                            N_COMMENT    VARCHAR(152),
                            N_DUMMY   VARCHAR(1));
                           
CREATE TABLE public.REGION  ( R_REGIONKEY  INTEGER NOT NULL,
                            R_NAME       CHAR(25) NOT NULL,
                            R_COMMENT    VARCHAR(152),
                            R_DUMMY VARCHAR(1));

CREATE TABLE public.PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT null,
                          P_DUMMY VARCHAR(1));

CREATE TABLE public.SUPPLIER ( S_SUPPKEY     INTEGER NOT NULL,
                             S_NAME        CHAR(25) NOT NULL,
                             S_ADDRESS     VARCHAR(40) NOT NULL,
                             S_NATIONKEY   INTEGER NOT NULL,
                             S_PHONE       CHAR(15) NOT NULL,
                             S_ACCTBAL     DECIMAL(15,2) NOT NULL,
                             S_COMMENT     VARCHAR(101) NOT NULL,
                             S_DUMMY    VARCHAR(1));

CREATE TABLE public.PARTSUPP ( PS_PARTKEY     INTEGER NOT NULL,
                             PS_SUPPKEY     INTEGER NOT NULL,
                             PS_AVAILQTY    INTEGER NOT NULL,
                             PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
                             PS_COMMENT     VARCHAR(199) NOT NULL, );

CREATE TABLE public.CUSTOMER ( C_CUSTKEY     INTEGER NOT NULL,
                             C_NAME        VARCHAR(25) NOT NULL,
                             C_ADDRESS     VARCHAR(40) NOT NULL,
                             C_NATIONKEY   INTEGER NOT NULL,
                             C_PHONE       CHAR(15) NOT NULL,
                             C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
                             C_MKTSEGMENT  CHAR(10) NOT NULL,
                             C_COMMENT     VARCHAR(117) NOT NULL,
                             C_DUMMY VARCHAR(1));

CREATE TABLE public.ORDERS  ( O_ORDERKEY       INTEGER NOT NULL,
                           O_CUSTKEY        INTEGER NOT NULL,
                           O_ORDERSTATUS    CHAR(1) NOT NULL,
                           O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
                           O_ORDERDATE      DATE NOT NULL,
                           O_ORDERPRIORITY  CHAR(15) NOT NULL,
                           O_CLERK          CHAR(15) NOT NULL,
                           O_SHIPPRIORITY   INTEGER NOT NULL,
                           O_COMMENT        VARCHAR(79) NOT NULL,
                           O_DUMMY VARCHAR(1));

CREATE TABLE public.LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL,
                             L_DUMMY VARCHAR(1));

데이터 입력

※ 테이블 명, 컬럼명 등은 대소문자 구분을 하지 않지만 소스 파일 명 및 경로는 대소문자를 구분하므로 주의한다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
COPY public.nation(N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT,N_dummy)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/nation.tbl'
DELIMITER '|'
;
COPY public.region(R_REGIONKEY,R_NAME,R_COMMENT,R_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl'
DELIMITER '|'
;
COPY public.part(P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT,P_DUMMY VARCHAR)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/part.tbl'
DELIMITER '|'
;
COPY public.supplier(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/supplier.tbl'
DELIMITER '|'
;
COPY public.partsupp(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT,PS_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/partsupp.tbl'
DELIMITER '|'
;
COPY public.customer(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT,C_dummy)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/customerp.tbl'
DELIMITER '|'
;
COPY public.orders(O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT,O_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/orders.tbl'
DELIMITER '|'
;
COPY public.lineitem(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT,L_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/lineitem.tbl'
DELIMITER '|'
;
COPY public.nation(N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT,N_dummy) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/nation.tbl' DELIMITER '|' ; COPY public.region(R_REGIONKEY,R_NAME,R_COMMENT,R_DUMMY) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl' DELIMITER '|' ; COPY public.part(P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT,P_DUMMY VARCHAR) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/part.tbl' DELIMITER '|' ; COPY public.supplier(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/supplier.tbl' DELIMITER '|' ; COPY public.partsupp(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT,PS_DUMMY) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/partsupp.tbl' DELIMITER '|' ; COPY public.customer(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT,C_dummy) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/customerp.tbl' DELIMITER '|' ; COPY public.orders(O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT,O_DUMMY) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/orders.tbl' DELIMITER '|' ; COPY public.lineitem(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT,L_DUMMY) FROM '/home/tpc/2.18.0_rc2/dbgen/sample/lineitem.tbl' DELIMITER '|' ;
COPY public.nation(N_NATIONKEY,N_NAME,N_REGIONKEY,N_COMMENT,N_dummy)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/nation.tbl'
DELIMITER '|'
;

COPY public.region(R_REGIONKEY,R_NAME,R_COMMENT,R_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl'
DELIMITER '|'
;

COPY public.part(P_PARTKEY,P_NAME,P_MFGR,P_BRAND,P_TYPE,P_SIZE,P_CONTAINER,P_RETAILPRICE,P_COMMENT,P_DUMMY VARCHAR)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/part.tbl'
DELIMITER '|'
;

COPY public.supplier(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/supplier.tbl'
DELIMITER '|'
;

COPY public.partsupp(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY,PS_SUPPLYCOST,PS_COMMENT,PS_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/partsupp.tbl'
DELIMITER '|'
;

COPY public.customer(C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_MKTSEGMENT,C_COMMENT,C_dummy)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/customerp.tbl'
DELIMITER '|'
;
                           
COPY public.orders(O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT,O_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/orders.tbl'
DELIMITER '|'
;

COPY public.lineitem(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_LINENUMBER,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,L_TAX,L_RETURNFLAG,L_LINESTATUS,L_SHIPDATE,L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT,L_SHIPMODE,L_COMMENT,L_DUMMY)
FROM '/home/tpc/2.18.0_rc2/dbgen/sample/lineitem.tbl'
DELIMITER '|'
;

확인

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 실행 예
# PostgresQL import 문법이므로 기억하자
tpc=$ COPY public.customer(c_custkey,c_name,c_address,c_nationkey,c_phone,c_acctbal,c_mktsegment,c_comment,c_dummy)
tpc-$ FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl'
tpc-$ DELIMITER '|'
tpc-$ ;
COPY 150000
tpc=$
# 확인
tpc=$ select count(*) from public.customer;
count
--------
150000
(1개 행)
tpc=$ select * from public.customer limit 1;
c_custkey | c_name | c_address | c_nationkey | c_phone | c_acctbal | c_mktsegment | c_comment | c_dummy
-----------+--------------------+-------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------+---------
1 | Customer#000000001 | IVhzIApeRb ot,c,E | 15 | 25-989-741-2988 | 711.56 | BUILDING | to the even, regular platelets. regular, ironic epitaphs nag e |
(1개 행)
tpc=$ \dt
릴레이션(relation) 목록
스키마 | 이름 | 종류 | 소유주
--------+----------+--------+--------
public | customer | 테이블 | tpc
public | lineitem | 테이블 | tpc
public | nation | 테이블 | tpc
public | orders | 테이블 | tpc
public | part | 테이블 | tpc
public | partsupp | 테이블 | tpc
public | region | 테이블 | tpc
public | supplier | 테이블 | tpc
(8개 행)
tpc=$
# 실행 예 # PostgresQL import 문법이므로 기억하자 tpc=$ COPY public.customer(c_custkey,c_name,c_address,c_nationkey,c_phone,c_acctbal,c_mktsegment,c_comment,c_dummy) tpc-$ FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl' tpc-$ DELIMITER '|' tpc-$ ; COPY 150000 tpc=$ # 확인 tpc=$ select count(*) from public.customer; count -------- 150000 (1개 행) tpc=$ select * from public.customer limit 1; c_custkey | c_name | c_address | c_nationkey | c_phone | c_acctbal | c_mktsegment | c_comment | c_dummy -----------+--------------------+-------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------+--------- 1 | Customer#000000001 | IVhzIApeRb ot,c,E | 15 | 25-989-741-2988 | 711.56 | BUILDING | to the even, regular platelets. regular, ironic epitaphs nag e | (1개 행) tpc=$ \dt 릴레이션(relation) 목록 스키마 | 이름 | 종류 | 소유주 --------+----------+--------+-------- public | customer | 테이블 | tpc public | lineitem | 테이블 | tpc public | nation | 테이블 | tpc public | orders | 테이블 | tpc public | part | 테이블 | tpc public | partsupp | 테이블 | tpc public | region | 테이블 | tpc public | supplier | 테이블 | tpc (8개 행) tpc=$
 # 실행 예
 # PostgresQL import 문법이므로 기억하자
tpc=$ COPY public.customer(c_custkey,c_name,c_address,c_nationkey,c_phone,c_acctbal,c_mktsegment,c_comment,c_dummy)
tpc-$ FROM '/home/tpc/2.18.0_rc2/dbgen/sample/region.tbl'
tpc-$ DELIMITER '|'
tpc-$ ;
COPY 150000
tpc=$
 # 확인
tpc=$ select count(*) from public.customer;
 count
--------
 150000
(1개 행)

tpc=$ select * from public.customer limit 1;
 c_custkey |       c_name       |     c_address     | c_nationkey |     c_phone     | c_acctbal | c_mktsegment |                           c_comment                            | c_dummy
-----------+--------------------+-------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------+---------
         1 | Customer#000000001 | IVhzIApeRb ot,c,E |          15 | 25-989-741-2988 |    711.56 | BUILDING     | to the even, regular platelets. regular, ironic epitaphs nag e |
(1개 행)

tpc=$ \dt
       릴레이션(relation) 목록
 스키마 |   이름   |  종류  | 소유주
--------+----------+--------+--------
 public | customer | 테이블 | tpc
 public | lineitem | 테이블 | tpc
 public | nation   | 테이블 | tpc
 public | orders   | 테이블 | tpc
 public | part     | 테이블 | tpc
 public | partsupp | 테이블 | tpc
 public | region   | 테이블 | tpc
 public | supplier | 테이블 | tpc
(8개 행)

tpc=$

PostgresQL #.2 설정

인터페이스 변경

이전 포스트에서 구동 한 PostgresQL서버는 기본 값 loopbak(127.0.0.1) 인터페이스로 구동 되었다. 외부 연결을 위해서는 서비스 리슨 인터페이스를 변경 해 줘야 한다.
Listen 인터페이스 관련 설정은 postgresql.conf 파일에 정의 돼 있다. 이전 포스트의 내용대로 설치 했을 경우 설정 파일은 /var/lib/pgsql/12/data 아래에 존재한다. 1설치 할 때의 조건에 따라 /etc/postgresql/version 디렉토리 아래에 존재할 수 도 있다.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[postgres@centos7:/home/]$ vi /var/lib/pgsql/12/data/postgresql.conf
# 아래 "listen_addresses"와 "port" 값을 변경해 주면 되는데 주석처리가 돼 있으므로 파일 끝에 새 값을 삽입한다.
... 중략...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - TCP settings -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
....중략...
# 여기부터 파일 끝에 삽입한다.
listen_addresses = '0.0.0.0'
# 인터페이스가 여러개일 경우 0.0.0.0을 입력하면 모든 인터페이스가 연결을 받아들이고
# 특정 IP를 입력하면 지정한 IP로만 연결이 가능한다.
port = 5432
[postgres@centos7:/home/]$ vi /var/lib/pgsql/12/data/postgresql.conf # 아래 "listen_addresses"와 "port" 값을 변경해 주면 되는데 주석처리가 돼 있으므로 파일 끝에 새 값을 삽입한다. ... 중략... #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - #listen_addresses = 'localhost' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart) #port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) #superuser_reserved_connections = 3 # (change requires restart) #unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories # (change requires restart) #unix_socket_group = '' # (change requires restart) #unix_socket_permissions = 0777 # begin with 0 to use octal notation # (change requires restart) #bonjour = off # advertise server via Bonjour # (change requires restart) #bonjour_name = '' # defaults to the computer name # (change requires restart) # - TCP settings - # see "man 7 tcp" for details #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; # 0 selects the system default #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; # 0 selects the system default #tcp_keepalives_count = 0 # TCP_KEEPCNT; # 0 selects the system default ....중략... # 여기부터 파일 끝에 삽입한다. listen_addresses = '0.0.0.0' # 인터페이스가 여러개일 경우 0.0.0.0을 입력하면 모든 인터페이스가 연결을 받아들이고 # 특정 IP를 입력하면 지정한 IP로만 연결이 가능한다. port = 5432
[postgres@centos7:/home/]$  vi /var/lib/pgsql/12/data/postgresql.conf
 
 # 아래 "listen_addresses"와 "port" 값을 변경해 주면 되는데 주석처리가 돼 있으므로 파일 끝에 새 값을 삽입한다.
      ... 중략...
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp'  # comma-separated list of directories
                                        # (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation
                                        # (change requires restart)
#bonjour = off                          # advertise server via Bonjour
                                        # (change requires restart)
#bonjour_name = ''                      # defaults to the computer name
                                        # (change requires restart)

# - TCP settings -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0                # TCP_KEEPIDLE, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_interval = 0            # TCP_KEEPINTVL, in seconds;
                                        # 0 selects the system default
#tcp_keepalives_count = 0               # TCP_KEEPCNT;
                                        # 0 selects the system default
    ....중략...

 # 여기부터 파일 끝에 삽입한다.
listen_addresses = '0.0.0.0'
 # 인터페이스가 여러개일 경우 0.0.0.0을 입력하면 모든 인터페이스가 연결을 받아들이고
 # 특정 IP를 입력하면 지정한 IP로만 연결이 가능한다.
port = 5432                         

인증 방법 설정

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 패스워드 인증을을 통한 접속 허용을 위한 설정
[postgres@centos7:/home/]$ vi /var/lib/pgsql/12/data/pg_hba.conf
# 파일의 끝에 아래를 붙여넣기 한다.
host all all 0.0.0.0/0 md5
#호스트를 기준으로 모든 계정을 이용해 모든 IP에서 들어오는 연결에 대해 패스워드 인증을 허용하는 설정이다.
# 패스워드 인증을을 통한 접속 허용을 위한 설정 [postgres@centos7:/home/]$ vi /var/lib/pgsql/12/data/pg_hba.conf # 파일의 끝에 아래를 붙여넣기 한다. host all all 0.0.0.0/0 md5 #호스트를 기준으로 모든 계정을 이용해 모든 IP에서 들어오는 연결에 대해 패스워드 인증을 허용하는 설정이다.
 # 패스워드 인증을을 통한 접속 허용을 위한 설정
[postgres@centos7:/home/]$  vi /var/lib/pgsql/12/data/pg_hba.conf
 # 파일의 끝에 아래를 붙여넣기 한다.

host    all             all             0.0.0.0/0               md5
#호스트를 기준으로 모든 계정을 이용해 모든 IP에서 들어오는 연결에 대해 패스워드 인증을 허용하는 설정이다.
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
#인터페이스 관련 설정을 변경할 경우 재기동이 필요하다.
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log stop
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log start
#인터페이스 관련 설정을 변경할 경우 재기동이 필요하다. [postgres@centos7:/home/]$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log stop [postgres@centos7:/home/]$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log start
 #인터페이스 관련 설정을 변경할 경우 재기동이 필요하다.
[postgres@centos7:/home/]$  /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log stop
[postgres@centos7:/home/]$  /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data -l /var/lib/pgsql/12/data/pgsql.log start

계정 생성

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 생성하는 계정에 관리자 권한을 부여할 때
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/createuser psqluser --interactive
createuser dataware --interactive
새 롤을 superuser 권한으로 지정할까요? (y/n) y
# 생성하는 계정에 관리자 권한을 부여할 때 [postgres@centos7:/home/]$ /usr/pgsql-12/bin/createuser psqluser --interactive createuser dataware --interactive 새 롤을 superuser 권한으로 지정할까요? (y/n) y
 # 생성하는 계정에 관리자 권한을 부여할 때
[postgres@centos7:/home/]$   /usr/pgsql-12/bin/createuser psqluser --interactive
createuser dataware --interactive
새 롤을 superuser 권한으로 지정할까요? (y/n) y
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# 관리자 권한을 부여하지 않을 때.
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/createuser psqluser --interactive
새 롤을 superuser 권한으로 지정할까요? (y/n) n
이 새 롤에게 데이터베이스를 만들 수 있는 권할을 줄까요? (y/n) y
이 새 롤에게 또 다른 롤을 만들 수 있는 권한을 줄까요? (y/n) y
# 관리자 권한을 부여하지 않을 때. [postgres@centos7:/home/]$ /usr/pgsql-12/bin/createuser psqluser --interactive 새 롤을 superuser 권한으로 지정할까요? (y/n) n 이 새 롤에게 데이터베이스를 만들 수 있는 권할을 줄까요? (y/n) y 이 새 롤에게 또 다른 롤을 만들 수 있는 권한을 줄까요? (y/n) y
 # 관리자 권한을 부여하지 않을 때.
[postgres@centos7:/home/]$   /usr/pgsql-12/bin/createuser psqluser --interactive
새 롤을 superuser 권한으로 지정할까요? (y/n) n
이 새 롤에게 데이터베이스를 만들 수 있는 권할을 줄까요? (y/n) y
이 새 롤에게 또 다른 롤을 만들 수 있는 권한을 줄까요? (y/n) y
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# SQL을 이용한 사용자 생성
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/psql
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.
postgres=# CREATE USER datauser WITH ENCRYPTED PASSWORD 'password';
CREATE ROLE
postgres=#
# SQL을 이용한 사용자 생성 [postgres@centos7:/home/]$ /usr/pgsql-12/bin/psql psql (12.4) 도움말을 보려면 "help"를 입력하십시오. postgres=# CREATE USER datauser WITH ENCRYPTED PASSWORD 'password'; CREATE ROLE postgres=#
 # SQL을 이용한 사용자 생성
[postgres@centos7:/home/]$   /usr/pgsql-12/bin/psql
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.

postgres=# CREATE USER datauser WITH ENCRYPTED PASSWORD 'password';
CREATE ROLE
postgres=#

데이터 베이스 생성

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/psql
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.
postgres=# CREATE DATABASE data OWNER data ENCODING 'utf-8';
CREATE ROLE
postgres=#
[postgres@centos7:/home/]$ /usr/pgsql-12/bin/psql psql (12.4) 도움말을 보려면 "help"를 입력하십시오. postgres=# CREATE DATABASE data OWNER data ENCODING 'utf-8'; CREATE ROLE postgres=#
[postgres@centos7:/home/]$   /usr/pgsql-12/bin/psql
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.

postgres=# CREATE DATABASE data OWNER data ENCODING 'utf-8';
CREATE ROLE
postgres=#

접속 확인

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
# postgres 가 아닌 다른 계정으로 시도해본다. 원격지에서도 가능하다.
root@centos7:/home/]# psql -U dataware -W -h localhost
# postgres 가 아닌 다른 계정으로 시도해본다. 원격지에서도 가능하다. root@centos7:/home/]# psql -U dataware -W -h localhost
 # postgres 가 아닌 다른 계정으로 시도해본다. 원격지에서도 가능하다.
root@centos7:/home/]#   psql -U dataware -W -h localhost

다음과 같이 입력을 쉘이 변하면 정상 구동 중인 상태

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
암호:
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.
postgres=# select * from pg_tables;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
pg_catalog | pg_statistic | postgres | | t | f | f | f
pg_catalog | pg_type | postgres | | t | f | f | f
pg_catalog | pg_foreign_server | postgres | | t | f | f | f
pg_catalog | pg_authid | postgres | pg_global | t | f | f | f
pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f
pg_catalog | pg_user_mapping | postgres | | t | f | f | f
pg_catalog | pg_subscription | postgres | pg_global | t | f | f | f
pg_catalog | pg_attribute | postgres | | t | f | f | f
pg_catalog | pg_proc | postgres | | t | f | f | f
pg_catalog | pg_class | postgres | | t | f | f | f
... 중략...
information_schema | sql_sizing_profiles | postgres | | f | f | f | f
(70개 행)
암호: psql (12.4) 도움말을 보려면 "help"를 입력하십시오. postgres=# select * from pg_tables; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity --------------------+-------------------------+------------+------------+------------+----------+-------------+------------- pg_catalog | pg_statistic | postgres | | t | f | f | f pg_catalog | pg_type | postgres | | t | f | f | f pg_catalog | pg_foreign_server | postgres | | t | f | f | f pg_catalog | pg_authid | postgres | pg_global | t | f | f | f pg_catalog | pg_statistic_ext_data | postgres | | t | f | f | f pg_catalog | pg_user_mapping | postgres | | t | f | f | f pg_catalog | pg_subscription | postgres | pg_global | t | f | f | f pg_catalog | pg_attribute | postgres | | t | f | f | f pg_catalog | pg_proc | postgres | | t | f | f | f pg_catalog | pg_class | postgres | | t | f | f | f ... 중략... information_schema | sql_sizing_profiles | postgres | | f | f | f | f (70개 행)
암호:
psql (12.4)
도움말을 보려면 "help"를 입력하십시오.

postgres=# select * from pg_tables;
     schemaname     |        tablename        | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog         | pg_statistic            | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_type                 | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_foreign_server       | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_authid               | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog         | pg_statistic_ext_data   | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_user_mapping         | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_subscription         | postgres   | pg_global  | t          | f        | f           | f
 pg_catalog         | pg_attribute            | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_proc                 | postgres   |            | t          | f        | f           | f
 pg_catalog         | pg_class                | postgres   |            | t          | f        | f           | f
... 중략...
 information_schema | sql_sizing_profiles     | postgres   |            | f          | f        | f           | f
(70개 행)