tpc
官方：http://www.tpc.org/
一 简介
The TPC is a non-profit corporation founded to define transaction processing and database benchmarks and to disseminate objective, verifiable TPC performance data to the industry.
TPC(The Transaction Processing Performance Council)是一个非盈利公司，致力于定义事务处理和数据库benchmark，同时向业界发布客观的可验证的tpc性能数据；
The term transaction is often applied to a wide variety of business and computer functions. Looked at as a computer function, a transaction could refer to a set of operations including disk read/writes, operating system calls, or some form of data transfer from one subsystem to another.
While TPC benchmarks certainly involve the measurement and evaluation of computer functions and operations, the TPC regards a transaction as it is commonly understood in the business world: a commercial exchange of goods, services, or money. A typical transaction, as defined by the TPC, would include the updating to a database system for such things as inventory control (goods), airline reservations (services), or banking (money).
In these environments, a number of customers or service representatives input and manage their transactions via a terminal or desktop computer connected to a database. Typically, the TPC produces benchmarks that measure transaction processing (TP) and database (DB) performance in terms of how many transactions a given system and database can perform per unit of time, e.g., transactions per second or transactions per minute.
TPC-DS is a Decision Support Benchmark
官方：http://www.tpc.org/tpcds/default.asp
文档：http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.10.1.pdf
A simple schema for decision support systems or data warehouses is the star schema, where events are collected in large fact tables, while smaller supporting tables (dimensions) are used to describe the data.
The TPC-DS is an example of such a schema. It models a typical retail warehouse where the events are sales and typical dimensions are date of sale, time of sale, or demographic of the purchasing party.
决策支持系统的schema中，event被存放在大的事实表（fact table）中，而小的维度表（dimension table）用来描述数据；
数据库界最具挑战的一个测试基准TPC-DS，它模拟了一个典型的零售行业的数据仓库；
The TPC Benchmark DS (TPC-DS) is a decision support benchmark that models several generally applicable aspects of a decision support system, including queries and data maintenance. The benchmark provides a representative evaluation of performance as a general purpose decision support system. A benchmark result measures query response time in single user mode, query throughput in multi user mode and data maintenance performance for a given hardware, operating system, and data processing system configuration under a controlled, complex, multi-user decision support workload. The purpose of TPC benchmarks is to provide relevant, objective performance data to industry users. TPC-DS Version 2 enables emerging technologies, such as Big Data systems, to execute the benchmark.
二 使用
1 下载
http://www.tpc.org/tpc_documents_current_versions/current_specifications.asp
2 安装
生成两个工具：
3 初始化表结构sql脚本
可能需要根据具体的db修改一些诸如字段类型；
4 生成测试数据
其中 -SCALE 用于指定生成的数据规模，可以修改比如10GB，1TB
5 生成查询脚本
默认支持dialect如下：
可见默认都是针对传统的关系型数据库，下面看怎样应用于大数据场景；
三 测试hive
官方：https://github.com/hortonworks/hive-testbench
1 下载安装
2 生成测试数据和查询脚本
单位为G，修改FORMAT，比如orc、parquet等
生成日志
生成结果
3 运行测试
测试sql脚本目录：sample-queries-tpcds
4 批量测试
根据需要修改hive配置：sample-queries-tpcds/testbench.settings
根据需要修改测试脚本（perl）：runSuite.pl
核心代码：
my $suite = shift;
my $scale = shift || ;
dieWithUsage("suite name required") unless $suite eq "tpcds" or $suite eq "tpch";
chdir $SCRIPT_PATH;
if( $suite eq 'tpcds' ) {
chdir "sample-queries-tpcds";
} else {
chdir 'sample-queries-tpch';
} # end if
my @queries = glob '*.sql';
my $db = {
'tpcds' => "tpcds_bin_partitioned_orc_$scale",
'tpch' => "tpch_flat_orc_$scale"
};
print "filename,status,time,rows\n";
for my $query ( @queries ) {
my $logname = "$query.log";
my $cmd="echo 'use $db->{${suite}}; source $query;' | hive -i testbench.settings 2>&1 | tee $query.log";这个脚本有两个参数：suite scale，比如tpcds 10
可以修改的更通用，一个是数据库硬编码orc，一个是硬编码hive命令，一个是打印正在执行的cmd，一个是启动命令有初始化环境的时间成本，直接使用beeline连接server的耗时更真实；修改之后可以用于其他测试，比如spark-sql、impala、drill等；
修改之后是这样：
#!/usr/bin/perl use strict;
use warnings;
use POSIX;
use File::Basename; # PROTOTYPES
sub dieWithUsage(;$); # GLOBALS
my $SCRIPT_NAME = basename( __FILE__ );
my $SCRIPT_PATH = dirname( __FILE__ ); # MAIN
dieWithUsage("one or more parameters not defined") unless @ARGV >= ;
my $suite = shift;
my $scale = shift || ;
my $format = shift || ;
my $engineCmd = shift || ;
dieWithUsage("suite name required") unless $suite eq "tpcds" or $suite eq "tpch";
print "params: $suite, $scale, $format, $engineCmd;"; chdir $SCRIPT_PATH;
if( $suite eq 'tpcds' ) {
chdir "sample-queries-tpcds";
} else {
chdir 'sample-queries-tpch';
} # end if
my @queries = glob '*.sql'; my $db = {
'tpcds' => "tpcds_bin_partitioned_${format}_$scale",
'tpch' => "tpch_flat_${format}_$scale"
}; print "filename,status,time,rows\n";
for my $query ( @queries ) {
my $logname = "$query.log";
my $cmd="${engineCmd}/$db->{${suite}} -i conf.settings -f $query 2>&1 | tee $query.log";
# my $cmd="cat $query.log";
#print $cmd ; exit;
my $currentTime = strftime("%Y-%m-%d %H:%M:%S", localtime(time));
print "$currentTime : ";
print "$cmd \n"; my $hiveStart = time(); my @hiveoutput=`$cmd`;
die "${SCRIPT_NAME}:: ERROR: hive command unexpectedly exited \$? = '$?', \$! = '$!'" if $?; my $hiveEnd = time();
my $hiveTime = $hiveEnd - $hiveStart;
my $is_success = ;
foreach my $line ( @hiveoutput ) {
if( $line =~ /[(\d+|No)]\s+row[s]? selected \(([\d\.]+) seconds\)/ ) {
$is_success = ;
print "$query,success,$hiveTime,$1\n";
} # end if
} # end while
if( $is_success == ) {
print "$query,failed,$hiveTime\n";
}
} # end for sub dieWithUsage(;$) {
my $err = shift || '';
if( $err ne '' ) {
chomp $err;
$err = "ERROR: $err\n\n";
} # end if print STDERR <<USAGE;
${err}Usage:
perl ${SCRIPT_NAME} [tpcds|tpch] [scale] [format] [engineCmd] Description:
This script runs the sample queries and outputs a CSV file of the time it took each query to run. Also, all hive output is kept as a log file named 'queryXX.sql.log' for each query file of the form 'queryXX.sql'. Defaults to scale of .
USAGE
exit ;
}
执行：
批量测试脚本
#!/bin/sh current_dir=`pwd` scale="$1"
format="$2" if [ -z "$scale" ]; then
scale=10
fi
if [ -z "$format" ]; then
format="parquet"
fi #echo "$current_dir $component $scale $test_dir"
echo "mkdir merge"
echo "" component="hive"
test_dir="test_$component" echo "# test $component"
echo "mkdir $test_dir"
echo "cp -R sample-queries-tpcds $test_dir"
echo "ln -s $current_dir/runSuiteCommon.pl $test_dir/runSuite.pl"
echo "cd $test_dir"
echo "perl runSuite.pl tpcds ${scale} $format \"$HIVE_HOME/bin/beeline -i conf.settings -n hadoop -u jdbc:hive2://localhost:10000\" 2>&1| tee ${component}_${scale}_${format}.log"
echo "cd .."
echo "grep -e '^query' $test_dir/${component}_${scale}_${format}.log|sort > merge/${component}_${scale}_${format}.log"
echo "wc -l merge/${component}_${scale}_${format}.log"
echo "" component="spark"
test_dir="test_$component" echo "# test $component"
echo "mkdir $test_dir"
echo "cp -R sample-queries-tpcds $test_dir"
echo "ln -s $current_dir/runSuiteCommon.pl $test_dir/runSuite.pl"
echo "cd $test_dir"
echo "perl runSuite.pl tpcds ${scale} $format \"$SPARK_HOME/bin/beeline -i conf.settings -u jdbc:hive2://localhost:11111\" 2>&1| tee ${component}_${scale}_${format}.log"
echo "cd .."
echo "grep -e '^query' $test_dir/${component}_${scale}_${format}.log|sort > merge/${component}_${scale}_${format}.log"
echo "wc -l merge/${component}_${scale}_${format}.log"
echo "" component="impala"
test_dir="test_$component" echo "# test $component"
echo "mkdir $test_dir"
echo "cp -R sample-queries-tpcds $test_dir"
echo "ln -s $current_dir/runSuiteCommon.pl $test_dir/runSuite.pl"
echo "cd $test_dir"
echo "perl runSuite.pl tpcds ${scale} $format \"$HIVE_HOME/bin/beeline -i conf.settings -d com.cloudera.impala.jdbc4.Driver -u jdbc:impala://localhost:21050\" 2>&1| tee ${component}_${scale}_${format}.log"
echo "cd .."
echo "grep -e '^query' $test_dir/${component}_${scale}_${format}.log|sort > merge/${component}_${scale}_${format}.log"
echo "wc -l merge/${component}_${scale}_${format}.log"
echo "" component="presto"
test_dir="test_$component" echo "# test $component"
echo "mkdir $test_dir"
echo "cp -R sample-queries-tpcds $test_dir"
echo "ln -s $current_dir/runSuiteCommon.pl $test_dir/runSuite.pl"
echo "cd $test_dir"
echo "perl runSuite.pl tpcds ${scale} $format \"$HIVE_HOME/bin/beeline -i conf.settings -d com.facebook.presto.jdbc.PrestoDriver -n hadoop -u jdbc:presto://localhost:8080/hive\" 2>&1| tee ${component}_${scale}_${format}.log"
echo "cd .."
echo "grep -e '^query' $test_dir/${component}_${scale}_${format}.log|sort > merge/${component}_${scale}_${format}.log"
echo "wc -l merge/${component}_${scale}_${format}.log" echo "awk -F ',' '{if(NF==4){print \$1\",\"\$4}else{print \$1\",0\"}}' merge/hive_${scale}_${format}.log > /tmp/hive_${scale}_${format}.log"
echo "awk -F ',' '{if(NF==4){print \$4}else{print \"0\"}}' merge/spark_${scale}_${format}.log > /tmp/spark_${scale}_${format}.log"
echo "awk -F ',' '{if(NF==4){print \$4}else{print \"0\"}}' merge/impala_${scale}_${format}.log > /tmp/impala_${scale}_${format}.log"
echo "awk -F ',' '{if(NF==4){print \$4}else{print \"0\"}}' merge/presto_${scale}_${format}.log > /tmp/presto_${scale}_${format}.log"
echo "paste -d\",\" /tmp/hive_${scale}_${format}.log /tmp/spark_${scale}_${format}.log /tmp/impala_${scale}_${format}.log /tmp/presto_${scale}_${format}.log > merge/result_${scale}_${format}.csv"
echo "sed -i \"1i sql_${scale}_${format},hive,spark,impala,presto\" merge/result_${scale}_${format}.csv"
echo ""
结果合并之后使用excel图形化显示