本次性能测试将在AnalyticDB for MySQL中创建24个表，其中包括7张业务数据的事实表，17张业务数据的复制表。
说明
本文的TPC-DS的实现基于TPC-DS的基准测试，并不能与已发布的TPC-DS基准测试结果相比较，本文中的测试并不符合TPC-DS基准测试的所有要求。
开启Native计算引擎和XUANWU_V2引擎（可选）
重要
Native计算引擎目前在邀测中。仅3.2.2.1及以上版本集群支持执行SET命令开启Native计算引擎和XUANWU_V2引擎。3.2.2.1以下内核版本，请提交工单联系技术支持升级内核版本，再直接执行SET命令开启Native计算引擎和XUANWU_V2引擎。
升级内核版本时会重启集群，集群将发生连接闪断，请在业务低峰期执行该操作，并确保应用程序具备重连机制。
如果您只需测试AnalyticDB for MySQL原生计算引擎和XUANWU引擎的性能，可跳过该步骤，直接创建测试表。如果您想测试Native计算引擎和XUANWU_V2引擎的性能，请在构建测试表之前打开Native计算引擎和和XUANWU_V2引擎，方法如下：
SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;创建表
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), dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR ) DISTRIBUTED 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, dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR ) DISTRIBUTED 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), dummy VARCHAR, 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), dummy VARCHAR, primary key(cp_catalog_page_sk) ) DISTRIBUTED 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, dummy VARCHAR, PRIMARY key(c_customer_sk) ) DISTRIBUTED 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), dummy VARCHAR, primary key(ca_address_sk) ) DISTRIBUTED 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, dummy VARCHAR, primary key(cd_demo_sk) ) DISTRIBUTED 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), dummy VARCHAR, 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, dummy VARCHAR, PRIMARY key(hd_demo_sk) ) DISTRIBUTED 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, dummy VARCHAR, 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), dummy VARCHAR, PRIMARY key(i_item_sk) ) DISTRIBUTED 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), dummy VARCHAR, PRIMARY key(p_promo_sk) ) DISTRIBUTED 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), dummy VARCHAR, 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), dummy VARCHAR, 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), dummy VARCHAR, 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), dummy VARCHAR, PRIMARY key(t_time_sk) ) DISTRIBUTED 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), dummy VARCHAR, 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, dummy VARCHAR, PRIMARY key(wp_web_page_sk) ) DISTRIBUTED 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), dummy VARCHAR, PRIMARY key(web_site_sk) ) DISTRIBUTED BY BROADCAST;
文档内容是否对您有帮助？