(官网下载地址)
此博文仅为笔录,方便回忆。
#unzip tpc-h-tool.zip
#cd tpch_2_17_0/dbgen
#cp makefile.suite makefile
#vim makefile
添加内容:
CC=gcc
DATABASE=MYSQL
MACHINE=LINUX
WORKLOAD=TPCH
#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
# 建表文件
# ls *.tbl
# 生成测试数据
#./dbgen -s 1 -vf
s 描述数据量,1对应1 GB 单位,由低到高依次是1、10、30、100、300、1 000、3 000、10 000。
# 生成测试脚本
# cp ./queries/*.sql ./
# ./qgen -s 1 > query.sql
mysql库建表语句
create table part (
p_partkey integer not null,
p_name varchar(55) ,
p_mfgr char(25) ,
p_brand char(10) ,
p_type varchar(25) ,
p_size integer ,
p_container char(10) ,
p_retailprice decimal(15,2) ,
p_comment varchar(23) );
create table nation (
n_nationkey integer not null,
n_name char(25) ,
n_regionkey integer not null,
n_comment varchar(152));
create table region (
r_regionkey integer not null,
r_name char(25) ,
r_comment varchar(152));
create table supplier (
s_suppkey integer not null,
s_name char(25) ,
s_address varchar(40) ,
s_nationkey integer not null,
s_phone char(15) ,
s_acctbal decimal(15,2) ,
s_comment varchar(101) );
create table partsupp (
ps_partkey integer not null,
ps_suppkey integer not null,
ps_availqty integer ,
ps_supplycost decimal(15,2) ,
ps_comment varchar(199) );
create table customer (
c_custkey integer not null,
c_name varchar(25) ,
c_address varchar(40) ,
c_nationkey integer not null,
c_phone char(15) ,
c_acctbal decimal(15,2) ,
c_mktsegment char(10) ,
c_comment varchar(117) );
create table orders (
o_orderkey integer not null,
o_custkey integer not null,
o_orderstatus char(1) ,
o_totalprice decimal(15,2) ,
o_orderdate date ,
o_orderpriority char(15) ,
o_clerk char(15) ,
o_shippriority integer ,
o_comment varchar(79) );
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) ,
l_extendedprice decimal(15,2) ,
l_discount decimal(15,2) ,
l_tax decimal(15,2) ,
l_returnflag char(1) ,
l_linestatus char(1) ,
l_shipdate date ,
l_commitdate date ,
l_receiptdate date ,
l_shipinstruct char(25) ,
l_shipmode char(10) ,
l_comment varchar(44) );
#装载数据
load data local infile '/home/tpch_2_17_0/dbgen/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\n';
load data local infile '/home/tpch_2_17_0/dbgen/lineitem.tbl' into table lineitem fields terminated by '|' lines terminated by '|\n';
load data local infile '/home/tpch_2_17_0/dbgen/nation.tbl' into table nation fields