华为云用户手册

命令示例 生成配置文件，不分离环境变量，指定生成配置文件地址。 python3 $GPHOME/script/DisasterFineGrained.py -t generate-config --user perfadm --primary-host-ip 192.168.250.152 --restore-host-ip 192.168.244.53 --meta-destination /home/CI/log/metadata --media-destination /home/CI/log/mediadata --config-file $HOME/config.ini --switchover-config-file $HOME/sw_config.ini

系统回显 [perfadm@ecs-env-2988 config]$ python3 $GPHOME/script/DisasterFineGrained.py -t create-publication --disaster-object-list-file $HOME/config/disaster_object_list.txt --config-file /home/mpp/mppcases_c10/cluster/gs_rch_DR-v6/test/backupRestore.ini[Start ESL disaster fine grained process]Stop backup loop.Stop crontab for backupStop crontab successfully.Stop backup successfully.Start parse disaster object file.parse disaster object file end, [disaster object num]: 1, [time]: 0.000s.Start filter disaster object.filter disaster object end, [db num]: 1, [schema num]: 0, [table num]: 0, [time]: 0.020s.Start transfer disaster object.transfer disaster object end, [db num]: 1, [schema num]: 0, [table num]: 0, [time]: 0.018s.Start alter publication.Start cancel all publication.cancel all publication end, [pub num]: 0, [time]: 0.044s.Start add publication.add publication end, [db num]: 1, [schema num]: 0, [table num]: 0, [time]: 0.030s.alter publication end, [time]: 0.074s.The publication file path: /home/perfadm/config/pub.list[Finished ESL disaster fine grained process]

使用指南 create-publication命令需在主集群的主节点上执行。当前备份过程结束之后，该命令会停止后续的周期性备份。 create-publication命令--disaster-object-list-file需传入全量容灾对象。 首次发布DB和schema时，会将DB和schema下所有enable_disaster_cstore非on，但支持设置enable_disaster_cstore="on"的表自动转为容灾表并同步，后续DB和schema下新增表，需要设置enable_disaster_cstore="on"才会自动同步。 发布的表需要是支持设置enable_disaster_cstore="on"的表才能同步。 发布失败的容灾对象会在命令执行完后写入$HOME/config/fail_disaster_object.txt。 默认生成的publication参数文件路径为$HOME/config/pub.list。 需手动创建容灾对象列表文件--disaster-object-list-file，文件格式如下： db_name //db级db_name.schema_name //schema级db_name.schema_name.table_name //table级

命令示例 创建发布。 python3 $GPHOME/script/DisasterFineGrained.py -t create-publication --disaster-object-list-file $HOME/config/disaster_object_list.txt --config-file /home/mpp/mppcases_c10/cluster/gs_rch_DR-v6/test/backupRestore.ini

参数说明 完整选项 缩写选项 数据类型 说明 取值类型 缺省值 应用实例 -t 不涉及 字符串 接口支持多种功能。指定该参数为create-publication，执行创建发布。 -t generate-config -t prepare -t create-publication -t alter-publication -t cancel-publication -t get-current-disaster 不涉及 -t create-publication --disaster-object-list-file 不涉及 字符串 容灾对象列表文件 不涉及 不涉及 --disaster-object-list-file /home/userA/disaster_object_list.txt --config-file 不涉及 字符串 双集群容灾配置文件存放路径。 说明： 该路径操作系统用户omm必须拥有读权限。 关于配置文件如何配置，请参见生成配置文件命令。 不涉及 不涉及 --config-file /home/userA/config.ini

概述 本节描述Teradata Perl文件迁移过程的详细信息。 请使用runDSC.sh或runDSC.bat命令并设置--application-lang=perl，将Perl文件中的Teradata BTEQ或SQL_LANG脚本迁移到兼容Perl文件的 GaussDB (DWS)中。迁移Perl文件后，可使用对比工具比较输入和输出文件进行验证。 Perl文件迁移流程如下： 完成前提条件中的步骤。 创建输入文件夹，并将待迁移Perl文件复制到该文件夹。例如：/migrationfiles/perlfiles 执行DSC迁移Perl脚本，并将db-bteq-tag-name设为BTEQ或db-tdsql-tag-name设为SQL_LANG。 DSC从Perl文件中提取BTEQ或SQL_LANG类型脚本。 BTEQ是标签名称，包含一组BTEQ脚本，可以通过perl-migration.properties文件中的db-bteq-tag-name参数来配置。 SQL_LANG也是标签名称，包含Teradata SQL语句，可以通过db-tdsql-tag-name参数来配置。 DSC通过调用Teradata SQL来迁移提取到的SQL脚本。有关Teradata SQL迁移的详细信息，请参见Teradata SQL迁移。 Perl文件嵌入迁移后脚本。 在指定的输出文件夹中创建迁移后的Perl文件。如果未指定输出文件夹，则工具会在输入文件夹内创建一个名为converted的输出文件夹，例如：/migrationfiles/perlfiles/converted。 包含SQL命令的Perl变量也可以通过migrate-variables参数迁移为SQL。 Perl v 5.10.0及以上提供兼容能力。

迁移后验证 Database Schema Convertor转换完含有SQL语句的源文件后，在目标GaussDB(DWS)上执行转换后的文件，并生成文件执行成功和失败的明细报告。 Database Schema Convertor完成迁移后，会调用迁移后验证脚本（通过配置项控制）。此验证脚本（配置详情见配置文件）会连接到目标 GaussDB数据库 并执行。 迁移后验证脚本会连接到目标GaussDB数据库（具体信息在配置文件中配置），并执行该脚本。 配置config文件夹下的application.properties 在GaussDB中执行迁移脚本的取值范围：true/false，默认值：false。 将executesqlingauss设置为true。 true: executesqlingauss将在GaussDB上执行迁移脚本。 配置config文件夹下的gaussdb.properties #目标数据库配置 #gauss database user with all privileges gaussdb-user= gaussdb-port= #Database name for GaussDBA gaussdb-name= #gaussdb ip gaussdb-ip= gsql客户端的依赖关系： 由于在GaussDB上执行脚本时需依赖gsql(GaussDB(DWS))，为保证Database Schema Convertor正常运行，需在安装了GaussDB实例或客户端(gsql)的节点上运行Database Schema Convertor，且进行验证的用户具有执行gsql命令的权限。 由于Gauss数据库实例/客户端只能安装在Linux操作系统中，因此只能用于Linux环境下的功能验证。 在远程GaussDB实例上执行gsql命令，建议在GaussDB实例的如下配置文件中增加客户端系统IP或主机名。 /home/gsmig/database/coordinator---pg_hba.conf 回显 GaussDB(DWS) ********************** Verification Started ****************************** Sql script execution on Gauss DB start time : Wed Jan 22 17:27:07 CS T 2020 Sql script execution on Gauss DB end time : Wed Jan 22 17:27:44 CST 2020 Summary of Verification : ================================================================================================================================== Statement | Total | Passed | Failed | Success Rate(%) ----------------------------------------------------------------------------------------------------------------------------------- COMMENT | 15 | 15 | 0 | 100 CREATE VIEW | 4 | 3 | 1 | 75 CREATE INDEX | 4 | 3 | 1 | 75 CREATE TABLE | 6 | 6 | 0 | 100 ALTER TABLE | 3 | 3 | 0 | 100 --------------------------------------------------------------------------------------------------------------------------------- Total | 32 | 30 | 2 | 93 Gauss Execution Log file : /home/gsmig/18Jan/DSC/DSC/log/gaussexecutionlog.log Gauss Execution Error Log file : /home/gsmig/18Jan/DSC/DSC/log/gaussexecutionerror.log Verification finished in 38 seconds ********************** Verification Completed ****************************

设置Java内存分配 DSC支持通过参数控制Java虚拟机（JVM）的内存分配量，并预设默认值。 在迁移操作期间，如果内存使用超过设置的值，DSC将提示“java.lang.OutOfMemoryError: GC overhead limit exceeded”错误消息并退出，此时用户可通过更改application.properties配置文件中的initialJVMMemory和maxJVMMemory 的值，以分配更多内存。 可用系统资源决定了内存分配量。 表2 JVM内存分配的控制参数 参数 说明 推荐取值 Xms 指定初始内存分配量，单位为MB。 该参数最小值为256 MB，最大值取决于可用的系统资源。 默认值：256 Xmx 指定内存分配量的上限，单位为MB。 该参数最小值为1024 MB，最大值取决于可用的系统资源。 默认值：1024 打开校验模块config文件夹下的gaussdb.properties文件，参照表3，配置参数以连接GaussDB(DWS)。 表3 gaussdb.properties文件内参数 参数名 描述 取值范围 默认值 样例 gaussdb-user GaussDB(DWS)数据库用户，拥有全部权限。 NA NA user1 gaussdb-port GaussDB(DWS)数据库端口号。 NA NA 8000 gaussdb-name GaussDB(DWS)的数据库名称。 NA NA gaussdb gaussdb-ip GaussDB(DWS)数据库IP地址。 NA NA 10.XX.XX.XX

设置application.properties application.properties文件中包括一系列应用配置参数，用于控制DSC在迁移数据库脚本时的行为，该文件中的参数为通用控制参数，适用于Teradata、MySQL迁移。 设置方法如下。 打开config文件夹中的application.properties文件。 根据实际需要修改application.properties文件中参数的值。 application.properties文件中的参数解释见表1。 参数值不区分大小写。 除了列出的参数外，用户不得更改任何参数值。 保存后退出。

dbc.sessioninfo 输入： SELECT username,clientsystemuserid ,clientipaddress,clientprogramname FROMdbc.sessioninfoWHEREsessionno = lv_mig_session ; 输出： select usename AS username, NULL::TEXT AS clientsystemuserid , client_addr AS clientipaddress, application_name AS clientprogramname from pg_catalog.pg_stat_activity WHERE pid = lv_mig_session;

dbc.sessioninfoV 输入： select username,clientsystemuserid,clientipaddress,clientprogramname from dbc.sessioninfoV where sessionno = 140167641814784; 输出： select usename AS username, NULL::TEXT AS clientsystemuserid , client_addr AS clientipaddress, application_name AS clientprogramname from pg_catalog.pg_stat_activity WHERE pid = 140167641814784;

支持指定部分列 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 91011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 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 91011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980 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; 父主题： 表迁移

AUTO_INCREMENT 在数据库应用中，我们经常需要用到自动递增的唯一编号来标识记录。在MySQL中，可通过数据列的auto_increment属性来自动生成。可在建表时可用“auto_increment=n”选项来指定一个自增的初始值。可用“alter table table_name auto_increment=n”命令来重设自增的起始值。GaussDB(DWS)不支持该参数，DSC迁移时会将设置该属性的字段迁移为SERIAL类型，并删除该关键字，转换如下表。 表1 数据类型转换 MySQL数字类型 MySQL INPUT GaussDB(DWS) OUTPUT TINYINT TINYINT SMALLSERIAL SMALLINT SMALLINT UNSIGNED SMALLINT SERIAL SMALLSERIAL DOUBLE/FLOAT DOUBLE/FLOAT BIGSERIAL INT/INTEGER INT/INTEGER UNSIGNED INT/INTEGER BIGSERIAL SERIAL BIGINT/SERIAL BIGINT/SERIAL BIGSERIAL 输入示例 12345 CREATE TABLE `public`.`job_instance` ( `job_sche_id` int(11) NOT NULL AUTO_INCREMENT, `task_name` varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (`job_sche_id`)) ENGINE=InnoDB AUTO_INCREMENT=219 DEFAULT CHARSET=utf8; 输出示例 123456789 CREATE TABLE "public"."job_instance"( "job_sche_id" SERIAL NOT NULL, "task_name" VARCHAR(400) NOT NULL DEFAULT '', PRIMARY KEY ("job_sche_id")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("job_sche_id"); 此外，GaussDB(DWS)也不支持基于AUTO_INCREMENT属性修改表定义信息。DSC迁移时会将其移除。 输入示例 12345678 CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` FLOAT(10,2), PRIMARY KEY(`dataType1`));ALTER TABLE runoob_alter_test AUTO_INCREMENT 100;ALTER TABLE runoob_alter_test AUTO_INCREMENT=100; 输出示例 123456789 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test"( "datatype1" SERIAL NOT NULL, "datatype2" REAL, PRIMARY KEY ("datatype1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); 父主题： 表（可选参数、操作）

DISTRIBUTE BY 在ADB中支持分布键，DSC迁移过程中会保留对应分布键。 输入示例 1234567 CREATE TABLE COPY_DI_DISTRIBUTOR_BUYER_CONTRIBUTION_RANKING_V2 (SHOP_ID VARCHAR ,DISTRIBUTOR_ID VARCHAR ,BUYER_ID VARCHAR ,DATE_TYPE BIGINT ,PRIMARY KEY (SHOP_ID,DISTRIBUTOR_ID,DATE_TYPE,BUYER_ID)) DISTRIBUTE BY HASH(SHOP_ID,DISTRIBUTOR_ID,DATE_TYPE); 输出示例 1 2 3 4 5 6 7 8 9101112 CREATE TABLE "public"."copy_di_distributor_buyer_contribution_ranking_v2" ( "shop_id" VARCHAR, "distributor_id" VARCHAR, "buyer_id" VARCHAR, "date_type" BIGINT, PRIMARY KEY ( "shop_id", "distributor_id", "date_type", "buyer_id" )) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH (SHOP_ID, DISTRIBUTOR_ID, DATE_TYPE); 父主题： 表（可选参数、操作）

类型对照 表1 日期和时间类型对照表 MySQL日期时间类型 MySQL INPUT GaussDB(DWS) OUTPUT DATETIME DATETIME[(fsp)] TIMESTAMP[(fsp)] WITHOUT TIME ZONE TIME TIME[(fsp)] TIME[(fsp)] WITHOUT TIME ZONE TIMESTAMP TIMESTAMP[(fsp)] TIMESTAMP[(fsp)] WITH TIME ZONE YEAR YEAR[(4)] SMALLINT(4) 该fsp值如果给出，则必须在0到6的范围内。值为0表示没有小数部分。如果省略，则默认精度为0。 输入示例DATETIME 1234567 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` DATETIME, `dataType_2` DATETIME(0), `dataType_3` DATETIME(6), `dataType_4` DATETIME DEFAULT NULL, `dataType_5` DATETIME DEFAULT '2018-10-12 15:27:33.999999'); 输出示例 1 2 3 4 5 6 7 8 91011 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" TIMESTAMP WITHOUT TIME ZONE, "datatype_2" TIMESTAMP(0) WITHOUT TIME ZONE, "datatype_3" TIMESTAMP(6) WITHOUT TIME ZONE, "datatype_4" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, "datatype_5" TIMESTAMP WITHOUT TIME ZONE DEFAULT '2018-10-12 15:27:33.999999') WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1"); 输入示例TIME 1 2 3 4 5 6 7 8 91011 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` TIME DEFAULT '20:58:10', `dataType_2` TIME(3) DEFAULT '20:58:10', `dataType_3` TIME(6) DEFAULT '20:58:10', `dataType_4` TIME(6) DEFAULT '2018-10-11 20:00:00', `dataType_5` TIME(6) DEFAULT '20:58:10.01234', `dataType_6` TIME(6) DEFAULT '2018-10-11 20:00:00.01234', `dataType_7` TIME DEFAULT NULL, `dataType_8` TIME(6) DEFAULT NULL, PRIMARY KEY (dataType_1)); 输出示例 1 2 3 4 5 6 7 8 9101112131415 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" TIME WITHOUT TIME ZONE DEFAULT '20:58:10', "datatype_2" TIME(3) WITHOUT TIME ZONE DEFAULT '20:58:10', "datatype_3" TIME(6) WITHOUT TIME ZONE DEFAULT '20:58:10', "datatype_4" TIME(6) WITHOUT TIME ZONE DEFAULT '2018-10-11 20:00:00', "datatype_5" TIME(6) WITHOUT TIME ZONE DEFAULT '20:58:10.01234', "datatype_6" TIME(6) WITHOUT TIME ZONE DEFAULT '2018-10-11 20:00:00.01234', "datatype_7" TIME WITHOUT TIME ZONE DEFAULT NULL, "datatype_8" TIME(6) WITHOUT TIME ZONE DEFAULT NULL, PRIMARY KEY ("datatype_1")) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1"); 输入示例TIMESTAMP 123456789 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` TIMESTAMP, `dateType_4` TIMESTAMP DEFAULT '2018-10-12 15:27:33', `dateType_5` TIMESTAMP DEFAULT '2018-10-12 15:27:33.999999', `dateType_6` TIMESTAMP DEFAULT '2018-10-12 15:27:33', `dateType_7` TIMESTAMP DEFAULT '2018-10-12 15:27:33', `dataType_8` TIMESTAMP(0) DEFAULT '2018-10-12 15:27:33', `dateType_9` TIMESTAMP(6) DEFAULT '2018-10-12 15:27:33'); 输出示例 1 2 3 4 5 6 7 8 910111213 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" TIMESTAMP WITH TIME ZONE, "datetype_4" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33', "datetype_5" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33.999999', "datetype_6" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33', "datetype_7" TIMESTAMP WITH TIME ZONE DEFAULT '2018-10-12 15:27:33', "datatype_8" TIMESTAMP(0) WITH TIME ZONE DEFAULT '2018-10-12 15:27:33', "datetype_9" TIMESTAMP(6) WITH TIME ZONE DEFAULT '2018-10-12 15:27:33') WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1"); 输入示例YEAR 123456 CREATE TABLE IF NOT EXISTS `runoob_dataType_test`( `dataType_1` YEAR, `dataType_2` YEAR(4), `dataType_3` YEAR DEFAULT '2018', `dataType_4` TIME DEFAULT NULL); 输出示例 1 2 3 4 5 6 7 8 910 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test"( "datatype_1" SMALLINT, "datatype_2" SMALLINT, "datatype_3" VARCHAR(4) DEFAULT '2018', "datatype_4" TIME WITHOUT TIME ZONE DEFAULT NULL ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype_1");

FOREIGN_KEY_CHECKS MySQL中的外键约束，GaussDB(DWS)不支持该属性修改表定义信息，DSC迁移时会将该属性删除。 输入示例 123456 SET foreign_key_checks = 0;CREATE TABLE mall_order_dc ( id bigint NOT NULL AUTO_INCREMENT, order_id varchar(50) NOT NULL, key order_id(order_id)); 输出示例 12345 CREATE TABLE "public"."mall_order_dc" ( "id" BIGSERIAL NOT NULL, "order_id" VARCHAR(200) NOT NULL) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("id");CREATE INDEX "order_id" ON "public"."mall_order_dc" USING BTREE ("order_id"); 父主题： 表（可选参数、操作）

PARTITION BY LIST 输入示例 1 2 3 4 5 6 7 8 91011121314 CREATE TABLE IF NOT EXISTS `runoob_tbl_part_test`( `runoob_id` INT NOT NULL, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR(30), PRIMARY KEY (`runoob_id`) )ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY LIST (runoob_id)( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24) );ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p5 VALUES IN(30, 40, 50, 60, 70, 80)); 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021 CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_part_test" ( "runoob_id" INTEGER NOT NULL, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR(120), PRIMARY KEY ("runoob_id")) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id") PARTITION BY LIST (runoob_id) ( PARTITION r0 VALUES (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES (4, 8, 12, 16, 20, 24));ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p5 VALUES (30, 40, 50, 60, 70, 80);

PARTITION BY RANGE 输入示例 1 2 3 4 5 6 7 8 9101112131415161718 CREATE TABLE IF NOT EXISTS `runoob_tbl_part_test`( `runoob_id` INT NOT NULL, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR(30))ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (`runoob_id`)( PARTITION p0 VALUES LESS THAN(100), PARTITION p1 VALUES LESS THAN(200), PARTITION p2 VALUES LESS THAN(300), PARTITION p3 VALUES LESS THAN(400), PARTITION p4 VALUES LESS THAN(500), PARTITION p5 VALUES LESS THAN (MAXVALUE));ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p6 VALUES LESS THAN (600));ALTER TABLE `runoob_tbl_part_test` ADD PARTITION (PARTITION p7 VALUES LESS THAN (700),PARTITION p8 VALUES LESS THAN (800)); 输出示例 1 2 3 4 5 6 7 8 9101112131415161718192021222324252627 CREATE TABLE IF NOT EXISTS "public"."runoob_tbl_part_test" ( "runoob_id" INTEGER NOT NULL, "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") PARTITION BY RANGE ("runoob_id") ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN (400), PARTITION p4 VALUES LESS THAN (500), PARTITION p5 VALUES LESS THAN (MAXVALUE));ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p6 VALUES LESS THAN (600);ALTER TABLE "public"."runoob_tbl_part_test" ADD PARTITION p7 VALUES LESS THAN (700), ADD PARTITION p8 VALUES LESS THAN (800);

转换数据类型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;

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

INT 输入：INT转换 123456 SELECT CAST( col1 AS INT ) ( FORMAT '9(5)' ) FROM table1; 输出 1234 SELECT LPAD( CAST( col1 AS INT ) ,5 ,'0' ) FROM table1; 输入：INT转换 123456 SELECT CAST( col1 AS INT ) ( FORMAT '999999' ) FROM table1; 输出 1234 SELECT LPAD( CAST( col1 AS INT ) ,6 ,'0' ) FROM table1; 输入：INT转换 1234 SELECT CAST( expression1 AS INT FORMAT '9(10)' ) FROM table1; 输出 1234 SELECT LPAD( CAST( expression1 AS INT ) ,10 ,'0' ) FROM table1; 输入：INT转换 1234 SELECT CAST( expression1 AS INT FORMAT '9999' ) FROM table1; 输出 1234 SELECT LPAD( CAST( expression1 AS INT ) ,4 ,'0' ) FROM table1;

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 ;

表达式 输入：对表达式进行类型转换和格式化 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;

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;

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 STATISTICS PRIMARY INDEX (LOGTYPE, 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"; 父主题： 表（可选参数、操作）

以#开头的列名 输入 输出 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 AC CES S 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 ) ;