Set field's value using native SQL (inline SQL)

Overview

If the destination is a relational database it is possible to use the native dialect of the SQL to calculate the column's value.

Passing calculation logic to the destination database can greatly improve the performance of the ingestion, compared to using JavaScript or Python because there will be no context switch between SQL and another scripting language.

Enable SQL

Step 1. Make sure Use Bind Variables is enabled.

Step 2. In Mapping, click the pen button to open the field function editor. 

Change the language of the field function editor to SQL.

Enter valid SQL

You can use any valid (for the destination database) SQL as a field function. You can reference any existing and calculated column as {column_name}. 

In SQL you can reference global and flow variables as {varname}. Flow variable name must be UPPERCASED ({VARNAME}). 

You can only use SQL, which is allowed, as a field value in INSERT/ UPDATE/ DELETE/ MERGE statement.

Example

LOWER({first_name}) || '-' || LOWER({last_name})

This SQL will convert the values of the columns first_name and last_name to lower case, then concatenate them and '-' in between.  

Generated SQL 

When the destination is a relational database, the ETL engine automatically generates INSERT, UPDATE, DELETE, or MERGE statement, based on the selected SQL action.

Example:

insert into table (first_name, last_name, name) values (?, ?, ?) 

If the field function is an SQL like above, the final INSERT statement, generated by the ETL engine will look like the following:

insert into table (first_name, last_name, name) values (?, ?, LOWER(?) || '-' || LOWER(?))

If using of bind variables is enabled (it is enabled by default) ? will be replaced on actual column values at runtime.