analyticdb mysql_AnalyticDB MySQL

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);