tpc-h数据导入mysql_在Linux下将TPC-H数据导入到MySQL

一、下载TPC-H

下载地址：http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp 。从这个页面中找到tpc-h，我下载的版本是v2.17.3。下载后会得到一个名为*-tpc-h-tool.zip的压缩包，使用unzip命令将压缩包解压并将解压后的文件夹重命名为tpch_2_17_3, 文件夹tpch_2_7_13结构如下图所示：

操作主要在文件夹dbgen下进行。

二、修改makefile.suite

执行以下命令(注意当前的工作目录)：

hadoop@sench-PC:~/Downloads/tpch_2_17_3/dbgen$ cpmakefile.suite makefile

hadoop@sench-PC:~/Downloads/tpch_2_17_3/dbgen$ vi makefile

将makefile的第100行到第111行修改为如下代码：

################

## CHANGE NAME OF ANSI COMPILER HERE

################

CC=gcc

# Current valuesforDATABASE are: INFORMIX, DB2, TDAT (Teradata)

# SQLSERVER, SYBASE, ORACLE, VECTORWISE

# Current valuesforMACHINE are: ATT, DOS, HP, IBM, ICL, MVS,

# SGI, SUN, U2200, VMS, LINUX, WIN32

# Current valuesforWORKLOAD are: TPCH

DATABASE=MYSQL

MACHINE=LINUX

WORKLOAD= TPCH

修改的地方为：将编译器设置为gcc，将WORKLOAD设为TPCH，以及给DATABASE、MACHINE、WORKLOAD赋值。

三、修改文件tpcd.h

在文件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

四、生成dbgen

执行以下命令：

hadoop@sench-PC:~/Downloads/tpch_2_17_3/dbgen$ make

执行完成后会在dbgen目录下发现一个可执行文件dbgen

五、生成.tbl数据文件

这一步要使用上一步生成的dbgen来生成数据，这些数据存储在8个表中(.tbl),执行以下命令：

hadoop@sench-PC:~/Downloads/tpch_2_17_3/dbgen$ ./dbgen -s 1

-s -1表示生成1G的数据 (如果你之前曾经尝试过生成数据，最好先make clean，再重新make，接着到这步加上-f覆盖掉)。完成后我们使用 ls *.tbl 可以看到产生了8个tbl文件：

hadoop@sench-PC:~/Downloads/tpch_2_17_3/dbgen$ ls *.tbl

customer.tbl nation.tbl partsupp.tbl region.tbl

lineitem.tbl orders.tbl part.tbl supplier.tbl

查看.tbl文件可以看到文件中的数据以‘|’隔开(类似于csv)。

六、修改初始化脚本

tpch提供了两个脚本dss.ddl (DSS库初始化DDL脚本)和dss.ri (DSS数据表创建索引、外键脚本)，这两个脚本不适用于MySQL，需要进行修改。

1、修改dss.ddl

在dss.ddl开头加上以下代码：

drop database tpch;

create database tpch;

use tpch;

修改后的dss.ddl如下：

1 -- Sccsid: @(#)dss.ddl 2.1.8.1

2 drop database tpch;3 create database tpch;4 use tpch;5

6 CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,7 N_NAME CHAR(25) NOT NULL,8 N_REGIONKEY INTEGER NOT NULL,9 N_COMMENT VARCHAR(152));10

11 CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,12 R_NAME CHAR(25) NOT NULL,13 R_COMMENT VARCHAR(152));14

15 CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,16 P_NAME VARCHAR(55) NOT NULL,17 P_MFGR CHAR(25) NOT NULL,18 P_BRAND CHAR(10) NOT NULL,19 P_TYPE VARCHAR(25) NOT NULL,20 P_SIZE INTEGER NOT NULL,21 P_CONTAINER CHAR(10) NOT NULL,22 P_RETAILPRICE DECIMAL(15,2) NOT NULL,23 P_COMMENT VARCHAR(23) NOT NULL );24

25 CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,26 S_NAME CHAR(25) NOT NULL,27 S_ADDRESS VARCHAR(40) NOT NULL,28 S_NATIONKEY INTEGER NOT NULL,29 S_PHONE CHAR(15) NOT NULL,30 S_ACCTBAL DECIMAL(15,2) NOT NULL,31 S_COMMENT VARCHAR(101) NOT NULL);32

33 CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,34 PS_SUPPKEY INTEGER NOT NULL,35 PS_AVAILQTY INTEGER NOT NULL,36 PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,37 PS_COMMENT VARCHAR(199) NOT NULL );38

39 CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,40 C_NAME VARCHAR(25) NOT NULL,41 C_ADDRESS VARCHAR(40) NOT NULL,42 C_NATIONKEY INTEGER NOT NULL,43 C_PHONE CHAR(15) NOT NULL,44 C_ACCTBAL DECIMAL(15,2) NOT NULL,45 C_MKTSEGMENT CHAR(10) NOT NULL,46 C_COMMENT VARCHAR(117) NOT NULL);47

48 CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,49 O_CUSTKEY INTEGER NOT NULL,50 O_ORDERSTATUS CHAR(1) NOT NULL,51 O_TOTALPRICE DECIMAL(15,2) NOT NULL,52 O_ORDERDATE DATE NOT NULL,53 O_ORDERPRIORITY CHAR(15) NOT NULL,54 O_CLERK CHAR(15) NOT NULL,55 O_SHIPPRIORITY INTEGER NOT NULL,56 O_COMMENT VARCHAR(79) NOT NULL);57

58 CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,59 L_PARTKEY INTEGER NOT NULL,60 L_SUPPKEY INTEGER NOT NULL,61 L_LINENUMBER INTEGER NOT NULL,62 L_QUANTITY DECIMAL(15,2) NOT NULL,63 L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,64 L_DISCOUNT DECIMAL(15,2) NOT NULL,65 L_TAX DECIMAL(15,2) NOT NULL,66 L_RETURNFLAG CHAR(1) NOT NULL,67 L_LINESTATUS CHAR(1) NOT NULL,68 L_SHIPDATE DATE NOT NULL,69 L_COMMITDATE DATE NOT NULL,70 L_RECEIPTDATE DATE NOT NULL,71 L_SHIPINSTRUCT CHAR(25) NOT NULL,72 L_SHIPMODE CHAR(10) NOT NULL,73 L_COMMENT VARCHAR(44) NOT NULL);

View Code

2、修改dss.ri

dss.ri修改的地方比较多，修改后的dss.ri如下：

1 --Sccsid: @(#)dss.ri 2.1.8.1

2 --tpch Benchmark Version 8.0

3

4 usetpch;5

6 --ALTER TABLE tpch.REGION DROP PRIMARY KEY;

7 --ALTER TABLE tpch.NATION DROP PRIMARY KEY;

8 --ALTER TABLE tpch.PART DROP PRIMARY KEY;

9 --ALTER TABLE tpch.SUPPLIER DROP PRIMARY KEY;

10 --ALTER TABLE tpch.PARTSUPP DROP PRIMARY KEY;

11 --ALTER TABLE tpch.ORDERS DROP PRIMARY KEY;

12 --ALTER TABLE tpch.LINEITEM DROP PRIMARY KEY;

13 --ALTER TABLE tpch.CUSTOMER DROP PRIMARY KEY;

14

15

16 --For table REGION

17 ALTER TABLEtpch.REGION18 ADD PRIMARY KEY(R_REGIONKEY);19

20 --For table NATION

21 ALTER TABLEtpch.NATION22 ADD PRIMARY KEY(N_NATIONKEY);23

24 ALTER TABLEtpch.NATION25 ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) referencestpch.REGION(R_REGIONKEY);26

27 COMMIT WORK;28

29 --For table PART

30 ALTER TABLEtpch.PART31 ADD PRIMARY KEY(P_PARTKEY);32

33 COMMIT WORK;34

35 --For table SUPPLIER

36 ALTER TABLEtpch.SUPPLIER37 ADD PRIMARY KEY(S_SUPPKEY);38

39 ALTER TABLEtpch.SUPPLIER40 ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) referencestpch.NATION(N_NATIONKEY);41

42 COMMIT WORK;43

44 --For table PARTSUPP

45 ALTER TABLEtpch.PARTSUPP46 ADD PRIMARY KEY(PS_PARTKEY,PS_SUPPKEY);47

48 COMMIT WORK;49

50 --For table CUSTOMER

51 ALTER TABLEtpch.CUSTOMER52 ADD PRIMARY KEY(C_CUSTKEY);53

54 ALTER TABLEtpch.CUSTOMER55 ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) referencestpch.NATION(N_NATIONKEY);56

57 COMMIT WORK;58

59 --For table LINEITEM

60 ALTER TABLEtpch.LINEITEM61 ADD PRIMARY KEY(L_ORDERKEY,L_LINENUMBER);62

63 COMMIT WORK;64

65 --For table ORDERS

66 ALTER TABLEtpch.ORDERS67 ADD PRIMARY KEY(O_ORDERKEY);68

69 COMMIT WORK;70

71 --For table PARTSUPP

72 ALTER TABLEtpch.PARTSUPP73 ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) referencestpch.SUPPLIER(S_SUPPKEY);74

75 COMMIT WORK;76

77 ALTER TABLEtpch.PARTSUPP78 ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) referencestpch.PART(P_PARTKEY);79

80 COMMIT WORK;81

82 --For table ORDERS

83 ALTER TABLEtpch.ORDERS84 ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) referencestpch.CUSTOMER(C_CUSTKEY);85

86 COMMIT WORK;87

88 --For table LINEITEM

89 ALTER TABLEtpch.LINEITEM90 ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) referencestpch.ORDERS(O_ORDERKEY);91

92 COMMIT WORK;93

94 ALTER TABLEtpch.LINEITEM95 ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references

96 tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY);97

98 COMMIT WORK;99

100 usetpch;101

102 alter table CUSTOMER rename tocustomer ;103 alter table LINEITEM rename tolineitem ;104 alter table NATION rename tonation ;105 alter table ORDERS rename toorders ;106 alter table PART rename topart ;107 alter table PARTSUPP rename topartsupp ;108 alter table REGION rename toregion ;109 alter table SUPPLIER rename to supplier ;

View Code

修改的地方有第4行、第6~13行(--后添加空格)、第25、40、55、73、78、84 、90、96行(添加外码)，第100~109行(将大写表名改为小写)，还要注意我们建立的数据库名称为tpch，所有引用表都要使用tpch.表名的形式，比如tpch.REGION

七、在MySQL中建表

打开MySQL执行以下语句：

mysql> \. /home/hadoop/Downloads/tpch_2_17_3/dbgen/dss.ddl

这个命令就是\. 加上空格加上dss.ddl的位置。

然后使用 show databases; 语句可以看到已经建立了一个名为tpch的数据库：

接着执行：

mysql>use tpch;

mysql> show tables;

可以看到数据库tpch中有8个表：

然后执行：

mysql> \. /home/hadoop/Downloads/tpch_2_17_3/dbgen/dss.ri

这句指令给表与表之间添加了主码及外码约束，并且将大写表名改为小写。

再 show tables; 可以看到表名已经改为了小写：

我们再使用 show create table 表名; 语句来检查主码与外码约束是否添加。这里对表customer测试结果如下：

可以看到主码与外码添加成功。

七、导入tbl文件到表中

上一步只是建立了表，表中没有数据，使用以下命令导入数据：

mysql> load data local infile '/home/hadoop/Downloads/tpch_2_17_3/dbgen/part.tbl' into table part fields terminated by '|' lines terminated by '|\n';

这句命令将/home/hadoop/Downloads/tpch_2_17_3/dbgen/part.tbl文件里的数据导入到表part中，可以使用语句select * from part limit 5; 来查看是否导入成功：

结果不为空则导入成功。

这样只是导入了一个表，还要使用相同结构的语句将剩余的7个tbl文件导入到相应的表中。由于外码约束，所以要注意导入文件的先后顺序。

八、参考