Override CREATE and ALTER TABLE SQL

Overview

In all flows where the destination is a database Etlworks automatically generates an SQL for creating a new table if it does not exist and [optionally] altering an existing table if the source dataset has changed. 

It automatically translates data types and column names in the source to the corresponding data types and column names in the destination. It also applies overrides defined in the mapping. 

In rare cases, the automatically generated SQL needs to be modified to correct some of the assumptions made by the ETL engine.  For example, when loading data from the Postgres database to Amazon Redshift the flow assumes that the column in the Postgres table defined as NUMERIC should be translated to NUMERIC in Redshift. Unlike Redshift, NUMERIC in Postgres is actually NUMERIC(38,2) (has numbers after the decimal point) which is causing the loss of decimals when loading data from NUMERIC columns. 

In Etlworks it is possible to override the automatically generated CREATE and ALTER TABLE SQLs using a small program in JavaScript.

Process

Here's how you can configure a database connection to override the default CREATE and ALTER TABLE SQL:

Step 1. Open database Connection.

Step 2. Scroll to the Metadata section and enter a JavaScript code in the Override Create and Alter Table SQL. 

The automatically generated SQL can be accessed using variable sql and the new CREATE TABLE or ALTER TABLE SQL must be assigned to the variable value. Compare variable isAlter to trueto check if SQL is used to alter the table.