PostgreSQL导入TPC-H测试集

最新推荐文章于 2024-09-02 09:46:17 发布

bournehuang

最新推荐文章于 2024-09-02 09:46:17 发布

阅读量374

点赞数

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/Lemon_QiQi/article/details/132412124

版权

PostgreSQL导入TPC-H测试集

本机环境：WSL2 Ubuntu20.04

1 工具准备

electrum/tpch-dbgen: TPC-H dbgen (github.com)

git clone git@github.com:electrum/tpch-dbgen.git
cd tpch-dbgen
make -j #自行准备好C的编译环境

编译完成后，会生成dbgen和qgen可执行文件，分别用于生成测试数据和SQL，本文介绍dbgen的使用

2 生成测试数据

dbgen的使用可通过./dbgen -help获取，本文生成1GB的测试数据：

./dbgen -vf -s 1

目录下会生成8个表的tbl数据，以.tbl结尾。生成的tbl数据每一行的末尾会有一个“|”，导致PG数据库读取时报错，需要将最后一个“|”去掉。写一个脚本process.sh进行处理，内容如下：

for i in `ls *.tbl`
do
 name="tbl/$i"
 echo $name
 `touch $name`
 `chmod 777 $name`
 sed 's/|$//' $i >> $name;
done

执行完脚本后，会将处理后的数据文件放到tbl目录下，记住该目录的位置供导入使用

3 导入数据

首先，登入pg数据库，建立tpch数据库，然后建表，建表语句可参考dss.ddl文件，直接复制执行：

-- Sccsid:     @(#)dss.ddl	2.1.8.1
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);

然后导入数据，在PG中执行如下语句，将from后的目录换成自己的路径：

copy region from '/home/hpq/tpch-dbgen/tbl/region.tbl' with delimiter as '|' NULL '';
copy nation from '/home/hpq/tpch-dbgen/tbl/nation.tbl' with delimiter as '|' NULL '';
copy partsupp from '/home/hpq/tpch-dbgen/tbl/partsupp.tbl' with delimiter as '|' NULL '';
copy customer from '/home/hpq/tpch-dbgen/tbl/customer.tbl' with delimiter as '|' NULL '';
copy lineitem from '/home/hpq/tpch-dbgen/tbl/lineitem.tbl' with delimiter as '|' NULL '';
copy orders from '/home/hpq/tpch-dbgen/tbl/orders.tbl' with delimiter as '|' NULL '';
copy part from '/home/hpq/tpch-dbgen/tbl/part.tbl' with delimiter as '|' NULL '';
copy supplier from '/home/hpq/tpch-dbgen/tbl/supplier.tbl' with delimiter as '|' NULL '';

4 导入主键和外键

见dss.ri文件，为了适配pg需要做处理，或者直接执行下面的语句：

ALTER TABLE REGION ADD PRIMARY KEY (R_REGIONKEY);
ALTER TABLE NATION ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE PART ADD PRIMARY KEY (P_PARTKEY);
ALTER TABLE SUPPLIER ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE PARTSUPP ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
ALTER TABLE CUSTOMER ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE LINEITEM ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
ALTER TABLE ORDERS ADD PRIMARY KEY (O_ORDERKEY);

ALTER TABLE NATION ADD FOREIGN KEY (N_REGIONKEY) references REGION;
ALTER TABLE SUPPLIER ADD FOREIGN KEY (S_NATIONKEY) references NATION;
ALTER TABLE CUSTOMER ADD FOREIGN KEY (C_NATIONKEY) references NATION;
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_SUPPKEY) references SUPPLIER;
ALTER TABLE PARTSUPP ADD FOREIGN KEY (PS_PARTKEY) references PART;
ALTER TABLE ORDERS ADD FOREIGN KEY (O_CUSTKEY) references CUSTOMER;
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_ORDERKEY)  references ORDERS;
ALTER TABLE LINEITEM ADD FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references PARTSUPP;

5 查询语句

github项目中的queries文件夹中以及提供了22类查询，有关介绍参见：TPC-H（二）：22个SQL语句说明（基于TPC-H2.17.3版本）_LCYong_的博客-CSDN博客

Q01 统计查询

Q02 WHERE条件中，使用子查询(=)

Q03 多表关联统计查询，并统计(SUM)

Q04 WHERE条件中，使用子查询(EXISTS)，并统计(COUNT)

Q05 多表关联查询(=)，并统计(SUM)

Q06 条件(BETWEEN AND)查询，并统计(SUM)

Q07 带有FROM子查询，从结果集中统计(SUM)

Q08 带有FROM多表子查询，从结果集中的查询列上带有逻辑判断(WHEN THEN ELSE)的统计(SUM)

Q09 带有FROM多表子查询，查询表中使用函数(EXTRACT)，从结果集中统计(SUM)

Q10 多表条件查询(>=, <)，并统计(SUM)

Q11 在GROUP BY中使用比较条件(HAVING >)，比较值从子查询中查出

Q12 带有逻辑判断(WHEN AND/ WHEN OR)的查询，并统计(SUM)

Q13 带有FROM子查询，子查询中使用外联结

Q14 使用逻辑判断(WHEN ELSE)的查询

Q15 使用视图和表关联查询

Q16 在WHERE子句中使用子查询，使用IN/ NOT IN判断条件，并统计(COUNT)

Q17 在WHERE子句中使用子查询，使用<比较，使用了AVG函数

Q18 在WHERE子句中使用IN条件从子查询结果中比较

Q19 多条件比较查询

Q20 WHERE条件子查询(三层)

Q21 在WHERE条件中使用子查询，使用EXISTS和NOT EXISTS判断

Q22 在WHERE条件中使用判断子查询、IN、NOT EXISTS，并统计(SUM、COUNT)查询结果