更新时间：2023-03-17 GMT+08:00
表迁移
输入文件中包含表的专用关键词MULTISET VOLATILE，但GaussDB(DWS)不支持该关键词。因此，DSC在迁移过程中用关键词LOCAL TEMPORARY/UNLOGGED替换该关键词。请通过session_mode参数为CREATE TABLE语句设置默认表类型（SET/MULTISET）。
详细内容查看以下节点：
示例：
输入：CREATE TABLE
1 2 3 |
CT tab1 ( id INT ); |
输出
1 2 3 4 5 6 |
CREATE TABLE tab1 ( id INTEGER ) ; |
执行CREATE tab2 AS tab1时，从tab1中复制表结构到tab2。如果CREATE TABLE语句包含WITH DATA选项，则将tab1的数据也复制到tab2中。如果还包含CREATE AS，则将源表中的约束行为保留到新表。
- 如果session_mode设为Teradata，则必须删除目标表中的重复记录。该操作通过在迁移脚本中添加MINUS运算符实现。
- 如果session_mode设为ANSI，则允许目标表中存在重复记录。
如果源表具有PRIMARY KEY（主键）或UNIQUE CONSTRAINT（唯一约束），则该表不包含任何重复记录。在这种情况下，不需要添加MINUS操作符删除重复的记录。
示例：
输入：CREATE TABLE AS WITH DATA（session_mode=Teradata）
1 2 |
CREATE TABLE tab2 AS tab1 WITH DATA; |
输出
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN CREATE TABLE tab2 ( LIKE tab1 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS ); INSERT INTO tab2 SELECT * FROM tab1 MINUS SELECT * FROM tab2; END ; / |
输入：CREATE TABLE AS WITH DATA AND STATISTICS
1 2 3 4 |
CREATE SET VOLATILE TABLE tab2025 AS ( SELECT * from tab2023 ) WITH DATA AND STATISTICS PRIMARY INDEX (LOGTYPE, OPERSEQ); |
输出
1 2 3 4 5 |
CREATE LOCAL TEMPORARY TABLE tab2025 DISTRIBUTE BY HASH ( LOGTYPE, OPERSEQ ) AS ( SELECT * FROM tab2023 ); ANALYZE tab2025; |
可以使用tdMigrateCharsetCase参数来配置是否迁移CHARACTER SET和CASESPECIFIC。如果该参数设为false，则工具将跳过该查询的迁移并记录消息。
输入：tdMigrateCharsetCase=True
1 2 3 4 5 6 7 8 |
CREATE MULTISET VOLATILE TABLE TAB1 ( col1 INTEGER NOT NULL ,col2 INTEGER NOT NULL ,col3 VARCHAR(100) NOT NULL CHARACTER SET UNICODE CASESPECIFIC ) PRIMARY INDEX (col1,col2) ON COMMIT PRESERVE ROWS ; |
输出
1 2 3 4 5 6 7 8 9 10 |
CREATE LOCAL TEMPORARY TABLE TMP_RATING_SYS_PARA ( col1 INTEGER NOT NULL ,col2 INTEGER NOT NULL ,col3 VARCHAR(100) NOT NULL /* CHARACTER SET UNICODE CASESPECIFIC */) ) ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH (col1,col2) ; |
输入：迁移支持的字符数据类型
在Teradata中，以下字符集支持以字符个数来衡量字符串数据类型的长度：
- LATIN
- UNICODE
- GRAPHIC
不过，KANJISJIS字符集支持以字节个数来衡量字符串数据类型的长度。
以COLUMN_NAME VARCHAR(100) CHARACTER SET UNICODE CASESPECIFIC COLUMN_NAME VARCHAR(100) CHARACTER SET LATIN CASESPECIFIC为例，字符串最大支持100个字符（而不是字节）。
在GaussDB(DWS)中，字符串数据类型长度通过字节（而不是字符）来衡量。 VARCHAR(100)和VARCHAR2(100)最多支持100个字节（而不是字符）。 但是，NVARCHAR2(100)最大可支持100个字符。
因此，如果Teradata使用LATIN、UNICODE或GRAPHIC字符集，VARCHAR应迁移为NVARCHAR。
1 2 3 4 5 |
CREATE TABLE tab1 ( col1 VARCHAR(10), COL2 CHAR(1) ); |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
a)when default_charset = UNICODE/GRAPHIC CREATE TABLE tab1 ( col1 NVARCHAR2 (10) ,COL2 NVARCHAR2 (1) ) ; b)when default_charset = LATIN CREATE TABLE tab1 ( col1 VARCHAR2 (10) ,COL2 VARCHAR2 (1) ) ; |
输入
1 2 3 4 5 |
CREATE TABLE tab1 ( col1 VARCHAR(10) CHARACTER SET UNICODE, COL2 CHAR(1) ); |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
a) when default_charset = UNICODE/GRAPHIC CREATE TABLE tab1 ( col1 NVARCHAR2 (10) /* CHARACTER SET UNICODE*/ ,COL2 NVARCHAR2( 1 ) ) ; b) when default_charset = LATIN CREATE TABLE tab1 ( col1 NVARCHAR2 (10) /* CHARACTER SET UNICODE*/ ,COL2 CHAR(1) ) |
VOLATILE
输入文件中包含表的专用关键词VOLATILE，但GaussDB(DWS)不支持该关键词。因此，DSC在迁移过程中用关键词LOCAL TEMPORARY替换该关键词。根据配置输入，Volatile表在迁移中标记为本地临时表或无日志表。
输入：CREATE VOLATILE TABLE
1
|
CREATE VOLATILE TABLE T1 (c1 int ,c2 int); |
输出
1 2 3 4 5 6 7 |
CREATE LOCAL TEMPORARY TABLE T1 ( c1 INTEGER ,c2 INTEGER ) ; |
输入：CREATE VOLATILE TABLE AS WITH DATA（session_mode=Teradata）
如果源表具有PRIMARY KEY（主键）或UNIQUE CONSTRAINT（唯一约束），则该表不包含任何重复记录。在这种情况下，不需要添加MINUS操作符删除重复的记录。
1 2 3 4 5 6 7 8 9 10 |
CREATE VOLATILE TABLE tabV1 ( C1 INTEGER DEFAULT 99 ,C2 INTEGER ,C3 INTEGER ,C4 NUMERIC (20,0) DEFAULT NULL (BIGINT) ,CONSTRAINT XX1 PRIMARY KEY ( C1, C2 ) ) PRIMARY INDEX (C1, C3 ); CREATE TABLE tabV2 AS tabV1 WITH DATA PRIMARY INDEX (C1) ON COMMIT PRESERVE ROWS; |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE LOCAL TEMPORARY TABLE tabV1 ( C1 INTEGER DEFAULT 99 ,C2 INTEGER ,C3 INTEGER ,C4 NUMERIC (20,0) DEFAULT CAST( NULL AS BIGINT ) ,CONSTRAINT XX1 PRIMARY KEY ( C1, C2 ) ) DISTRIBUTE BY HASH (C1); BEGIN CREATE TABLE tabV2 ( LIKE tabV1 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS EXCLUDING DISTRIBUTION ) DISTRIBUTE BY HASH (C1); INSERT INTO tabV2 SELECT * FROM tabV1; END ; / |
输入：SET TABLE
1 2 3 4 5 |
CREATE SET VOLATILE TABLE tab1 … ; INSERT INTO tab1 SELECT expr1, expr2, … FROM tab1, … WHERE ….; |
输出
1 2 3 4 5 6 7 |
CREATE LOCAL TEMPORARY TABLE tab1 … ; INSERT INTO tab1 SELECT expr1, expr2, … FROM tab1, … WHERE …. MINUS SELECT * FROM tab1 ; |
MULTISET表支持与VOLATILE一起使用。
输入：CREATE MULTISET TABLE
1
|
CREATE VOLATILE MULTISET TABLE T1 (c1 int ,c2 int); |
输出
1 2 3 4 5 6 7 |
CREATE LOCAL TEMPORARY TABLE T1 ( c1 INTEGER ,c2 INTEGER ) ; |
如果TITLE文本拆分为多行，则在迁移后脚本中，换行符（ENTER）替换为空格。
输入：CREATE TABLE，使用TITLE
1 2 3 |
CREATE TABLE tab1 ( c1 NUMBER(2) TITLE 'column_a' ); |
输出
1 2 3 |
CREATE TABLE tab1 ( c1 NUMBER(2) /* TITLE 'column_a' */ ); |
输入：TABLE，使用多行TITLE
1 2 3 4 |
CREATE TABLE tab1 ( c1 NUMBER(2) TITLE 'This is a very long title' ); |
输出
1 2 3 |
CREATE TABLE tab1 ( c1 NUMBER(2) /* TITLE 'This is a very long title' */ ); |
输入：TABLE，使用列TITLE
DSC将列TITLE迁移为新的外部查询。
1 2 3 |
SELECT customer_id (TITLE 'cust_id') FROM Customer_T WHERE cust_id > 10; |
输出
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT customer_id AS "cust_id" FROM ( SELECT customer_id FROM Customer_T WHERE cust_id > 10 ) ; |
输入：TABLE，使用列TITLE和QUALIFY
1 2 3 4 5 |
SELECT ord_id (TITLE 'Order_Id'), order_date, customer_id FROM order_t WHERE Order_Id > 100 QUALIFY ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) <= 5; |
输出
SELECT
"mig_tmp_alias1" AS "Order_Id"
FROM
(
SELECT
ord_id AS "mig_tmp_alias1"
,ROW_NUMBER( ) OVER( PARTITION BY customer_id ORDER BY order_date DESC ) AS ROW_NUM1
FROM
order_t
WHERE
Order_Id > 100
) Q1
WHERE
Q1.ROW_NUM1 <= 5
;
- TITLE和ALIAS
如果使用TITLE并指定ALIAS，则工具将按如下方式进行迁移：
- TITLE with AS：迁移为AS alias。
- TITLE with NAMED：迁移为NAMED alias。
- TITLE with NAMED and AS：迁移为AS alias。
输入：TABLE TITLE，使用NAMED和AS
1 2 3 4
SELECT Acct_ID (TITLE 'Acc Code') (NAMED XYZ) AS "Account Code" ,Acct_Name (TITLE 'Acc Name') FROM GT_JCB_01030_Acct_PBU where "Account Code" > 500 group by "Account Code" ,Acct_Name ;
输出
SELECT Acct_ID AS "Account Code" ,Acct_Name AS "Acc Name" FROM GT_JCB_01030_Acct_PBU WHERE Acct_ID > 500 GROUP BY Acct_ID ,Acct_Name ;
目前，DSC支持迁移初始CREATE/ALTER语句中的TITLE命令，但不支持后续对TITLE指定列的引用。例如，在下面的CREATE TABLE语句中，带有TITLE Employee ID的列eid在迁移后被注释掉，但是SELECT语句中对eid的引用将保持原样。
输入
1 2
CREATE TABLE tab1 ( eid INT TITLE 'Employee ID'); SELECT eid FROM tab1;
输出
1 2
CREATE TABLE tab1 (eid INT /*TITLE 'Employee ID'*/); SELECT eid from tab1;
- TITLE和CREATE VIEW
REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} AS LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS SELECT AUM_DATE (TITLE ' ') ,CLNTCODE (TITLE ' ') ,ACCTYPE (TITLE ' ') ,CCY (TITLE ' ') ,BAL_AMT (TITLE ' ') ,MON_BAL_AMT (TITLE ' ') ,HK_CLNTCODE (TITLE ' ') ,MNT_DATE (TITLE ' ') FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC}; it should be migrated as below: CREATE OR REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} AS /*LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS */ SELECT AUM_DATE /* (TITLE ' ') */ ,CLNTCODE /* (TITLE ' ') */ ,ACCTYPE /* (TITLE ' ') */ ,CCY /* (TITLE ' ') */ ,BAL_AMT /* (TITLE ' ') */ ,MON_BAL_AMT /* (TITLE ' ') */ ,HK_CLNTCODE /* (TITLE ' ') */ ,MNT_DATE /* (TITLE ' ') */ FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC};输出
CREATE OR REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} AS /*LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR ACCESS */ SELECT AUM_DATE /* (TITLE ' ') */ ,CLNTCODE /* (TITLE ' ') */ ,ACCTYPE /* (TITLE ' ') */ ,CCY /* (TITLE ' ') */ ,BAL_AMT /* (TITLE ' ') */ ,MON_BAL_AMT /* (TITLE ' ') */ ,HK_CLNTCODE /* (TITLE ' ') */ ,MNT_DATE /* (TITLE ' ') */ FROM ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC};
该工具不会添加DISTRIBUTE BY HASH用于创建具有主键（PRIMARY KEY）和非唯一主索引的表。
输入：CREATE TABLE，使用INDEX
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE SET TABLE DP_TEDW.B0381_ACCOUNT_OBTAINED_MAP, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( Ranked_Id INTEGER NOT NULL , Source_System_Code SMALLINT NOT NULL , Operational_Acc_Obtained_Id VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC FORMAT 'X(50)' , Mapped_Id INTEGER NOT NULL ) PRIMARY INDEX B0381_ACCOUNT_OBTAINED_idx_PR ( Ranked_Id ) UNIQUE INDEX B0381_ACCT_OBT_MAP__idx_SCD ( Source_System_Code ) INDEX B0381_ACCT_OBT_MAP__idx_OPID ( Operational_Acc_Obtained_Id ); |
输出
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE DP_TEDW.B0381_ACCOUNT_OBTAINED_MAP ( Ranked_Id INTEGER NOT NULL , Source_System_Code SMALLINT NOT NULL , Operational_Acc_Obtained_Id VARCHAR( 100 ) , Mapped_Id INTEGER NOT NULL ) DISTRIBUTE BY HASH ( Ranked_Id ); CREATE INDEX B0381_ACCT_OBT_MAP__idx_SCD ON DP_TEDW.B0381_ACCOUNT_OBTAINED_MAP ( Source_System_Code ); CREATE INDEX B0381_ACCT_OBT_MAP__idx_OPID ON DP_TEDW.B0381_ACCOUNT_OBTAINED_MAP ( Operational_Acc_Obtained_Id ); |
由于索引列列表（organic_name）不是DISTRIBUTE BY列列表（serial_no、organic_name）的超集，因此索引中删除了UNIQUE。
输入：CREATE TABLE，使用主键和非唯一主索引（未添加DISTRIBUTE BY HASH）
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE employee ( EMP_NO INTEGER , DEPT_NO INTEGER , FIRST_NAME VARCHAR(20) , LAST_NAME CHAR(20) , SALARY DECIMAL(10,2) , ADDRESS VARCHAR(100) , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO ) ) PRIMARY INDEX ( DEPT_NO ) ; |
输出
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE employee ( EMP_NO INTEGER , DEPT_NO INTEGER , FIRST_NAME VARCHAR(20) , LAST_NAME CHAR(20) , SALARY DECIMAL(10,2) , ADDRESS VARCHAR(100) , CONSTRAINT pk_emp PRIMARY KEY ( EMP_NO ) ) ; |
- REFERENCES约束/FOREIGN KEY：目前无法通过工具迁移。
- PRIMARY KEY约束：可通过工具迁移。
- UNIQUE约束：可通过工具迁移。
输入：CREATE TABLE，使用CONSTRAINT
1 2 3 4 5 6 7 8 9 10 |
CREATE SET TABLE DP_SEDW.T_170UT_HOLDER_ACCT, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( BUSINESSDATE VARCHAR(10) , SOURCESYSTEM VARCHAR(5) , UPLOADCODE VARCHAR(1) , HOLDER_NO VARCHAR(7) NOT NULL , POSTAL_ADD_4 VARCHAR(40) , EPF_IND CHAR(1) , CONSTRAINT uq_t_170ut_hldr UNIQUE ( SOURCESYSTEM, UPLOADCODE, HOLDER_NO ) ) PRIMARY INDEX ( HOLDER_NO, SOURCESYSTEM ) ; |
输出
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE DP_SEDW.T_170UT_HOLDER_ACCT ( BUSINESSDATE VARCHAR( 10 ) , SOURCESYSTEM VARCHAR( 5 ) , UPLOADCODE VARCHAR( 1 ) , HOLDER_NO VARCHAR( 7 ) NOT NULL , POSTAL_ADD_4 VARCHAR( 40 ) , EPF_IND CHAR( 1 ) , CONSTRAINT uq_t_170ut_hldr UNIQUE ( SOURCESYSTEM, UPLOADCODE, HOLDER_NO ) ) DISTRIBUTE BY HASH ( HOLDER_NO, SOURCESYSTEM ); |
输入
建表后，可使用ALTER语句为该表字段添加列级约束。
1 2 3 4 |
CREATE TABLE GCC_PLAN.T1033 ( ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL, UDF_FIELD_VALUE_ID NUMBER NOT NULL) ; ALTER TABLE GCC_PLAN.T1033 ADD CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ; |
输出
1 2 3 4 |
CREATE TABLE GCC_PLAN.T1033 ( ROLLOUT_PLAN_LINE_ID NUMBER NOT NULL, UDF_FIELD_VALUE_ID NUMBER NOT NULL, CONSTRAINT UDF_FIELD_VALUE_ID_PK UNIQUE (UDF_FIELD_VALUE_ID) ; |
建表脚本中，需在所有列声明之后添加约束创建语法。
COLUMN STORE
表的存储方式可使用CREATE TABLE语句中的WITH（ORIENTATION=COLUMN）从ROW-STORE转换为COLUMN存储。可使用rowstoreToColumnstore参数启用/禁用此功能。
输入：CREATE TABLE，修改存储模式为 COLUMN STORE
1 2 3 4 5 6 7 |
CREATE MULTISET VOLATILE TABLE tab1 ( c1 VARCHAR(30) CHARACTER SET UNICODE , c2 DATE , ... ) PRIMARY INDEX (c1, c2) ON COMMIT PRESERVE ROWS; |
输出
1 2 3 4 5 6 7 |
CREATE LOCAL TEMPORARY TABLE tab1 ( c1 VARCHAR(30) , c2 DATE , ... ) WITH (ORIENTATION = COLUMN) ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH (c1, c2); |
- 范围分区/子分区
- 列表分区/子分区
- 哈希分区/子分区
场景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分区，未指定嵌套分区。
Input
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 )
) */
;
1 2 |
CREATE TABLE EMP27 AS emp21 WITH DATA PRIMARY INDEX (EMPNO) ON COMMIT PRESERVE ROWS; |
输出
Begin CREATE TABLE EMP27 ( LIKE emp21 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS EXCLUDING DISTRIBUTION ) DISTRIBUTE BY HASH ( EMPNO ) ; INSERT INTO EMP27 select * from emp21 ; end ; / ANALYZE Emp27 (EmpNo);
|
输入 |
输出 |
|---|---|
|
Numeric |
Numeric |
|
BIGINT |
BIGINT |
|
BYTEINT |
SMALLINT |
|
DECIMAL [(n[,m])] |
DECIMAL [(n[,m])] |
|
DOUBLE PRECISION |
DOUBLE PRECISION |
|
FLOAT |
DOUBLE PRECISION |
|
INT / INTEGER |
INTEGER |
|
NUMBER / NUMERIC |
NUMERIC |
|
NUMBER(n[,m]) |
NUMERIC (n[,m]) |
|
REAL |
REAL |
|
SMALLINT |
SMALLINT |
|
Character |
Character |
|
CHAR[(n)] / CHARACTER [(n)] |
CHAR(n) |
|
CLOB |
CLOB |
|
LONG VARCHAR |
TEXT |
|
VARCHAR(n) / CHAR VARYING(n) / CHARACTER VARYING(n) |
VARCHAR(n) |
|
DateTime |
DateTime |
|
DATE |
DATE |
|
TIME [(n)] |
TIME [(n)] |
|
TIME [(n)] WITH TIME ZONE |
TIME [(n)] WITH TIME ZONE |
|
TIMESTAMP [(n)] |
TIMESTAMP [(n)] |
|
TIMESTAMP [(n)] WITH TIME ZONE |
TIMESTAMP [(n)] WITH TIME ZONE |
|
Period |
Period |
|
PERIOD(DATE) |
daterange |
|
PERIOD(TIME [(n)]) |
tsrange [(n)] |
|
PERIOD(TIME WITH TIME ZONE) |
tstzrange |
|
PERIOD(TIMESTAMP [(n)]) |
tsrange [(n)] |
|
PERIOD(TIMESTAMP WITH TIME ZONE) |
tstzrange |
|
Binary |
Binary |
|
BLOB[(n)] |
blob |
|
BYTE[(n)] |
bytea |
|
VARBYTE[(n)] |
bytea |
示例：BYTEINT
输入
select cast(col as byteint) from tab;
输出
SELECT CAST( col AS SMALLINT ) FROM tab ;
session_mode设为Teradata时支持此功能。
- INSERT-INTO-SELECT中的SELECT语句不得包含以下内容：
- SET操作符
- MERGE、使用PERCENT的TOP、使用TIES的TOP PERCENT
输入：TABLE，且INSERT语句中未指定CREATE中的全部列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
CREATE VOLATILE TABLE Convert_Data3 ,NO LOG ( zoneno CHAR( 6 ) ,brno CHAR( 6 ) ,currtype CHAR( 4 ) ,Commuteno CHAR( 4 ) ,Subcode CHAR( 12 ) ,accdate DATE format 'YYYY-MM-DD' NOT NULL ,acctime INTEGER ,quoteno CHAR( 1 ) ,quotedate DATE FORMAT 'YYYY-MM-DD' ,lddrbaL DECIMAL( 18 ,0 ) DEFAULT 0 ,ldcrbal DECIMAL( 18 ,0 ) ,tddramt DECIMAL( 18 ,0 ) DEFAULT 25 ,tdcramt DECIMAL( 18 ,0 ) ,tddrbal DECIMAL( 18 ,2 ) ,tdcrbal DECIMAL( 18 ,2 ) ) PRIMARY INDEX ( BRNO ,CURRTYPE ,SUBCODE ) ON COMMIT PRESERVE ROWS ; INSERT INTO Convert_Data3 ( zoneno ,brno ,currtype ,commuteno ,subcode ,accdate ,acctime ,quoteno ,quotedate ,tddrbal ,tdcrbal ) SELECT A.zoneno ,A.brno ,'014' currtype ,'2' commuteno ,A.subcode ,A.Accdate ,A.Acctime ,'2' quoteno ,B.workdate quoteDate ,CAST( ( CAST( SUM ( CAST( A.tddrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DEC ( 18 ,2 ) ) AS tddrbal ,CAST( ( CAST( SUM ( CAST( A.tdcrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DEC ( 18 ,2 ) ) AS tdcrbal FROM table2 A ; |
输出
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
CREATE LOCAL TEMPORARY TABLE Convert_Data3 ( zoneno CHAR( 6 ) ,brno CHAR( 6 ) ,currtype CHAR( 4 ) ,Commuteno CHAR( 4 ) ,Subcode CHAR( 12 ) ,accdate DATE NOT NULL ,acctime INTEGER ,quoteno CHAR( 1 ) ,quotedate DATE ,lddrbaL DECIMAL( 18 ,0 ) DEFAULT 0 ,ldcrbal DECIMAL( 18 ,0 ) ,tddramt DECIMAL( 18 ,0 ) DEFAULT 25 ,tdcramt DECIMAL( 18 ,0 ) ,tddrbal DECIMAL( 18 ,2 ) ,tdcrbal DECIMAL( 18 ,2 ) ) ON COMMIT PRESERVE ROWS DISTRIBUTE BY HASH ( BRNO ,CURRTYPE ,SUBCODE ) ; INSERT INTO Convert_Data3 ( lddrbaL ,ldcrbal ,tddramt ,tdcramt ,zoneno ,brno ,currtype ,commuteno ,subcode ,accdate ,acctime ,quoteno ,quotedate ,tddrbal ,tdcrbal ) SELECT 0 ,NULL ,25 ,NULL ,A.zoneno ,A.brno ,'014' currtype ,'2' commuteno ,A.subcode ,A.Accdate ,A.Acctime ,'2' quoteno ,B.workdate quoteDate ,CAST( ( CAST( SUM ( CAST( A.tddrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DECIMAL( 18 ,2 ) ) AS tddrbal ,CAST( ( CAST( SUM ( CAST( A.tdcrbal AS FLOAT ) * CAST( B.USCVRATE AS FLOAT ) ) AS FLOAT ) ) AS DECIMAL( 18 ,2 ) ) AS tdcrbal FROM table2 A MINUS SELECT lddrbaL ,ldcrbal ,tddramt ,tdcramt ,zoneno ,brno ,currtype ,commuteno ,subcode ,accdate ,acctime ,quoteno ,quotedate ,tddrbal ,tdcrbal FROM CONVERT_DATA3 ; |
父主题： 模式对象
