TPCH生成数据导入Postgres数据库

1. 数据生成

工具下载

git clone git@github.com:electrum/tpch-dbgen.git
1

在degen目录下修改makefile

################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc 
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS, 
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32 
# Current values for WORKLOAD are:  TPCH
DATABASE= POSTGRESQL
MACHINE = LINUX
WORKLOAD = TPCH
1
2
3
4
5
6
7
8
9
10
11
12

在degen目录下修改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
1
2
3
4
5
6
7
8

在degen目录下执行命令生成dbgen和qgen文件

make -f makefile.suite
1

在degen目录下生成.tlb数据

 ./dbgen -s 1 -f
1

-s 1 表示生成1G数据
-f 覆盖之前产生的文件

查看生成的数据

ls | grep '.*.tbl'
1

2. 数据导入到Postgres数据库中

创建数据库

createdb -p 5432 -e -U postgres tpch
1

-p 表示连接端口 5432
-e 表示命令进行回显
-U 创建数据库的用户名 是postgres
tpch 数据库的名字

建表

表结构在degen目录下dss.ddl文件中

psql -d tpch < dss.ddl
1

查看创建的表

psql -d tpch -c '\dt+'
1

表中导入数据

for i in `ls *.tbl`; do
    echo $i;
    sed -i 's/|$//' *.tbl;
    name=`echo $i| cut -d'.' -f1`;
    psql -d tpch -c "COPY $name FROM '`pwd`/$i' DELIMITER '|' ENCODING 'LATIN1';";
done
1
2
3
4
5
6

查看数据导入

psql -d tpch -c '\dt+'
1

可以看到表中已经有了数据

# 查看表的行数
psql -d tpch  -c "select count(*) from lineitem";
1
2

给表加约束

约束在dss.ri 文件, 需要对文件中TPCD修改为你的数据库名字，也可以直接使用下面的在数据库中执行。

-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

3. 生成查询语句

生成查询语句

复制qgen 和dists.dss 到queries ,cd到queries目录下执行

./qgen -d 1 > d1.sql
1

-d表示默认参数，1表示按照模板一生成sql语句,d1.sql为生成的sql文件名称
批量生成sql文件

for i  in {1..22}
do
name="d$i.sql"
echo $name
./qgen -d $i >$name
done
1
2
3
4
5
6

执行查询语句

psql -p 5432 -U postgres -d tpch -f ./d1.sql
1

查询模板说明

Q01 带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%行被读取到。

Q02 ：带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

Q03 带有分组、排序、聚集操作并存的三表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。

Q04 带有分组、排序、聚集操作、子查询并存的单表查询操作。子查询是相关子查询。

Q05 带有分组、排序、聚集操作、子查询并存的多表连接查询操作。

Q06 带有聚集操作的单表查询操作。查询语句使用了BETWEEN-AND操作符，有的数据库可以对BETWEEN-AND进行优化。

Q07 带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。

Q08 带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。

Q09 带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。

Q10 带有分组、排序、聚集操作并存的多表连接查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。

Q11 带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。

Q12 带有分组、排序、聚集操作并存的两表连接查询操作。

Q13 带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

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

Q15 带有分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。

Q16 带有分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作。

Q17 带有聚集、聚集子查询操作并存的两表连接操作。

Q18 带有分组、排序、聚集、IN子查询操作并存的三表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

Q19 带有分组、排序、聚集、IN子查询操作并存的三表连接操作。

Q20带有排序、聚集、IN子查询、普通子查询操作并存的两表连接操作。

Q21 带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

Q22 带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。

TPCH度量指标

测试中测量的基础数据都与执行时间有关，这些时间又可分为：装载数据的每一步操作时间、每个查询执行时间和每个更新操作执行时间，由这些时间可计算出：数据装载时间、QphH@Size, Power@Size, and Throughput@Size.

• 装载数据时间
  装载数据的全过程有记时操作和不记时操作之分，记时操作必须测量所用时间，并计入到数据装载时间中。一般情况下，需要记时的操作有建表、插入数据和建立索引。

• 查询和更新时间
  在Power 测试和Throughput 测试中所有查询和更新流的时间必须被测量和记录，每个查询时间的计时是从被提交查询的第一个字符开始到获得查询结果最后一个字符的时间为止。更新时间要分别测量RF1 和RF2 的时间，是从提交操作开始到完成操作结束的时间。

• Power@Size
  Power@Size 是Power 测试的结果，被定义为查询时间和更改时间的几何平均值的倒数，Power@Size计算DBMS计算单个查询到得出结果的速度，公式如下：
  
  其中：Size 为数据规模；SF 为数据规模的比例因子；QI （i，0）为第 i个查询的时间，以秒为单位；RI(j , 0)为 RFj更新的时间，以秒为单位。

• Throughput@Size
  Throughput@Size 是Throughput 测试的结果，被定义为所有查询执行时间平均值的倒数，公式如下：
  
  其中，S是执行的查询流的数量，Ts是运行s流的吞吐量测试所需的总时间。

• QphH@Size
  

  每小时查询性能(QphH@Size)度量，它是从前两个指标的几何平均值中获得的，并反映了数据库处理查询的能力的多个方面。

参考博客

链接: https://www.cnblogs.com/joyeecheung/p/3599698.html
链接: https://blog.csdn.net/hehong_78/article/details/6091011
链接: https://blog.csdn.net/leixingbang1989/article/details/8766047
链接: https://blog.csdn.net/weixin_30329623/article/details/101092449
链接: https://blog.csdn.net/iteapoy/article/details/104214119