使用TPC_DS产生压测数据
下载TPC-DS压测工具：(测试版本 2.11.0)
http://www.tpc.org/tpc_documents_current_versions/current_specifications5.asp
解压之后在tools目录下执行make进行编译
编译之后使用dsdgen产生数据，其中 -scale 指定产生多大的数据，比如下面产生 1000GB，-parallel指定将数据切成4个片段，-child指定当前产生第几个片段
./dsdgen -dir /home/ec2-user/data/ -scale 1000 -parallel 4 -child 1
./dsdgen -dir /home/ec2-user/data/ -scale 1000 -parallel 4 -child 2
./dsdgen -dir /home/ec2-user/data/ -scale 1000 -parallel 4 -child 3
./dsdgen -dir /home/ec2-user/data/ -scale 1000 -parallel 4 -child 4产生查询脚本:
./dsqgen -output_dir /home/ec2-user/query/ -input ../query_templates/templates.lst -scale 1 -DIALECT netezza -DIRECTORY ../query_templates -QUALIFY y -VERBOSE Y或者用这个方法产生
for id in `seq 1 99`; do ./dsqgen -DIRECTORY ../query_templates -TEMPLATE "query$id.tpl" -DIALECT netezza -FILTER Y > ~/query/"query$id.sql"; done如果遇到_END的报错，那么在../query_templates路径下，对所有的tpl文件后面添加一行： define _END = "";，使用脚本做如下：
ls | while read line; do echo "define _END = \"\";" >> $line; done建表语句
创建表的语句在 tools文件夹下的 tpcds.sql 文件中，以及tpcds_source.sql 文件中
TPC-DS 工具说明
有关工具的使用以及目录结构说明，参考specification文件夹下的 specification.pdf 文件中的表格：Table 0-1 Electronically Available Specification Material
有关产生1G，1T，3T，10T，30T，100T的文件，每个表数据量大小，可以参考 specification文件夹下的 specification.pdf 文件中的 Table 3-2 Database Row Counts 这个表格来对比。
附录：在AWS EMR创建Hive表：
在AWS Global账号下启动过EMR进行测试的命令
aws emr create-cluster --auto-scaling-role EMR_AutoScaling_DefaultRole \
--applications Name=Hadoop Name=Hive Name=Hue Name=Ganglia Name=Spark Name=Presto --ebs-root-volume-size 10 --ec2-attributes '{"KeyName":"Virginia","InstanceProfile":"EMR_EC2_DefaultRole","SubnetId":"subnet-fc22e0a4","EmrManagedSlaveSecurityGroup":"sg-68c8f923","EmrManagedMasterSecurityGroup":"sg-08c2f343"}' \
--service-role EMR_DefaultRole \
--enable-debugging --log-uri 's3://aws-logs-372809795158-us-east-1/elasticmapreduce/' \
--name 'test-hive-spark' \
--release-label emr-5.25.0 \
--instance-groups '[{"InstanceCount":5,"EbsConfiguration":{"EbsBlockDeviceConfigs":[{"VolumeSpecification":{"SizeInGB":1000,"VolumeType":"gp2"},"VolumesPerInstance":1}],"EbsOptimized":true},"InstanceGroupType":"CORE","InstanceType":"c5.2xlarge","Name":"核心实例组 - 2"},{"InstanceCount":1,"EbsConfiguration":{"EbsBlockDeviceConfigs":[{"VolumeSpecification":{"SizeInGB":32,"VolumeType":"gp2"},"VolumesPerInstance":4}]},"InstanceGroupType":"MASTER","InstanceType":"c5.2xlarge","Name":"主实例组 - 1"}]' \
--configurations '[{"Classification":"hive-site","Properties":{"hive.metastore.client.factory.class":"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory"}},{"Classification":"spark-hive-site","Properties":{"hive.metastore.client.factory.class":"com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory"}}]' \
--steps '[{"Args":["s3-dist-cp","--src","s3://xlaws/data/catalog_sales","--dest","hdfs:///catalog_sales"],"Type":"CUSTOM_JAR","ActionOnFailure":"CONTINUE","Jar":"command-runner.jar","Properties":"","Name":"S3DistCpSales"},{"Args":["s3-dist-cp","--src","s3://xlaws/data/warehouse","--dest","hdfs:///warehouse"],"Type":"CUSTOM_JAR","ActionOnFailure":"CONTINUE","Jar":"command-runner.jar","Properties":"","Name":"S3DistCpWarehouse"},{"Args":["s3-dist-cp","--src","s3://xlaws/data/date_dim","--dest","hdfs:///date_dim"],"Type":"CUSTOM_JAR","ActionOnFailure":"CONTINUE","Jar":"command-runner.jar","Properties":"","Name":"S3DistCpDate"},{"Args":["s3-dist-cp","--src","s3://xlaws/data/ship_mode","--dest","hdfs:///ship_mode"],"Type":"CUSTOM_JAR","ActionOnFailure":"CONTINUE","Jar":"command-runner.jar","Properties":"","Name":"S3DistCpShip"},{"Args":["s3-dist-cp","--src","s3://xlaws/data/call_center","--dest","hdfs:///call_center"],"Type":"CUSTOM_JAR","ActionOnFailure":"CONTINUE","Jar":"command-runner.jar","Properties":"","Name":"S3DistCpCallCenter"}]' \
--scale-down-behavior TERMINATE_AT_TASK_COMPLETION --region us-east-1 --profile global创建S3上的表
创建catalog_sales表
CREATE EXTERNAL TABLE `catalog_sales`(
`cs_sold_date_sk` bigint,
`cs_sold_time_sk` bigint,
`cs_ship_date_sk` bigint,
`cs_bill_customer_sk` bigint,
`cs_bill_cdemo_sk` bigint,
`cs_bill_hdemo_sk` bigint,
`cs_bill_addr_sk` bigint,
`cs_ship_customer_sk` bigint,
`cs_ship_cdemo_sk` bigint,
`cs_ship_hdemo_sk` bigint,
`cs_ship_addr_sk` bigint,
`cs_call_center_sk` bigint,
`cs_catalog_page_sk` bigint,
`cs_ship_mode_sk` bigint,
`cs_warehouse_sk` bigint,
`cs_item_sk` bigint,
`cs_promo_sk` bigint,
`cs_order_number` bigint,
`cs_quantity` bigint,
`cs_wholesale_cost` double,
`cs_list_price` double,
`cs_sales_price` double,
`cs_ext_discount_amt` double,
`cs_ext_sales_price` double,
`cs_ext_wholesale_cost` double,
`cs_ext_list_price` double,
`cs_ext_tax double` double,
`cs_coupon_amt` double,
`cs_ext_ship_cost` double,
`cs_net_paid` double,
`cs_net_paid_inc_tax` double,
`cs_net_paid_inc_ship` double,
`cs_net_paid_inc_ship_tax` double,
`cs_net_profit double` double)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xlaws/data/catalog_sales';创建call_center 表
CREATE EXTERNAL TABLE `call_center`(
`cc_call_center_sk` bigint,
`cc_call_center_id` string,
`cc_rec_start_date` string,
`cc_rec_end_date` string,
`cc_closed_date_sk` string,
`cc_open_date_sk` bigint,
`cc_name` string,
`cc_class` string,
`cc_employees` bigint,
`cc_sq_ft` bigint,
`cc_hours` string,
`cc_manager` string,
`cc_mkt_id` bigint,
`cc_mkt_class` string,
`cc_mkt_desc` string,
`cc_market_manager` string,
`cc_division` bigint,
`cc_division_name` string,
`cc_company` bigint,
`cc_company_name` string,
`cc_street_number` bigint,
`cc_street_name` string,
`cc_street_type` string,
`cc_suite_number` string,
`cc_city` string,
`cc_county` string,
`cc_state` string,
`cc_zip` bigint,
`cc_country` string,
`cc_gmt_offset` bigint,
`cc_tax_percentage` double)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xlaws/data/call_center/';
创建 date_dim 表
CREATE EXTERNAL TABLE `date_dim`(
`d_date_sk` bigint,
`d_date_id` string,
`d_date` string,
`d_month_seq` bigint,
`d_week_seq` bigint,
`d_quarter_seq` bigint,
`d_year` bigint,
`d_dow` bigint,
`d_moy` bigint,
`d_dom` bigint,
`d_qoy` bigint,
`d_fy_year` bigint,
`d_fy_quarter_seq` bigint,
`d_fy_week_seq` bigint,
`d_day_name` string,
`d_quarter_name` string,
`d_holiday` string,
`d_weekend` string,
`d_following_holiday` string,
`d_first_dom` bigint,
`d_last_dom` bigint,
`d_same_day_ly` bigint,
`d_same_day_lq` bigint,
`d_current_day` string,
`d_current_week` string,
`d_current_month` string,
`d_current_quarter` string,
`d_current_year` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xlaws/data/date_dim/';创建ship_mode表
CREATE EXTERNAL TABLE `ship_mode`(
`sm_ship_mode_sk` bigint,
`sm_ship_mode_id` string,
`sm_type` string,
`sm_code` string,
`sm_carrier` string,
`sm_contract` string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xlaws/data/ship_mode/';
创建ware_house表
CREATE EXTERNAL TABLE `warehouse`(
`w_warehouse_sk` bigint,
`w_warehouse_id` string,
`w_warehouse_name` string,
`w_warehouse_sq_ft` bigint,
`w_street_number` bigint,
`w_street_name` string,
`w_street_type` string,
`w_suite_number` string,
`w_city` string,
`w_county` string,
`w_state` string,
`w_zip` bigint,
`w_country` string,
`w_gmt_offset` bigint)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://xlaws/data/warehouse/';最后更新于