In all flows where the destination is a database Etlworks Integrator 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
Here's how you can configure a database connection to override the default CREATE and ALTER TABLE SQL:
Step 1. Open database Connection.
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
trueto check if SQL is used to alter the table.