SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillshuawei-cloud-dws-migration-teradata-to-dws

huawei-cloud-dws-migration-teradata-to-dws

Data & analytics

View repository

Updated 10-15 22:14

About this skill

Teradata to DWS Migration Skill

Overview

This skill is used to migrate a Teradata database to Huawei Cloud DWS (Data Warehouse Service). The migration process strictly follows these core rules:

  1. No write operations are allowed on the source-side Teradata — all access to the source side is read-only
  2. Follow the Teradata to DWS data type mapping rules — refer to https://support.huaweicloud.com/migration-dws/dws_15_0131.html
  3. Migration is divided into two major steps — migrate table structure (DDL) first, then migrate table data (DML)
  4. Duplicate tables require user confirmation — when tables that already exist in DWS are found, list them and ask the user for confirmation before dropping and recreating
  5. OBS temporary directory — data is exported to OBS instead of local disk to avoid disk capacity issues
  6. COPY command bulk import — use the COPY command instead of row-by-row INSERT, improving performance by 10-100x and supporting TB-level data

Migration Process

Step 0   迁移前预检     pre_migration_check.py  网络/源端/目标端/业务影响/时间预估（全部只读）
  ↓
Step 1   表结构 DDL    migrate_schema.py       只读提取 DDL → 数据类型映射 → PPI 分区转换(ppi_converter)
                                                  → 生成 DWS DDL → 重复表确认 → 建表
  ↓
Step 2   表数据 DML    migrate_data.py         CSV/OBS 临时目录导出 → 实时进度 → COPY/OBS 外表导入 → 校验
  ↓
Step 2.5 视图/宏/过程  migrate_views_procs.py  提取(SHOW VIEW/MACRO/PROCEDURE) → SQL 语法转换 → DWS 重建
  ↓
Step 3   数据校验       validate_migration.py   表数/行数/列/聚合/抽样/TIMESTAMP 精确值比对
  ↓
Step 3.5 功能性校验     validate_functional.py  宏/存储过程/视图调用与结果比对

Prerequisites

Environment Requirements

  • Python 3.8+
  • Teradata ODBC driver or teradatasql Python package
  • DWS gsql client or psycopg2 Python package
  • (Optional) DSC tool — used for SQL script migration, requires JDK 1.8+
  • (Optional) obsutil — used to import data via OBS transfer

Install Dependencies

pip3 install teradatasql psycopg2-binary

Configuration Files

Before use, the following configuration files need to be prepared:

  1. Teradata connection configuration — refer to config/teradata_config_template.ini
  2. DWS connection configuration — refer to config/dws_config_template.ini
  3. Migration configuration file(used for view/macro/stored procedure migration and functional verification) — refer to template config/migration_config_template.yaml, copy as config/migration_config.yaml and fill in the Teradata/DWS connection information (including source_database, target schema and other fields); for format details, see "Migration Configuration File (migration_config.yaml)" below

Migration Configuration File (migration_config.yaml)

migrate_views_procs.py(Step 2.5) and validate_functional.py(Step 3.5) read Teradata/DWS connection information via --config config/migration_config.yaml . This file is not included in the skill package and needs to be created from the template:

cp config/migration_config_template.yaml config/migration_config.yaml
# 编辑 config/migration_config.yaml 填写实际连接信息

Format Description(YAML, see the comments in the template for field descriptions):

teradata:
  host: <teradata_host>          # Teradata 主机地址
  port: 1025                     # Teradata 端口（默认 1025）
  user: <td_user>                # Teradata 只读账号
  password: <td_password>        # 或通过环境变量 TD_PASSWORD 注入
  database: DBC                  # 连接数据库（默认 DBC）
  logmech: TD2                   # 登录机制（默认 TD2）
  source_database: <source_db>   # 待迁移的源数据库名（必填）
dws:
  host: <dws_host>               # DWS 集群连接端点
  port: 25308                    # DWS 端口（默认 25308）
  database: <dws_database>       # DWS 数据库名
  user: <dws_user>               # DWS 账号
  password: <dws_password>       # 或通过环境变量 DWS_PASSWORD 注入
  schema: public                 # 目标 Schema（默认 public）
  target_schema: public          # target_schema 优先于 schema 用于目标表

Password Injection：migrate_views_procs.py requires that the password field must be filled in the configuration file;validate_functional.py supports leaving the password blank and falling back to reading the TD_PASSWORD / DWS_PASSWORD environment variable. For security, the password can preferably be injected via environment variable (validate_functional.py scenario) to avoid storing it in plaintext on disk.

Usage

Pre-migration Precheck (recommended to run first)

python3 scripts/pre_migration_check.py \
    --td-config config/teradata_config.ini \
    --dws-config config/dws_config.ini \
    --schema <schema_name> \
    --output-dir ./output

The precheck includes:

  • Network connectivity: port reachability of the source-side Teradata and target-side DWS
  • Source-side Teradata: connection test, number of active sessions, running queries, CPU usage, data volume estimation
  • Target-side DWS: connection test, cluster version, number of connections, storage space, schema existence, node status
  • Business impact assessment: Assess the migration risk level (low/medium/high) based on the source-side load, and provide recommendations
  • Migration time estimation: Estimate migration duration for the three methods direct/csv/obs

Save the pre-check report as a JSON file {schema}_pre_migration_check.json。

Full migration (pre-check + table schema + table data + validation)

# Step 0: 预检
python3 scripts/pre_migration_check.py --td-config config/teradata_config.ini --dws-config config/dws_config.ini --schema <schema_name>

# Step 1: 迁移表结构
python3 scripts/migrate_schema.py --td-config config/teradata_config.ini --dws-config config/dws_config.ini --schema <schema_name>

# Step 2: 迁移表数据
python3 scripts/migrate_data.py --td-config config/teradata_config.ini --dws-config config/dws_config.ini --schema <schema_name> \
    --truncate-before-import --timezone-adjust --source-tz UTC-4 --target-tz UTC+8

# Step 2.5: 迁移视图/宏/存储过程
cp config/migration_config_template.yaml config/migration_config.yaml  # 首次使用：从模板创建迁移配置
python3 scripts/migrate_views_procs.py --config config/migration_config.yaml --types views,macros,procedures

# Step 3: 校验验证（含 TIMESTAMP 精确值比对）
python3 scripts/validate_migration.py --td-config config/teradata_config.ini --dws-config config/dws_config.ini --schema <schema_name> \
    --checks table_count row_count columns aggregate sampling timestamp_check \
    --timezone-adjust --source-tz UTC-4 --target-tz UTC+8

# Step 3.5: 功能性校验（宏/存储过程/视图）
python3 scripts/validate_functional.py --config config/migration_config.yaml \
    --output functional_validation_report.json

Functional validation (Step 3.5)

After data validation passes, functionally verify the migrated macros, stored procedures, and views:

Connection information can be specified directly via command-line parameters (passwords are always injected via environment variables, never written on the command line):

export TD_PASSWORD=<teradata-密码>
export DWS_PASSWORD=<dws-密码>

python3 scripts/validate_functional.py \
    --td-host <td_host> --td-user <td_user> \
    --td-database <td_database> \
    --dws-host <dws_host> --dws-port <dws_port> \
    --dws-db <dws_db> --dws-user <dws_user> \
    --dws-schema public \
    --output functional_validation_report.json

Validation content：

Object type TD-side invocation method DWS-side invocation method Comparison method
Macro EXEC macro_name() SELECT * FROM func() AS (col_def) Row count + sorted comparison of the first 20 rows of data
Stored procedure CALL proc_name() CALL proc_name()(transaction rollback) Row count comparison / executes without errors
View SELECT * FROM view SELECT * FROM view Row count + sorted comparison of the first 20 rows of data

Key design points：

  • Automatic inference of macro column types：DWS SETOF record function calls require a column definition list; the script uses pg_attribute to automatically infer the return column types
  • Write operation safety: write-operation stored procedures are executed on the DWS side within a transaction and then automatically ROLLBACK, without polluting the data
  • Support for macros with parameters: via --macro-params JSON file to specify test parameter values
  • Handling of non-deterministic differences: for non-determinism issues such as ROW_NUMBER() tie-breaking, focus on consistency of business-critical fields during comparison

Validation report: in JSON format, containing the validation status, row count, match result, and error information for each object

Migrate table schema only

python3 scripts/migrate_schema.py --td-config config/teradata_config.ini --dws-config config/dws_config.ini --schema <schema_name> --output-dir ./output

Duplicate table handling: before executing DDL, the script checks for tables that already exist in DWS. If duplicate tables are found, it will list all duplicate tables and their row counts, and wait for the user to enter yes for confirmation before dropping and recreating them. Entering no skips these tables.

Migrate table data only

Method 1: CSV relay (default, suitable for medium tables)

python3 scripts/migrate_data.py \
    --td-config config/teradata_config.ini \
    --dws-config config/dws_config.ini \
    --schema <schema_name> \
    --database <td_database> \
    --method csv \
    --truncate-before-import \
    --timezone-adjust --source-tz UTC-4 --target-tz UTC+8

Use the COPY command for batch import, which is 10-100 times faster than row-by-row INSERT.

New options：

  • --truncate-before-import: automatically clears the target table data before import, avoiding data duplication caused by repeated imports
  • --timezone-adjust: enables TIMESTAMP time zone conversion (automatically offsets TIMESTAMP column values)
  • --source-tz / --target-tz: specifies the source/target time zone (default UTC-4 / UTC+8)

Method 2: OBS foreign table parallel import (suitable for large tables/TB-level)

python3 scripts/migrate_data.py \
    --td-config config/teradata_config.ini \
    --dws-config config/dws_config.ini \
    --schema <schema_name> \
    --database <td_database> \
    --method obs \
    --obs-config config/obs_config.ini \
    --obs-temp-dir migration/temp/<schema_name>

OBS temporary directory: CSV data is exported to OBS instead of local disk, avoiding disk capacity issues. The script will display the OBS temporary path:

☁️ OBS 临时目录: obs://<bucket>/migration/temp/<schema_name>
   CSV 数据将导出到此 OBS 路径，避免本地磁盘容量问题

Parallel import: parallel import via DWS foreign tables (Foreign Table) + INSERT INTO ... SELECT, with configurable parallelism and shard size, suitable for TB-level data migration.

Method 3: Direct import (suitable for small tables)

python3 scripts/migrate_data.py \
    --td-config config/teradata_config.ini \
    --dws-config config/dws_config.ini \
    --schema <schema_name> \
    --database <td_database> \
    --method direct

Real-time progress display

During data migration, the shell window displays the migration progress of each table in real time:

┌─ [3/19] 16% ████░░░░░░░░░░░░░░░░░░░░░░░░
│  表: customer_orders
│  已用: 5.2min  预估剩余: 26.3min
└─ 开始迁移...
  📤 导出进度: ████░░░░░░ 40% (56,000/140,000 行, 12,000 rows/s)
  📤 导出完成: 140,000 行, 45.2MB, 11.7s, 12,000 rows/s
  📋 copy: COPY 导入 140,000 行...
  📥 COPY 导入完成: 140,000 行, 3.2s, 43,750 rows/s
  ✅ [3/19] 16% ████░░░░░░ customer_orders: 140,000 行, 15.1s

Migrate views/macros/stored procedures (Step 2.5)

After table schema and data migration is complete, you can continue to migrate views, macros, and stored procedures:

cp config/migration_config_template.yaml config/migration_config.yaml  # 首次使用：从模板创建迁移配置
python3 scripts/migrate_views_procs.py \
    --config config/migration_config.yaml \
    --types views,macros,procedures

Supported conversions：

Object type Teradata DWS Key conversion
View CREATE VIEW CREATE OR REPLACE VIEW ZEROIFNULL→COALESCE, QUALIFY→CTE+ROW_NUMBER, GROUP BY position→column name
Macro REPLACE MACRO CREATE OR REPLACE FUNCTION Parameter reference :param→param, returns SETOF record
Stored procedure REPLACE PROCEDURE (SPL) CREATE OR REPLACE PROCEDURE (PL/pgSQL) SET→:=, WHILE..DO→WHILE..LOOP, LEAVE→EXIT

Common parameters：

  • --types: Specify migration type (views,macros,procedures, default all)
  • --views/--macros/--procedures: Specify object names (comma-separated)
  • --output-dir: Output DDL to a file without executing
  • --dry-run: Convert only without executing (do not connect to DWS)

Automatic SQL syntax conversion：

  • ZEROIFNULL(expr) → COALESCE(expr, 0)
  • NULLIFZERO(expr) → NULLIF(expr, 0)
  • QUALIFY ROW_NUMBER() OVER(...) = 1 → CTE + ROW_NUMBER() + WHERE clause
  • GROUP BY 1, 2 → GROUP BY 列名(position reference converted to column name)
  • EXTRACT(YEAR FROM date_col) → EXTRACT(YEAR FROM date_col)(compatible)
  • Teradata database reference db.table → schema.table

PPI partitioned table conversion (Step 1.3.1)

ppi_converter.py The module automatically converts Teradata PPI (Partitioned Primary Index) partition syntax to DWS partition syntax:

Supported conversion modes：

Teradata PPI mode DWS partition syntax Description
Single-level RANGE_N with EACH INTERVAL '1' DAY PARTITION BY RANGE (by day) Daily partition
Single-level RANGE_N with EACH INTERVAL 'N' MONTH PARTITION BY RANGE (by month) Monthly partition
RANGE_N with NO RANGE / UNKNOWN DWS default partition Automatically add default partition
CASE_N PARTITION BY LIST List partition
Multi-level RANGE_N + CASE_N Single-level PARTITION BY RANGE (downgraded) DWS does not support SUBPARTITION; downgraded to single-level RANGE, with the original CASE_N information retained in comments

Downgrade description: DWS (GaussDB) does not support SUBPARTITION syntax. When a Teradata multi-level PPI (RANGE_N + CASE_N) is encountered, it is automatically downgraded to a single-level RANGE partition, and the original CASE_N subpartition information is retained in the DDL as comments for reference.

Use the DSC tool to migrate SQL scripts

Offline SQL script migration can use the DSC tool (runDSC.sh -S teradata -I <input> -O <output> -L <log>), which can convert without connecting to a database; the converted script is executed on the DWS side using gsql (the password is injected via the PGPASSWORD environment variable). For detailed steps, see references/migration-guide.md。

File structure

huawei-cloud-dws-migration-teradata-to-dws/
├── SKILL.md                          # 本文档
├── scripts/
│   ├── teradata_reader.py            # Teradata 只读连接器（禁止写操作）
│   ├── dws_writer.py                 # DWS 连接和执行器
│   ├── datatype_mapping.py           # 数据类型映射模块
│   ├── pre_migration_check.py        # 迁移前预检脚本（Step 0）
│   ├── migrate_schema.py             # 表结构迁移脚本（Step 1）
│   ├── migrate_data.py               # 表数据迁移脚本（Step 2）
│   ├── migrate_views_procs.py        # 视图/宏/存储过程迁移脚本（Step 2.5，入口）
│   ├── sql_converter.py              # SQL 语法转换器（视图/宏/过程共用）
│   ├── macro_converter.py            # 宏 (MACRO→FUNCTION) 转换器
│   ├── procedure_converter.py        # 存储过程 (SPL→PL/pgSQL) 转换器
│   ├── ppi_converter.py              # PPI 分区表转换器（Teradata PPI → DWS 分区）
│   ├── validate_migration.py         # 数据校验脚本（Step 3）
│   └── validate_functional.py        # 功能性校验脚本（Step 3.5）
├── config/
│   ├── teradata_config_template.ini  # Teradata 连接配置模板
│   ├── dws_config_template.ini       # DWS 连接配置模板
│   ├── obs_config_template.ini       # OBS 连接配置模板（method=obs 时使用）
│   ├── migration_config_template.yaml # 视图/宏/过程迁移与功能性校验连接配置模板
│   └── features-teradata.properties  # DSC Teradata 配置模板
├── references/
│   ├── datatype-mapping.md           # 数据类型映射详细参考
│   ├── migration-guide.md            # 迁移指南
│   ├── cli-installation-guide.md     # KooCLI 安装与凭证配置
│   ├── iam-policies.md               # 最小权限策略
│   ├── verification-method.md        # 迁移验证方法
│   ├── acceptance-criteria.md        # 验收标准
│   └── forbidden-operations.md       # 高危操作禁止列表
└── examples/
    └── sample_migration.sh           # 示例迁移脚本

Detailed explanation of core rules

Rule 1: Prohibit write operations on the source side

teradata_reader.py The module ensures through the following mechanisms that no write operations are performed on the source Teradata:

  • SQL statement whitelist filtering: only read-only statements such as SELECT, SHOW, HELP, SELECT are allowed
  • Prohibit all data change/schema change operations on the source side (write operations such as insert, delete, update, truncate, create table, drop table, alter table structure, transaction control, etc.)
  • Connections are established in read-only mode
  • All SQL undergoes security checks before execution

For the complete list of prohibited high-risk operations, see references/forbidden-operations.md, covering:

  • Prohibited operations on the source side for DDL/DML/transactions/permissions/sessions/database management
  • Prohibited operations on the target side for destructive DDL/data operations/permission abuse
  • Prohibited operations at the system environment level (files/network/processes)
  • Prohibited operations for data security/privacy
  • Prohibited operations for migration logic
  • Prohibited operations for the AI decision engine
  • Read-only whitelist and security validation implementation specification

Rule 2: Data type mapping

Data type mapping strictly follows the official Huawei Cloud documentation: https://support.huaweicloud.com/migration-dws/dws_15_0131.html

For the complete mapping table, see references/datatype-mapping.md。

Rule 3: Table structure before table data

Migration is executed strictly in order:

  1. Table structure migration (migrate_schema.py): Extract Teradata DDL → data type conversion → create tables in DWS
  2. Table data migration (migrate_data.py): Export data from Teradata → import into DWS
  3. Validation (validate_migration.py): Compare data consistency between the source and target

Rule 4: Pre-check before migration

Before executing the migration,it is strongly recommendedto first run the pre-check script pre_migration_check.py, in order to:

  • Confirm network connectivity between the source and target
  • Assess the current load on the source Teradata to ensure the migration will not affect source business
  • Check whether target DWS resources such as storage space and number of connections are sufficient
  • Estimate migration time and risk level

Pre-check scriptexecutes only read-only queriesand does not cause any side effects on the source or target.

Rule 5: Migration progress display

migrate_schema.py and migrate_data.py both have built-in real-time progress display:

  • Overall progress bar：[3/19] 16% ████░░░░░░ shows the current table sequence number and percentage
  • Estimated remaining time: dynamically estimated based on the average time of completed tables
  • Per-table time statistics: after each table migration is completed, display the number of rows, time taken, and throughput
  • Sub-step progress: export, upload, and import each sub-step independently display progress and speed
  • Migration summary report: after all tables are completed, output a summary table including each table's status, row count, and time taken

Rule 6: Duplicate table detection and user confirmation

migrate_schema.py automatically detects tables that already exist in DWS before executing DDL:

  1. Detection phase: Compare the source table list with existing DWS tables to find duplicate tables
  2. Confirmation phase: List all duplicate tables and their current row counts, and wait for user input
    • Input yes: Drop duplicate tables and recreate them
    • Input no: Skip duplicate tables and retain the existing structure
    • Input 全部: Confirm dropping and recreating all duplicate tables
    • Input 跳过: Skip all duplicate tables and migrate only new tables
  3. --force mode: Skip interactive confirmation and automatically drop and recreate duplicate tables (suitable for script/CI environments)
  4. Non-interactive environment handling: When it is detected that stdin is not a terminal (such as when run via script/pipe) and there are duplicate tables but --force is not specified, the script will print a clear error message and exit with error code 1, avoiding silently skipping DDL execution
  5. Safety guarantee: No table will be dropped without user confirmation

Rule 7: OBS temporary directory

migrate_data.py supports exporting CSV data to an OBS temporary directory instead of the local disk:

  • Avoid disk capacity issues: TB-level data is no longer limited by local disk space
  • Visible path: Display the OBS temporary directory path when the script starts
  • Automatic cleanup: Automatically clean up OBS temporary files after migration is complete
  • Flexible configuration: Via --obs-temp-dir parameter to specify the OBS path

Rule 8: COPY command batch import

dws_writer.py Provides three data import methods, with increasing performance:

Method Applicable scenario Performance Description
Row-by-row INSERT Small tables (<10,000 rows) 1x Baseline
COPY command Medium tables (10,000-100 million rows) 10-100x CSV file batch import
OBS foreign table parallel Large tables (>100 million rows/TB-level) 50-500x Foreign table + parallel INSERT SELECT

OBS foreign table parallel importBy creating a DWS Foreign Table to read CSV files on OBS, then executing INSERT INTO target SELECT * FROM foreign_table parallel import, it is the optimal solution for TB-level data migration.

KooCLI command format standard

The core migration toolchain is a Python script, and data migration does not depend on KooCLI; the following auxiliary scenarios use Huawei Cloud KooCLI (hcloud）：

  1. Query the DWS cluster list and connection information (confirm cluster status, version, connection endpoint)
  2. View the OBS bucket list and objects (--method obs confirm bucket availability before migration)

Command format: region parameters are uniformly specified via --cli-region .

# 查询 DWS 集群列表
hcloud DWS ListClusters --cli-region=cn-south-1 --project_id=<project_id>

# 查询 OBS 桶列表（hcloud obs 为 obsutil 透传命令，使用 obsutil 风格语法）
hcloud obs ls

For KooCLI installation, credential configuration, and network troubleshooting, see references/cli-installation-guide.md。

Reference documentation

  • List of prohibited high-risk operations：references/forbidden-operations.md
  • IAM permission requirements：references/iam-policies.md
  • KooCLI installation and command format：references/cli-installation-guide.md
  • Migration verification methods：references/verification-method.md
  • Acceptance criteria：references/acceptance-criteria.md
  • OBS configuration file template：references/obs-config-template.md
  • Complete change history：references/change-log.md
  • OBS reference documentation:obs://cloud-bigdata/teradata_to_dws/dws_doc/
  • Data type mapping:https://support.huaweicloud.com/migration-dws/dws_15_0131.html
  • DWS Data Migration and Synchronization Guide
  • DWS Tool Guide (DSC, DataCheck)
  • DWS SQL Syntax Reference

Precautions

  1. Network connectivity: Ensure that the execution environment can connect to both Teradata and DWS
  2. Permission requirements: Teradata requires SELECT permission; DWS requires table creation and data write permissions
  3. Data volume assessment: For large table data migration, it is recommended to use OBS parallel import or the GDS tool
  4. Character encoding: Ensure that the source and destination character encodings are consistent (UTF-8 is recommended)
  5. Compatibility: In DWS Teradata-compatible mode, foreign tables do not support the DATE type
  6. DSC tool: DSC is an offline tool and does not need to connect to the database, so it can migrate SQL scripts with zero downtime
  7. OBS configuration: When using --method obs , you need to prepare an OBS configuration file (including bucket, AK/SK, endpoint), and ensure that obsutil is configured
  8. Duplicate table handling: When migrating table structures, if an existing table is encountered, the script will pause and wait for user confirmation; in a non-interactive environment (script/pipeline), the --force parameter must be used, otherwise it will exit with an error code
  9. TB-level data: For TB-level data migration, it is recommended to use --method obs + OBS foreign table parallel import to avoid local disk bottlenecks

OBS profile template

When using --method obs , you need to prepare an OBS profile config/obs_config.ini. For the complete configuration template and parameter descriptions, see references/obs-config-template.md。

Changelog

For the complete change history, see references/change-log.md。