Contents x
    SQL Logic Step
    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    SQL Logic Step

    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article summary

    Overview

    The Logic Step of 'SQL / Script' enables for in-database transformations to be performed using a query or a script (the SQL syntax to be used is that of your cloud data warehouse).

    A SQL query is created to execute select statements, resulting in data as the outcome. In contrast, a SQL script serves the purpose of executing commands, such as Data Manipulation Language (DML) operations.

    SQL Query:
    To define the structure of the table that will be managed and created in the Target, a SQL SELECT clause is required at the transformation level. This specific SQL statement will determine the layout of the table. As Rivery handles table management, there's no need for a CREATE/ALTER TABLE clause.

    SQL Script:
    With the SQL Script option, you have the flexibility to execute free text operations and SQL Scripts on your instance. This allows you to execute various SQL clauses such as UPDATE, CREATE/ALTER TABLE, DROP TABLE, INSERT INTO, COPY, and even multi-command clauses in a single step.

    Note:
    • The SQL script is not encrypted in any way. Please avoid using credentials in the script.
    • The SQL script runs according to user configurations. Users are responsible for any changes to the table, schema, or data that occur due to the script.
    • Ensure the validity of your SQL clause using the Worksheet provided by your warehouse before pasting it into Rivery.**
    • Here’s an example demonstrating the use of the SQL Script option with Snowflake Stored Procedures in a Rivery Logic River.
    • Rivery's look has been updated, but it still serves the same functionality.

    How it Works

    1. To begin, select SQL / Script as the Logic Step type and the cloud database to which you want to connect.

    ezgif.com-gif-maker 1.gif

    1. In the Source section:
      a. Select the Connection Name.
      b. Enter your SQL script/query into the SQL field.
    Please Note:
    • If you're using colons, make sure they're preceded by double backslashes, or else the River will fail.
    • A limitation of SQL querying in Snowflake is that it does not support the "WITH" clause.
    • In Snowflake's SQL querying, refrain from using '--' for remarks, as this could result in failure of your Logic River.
    • In Snowflake, the connection form includes a "Default Database/Schema" section. This allows you to specify the database and schema you want to work with, and the connection will automatically remember and use these as the default.
      This simplifies the setup process for the target, saving time and effort.

    image.png

    ezgif.com-gif-maker 7.gif

     

     

    Working With Variables

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

    How To Use Logic River Variables

    1. Select the 'Variables' tab in the upper right corner.
      image.png

    2. Add a Variable ('temp' is an example).

    Please Note:

    • To create an array, tick the 'Contains Multiple Values' box.
    • If the item 'Clear Value On Start' is checked, the value of the variable will always be updated to the original value when a new River is formed, regardless of what happens to it during a River.
    • Multiple variables can be defined.
    • Variable Encryption is currently only availalbe in Python steps.

    image.png

    1. Click Apply Changes.

    image.png

    1. To use the variable(s), run this:
    SELECT *
FROM {temp}


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

    SELECT *
WHERE my_string_field = '{Var_Name}'

    Expand & Preview Results

    To Expand & Preview Results click the diagonal arrows in the SQL field:
    ezgif.com-gif-maker 3.gif

    Results Tab

    The Result tab displays the output of your SQL script/query.
    Click Run to view the output:
    ezgif.com-gif-maker 4.gif

    Note:

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

    Compiled SQL Tab

    A compilation is the process of optimizing the query execution plan.
    When variables are used in the query, the real value will be displayed in the compiled SQL tab.
    'Compiled SQL' can only be used for SQL queries, not SQL scripts.
    Here's an example:

    image.png

    Move & Scroll

    To make things easier, the preview window may be moved and scrolled:
    ezgif.com-gif-maker 6.gif

    Download SQL

    The SQL query can be downloaded to your local computer.
    Click on the arrow pointing down in the upper right corner:
    ezgif.com-gif-maker 5.gif

    Was this article helpful?
    What's Next