TPC-H DBGEN 的使用
TPC是业界公认的用来测试数据仓库性能的基准，它是一个非常公正和中立的一个平台，而TPC-H是基于这个而衍生出来的测试数据仓库性能的基准版本。
官网下载DBGEN工具
http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp
下载完成解压，并用Visual Studio打开dbgen目录下的tpch.sln文件
打开文件后点击生成，可生成dbgen.exe文件
生成的文件会在dbgen\Debug目录下，然后需要把dbgen.exe放到dbgen目录
注意：Visual Studio得安装.Net环境和Windows SDK
双击运行，全部输入Y ,生成tbl文件
生成后的tbl文件，一共八个
接下来去数据库创建表，这里以SqlServer演示，执行以下SQL
IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REGION]')AND type in (N'U')) BEGIN CREATE TABLE [dbo].[REGION]( [R_REGIONKEY] [int] NOT NULL, [R_NAME] [char](25) NOT NULL, [R_COMMENT] [varchar](152)NULL, CONSTRAINT[PK_REGION] PRIMARY KEY CLUSTERED ( [R_REGIONKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[PART]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[PART]( [P_PARTKEY] [int] 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] [int] NOT NULL, [P_CONTAINER] [char](10) NOT NULL, [P_RETAILPRICE] [decimal](15, 2) NOT NULL, [P_COMMENT] [varchar](23) NOT NULL, CONSTRAINT[PK_PART] PRIMARY KEY CLUSTERED ( [P_PARTKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[ORDERS]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[ORDERS]( [O_ORDERKEY] [int] NOT NULL, [O_CUSTKEY] [int] NOT NULL, [O_ORDERSTATUS] [char](1) NOT NULL, [O_TOTALPRICE] [decimal](15, 2) NOT NULL, [O_ORDERDATE] [datetime] NOT NULL, [O_ORDERPRIORITY] [char](15) NOT NULL, [O_CLERK] [char](15) NOT NULL, [O_SHIPPRIORITY] [int] NOT NULL, [O_COMMENT] [varchar](79) NOT NULL, CONSTRAINT[PK_ORDERS_1] PRIMARY KEY CLUSTERED ( [O_ORDERKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[LINEITEM]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[LINEITEM]( [L_ORDERKEY] [int] NOT NULL, [L_PARTKEY] [int] NOT NULL, [L_SUPPKEY] [int] NOT NULL, [L_LINENUMBER] [int] 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] [datetime] NOT NULL, [L_COMMITDATE] [datetime] NOT NULL, [L_RECEIPTDATE] [datetime] NOT NULL, [L_SHIPINSTRUCT] [char](25) NOT NULL, [L_SHIPMODE] [char](10) NOT NULL, [L_COMMENT] [varchar](44) NOT NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[CUSTOMER]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[CUSTOMER]( [C_CUSTKEY] [int] NOT NULL, [C_NAME] [varchar](25) NOT NULL, [C_ADDRESS] [varchar](40) NOT NULL, [C_NATIONKEY] [int] 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, CONSTRAINT[PK_CUSTOMER] PRIMARY KEY CLUSTERED ( [C_CUSTKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[SUPPLIER]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[SUPPLIER]( [S_SUPPKEY] [int] NOT NULL, [S_NAME] [char](25) NOT NULL, [S_ADDRESS] [varchar](40) NOT NULL, [S_NATIONKEY] [int] NOT NULL, [S_PHONE] [char](15) NOT NULL, [S_ACCTBAL] [decimal](15, 2) NOT NULL, [S_COMMENT] [varchar](101) NOT NULL, CONSTRAINT[PK_SUPPLIER] PRIMARY KEY CLUSTERED ( [S_SUPPKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[NATION]') AND type in(N'U')) BEGIN CREATE TABLE [dbo].[NATION]( [N_NATIONKEY] [int] NOT NULL, [N_NAME] [char](25) NOT NULL, [N_REGIONKEY] [int] NOT NULL, [N_COMMENT] [varchar](152)NULL, CONSTRAINT[PK_NATION] PRIMARY KEY CLUSTERED ( [N_NATIONKEY] ASC )WITH(PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[dbo].[PARTSUPP]')AND type in(N'U')) BEGIN CREATE TABLE [dbo].[PARTSUPP]( [PS_PARTKEY] [int] NOT NULL, [PS_SUPPKEY] [int] NOT NULL, [PS_AVAILQTY] [int] NOT NULL, [PS_SUPPLYCOST] [decimal](15, 2) NOT NULL, [PS_COMMENT] [varchar](199) NOT NULL, CONSTRAINT[PK_PARTSUPP] PRIMARY KEY CLUSTERED ( [PS_PARTKEY] ASC, [PS_SUPPKEY] ASC )WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_ORDERS_CUSTOMER]')AND parent_object_id=OBJECT_ID(N'[dbo].[ORDERS]')) ALTER TABLE [dbo].[ORDERS] WITH CHECK ADD CONSTRAINT [FK_ORDERS_CUSTOMER] FOREIGN KEY([O_CUSTKEY]) REFERENCES [dbo].[CUSTOMER]([C_CUSTKEY]) GO ALTER TABLE [dbo].[ORDERS] CHECK CONSTRAINT [FK_ORDERS_CUSTOMER] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_LINEITEM_ORDERS]') AND parent_object_id=OBJECT_ID(N'[dbo].[LINEITEM]')) ALTER TABLE [dbo].[LINEITEM] WITH NOCHECK ADD CONSTRAINT [FK_LINEITEM_ORDERS] FOREIGN KEY([L_ORDERKEY]) REFERENCES [dbo].[ORDERS]([O_ORDERKEY]) GO ALTER TABLE [dbo].[LINEITEM] CHECK CONSTRAINT [FK_LINEITEM_ORDERS] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_LINEITEM_PARTSUPP]')AND parent_object_id=OBJECT_ID(N'[dbo].[LINEITEM]')) ALTER TABLE [dbo].[LINEITEM] WITH NOCHECK ADD CONSTRAINT [FK_LINEITEM_PARTSUPP] FOREIGN KEY([L_PARTKEY],[L_SUPPKEY]) REFERENCES [dbo].[PARTSUPP]([PS_PARTKEY],[PS_SUPPKEY]) GO ALTER TABLE [dbo].[LINEITEM] CHECK CONSTRAINT [FK_LINEITEM_PARTSUPP] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_CUSTOMER_NATION]')AND parent_object_id=OBJECT_ID(N'[dbo].[CUSTOMER]')) ALTER TABLE [dbo].[CUSTOMER] WITH CHECK ADD CONSTRAINT [FK_CUSTOMER_NATION] FOREIGN KEY([C_NATIONKEY]) REFERENCES [dbo].[NATION]([N_NATIONKEY]) GO ALTER TABLE [dbo].[CUSTOMER] CHECK CONSTRAINT [FK_CUSTOMER_NATION] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_SUPPLIER_NATION]')AND parent_object_id=OBJECT_ID(N'[dbo].[SUPPLIER]')) ALTER TABLE [dbo].[SUPPLIER] WITH CHECK ADD CONSTRAINT [FK_SUPPLIER_NATION] FOREIGN KEY([S_NATIONKEY]) REFERENCES [dbo].[NATION]([N_NATIONKEY]) GO ALTER TABLE [dbo].[SUPPLIER] CHECK CONSTRAINT [FK_SUPPLIER_NATION] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_NATION_REGION]')AND parent_object_id=OBJECT_ID(N'[dbo].[NATION]')) ALTER TABLE [dbo].[NATION] WITH CHECK ADD CONSTRAINT [FK_NATION_REGION] FOREIGN KEY([N_REGIONKEY]) REFERENCES [dbo].[REGION]([R_REGIONKEY]) GO ALTER TABLE [dbo].[NATION] CHECK CONSTRAINT [FK_NATION_REGION] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_PARTSUPP_PART]')AND parent_object_id=OBJECT_ID(N'[dbo].[PARTSUPP]')) ALTER TABLE [dbo].[PARTSUPP] WITH CHECK ADD CONSTRAINT [FK_PARTSUPP_PART] FOREIGN KEY([PS_PARTKEY]) REFERENCES [dbo].[PART]([P_PARTKEY]) GO ALTER TABLE [dbo].[PARTSUPP]CHECK CONSTRAINT [FK_PARTSUPP_PART] GO IF NOT EXISTS(SELECT * FROM sys.foreign_keys WHERE object_id=OBJECT_ID(N'[dbo].[FK_PARTSUPP_SUPPLIER]')AND parent_object_id=OBJECT_ID(N'[dbo].[PARTSUPP]')) ALTER TABLE [dbo].[PARTSUPP] WITH CHECK ADD CONSTRAINT [FK_PARTSUPP_SUPPLIER] FOREIGN KEY([PS_SUPPKEY]) REFERENCES [dbo].[SUPPLIER]([S_SUPPKEY]) GO ALTER TABLE [dbo].[PARTSUPP] CHECK CONSTRAINT [FK_PARTSUPP_SUPPLIER]
执行成功会生成八张表
接着执行以下SQL用来插入tbl文件，向表中插入数据
GO BULK INSERT part FROM 'E:\part.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT customer FROM 'E:\customer.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT orders FROM 'E:\orders.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT partsupp FROM 'E:\partsupp.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT supplier FROM 'E:\supplier.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT lineitem FROM 'E:\lineitem.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT nation FROM 'E:\nation.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|') BULK INSERT region FROM 'E:\region.tbl' WITH (TABLOCK, DATAFILETYPE='char', CODEPAGE='raw', FIELDTERMINATOR = '|')
如果报这个错，把tbl文件放到其他路径就好了，我这里转移到了E盘根目录
我这里转移到E盘执行成功
到这里，TPC-H的8个数据库表已经创建成功了。
SAP HANA建模的时候可以用这八个表来测试，但是我们这里表是在SqlServer中，那怎么把它们迁移到HANA数据库中呢？
另一篇文章等着你：
