Articles in this section

See more

Override CREATE and ALTER TABLE SQL

  • Updated
Follow

Overview

In any flow where the destination is a database, Etlworks generates SQL to create the table (if missing) and optionally to alter it when the source dataset changes. Source data types and column names are mapped to destination data types and column names automatically, and overrides defined in the mapping are applied.

In rare cases the auto-generated SQL needs adjustment. For example, when loading from PostgreSQL to Amazon Redshift, Etlworks assumes Postgres NUMERIC maps to Redshift NUMERIC. In Postgres, NUMERIC is effectively NUMERIC(38,2), which causes loss of decimal precision in Redshift.

You can override the auto-generated CREATE and ALTER TABLE SQL using a small JavaScript snippet on the database connection.

How do I override the SQL?

  1. Open the target database connection.
  2. Scroll to the Metadata section and add JavaScript to Override Create and Alter Table SQL.

The auto-generated SQL is available as sql. Assign the new SQL to value. Compare isAlter to true to check whether the SQL is an ALTER statement.

Example: change NUMERIC precision when creating a table

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

See also Override data types when creating or altering tables for the simpler key/value mapping option.

Related articles