Table Definition
As you probably have thousands of tables in your current
This script generates the Exasol DDLs and import statements for the initial load by connecting to the
To use the script, you need direct network connectivity from your Exasol database to your
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.
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.
Other methods to convert Teradata DDL to Exasol
If you are using a data modeling tool, you can export the DDL into an ANSI SQL format.