利用TPC-H为MYSQL生成数据

2023-08-04 10:55:58

利用TPC-H为MYSQL生成数据

导言

这篇文章是看了joyee写的TPC-H数据导入MySQL教程以及另一篇网上的MySQL TPCH测试工具简要手册后写的，有些内容是完全转载自以上两篇文章的，这里我写这篇文章主要写下自己的归纳和更改。

TPC-H简介

TCP-H是一个决策支持的测试基准，由一系列面向商务应用的查询和并发数据修改组成，其选择的查询和组成数据库的数据在商业上都具有广泛的代表性并且易于实现。该基准描述了决策支持系统的三个方面：分析大量的数据，执行高复杂度的查询，回答关键的商业问题。

我主要是想用TPC-H生成数据库样本，用于进行数据库实验。但是TPC-H是不支持为MYSQL生成数据的，所以我们需要对它的配置文件进行一些“魔改”。

TPC-H的安装包可以从官网免费获取。

过程

1、解压和编译

解压tpch文件，进入dbgen目录。

首先，我们需要更改TPC-H编译文件，

cp makefile.suite makefile

接着修改makefile文件

将103~112行改成以下：

CC      = gcc

# 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

DATABASE = MYSQL

MACHINE = LINUX

WORKLOAD = TPCH

第一行的意思是设定C语言编译器为gcc。

我们从这里也看到，DATABASE其实是没有MYSQL选项的，所以，我们后面需要为MYSQL添加依赖。

修改tpcd.h

在文件末尾加上

#ifdef MYSQL

#define GEN_QUERY_PLAN  ""

#define START_TRAN      "START TRANSACTION"

#define END_TRAN        "COMMIT"

#define SET_OUTPUT      ""

#define SET_ROWCOUNT    "limit %d;\n"

#define SET_DBASE       "use %s;\n"

#endif

修改完编译配置文件，我们可以编译出程序。

命令行执行make

这个过程中会有一些关于数据类型的警告，无视它。

make完dbgen目录下之后就会多出很多.o 文件，dbgen、qgen可执行程序，dss.ri，dss.dll文件。

2.生成数据

dbgen：数据生成工具。可以用该工具生成tpch相关表数据。

我们使用dbgen生成数据，共生成8个表(.tbl)。

./dbgen -s 1

-s 1表示生产1G数据，我们也可以根据我们需要更改大小。

3.修改相关脚本

接着，

dss.ddl – DSS库初始化DDL脚本

dss.ri – DSS数据表创建索引、外键脚本

由于我们用的是MYSQL，所以需要对这些脚本进行适当修改。

dss.dll 需要在开头添加

drop database tpch;

create database tpch;

use tpch;

成品文件 https://gist.github.com/MR-HOWE/9cd5c3438a1e2c79c66d2526e3ae30e3

dss.ri 的情况复杂一些。

首先，更改第6~13行命令（原来的与MYSQL不兼容），在所有的SQL注释符 “--” 后面再加一个空格

-- ALTER TABLE TPCD.REGION DROP PRIMARY KEY;

-- ALTER TABLE TPCD.NATION DROP PRIMARY KEY;

-- ALTER TABLE TPCD.PART DROP PRIMARY KEY;

-- ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;

-- ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;

-- ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;

-- ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;

-- ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;

接着，因为MySQL里添加外键要指明键的名字，所以每个加外键的指令都需要改。

修改第25行：

ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION;

=>

ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION(R_REGIONKEY); 

修改第40行：

ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION;

=>

ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION(N_NATIONKEY);

修改第55行：

ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION;

=>

ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION(N_NATIONKEY); 

修改第73行：

ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER;

=>

ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER(S_SUPPKEY);

修改第78行：

ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART;

=>

ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART(P_PARTKEY);

修改第84行：

ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER;

=>

ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER(C_CUSTKEY);

修改第90行：

ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references TPCD.ORDERS;

=>

ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references TPCD.ORDERS(O_ORDERKEY);

修改第96行：

TPCD.PARTSUPP;

=>

TPCD.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);

另外，由于TPC-H生成的表名是大写的，基于MYSQL的标准，我们最好把表名修改为小写的，因此在文件末尾添加：

ALTER TABLE CUSTOMER rename to customer;

ALTER TABLE LINEITEM rename to lineitem;

ALTER TABLE NATION rename to nation;

ALTER TABLE ORDERS rename to orders;

ALTER TABLE PART rename to part;

ALTER TABLE PARTSUPP rename to partsupp;

ALTER TABLE REGION rename to region;

ALTER TABLE SUPPLIER rename to supplier;

成品文件 https://gist.github.com/MR-HOWE/18dc736352183f3a3447db8f49b397d1

接着打开mysql，执行

mysql> \. 路径/dss.ddl

然后可以用

mysql> SHOW DATABASES;

如果看到tcph数据库，所以建库成功。

再执行

mysql> USE tpch;

mysql> SHOW TABLES;

可以看到生成的8个表就说明建表成功。

下一步需要添加外键和主键，执行

mysql> \. 路径/dss.ri

4.导入数据

现在，数据库tcph只是建好了表，设定好了主键和外键，内容还是为空。我们需要将之前生成的tbl文件导入。

8个表就需要输入执行8条命令，这里joyee写了一个脚本，可以方便的生成sql命令，脚本如下（这里表示要向大佬学习，发挥“偷懒精神”）

#!/bin/bash

write_to_file()

{

    file="loaddata.sql"

    if [ ! -f "$file" ] ; then

        touch "$file"

    fi

    echo 'USE tpch;' >> $file

    DIR=`pwd`

    for tbl in `ls *.tbl`; do

        table=$(echo "${tbl%.*}" | tr '[:lower:]' '[:upper:]')

        echo "LOAD DATA LOCAL INFILE '$DIR/$tbl' INTO TABLE $table" >> $file

        echo "FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';" >> $file

    done

 }

write_to_file

这里由于我之前把表名改为小写了，所以我们不需要大写表名。同时，因为有外键约束，表的导入需要安装约束顺序来，为了方便导入，我们可以先把外键约束关了，导入完表再重新打开外键约束，代码如下：

#!/bin/bash

write_to_file()

{

    file="loaddata.sql"

    if [ ! -f "$file" ] ; then

        touch "$file"

    fi

    echo 'USE tpch;' >> $file

    echo 'SET FOREIGN_KEY_CHECKS=0;' >> $file

    DIR=`pwd`

    for tbl in `ls *.tbl`; do

        table=$(echo "${tbl%.*}")

        echo "LOAD DATA LOCAL INFILE '$DIR/$tbl' INTO TABLE $table" >> $file

        echo "FIELDS TERMINATED BY '|' LINES TERMINATED BY '|\n';" >> $file

    done

    echo 'SET FOREIGN_KEY_CHECKS=1;' >> $file

 }

write_to_file

将以上代码保存到dbgen目录下，保存为“load.sh”，然后执行

sh load.sh

同目录下就会生成一个loaddata.sql，里面是从8个tbl里导入数据的sql指令。

在dbgen目录下运行

mysql -u root -p < loaddata.sql即可。

(这里如果运行有问题看看试试joyee说的加上 --local-infile。mysql --local-infile -u root -p < loaddata.sql)

码农公寓