MySQL---决策支持的基本测试标准TPC-DS测试数据的生成及导入

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: TPC-DS是tpc组织提供的官方决策支持基本测试标准,这个标准的数据对于决策支持的学习和测试很有帮助,怎样生成测试数据及将测试数据导入到MySQL数据库,可以参阅本文。

1 TPC-DS和TPC-H的区别

     说起数据库测试基准,第一个想到的tpc-c,tpc-c常常被用于在线事务处理(OLTP)数据库的性能测试,比如linux上常用的sysbench测试工具就支持oltp测试,开源工具sysbench-tpcc就基于sysbench的tpcc测试工具。

     oltp的测试基准是tpcc,olap(在线分析处理)的测试基准呢?广为人知的是tpc-H,TPC-H面向商品零售业,它定义了8张表,22个查询,遵循SQL92标准,它的表结构同oltp的表架构比较接近。

      另一个不太为人知的标准就是本文要提到的TPC-DS,这个标准表结构是典型的数据仓库的表结构,采用星型、雪花型等多维数据模式。它包含7张事实表,17张纬度表,跟大数据的分析挖掘应用非常类似。对于数据仓库的初学者来说,通过实际的例子来学习相对起来容易一些,这个数据集是不错的选择。

2  TPC-DS的下载和编译

     下载连接在这个位置

  https://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp

     在这个页面填入信息后,点击下载,可能会出现下面这个错误

捕获.PNG

    这个错误并不是输入有误,用必应搜索一下得知可以在火狐浏览器上安装gooreplacer插件来解决,插件的地址是:

https://addons.mozilla.org/zh-CN/firefox/addon/gooreplacer/

      下载插件安装后,设置一下这个插件:

屏幕截图(1173).png

     设置完之后重启一下浏览器,打开TPC-DS的下载连接,输入信息,点击下载后。输入的电子邮件地址会收到下载连接。将这个连接复制粘贴到浏览器就可以下载了。

     这个工具的编译十分简单,进入/usr/local/tpcds/tools目录下运行make命令即可。

[root@ tools]# pwd    /usr/local/tpcds/tools
[root@ tools]# make

3 准备MySQL数据库

    MySQL数据库的准备也不复杂,创建一个数据库,在数据库内创建表就可以了。

3.1 创建并切换到数据库

mysql>create database tpcds DEFAULT CHARSET utf8 COLLATE utf8_general_ci;    Query OK,1 row affected (0.00 sec)mysql> use tpcds;    Database changed

3.2 创建表

  建表脚本比较长,就不在这里粘贴了,建表后的结果如下(一共是25个表):

mysql> show tables;+------------------------+| Tables_in_tpcds        |+------------------------+| call_center            || catalog_page           || catalog_returns        || catalog_sales          || customer               || customer_address       || customer_demographics  || date_dim               || dbgen_version          || household_demographics || income_band            || inventory              || item                   || promotion              || reason                 || ship_mode              || store                  || store_returns          || store_sales            || time_dim               || warehouse              || web_page               || web_returns            || web_sales              || web_site               |+------------------------+25 rows inset(0.00 sec)

4 生成测试数据

       生成测试数据之前先要创建测试数据存放的目录

[root@ tools]# mkdir -p /tmp/tpcds_data

      运行生成测试数据的命令

[root@ tools]# ./dsdgen -DIR /tmp/tpcds_data -SCALE 1 -TERMINATE N, tpcds.sql    dsdgen Population Generator (Version 3.2.0)
    Copyright Transaction Processing Performance Council (TPC) 2001-2021    Warning: This scale factor is valid for QUALIFICATION ONLY

    -SCALE 参数指定数据的大小,以G为单位。

5 生成导入数据的脚本

          tpc-ds生成的测试数据可以用load命令导入MySQL数据库,可以用文本编辑器编辑,也可以用shell脚本生成,这里给出一个shell脚本,只需要调整数据文件所在的位置就可以生成25个表的导入脚本。

[root@ tpcds_data]# for file in `ls -l |awk '{print $9}'`;    >  do    >      echo"LOAD DATA INFILE '/tmp/tpcds_data/"$file"' INTO TABLE"${file%%.*}" FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';";
    >  done


      脚本是一个for循环,ls -l |awk '{print $9}'打印出来是当前目录下的文件名,文件名是带有扩展名的,针对每一取到的文件名,利用linux字符串功能拼接成load语句,${file%%.*}截取文件名中'.'左边的部分,这时是花括号,不是括号。这个脚本的输出结果如下:

LOAD DATA INFILE '/tmp/tpcds_data/call_center.dat' INTO TABLE call_center  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/catalog_page.dat' INTO TABLE catalog_page  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/catalog_returns.dat' INTO TABLE catalog_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/catalog_sales.dat' INTO TABLE catalog_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/customer_address.dat' INTO TABLE customer_address  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/customer.dat' INTO TABLE customer  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/customer_demographics.dat' INTO TABLE customer_demographics  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/date_dim.dat' INTO TABLE date_dim  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/dbgen_version.dat' INTO TABLE dbgen_version  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/household_demographics.dat' INTO TABLE household_demographics  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/income_band.dat' INTO TABLE income_band  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/inventory.dat' INTO TABLE inventory  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/item.dat' INTO TABLE item  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/promotion.dat' INTO TABLE promotion  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/reason.dat' INTO TABLE reason  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/ship_mode.dat' INTO TABLE ship_mode  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/store.dat' INTO TABLE store  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/store_returns.dat' INTO TABLE store_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/store_sales.dat' INTO TABLE store_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/time_dim.dat' INTO TABLE time_dim  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/warehouse.dat' INTO TABLE warehouse  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/web_page.dat' INTO TABLE web_page  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/web_returns.dat' INTO TABLE web_returns  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/web_sales.dat' INTO TABLE web_sales  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA INFILE '/tmp/tpcds_data/web_site.dat' INTO TABLE web_site  FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

6 导入数据

      登录MySQL数据库,切换到tcpds数据库下,粘贴生成的脚本就可以导入数据了。导入时往往报下面这个错误。

mysql> LOAD DATA INFILE '/tmp/tpcds_data/call_center.dat'INTOTABLE call_center  FIELDS TERMINATED BY'|' LINES TERMINATED BY'\n';    ERROR 1290(HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

出现这个错误的原因是secure_file_priv设置的问题,这个变量默认的设置为空

mysql> show variables like'%secure_file_priv%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| secure_file_priv |NULL|+------------------+-------+1 row inset(0.00 sec)

需要将它设置为存放要导入文件的目录

mysql>set secure_file_priv="/tmp/tpcds_data/";    ERROR 1238(HY000): Variable 'secure_file_priv'is a read only variable

这个变量是只读变量,只能在数据库启动之前设置,关闭MySQL数据库,重启后带上这个选项

[root@ tpcds_data]# mysqld_safe --user=mysql --datadir=/mysqldata --secure_file_priv="/tmp/tpcds_data/"&    [1] 60061[root@ tpcds_data]# 2022-08-30T07:10:08.899244Z mysqld_safe Logging to '/mysqldata/iZ2ze0t8khaprrpfvmevjiZ.err'.2022-08-30T07:10:08.926603Z mysqld_safe Starting mysqld daemon with databases from /mysqldata

再次登录数据库就可以导入了。

mysql> LOAD DATA INFILE '/tmp/tpcds_data/call_center.dat'INTOTABLE call_center  FIELDS TERMINATED BY'|' LINES TERMINATED BY'\n';    ERROR 1292(22007): Incorrect date value:'' for column 'cc_rec_end_date' at row 1

这个错误可以通过设置sql模式来消除

mysql>set sql_mode ='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';    Query OK,0 rows affected (0.00 sec)

再次导入

mysql> LOAD DATA INFILE '/tmp/tpcds_data/call_center.dat'INTOTABLE call_center  FIELDS TERMINATED BY'|' LINES TERMINATED BY'\n';    Query OK,6 rows affected,9 warnings (0.00 sec)    Records:6  Deleted:0  Skipped:0  Warnings:9

7 生成查询

# ./dsqgen -input ../query_templates/templates.lst -directory ../query_templates -output ./sql.ansi/ -DIALECT ansi -LOG ./sql.ansi/ansi.log

-directory是查询模板所在的目录,-DIALECT设置sql语言的版本

  生成查询的过程中也有可能报下面这个错误

qgen2 Query Generator (Version 3.2.0)
      Copyright Transaction Processing Performance Council (TPC) 2001-2021      Warning: This scale factor is valid for QUALIFICATION ONLY
      ERROR: Substitution'_END' is used before being initialized at line 63in ../query_templates/query1.tpl

需要处理一下查询模板文件,在每个文件的末尾加上一行

[root@ query_templates]# for i in `ls query*tpl`    > do    >     echo$i;
    >     echo"define _END = \"\";" >> $i    > done

生成查询之间仍然需要创建查询的存储目录

[root@ tools]# mkdir sql.ansi

生成查询

[root@ tools]# ./dsqgen -input ../query_templates/templates.lst -directory ../query_templates -output ./sql.ansi/ -DIALECT ansi -LOG ./sql.ansi/ansi.log    qgen2 Query Generator (Version 3.2.0)
    Copyright Transaction Processing Performance Council (TPC) 2001-2021    Warning: This scale factor is valid for QUALIFICATION ONLY

8 运行查询

找一个查询语句运行一下

selectcount(*)from store_sales
,household_demographics
,time_dim, store
where ss_sold_time_sk = time_dim.t_time_skand ss_hdemo_sk = household_demographics.hd_demo_skand ss_store_sk = s_store_sk
and time_dim.t_hour=8and time_dim.t_minute>=30and household_demographics.hd_dep_count=5and store.s_store_name='ese'orderbycount(*);

看一下这条语句的执行计划

mysql> explain selectcount(*)from store_sales     ,household_demographics     ,time_dim, store where ss_sold_time_sk
= time_dim.t_time_skand ss_hdemo_sk = household_demographics.hd_demo_skand ss_store_sk = s_store_sk     and time_dim.t_hour=8and time_dim.t_minute>=30and household_demographics.hd_dep_count=5and store.s_store_name='ese'orderbycount(*);+----+-------------+------------------------+------------+--------+---------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------------+| id | select_type |table| partitions | type   | possible_keys | key     | key_len | ref                               | rows    | filtered | Extra                                              |+----+-------------+------------------------+------------+--------+---------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------------+|1| SIMPLE      | store                  |NULL| ALL    | PRIMARY       |NULL|NULL|NULL|12|10.00| Using where||1| SIMPLE      | store_sales            |NULL| ALL    |NULL|NULL|NULL|NULL|3754283|10.00| Using where; Using join buffer (Block Nested Loop)||1| SIMPLE      | household_demographics |NULL| eq_ref | PRIMARY       | PRIMARY |4| tpcds.store_sales.ss_hdemo_sk|1|10.00| Using where||1| SIMPLE      | time_dim               |NULL| eq_ref | PRIMARY       | PRIMARY |4| tpcds.store_sales.ss_sold_time_sk|1|5.00| Using where|+----+-------------+------------------------+------------+--------+---------------+---------+---------+-----------------------------------+---------+----------+----------------------------------------------------+4 rows inset,1 warning (0.00 sec)




相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
19天前
|
SQL 关系型数据库 MySQL
如何将Excel表的数据导入RDS MySQL数据库?
本文介绍如何通过数据管理服务DMS将Excel文件(转为CSV格式)导入RDS MySQL数据库,涵盖建表、编码设置、导入模式选择及审批执行流程,并提供操作示例与注意事项。
|
25天前
|
存储 测试技术 API
数据驱动开发软件测试脚本
今天刚提交了我的新作《带着ChatGPT玩转软件开发》给出版社,在写作期间跟着ChatGPT学到许多新知识。下面分享数据驱动开发软件测试脚本。
29 0
|
3月前
|
关系型数据库 MySQL Java
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
字节面试: MySQL 百万级 导入发生的 “死锁” 难题如何解决?“2序4拆”,彻底攻克
|
9月前
|
分布式计算 Shell MaxCompute
odps测试表及大量数据构建测试
odps测试表及大量数据构建测试
|
9月前
|
人工智能 关系型数据库 MySQL
AnalyticDB MySQL版:云原生离在线一体化数据仓库支持实时业务决策
AnalyticDB MySQL版是阿里云推出的云原生离在线一体化数据仓库,支持实时业务决策。产品定位为兼具数据库应用性和大数据处理能力的数仓,适用于大规模数据分析场景。核心技术包括混合负载、异构加速、智能弹性与硬件优化及AI集成,支持流批一体架构和物化视图等功能,帮助用户实现高效、低成本的数据处理与分析。通过存算分离和智能调度,AnalyticDB MySQL可在复杂查询和突发流量下提供卓越性能,并结合AI技术提升数据价值挖掘能力。
209 16
|
10月前
|
开发框架 .NET Java
C#集合数据去重的5种方式及其性能对比测试分析
C#集合数据去重的5种方式及其性能对比测试分析
118 11
|
10月前
|
开发框架 .NET Java
C#集合数据去重的5种方式及其性能对比测试分析
C#集合数据去重的5种方式及其性能对比测试分析
144 10
|
10月前
|
关系型数据库 MySQL Linux
Linux下mysql数据库的导入与导出以及查看端口
本文详细介绍了在Linux下如何导入和导出MySQL数据库,以及查看MySQL运行端口的方法。通过这些操作,用户可以轻松进行数据库的备份与恢复,以及确认MySQL服务的运行状态和端口。掌握这些技能,对于日常数据库管理和维护非常重要。
369 8
|
10月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
11月前
|
机器学习/深度学习 算法 UED
在数据驱动时代,A/B 测试成为评估机器学习项目不同方案效果的重要方法
在数据驱动时代,A/B 测试成为评估机器学习项目不同方案效果的重要方法。本文介绍 A/B 测试的基本概念、步骤及其在模型评估、算法改进、特征选择和用户体验优化中的应用,同时提供 Python 实现示例,强调其在确保项目性能和用户体验方面的关键作用。
356 6

推荐镜像

更多