Overriding Data Types When Creating or Altering Tables

Etlworks provides flexible options for customizing the data types used when automatically generating CREATE TABLE and ALTER TABLE SQL statements during ETL processes. This is particularly useful when working with databases that have specific requirements or limitations regarding data types.

Data Type Overrides

The Data Type Overrides feature allows you to define custom mappings between source data types and the desired target data types. This ensures that the automatically generated SQL statements use the appropriate data types for your specific database environment.

How It Works

• Exact Match Replacement: Only exact, unparameterized data type names are replaced. For example, a mapping from datetime2 to datetime2(6) will replace occurrences of datetime2 but not datetime2(3).

• Case-Insensitive Matching: Data type names are matched in a case-insensitive manner.

• Global Application: Once defined, these mappings apply to all database connections within Etlworks.

Example Mappings

How to Override Data Types

1. Access the Database Connection: Navigate to the desired database connection in Etlworks.

2. In the Data Type Overrides field, add a list of key-value pairs where:

   • Key is the original data type (as returned by the database driver)

   • Value is the desired replacement data type

Override CREATE and ALTER TABLE SQL

For scenarios requiring more granular control over the SQL statements, Etlworks offers the ability to override the automatically generated CREATE TABLE and ALTER TABLE SQL using custom JavaScript code. This is particularly useful when dealing with complex schema transformations or database-specific nuances.

How to Use

1. Access the Database Connection: Navigate to the desired database connection in Etlworks.

2. Enter Custom JavaScript: In the Override Create and Alter Table SQL field, input your custom JavaScript code. This code can modify the automatically generated SQL statements as needed.

if (!isAlter) {
    value = sql.replace(/NUMERIC/g, 'NUMERIC(38,2)');
}

In this example, any occurrence of the NUMERIC data type in a CREATE TABLE statement is replaced with NUMERIC(38,2).

For more detailed information and examples, refer to the Override CREATE and ALTER TABLE SQL article.