在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$ cp makefile.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 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
修改的地方为：将编译器设置为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);
2、修改dss.ri
dss.ri修改的地方比较多，修改后的dss.ri如下：
1 -- Sccsid: @(#)dss.ri 2.1.8.1 2 -- tpch Benchmark Version 8.0 3 4 use tpch; 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 TABLE tpch.REGION 18 ADD PRIMARY KEY (R_REGIONKEY); 19 20 -- For table NATION 21 ALTER TABLE tpch.NATION 22 ADD PRIMARY KEY (N_NATIONKEY); 23 24 ALTER TABLE tpch.NATION 25 ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references tpch.REGION(R_REGIONKEY); 26 27 COMMIT WORK; 28 29 -- For table PART 30 ALTER TABLE tpch.PART 31 ADD PRIMARY KEY (P_PARTKEY); 32 33 COMMIT WORK; 34 35 -- For table SUPPLIER 36 ALTER TABLE tpch.SUPPLIER 37 ADD PRIMARY KEY (S_SUPPKEY); 38 39 ALTER TABLE tpch.SUPPLIER 40 ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references tpch.NATION(N_NATIONKEY); 41 42 COMMIT WORK; 43 44 -- For table PARTSUPP 45 ALTER TABLE tpch.PARTSUPP 46 ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY); 47 48 COMMIT WORK; 49 50 -- For table CUSTOMER 51 ALTER TABLE tpch.CUSTOMER 52 ADD PRIMARY KEY (C_CUSTKEY); 53 54 ALTER TABLE tpch.CUSTOMER 55 ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references tpch.NATION(N_NATIONKEY); 56 57 COMMIT WORK; 58 59 -- For table LINEITEM 60 ALTER TABLE tpch.LINEITEM 61 ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER); 62 63 COMMIT WORK; 64 65 -- For table ORDERS 66 ALTER TABLE tpch.ORDERS 67 ADD PRIMARY KEY (O_ORDERKEY); 68 69 COMMIT WORK; 70 71 -- For table PARTSUPP 72 ALTER TABLE tpch.PARTSUPP 73 ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references tpch.SUPPLIER(S_SUPPKEY); 74 75 COMMIT WORK; 76 77 ALTER TABLE tpch.PARTSUPP 78 ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references tpch.PART(P_PARTKEY); 79 80 COMMIT WORK; 81 82 -- For table ORDERS 83 ALTER TABLE tpch.ORDERS 84 ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references tpch.CUSTOMER(C_CUSTKEY); 85 86 COMMIT WORK; 87 88 -- For table LINEITEM 89 ALTER TABLE tpch.LINEITEM 90 ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references tpch.ORDERS(O_ORDERKEY); 91 92 COMMIT WORK; 93 94 ALTER TABLE tpch.LINEITEM 95 ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references 96 tpch.PARTSUPP(PS_PARTKEY,PS_SUPPKEY); 97 98 COMMIT WORK; 99 100 use tpch; 101 102 alter table CUSTOMER rename to customer ; 103 alter table LINEITEM rename to lineitem ; 104 alter table NATION rename to nation ; 105 alter table ORDERS rename to orders ; 106 alter table PART rename to part ; 107 alter table PARTSUPP rename to partsupp ; 108 alter table REGION rename to region ; 109 alter table SUPPLIER rename to supplier ;
修改的地方有第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文件导入到相应的表中。由于外码约束，所以要注意导入文件的先后顺序。
八、参考
本站使用「CC BY-NC-SA」创作共享协议，转载请在文章明显位置注明作者及出处。
