pg数据库导入TPCH数据
pg数据库导入TPCH数据，Windows和Linux环境均有整理
·
一、安装pg数据库
Linux环境PostgreSQL源码编译安装
在Linux上安装pg数据库可以参考这篇博客
在Windows上安装pg数据库在官网上有简易的安装包
二、下载TPCH数据
可以从官网中下载，但是要填写一大堆资料，还可能半天通不过。
可以直接从下方的百度网盘链接中下载：
链接：https://pan.baidu.com/s/1rJvSA5tjER9oe55EBAR_9A?pwd=szn2
提取码：szn2
三、生成数据
1、若在Windows中生成
首先解压下载的压缩包
用visual studio打开解压的包中的dbgen文件夹。（本人用的visual studio community 2021版）
在解决方案资源管理器中点击dbgen，右键选择生成
此时，在dbgen文件夹下，出现了debug文件夹。在Debug文件夹中，找到dbgen.exe的文件。copy到上一层目录，也就是dbgen文件夹中。
在dbgen文件夹下，打开dos界面。（在文件地址栏输入cmd）。输入命令dbgen -h
然后输入dbgen -vf -s 1就可以生成你想要的数据了。这里1的单位是GB，要生成多少GB就写多少。
生成结束后，就能在dbgen文件夹下面看到生成的数据集(8个.tbl)了
| 表格 |
|---|
| customer.tbl |
| lineitem.tbl |
| nation.tbl |
| order.tbl |
| part.tbl |
| region.tbl |
| supplier.tbl |
2、若在Linux中生成
首先解压
unzip tpc-h-tool-3.0.0.zip
进入解压后的文件夹中的dbgen文件
cd TPC-H_Tools_v3.0.0/dbgen
将 makefile.suite 的复制到 makefile
cp makefile.suite makefile
修改makefile文件
vim makefile
填写其中的四个值
CC=gcc
DATABASE=POSTGRESQL
MACHINE=LINUX
WORKLOAD=TPCH
修改dbgen目录下的tpcd.h文件
vim tpcd.h
在其中添加如下内容
#ifdef POSTGRESQL
#define GEN_QUERY_PLAN "EXPLAIN"
#define START_TRAN "BEGIN TRANSACTION"
#define END_TRAN "COMMIT;"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "LIMIT %d\n"
#define SET_DBASE ""
#endif
编译（在dbgen目录下执行）
make
产生数据
-s 1 表示产生1GB的数据
-f 表示复制原来的数据
比如 ./dbgen -s 100生成100G的数据
./dbgen -s 1 -f
四、处理数据
由于直接生成的数据若直接导入数据库会报错，需要把 .tbl文件中每一行后面的 | 这个竖线符号去掉，所以要对数据进行处理。
在Linux上修改的方法
[root@localhost dbgen]# for i in `ls *.tbl`; do sed 's/|$//' $i > ${i/tbl/csv}; echo $i; done;
customer.tbl
lineitem.tbl
nation.tbl
orders.tbl
partsupp.tbl
part.tbl
region.tbl
supplier.tbl
使用python修改的方法
可以使用我写的程序，将数据集放在tbl文档中
from os import remove
if __name__ == '__main__':
tbl = ["customer", "lineitem",
"nation", "orders", "part",
"partsupp", "region", "supplier"]
for table in tbl:
readfile = open("tbl/" + table + ".tbl", 'r')
writefile = open("tbl/" + table + "1.tbl", 'w')
writefile.flush()
for line in readfile.readlines():
line = line.replace("|\n", "\n")
writefile.write(line)
readfile.close()
writefile.close()
readfile = open("tbl/" + table + "1.tbl", 'r')
writefile = open("tbl/" + table + ".tbl", 'w')
writefile.flush()
txt = readfile.read()
writefile.write(txt)
readfile.close()
writefile.close()
remove("tbl/" + table + "1.tbl")
五、在数据库中建表
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE 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 );
CREATE TABLE 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);
CREATE TABLE 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 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);
CREATE TABLE 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);
CREATE TABLE 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);
六、往数据库中导入数据
把数据集导到一个目录（比如在linux中我是导到/opt/module/tpchdata）中，然后在数据库中执行下列语句导入数据
copy region from '/opt/module/tpchdata/region.tbl' with delimiter as '|' NULL '';
copy nation from '/opt/module/tpchdata/nation.tbl' with delimiter as '|' NULL '';
copy partsupp from '/opt/module/tpchdata/partsupp.tbl' with delimiter as '|' NULL '';
copy customer from '/opt/module/tpchdata/customer.tbl' with delimiter as '|' NULL '';
copy lineitem from '/opt/module/tpchdata/lineitem.tbl' with delimiter as '|' NULL '';
copy orders from '/opt/module/tpchdata/orders.tbl' with delimiter as '|' NULL '';
copy part from '/opt/module/tpchdata/part.tbl' with delimiter as '|' NULL '';
copy supplier from '/opt/module/tpchdata/supplier.tbl' with delimiter as '|' NULL '';
如果pg数据库是装在Windows中，目录就应该是Windows上的数据集存放的位置。
七、添加约束
-- For table REGION
ALTER TABLE REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE NATION
ADD FOREIGN KEY (N_REGIONKEY) references REGION;
COMMIT WORK;
-- For table PART
ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);
COMMIT WORK;
-- For table SUPPLIER
ALTER TABLE SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE SUPPLIER
ADD FOREIGN KEY (S_NATIONKEY) references NATION;
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;
-- For table CUSTOMER
ALTER TABLE CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE CUSTOMER
ADD FOREIGN KEY (C_NATIONKEY) references NATION;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
COMMIT WORK;
ALTER TABLE PARTSUPP
ADD FOREIGN KEY (PS_PARTKEY) references PART;
COMMIT WORK;
-- For table ORDERS
ALTER TABLE ORDERS
ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_ORDERKEY) references ORDERS;
COMMIT WORK;
ALTER TABLE LINEITEM
ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;
COMMIT WORK;
八、生成查询脚本
[root@tpch dbgen]# vim gen_query_sql.sh
#!/bin/sh
# generate queries from query templates with qgen
DIR=.
mkdir $DIR/finals
cp $DIR/queries/*.sql $DIR
for FILE in $(find $DIR -maxdepth 1 -name "[0-9]*.sql")
do
DIGIT=$(echo $FILE | tr -cd '[[:digit:]]')
./qgen $DIGIT > $DIR/finals/$DIGIT.sql
done
rm *.sql
[root@tpch dbgen]# chmod +x gen_query_sql.sh
[root@tpch dbgen]# ./gen_query_sql.sh
[root@tpch dbgen]# ls finals/
10.sql 12.sql 14.sql 16.sql 18.sql 1.sql 21.sql 2.sql 4.sql 6.sql 8.sql
11.sql 13.sql 15.sql 17.sql 19.sql 20.sql 22.sql 3.sql 5.sql 7.sql 9.sql
九、查询的22条语句
存放在dgen\queries下
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)