形容七月Tpc-ds测试以及结果
1.1 背景介绍
TPC-DS是衡量决策⽀持解决⽅案的性能，包括事实上的⾏业标准，但不限于，⼤数据系统。
⽬前版本为V2。它模型的⼏个⼀般适⽤的⽅⾯的决策⽀持系统，包括查询和数据维护。
虽然TPC-DS基本的商业模式是零售产品供应商、数据库架构、数据查询、数据维护⼈⼝模型和实施规则被设计成具有⼴泛代表性的现代决策⽀持系统。
1.2 测试⽬的及重点
本测试旨在测试spark2.0的sql兼容性及性能测试，主要对由⼯具⽣成的99个sql进⾏测试。
1.3 下载
下载并解压⾄hadoop集群
Ps：因为⽆法从虚拟终端⽆法复制，多以截图为主！
1.4 编译
进⼊到tools⽬录
投资招商# make -f Makefile.suite
编译成功后会⽣成dsdgen和dsqgen两个⽂件
1.5 ⽣成数据
Scale 以GB作为单位
1.6 ⽣成sql
# vi hive.tpl
在最后⼀⾏加上define _END =””;
创建存储sql的⽂件夹黑色桌面
⽣成sql样例
# cd tools
# for i in {1..99}; do ./dsqgen -template ../query_templates/querry$i.tpl -directory ../query_templates/ -dialect hive -scale 1 -output ../sql; mv /home/hadoop/ly_tpc_ds/v2.3.0.pc_bak/sql/querrt_0.sql；done
1.1 建表及导⼊数据
create table store_sales实践活动有哪些
(
ss_sold_date_sk bigint,
ss_sold_time_sk bigint,
ss_item_sk bigint,
ss_customer_sk bigint,
ss_cdemo_sk bigint,
ss_hdemo_sk bigint,
ss_addr_sk bigint,
ss_store_sk bigint,
ss_promo_sk bigint,
ss_ticket_number bigint,
ss_quantity int,
ss_wholesale_cost decimal(7,2),
ss_list_price decimal(7,2),
ss_sales_price decimal(7,2),
ss_ext_discount_amt decimal(7,2),
ss_ext_sales_price decimal(7,2),
ss_ext_wholesale_cost decimal(7,2),
ss_ext_list_price decimal(7,2),
ss_ext_tax decimal(7,2),
ss_coupon_amt decimal(7,2),
ss_net_paid decimal(7,2),
ss_net_paid_inc_tax decimal(7,2),
NULL DEFINED AS ''
STORED AS TEXTFILE;
load data inpath '/data_1tb/store_sales.dat' overwrite into table store_sales;
create table customer_demographics
(
cd_demo_sk bigint,
cd_gender string,
cd_marital_status string,
cd_education_status string,
cd_purcha_estimate int,
cd_credit_rating string,
户外亲子活动cd_dep_count int,
cd_dep_employed_count int,
cd_dep_college_count int
)row format delimited fields terminated by '|'
NULL DEFINED AS ''
STORED AS TEXTFILE;
load data inpath '/data_1tb/customer_demographics.dat' overwrite into table customer_demographics; create table date_dim
(
d_date_sk bigint,
d_date_id string,
d_date string, -- YYYY-MM-DD format
d_month_q int,
d_week_q int,
d_quarter_q int,
d_year int,
d_dow int,
d_moy int,
d_dom int,
d_qoy int,
d_fy_year int,
d_quarter_name string,
d_holiday string,
d_weekend string,
d_following_holiday string,
d_first_dom int,
标准论文格式d_last_dom int,
d_same_day_ly int,
d_same_day_lq int,
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 '|'
NULL DEFINED AS ''
STORED AS TEXTFILE;
load data inpath '/data_1tb/date_dim.dat' overwrite into table date_dim; create table item
(
i_item_sk bigint,
i_item_id string,
i_rec_start_date string,
i_rec_end_date string,
i_item_desc string,
i_current_price decimal(7,2),
i_wholesale_cost decimal(7,2),
i_brand_id int,
i_brand string,
i_class_id int,
i_class string,
i_category_id int,
i_size string,
i_formulation string,
i_color string,
i_units string,
i_container string,
i_manager_id int,
i_product_name string
)
row format delimited fields terminated by '|'
NULL DEFINED AS ''
STORED AS TEXTFILE
;
load data inpath '/data_1tb/item.dat' overwrite into table item; create table store
(
s_store_sk bigint,
我国的国情s_store_id string,
s_rec_start_date string,
s_rec_end_date string,
s_clod_date_sk bigint,
s_store_name string,
柳绿成语
s_number_employees int,
s_floor_space int,
s_hours string,
s_manager string,
s_market_id int,
s_geography_class string,
s_market_desc string,
s_market_manager string,
s_division_id int,
s_division_name string,
