PoC Practice for Migrating from Teradata to GBase 8a

Published on 2023-02-21

As Teradata announced its withdrawal from the Chinese market, many users are considering migrating their existing databases. Database migration tests the supporting tools, methodology, and implementation experience of the database vendor. It requires not only a high conversion success rate and efficiency but also that the converted code is executable and the results are accurate. In recent years, GBase has completed over 100 customer migrations replacing foreign databases such as Teradata, accumulating extensive project experience. Its high conversion accuracy and precision have been well received by partners and vendors.

Taking a PoC test for a financial customer in 2022 as an example: The migration scope included DDL, views, and batch Perl scripts. The volume was relatively large for a PoC, including over 600 DDL statements and views, and more than 100 Perl scripts. The final conversion success rates met the evaluation criteria: DDL and view conversion achieved 99%, while Perl script conversion required at least 85%. Crucially, the scripts had to run immediately after conversion without any manual modification—what you convert is what you run, and it must run correctly. Database migration tool vendors understand what this implies; the approach of relying on massive manual workforce for migration is not applicable to and does not occur in Teradata-to-GBase 8a migration projects.

Database system migration involves the following core tasks. The migration from Teradata to GBase 8a follows this process in the order below:

  • Export data from Teradata as required. Parameters such as column delimiters, enclosing characters, distinction between empty strings and NULL values, and character sets must be planned in advance.

  • Export database objects from Teradata, including DDL, views, functions, etc.

  • Customize the GBase 8a migration tool to maximally cover the customer's specific SQL syntax patterns, thus maximizing conversion success rates. This customization can be completed within 3 to 5 days.

  • Create database objects on the GBase side.

  • Based on the SQL statements in the Perl scripts, use tools to recreate data distribution keys. The default distribution key is the original Teradata index.

  • Load the text files that have been exported from Teradata into GBase 8a, paying attention to preserving leading spaces, date storage formats, case insensitivity, etc.

  • Migrate Perl scripts to GBase 8a.

  • Compare the processing results between Teradata and GBase 8a after migration.

  • Optimize performance to the extreme after migration to GBase 8a.

  • Run Teradata and GBase 8a in parallel to ensure stability and data accuracy.

  • Formally cut over to GBase 8a to complete the database system migration.

In this PoC, due to the high compatibility between GBase 8a and Teradata, the migration scope was limited, as outlined in the following two sections. All migration tasks were performed automatically by tools and could run immediately upon completion.

DDL and View Migration

This part mainly includes handling table type keywords, field type handling, field constraints, character set handling, and other special DDL data content from Teradata. Details are as follows:

Table types: CREATE [MULTI]SET TABLE is directly changed to CREATE TABLE; CREATE GLOBAL TEMPORARY TABLE is changed to a GBase physical table; CREATE VOLATILE TABLE (session-level temporary table) is changed to a GBase TEMPORARY table.

Common data types: 99% are compatible, including CHAR, VARCHAR, DATETIME, DATE, TIME, TIMESTAMP, DECIMAL, INT, etc.

Character sets: Migrate to a unified character set, which can be GBK, UTF8, or other character sets supported by GBase, including GBK, UTF8, UTF8MB4, GB18030, etc.

Case insensitivity: Keywords such as UPPERCASE and NO CASESPECIFIC are removed, with table and field information recorded. In scripts, comparison operations on corresponding fields are processed with UPPER or LOWER functions.

Other table attributes: Must be removed, including FALLBACK, JOURNAL, CHECKSUM, MERGERBLOCKRATIO, FREESPACE, etc.

PRIMARY INDEX information: Converted into the distribution key of GBase 8a.

COMPRESS compression information: Removed directly and replaced with default medium table-level compression. Without replicas, it can achieve a 4.4x compression ratio.

TITLE comments: Changed to COMMENT keyword.

Script Migration

This primarily involves handling syntax and functions—removing what needs to be removed and replacing what needs to be replaced. Since GBase 8a is also a relational database and supports standard SQL, over 90% of SQL syntax can be used directly without modification. Details include:

Missing functions: NVL2 and RANK. The NVL2 function is replaced with a UDF, and RANK is changed to RANK OVER.

Functions to replace: The CHAR and CHARACTER functions correspond to GBase 8a's LENGTH function. ZEROIF and IFZERO are replaced with GBase's IFNULL and NULLIF respectively. The FORMAT function is widely used in TD; when migrating to 8a, it must be modified according to specific usage contexts.

Sort order and position of NULL values:

DESC: In TD, NULLs are placed last; in GBase 8a, NULLs are placed first (add NULLS LAST during migration).

ASC: In TD, NULLs are placed first; in GBase 8a, NULLs are placed last (add NULLS FIRST during migration).

Date formatting: Mainly handled using GBase's DATE_FORMAT, STR_TO_DATE, and DATE functions; all can be converted automatically through the migration tool.

Alias dependencies: Currently GBase supports aliases in projection columns, but aliases used in WHERE clauses and OLAP functions still need to be replaced by the conversion tool.

In summary, after automatically completing the migration with tools, over 95% of the workload is finished. Only a very small number of items that are not covered by the tool and have irregular syntax characteristics need to be manually modified to complete the overall migration. With extensive migration experience and highly accurate conversion tools, the GBase 8a product has become very mature in replacing Teradata databases. For customers urgently needing to replace Teradata, feel free to contact GBase 8a anytime—first-class service awaits you.

About GBase 8a

GBase 8a is a high-performance, domestically developed distributed logical data warehouse independently developed by General Data Technology Co., Ltd., designed for massive data query and analysis applications. It meets the growing data storage, management, and processing needs of data-intensive industries—including data querying, statistics, analysis, mining, and backup—and can serve as the underlying database for data warehouse systems, BI systems, and decision support systems. The product has been deployed at scale in industries with massive business data, such as finance, telecommunications, government and enterprises, energy, and security.