This topic describes how to create 24 tables for a performance test in AnalyticDB for MySQL, including 7 fact tables and 17 replicated tables that contain business data.
Note
The TPC-DS performance tests described in this topic are implemented based on the TPC-DS benchmark test but do not meet all requirements of the TPC-DS benchmark test. Therefore, the test results described in this topic cannot be compared with the published results of the TPC-DS benchmark test.
Prerequisites
An AnalyticDB for MySQL cluster of V3.2.3.3 or later is created.
Create test tables
Execute the following statements to create four primary tables:
customer table
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, KEY idx_c_current_hdemo_sk (c_current_hdemo_sk), PRIMARY KEY(c_customer_sk) ) DISTRIBUTED BY HASH(c_customer_sk) compression='lz4hc';promotion table
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) compression='lz4hc';web_page table
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) compression='lz4hc';income_band table
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 compression='lz4hc';
Execute the following statements to create another 20 tables:
catalog_returns table
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, KEY idx_cr_returned_date_sk (cr_returned_date_sk), KEY idx_cr_return_amount (cr_return_amount) ) DISTRIBUTED BY HASH(cr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(cr_returned_date_sk)) PARTITION NUM 1000 compression='lz4hc';catalog_sales table
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, KEY idx_cs_sold_date_sk (cs_sold_date_sk), KEY idx_cs_item_sk (cs_item_sk), KEY idx_cs_bill_hdemo_sk (cs_bill_hdemo_sk), KEY idx_cs_quantity (cs_quantity), KEY idx_cs_net_paid (cs_net_paid), KEY idx_cs_net_profit (cs_net_profit), KEY idx_cs_ship_addr_sk (cs_ship_addr_sk) ) DISTRIBUTED BY HASH(cs_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(cs_sold_date_sk)) PARTITION NUM 1000 compression='lz4hc';inventory table
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, KEY idx_inv_quantity_on_hand (inv_quantity_on_hand), KEY idx_inv_date_sk (inv_date_sk) ) DISTRIBUTED BY HASH(inv_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(inv_date_sk)) PARTITION NUM 1000 compression='lz4hc';store_returns table
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, KEY idx_sr_item_sk (sr_item_sk), KEY idx_sr_returned_date_sk (sr_returned_date_sk), KEY idx_sr_return_amt (sr_return_amt) ) DISTRIBUTED BY HASH(sr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(sr_returned_date_sk)) PARTITION NUM 1000 compression='lz4hc';store_sales table
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, KEY idx_ss_store_sk (ss_store_sk), KEY idx_ss_hdemo_sk (ss_hdemo_sk), KEY idx_ss_item_sk (ss_item_sk), KEY idx_ss_sold_date_sk (ss_sold_date_sk), KEY idx_ss_net_profit (ss_net_profit), KEY idx_ss_net_paid (ss_net_paid), KEY idx_ss_sales_price (ss_sales_price), KEY idx_ss_quantity (ss_quantity), KEY idx_ss_list_price (ss_list_price), KEY idx_ss_coupon_amt (ss_coupon_amt), KEY idx_ss_wholesale_cost (ss_wholesale_cost), KEY idx_ss_addr_sk (ss_addr_sk), CONSTRAINT `ss_customer_sk_fk` FOREIGN KEY (`ss_customer_sk`) REFERENCES `customer`(`c_customer_sk`), CONSTRAINT `ss_promo_sk_fk` FOREIGN KEY (`ss_promo_sk`) REFERENCES `promotion`(`p_promo_sk`) ) DISTRIBUTED BY HASH(ss_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(ss_sold_date_sk)) PARTITION NUM 1000 compression='lz4hc'web_returns table
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, KEY idx_wr_returned_date_sk (wr_returned_date_sk), KEY idx_wr_return_amt (wr_return_amt), KEY idx_wr_order_number (wr_order_number) ) DISTRIBUTED BY HASH(wr_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(wr_returned_date_sk)) PARTITION NUM 1000 compression='lz4hc';web_sales table
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, KEY idx_ws_web_site_sk (ws_web_site_sk), KEY idx_ws_sold_date_sk (ws_sold_date_sk), KEY idx_ws_ship_date_sk (ws_ship_date_sk), KEY idx_ws_quantity (ws_quantity), KEY idx_ws_net_profit (ws_net_profit), KEY idx_ws_net_paid (ws_net_paid), KEY idx_ws_ship_customer_sk (ws_ship_customer_sk), KEY idx_ws_sales_price (ws_sales_price), CONSTRAINT `ws_web_page_sk_fk` FOREIGN KEY (`ws_web_page_sk`) REFERENCES `web_page`(`wp_web_page_sk`) ) DISTRIBUTED BY HASH(ws_item_sk) PARTITION BY VALUE(JULIAN_DATE_TO_YEAR_MONTH(ws_sold_date_sk)) PARTITION NUM 1000 compression='lz4hc'call_center table
CREATE TABLE call_center ( cc_call_center_sk BIGINT NOT NULL, cc_call_center_id CHAR(16) NOT6 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 compression='lz4hc';catalog_page table
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) compression='lz4hc';customer_address table
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) compression='lz4hc';customer_demographics table
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) compression='lz4hc';date_dim table
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 compression='lz4hc';household_demographics table
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), CONSTRAINT `hd_income_band_sk_fk` FOREIGN KEY (`hd_income_band_sk`) REFERENCES `income_band`(`ib_income_band_sk`) ) DISTRIBUTED BY HASH(hd_demo_sk) compression='lz4hc'item table
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) compression='lz4hc' ;reason table
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 compression='lz4hc';ship_mode table
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 compression='lz4hc';store table
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 compression='lz4hc';time_dim table
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) compression='lz4hc';warehouse table
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 compression='lz4hc';web_site table
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 compression='lz4hc';