TPCH安装

 

TPCH官方地址： https://www.tpc.org/tpch/

官方网站如果下载不到软件包，可以到这里下载： https://gitee.com/hel10word/tpc-h

TPC-H 工具包的编译安装
提前准备
由于我们下载的 TPC-H 是 .zip 格式，需要安装 unzip 来解压。工具包内部代码基本是 ANSI 'C' 写的，并没有编译，因此我们需要手动编译安装，因此还需要gcc工具。
yum install unzip
yum install gcc

工具包的解压
工具安装好后，我们创建一个目录并将下载的工具包解压。

unzip 85cce4a1-b5b3-451b-8952-f8f08293b1e0-tpc-h-tool.zip
cd 2.18.0_rc2/dbgen
cp makefile.suite Makefile

# 修改makefile 文件 103 109 110 111 这四行的内容
# vim Makefile
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 需要在 tpcd.h 文件中添加部分宏定义
# 如果 DATABASE 字段写的是ORACLE则不需要修改 tpcd.h 文件
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

修改 tpcd.h 文件
vi 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 --help查看代码生成工具的相关参数。
运行如下代码生成1GB数据
./dbgen -vf -s 1

使用如下代码生成100GB数据
./dbgen -vf -s 100

查看生成的数据
ls *.tbl

建表DDL在文件dss.ddl里面

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);

 

导入数据到MySQL
登录MySQL：

mysql -uroot -p，随后输入密码

建库 tpch

切换库 use tpch;

导入数据

LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';
LOAD DATA LOCAL INFILE '/root/2.18.0_rc2/dbgen/supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';

 

如果导入遇到错误：
ERROR 1148 (42000): The used command is not allowed with this MySQL version
登录mysql后，输入命令：
set global local_infile = 'ON';
退出，重新登录即可。

 

 

 

添加约束
alter table tpch.region add primary key(R_REGIONKEY);
alter table tpch.nation add primary key(N_NATIONKEY);
alter table tpch.nation add foreign key nation (N_REGIONKEY) references tpch.region(R_REGIONKEY);
alter table tpch.part add primary key(P_PARTKEY);
alter table tpch.supplier add primary key(S_SUPPKEY);
alter table tpch.supplier add foreign key supplier (S_NATIONKEY) references tpch.nation(N_NATIONKEY);
alter table tpch.partsupp add primary key(PS_PARTKEY,PS_SUPPKEY);
alter table tpch.customer add primary key(C_CUSTKEY);
alter table tpch.customer add foreign key customer(C_NATIONKEY) references tpch.nation(N_NATIONKEY);
alter table tpch.lineitem add primary key(L_ORDERKEY,L_LINENUMBER);
alter table tpch.orders add primary key(O_ORDERKEY);
alter table tpch.partsupp add foreign key partsupp_FK1(PS_SUPPKEY) references tpch.supplier(S_SUPPKEY);
alter table tpch.partsupp add foreign key partsupp_FK2(PS_PARTKEY) references tpch.part(P_PARTKEY);
alter table tpch.orders add foreign key orders(O_CUSTKEY) references tpch.customer(C_CUSTKEY);
alter table tpch.lineitem add foreign key lineitem(L_ORDERKEY) references tpch.orders(O_ORDERKEY);
alter table tpch.lineitem add foreign key lineitem(L_PARTKEY,L_SUPPKEY) references tpch.partsupp(PS_PARTKEY,PS_SUPPKEY);

 

 

 

 

 

文章参考：
站外：
https://www.tpc.org/tpch/
https://gitee.com/hel10word/tpc-h
https://help.aliyun.com/document_detail/252748.html