文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Teradata语法迁移/ 表迁移/ PARTITION

更新时间：2024-10-26 GMT+08:00

PARTITION

工具不支持迁移分区/子分区，在迁移后脚本中注释掉以下分区/子分区的关键字：

范围分区/子分区
列表分区/子分区
哈希分区/子分区

场景1：假设参数tdMigrateCASE_N和tdMigrateRANGE_N分别设置为comment和range。

以下示例为Teradata建表脚本，指定嵌套分区。

输入：PARTITION BY RANGE_N

CREATE TABLE tab1
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
PRIMARY INDEX (entry_id, oper_id, source_system_cd)
PARTITION BY ( CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                             , RANGE_N(  entry_dt BETWEEN DATE '2012-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY, NO RANGE )
                             );

输出：

CREATE TABLE tab1
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
DISTRIBUTE BY HASH (entry_id, oper_id, source_system_cd)
PARTITION BY RANGE (entry_dt) ( PARTITION tab1_p1 START (CAST('2012-01-01' AS DATE)) 
                                                                             END (CAST('2025-12-31' AS DATE)) 
                                                                             EVERY (INTERVAL '1' DAY) );

场景2：假设参数tdMigrateCASE_N和tdMigrateRANGE_N分别设置为comment和range。

以下示例为Teradata建表脚本，指定嵌套分区。

输入：

CREATE TABLE tab2
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
PRIMARY INDEX (entry_id, oper_id, source_system_cd)
PARTITION BY ( RANGE_N(  entry_dt BETWEEN DATE '2012-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY, NO RANGE )
                             , CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                             );

输出：

CREATE TABLE tab2
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
DISTRIBUTE BY HASH (entry_id, oper_id, source_system_cd)
PARTITION BY RANGE (entry_dt) ( PARTITION tab2_p1 START (CAST('2012-01-01' AS DATE)) 
                                                                             END (CAST('2025-12-31' AS DATE)) 
                                                                             EVERY (INTERVAL '1' DAY) );

场景3：假设参数tdMigrateCASE_N和tdMigrateRANGE_N分别设置为非comment和range的值。

工具支持迁移，且不会注释掉分区语法。

输入：

CREATE TABLE tab1
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
PRIMARY INDEX (entry_id, oper_id, source_system_cd)
PARTITION BY ( CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                             , RANGE_N(  entry_dt BETWEEN DATE '2012-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY, NO RANGE )
                             );

输出：

CREATE TABLE tab2
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
DISTRIBUTE BY HASH (entry_id, oper_id, source_system_cd)
/* PARTITION BY ( CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                             , RANGE_N(  entry_dt BETWEEN DATE '2012-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY, NO RANGE )
                             ) */
;

场景4：假设参数tdMigrateCASE_N和tdMigrateRANGE_N设为任意值。

以下示例为Teradata建表脚本，指定RANGE_N分区，未指定嵌套分区。

输入：

CREATE TABLE tab4
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
PRIMARY INDEX (entry_id, oper_id, source_system_cd)
PARTITION BY (RANGE_N(  entry_dt BETWEEN DATE '2012-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' DAY, NO RANGE )
                       );

输出：

CREATE TABLE tab4
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
DISTRIBUTE BY HASH (entry_id, oper_id, source_system_cd)
PARTITION BY RANGE (entry_dt) ( PARTITION tab4_p1 START (CAST('2012-01-01' AS DATE)) 
                                                                             END (CAST('2025-12-31' AS DATE)) 
                                                                             EVERY (INTERVAL '1' DAY) );

场景5：假设参数tdMigrateCASE_N和tdMigrateRANGE_N分别设置为comment和range。

以下示例为Teradata建表脚本，指定CASE_N分区，未指定嵌套分区。

输入：

CREATE TABLE tab5
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
PRIMARY INDEX (entry_id, oper_id, source_system_cd)
PARTITION BY ( CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                     );

输出：

CREATE TABLE tab5
     ( entry_id           integer   not null
          , oper_id            integer   not null
          , source_system_cd   varchar(5)
          , entry_dt           date
          , file_id            integer   
           , load_id            integer
          , contract_id        varchar(50)
          , contract_type_cd   varchar(50)
          )
DISTRIBUTE BY HASH (entry_id, oper_id, source_system_cd)
/* PARTITION BY ( CASE_N( source_system_cd = '00000'
                                                , source_system_cd = '00002'
                                                , source_system_cd = '00006'
                                                , source_system_cd = '00018'
                                                , NO CASE )
                     ) */
;

RANGE_N在字符串列的分区

输入：

CREATE SET TABLE SC.TAB , NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM=DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
ACCOUNT_NUM VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
,ACCOUNT_MODIFIER_NUM CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
,DATA_SOURCE_ID CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC 
,END_DT DATE FORMAT 'YYYY-MM-DD'
,UPD_TXF_BATCHTD INTEGER COMPRESS
)
PRIMARY INDEX XPKT0300_AGREEMENT (ACCOUNT_NUM,ACCOUNT_MODIFIER_NUM)
PARTITION BY ( RANGE_N(DATA_SOURCE_ID BETWEEN 'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z' AND 'ZZ', NO RANGE ,UNKNOWN) ,CASE_N(END_DT IS NULL , NO CASE , UNKNOWN))
;

输出：

CREATE
     TABLE
          SC.TAB (
               ACCOUNT_NUM VARCHAR( 255 ) /* CHARACTER SET LATIN*/       /* NOT CASESPECIFIC*/               NOT NULL
               ,ACCOUNT_MODIFIER_NUM CHAR( 18 ) /* CHARACTER SET LATIN*/               /* NOT CASESPECIFIC*/               NOT NULL
               ,DATA_SOURCE_ID CHAR( 10 ) /* CHARACTER SET LATIN*/               /* NOT CASESPECIFIC*/
               ,END_DT DATE
               ,UPD_TXF_BATCHTD INTEGER /* COMPRESS */
          ) DISTRIBUTE BY HASH (
               ACCOUNT_NUM
               ,ACCOUNT_MODIFIER_NUM
          )/* PARTITION BY (
               RANGE_N (
                    DATA_SOURCE_ID BETWEEN 'A'
                    ,'B'
                    ,'C'
                    ,'D'
                    ,'E'
                    ,'F'
                    ,'G'
                    ,'H'
                    ,'I'
                    ,'J'
                    ,'K'
                    ,'L'
                    ,'M'
                    ,'N'
                    ,'O'
                    ,'P'
                    ,'Q'
                    ,'R'
                    ,'S'
                    ,'T'
                    ,'U'
                    ,'V'
                    ,'W'
                    ,'X'
                    ,'Y'
                    ,'Z' AND 'ZZ'
                    ,NO RANGE
                    ,UNKNOWN
               )
               ,*/
/* CASE_N(END_DT IS NULL , NO CASE , UNKNOWN))  */
               ;

RANGE_N with different partition INTERVAL

输入：

CREATE MULTISET TABLE tab1
     ( TICD                  VARCHAR(10)
     , TCIT                   VARCHAR(10)
     , TCCM                  VARCHAR(50)
     , DW_Stat_Dt         DATE
    )
PRIMARY INDEX ( TICD )
PARTITION BY RANGE_N 
  ( DW_Stat_Dt BETWEEN DATE '0001-01-01' AND DATE '0001-01-04' EACH INTERVAL '1' DAY,
     DATE '0001-01-05' AND DATE '1899-12-31',
     DATE '1900-01-01' AND DATE '1900-01-01',
     DATE '1900-01-02' AND DATE '1999-12-31',
     DATE '2000-01-01' AND DATE '2009-12-31' EACH INTERVAL '1' YEAR,
     DATE '2010-01-01' AND DATE '2021-12-31' EACH INTERVAL '1' DAY,
     DATE '9999-12-31' AND DATE '9999-12-31', 
     NO RANGE );

输出：

CREATE TABLE tab1 
     ( TICD                  VARCHAR( 10 )
     , TCIT                   VARCHAR( 10 )
     , TCCM                  VARCHAR( 50 )
     , DW_Stat_Dt         DATE
     ) 
DISTRIBUTE BY HASH (TICD) 
PARTITION BY RANGE (DW_Stat_Dt)
  ( PARTITION tab1_0 START (DATE '0001-01-01') END (DATE '0001-01-04') EVERY (INTERVAL '1' DAY),
     PARTITION tab1_1 START (DATE '0001-01-04') END (DATE '1899-12-31'),
     PARTITION tab1_2 START (DATE '1899-12-31') END (DATE '1900-01-01'),
     PARTITION tab1_ 3 START (DATE '1900-01-01') END (DATE '1999-12-31'),
     PARTITION tab1_4 START (DATE '1999-12-31') END (DATE '2009-12-31') EVERY (INTERVAL '1' YEAR) ,
     PARTITION tab1_5 START (DATE '2009-12-31') END (DATE '2021-12-31') EVERY (INTERVAL '1' DAY) ,
     PARTITION tab1_6 START (DATE '2021-12-31') END (DATE '9999-12-31') 
  );

RANGE_N with * for start-date

输入：

CREATE MULTISET TABLE Orders5 (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE, 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE * AND DATE '2016-12-31' EACH INTERVAL '1' YEAR,
     DATE '2017-01-01'  EACH INTERVAL '1' MONTH,
     DATE '2020-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY
);

输出：

CREATE TABLE Orders5 (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE, 
   OrderTotal INTEGER 
) 
DISTRIBUTE BY HASH (OrderNo) 
PARTITION BY RANGE (OrderDate)
   ( PARTITION Orders5_0 START (DATE '0001-01-01') END (DATE '2016-12-31') EVERY (INTERVAL '1' YEAR),
PARTITION Orders5_1 START (DATE '2016-12-31') END (DATE '2020-01-01') EVERY (INTERVAL '1' MONTH),
PARTITION Orders5_2 START (DATE '2020-01-01') END (DATE '2020-12-31') EVERY (INTERVAL '1' DAY)
);

RANGE_N with * for end-date

输入：

CREATE SET TABLE Orders4 (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE, 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' YEAR,
     DATE '2017-01-01' EACH INTERVAL '1' MONTH,
     DATE '2019-01-01' AND * 
);

输出：

CREATE TABLE Orders4 (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE, 
   OrderTotal INTEGER 
) 
DISTRIBUTE BY HASH (OrderNo) 
PARTITION BY RANGE (OrderDate)
   ( PARTITION Orders4_0 START (DATE '2010-01-01') END (DATE '2016-12-31') EVERY (INTERVAL '1' YEAR),
PARTITION Orders4_1 START (DATE '2016-12-31') END (DATE '2020-01-01') EVERY (INTERVAL '1' MONTH) ,
PARTITION Orders4_2 START (DATE '2020-01-01') END (MAXVALUE)
);

RANGE_N with comma separated values

输入：

CREATE TABLE orders10
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE NOT NULL
    ,totalorders INTEGER NOT NULL)
    PRIMARY INDEX (storeid, productid) 
     PARTITION BY ( RANGE_N(totalorders BETWEEN *, 100, 1000 AND *) );

输出：

CREATE TABLE orders10
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE NOT NULL
    ,totalorders INTEGER NOT NULL)
DISTRIBUTE BY HASH (storeid, productid) 
PARTITION BY RANGE (totalorders)
   ( PARTITION Orders10_0 END (100),
     PARTITION Orders10_1 END (1000),
     PARTITION Orders10_2 END (MAXVALUE)
   );

父主题： 表迁移

上一篇：COLUMN STORE

下一篇：ANALYZE

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消