文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Teradata语法迁移> 表迁移

表迁移

分享
更新时间:2020/08/25 GMT+08:00

输入文件中包含表的专用关键词MULTISET VOLATILE,但GaussDB T、GaussDB A 和GaussDB(DWS)不支持该关键词。因此,DSC在迁移过程中用关键词LOCAL TEMPORARY/UNLOGGED替换该关键词。请通过session_mode参数为CREATE TABLE语句设置默认表类型(SET/MULTISET)。

详细内容查看以下节点:

CREATE TALBE

CHARACTER SET和CASESPECIFIC

VOLATILE

SET

MULTISET

TITLE

索引

约束

COLUMN STORE

PARTITION

ANALYZE

数据类型

支持指定部分列

CREATE TALBE

Teradata的CREATE TABLE (缩写关键字为CT)语句用于创建表。

示例:

输入: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;

CHARACTER SET和CASESPECIFIC

CHARACTER SET用于指定字符列的服务器字符集,CASESPECIFIC指定字符数据比较及排序时的大小写情况。

可以使用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 A中,字符串数据类型长度通过字节(而不是字符)来衡量。 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 T、GaussDB A 和 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

SET是Teradata的独有功能。它不允许重复的记录。它使用MINUS集合操作符来实现。DSC支持MULTISET和SET表。SET表支持与VOLATILE一起使用。

输入: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

MULTISET是一个普通表,所有数据库都支持这个表。迁移工具同时支持MULTISET和SET表。

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

Teradata Permanent、Global Temporary和Volatile表支持关键字TITLE。在迁移过程中,TITLE文本将被注释掉。

如果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
;
  1. 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;
    
  2. 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};

索引

CREATE TABLE语句支持创建索引。DSC支持带有主索引(PRIMARY INDEX)和唯一索引(UNIQUE INDEX)的TABLE语句。

该工具不会添加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 )       
  )
;

约束

表中的约束应用于多列。DSC支持以下约束:

  • CHECK约束:由GaussDB T、GaussDB A 和 GaussDB(DWS)支持。
  • 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);

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分区,未指定嵌套分区。

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 )
                     ) */
;

ANALYZE

输入:CREATE TABLE,使用INDEX

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);

数据类型

DSC支持以下数据类型映射:

输入

输出

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 ;

支持指定部分列

DSC支持在执行INSERT期间指定部分列(非全部列)。当输入的INSERT语句不包含输入的CREATE语句中提到的所有列时会出现这种情况。在迁移时,会向这些列添加指定的默认值。

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
;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!非常感谢您的反馈,我们会继续努力做到更好!
反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

智能客服提问云社区提问