华为云用户手册

.LABEL 和 .GOTO Teradata命令.LABEL用于创建标签，通常与.GOTO配对使用。.GOTO会跳过所有中间的BTEQ命令和SQL语句，指导到达指定标签位置，执行相应的恢复处理。 gsql元命令\goto LABEL ... \label LABEL可以等价实现无条件跳转。 输入 输出 .IF CHECK_PK='' THEN .GOTO NOCHECK${CHECK_PK};.LABEL NOCHECK.QUIT 0 \if ${CHECK_PK} == ''\goto NOCHECK\endif${CHECK_PK}\label NOCHECK\q 0

CHARSET CHARSET指定表的默认字符集。 GaussDB (DWS)不支持该属性修改表定义信息，DSC迁移时会将该关键字删除。 输入示例 123456 CREATE TABLE `public`.`runoob_tbl_test`( `runoob_id` VARCHAR(30), `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR(30))DEFAULT CHARSET=utf8; 输出示例 1 2 3 4 5 6 7 8 910 CREATE TABLE "public"."runoob_tbl_test"( "runoob_id" VARCHAR(120), "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR(120)) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id"); 父主题： 表（可选参数、操作）

COLLATE 在MySQL中，COLLATE表示默认的数据库排序规则。GaussDB(DWS)不支持该属性修改表定义信息，DSC迁移时会将该关键字删除。 输入示例 12345678 CREATE TABLE `public`.`runoob_tbl_test`( `runoob_id` VARCHAR(30), `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR(30)) COLLATE=utf8_general_ci;ALTER TABLE `public`.`runoob_tbl_test` COLLATE=utf8mb4_bin; 输出示例 1 2 3 4 5 6 7 8 910 CREATE TABLE "public"."runoob_tbl_test"( "runoob_id" VARCHAR(120), "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR(120)) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id"); 父主题： 表（可选参数、操作）

系统视图 DSC将系统视图dbc.columnsV和dbc.IndicesV进行迁移，输出如下结果。 输入： 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930 SELECT A.ColumnName AS V_COLS ,A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(columnlength (INT))|| ') CHARACTER SET LATIN'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE '' END || ' CASESPECIFIC' WHEN columnType='DA' THEN 'DATE' WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER' WHEN columnType='I1' THEN 'BYTEINT' WHEN columnType='I2' THEN 'SMALLINT' WHEN columnType='I8' THEN 'BIGINT' WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown' END||CASE WHEN Nullable='Y' THEN '' ELSE ' NOT NULL' END||'0A'XC AS V_ColT - ,B.ColumnName AS V_PICol FROM dbc.columnsV A LEFT JOIN dbc.IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = '${V_TDDLDB}' AND B.tablename='${TARGET_TABLE}'WHERE A.databasename='${V_TDDLDB}' AND A.tablename = '${TARGET_TABLE}' AND A.columnname NOT IN ( 'ETL_JOB_NAME' ,'ETL_TX_DATE' ,'ETL_PROC_DATE' ) ORDER BY A.columnid; 输出： 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031 DECLARE lv_mig_V_COLS TEXT; lv_mig_V_ColT TEXT; lv_mig_V_PICol TEXT;BEGINSELECT STRING_AGG(A.ColumnName, ',') , STRING_AGG(A.columnname || ' ' ||CASE WHEN columnType in ('CF','CV') THEN CASE WHEN columnType='CV' THEN 'VAR' ELSE '' END||'CHAR('||TRIM(mig_td_ext.mig_fn_castasint(columnlength))|| ') /*CHARACTER SET LATIN*/'|| CASE WHEN UpperCaseFlag='N' THEN ' NOT' ELSE '' END || ' /*CASESPECIFIC*/' WHEN columnType='DA' THEN 'DATE' WHEN columnType='TS' THEN 'TIMESTAMP(' || TRIM(DecimalFractionalDigits)||')' WHEN columnType='AT' THEN 'TIME('|| TRIM(DecimalFractionalDigits)||')' WHEN columnType='I' THEN 'INTEGER' WHEN columnType='I1' THEN 'BYTEINT' WHEN columnType='I2' THEN 'SMALLINT' WHEN columnType='I8' THEN 'BIGINT' WHEN columnType='D' THEN 'DECIMAL('||TRIM(DecimalTotalDigits)||','||TRIM(DecimalFractionalDigits)||')' ELSE 'Unknown' END||CASE WHEN Nullable='Y' THEN '' ELSE ' NOT NULL' END||E'\x0A', ',') , STRING_AGG(B.ColumnName, ',') INTO lv_mig_V_COLS, lv_mig_V_ColT, lv_mig_V_PIColFROM mig_td_ext.vw_td_dbc_columnsV A LEFT JOIN mig_td_ext.vw_td_dbc_IndicesV B ON A.columnName = B.columnName AND B.IndexType IN ('Q','P') AND B.DatabaseName = 'public' AND B.tablename='emp2'WHERE A.databasename='public' AND A.tablename = 'emp2';-- ORDER BY A.columnid;END;/ 父主题： Teradata语法迁移

DIRECTORY DIRECTORY表示允许在数据目录和索引目录之外创建表空间。DIRECTORY包含DATA DIRECTORY和INDEX DIRECTORY。GaussDB(DWS)不支持该属性修改表定义信息，DSC迁移时会将该属性删除。 输入示例 1 2 3 4 5 6 7 8 91011 CREATE TABLE `public`.`runoob_tbl_test1` (`dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` DOUBLE(20,8), PRIMARY KEY(`dataType1`)) ENGINE=MYISAM DATA DIRECTORY = 'D:\\input' INDEX DIRECTORY= 'D:\\input';CREATE TABLE `public`.`runoob_tbl_test2` (`dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` DOUBLE(20,8), PRIMARY KEY(`dataType1`)) ENGINE=INNODB DATA DIRECTORY = 'D:\\input'; 输出示例 1 2 3 4 5 6 7 8 910111213141516171819 CREATE TABLE "public"."runoob_tbl_test1"( "datatype1" SERIAL NOT NULL, "datatype2" DOUBLE PRECISION, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");CREATE TABLE "public"."runoob_tbl_test2"( "datatype1" SERIAL NOT NULL, "datatype2" DOUBLE PRECISION, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); 父主题： 表（可选参数、操作）

LOCK GaussDB(DWS)不支持MySQL中的“ALTER TABLE tbName LOCK”语句，DSC工具迁移时会将其删除。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324 CREATE TABLE IF NOT EXISTS `runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` FLOAT(10), `dataType4` TEXT NOT NULL, `dataType5` YEAR NOT NULL DEFAULT '2018', `dataType6` DATETIME NOT NULL, `dataType7` CHAR NOT NULL DEFAULT '', `dataType8` VARCHAR(50), `dataType9` VARCHAR(50) NOT NULL DEFAULT '', `dataType10` TIME NOT NULL DEFAULT '10:20:59', PRIMARY KEY(`dataType1`))ENGINE=InnoDB DEFAULT CHARSET=utf8;## A.ALTER TABLE runoob_alter_test LOCK DEFAULT;## B.ALTER TABLE runoob_alter_test LOCK=DEFAULT;## C.ALTER TABLE runoob_alter_test LOCK EXCLUSIVE;## D.ALTER TABLE runoob_alter_test LOCK=EXCLUSIVE; 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "datatype2" REAL, "datatype4" TEXT NOT NULL, "datatype5" SMALLINT NOT NULL DEFAULT '2018', "datatype6" TIMESTAMP WITHOUT TIME ZONE NOT NULL, "datatype7" CHAR(4) NOT NULL DEFAULT '', "datatype8" VARCHAR(200), "datatype9" VARCHAR(200) NOT NULL DEFAULT '', "datatype10" TIME WITHOUT TIME ZONE NOT NULL DEFAULT '10:20:59', PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");-- A.-- B.-- C.-- D. 父主题： 表（可选参数、操作）

MAX_ROWS 在MySQL中，MAX_ROWS表示在表中存储的最大行数。DSC迁移过程时会将该属性删除。 输入示例 123456789 CREATE TABLE `public`.`runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` DOUBLE(20,8), `dataType3` TEXT NOT NULL, PRIMARY KEY(`dataType1`));ALTER TABLE runoob_alter_test MAX_ROWS 100000;ALTER TABLE runoob_alter_test MAX_ROWS=100000; 输出示例 1 2 3 4 5 6 7 8 910 CREATE TABLE "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "datatype2" DOUBLE PRECISION, "datatype3" TEXT NOT NULL, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); 父主题： 表（可选参数、操作）

STATS_PERSISTENT 在MySQL中，STATS_PERSISTENT指定是否为InnoDB表启动持久性统计信息，通过CREATE TABLE或ALTER TABLE语句启动持久性统计信息。DSC迁移时会将该属性删除。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718 CREATE TABLE `public`.`runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` DOUBLE(20,8), `dataType3` TEXT NOT NULL, PRIMARY KEY(`dataType1`)) ENGINE=InnoDB, STATS_PERSISTENT=0;## A.ALTER TABLE runoob_alter_test STATS_PERSISTENT DEFAULT;ALTER TABLE runoob_alter_test STATS_PERSISTENT=DEFAULT;## B.ALTER TABLE runoob_alter_test STATS_PERSISTENT 0;ALTER TABLE runoob_alter_test STATS_PERSISTENT=0;## C.ALTER TABLE runoob_alter_test STATS_PERSISTENT 1;ALTER TABLE runoob_alter_test STATS_PERSISTENT=1; 输出示例 1 2 3 4 5 6 7 8 910111213141516 CREATE TABLE "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "datatype2" DOUBLE PRECISION, "datatype3" TEXT NOT NULL, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");-- A.-- B.-- C. 父主题： 表（可选参数、操作）

Teradata 如果含有FORMAT参数的case语句未用半角括号“()”括起来，该语句不会处理。 例如： 1 case when column1='0' then column1='value' end (FORMAT 'YYYYMMDD')as alias1 在该示例中，case when column1='0' then column1='value' end未用半角括号括起，因此不会处理该语句。

TITLE和CREATE VIEW 输入： REPLACE VIEW ${STG_VIEW}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} AS LOCK TABLE ${STG_DATA}.B971_AUMSUMMARY${TABLE_SUFFIX_INC} FOR AC CES SSELECT 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};

约束 表中的约束应用于多列。DSC支持以下约束： REFERENCES约束/FOREIGN KEY：目前无法通过工具迁移。 PRIMARY KEY约束：可通过工具迁移。 UNIQUE约束：可通过工具迁移。 输入：CREATE TABLE，使用CONSTRAINT 1 2 3 4 5 6 7 8 910 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 910 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语句为该表字段添加列级约束。 1234 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) ; 输出： 1234 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) ; 建表脚本中，需在所有列声明之后添加约束创建语法。 父主题： 表迁移

ACCESS LOCK ACCESS LOCK允许用户从可能已经锁定READ或WRITE的表中读取数据。 可以通过tdMigrateLOCKoption参数来配置如何对包含LOCK关键字的查询进行迁移。如果该参数设置为false，工具将跳过该查询的迁移并记录日志。 输入：ACCESS LOCK（tdMigrateLOCKOption=True） 12345 LOCKING TABLE tab1 FOR ACCESS INSERT INTO tab2 SELECT … FROM … WHERE ...; 输出： 12345 /* LOCKING TABLE tab1 FOR ACCESS */INSERT INTO tab2 SELECT … FROM … WHERE ...; 父主题： Teradata语法迁移

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

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在字符串列的分区 输入： 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)) */ ;

索引迁移 Teradata中CREATE INDEX的列和表名的顺序和GaussDB(DWS)中不同。使用参数distributeByHash配置数据在集群节点间的分布方式。该工具不会添加DISTRIBUTE BY HASH用于创建具有主键和非唯一主索引的表。 输入：主键非主索引的超集，且仅有1列匹配 123456 CREATE TABLE good_5 ( column_1 INTEGER NOT NULL PRIMARY KEY ,column_2 INTEGER ,column_3 INTEGER NOT NULL ,column_4 INTEGER ) PRIMARY INDEX (column _1,column_2); 输出： 1234567 CREATE TABLE good_5 ( column_1 INTEGER NOT NULL PRIMARY KEY ,column_2 INTEGER ,column_3 INTEGER NOT NULL ,column_4 INTEGER ); 输入：主键非主索引的超集，且无匹配的列 1 2 3 4 5 6 7 8 9101112 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 ) ,PRIMARY KEY ( UPLOADCODE ,HOLDER_NO ) ) PRIMARY INDEX ( SOURCESYSTEM,EPF_IND ); 输出： 12345678 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 ) ,PRIMARY KEY (UPLOADCODE ,HOLDER_NO ) ); 输入：不存在主键，且唯一索引有名称 1 2 3 4 5 6 7 8 91011 CREATE SET TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL ( Organization_Party_Id INTEGER NOT NULL , Function_Code SMALLINT NOT NULL , Intern_Funct_Strt_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL , Intern_Funct_End_Date DATE FORMAT 'YYYY-MM-DD' )PRIMARY INDEX ( Organization_Party_Id )UNIQUE INDEX ux_t0409_intr_fn_1 ( Function_Code, Intern_Funct_Strt_Date )UNIQUE INDEX ( Organization_Party_Id, Intern_Funct_Strt_Date ); 输出： 123456789 CREATE TABLE "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Organization_Party_Id INTEGER NOT NULL , Function_Code SMALLINT NOT NULL , Intern_Funct_Strt_Date DATE NOT NULL , Intern_Funct_End_Date DATE ) DISTRIBUTE BY HASH ( Organization_Party_Id );CREATE INDEX ux_t0409_intr_fn_1 ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Function_Code, Intern_Funct_Strt_Date );CREATE UNIQUE INDEX ON "DP_TEDW"."T0409_INTERNAL_ORG_GRP_FUNCT" ( Organization_Party_Id, Intern_Funct_Strt_Date ); 输入：CREATE TABLE，使用主键和非唯一主索引（未添加DISTRIBUTE BY HASH） 1 2 3 4 5 6 7 8 910 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 91011 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 ) ); 父主题： Teradata语法迁移

WITH AS WITH AS 在GaussDB(DWS)中用于声明一个或多个可以在主查询中通过名字引用的子查询，相当于临时表。DSC工具支持该关键字，迁移工程中保留。 输入示例 1 2 3 4 5 6 7 8 910 WITH e AS ( SELECT city, sum(population) FROM t1 group by city ), d AS ( SELECT max(music) as max_music, min(music) as min_music from student ), s AS ( SELECT * FROM subject )SELECT * FROM e; 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324 WITH e AS ( SELECT city, sum(population) FROM t1 GROUP BY city),d AS ( SELECT max(music) AS "max_music", min(music) AS "min_music" FROM student),s AS ( SELECT * FROM subject)SELECT *FROM e; 父主题： 表（可选参数、操作）

DBC.COLUMNS DBC.COLUMNS视图是一个表，包含有关表和视图列、存储过程、或宏参数的信息。其中包括以下列：DatabaseName、TableName、ColumnName、ColumnFormat、ColumnTitle、ColumnType、DefaultValue。在GaussDB(DWS)中，这个表等效于information_schema.columns表。 本特性要求一次性执行以下自定义脚本文件：DSC/scripts/teradata/db_scripts/mig_fn_get_datatype_short_name.sql 有关文件执行的详细步骤，请参见运行环境和前提条件。 迁移工具将以下dbc.columns列迁移为对应的information_schema列： 表1 dbc.columns列迁移到information_schema列 dbc.columns information_schema.columns ColumnName Column_Name ColumnType mig_fn_get_datatype_short_name (data_Type) ColumnLength character_maximum_length DecimalTotalDigits numeric_precision DecimalFractionalDigits numeric_scale databasename table_schema tablename table_name ColumnId ordinal_position 迁移dbc.columns时，假设以下条件成立： FROM子句仅包含dbc.columns的TABLE NAME。 COLUMN NAME为以下任一格式：column_name或schema_name.table_name.column_name。 以下场景不支持dbc.columns迁移： FROM子句包含dbc.columns表名的别名（dbc.columns别名）。 dbc.columns与其他表组合（FROM dbc.columns alias1，table1 alias2 OR dbc.columns alias1 join table1 alias2）。 如果输入的SELECT语句直接包含dbc.columns的列名，则该工具会将输入的列名称迁移为别名。例如，输入列名称DecimalFractionalDigits会迁移为numeric_scale，其别名为DecimalFractionalDigits。 示例： 输入： 123456 SEL columnid ,DecimalFractionalDigits FROM dbc.columns; 输出： 123456 SELECT ordinal_position columnid ,numeric_scale DecimalFractionalDigits FROM information_schema.columns; 关于表名和模式名称，迁移工具会将所有字符串值转换为小写。如果要区分大小写，使用双引号表示表/模式名称。在以下输入示例中，“Test”不会转换为小写。 123456 SELECT TableName FROM dbc . columns WHERE dbc.columns.databasename = '"Test"'; 输入：dbc.columns table，指定所有支持列 1 2 3 4 5 6 7 8 910111213141516171819202122232425 SELECT'$AUTO_DB_IP',objectdatabasename,objecttablename,'$TX_DATE_10','','0' ,FirstStepTime,FirstRespTime,RowCount,cast(RowCount*sum(case when T2.ColumnType ='CV' then T2.ColumnLength/3 else T2.ColumnLength end) as decimal(38,0)),'3' ,'','BAK_CLR_DATA','2',''FROM TMP_clr_information T1inner join dbc.columns T2on T1.objectdatabasename =T2.DatabaseNameand T1.objecttablename =T2.TableNamewhere T2.DatabaseName not in (sel child from dbc.children where parent='$FCRM_DB')group by 1,2,3,4,5,6,7,8,9,11,12,13,14,15; 输出： 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930313233 SELECT '$AUTO_DB_IP' ,objectdatabasename ,objecttablename ,'$TX_DATE_10' ,'' ,'0' ,FirstStepTime ,FirstRespTime ,RowCount ,CAST( RowCount * SUM ( CASE WHEN mig_fn_get_datatype_short_name ( T2.data_Type ) = 'CV' THEN T2.character_maximum_length / 3 ELSE T2.character_maximum_length END ) AS DECIMAL( 38 ,0 ) ) ,'3' ,'' ,'BAK_CLR_DATA' ,'2' ,'' FROM TMP_clr_information T1 INNER JOIN information_schema.columns T2 ON T1.objectdatabasename = T2.table_schema AND T1.objecttablename = T2.table_name WHERE NOT EXISTS ( SELECT child FROM dbc.children WHERE child = T2.table_schema AND( parent = '$FCRM_DB' ) ) GROUP BY 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,11 ,12 ,13 ,14 ,15; 输入：dbc.columns table，指定表名 1 2 3 4 5 6 7 8 910 SELECT TRIM( ColumnName ) ,UPPER( dbc.columns.ColumnType ) FROM dbc . columns WHERE dbc.columns.databasename = '"Test"' ORDER BY dbc.columns.ColumnId; 输出： 1 2 3 4 5 6 7 8 91011121314 SELECT TRIM( Column_Name ) ,UPPER( mig_fn_get_datatype_short_name ( information_schema.columns.data_Type ) ) FROM information_schema.columns WHERE information_schema.columns.table_schema = CASE WHEN TRIM( '"Test"' ) LIKE '"%' THEN REPLACE( SUBSTR( '"Test"' ,2 ,LENGTH( '"Test"' ) - 2 ) ,'""' ,'"' ) ELSE LOWER( '"Test"' ) END ORDER BY information_schema.columns.ordinal_position; 父主题： Teradata语法迁移

LIKE 表克隆 MySQL数据库中，可以使用CREATE TABLE .. LIKE ..方式克隆旧表结构创建新表。GaussDB(DWS)也支持这种建表方式。DSC工具迁移时会添加额外的表属性信息。 输入示例 12345678 CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl_old`( `dataType_1` YEAR, `dataType_2` YEAR(4), `dataType_3` YEAR DEFAULT '2018', `dataType_4` TIME DEFAULT NULL);CREATE TABLE `runoob_tbl` (like `runoob_tbl_old`); 输出示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_old"( "datatype_1" SMALLINT, "datatype_2" SMALLINT, "datatype_3" SMALLINT DEFAULT '2018', "datatype_4" TIME WITHOUT TIME ZONE DEFAULT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1");CREATE TABLE "public"."runoob_tbl"( LIKE "public"."runoob_tbl_old" INCLUDING COMMENTS INCLUDING CONSTRAINTS INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING STORAGE); 父主题： 表（可选参数、操作）

MODIFY修改列 MySQL使用MODIFY关键字修改列数据类型、设置非空约束。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718 CREATE TABLE IF NOT EXISTS `runoob_alter_test`( `dataType0` varchar(100), `dataType1` bigint, `dataType2` bigint, `dataType3` bigint)ENGINE=InnoDB DEFAULT CHARSET=utf8;## A.ALTER TABLE runoob_alter_test MODIFY dataType1 smallint;## B.ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL;## C.ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL FIRST;## D.ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL AFTER dataType3; 输出示例 1 2 3 4 5 6 7 8 910111213141516171819202122 CREATE TABLE "public"."runoob_alter_test"( "datatype0" VARCHAR(400), "datatype1" BIGINT, "datatype2" BIGINT, "datatype3" BIGINT) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype0");-- A.ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NULL DEFAULT NULL;-- B.ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL;-- C.ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL;-- D.ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL; 父主题： 表（可选参数、操作）

行列存压缩 GaussDB(DWS)中，只支持列存表压缩功能，暂不支持行存表压缩功能。优化行列存压缩机制，DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 压缩参数 : table.compress.mode创建新表时，需要在CREATE TABLE语句中指定关键字COMPRESS，这样，当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据，生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。 table.compress.row和table.compress.column指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。 table.compress.level指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。 行存表输入示例 1234567 DROP TABLE IF EXISTS `public`.`runoob_tbl`;CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl`( `runoob_id` VARCHAR, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR)ENGINE=InnoDB DEFAULT CHARSET=utf8; 行存表输出示例 1234567 DROP TABLE IF EXISTS "public"."runoob_tbl";CREATE TABLE IF NOT EXISTS "public"."runoob_tbl" ( "runoob_id" VARCHAR, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR) WITH (ORIENTATION = ROW, COMPRESSION = YES) COMPRESS DISTRIBUTE BY HASH ("runoob_id"); 列存表输入示例 1234567 DROP TABLE IF EXISTS `public`.`runoob_tbl`;CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl`( `runoob_id` VARCHAR, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR)ENGINE=InnoDB DEFAULT CHARSET=utf8; 列存表输出示例 1 2 3 4 5 6 7 8 91011 DROP TABLE IF EXISTS "public"."runoob_tbl";CREATE TABLE IF NOT EXISTS "public"."runoob_tbl" ( "runoob_id" VARCHAR, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR) WITH ( COMPRESSLEVEL = 1, ORIENTATION = COLUMN, COMPRESSION = LOW) DISTRIBUTE BY HASH ("runoob_id"); 父主题： 表（可选参数、操作）

唯一索引 GaussDB(DWS)不支持唯一索引(约束)与主键约束联合使用。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 MySQL唯一索引(约束)与主键约束联合使用的场景在工具迁移时会与OLAP场景下的分布键构成复杂的关系。工具暂不支持唯一索引(约束)与主键约束联合使用的场景。 内联唯一索引，如存在主键索引与唯一索引是相同列，DSC工具迁移时会将唯一索引移除。 输入示例 123456 CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, UNIQUE (id ASC)); 输出示例 12345678 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "id" SERIAL PRIMARY KEY, "name" VARCHAR(128) NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id"); ALTER TABLE创建唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性创建普通索引。 输入示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425 CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8))ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE runoob_alter_test ADD UNIQUE idx_runoob_alter_test_datatype1(dataType1);ALTER TABLE runoob_alter_test ADD UNIQUE INDEX idx_runoob_alter_test_datatype1(dataType2);ALTER TABLE runoob_alter_test ADD UNIQUE KEY idx_runoob_alter_test_datatype1(dataType3);CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8), `dataType4` TEXT NOT NULL, `dataType5` YEAR NOT NULL DEFAULT '2018', `dataType6` DATETIME NOT NULL DEFAULT '2018-10-12 15:27:33.999999')ENGINE=InnoDB DEFAULT CHARSET=utf8;ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE idx_runoob_alter_test_datatype1(dataType1);ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE INDEX idx_runoob_alter_test_datatype2(dataType2);ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE KEY idx_runoob_alter_test_datatype3(dataType3);ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE idx_runoob_alter_test_datatype4(dataType4);ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE INDEX idx_runoob_alter_test_datatype5(dataType5);ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE KEY idx_runoob_alter_test_datatype6(dataType6); 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627282930313233 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"( "datatype1" INTEGER, "datatype2" REAL, "datatype3" DOUBLE PRECISION) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1");CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype2");CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype3");CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"( "datatype1" INTEGER, "datatype2" REAL, "datatype3" DOUBLE PRECISION, "datatype4" TEXT NOT NULL, "datatype5" SMALLINT NOT NULL DEFAULT '2018', "datatype6" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2018-10-12 15:27:33.999999') WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1");CREATE INDEX "idx_runoob_alter_test_datatype2" ON "public"."runoob_alter_test" ("datatype2");CREATE INDEX "idx_runoob_alter_test_datatype3" ON "public"."runoob_alter_test" ("datatype3");CREATE INDEX "idx_runoob_alter_test_datatype4" ON "public"."runoob_alter_test" ("datatype4");CREATE INDEX "idx_runoob_alter_test_datatype5" ON "public"."runoob_alter_test" ("datatype5");CREATE INDEX "idx_runoob_alter_test_datatype6" ON "public"."runoob_alter_test" ("datatype6"); CREATE INDEX创建唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性创建普通索引。 输入示例 123456789 CREATE TABLE `public`.`test_index_table01` (`TABLE01_ID` INT(11) NOT NULL,`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,`AUTHOR_ID` INT(11) NULL DEFAULT NULL,`CREATE_TIME` INT NULL DEFAULT NULL,PRIMARY KEY(`TABLE01_ID`));CREATE UNIQUE INDEX AUTHOR_INDEX ON `test_index_table01`(AUTHOR_ID); 输出示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE "public"."test_index_table01"( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, PRIMARY KEY ("table01_id")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id");CREATE INDEX "author_index" ON "public"."test_index_table01" ("author_id"); CREATE TABLE中存在多个唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性将所有唯一索引创建为普通索引。 输入示例 123456789 CREATE TABLE `public`.`test_index_table01` (`TABLE01_ID` INT(11) NOT NULL,`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,`AUTHOR_ID` INT(11) NULL DEFAULT NULL,`CREATE_TIME` INT NULL DEFAULT NULL,UNIQUE(`TABLE01_ID`),UNIQUE(`AUTHOR_ID`)); 输出示例 123456789 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id");CREATE INDEX "idx_test_index_table01_table01_id" ON "public"."test_index_table01"("TABLE01_ID");CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID"); CREATE TABLE中存在一个唯一索引，并不存在主键索引时，DSC工具迁移时会根据GaussDB(DWS)的特性保留该唯一索引。 输入示例 12345678 CREATE TABLE `public`.`test_index_table01` (`TABLE01_ID` INT(11) NOT NULL,`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,`AUTHOR_ID` INT(11) NULL DEFAULT NULL,`CREATE_TIME` INT NULL DEFAULT NULL,UNIQUE(`AUTHOR_ID`)); 输出示例 12345678 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, UNIQUE ("author_id")) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("author_id"); CREATE TABLE中存在主键索引时，DSC工具迁移时会根据GaussDB(DWS)的特性将所有的唯一索引创建为普通索引。 输入示例 123456789 CREATE TABLE `public`.`test_index_table01` (`TABLE01_ID` INT(11) NOT NULL,`TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL,`AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL,`AUTHOR_ID` INT(11) NULL DEFAULT NULL,`CREATE_TIME` INT NULL DEFAULT NULL,PRIMARY KEY(`TABLE01_ID`),UNIQUE(`AUTHOR_ID`)); 输出示例 123456789 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, PRIMARY KEY ("table01_id")) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id");CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID"); 父主题： 索引

LIKE ANY/NOT LIKE ANY 输入：LIKE ANY/NOT LIKE ANY 123 SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY ('STR1%', 'STR2%', 'STR3%'); 输出： 123 SELECT c1, c2 FROM tab1 WHERE c3 LIKE ANY (ARRAY[ 'STR1%', 'STR2%', 'STR3%' ]);

LIKE ALL/NOT LIKE ALL 输入：LIKE ALL/NOT LIKE ALL 123 SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL ('%STR1%', '%STR2%', '%STR3%'); 输出： 123 SELECT c1, c2 FROM tab1 WHERE c3 NOT LIKE ALL (ARRAY[ '%STR1%', '%STR2%', '%STR3%' ]);

FORMAT和CAST Teradata中，关键词FORMAT用于格式化列或表达式。例如，LPAD中FORMAT '9(n)'和'z(n)'分别用'0'和空格(' ')表示。 数据类型转换可使用CAST或直接数据类型（[like (expression1)(CHAR(n))]）进行。该功能使用CAST实现。详情参见类型转换和格式化。 输入：FORMAT和CAST 1 2 3 4 5 6 7 8 91011121314151617 SELECT CAST(TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) FORMAT '9(5)' ) FROM C03_AGENT_BOND;SELECT CAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) ) FROM C03_AGENT_BOND;SELECT CHAR(CAST( CAST( CND_VLU AS DECIMAL( 17 ,0 ) FORMAT 'Z(17)' ) AS VARCHAR( 17 ) ) ) FROM C03_AGENT_BOND; 输出： SELECT LPAD( CAST( TRIM( Agt_Num ) AS DECIMAL( 5 ,0 ) ) ,5 ,'0' ) AS Agt_Num FROM C03_AGENT_BOND;SELECTCAST(CAST( Agt_Num AS INT FORMAT 'Z(17)' ) AS CHAR( 5 ) )FROMC03_AGENT_BOND;SELECT LENGTH( CAST( LPAD( CAST( CND_VLU AS DECIMAL( 17 ,0 ) ) ,17 ,' ' ) AS VARCHAR( 17 ) ) ) AS CND_VLU FROM C03_AGENT_BOND; 输入：FORMAT 'Z(n)9' 123456 SELECT standard_price (FORMAT 'Z(5)9') (CHAR( 6 )) ,max_price (FORMAT 'ZZZZZ9') (CHAR( 6 )) FROM product_t; 输出： 123456 SELECT CAST( TO_CHAR( standard_price ,'999990' ) AS CHAR( 6 ) ) AS standard_price ,CAST( TO_CHAR( max_price ,'999990' ) AS CHAR( 6 ) ) AS max_price FROM product_t; 输入：FORMAT 'z(m)9.9(n)' 12345 SELECT standard_price (FORMAT 'Z(6)9.9(2)') (CHAR( 6 )) FROM product_t; 输出： 12345 SELECT CAST( TO_CHAR( standard_price ,'9999990.00' ) AS CHAR( 6 ) ) AS standard_price FROM product_t; 输入：CAST AS INTEGER 12345 SELECT CAST( standard_price AS INTEGER ) FROM product_t; 输出： 12345 SELECT (standard_price) FROM product_t; 输入：CAST AS INTEGER FORMAT 1234567 SELECT CAST( price11 AS INTEGER FORMAT 'Z(4)9' ) ( CHAR( 10 ) ) FROM product_t; 输出： 12345 SELECT CAST( TO_CHAR( ( price11 ) ,'99990' ) AS CHAR( 10 ) ) AS price11 FROM product_t; 新增以下GaussDB(DWS)函数来转换为INTEGER： 1 2 3 4 5 6 7 8 910111213141516171819 CREATE OR REPLACE FUNCTION /* This function is used to support "CAST AS INTEGER" of Teradata. It should be created in the "mig_td_ext" schema.*/ ( i_param TEXT )RETURN INTEGERAS v_castasint INTEGER;BEGIN v_castasint := CASE WHEN i_param IS NULL THEN NULL -- if NULL value is provided as input WHEN TRIM(i_param) IS NULL THEN 0 -- if empty string with one or more spaces is provided ELSE TRUNC(CAST(i_param AS NUMBER)) -- if any numeric value is provided END;RETURN v_castasint;END; 父主题： 函数和操作符

类型对照 输入示例JSON 1234567 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` INT, `dataType_2` VARCHAR, `dataType_3` JSON);ALTER TABLE `runoob_dataType_test` ADD COLUMN `dataType_4` JSON NOT NULL;ALTER TABLE `runoob_dataType_test` CHANGE COLUMN `dataType_4` `dataType_5` JSON NOT NULL; 输出示例 1 2 3 4 5 6 7 8 9101112131415 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" INTEGER, "datatype_2" VARCHAR, "datatype_3" JSONB) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1");ALTER TABLE "public"."runoob_datatype_test"ADD COLUMN "datatype_4" JSONB;ALTER TABLE "public"."runoob_datatype_test" CHANGE COLUMN "datatype_4" "datatype_5" JSONB;

AVG_ROW_LENGTH 在MySQL中，AVG_ROW_LENGTH表示平均每行的长度。 GaussDB(DWS)不支持此属性，并在迁移过程中被DSC删除。 输入示例 123456 CREATE TABLE `public`.`runoob_tbl_test`( `runoob_id` VARCHAR(30), `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR(30))AVG_ROW_LENGTH=10000; 输出示例 1 2 3 4 5 6 7 8 910 CREATE TABLE "public"."runoob_tbl_test"( "runoob_id" VARCHAR(120), "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR(120)) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id"); 父主题： 表（可选参数、操作）

类型对照 表1 二进制类型对照表 MySQL二进制类型 MySQL INPUT GaussDB(DWS) OUTPUT BIT[(M)] BIT[(M)] BIT[(M)] BINARY[(M)] BINARY[(M)] BYTEA CHAR BYTE[(M)] BINARY[(M)] BYTEA VARBINARY[(M)] VARBINARY[(M)] BYTEA 输入示例BIT 12345 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` INT, `dataType_2` BIT(1), `dataType_3` BIT(64)); 输出示例 123456789 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" INTEGER, "datatype_2" BIT(1), "datatype_3" BIT(64)) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1"); 输入示例[VAR]BINARY 12345678 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` INT, `dataType_2` BINARY, `dataType_3` BINARY(0), `dataType_4` BINARY(255), `dataType_5` VARBINARY(0), `dataType_6` VARBINARY(6553)); 输出示例 1 2 3 4 5 6 7 8 9101112 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" INTEGER, "datatype_2" BYTEA, "datatype_3" BYTEA, "datatype_4" BYTEA, "datatype_5" BYTEA, "datatype_6" BYTEA) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1");