Snowflake
This is a component for managing a Snowflake database on Integrations Service.
Whenever a user is created or modified in the CMS, you can build a flow with this component to create a new row in your Snowflake database, ensuring your records remain accurate and up-to-date.
Snowflake supports the following constraint types from the ANSI SQL standard:
-
UNIQUE
-
PRIMARY KEY
-
FOREIGN KEY
-
NOT NULL
Snowflake supports defining and maintaining constraints but does not enforce them, except for NOT NULL constraints, which are consistently enforced. Read the Snowflake Constraints documentation to understand more about the other constraints that Snowflake constrains.
-
Multiple rows can be created with the same UNIQUE value.
-
Multiple rows can be created with the same PRIMARY KEY value.
-
Credentials
- Username
-
The Snowflake username with which to connect.
- Password
-
The user password.
- Account name
-
The account name provided by Snowflake (excluding the
snowflakecomputing.comsegment). - User role
-
The security role to use for the session.
- Warehouse
-
The default virtual warehouse to use for the session.
- Database name
-
The database name.
Triggers
Get rows polling trigger
This trigger will execute polling new and updated rows from a specified table with simple criteria of the selected datetime or timestamp table.
The trigger will remember the last execution timestamp and let you build queries on it.
Configuration
- Table
-
A required drop-down to specify the table from which to get rows. Tables with only date type columns appear in the drop-down list.
- Column
-
A required drop-down to specify a table column that contains date type. The supported type is SQL TIMESTAMP without a timezone,
TIMESTAMP_NTZ(9). For example:2004-10-19 10:23:54.123.Read Snowflake’s Datetime documentation for more information.
- Start Polling From Time (optional)
-
An optional text field. Last polled date time. For example,
2021-08-01 00:00:00.000(YYYY-MM-DD hh:mm:ss[.sss]).If this field is left empty, the date will equal
1970-01-01 00:00:00.000, meaning result rows will be returned from the first date. - Emit Behavior
-
A required dropdown. This defines how result objects are emitted; either Emit all or Emit individually.
Output metadata
This depends on how the Emit behavior option is set.
- Emit all
-
An array of rows from the selected table and table column or a message No new records found if there is no match at the specified timestamp or if there are no new rows since the last trigger execution.
- Emit individually
-
Each row is individually emitted from the selected table and table column.
If the table contains rows without a valid date or empty date cell value, the query will skip this row and not return it in the result.
Actions
The Snowflake component includes the following actions:
- Execute custom query action
-
Action to execute custom SQL query from provided request string.
- Insert action
-
This action will execute an insert query into the specified table.
- Lookup row by primary key action
-
This action will look up a row by its primary key.
- Delete row by primary key action
-
This action will delete a row by its primary key.
- Upsert row by primary key action
-
This action will execute upsert row by its primary key.
- Select action
-
The action will execute an SQL query that can return multiple results.
- Execute stored procedure action
-
This action calls stored procedure.
Execute custom query action
| Execute custom query is an advanced action that is not protected from SQL injection. Be aware of the query you are trying to execute. |
Action to execute custom SQL query from provided request string:
Insert action
This action will execute an insert query into the specified table.
Configuration
- Table
-
A required drop-down to specify the table where a row will be inserted.
- Show insert result
-
If selected, 'inserted object' will be returned. Otherwise if not selected (not selected by default) then value '{ result: true }' will be returned as a result of a successful insert operation.
Upsert row by primary key action
This action will execute an upsert row by its primary key. This means if there is no row with the specified ID, it will be inserted, if there is a row with the provided ID, that row will be updated.
An error will be thrown if the number of rows in the table with the same ID is greater than 1.
Configuration
- Table
-
A required drop-down to specify a table where a row will be upserted. Tables with only Primary Key columns will appear in the drop-down.
- Show upsert result
-
If selected, then 'upserted object' will be returned. Otherwise, if not selected (not selected by default), the value
{ result: true }will be returned as a result of a successful upsert operation.
Select action
The action will execute an SQL query that can return multiple results. It has limitations on the query and is suitable only for SELECT type queries.
In SQL queries, you can use clause variables with specific data types.
When using prepared statements, it’s important to validate all incoming data against SQL injection attacks.
To ensure accurate data conversion between JavaScript types and SQL data types, you may need to add a :type attribute to each prepared statement variable.
This will help prevent errors and improve the overall security of your database interactions.
By following this best practice, you can help protect your application and user data from potential threats.
For example, in the SQL statement at 1, you should add a :type notation to all @parameter lines so your SQL query will look like the example at 2:
SELECT
FROM users
WHERE userid = @id AND language = @langSELECT
FROM users
WHERE userid = @id:number AND language = @lang:stringThe following types are supported:
-
string -
number -
bigint -
boolean -
float -
date