Configuring SQL logic step

The SQL / Script Logic Step lets you run in-database transformations using a query or a script. The SQL syntax used depends on your cloud data warehouse.

Using SQL Query

Use the SQL Query to execute select statements, resulting in data as the outcome. A SQL SELECT clause is required at the transformation level to define the table's structure managed and created in the Target. This query determines the structure of the resulting table in the Target.

note

As Data Integration handles table management; there is no need for a CREATE/ALTER TABLE clause.

Using SQL Script

Use the SQL Script option to execute free text operations and SQL Scripts on your instance, including clauses such as UPDATE, CREATE/ALTER TABLE, DROP TABLE, INSERT INTO, COPY, and multi-command clauses in a single step.

note

• The SQL script is not encrypted in any way. Avoid using credentials in the script.
• The SQL script runs according to user configurations. You are responsible for changes to the table, schema, or data due to the script.
• Ensure the validity of your SQL clause using the Worksheet provided by your warehouse before pasting it into Data Integration. To learn more, refer to the Snowflake Stored Procedures in a Data Integration Logic River to use the SQL Script option with Snowflake.

Procedure

1. Navigate to the Data Integration console.
2. Click the River tab from the left-hand menu.
3. Click ADD River and choose Logic River or the existing one from the list.
4. Select SQL / Script as the Logic Step type and the cloud database you want to connect to.
5. In the Source section: a. Select the Connection Name. b. Enter your SQL script/query into the SQL field.

note

• When you use colons (:), precede them with double backslashes (\:) to prevent your Logic River from failing.
• Snowflake SQL queries do not support this platform's WITH clause.
• Avoid using -- for comments in Snowflake SQL queries because it causes your Logic River to fail.
• The Snowflake connection form includes a Default Database/Schema section. You specify the database and schema you want to work with, and the connection automatically uses these as the default to simplify the target setup.

Working with variables

In the SQL Logic step, dynamic variables can establish connections between Rivers, automate tasks, and exchange data.

Procedure

1. Navigate to the Data Integration website.
2. Click the River tab from the left-hand menu.
3. Click ADD River and choose Logic River or the existing one from the list.
4. Select the Variables tab in the upper right-hand corner.
5. Add a Variable. For example, temp.

:::

6. Click Apply Changes.

7. To use the variable(s), run this:

SELECT *
FROM {temp}

note

Make sure to run this code whenever you refer to a String:

SELECT *
WHERE my_string_field = '{Var_Name}'

Expand and preview results

Click the diagonal arrows in the SQL field to expand and Preview Results.

Results tab

The Result tab displays the output of your SQL script/query.

• Click Run to view the output.
• Click the arrow icon to download the SQL query.
• Click the Expand & Preview results to expand and preview the results.

note

When the output reaches a maximum of 500 rows, a notification appears.

Compiled SQL tab

Compilation is the process of optimizing the query execution plan. When variables are used in the query, the system replaces the real value in the Compiled SQL tab. Compiled SQL can be used only for SQL queries, not SQL scripts.

• You can move and scroll the preview window for easier navigation.
• Click the arrow icon in the upper right-hand corner to download the SQL query.

Download SQL

The SQL query can be downloaded to your local computer. Click the arrow pointing down in the upper right corner.

-->