在PostgreSQL上安装TPC-H（其他数据库也适用）_sql

概述1、下载tpch 在这个网站下载： https://github.com/electrum/tpch-dbgen 解压： unzip tpch_2_16_0v1.zip 2、安装tpch $cd tpch_2_17_0/dbgen $vim makefile.suite 更改其中参数： CC = gcc DATABASE =SQLSERVER VECTORWISE =LINUX WORKLOAD

1、下载tpch
在这个网站下载：
https://github.com/electrum/tpch-dbgen
解压：
unzip tpch_2_16_0v1.zip

2、安装tpch
$cd tpch_2_17_0/dbgen
$vim makefile.suite
更改其中参数：
CC = gcc
DATABASE =sqlSERVER
VECTORWISE =liNUX
WORKLOAD =TPCH

3、安装
$make

4、生成1G的数据
./dbgen -s 1 -f -T L

5、连接PG数据库test（用postgres用户）
/psql test

6、创建表
创建表语句在dss.dll中，找到打开并复制
gedit dss.dll
将复制的创建表语句黏贴出来：

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,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,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,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,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,L_EXTENDEDPRICE DECIMAL(15,L_disCOUNT DECIMAL(15,L_TAX DECIMAL(15,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);

7、加工数据
1）将测试数据转换为postgresql识别的格式，删除末尾的分隔符|
$for i in `ls *.tbl`; do sed ‘s/|$//’ $i > ${i/tbl/csv}; done
2）查看刚才另存为csv格式的数据是否存在
$ll -rth *.csv
3）把包含csv文件的目录，软链接到/tmp/dss-data。tpch-pg脚本中一会要用到这个目录
\$pwd
/home/digoal/tpch/tpch_2_17_0/dbgen
\$ln -s /home/digoal/tpch/tpch_2_17_0/dbgen /tmp/dss-data

8、导入数据

test=# \copy customer from '/home/scIDb/Downloads/tpch-dbgen-master/customer.csv'with CSV DEliMITER '|';copY 150000test=# \copy supplier from '/home/scIDb/Downloads/tpch-dbgen-master/supplier.csv'with CSV DEliMITER '|';copY 10000test=# \copy nation from '/home/scIDb/Downloads/tpch-dbgen-master/nation.csv'with CSV DEliMITER '|';copY 25test=# \copy region from '/home/scIDb/Downloads/tpch-dbgen-master/region.csv'with CSV DEliMITER '|';copY 5test=# \copy part from '/home/scIDb/Downloads/tpch-dbgen-master/part.csv'with CSV DEliMITER '|';copY 200000test=# \copy partsupp from '/home/scIDb/Downloads/tpch-dbgen-master/partsupp.csv'with CSV DEliMITER '|';copY 800000test=# \copy order from '/home/scIDb/Downloads/tpch-dbgen-master/order.csv'with CSV DEliMITER '|';/home/scIDb/Downloads/tpch-dbgen-master/order.csv: No such file or directorytest=# \copy orders from '/home/scIDb/Downloads/tpch-dbgen-master/orders.csv'with CSV DEliMITER '|';copY 1500000test=# \copy lineitem from '/home/scIDb/Downloads/tpch-dbgen-master/lineitem.csv'with CSV DEliMITER '|';copY 6001215

9、报错
ERROR: Could not open file “/home/scIDb/Downloads/tpch-dbgen-master/customer.csv” for reading: Permission denIEd
解决：（全用过，不知道哪个管用了，都写下来）
1） chmod a+rX /home/scIDb/Downloads /home/scIDb/Downloads/tpch-dbgen-master /home/scIDb/Downloads/tpch-dbgen-master/customer.csv
2） chown postgres /home/scIDb/Downloads/tpch-dbgen-master/customer.csv
3）最重要的是不能直接写copy！要写/copy！

10、添加外键、主键
载入数据后，就可以添加外键、主键；对每张表添加外键、主键，修改tpch-dbgen文件夹中的dss.ri(用文本编辑器打开)，将添加外键和主键的信息都按照以下修改，并注释掉图上的connect to tpcd

-- For table REGIONALTER table REGION ADD PRIMARY KEY (R_REGIONKEY);-- For table NATIONALTER table NATION ADD PRIMARY KEY (N_NATIONKEY);ALTER table NATION ADD FOREIGN KEY (N_REGIONKEY) references REGION;COMMIT WORK;-- For table PARTALTER table PART ADD PRIMARY KEY (P_PARTKEY);COMMIT WORK;-- For table supplierALTER table supplier ADD PRIMARY KEY (S_SUPPKEY);ALTER table supplier ADD FOREIGN KEY (S_NATIONKEY) references NATION;COMMIT WORK;-- For table PARTSUPPALTER table PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);COMMIT WORK;-- For table CUSTOMERALTER table CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);ALTER table CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) references NATION;COMMIT WORK;-- For table liNEITEMALTER table liNEITEM ADD PRIMARY KEY (L_ORDERKEY,L_liNENUMBER);COMMIT WORK;-- For table ORDERSALTER table ORDERS ADD PRIMARY KEY (O_ORDERKEY);COMMIT WORK;-- For table PARTSUPPALTER 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 ORDERSALTER table ORDERS ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;COMMIT WORK;-- For table liNEITEMALTER 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;

11、测试语句
1）创建一个querIEs目录，用于存放转换后的tpc-h 测试sql
$mkdir dss/querIEs
2）生成测试sql

$for q in `seq 1 22`do    DSS_query=dss/templates ./qgen $q >> dss/querIEs/$q.sql    sed 's/^select/explain select/' dss/querIEs/$q.sql > dss/querIEs/$q.explain.sql    cat dss/querIEs/$q.sql >> dss/querIEs/$q.explain.sql;done