一.生成数据集
1.下载TPC-H压缩包
下载地址:http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp
2.解压TPC-H的压缩包,进入dbgen文件夹,找到tpch.sln文件,用visual studio打开。
3.在工程处右键dbgen,选择生成,执行完毕
4.此时,在dbgen文件夹下,出现了debug文件夹,在debug文件夹中,找到dbgen.exe的文件。copy到上一层目录,也就是dbgen文件夹中。
5.在dbgen文件夹下,打开dos界面。(在文件地址栏输入cmd)。输入命令dbgen -h
5.运行完后直接输入dbgen -vf -s XXX就可以生成你想要的数据了。这里的XXX表示想要生成的数据量(单位GB)
6.生成结束后,就能在dbgen文件夹下面看到生成的数据集了。生成完毕(20GB)
二.导入数据
1.新建一个数据库用于导入数据
2.建表sql
3.建立表约束
4.导入数据
5.查看导入的数据
6.TPC-H数据集表关系图
三.SQL语句
use Big
------------1.建表
CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY INTEGER NOT NULL,
N_COMMENT VARCHAR(152));
CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR(152));
CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,
P_NAME VARCHAR(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR(25) NOT NULL,
P_SIZE INTEGER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT VARCHAR(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR(40) NOT NULL,
S_NATIONKEY INTEGER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL DECIMAL(15,2) NOT NULL,
S_COMMENT VARCHAR(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,
PS_SUPPKEY INTEGER NOT NULL,
PS_AVAILQTY INTEGER NOT NULL,
PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,
PS_COMMENT VARCHAR(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER NOT NULL,
C_NAME VARCHAR(25) NOT NULL,
C_ADDRESS VARCHAR(40) NOT NULL,
C_NATIONKEY INTEGER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL DECIMAL(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,
O_CUSTKEY INTEGER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE DECIMAL(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY INTEGER NOT NULL,
O_COMMENT VARCHAR(79) NOT NULL);
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) NOT NULL,
L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
L_DISCOUNT DECIMAL(15,2) NOT NULL,
L_TAX DECIMAL(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR(44) NOT NULL);
------------2.建立表约束
use Big
--ALTER TABLE Example.REGION DROP PRIMARY KEY;
--ALTER TABLE Example.NATION DROP PRIMARY KEY;
--ALTER TABLE Example.PART DROP PRIMARY KEY;
--ALTER TABLE Example.SUPPLIER DROP PRIMARY KEY;
--ALTER TABLE Example.PARTSUPP DROP PRIMARY KEY;
--ALTER TABLE Example.ORDERS DROP PRIMARY KEY;
--ALTER TABLE Example.LINEITEM DROP PRIMARY KEY;
--ALTER TABLE Example.CUSTOMER DROP PRIMARY KEY;
-- For table REGION
ALTER TABLE dbo.REGION
ADD PRIMARY KEY (R_REGIONKEY);
-- For table NATION
ALTER TABLE dbo.NATION
ADD PRIMARY KEY (N_NATIONKEY);
ALTER TABLE dbo.NATION
ADD constraint NATION_FK1 FOREIGN KEY(N_REGIONKEY) references dbo.REGION(R_REGIONKEY);
-- For table PART
ALTER TABLE dbo.PART
ADD PRIMARY KEY (P_PARTKEY);
-- For table SUPPLIER
ALTER TABLE dbo.SUPPLIER
ADD PRIMARY KEY (S_SUPPKEY);
ALTER TABLE dbo.SUPPLIER
ADD constraint SUPPLIER_FK1 FOREIGN KEY(S_NATIONKEY) references dbo.NATION(N_NATIONKEY);
-- For table PARTSUPP
ALTER TABLE dbo.PARTSUPP
ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
-- For table CUSTOMER
ALTER TABLE dbo.CUSTOMER
ADD PRIMARY KEY (C_CUSTKEY);
ALTER TABLE dbo.CUSTOMER
ADD constraint CUSTOMER_FK1 FOREIGN KEY (C_NATIONKEY) references dbo.NATION(N_NATIONKEY);
-- For table LINEITEM
ALTER TABLE dbo.LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
-- For table ORDERS
ALTER TABLE dbo.ORDERS
ADD PRIMARY KEY (O_ORDERKEY);
-- For table PARTSUPP
ALTER TABLE dbo.PARTSUPP
ADD constraint PARTSUPP_FK1 FOREIGN KEY (PS_SUPPKEY) references dbo.SUPPLIER(S_SUPPKEY);
ALTER TABLE dbo.PARTSUPP
ADD constraint PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references dbo.PART(P_PARTKEY);
-- For table ORDERS
ALTER TABLE dbo.ORDERS
ADD constraint ORDERS_FK1 FOREIGN KEY (O_CUSTKEY) references dbo.CUSTOMER(C_CUSTKEY);
-- For table LINEITEM
ALTER TABLE dbo.LINEITEM
ADD constraint LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references dbo.ORDERS(O_ORDERKEY);
ALTER TABLE dbo.LINEITEM
ADD constraint LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references
dbo.PARTSUPP(PS_PARTKEY, PS_SUPPKEY);
------------3.导入数据
use Big
BULK INSERT part FROM 'D:\JAVA\TPC-H\dbgen\part.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT customer FROM 'D:\JAVA\TPC-H\dbgen\customer.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT orders FROM 'D:\JAVA\TPC-H\dbgen\orders.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT partsupp FROM 'D:\JAVA\TPC-H\dbgen\partsupp.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT supplier FROM 'D:\JAVA\TPC-H\dbgen\supplier.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT lineitem FROM 'D:\JAVA\TPC-H\dbgen\lineitem.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT nation FROM 'D:\JAVA\TPC-H\dbgen\nation.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
BULK INSERT region FROM 'D:\JAVA\TPC-H\dbgen\region.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')