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数据库中呢?

另一篇文章等着你:

Data Services Designer将数据从sql server抽取到hana

转载于:https://www.cnblogs.com/suenshuai/p/9836705.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值