【TPC-DS】SourceTable的建表SQL（属于Data Maintenance部分，用来测试更新维护Refresh Data）

对应的是TPC-DS文档中的

 

 ============建表SQL如下=============

-- s_purchase_lineitem
create table if not exists s_purchase_lineitem
(
plin_purchase_id bigint not null,
plin_line_number bigint not null,
plin_item_id char(16) ,
plin_promotion_id char(16) ,
plin_quantity bigint ,
plin_sale_price decimal(7,2) ,
plin_coupon_amt decimal(7,2) ,
plin_comment varchar(100)
) DISTRIBUTE BY HASH(plin_purchase_id)
;

-- s_purchase
create table if not exists s_purchase
(
purc_purchase_id bigint not null,
purc_store_id char(16) ,
purc_customer_id char(16) ,
purc_purchase_date char(10) ,
purc_purchase_time bigint ,
purc_register_id bigint ,
purc_clerk_id bigint ,
purc_comment char(100)
) DISTRIBUTE BY HASH(purc_purchase_id);

-- s_catalog_order
create table if not exists s_catalog_order
(
cord_order_id bigint not null,
cord_bill_customer_id char(16) ,
cord_ship_customer_id char(16) ,
cord_order_date char(10) ,
cord_order_time bigint ,
cord_ship_mode_id char(16) ,
cord_call_center_id char(16) ,
cord_order_comments varchar(100)
) DISTRIBUTE BY HASH(cord_order_id)
;

-- s_web_order
create table if not exists s_web_order
(
word_order_id bigint not null,
word_bill_customer_id char(16) ,
word_ship_customer_id char(16) ,
word_order_date char(10) ,
word_order_time bigint ,
word_ship_mode_id char(16) ,
word_web_site_id char(16) ,
word_order_comments char(100)
) DISTRIBUTE BY HASH(word_order_id)
;

-- s_catalog_order_lineitem
create table if not exists s_catalog_order_lineitem
(
clin_order_id bigint not null,
clin_line_number bigint not null,
clin_item_id char(16) ,
clin_promotion_id char(16) ,
clin_quantity bigint ,
clin_sales_price decimal(7,2) ,
clin_coupon_amt decimal(7,2) ,
clin_warehouse_id char(16) ,
clin_ship_date char(10) ,
clin_catalog_number bigint ,
clin_catalog_page_number bigint ,
clin_ship_cost decimal(7,2)
) DISTRIBUTE BY HASH(clin_order_id)
;

-- s_web_order_lineitem
create table if not exists s_web_order_lineitem
(
wlin_order_id bigint not null,
wlin_line_number bigint not null,
wlin_item_id char(16) ,
wlin_promotion_id char(16) ,
wlin_quantity bigint ,
wlin_sales_price decimal(7,2) ,
wlin_coupon_amt decimal(7,2) ,
wlin_warehouse_id char(16) ,
wlin_ship_date char(10) ,
wlin_ship_cost decimal(7,2) ,
wlin_web_page_id char(16)
) DISTRIBUTE BY HASH(wlin_order_id)
;

-- s_store_returns
create table if not exists s_store_returns
(
sret_store_id char(16) ,
sret_purchase_id char(16) not null,
sret_line_number bigint not null,
sret_item_id char(16) not null,
sret_customer_id char(16) ,
sret_return_date char(10) ,
sret_return_time char(10) ,
sret_ticket_number char(20) ,
sret_return_qty bigint ,
sret_return_amt decimal(7,2) ,
sret_return_tax decimal(7,2) ,
sret_return_fee decimal(7,2) ,
sret_return_ship_cost decimal(7,2) ,
sret_refunded_cash decimal(7,2) ,
sret_reversed_charge decimal(7,2) ,
sret_store_credit decimal(7,2) ,
sret_reason_id char(16)
) DISTRIBUTE BY HASH(sret_purchase_id)
;

-- s_catalog_returns
create table if not exists s_catalog_returns
(
cret_call_center_id char(16) ,
cret_order_id bigint not null,
cret_line_number bigint not null,
cret_item_id char(16) not null,
cret_return_customer_id char(16) ,
cret_refund_customer_id char(16) ,
cret_return_date char(10) ,
cret_return_time char(10) ,
cret_return_qty bigint ,
cret_return_amt decimal(7,2) ,
cret_return_tax decimal(7,2) ,
cret_return_fee decimal(7,2) ,
cret_return_ship_cost decimal(7,2) ,
cret_refunded_cash decimal(7,2) ,
cret_reversed_charge decimal(7,2) ,
cret_merchant_credit decimal(7,2) ,
cret_reason_id char(16) ,
cret_shipmode_id char(16) ,
cret_catalog_page_id char(16) ,
cret_warehouse_id char(16)
) DISTRIBUTE BY HASH(cret_order_id)
;

-- s_inventory
create table if not exists s_inventory
(
invn_warehouse_id char(16) not null,
invn_item_id char(16) not null,
invn_date char(10) not null,
invn_qty_on_hand bigint
) DISTRIBUTE BY HASH(invn_item_id)
;

-- s_web_returns
create table if not exists s_web_returns
(
wret_web_page_id char(16) ,
wret_order_id bigint not null,
wret_line_number bigint not null,
wret_item_id char(16) not null,
wret_return_customer_id char(16) ,
wret_refund_customer_id char(16) ,
wret_return_date char(10) ,
wret_return_time char(10) ,
wret_return_qty bigint ,
wret_return_amt decimal(7,2) ,
wret_return_tax decimal(7,2) ,
wret_return_fee decimal(7,2) ,
wret_return_ship_cost decimal(7,2) ,
wret_refunded_cash decimal(7,2) ,
wret_reversed_charge decimal(7,2) ,
wret_account_credit decimal(7,2) ,
wret_reason_id char(16)
) DISTRIBUTE BY HASH(wret_item_id)
;