例子七 使用LIST分区
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
WITH (appendonly=true,orientation=column,compresstype=QUICKLZ,COMPRESSLEVEL=1)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
partition by list (channel_id)
( partition c_1 values (2, 4),
partition c_2 values (3,9),
default partition other);
例子八
---使用RANGE分区
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
WITH (appendonly=true,orientation=column,compresstype=QUICKLZ,COMPRESSLEVEL=1)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
partition by range(time_id)
( START (date '1998-01-01') INCLUSIVE
END (date '2001-12-31') EXCLUSIVE
EVERY (INTERVAL '1 year'), DEFAULT PARTITION other);
---每个分区独立定义
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
partition by range(time_id)
(PARTITION FY1998 START (date '1998-01-01') INCLUSIVE ,
PARTITION FY1999 START (date '1999-01-01') INCLUSIVE ,
PARTITION FY2000 START (date '2000-01-01') INCLUSIVE ,
PARTITION FY2001 START (date '2001-01-01') INCLUSIVE ,
DEFAULT PARTITION extra);
例子九
--使用复合分区方式 RANGE-LIST
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
partition by range(time_id)
SUBPARTITION BY LIST(channel_id)
SUBPARTITION TEMPLATE
( subpartition c_1 values (2, 4),
subpartition c_2 values (3,9),
default subpartition other_1)
( START (date '1998-01-01') INCLUSIVE
END (date '2001-12-31') EXCLUSIVE
EVERY (INTERVAL '1 year'), DEFAULT PARTITION other_2);
--使用复合分区方式LIST-RANGE
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
PARTITION BY LIST(channel_id)
subpartition by range(time_id)
SUBPARTITION TEMPLATE
( START (date '1998-01-01') INCLUSIVE
END (date '2001-12-31') EXCLUSIVE
EVERY (INTERVAL '1 year'), DEFAULT SUBPARTITION other_2)
( partition c_1 values (2, 4),
partition c_2 values (3,9),
default partition other_1);
--使用复合分区方式RANGE-RANGE
CREATE TABLE SALES
(PROD_ID numeric NOT NULL ,
CUST_ID numeric NOT NULL ,
TIME_ID DATE NOT NULL ,
YEAR INT ,
MON INT,
CHANNEL_ID numeric NOT NULL ,
PROMO_ID numeric NOT NULL ,
QUANTITY_SOLD numeric(10,2) NOT NULL ,
AMOUNT_SOLD numeric(10,2) NOT NULL)
distributed by (prod_id,cust_id,time_id,channel_id,promo_id)
PARTITION BY RANGE (year)
subpartition by range(mon)
SUBPARTITION TEMPLATE (
START (1) INCLUSIVE END (13) EXCLUSIVE EVERY (1),
DEFAULT SUBPARTITION other_months )
( START (1998) INCLUSIVE END (2001) INCLUSIVE EVERY (1),
DEFAULT PARTITION OTHER_years );