TPC-H数据仓库基准测试工具(Linux\mysql)

（官网下载地址）
此博文仅为笔录，方便回忆。

#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