更新时间: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) );
父主题: 表迁移