Teradata to Amazon Redshift

Business Opportunity

Ispirer representatives had the honour to negotiate business opportunities with Amazon Web Services. Our team’s experience across database and cloud technologies was the point at issue along with the matter how Ispirer expertise could be used to power a wide variety of AWS projects. This global platform offers a broad set of global cloud-based products including compute, storage, databases, management tools, IoT, security and enterprise applications.

We would not like to miss such a cooperation opportunity. The Ispirer team of ambitious professionals was eager about nonroutine projects for Amazon Redshift as well. So we had both internal and external triggers for the development of Teradata migration into Amazon Redshift. To successfully meet this challenge, we decided to adopt this target direction and expand the Ispirer Toolkit accordingly.

Challenge

We planned a series of migration projects from Teradata to Amazon Redshift. Ispirer Toolkit has been already supporting the migration into this direction for a long time. However, SQL procedures have been recently added to Redshift warehouse. We had a chance to extend conversion capabilities into Redshift.

The challenge involved conversion automation of Teradata SQL into Redshift SQL, and a batch/ad-hoc application (BTEQ) into Python 3.6 conversion. Ispirer team accepted the challenge and started with comprehensive analysis of requirements for the future system. The project involved data conversion, which was automated previously and expanded now.

Solution

The migration to Redshift SQL by Ispirer SQLWays Wizard has to be developed from scratch. Flexibility is one of the features of Ispirer Toolkit, which makes it a unique migration solution. A range of migration rules was introduced in order to achieve the highest automated rate. The conversion of the following Teradata database objects into Redshift was successfully automated: views, functions and operators, including built-in functions and LOB functions, tables, DML statements, and other SQL objects.

Main equivalents for Teradata statements were smoothly added. Ispirer migration experts developed several solutions, which allow to convert Teradata DML statements into Redshift automatically, notwithstanding considerable differences in SQL dialects. For example, operator MERGE does not exist in Redshift. Ispirer tools analyze the structure of the statement in Teradata and separate it into a set of different commands (Insert and Update) in Redshift. Aliases in UPDATE statement had no equivalents in Redshift. We handled it and replaced aliases with table name. Lots of unsupported constraints (e.g. CHECK) and indexes were commented. Some statements (ALTER statement, for example) might be migrated to the target environment after additional elaboration.

Ispirer Toolkit seamlessly converted procedures, functions, macros. By the way, macros were converted to procedures in Redshift. Redshift does not support some of the basic Teradata data types. Several time types, byte types, arrays, and others were not supported. Moreover, user defined types and domains had no equivalent in Redshift. Special customizations of the Toolkit in order to cope with unsupported types were needed.

Teradata business logic involved conditional statements and exceptions. This required some special developments, since Redshift allows only one exception in a procedure and only RAISE statements inside it. Ispirer Toolkit commented logic of the original exceptions and converted BL in its entirety to the target language. What refers to applications, BTEQ, FastLoad, MultiLoad, FastExport were converted to Python (Amazon Redshift). A total of 70 BTEQ commands equivalents were developed and added to automatic conversion.

Benefits Derived from Conversion

Ispirer Toolkit expansion was performed to convert the Teradata SQL and data. This “pilot project” was completed in 2 months. As a result, we managed to quickly customize the Toolkit for application migration to Python, and database objects migration into Redshift SQL. Now Ispirer Toolkit efficiently performs the conversion of any Teradata database to Redshift out of the box.

Meanwhile, the Ispirer team has gained vast experience in the field of Teradata to Redshift warehouse conversion. The solution developed as part of the non-production project might be applied to other similar tasks in a shorter term. You might see for yourself that the Ispirer Toolkit is extremely flexible and can be tailored for any migration direction.