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

PARTITION

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

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

场景1:假设参数tdMigrateCASE_NtdMigrateRANGE_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_NtdMigrateRANGE_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_NtdMigrateRANGE_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_NtdMigrateRANGE_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_NtdMigrateRANGE_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)
   );