Table Definition

As you probably have thousands of tables in your current Teradata data warehouse, you should automate the task of converting the Table Definitions (DDL) of Teradata tables as much as possible. The recommended approach is to use the Teradata to Exasol migration script from the database migration script repository on GitHub.

This script generates the Exasol DDLs and import statements for the initial load by connecting to the Teradata database and reading the Teradata system views. It also maps the Teradata data types to the Exasol data types; see the Data Type Mapping section for more information.

To use the script, you need direct network connectivity from your Exasol database to your Teradata instance.

DDL Migration

After you have the prerequisites in place, go to the Exasol Database Migration repository on GitHub and download the Teradata to Exasol script.

Next, create the script TERADATA_TO_EXASOL in a schema called DATABASE_MIGRATION in your Exasol database. For example, when you run the below example script, you will see that this script generates the DDL and import commands for all tables in the Teradata database called AdventureWorksDW using the connection TERADATA_DB, and also generates all identifiers case insensitive in Exasol.

Identifiers are transformed to UPPERCASE, which makes them regular identifiers in Exasol. For more information, refer to SQL identifier.

EXECUTE SCRIPT DATABASE_MIGRATION.TERADATA_TO_EXASOL(
    'TERADATA_DB'     
    , TRUE             
    ,'AdventureWorksDW'  
    ,'%'              
);

The script will return a result set containing all DDL statements.

Let's have a closer look at the translated DDL from Teradata and compare it with the translated Exasol DDL:

Teradata DDL

CREATE SET TABLE DimAccount
 (
      AccountKey INTEGER NOT NULL,
      ParentAccountKey INTEGER,
      AccountCodeAlternateKey INTEGER,
      ParentAccountCodeAlternateKey INTEGER,
      AccountDescription VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      AccountType VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      "Operator" VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CustomMembers VARCHAR(300) CHARACTER SET LATIN NOT CASESPECIFIC,
      ValueType VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
      CustomMemberOptions VARCHAR(200) CHARACTER SET LATIN NOT CASESPECIFIC
);

Exasol DDL

CREATE TABLE 
"ADVENTUREWORKSDW"."DIMACCOUNT" 
( 
"ACCOUNTKEY"                    DECIMAL(10) NOT NULL , 
"PARENTACCOUNTKEY"              DECIMAL(10), 
"ACCOUNTCODEALTERNATEKEY"       DECIMAL(10), 
"PARENTACCOUNTCODEALTERNATEKEY" DECIMAL(10), 
"ACCOUNTDESCRIPTION"            VARCHAR(50) NOT NULL , 
"ACCOUNTTYPE"                   VARCHAR(50), 
"OPERATOR"                      VARCHAR(50), 
"CUSTOMMEMBERS"                 VARCHAR(300), 
"VALUETYPE"                     VARCHAR(50), 
"CUSTOMMEMBEROPTIONS"           VARCHAR(200) 
);

Looking at the data type mapping, you can see that the CHARACTER SET LATIN NOT CASESPECIFIC is not needed as Exasol does not support it.

Additionally, note that Teradata specific syntax elements like MULTISET, VOLATILE, [NO] FALLBACK,[NO] BEFORE|AFTER JOURNAL, CHECKSUM, FREESPACE, MERGEBLOCKRATIO, DATABLOCKSIZE, BLOCKCOMPRESSSION, PRIMARY INDEX, [NOT] CASESPECIFIC are not needed when you convert your DDL to Exasol.

Like Teradata, Exasol also supports Identity Columns to generate auto-increments but supports fewer options; for example, you cannot specify the size of the increment or enforce the creation only by the system.