tpc ds 导入到oracle,用TPC-DS测试Sql server，Oracle

TPC-DS是什么

TPC-DS是TPC组织发布的用于测试决策系统的基准测试，是TPC-H的改进版。我们可以用它生成测试数据集和sql语句来测试数据库的OLAP能力。

最近我们用TPC-DS测试了一下Sql server和Oracle，这里把遇到的问题记录一下。首先说一下结论，我以后再不相信TPC的测试结果了，这个软件给我的感觉是根本没人维护，文档散乱无序，体验糟糕至极。

TPC-DS的使用

下载2.11版本后，在tools里面有个How_To_Guide-DS-V2.0.0.docx，照着做就可以，但是有几个问题需要注意一下

使用dsdgen命令产生数据时，需要设置-TERMINATE n选项不在每一行末尾生成分隔符，否则会生成类似下面末尾有换行符的数据，无法通过Sql server的bulk insert导入——为什么默认要在末尾加分隔符呢?想不明白。

1|0|10000|

2|10001|20000|

3|20001|30000|

4|30001|40000|

使用dsqgen命令产生sql语句时，要在query_templates目录下sqlserver.tpl，oracle.tpl，添加define _END=。哥，你倒是给个默认值啊。

自动生成导入语句

TPC-DS tools里面有个tpcds.sql，内容是创建测试数据库的sql语句。例如

create table catalog_page

(

cp_catalog_page_sk integer not null,

cp_catalog_page_id char(16) not null,

cp_start_date_sk integer ,

cp_end_date_sk integer ,

cp_department varchar(50) ,

cp_catalog_number integer ,

cp_catalog_page_number integer ,

cp_deion varchar(100) ,

cp_type varchar(100) ,

primary key (cp_catalog_page_sk)

);

create table inventory

(

inv_date_sk integer not null,

inv_item_sk integer not null,

inv_warehouse_sk integer not null,

inv_quantity_on_hand integer ,

primary key (inv_date_sk, inv_item_sk, inv_warehouse_sk)

);

它一共创建了25个表，这个25个表需要我们一个一个把数据导进去么?我们可以解析这个文件，自动生成导入的语句——python大法好!!!

Sql server的导入

dsdgen生成的文本文件是linux换行符，使用Sql server的bulk insert导入时，指定了rowterminator=’\n’也没有成功，后来发现需要使用rowterminator=‘0x0a’。呵呵。

例如

bulk insert call_center from 'C:\data\call_center.dat' with (fieldterminator='|',rowterminator='0x0a',batch_size=1000);

使用如下语句可以马上生成所有的bulk insert

#!/usr/bin/env python

import os

with open(/opt/share/tpc/v2.11.0rc2/tools/tpcds.sql) as ddl:

for line in ddl:

if line.startswith('--'):

continue

if line.startswith('create table'):

table = line.replace(\n,).split( )[2]

print(bulk insert %s from 'G:\%s' with (fieldterminator='|',rowterminator='0x0a',batchsize=1000); %(table,table))

Oracle的导入

Oracle的情况比较复杂，它的导入工具是sqlldr，需要为每一个表生成一个control file，内容如下

load data

infile G:\income_band.dat

INTO TABLE income_band

truncate

fields terminated by '|'

(

ib_income_band_sk,

ib_lower_bound,

ib_upper_bound

)

问题是居然要把column显式的写出来——你TM是在逗我?我会自己复制粘贴?所以可以使用如下python脚本生成一堆control文件。注意，TPC-DS生成的日期是YYYY-MM-DD格式，需要显式的指定。显然，Oracle默认不是YYYY-MM-DD这种格式。。。

#!/usr/bin/env python

import os

with open(/opt/share/tpc/v2.11.0rc2/tools/tpcds.sql) as ddl:

for line in ddl:

if line.startswith('--'):

continue

if line.startswith('create table'):

table = line.replace(\n,).split( )[2]

fieldList=[]

controlFile = open(%s.txt%table,w)

controlFile.write((\

load data

infile G:\%s.dat

INTO TABLE %s

truncate

fields terminated by '|'

(

% (table,table)).replace(\n,\r\n))

if line.startswith(');'):

controlFile.write(,\r\n.join(fieldList))

controlFile.write(\r\n)\r\n)

controlFile.close()

if line.endswith(',\n'):

field = line.split()[0]

type = line.split()[1]

if 'date' in type:

fieldList.append('%s DATE YYYY-MM-DD' % field)

elif 'time' in type:

fieldList.append('%s DATE hh24:mi:ss' % field)

else:

fieldList.append(field)

这个脚本会将tpcds.sql文件中的

create table item

(

i_item_sk integer not null,

i_item_id char(16) not null,

i_rec_start_date date ,

i_rec_end_date date ,

i_item_desc varchar(200) ,

i_current_price decimal(7,2) ,

i_wholesale_cost decimal(7,2) ,

i_brand_id integer ,

i_brand char(50) ,

i_class_id integer ,

i_class char(50) ,

i_category_id integer ,

i_category char(50) ,

i_manufact_id integer ,

i_manufact char(50) ,

i_size char(20) ,

i_formulation char(20) ,

i_color char(20) ,

i_units char(10) ,

i_container char(10) ,

i_manager_id integer ,

i_product_name char(50) ,

primary key (i_item_sk)

);

生成为item.txt

load data

infile G:\item.dat

INTO TABLE item

truncate

fields terminated by '|'

(

i_item_sk,

i_item_id,

i_rec_start_date DATE YYYY-MM-DD,

i_rec_end_date DATE YYYY-MM-DD,

i_item_desc,

i_current_price,

i_wholesale_cost,

i_brand_id,

i_brand,

i_class_id,

i_class,

i_category_id,

i_category,

i_manufact_id,

i_manufact,

i_size,

i_formulation,

i_color,

i_units,

i_container,

i_manager_id,

i_product_name

)