本次性能测试将在AnalyticDB MySQL中创建25个表。
TPC-DS测试会生成25张表，其中包括7张业务数据的事实表，17张业务数据的维度表，还有1张TPC-DS的系统表（与性能测试无关）。
说明 本文的TPC-DS的实现基于TPC-DS的基准测试，并不能与已发布的TPC-DS基准测试结果相比较，本文中的测试并不符合TPC-DS基准测试的所有要求。
| 表类型 | 表名称 | 说明 |
|---|---|---|
| 事实表 | catalog_sales | 通过catalog渠道销售商品的订单信息 |
| catalog_returns | 通过catalog渠道销售商品的退货信息 | |
| store_sales | 通过store渠道销售商品的订单信息 | |
| store_returns | 通过store渠道销售商品的退货信息 | |
| web_sales | 通过Web渠道销售商品的订单信息 | |
| web_returns | 通过Web渠道销售商品的退货信息 | |
| inventory | 仓储相关信息 | |
| 维度表 | call_center | 客户服务中心相关信息 |
| catalog_page | 商品目录相关信息 | |
| customer | 客户相关信息 | |
| customer_address | 客户地址信息 | |
| customer_demographics | 客户基本信用信息 | |
| date_dim | 时间维度信息 | |
| household_demographics | 家庭基本信用信息 | |
| income_band | 收入信息 | |
| item | 商品信息 | |
| promotion | 商品促销信息 | |
| reason | 用户退货原因 | |
| ship_mode | 商品快递信息 | |
| store | 商户信息 | |
| time_dim | 时间维度信息 | |
| warehouse | 仓库级别信息 | |
| web_page | 商品网页基本信息 | |
| web_site | 商品网站基本信息 | |
| 系统表 | dbgen_version | TPC-DS的命令记录 |
创建表的SQL语句如下所示：
- catalog_returns表
create table catalog_returns ( cr_returned_date_sk bigint, cr_returned_time_sk bigint, cr_item_sk bigint not null, cr_refunded_customer_sk bigint, cr_refunded_cdemo_sk bigint, cr_refunded_hdemo_sk bigint, cr_refunded_addr_sk bigint, cr_returning_customer_sk bigint, cr_returning_cdemo_sk bigint, cr_returning_hdemo_sk bigint, cr_returning_addr_sk bigint, cr_call_center_sk bigint, cr_catalog_page_sk bigint , cr_ship_mode_sk bigint , cr_warehouse_sk bigint , cr_reason_sk bigint , cr_order_number bigint not null, cr_return_quantity int, cr_return_amount decimal(7,2), cr_return_tax decimal(7,2), cr_return_amt_inc_tax decimal(7,2), cr_fee decimal(7,2), cr_return_ship_cost decimal(7,2), cr_refunded_cash decimal(7,2), cr_reversed_charge decimal(7,2), cr_store_credit decimal(7,2), cr_net_loss decimal(7,2) ) DISTRIBUTE BY HASH(cr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(cr_returned_date_sk)) PARTITION NUM 1000; - catalog_sales表
create 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 not null, cs_promo_sk bigint, cs_order_number bigint not null, cs_quantity int, cs_wholesale_cost decimal(7,2), cs_list_price decimal(7,2), cs_sales_price decimal(7,2), cs_ext_discount_amt decimal(7,2), cs_ext_sales_price decimal(7,2), cs_ext_wholesale_cost decimal(7,2), cs_ext_list_price decimal(7,2), cs_ext_tax decimal(7,2), cs_coupon_amt decimal(7,2), cs_ext_ship_cost decimal(7,2), cs_net_paid decimal(7,2), cs_net_paid_inc_tax decimal(7,2), cs_net_paid_inc_ship decimal(7,2), cs_net_paid_inc_ship_tax decimal(7,2), cs_net_profit decimal(7,2) ) DISTRIBUTE BY HASH(cs_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(cs_sold_date_sk)) PARTITION NUM 1000; - inventory表
create table inventory ( inv_date_sk bigint not null, inv_item_sk bigint not null, inv_warehouse_sk bigint not null, inv_quantity_on_hand int ) DISTRIBUTE BY HASH(inv_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(inv_date_sk)) PARTITION NUM 1000 ; - store_returns表
create table store_returns ( sr_returned_date_sk bigint, sr_return_time_sk bigint, sr_item_sk bigint not null, sr_customer_sk bigint, sr_cdemo_sk bigint, sr_hdemo_sk bigint, sr_addr_sk bigint, sr_store_sk bigint, sr_reason_sk bigint, sr_ticket_number bigint not null, sr_return_quantity int, sr_return_amt decimal(7,2), sr_return_tax decimal(7,2), sr_return_amt_inc_tax decimal(7,2), sr_fee decimal(7,2), sr_return_ship_cost decimal(7,2), sr_refunded_cash decimal(7,2), sr_reversed_charge decimal(7,2), sr_store_credit decimal(7,2), sr_net_loss decimal(7,2) ) DISTRIBUTE BY HASH(sr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(sr_returned_date_sk)) PARTITION NUM 1000; - store_sales表
create table store_sales ( ss_sold_date_sk bigint, ss_sold_time_sk bigint, ss_item_sk bigint not null, 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 not null, 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), ss_net_profit decimal(7,2) ) DISTRIBUTE BY HASH(ss_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(ss_sold_date_sk)) PARTITION NUM 1000; - web_returns表
create table web_returns ( wr_returned_date_sk bigint, wr_returned_time_sk bigint, wr_item_sk bigint not null, wr_refunded_customer_sk bigint, wr_refunded_cdemo_sk bigint, wr_refunded_hdemo_sk bigint, wr_refunded_addr_sk bigint, wr_returning_customer_sk bigint, wr_returning_cdemo_sk bigint, wr_returning_hdemo_sk bigint, wr_returning_addr_sk bigint, wr_web_page_sk bigint, wr_reason_sk bigint, wr_order_number bigint not null, wr_return_quantity int, wr_return_amt decimal(7,2), wr_return_tax decimal(7,2), wr_return_amt_inc_tax decimal(7,2), wr_fee decimal(7,2), wr_return_ship_cost decimal(7,2), wr_refunded_cash decimal(7,2), wr_reversed_charge decimal(7,2), wr_account_credit decimal(7,2), wr_net_loss decimal(7,2) ) DISTRIBUTE BY HASH(wr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(wr_returned_date_sk)) PARTITION NUM 1000; - web_sales表
create table web_sales ( ws_sold_date_sk bigint, ws_sold_time_sk bigint, ws_ship_date_sk bigint, ws_item_sk bigint not null, ws_bill_customer_sk bigint, ws_bill_cdemo_sk bigint, ws_bill_hdemo_sk bigint, ws_bill_addr_sk bigint, ws_ship_customer_sk bigint, ws_ship_cdemo_sk bigint, ws_ship_hdemo_sk bigint, ws_ship_addr_sk bigint, ws_web_page_sk bigint, ws_web_site_sk bigint, ws_ship_mode_sk bigint, ws_warehouse_sk bigint, ws_promo_sk bigint, ws_order_number bigint not null, ws_quantity int, ws_wholesale_cost decimal(7,2), ws_list_price decimal(7,2), ws_sales_price decimal(7,2), ws_ext_discount_amt decimal(7,2), ws_ext_sales_price decimal(7,2), ws_ext_wholesale_cost decimal(7,2), ws_ext_list_price decimal(7,2), ws_ext_tax decimal(7,2), ws_coupon_amt decimal(7,2), ws_ext_ship_cost decimal(7,2), ws_net_paid decimal(7,2), ws_net_paid_inc_tax decimal(7,2), ws_net_paid_inc_ship decimal(7,2), ws_net_paid_inc_ship_tax decimal(7,2), ws_net_profit decimal(7,2) ) DISTRIBUTE BY HASH(ws_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(ws_sold_date_sk)) PARTITION NUM 1000; - call_center表
create table call_center ( cc_call_center_sk bigint not null, cc_call_center_id char(16) not null, cc_rec_start_date date, cc_rec_end_date date, cc_closed_date_sk bigint, cc_open_date_sk bigint, cc_name varchar(50), cc_class varchar(50), cc_employees int, cc_sq_ft int, cc_hours char(20), cc_manager varchar(40), cc_mkt_id int, cc_mkt_class char(50), cc_mkt_desc varchar(100), cc_market_manager varchar(40), cc_division int, cc_division_name varchar(50), cc_company int, cc_company_name char(50), cc_street_number char(10), cc_street_name varchar(60), cc_street_type char(15), cc_suite_number char(10), cc_city varchar(60), cc_county varchar(30), cc_state char(2), cc_zip char(10), cc_country varchar(20), cc_gmt_offset decimal(5,2), cc_tax_percentage decimal(5,2), PRIMARY key(cc_call_center_sk) ) DISTRIBUTED BY BROADCAST; - catalog_page表
create table catalog_page ( cp_catalog_page_sk bigint not null, cp_catalog_page_id varchar(16) not null, cp_start_date_sk bigint, cp_end_date_sk bigint, cp_department varchar(50), cp_catalog_number int, cp_catalog_page_number int, cp_description varchar(100), cp_type varchar(100), primary key(cp_catalog_page_sk) ) DISTRIBUTE BY HASH(cp_catalog_page_sk);
- customer表
CREATE TABLE customer ( c_customer_sk bigint NOT NULL, c_customer_id char(16) NOT NULL, c_current_cdemo_sk bigint, c_current_hdemo_sk bigint, c_current_addr_sk bigint, c_first_shipto_date_sk bigint, c_first_sales_date_sk bigint, c_salutation char(10), c_first_name char(20), c_last_name char(30), c_preferred_cust_flag char(1), c_birth_day int, c_birth_month int, c_birth_year int, c_birth_country varchar(20), c_login char(13), c_email_address char(50), c_last_review_date_sk bigint, PRIMARY key(c_customer_sk) ) DISTRIBUTE BY HASH(c_customer_sk); - customer_address表
CREATE TABLE customer_address ( ca_address_sk bigint NOT NULL, ca_address_id varchar(16) NOT NULL, ca_street_number varchar(10), ca_street_name varchar(60), ca_street_type varchar(15), ca_suite_number varchar(10), ca_city varchar(60), ca_county varchar(30), ca_state varchar(2), ca_zip varchar(10), ca_country varchar(20), ca_gmt_offset decimal(5,2), ca_location_type varchar(20), primary key(ca_address_sk) ) DISTRIBUTE BY HASH(ca_address_sk); - customer_demographics表
create table customer_demographics ( cd_demo_sk bigint not null, cd_gender char(1), cd_marital_status char(1), cd_education_status char(20), cd_purchase_estimate int, cd_credit_rating char(10), cd_dep_count int, cd_dep_employed_count int, cd_dep_college_count int, primary key(cd_demo_sk) ) DISTRIBUTE BY HASH(cd_demo_sk); - date_dim表
create table date_dim ( d_date_sk bigint not null, d_date_id char(16) not null, d_date date, d_month_seq int, d_week_seq int, d_quarter_seq int, d_year int, d_dow int, d_moy int, d_dom int, d_qoy int, d_fy_year int, d_fy_quarter_seq int, d_fy_week_seq int, d_day_name char(9), d_quarter_name char(6), d_holiday char(1), d_weekend char(1), d_following_holiday char(1), d_first_dom int, d_last_dom int, d_same_day_ly int, d_same_day_lq int, d_current_day char(1), d_current_week char(1), d_current_month char(1), d_current_quarter char(1), d_current_year char(1), primary key(d_date_sk) ) DISTRIBUTED BY BROADCAST; - household_demographics表
create table household_demographics ( hd_demo_sk bigint not null, hd_income_band_sk bigint, hd_buy_potential char(15), hd_dep_count int, hd_vehicle_count int, PRIMARY key(hd_demo_sk) ) DISTRIBUTE BY HASH(hd_demo_sk); - income_band表
create table income_band ( ib_income_band_sk bigint not null, ib_lower_bound int, ib_upper_bound int, PRIMARY key(ib_income_band_sk) ) DISTRIBUTED BY BROADCAST; - item表
create table item ( i_item_sk bigint not null, i_item_id char(16) not null, i_rec_start_date date, i_rec_end_date date, i_item_desc varchar(200), i_current_price decimal(7,2), i_wholesale_cost decimal(7,2), i_brand_id int, i_brand char(50), i_class_id int, i_class char(50), i_category_id int, i_category char(50), i_manufact_id int, i_manufact char(50), i_size char(20), i_formulation char(20), i_color char(20), i_units char(10), i_container char(10), i_manager_id int, i_product_name char(50), PRIMARY key(i_item_sk) ) DISTRIBUTE BY HASH(i_item_sk); - promotion表
create table promotion ( p_promo_sk bigint not null, p_promo_id char(16) not null, p_start_date_sk bigint, p_end_date_sk bigint, p_item_sk bigint, p_cost decimal(15,2), p_response_target int, p_promo_name char(50), p_channel_dmail char(1), p_channel_email char(1), p_channel_catalog char(1), p_channel_tv char(1), p_channel_radio char(1), p_channel_press char(1), p_channel_event char(1), p_channel_demo char(1), p_channel_details varchar(100), p_purpose char(15), p_discount_active char(1), PRIMARY key(p_promo_sk) ) DISTRIBUTE BY HASH(p_promo_sk); - reason表
create table reason ( r_reason_sk bigint not null, r_reason_id char(16) not null, r_reason_desc char(100), PRIMARY key(r_reason_sk) ) DISTRIBUTED BY BROADCAST;
- ship_mode表
create table ship_mode ( sm_ship_mode_sk bigint, sm_ship_mode_id char(16) not null, sm_type char(30), sm_code char(10), sm_carrier char(20), sm_contract char(20), primary key(sm_ship_mode_sk) ) DISTRIBUTED BY BROADCAST; - store表
create table store ( s_store_sk bigint not null, s_store_id char(16) not null, s_rec_start_date date, s_rec_end_date date, s_closed_date_sk bigint, s_store_name varchar(50), s_number_employees int, s_floor_space int, s_hours char(20), s_manager varchar(40), s_market_id int, s_geography_class varchar(100), s_market_desc varchar(100), s_market_manager varchar(40), s_division_id int, s_division_name varchar(50), s_company_id int, s_company_name varchar(50), s_street_number varchar(10), s_street_name varchar(60), s_street_type char(15), s_suite_number char(10), s_city varchar(60), s_county varchar(30), s_state char(2), s_zip char(10), s_country varchar(20), s_gmt_offset decimal(5,2), s_tax_percentage decimal(5,2), PRIMARY key(s_store_sk) ) DISTRIBUTED BY BROADCAST; - time_dim表
create table time_dim ( t_time_sk bigint not null, t_time_id char(16) not null, t_time int, t_hour int, t_minute int, t_second int, t_am_pm char(2), t_shift char(20), t_sub_shift char(20), t_meal_time char(20), primary key(t_time_sk) ) DISTRIBUTE BY HASH(t_time_sk); - warehouse表
create table warehouse ( w_warehouse_sk bigint not null, w_warehouse_id char(16) not null, w_warehouse_name varchar(20), w_warehouse_sq_ft int, w_street_number char(10), w_street_name varchar(60), w_street_type char(15), w_suite_number char(10), w_city varchar(60), w_county varchar(30), w_state char(2), w_zip char(10), w_country varchar(20), w_gmt_offset decimal(5,2), primary key(w_warehouse_sk) ) DISTRIBUTED BY BROADCAST; - web_page表
create table web_page ( wp_web_page_sk bigint not null, wp_web_page_id char(16) not null, wp_rec_start_date date, wp_rec_end_date date, wp_creation_date_sk bigint, wp_access_date_sk bigint, wp_autogen_flag char(1), wp_customer_sk bigint, wp_url varchar(100), wp_type char(50), wp_char_count int, wp_link_count int, wp_image_count int, wp_max_ad_count int, PRIMARY key(wp_web_page_sk) ) DISTRIBUTE BY HASH(wp_web_page_sk); - web_site表
create table web_site ( web_site_sk bigint not null, web_site_id char(16) not null, web_rec_start_date date, web_rec_end_date date, web_name varchar(50), web_open_date_sk bigint, web_close_date_sk bigint, web_class varchar(50), web_manager varchar(40), web_mkt_id int, web_mkt_class varchar(50), web_mkt_desc varchar(100), web_market_manager varchar(40), web_company_id int, web_company_name char(50), web_street_number char(10), web_street_name varchar(60), web_street_type char(15), web_suite_number char(10), web_city varchar(60), web_county varchar(30), web_state char(2), web_zip char(10), web_country varchar(20), web_gmt_offset decimal(5,2), web_tax_percentage decimal(5,2), PRIMARY key(web_site_sk) ) DISTRIBUTED BY BROADCAST;