- Getting Started
- Demo Videos
- Blueprint
- Rivers
- Rivers List & Groups
- Source to Target River
- Logic River
- Action River
- Settings Tab
- Sources
- Applications
- Reconnecting to an Existing OAuth2 Connection in Rivery
- Active Campaigns
- Adaptv
- Adaptive Planning
- Adjust
- Adobe Ads
- Adobe Analytics
- Adroll
- Adtelligent (Verta Media)
- Airtable
- Amplitude
- Anaplan
- Apple Ads
- AppNexus
- Appsflyer
- Appstore Connect - Sales and Trends
- Appstore Connect - Apps Analytics
- Avantlink
- AWS Billing
- Bing Ads
- BlueSnap
- Branch
- Brandwatch
- Bronto
- CallRail
- Cellxpert
- Content.Ad
- Coupa
- CreativeX (Picasso Labs)
- Crimson Hexagon
- Criteo
- Criteo V2023 -10
- Customer Gauge
- Currency Service
- Dear Systems
- Delta Projects
- Digital Turbine
- Display & Video 360
- Docebo
- DoubleClick DCM
- Ex-Co
- Facebook Ads
- Facebook Audience Network
- Facebook Social
- Freshservice
- Genesys
- GMP365
- Google Ad Manager (DoubleClick for Publishers)
- Google Ads
- Google Analytics
- Google Play Store
- Google Search Console
- GothamAds
- Greenhouse
- Harvest
- Hibob
- HiveStack
- HubSpot
- Impact Radius
- Index Exchange
- Innovid
- Iron Source
- Instagram Social
- Intercom
- Jira
- JWPlayer
- JWPlayer Analytics
- Kantata (Mavenlink)
- Keepcon
- Klaviyo
- Kochava
- LinkedIn Ads
- Linkedin Social
- LKQD
- Magento
- Marketo
- Maxio Chargify
- Mediamath
- MixPanel
- Moat Analytics
- MobileAction
- Monday
- MyAffiliates
- NetSuite APIs
- Nium
- Nuviad
- Optimove
- Outbrain
- PayPal Reports
- Pendo
- Pepperjam
- Quickbooks
- Recharge
- Recurly
- Reddit Ads
- Revcontent
- Sage Intacct
- Sailthru
- Salesforce
- Salesforce Audience
- Salesforce Marketing Cloud
- Salesforce Pardot
- SAP Concur
- Search Ads 360 Reporting
- Sendgrid
- Sensor Tower
- Servicenow
- ShareASale
- ShipHero
- Shopify
- Skai
- Snapchat Marketing
- Socialbakers
- Social Studio
- Spotx
- Sprinklr
- StackAdapt
- Streamrail
- Stripe
- SuccessFactors
- SurveyGizmo
- SurveyMonkey
- Taboola
- The Trade Desk
- Tiger Pistol
- Tiktok
- TrendKite
- Tune (HasOffers)
- UXCam
- Verizon Media (Oath)
- X (Twitter)
- Yahoo Ads
- Yandex
- Yotpo
- YouTube
- Zendesk
- Zendesk Chat
- Zendesk Talk
- Databases
- Databases Overview
- Database Connectivity Options
- Database River Modes
- Automatic Schema Drift Management
- Database Table Configuration Options
- CDC 'Point in Time' Position
- BigQuery
- Datastore
- ElasticSearch
- MariaDB
- Microsoft SQL Server
- MongoDB
- MySQL
- Oracle
- PostgreSQL
- Snowflake as a Source
- Teradata
- Vertica
- Events
- Storage & Files
- Rest API
- Applications
- Targets
- Administration
- Security
- REST API
- Support
- Troubleshooting
- Release Notes
- 2024
- 2023
- December 2023 Release Notes
- November 2023 Release Notes
- October 2023 Release Notes
- September 2023 Release Notes
- August 2023 Release Notes
- July 2023 Release Notes
- June 2023 Release Notes
- May 2023 Release Notes
- April 2023 Release Notes
- March 2023 Release Notes
- February 2023 Release Notes
- January 2023 Release Notes
- 2022
- December 2022 Release Notes
- November 2022 Release Notes
- October 2022 Release Notes
- September 2022 Release Notes
- August 2022 Release Notes
- July 2022 Release Notes
- June 2022 Release Notes
- May 2022 Release Notes
- April 2022 Release Notes
- March 2022 Release Notes
- February 2022 Release Notes
- January 2022 Release Notes
- 2021
- API Upgrades Changelog
- API Upgrade Overview
- 2024
- 2024-12 Campaign Manager 360 Fields Update
- 2024-10 Shopify API Upgrade v2024-10
- 2024-10 Monday API Upgrade v2024-10
- 2024-08 Display & Video 360 Upgrade v3
- 2024-08 Google Ads Upgrade v17
- 2024-08 LinkedIn Social Upgrade v202406
- 2024-08 LinkedIn Ads Upgrade v202406
- 2024-08 Google Ad Manager Upgrade v202405
- 2024-07 Pendo Upgrade
- 2024-05 Adjust API Upgrade
- 2024-05 Search Ads 360 API Upgrade
- 2024-05 Klaviyo Legacy v1 and v2 to v2024-02-15 API Migration
- 2024-03 Facebook Social Deprecated Metrics
- 2024-02 Shopify API Upgrade v2024-01
- 2024-02 Google Ads API Upgrade V15
- 2024-02 Airtable API Keys Deprecation
- 2024-01 Instagram Social Upgrade to Graph API v18
- 2024-01 Criteo API Upgrade v2023-10
- 2024-01 Google Ad Manager upgrade to v202311
- 2024-01 Monday API Upgrade v2024-01
- 2024-01 Facebook Ads - Marketing API Upgrade v18
- 2023
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.
- 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
- To begin, select SQL / Script as the Logic Step type and the cloud database to which you want to connect.
- In the Source section:
a. Select the Connection Name.
b. Enter your SQL script/query into the SQL field.
- 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.
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
Select the 'Variables' tab in the upper right corner.
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.
- Click Apply Changes.
- 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:
Results Tab
The Result tab displays the output of your SQL script/query.
Click Run to view the output:
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:
Move & Scroll
To make things easier, the preview window may be moved and scrolled:
Download SQL
The SQL query can be downloaded to your local computer.
Click on the arrow pointing down in the upper right corner: