如何使用TPC-DS生成测试数据并导入MySQL

测试环境

centos7

mysql搭建指南可参考：https://lrting.top/backend/2111/

安装依赖：

yum install gcc make flex bison byacc git

下载TPC-DS tools

下载地址：https://www.tpc.org/tpc_documents_current_versions/download_programs/tools-download-request5.asp?bm_type=TPC-DS&bm_vers=3.2.0&mode=CURRENT-ONLY

下载之后得到类似如下文件名压缩包：1d87cca3-7268-4f0a-8678-36564d0b45f9-tpc-ds-tool.zip，解压编译

unzip 1d87cca3-7268-4f0a-8678-36564d0b45f9-tpc-ds-tool.zip
cd DSGen-software-code-3.2.0rc1/tools/
make

数据生成与导入

在mysql中新建tpcds数据库

create database tpcds DEFAULT CHARSET utf8 COLLATE utf8_general_ci

将表结构导入数据库中

mysql -u root -p -D tpcds < DSGen-software-code-3.2.0rc1/tools/tpcds.sql

生成测试数据，其中-SCALE 10 表示生成10G数据

mkdir /home/hadoop/data
cd DSGen-software-code-3.2.0rc1/tools/
./dsdgen -DIR /home/hadoop/data -SCALE 10 -TERMINATE N, tpcds.sql

数据导入

LOAD DATA LOCAL INFILE '/home/hadoop/data/call_center.dat' INTO TABLE 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的问题 先查看当前的secure-file-priv值，
show variables like ‘%secure%’;
若为NULL，表示限制不能导入导出数据，需要在mysql-5.7/my.cnf中修改该值
加上secure_file_priv=”
重启mysql

重新导入，导入SQL

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