Compiling and Installing the TPC-H Test Tools Package

Before compiling and installing the tools package, you need to modify it.

Save the package to the /home directory or another directory in the MySQL environment. The disk space must be greater than the size of the .tbl data file generated in 5.

Modify the makefile file.

Decompress the .zip file, go to the dbgen directory and open the makefile.suite file. tpch-pq.zip is the name of the package, and /home/tpch-pq/2.18.0_rc2/dbgen/ is the dbgen directory. Set them as required.

         
              unzip tpch-pq.zip
cd /home/tpch-pq/2.18.0_rc2/dbgen/
vim makefile.suite

Press i to enter the insert mode and modify lines 103 to 112 as follows:

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
#

Press Esc, type :wq!, and press Enter to save the file and exit.
Change the file name from makefile.suite to makefile. Therefore, the modified content can be correctly applied when the make command is used in 4.b.
1

cp makefile.suite makefile

Modify the tpcd.h file.

Open the tpcd.h file.
1

vim tpcd.h

Press i to enter the insert mode and add the following macro definition to the top of the file:

         
              #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

Press Esc, type :wq!, and press Enter to save the file and exit.

Generate the dbgen executable file.

Go to the dbgen directory. /home/tpch-pq/2.18.0_rc2/dbgen/ is the dbgen directory. Set it as required.

         
              cd /home/tpch-pq/2.18.0_rc2/dbgen/

Run the make command. Then, you will see many .o files and a dbgen executable file in the dbgen folder.
1

make

In the dbgen directory, run the following command to generate a .tbl data file. In this example, -s 1 indicates that 1 GB data is to be generated. To generate 10 GB data, change 1 to 10.
1

./dbgen -s 1
After the above command is executed, the dbgen tool starts to generate a data file and save it in the current directory. The generated data file contains eight tables: supplier.tbl, region.tbl, part.tbl, partsupp.tbl, orders.tbl, nation.tbl, lineitem.tbl and customer.tbl.

Modify the initialization scripts.

The package contains two scripts: dss.ddl for creating tables, and dss.ri for associating primary keys and foreign keys in tables. These scripts can be used in MySQL only after being modified.

Modify dss.ddl

To establish a MySQL database connection, you need to add commands at the beginning of dss.ddl. Add the following code at the beginning of the script:

         
              DROP DATABASE tpch;
CREATE DATABASE tpch;
USE tpch;

The test table name in TPC-H is in lowercase, but the table name in dss.ddl is in uppercase. To ensure consistency, you are advised to change the table name to lowercase.
Open dss.ddl.
1

vi dss.ddl
Press ESC to enter the CLI mode, enter the following command to change the table name to lowercase and press Enter.
```
:%s/TABLE\(.*\)/TABLE\L\1
```

Modify dss.ri

Open dss.ri.
1

vi dss.ri

Press i to enter the insert mode and replace the original content with the following content:

For the following eight lines -- ALTER TABLE tpch.* DROP PRIMARY KEY;, make sure that the first three characters are "-- ", that is, two hyphens and a space.
Format: -- ALTER TABLE tpch.* DROP PRIMARY KEY;

-- Sccsid:     @(#)dss.ri   2.1.8.1
-- tpch Benchmark Version 8.0

USE tpch;

-- ALTER TABLE tpch.region DROP PRIMARY KEY;
-- ALTER TABLE tpch.nation DROP PRIMARY KEY;
-- ALTER TABLE tpch.part DROP PRIMARY KEY;
-- ALTER TABLE tpch.supplier DROP PRIMARY KEY;
-- ALTER TABLE tpch.partsupp DROP PRIMARY KEY;
-- ALTER TABLE tpch.orders DROP PRIMARY KEY;
-- ALTER TABLE tpch.lineitem DROP PRIMARY KEY;
-- ALTER TABLE tpch.customer DROP PRIMARY KEY;


-- For table region
ALTER TABLE tpch.region
ADD PRIMARY KEY (R_REGIONKEY);

-- For table nation
ALTER TABLE tpch.nation
ADD PRIMARY KEY (N_NATIONKEY);

ALTER TABLE tpch.nation
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references
tpch.region(R_REGIONKEY);

COMMIT WORK;

-- For table part
ALTER TABLE tpch.part
ADD PRIMARY KEY (P_PARTKEY);

COMMIT WORK;

-- For table supplier
ALTER TABLE tpch.supplier
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE tpch.supplier
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references
tpch.nation(N_NATIONKEY);

COMMIT WORK;

-- For table partsupp
ALTER TABLE tpch.partsupp
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);

COMMIT WORK;

-- For table customer
ALTER TABLE tpch.customer
ADD PRIMARY KEY (C_CUSTKEY);

ALTER TABLE tpch.customer
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references
tpch.nation(N_NATIONKEY);

COMMIT WORK;

-- For table lineitem
ALTER TABLE tpch.lineitem
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

COMMIT WORK;

-- For table orders
ALTER TABLE tpch.orders
ADD PRIMARY KEY (O_ORDERKEY);

COMMIT WORK;

-- For table partsupp
ALTER TABLE tpch.partsupp
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references
tpch.supplier(S_SUPPKEY);
COMMIT WORK;

ALTER TABLE tpch.partsupp
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references
tpch.part(P_PARTKEY);

COMMIT WORK;

-- For table orders
ALTER TABLE tpch.orders
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references
tpch.customer(C_CUSTKEY);

COMMIT WORK;

-- For table lineitem
ALTER TABLE tpch.lineitem
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references
tpch.orders(O_ORDERKEY);

COMMIT WORK;

ALTER TABLE tpch.lineitem
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references
tpch.partsupp(PS_PARTKEY,PS_SUPPKEY);

COMMIT WORK;

Press Esc, type :wq!, and press Enter to save the file and exit.

Create the tpch database and data table, and import the DDL file to the MySQL database. In this example, /home/tpch-pq/2.18.0_rc2 is the actual path on the server. Change the path as required.
```
\. /home/tpch-pq/2.18.0_rc2/dbgen/dss.ddl
```
Check the database. Run the following statement to check whether the database is successfully created. You can see that a database named tpch is added.
1

SHOW DATABASES;
Import the dds.ri file to the MySQL database. In this example, /home/tpch-pq/2.18.0_rc2 is the actual path on the server. Change the path as required.
1

\. /home/tpch-pq/2.18.0_rc2/dbgen/dss.ri

Import data.

Create a load.sh script file in the dbgen directory.
1

vi load.sh

Press i to enter the insert mode and enter the following code. tpch is the database name. Change it as required.

         
              #!/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

Press Esc, type :wq!, and press Enter to save the file and exit.
Execute the load.sh script.
1

sh load.sh
A loaddata.sql file is generated in the same directory. The file contains the SQL statements used for importing data from the eight .tbl tables.

Import data:

         
              mysql --local-infile -uroot -p123456 -S /data/mysql/run/mysql.sock < loaddata.sql

The time for importing data varies, depending on the size of the data to be imported. Please wait.

Check whether the import is successful. You can log in to the database to check the eight tables. If the tables contain data, the data is successfully imported.

Generate the SQL query statement in the dbgen directory.

Copy the qgen execution file and the dists.dss script to the directory of the queries template.
1

cp qgen dists.dss queries/
Run the ll command. The output shows that the copy is successful.
1

ll
Create the saveSql folder in the upper-level directory of dbgen.
1

mkdir ../saveSql
Go to the queries directory.
1

cd queries

Generate the SQL query statement in the queries directory.

To generate 22 SQL statements, run the following command:

for i in {1..22};do ./qgen -d ${i} > ../../saveSql/${i}.sql;done

To generate a specified SQL statement, run the command with the corresponding sequence number. For example, to generate the query statement of 1.sql, run the following command:
```
./qgen -d 1 > ../../saveSql/1.sql
```

The TPC-H does not adapt to MySQL. Therefore, the generated .sql files cannot run on MySQL. To make the files adapt to MySQL, open the generated SQL files in the saveSql directory, modify the files based on Table 1, and save the modifications. In this example, the path of the .sql files is /home/tpch-pq/2.18.0_rc2/saveSql/.

Table 1 SQL file modification description

File Name

Modification Description

1.sql

Delete (3) behind day.

1.sql, 4.sql, 5.sql, 6.sql, 7.sql, 8.sql, 9.sql, 11.sql, 12.sql, 13.sql, 14.sql, 15.sql, 16.sql, 17.sql, 19.sql, 20.sql, and 22.sql

Delete limit -1; from the last line.

You can run the following command to quickly delete it in the saveSql directory:

              
                   sed -i "s/limit\ -1;//g" *.sql

2.sql, 3.sql, 10.sql, 18.sql, and 21.sql

Delete the semicolon (;) in the second line from the bottom.

For example, delete (3) and limit -1; from the 1.sql file.

Parent topic: Using TPC-H