TPC-DS 数据仓库查询性能测试 -- 中国DBA社区

TPC-DS

最近，需要导入大量的测试数据来评估下某个产品复杂查询SQL性能是否能满足需要，然后，领导大致提了下TPC，找到了TPC-DS工具，创造数据，利用SQL查询模板查询.

参考官方地址链接：http://www.tpc.org/tpcds/

一、安装TPC-DS工具

1、下载工具 

     下载地址：http://www.tpc.org/tpc_documents_current_versions/download_programs/tools-download-request.asp?bm_type=TPC-DS&bm_vers=2..0&mode=CURRENT-ONLY 

    注意：必须输入邮箱，他会发下载地址到邮箱中，点击下载地址即可下载。 

2、解压编译工具 

   1）解压 

    执行命令：unzip tpc-ds-tool.zip 

   2）进入tools目录编译 

    执行命令：make

  以上TPC-DS测试环境就在准备好了

二、创建TPC-DS测试需要用到的表

1、准备好创建表语句 

    TPC-DS已经提前准备好了创建表相关的SQL文件 

    文件位于:tools目录下，具体文件如下： 

        tpcds.sql 创建25张表的sql语句 

        tpcds_ri.sql 创建表与表之间关系的sql语句 

        tpcds_source.sql  

2、创建相应的数据库和表,运行sql脚本
SQL> conn marvin/marvin;
Connected.
SQL> @tpcds.sql
    dv_create_time            time                          ,
                              *
ERROR at line 5:
ORA-00902: invalid datatype

修改表dbgen_version字段dv_create_time 类型为timestamp，再次手动创建
SQL> create table dbgen_version
  2  (
  3      dv_version                varchar(16)                   ,
    dv_create_date            date                          ,
  4    5      dv_create_time            timestamp                     ,
  6      dv_cmdline_args           varchar(200)
  7  );

Table created.

SQL>tpcds_ri.sql
SQL>tpcds_source.sql

三、生成数据并将数据导入到相应的表中

1、数据工具 
dsdgen 生成数据
    -dir 生成数据存放目录
    -scale 生成数据大小

dsqgen 生成查询语句
    -output_dir 输出文件目录
    -input 输入文件
    -scale 生成数据大小
    -dialect 数据库类型
    -directory 查询语句模板文件

创建数据存放目录
oracle@rac2:/u01/tpc/v2.8.0rc4>mkdir data
oracle@rac2:/u01/tpc/v2.8.0rc4>pwd
/u01/tpc/v2.8.0rc4

1）命令1:./dsdgen -DIR /u01/tpc/v2.8.0rc4/data/ -SCALE 1 （scale表示产生1G测试数据）
或者
2）命令2：./dsdgen -DIR /u01/tpc/v2.8.0rc4/data/ -SCALE 10 -parallel 4 -child 1 （并行产生1g数据）

2、对生成的数据进行处理（不处理无法导入到表中）因为每一行最后多了一个“|”,需要进一步处理

在/u01/tpc/v2.8.0rc4/data/目录下创建目录handled 
命令：mkdir handled 
在/u01/tpc/v2.8.0rc4/data/目录下执行如下命令:
处理“|”问题：
for i in `ls *.dat`
    do
     name="handled/$i"
     echo $name
     `touch $name`
     `chmod 777 $name`
     sed 's/|$//' $i >> $name;
    done
3、导入处理过后的数据到数据表中(handle目录下的数据)
Oracel用sqlldr工具导入,由于sqlldr工具数据类型是CHAR类型，不能直接写NUMBER之类的，所以需要进行转换，CHAR跟VARCHAR2不转都字符型，但是写VARCHAR2会报错（如下所示：只写一个表示例，
TO_NUMBER内不可以指定格式TO_NUMBER(:CC_CALL_CENTER_SK,'999,999,999')，否则会报错

Record 45: Rejected - Error on table MARVIN.CATALOG_RETURNS, column CR_RETURNED_DATE_SK.
ORA-01722: invalid number
)

load data
infile 'call_center_1_2.dat'     --访问的导入文件
into table marvin.call_center     --这里可以指定scheme 如marvin.call_center 否则导入就只能用表所属用户导入，指定schema后可用system用户
FIELDS TERMINATED BY '|'           --字段分隔符
trailing nullcols   --表示支持导入空值
(
CC_CALL_CENTER_SK  CHAR "TO_NUMBER(:CC_CALL_CENTER_SK)",
CC_CALL_CENTER_ID CHAR ,
CC_REC_START_DATE DATE "YYYY-MM-DD HH24:MI:SS",
CC_REC_END_DATE DATE "YYYY-MM-DD HH24:MI:SS",
CC_CLOSED_DATE_SK CHAR "TO_NUMBER(:CC_CLOSED_DATE_SK)",
CC_OPEN_DATE_SK CHAR "TO_NUMBER(:CC_OPEN_DATE_SK)",
CC_NAME CHAR,
CC_CLASS CHAR ,
CC_EMPLOYEES CHAR "TO_NUMBER(:CC_EMPLOYEES)",
CC_SQ_FT CHAR "TO_NUMBER(:CC_SQ_FT)" ,
CC_HOURS CHAR,
CC_MANAGER CHAR,
CC_MKT_ID CHAR "TO_NUMBER(:CC_MKT_ID)" ,
CC_MKT_CLASS CHAR,
CC_MKT_DESC CHAR,
CC_MARKET_MANAGER CHAR,
CC_DIVISION CHAR "TO_NUMBER(:CC_DIVISION)",
CC_DIVISION_NAME CHAR,
CC_COMPANY CHAR "TO_NUMBER(:CC_COMPANY)",
CC_COMPANY_NAME CHAR,
CC_STREET_NUMBER CHAR,
CC_STREET_NAME CHAR,
CC_STREET_TYPE CHAR,
CC_SUITE_NUMBER CHAR,
CC_CITY CHAR,
CC_COUNTY CHAR,
CC_STATE CHAR,
CC_ZIP CHAR,
CC_COUNTRY CHAR,
CC_GMT_OFFSET CHAR "TO_NUMBER(:CC_GMT_OFFSET)",
CC_TAX_PERCENTAGE CHAR "TO_NUMBER(:CC_GMT_OFFSET)"
)
执行导入：
oracle@rac2:/u01/tpc/v2.8.0rc4/data/handled>sqlldr system/yunq111@orcl control=result.ctl log=/u01/tpc/v2.8.0rc4/data/handled/result.log

对比数据文件中得数据行数，查看导入日志，导入行数
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
验证数据导入：
SQL>SELECT COUNT(*) FROM MARVIN.CALL_CENTER；
  COUNT(*)
----------
         6

四、生成对应查询SQL模板

切换到query_templates目录下
oracle@rac2:/u01/tpc/v2.8.0rc4/query_templates>pwd
/u01/tpc/v2.8.0rc4/query_templates

新增编辑update_query.sh脚本，修改query_template下query1-99模板，在行尾加define _END = ""，否则执行生成命令会出错; 
vi update_query.sh

for i in `ls /u01/tpc/v2.8.0rc4/query_templates/query*`;
        do
                echo "define _END= /"/";" >> $i
        done
 oracle@rac2:/u01/tpc/v2.8.0rc4/query_templates>sh update_query.sh

 切换到tools目录下执行
 oracle@rac2:/u01/tpc/v2.8.0rc4/query_templates>mkdir -p /u01/tpc/v2.8.0rc4/sql

 oracle@rac2:/u01/tpc/v2.8.0rc4/tools>./dsqgen -output_dir /u01/tpc/v2.8.0rc4/sql -input /u01/tpc/v2.8.0rc4/query_templates/templates.lst -scale 1 -dialect netezza -directory /u01/tpc/v2.8.0rc4/query_templates

 验证查看SQL目录是否生成QUERY 脚本
 oracle@rac2:/u01/tpc/v2.8.0rc4/sql>ls
    query_0.sql
使用vi 打开query0.sql 里面有99个用于SQL查询的脚本，但是这是需要注意的是，生成的SQL查询脚本中有一个 limit 参数，这个对    于MYSQL数据库可以使用，但是ORACLE数据库无法使用,需要手动替换成 rownum 参数 或者直接把 limit 参数去掉
vi 模式下发现 都多limit 100，
可直接:1,$s/limit 100//g 删除limit 100,其他不变动即可

最后，可以通过复制SQL语句，用于SQL查询.......

另外...生成查询语句（放到了/u01/tpc/v2.8.0rc4/sql下，可以自己指定输出目录）

最后附上，生成数据的其他处理：
处理空值问题:（将控制替换成NULL）--当前ORACLE未执行该项,可忽略
for i in ls *.dat
    do
        name="sql/$i"
        sed 's/^//NULL|/g;s/|||/|/NULL|/NULL|/g;s/||/|/NULL|/g;s/||/|/NULL|/g;s/||/|/NULL|/g;' i > name;
    done

签到成功

CNDBA社区

TPC-DS 数据仓库查询性能测试

一、安装TPC-DS工具

二、创建TPC-DS测试需要用到的表

Marvinn

QQ交流群

注册联系QQ