TPC-H在windows上导入mysql
介绍
用来生成数据库的,本文参考了各位大佬的,使用本教程需要visual studio,mysql
下载
http://pan.baidu.com/s/1qXSqRZA 密码:hb4o
下载解压,路径中不要有中文
具体导入步骤
在dbgen目录下找到tpch.sln文件,用Microsoft Visual Studio打开
打开的时候vs会自动转换
重新生成或者生成,如图
生成成功
这个时候在dbgen/Degub下生成了dbgen.exe
复制到dbgen目录
shift加右键打开命令行
输入dbgen.exe -h
查看命令
我选择dbgen.exe -vf -s 0.1
生成0.1G的数据(这个看个人需求)
成功之后就可以在dbgen目录下看8个.tbl文件,用notepad可以看到里面是生成好的数据
然后就可以开始往数据库里面弄了
我用的Navicat,你可以选择使用workbench或者其他什么的
创建数据库
create database tpcd
这里要注意数据库名是tpcd
然后利用dbgen目录下的dss.ddl建表(导入这个就各显神通了)
下面再建立外键,dbgen里面的dss.ri对mysql不适用
注意要改成下面这样
-- Sccsid: @(#)dss.ri 2.1.8.1
-- TPCD Benchmark Version 8.0
use TPCD;
-- ALTER TABLE TPCD.REGION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.NATION DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PART DROP PRIMARY KEY;
-- ALTER TABLE TPCD.SUPPLIER DROP PRIMARY KEY;
-- ALTER TABLE TPCD.PARTSUPP DROP PRIMARY KEY;
-- ALTER TABLE TPCD.ORDERS DROP PRIMARY KEY;
-- ALTER TABLE TPCD.LINEITEM DROP PRIMARY KEY;
-- ALTER TABLE TPCD.CUSTOMER DROP PRIMARY KEY;
-- For table REGION
ALTER TABLE TPCD.REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE TPCD.NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE TPCD.NATION
ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references TPCD.REGION;
COMMIT WORK;
-- For table PART
ALTER TABLE TPCD.PART
ADD PRIMARY KEY (P_PARTKEY);
COMMIT WORK;
-- For table SUPPLIER
ALTER TABLE TPCD.SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE TPCD.SUPPLIER
ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references TPCD.NATION;
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE TPCD.PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
COMMIT WORK;
-- For table CUSTOMER
ALTER TABLE TPCD.CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE TPCD.CUSTOMER
ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references TPCD.NATION;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE TPCD.LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
COMMIT WORK;
-- For table ORDERS
ALTER TABLE TPCD.ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
COMMIT WORK;
-- For table PARTSUPP
ALTER TABLE TPCD.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references TPCD.SUPPLIER;
COMMIT WORK;
ALTER TABLE TPCD.PARTSUPP
ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references TPCD.PART;
COMMIT WORK;
-- For table ORDERS
ALTER TABLE TPCD.ORDERS
ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references TPCD.CUSTOMER;
COMMIT WORK;
-- For table LINEITEM
ALTER TABLE TPCD.LINEITEM
ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY) references TPCD.ORDERS;
COMMIT WORK;
ALTER TABLE TPCD.LINEITEM
ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references
TPCD.PARTSUPP;
COMMIT WORK;
ok之后就可以开始放数据了,这几条导入是有顺序的,因为有外码依赖,可以直接用下面这个
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\customer.tbl' INTO TABLE CUSTOMER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\orders.tbl' INTO TABLE ORDERS FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\lineitem.tbl' INTO TABLE LINEITEM FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\nation.tbl' INTO TABLE NATION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\partsupp.tbl' INTO TABLE PARTSUPP FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\part.tbl' INTO TABLE PART FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\region.tbl' INTO TABLE REGION FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'E:\tpch_2_14_3\dbgen\supplier.tbl' INTO TABLE SUPPLIER FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
把里面的路径改成自己的,‘\’和’/’在Navicat里面要区分开来
不然会报错
等一小会就完成了,查看的时候建议不要直接用cmd,数据太多会卡住
导完之后差不多就这个样子
可以把每个表都看看,检查下是不是都有数据了