华为云用户手册

DECIMAL 输入：DECIMAL转换 123456789 SELECT standard_price ( DECIMAL( 17 ) ,FORMAT '9(17)' ) ( CHAR( 17 ) ) FROM db_pvfc9_std.Product_t; 输出 1234 SELECT CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS CHAR( 17 ) ) FROM db_pvfc9_std.Product_t; 输入：DECIMAL转换 123456789 SELECT standard_price ( DECIMAL( 17 ,0 ) ,FORMAT '9(17)' ) ( VARCHAR( 17 ) ) FROM db_pvfc9_std.Product_t; 输出 1234 SELECT CAST( LPAD( CAST( standard_price AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) ) FROM db_pvfc9_std.Product_t; 输入：DECIMAL转换 1 2 3 4 5 6 7 8 910 SELECT customer_id ( DECIMAL( 17 ) ) ( FORMAT '9(17)' ) ( VARCHAR( 17 ) ) FROM db_pvfc9_std.Customer_t; 输出 1234 SELECT CAST( LPAD( CAST( customer_id AS DECIMAL( 17 ,0 ) ) ,17 ,'0' ) AS VARCHAR( 17 ) ) FROM db_pvfc9_std.Customer_t;

隐式类型转换 输入：隐式类型转换 1 2 3 4 5 6 7 8 910111213141516171819202122232425262728293031 SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT '101' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT UNION ALL SELECT '201' AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt FROM Sys_Calendar.CALENDAR WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AND Day_Of_Week = 1 UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT '401' AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD') UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT '501' AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD') UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT '701' AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD') ) T1 ; 输出 SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT CAST('101' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-1 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT UNION ALL SELECT CAST('201' AS TEXT) AS Data_Type,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')-7 AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt FROM Sys_Calendar.CALENDAR WHERE calendar_date = CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AND Day_Of_Week = 1 UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT CAST('401' AS TEXT) AS Data_Type,CAST('${TX_PRIMONTH_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_MONTH_END}' AS DATE FORMAT 'YYYYMMDD') UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT CAST('501' AS TEXT) AS Data_Type,CAST('${TX_PRIQUARTER_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_QUARTER_END}' AS DATE FORMAT 'YYYYMMDD') UNION ALL SELECT Data_Type,Start_Dt,End_Dt FROM ( SELECT CAST('701' AS TEXT) AS Data_Type,CAST('${TX_PRIYEAR_END}' AS DATE FORMAT 'YYYYMMDD') AS Start_Dt,CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD') AS End_Dt ) TT WHERE CAST('${TX_DATE}' AS DATE FORMAT 'YYYYMMDD')=CAST('${TX_YEAR_END}' AS DATE FORMAT 'YYYYMMDD') ) T1 ;

DATE 在Teradata中对DATE进行格式转换时，使用AS FORMAT。DSC将添加TO_CHAR函数来保留指定的输入格式。 详情请参见日期和时间函数。 输入：数据类型转换，不使用DATE关键字 123 SELECT CAST( CAST( '2013-02-12' AS DATE FORMAT 'YYYY/MM/DD' ) AS FORMAT 'DD/MM/YY' ); 输出 123 SELECT TO_CHAR( CAST( '2013-02-12' AS DATE ) ,'DD/MM/YY' );

转换数据类型DAY为SECOND 输入：DAY转换为SECOND 12 SELECT CAST(T1.Draw_Gold_Dt || ' ' ||T1.Draw_Gold_Tm as Timestamp)- CAST(T1.Tx_Dt || ' '|| T1.Tx_Tm as Timestamp) DAY(4) To SECOND from db_pvfc9_std.draw_tab T1; 输出 12345 SELECT CAST(( CAST( T1.Draw_Gold_Dt || ' ' || T1.Draw_Gold_Tm AS TIMESTAMP ) - CAST(T1.Tx_Dt || ' ' || T1.Tx_Tm AS TIMESTAMP ) ) AS INTERVAL DAY ( 4 ) TO SECOND ) FROM db_pvfc9_std.draw_tab T1;

COLUMNS和COLUMN ALIAS 输入：对某列进行类型转换和格式化时，应确保列名和别名相同 1234567 SELECT Product_Line_ID, MAX(Standard_Price) FROM ( SELECT A.Product_Description, A.Product_Line_ID , A.Standard_Price(DECIMAL(18),FORMAT '9(18)')(CHAR(18)) FROM product_t A WHERE Product_Line_ID in (1, 2) ) AS tabAls GROUP BY Product_Line_ID; 输出 1234567 SELECT Product_Line_ID, MAX( Standard_Price ) FROM ( SELECT A.Product_Description, A.Product_Line_ID , CAST( LPAD( CAST(A.Standard_Price AS DECIMAL( 18 ,0 )), 18, '0' ) AS CHAR( 18 ) ) AS Standard_Price FROM product_t A WHERE Product_Line_ID IN( 1 ,2 ) ) AS tabAls GROUP BY Product_Line_ID;

表达式 输入：对表达式进行类型转换和格式化 123 SELECT product_id, standard_price*100.00(DECIMAL (17),FORMAT '9(17)' )(CHAR(17) ) AS order_amt FROM db_pvfc9_std.Product_t WHERE product_line_id is not null; 输出 123 SELECT product_id, CAST(LPAD(CAST(standard_price*100.00 AS DECIMAL(17)), 17, '0') AS CHAR(17)) AS order_amt FROM db_pvfc9_std.Product_t WHERE product_line_id is not null;

CREATE TABLE Teradata的CREATE TABLE （缩写关键字为CT）语句用于创建表。 示例： 输入：CREATE TABLE 123 CT tab1 ( id INT); 输出： 123456 CREATE TABLE tab1 ( id INTEGER ); 执行CREATE tab2 AS tab1时，从tab1中复制的结构将用于创建表tab2。如果CREATE TABLE语句包含WITH DATA选项，则会将tab1的数据也复制到tab2中。使用CREATE AS时，源表中的CONSTRAINT行将保留在新表中。 如果session_mode设为Teradata，则必须删除目标表中的重复记录。该操作通过在迁移脚本中添加MINUS运算符实现。 如果session_mode设为ANSI，则允许目标表中存在重复记录。 如果源表具有PRIMARY KEY（主键）或UNIQUE CONSTRAINT（唯一约束），则该表不包含任何重复记录。在这种情况下，不需要添加MINUS操作符删除重复的记录。 示例： 输入：CREATE TABLE AS WITH DATA（session_mode=Teradata） 12 CREATE TABLE tab2 AS tab1 WITH DATA; 输出： 1 2 3 4 5 6 7 8 91011 BEGIN CREATE TABLE tab2 ( LIKE tab1 INCLUDING ALL EXCLUDING PARTITION EXCLUDING RELOPTIONS ); INSERT INTO tab2SELECT * FROM tab1 MINUS SELECT * FROM tab2;END;/ 输入：CREATE TABLE AS WITH DATA AND STATIS TICS 1234 CREATE SET VOLATILE TABLE tab2025 AS ( SELECT * from tab2023 ) WITH DATA AND STATISTI CS PRIMARY INDEX ( LOG TYPE, OPERSEQ); 输出： 12345 CREATE LOCAL TEMPORARY TABLE tab2025 DISTRIBUTE BY HASH ( LOGTYPE, OPERSEQ ) AS ( SELECT * FROM tab2023 ); ANALYZE tab2025; 父主题： 表迁移

ALTER TABLE RENAME GaussDB (DWS)不支持rename子句包含schema名，因此DSC工具只支持同schema下的rename。同schema下rename，转换结果去掉子句schema，跨schema的rename报错。 输入示例 1234 ALTER TABLE `shce1`.`t1` rename to `t2`;ALTER TABLE `shce1`.`t1` rename to t2;ALTER TABLE `charge_data`.`group_shengfen2022` RENAME `charge_data`.`group_shengfen2022_jiu`;ALTER TABLE `charge_data`.`group_shengfen2022` RENAME `charge_data`.`group_shengfen2022_jiu`, RENAME `charge_data`.`group_shengfen2023_jiu`, RENAME `charge_data`.`group_shengfen2024_jiu`; 输出示例 1234 ALTER TABLE "shce1"."t1" RENAME TO "t2";ALTER TABLE "shce1"."t1" RENAME TO "t2";ALTER TABLE "charge_data"."group_shengfen2022" RENAME TO "group_shengfen2022_jiu";ALTER TABLE "charge_data"."group_shengfen2022" RENAME TO "group_shengfen2022_jiu", RENAME TO "group_shengfen2023_jiu", RENAME TO "group_shengfen2024_jiu"; 父主题： 表（可选参数、操作）

类型对照 表1 空间数据类型对照表 MySQL空间数据类型 MySQL INPUT GaussDB(DWS) OUTPUT GEOMETRY GEOMETRY GEOMETRY POINT POINT POINT LINESTRING LINESTRING POLYGON POLYGON POLYGON POLYGON MULTIPOINT MULTIPOINT BOX MULTILINESTRING MULTILINESTRING BOX MULTIPOLYGON MULTIPOLYGON POLYGON GEOMETRYCOLLECTION GEOMETRYCOLLECTION GEOMETRYCOLLECTION GEOMETRY可以存储任何类型的几何值。其他单值类型（POINT， LINESTRING和POLYGON）将其值限制为特定的几何类型。 GEOMETRYCOLLECTION可以存储任何类型的对象的集合。其他集合类型（MULTIPOINT， MULTILINESTRING， MULTIPOLYGON，和 GEOMETRYCOLLECTION）限制集合成员像那些具有特定的几何形状的类型。 输入示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE `t_geo_test2` ( `id` int(11) NOT NULL, `name` varchar(255), `geometry_1` geometry NOT NULL, `point_1` point NOT NULL, `linestring_1` linestring NOT NULL, `polygon_1` polygon NOT NULL, `multipoint_1` multipoint NOT NULL, `multilinestring_1` multilinestring NOT NULL, `multipolygon_1` multipolygon NOT NULL, `geometrycollection_1` geometrycollection NOT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB; 输出示例 1 2 3 4 5 6 7 8 91011121314151617 CREATE TABLE "public"."t_geo_test2"( "id" INTEGER(11) NOT NULL, "name" VARCHAR(255), "geometry_1" GEOMETRY NOT NULL, "point_1" POINT NOT NULL, "linestring_1" POLYGON NOT NULL, "polygon_1" POLYGON NOT NULL, "multipoint_1" BOX NOT NULL, "multilinestring_1" BOX NOT NULL, "multipolygon_1" POLYGON NOT NULL, "geometrycollection_1" GEOMETRYCOLLECTION NOT NULL, PRIMARY KEY ("id")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");

BLOCK_SIZE 在ADB中，指定列式存储中每个block存储的Value的个数，也是最小的IO单元。GaussDB(DWS)不支持该属性修改表定义信息，DSC迁移时会将该关键字删除。 输入示例 1234567 DROP TABLE IF EXISTS exists unsupport_parse_test;CREATE TABLE `unsupport_parse_test` ( `username` int, `update` timestamp not null default current_timestamp on update current_timestamp , clustered key clustered_key(shopid ASC, datetype ASC))BLOCK_SIZE = 1024 index_ALL = 'y';DROP TABLE IF EXISTS unsupport_parse_test; 输出示例 123456 DROP TABLE IF EXISTS "public"."unsupport_parse_test";CREATE TABLE "public"."unsupport_parse_test" ( "username" INTEGER, "update" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("username");DROP TABLE IF EXISTS "public"."unsupport_parse_test"; 父主题： 表（可选参数、操作）

以YYYYMMDD格式输入的日期 输入 输出 123 SELECT 1 FROM tb_dt_fmtyyyymmdd WHERE JobName ='${JOB_NAME}' AND TXDATE = ${TX_DATE} - 19000000; SELECT 1 FROM tb_dt_fmtyyyymmdd WHERE JobName ='${JOB_NAME}' AND TXDATE = TO_DATE(${TX_DATE}, 'YYYYMMDD');

以YYYYDDD格式输入日期 输入 输出 1 2 3 4 5 6 7 8 9101112 REPLACE VIEW SC.VIEW_1 ( col_1 ) LOCKING TABLE sc.tab FOR AC CES S AS SEL --tgt.col_1 is date type CAST( CAST(TGT.col_1 AS DATE FORMAT 'YYYYDDD') AS CHAR(7) ) AS col_1 FROM sc.tab TGT ; 1 2 3 4 5 6 7 8 910 CREATE OR REPLACE VIEW SC.VIEW_1 (col_1) /*LOCKING TABLE sc.tab FOR ACCESS */ AS ( SELECT /* tgt.col_1 is date type */ CAST( TO_DATE(TGT.col_1, 'YYYYDDD') AS CHAR( 7 ) ) AS col_1 FROM sc.tab TGT ) ;

类型转换时优先执行数据库操作 输入 输出 1 2 3 4 5 6 7 8 91011 REPLACE VIEW SC.VIEW_1 ( col_1 ) LOCKING TABLE sc.tab FOR ACCESS AS SEL (COALESCE(TRIM(TGT.col_1),'')) || '_' || (COALESCE(TRIM(TGT.col_1),'')) (CHAR(22)) AS col_1 FROM sc.tab TGT ; 1 2 3 4 5 6 7 8 91011 CREATE OR REPLACE VIEW SC.VIEW_1 (col_1) /*LOCKING TABLE sc.tab FOR ACCESS */ AS ( SELECT CAST( ( COALESCE( TRIM( TGT.col_1 ) ,'' ) ) || '_' || ( COALESCE( TRIM( TGT.col_1 ) ,'' ) ) AS CHAR( 22 ) ) AS col_1 FROM sc.tab TGT ) ;

以#开头的列名 输入 输出 1 2 3 4 5 6 7 8 9101112131415 REPLACE VIEW SC.VIEW_1 ( ,col_1 ,#_col_2 ,#_col_3 ) LOCKING TABLE sc.tab FOR ACCESS AS SEL Tgt.col1 ,Tgt.#_col_2 ,Tgt.#_col_3 FROM sc.tab TGT ; 1 2 3 4 5 6 7 8 91011121314 CREATE OR REPLACE VIEW SC.VIEW_1 ( ,col_1 ,"#_COL_2" ,"#_COL_3" ) /*LOCKING TABLE sc.tab FOR ACCESS */ AS ( SELECT Tgt.col1 ,Tgt."#_COL_2" ,Tgt."#_COL_3" FROM sc.tab TGT ) ;

TRUNCATE 删除表 MySQL在使用TRUNCATE语句删除表数据时可以省略“TABLE”关键字，GaussDB(DWS)不支持这种用法。此外，DSC工具在做迁移TRUNCATE语句时会添加“CONTINUE IDENTITY RESTRICT”关键字。 输入示例 12 TRUNCATE TABLE `public`.`test_create_table01`;TRUNCATE TEST_CREATE_TABLE01; 输出示例 12 TRUNCATE TABLE "public"."test_create_table01" CONTINUE IDENTITY RESTRICT;TRUNCATE TABLE "public"."test_create_table01" CONTINUE IDENTITY RESTRICT; 父主题： 表（可选参数、操作）

ROUNDROBIN表 GaussDB(DWS)支持建立roundrobin表，根据实际需要设置表1中的参数table.type进行配置。设置table.type=ROUND-ROBIN。 输入示例 12345 CREATE TABLE charge_snapshot (id bigint NOT NULL,profit_model integer,ladder_rebate_rule text); 输出示例 12345 CREATE TABLE "public"."charge_snapshot" ( "id" BIGINT NOT NULL, "profit_model" INTEGER, "ladder_rebate_rule" TEXT) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY ROUNDROBIN; 父主题： 表（可选参数、操作）

ROW_FORMAT ROW_FORMAT定义了行存储的物理形式。ROW_FORMAT的选择与存储引擎有关，如果在创建表的时候选择了存储引擎不相关的ROW_FORMAT，则使用默认的ROW_FORMAT创建表。当ROW_FORMAT取值为DEFAULT，DSC迁移为SET NOCOMPRESS；当ROW_FORMAT取值为COMPRESSED时，DSC迁移为SET COMPRESS。GaussDB(DWS)不支持其他取值，当取其他值时DSC迁移时会将该属性删除。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627 CREATE TABLE `public`.`runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8), `dataType4` TEXT NOT NULL, PRIMARY KEY(`dataType1`)) ENGINE=InnoDB;## A.ALTER TABLE runoob_alter_test ROW_FORMAT DEFAULT;ALTER TABLE runoob_alter_test ROW_FORMAT=DEFAULT;## B.ALTER TABLE runoob_alter_test ROW_FORMAT DYNAMIC;ALTER TABLE runoob_alter_test ROW_FORMAT=DYNAMIC;## C.ALTER TABLE runoob_alter_test ROW_FORMAT COMPRESSED;ALTER TABLE runoob_alter_test ROW_FORMAT=COMPRESSED;## D.ALTER TABLE runoob_alter_test ROW_FORMAT REDUNDANT;ALTER TABLE runoob_alter_test ROW_FORMAT=REDUNDANT;## E.ALTER TABLE runoob_alter_test ROW_FORMAT COMPACT;ALTER TABLE runoob_alter_test ROW_FORMAT=COMPACT; 输出示例 1 2 3 4 5 6 7 8 910111213141516171819202122232425 CREATE TABLE "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "datatype2" REAL, "datatype3" DOUBLE PRECISION, "datatype4" TEXT NOT NULL, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1");-- A.ALTER TABLE "public"."runoob_alter_test" SET NOCOMPRESS;ALTER TABLE "public"."runoob_alter_test" SET NOCOMPRESS;-- B.-- C.ALTER TABLE "public"."runoob_alter_test" SET COMPRESS;ALTER TABLE "public"."runoob_alter_test" SET COMPRESS;-- D.-- E. 父主题： 表（可选参数、操作）

RENAME 重命名表名 MySQL重命名表名的语句与GaussDB(DWS)有一些差异。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 工具暂不支持原表名附有DATABASE(SCHEMA)的场景。 MySQL通过RENAME TABLE语句修改表名。 输入示例 12345 # 单表重命名RENAME TABLE DEPARTMENT TO NEWDEPT;# 多表重命名RENAME TABLE NEWDEPT TO NEWDEPT_02,PEOPLE TO PEOPLE_02; 输出示例 123456 --单表重命名ALTER TABLE "public"."department" RENAME TO "newdept";--多表重命名ALTER TABLE "public"."newdept" RENAME TO "newdept_02";ALTER TABLE "public"."people" RENAME TO "people_02"; MySQL通过ALTER TABLE RENAME 语句修改表名，DSC工具迁移该语句时会将“AS”关键字迁移为“TO”。 输入示例 12345 ## A.ALTER TABLE runoob_alter_test RENAME TO runoob_alter_testnew;## B.ALTER TABLE runoob_alter_testnew RENAME AS runoob_alter_testnewnew; 输出示例 12345 -- A.ALTER TABLE "public"."runoob_alter_test" RENAME TO "runoob_alter_testnew";-- B.ALTER TABLE "public"."runoob_alter_testnew" RENAME TO "runoob_alter_testnewnew"; 父主题： 表（可选参数、操作）

设置与清除列默认值 MySQL使用ALTER语句设置列默认值时可省略“COLUMN”关键字。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 9101112131415161718 CREATE TABLE IF NOT EXISTS `runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `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', `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;ALTER TABLE runoob_alter_test ALTER dataType2 SET DEFAULT 1;ALTER TABLE runoob_alter_test ALTER COLUMN dataType2 SET DEFAULT 3;ALTER TABLE runoob_alter_test ALTER dataType2 DROP DEFAULT;ALTER TABLE runoob_alter_test ALTER COLUMN dataType2 DROP DEFAULT; 输出示例 1 2 3 4 5 6 7 8 910111213141516171819202122 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "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', "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");ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '1';ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" SET DEFAULT '3';ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" DROP DEFAULT;ALTER TABLE "public"."runoob_alter_test" ALTER COLUMN "datatype2" DROP DEFAULT; 父主题： 表（可选参数、操作）

普通索引和前缀索引 GaussDB(DWS)不支持前缀索引，也不支持内联普通索引。DSC工具迁移时会根据GaussDB(DWS)的特性将其迁移为普通索引。 内联普通(前缀)索引。 输入示例 123456 CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test`( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, INDEX index_single(name(10))); 输出示例 123456789 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "id" SERIAL PRIMARY KEY, "name" VARCHAR(512) NOT NULL) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "index_single" ON "public"."runoob_datatype_test" USING BTREE ("name"); ALTER TABLE创建普通(前缀)索引。 输入示例 123456789 CREATE TABLE `public`.`test_create_table05` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `USER_ID` INT(20) NOT NULL, `USER_NAME` CHAR(20) NULL DEFAULT NULL, `DETAIL` VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (`ID`));ALTER TABLE TEST_CREATE_TABLE05 ADD INDEX USER_NAME_INDEX_02(USER_NAME(10)); 输出示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE "public"."test_create_table05"( "id" SERIAL NOT NULL, "user_id" INTEGER NOT NULL, "user_name" CHAR(80) DEFAULT NULL, "detail" VARCHAR(400) DEFAULT NULL, PRIMARY KEY ("id")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "user_name_index_02" ON "public"."test_create_table05" ("user_name"); CREATE INDEX创建普通(前缀)索引。 输入示例 1234567 CREATE TABLE IF NOT EXISTS `public`.`customer`( `name` varchar(64) primary key,id integer,id2 integer);CREATE INDEX part_of_name ON customer (name(10)); 输出示例 1 2 3 4 5 6 7 8 91011 CREATE TABLE IF NOT EXISTS "public"."customer"( "name" VARCHAR(256) PRIMARY KEY, "id" INTEGER, "id2" INTEGER) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("name");CREATE INDEX "part_of_name" ON "public"."customer" USING BTREE ("name"); 父主题： 索引

活动日志 DSC将所有日志和错误信息保存到DSC.log文件中。该文件位于log文件夹中。DSC.log文件包含执行迁移的用户、迁移的文件、时间戳等详细信息。活动日志的记录级别为INFO。 DSC.log的文件结构如下： 2020-01-22 09:35:10,769 INFO CLMigrationUtility:159 DSC is initiated by xxxxx2020-01-22 09:35:10,828 INFO CLMigrationUtility:456 Successfully changed permission of files in D:\Migration\Gauss_Tools_18_Migration\code\migration\config2020-01-22 09:35:10,832 INFO PropertyLoader:90 Successfully loaded Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\application.properties2020-01-22 09:35:10,833 INFO ApplicationPropertyLoader:42 Application properties have been loaded Successfully2020-01-22 09:35:10,917 INFO MigrationValidatorService:549 Files in output directory has been overwritten as configured by xxxxx2020-01-22 09:35:10,920 INFO PropertyLoader:90 Successfully loaded Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\features-oracle.properties2020-01-22 09:35:10,921 INFO FeatureLoader:41 Features have been loaded Successfully2020-01-22 09:35:10,926 INFO MigrationService:80 DSC process start time : Wed Jan 22 09:35:10 GMT+05:30 20202020-01-22 09:35:10,933 INFO FileHandler:179 File is not supported. D:\Migration_Output\Source\ARRYTYPE.sql-2020-01-22 09:35:10,934 INFO FileHandler:179 File is not supported. D:\Migration_Output\Source\varray.sql-2020-01-22 09:35:12,816 INFO PropertyLoader:90 Successfully loaded Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\global-temp-tables.properties2020-01-22 09:35:12,830 INFO PropertyLoader:90 Successfully loaded Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\create-types-UDT.properties2020-01-22 09:35:12,834 INFO PropertyLoader:90 Successfully loaded Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\package-names-oracle.properties2020-01-22 09:35:12,849 INFO DBMigrationService:76 Number of Available Processors: 42020-01-22 09:35:12,850 INFO DBMigrationService:78 Configured simultaneous processes in the Tool : 32020-01-22 09:35:13,032 INFO MigrationProcessor:94 File name: D:\Migration_Output\Source\Input.sql is started2020-01-22 09:35:13,270 INFO FileHandler:606 guessencoding command output = Error: Unable to access jarfile D:\Migration\Gauss_Tools_18_Migration\code\migration\RE_migration\target\dsctool.jar , for file= D:\Migration_Output\Source\Input.sql2020-01-22 09:35:13,272 INFO FileHandler:625 couldn't get the encoding format, so using the default charset for D:\Migration_Output\Source\Input.sql2020-01-22 09:35:13,272 INFO FileHandler:310 File D:\Migration_Output\Source\Input.sql will be read with charset : UTF-82020-01-22 09:35:13,390 INFO FileHandler:668 D:\Migration_Output\target\output\Input.sql - File already exists/Failed to create target file2020-01-22 09:35:13,562 INFO FileHandler:606 guessencoding command output = Error: Unable to access jarfile D:\Migration\Gauss_Tools_18_Migration\code\migration\RE_migration\target\dsctool.jar , for file= D:\Migration_Output\Source\Input.sql2020-01-22 09:35:13,563 INFO FileHandler:625 couldn't get the encoding format, so using the default charset for D:\Migration_Output\Source\Input.sql2020-01-22 09:35:13,563 INFO FileHandler:675 File D:\Migration_Output\Source\Input.sql will be written with charset : UTF-82020-01-22 09:35:13,604 INFO MigrationProcessor:139 File name: D:\Migration_Output\Source\Input.sql is processed successfully2020-01-22 09:35:13,605 INFO MigrationService:147 Total number of files in Input folder : 32020-01-22 09:35:13,605 INFO MigrationService:148 Total number of queries : 122020-01-22 09:35:13,607 INFO PropertyLoader:164 Successfully updated Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\global-temp-tables.properties2020-01-22 09:35:13,630 INFO PropertyLoader:164 Successfully updated Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\create-types-UDT.properties2020-01-22 09:35:13,631 INFO PropertyLoader:164 Successfully updated Property file : D:\Migration\Gauss_Tools_18_Migration\code\migration\config\package-names-oracle.properties2020-01-22 09:35:13,632 INFO CLMigrationUtility:305 Log file : dsc.log and the file is present in the path : D:\Migration_Output\log2020-01-22 09:35:13,632 INFO CLMigrationUtility:312 DSC process end time : Wed Jan 22 09:35:13 GMT+05:30 20202020-01-22 09:35:13,632 INFO CLMigrationUtility:217 Total process time : 2842 seconds

成功读 在DSC读取文件之后，该文件将被记录日志以进行跟踪。在某些情况下，用户可通过这些日志获取文件执行状态的信息。该文件位于log文件夹中。日志文件包括日期、时间、文件名等详细信息。此日志文件的日志记录级别为INFO。 successRead.log的文件结构如下： 2017-07-21 14:13:00,461 INFO readlogger:213 /home/testmigration/Documentation/is not in.sql is read successfully.2017-07-21 14:13:00,957 INFO readlogger:213 /home/testmigration/Documentation/date quotes.sql is read successfully.2017-07-21 14:13:01,509 INFO readlogger:213 /home/testmigration/Documentation/column alias replace.sql is read successfully.2017-07-21 14:13:02,034 INFO readlogger:213 /home/testmigration/Documentation/sampleRownum.sql is read successfully.2017-07-21 14:13:02,578 INFO readlogger:213 /home/testmigration/Documentation/samp.sql is read successfully.2017-07-21 14:13:03,145 INFO readlogger:213 /home/testmigration/Documentation/2.6BuildInputs/testWithNodataSamples.sql is read successfully.

成功写 DSC读取、处理文件并将输出写入磁盘。这个过程被记录到成功写日志文件中。在某些情况下，用户可通过此文件了解哪些文件已处理成功。在重新运行的情况下，用户可以跳过这些文件运行剩余的文件。该文件位于log文件夹中。日志文件包括日期、时间、文件名等详细信息。此日志文件的日志记录级别为INFO。 successWrite.log的文件结构如下： 2017-07-21 14:13:00,616 INFO writelogger:595 /home/testmigration/Documentation/is not in.sql has written successfully.2017-07-21 14:13:01,055 INFO writelogger:595 /home/testmigration/Documentation/date quotes.sql has written successfully.2017-07-21 14:13:01,569 INFO writelogger:595 /home/testmigration/Documentation/column alias replace.sql has written successfully.2017-07-21 14:13:02,055 INFO writelogger:595 /home/testmigration/Documentation/sampleRownum.sql has written successfully.2017-07-21 14:13:02,597 INFO writelogger:595 /home/testmigration/Documentation/samp.sql has written successfully.2017-07-21 14:13:03,178 INFO writelogger:595 /home/testmigration/Documentation/testWithNodataSamples.sql has written successfully.

错误日志 DSC仅将迁移过程中发生的错误记录到DSCError.log文件中。该文件位于log文件夹中。DSCError.log文件包含这些错误的日期、时间，文件详细信息（如文件名），以及查询位置等信息。错误日志的记录级别为ERROR。 DSCError.log的文件结构如下： 2017-06-29 14:07:39,585 ERROR TeradataBulkHandler:172 Error occurred during processing of input in Bulk Migration. PreQueryValidation failed in not proper termination or exclude keyword. /home/testmigration/Documentation/Input/c005.sql for Query in position : 42017-06-29 14:07:39,962 ERROR TeradataBulkHandler:172 Error occurred during processing of input in Bulk Migration. PreQueryValidation failed in not proper termination or exclude keyword. /home/testmigration/Documentation/Input/c013.sql for Query in position : 112017-06-29 14:07:40,136 ERROR QueryConversionUtility:250 Query is not converted as it contains unsupported keyword: join select2017-06-29 14:07:40,136 ERROR TeradataBulkHandler:172 Error occurred during processing of input in Bulk Migration. PreQueryValidation failed in not proper termination or exclude keyword. /home/testmigration/Documentation/Input/sample.sql for Query in position : 12017-06-29 14:07:40,136 ERROR TeradataBulkHandler:172 Error occurred during processing of input in Bulk Migration. PreQueryValidation failed in not proper termination or exclude keyword. /home/testmigration/Documentation/Input/sample.sql for Query in position : 3

QUALIFY、CASE和ORDER BY 输入： select a.Cust_UID as Cust_UID /* UID */ ,a.Rtl_Usr_Id as Ini_CM /* */ ,a.Cntr_Aprv_Dt as Aprv_Pass_Tm /* */ ,a.Blg_Org_Id as CM_BRN_Nbr /* */ ,a.Mng_Chg_Typ_Cd as MNG_CHG_TYP_CD /* */ ,case when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106' then 'Y' ---- when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204') then 'Y' ---- , when a.Blg_Org_Id = b.BRN_Org_Id and a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109' then 'Y' ---- , else ''end as Pst_Flg /* */ ,a.Pst_Id as Pst_Id /* */ ,a.BBK_Org_Id as BBK_Org_Id /* */from VT_CUID_MND_NMN_CHG_INF as a /* VT_ */LEFT OUTER JOIN ${BRTL_VCOR}.BRTL_EM_USR_PST_REL_INF_S as b /* EM_ */ on a.Rtl_Usr_Id = b.Rtl_Usr_Id AND a.Blg_Org_Id = b.BRN_Org_Id AND a.Pst_Id = b.Pst_Id AND b.Sys_Id = 'privatebanking' AND b.pst_sts IN ('1','0','-2') /* 1 -2 0 */ AND b.DW_Snsh_Dt = cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') qualify row_number() over(partition by a.Cust_UID,a.bbk_org_id order by case when ( a.Mng_Chg_Typ_Cd= 'PMD' and a.Pst_Id in ('PB0101','PB0104')) or ( a.Mng_Chg_Typ_Cd= 'DEVPMD' and a.Pst_Id ='PB0106') then 0 when (a.Mng_Chg_Typ_Cd= 'DMD' and a.Pst_Id in ('PB0201','PB0204')) or (a.Mng_Chg_Typ_Cd= 'DEVDMD' and a.Pst_Id ='PB0109 ') then 0 else 1 end asc ) = 1 ; 输出：

TIMESTAMP FORMAT 输入： select a.Org_Id as Brn_Org_Id /* */ ,a.Evt_Id as Vst_Srl_Nbr /* */ ,a.EAC_Id as EAC_Id /* */ ,cast(cast(cast(Prt_Tm as timestamp format 'YYYY-MM-DDBHH:MI:SS' ) as varchar(19) )as timestamp(0)) as Tsk_Start_Tm /* */from ${BRTL_VCOR}.BRTL_BC_SLF_TMN_RTL_PRT_JNL as a /* BC_ */where a.DW_Dat_Dt = CAST('${v_Trx_Dt}' AS DATE FORMAT 'YYYY-MM-DD') ;

TIMESTAMP(n) FORMAT 输入： select cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_Snsh_Dt /* */ ,coalesce(a.CRE_DAT,cast('0001-01-01 00:00:01' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')) as Crt_Tm /* */ ,cast('${v_Trx_Dt}' as date format 'yyyy-mm-dd') as DW_ETL_Dt /* */ ,cast(current_date as date format 'yyyy-mm-dd') as DW_Upd_Dt /* */ ,current_time(0) as DW_Upd_Tm /* */ ,1 as DW_Job_Seq /* */from ${NDS_VIEW}.NLV65_MGM_GLDCUS_INF_NEW as a /* MGM */;-----------cast('0001-01-01 00:00:00' as timestamp(6) format 'yyyy-mm-ddbhh:mi:ssds(6)')TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.US' )----------cast('0001-01-01 00:00:00.000000' as timestamp(6))cast('0001-01-01 00:00:00.000000' as timestamp(6))----------CAST('0001-01-01 00:00:00.000000' AS TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)')TO_TIMESTAMP('0001-01-01 00:00:00.000000', 'yyyy-mm-dd HH24:MI:SS.US' )----------cast(LA02_USERLOG_M.LOGTIME as TIMESTAMP(6) FORMAT 'YYYY-MM-DD HH:MI:SS.S(0)' )TO_TIMESTAMP(LA02_USERLOG_M.LOGTIME, 'YYYY-MM-DD HH24:MI:SS' )----------cast('0001-01-01 00:00:00' as timestamp(3) format 'yyyy-mm-ddbhh:mi:ssds(3)')TO_TIMESTAMP('0001-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS.MS' )-----------CAST( '0001-01-01 00:00:01.000000' AS TIMESTAMP ( 6 ) format 'yyyy-mm-ddbhh:mi:ssds(6)' )TO_TIMESTAMP('0001-01-01 00:00:01.000000', 'yyyy-mm-dd HH24:MI:SS.US' )

TIME FORMAT 输入： COALESCE(t3.Crt_Tm , CAST('00:00:00' AS TIME FORMAT 'HH:MI:SS'))COALESCE(LI07_F3EABCTLP.CTLREGTIM,CAST('${NULL_TIME}' AS TIME FORMAT 'HH:MI:sS'))trim(cast(cast(a.Ases_Orig_Tm as time format'hhmiss') as varchar(10)))

.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"); 父主题： 表（可选参数、操作）