Open topic with navigation

TIBCO ActiveMatrix BusinessWorks™ Plug-in for Snowflake 6.3.0

# Snowflake Insert Activity

You can use this activity to run the insert SQL statements on the Snowflake data warehouse. You can insert multiple rows in the database in batches. Error in one batch does not stop the execution of the subsequent batch. To stop the execution of batch for error, select the Fault on Batch Failure checkbox. The failure record of a batch depends on the behavior of the underlying Snowflake JDBC driver and Snowflake data warehouse engine. If a batch fails to insert one or more records, the detailed message is displayed in logs including the batch number, reason of failure, and rows that failed to insert. The insertion operation results in the formation of total rows attempted and total rows affected.

## General

The General tab contains the following fields.

Field | Module Property? | Visual Diff? | Description
--- | --- | --- | ---
Name | No | Yes | Specify the name to be displayed as the label for the activity in the process.
Snowflake Connection Resource | Yes | Yes | Click the Create New Process Property icon to select a Snowflake JDBC Connection shared resource. If no matching Snowflake JDBC Connection shared resource is found, click Create Shared Resource to create one. For more details, see Creating and Configuring the Snowflake JDBC Connection Resource.
Create Table from XSD | No | Yes | Use this option to create a new table in Snowflake from the XSD provided by you and select table format from external schema. When this checkbox is selected, Entity field on General tab is disabled and Table Name field, Load Schema button from SQL Builder tab are enabled. Click SQL Builder > Load Schema to provide the XSD schema for the table.
Entity | No | Yes | Click the Browse resource icon to select an entity. The entities downloaded in Snowflake JDBC Connection shared resource's Schema tab is available for selection. Note: This field is disabled when Create Table from XSD checkbox is selected.
Batch Size | Yes | Yes | All incoming messages are inserted performing SQL, number specified here defines how many SQL statements can be batched together that can be represented as a single SQL. The default value is 100.
Time Out | Yes | Yes | Defines activity timeout in seconds. Default value is 0, means activity timeout disabled. Note: Insert activity works in multiple batches, so per batch timeout is evaluated using the timeout value specified. Refrain from using a smaller timeout value.

## Description

On the Description tab, provide a short description for the activity.

## SQL Builder

The SQL Builder tab displays the metadata of the entity selected in the General tab or details of the entity schema loaded using the Load Schema button.

Field | Editable | Description
--- | --- | ---
Table Name | Yes | Displays the name of the table to be created. When General tab > Create Table from XSD checkbox is selected, this field is enabled. If you provide XSD using the Load Schema button, the value of this field is set to name of root complex element of the XSD. However, you can edit this value and set to desired value.
Field Name | No | Displays name of the column.
Data Type | No | Displays the data type of the column.
Primary Key | Yes, if 'Metadata' table is loaded using XSD provided with 'Load Schema' button from SQL Builder tab. No, if 'Metadata' table is loaded using Entity field from General tab. | Displays if the column is a primary key. The column is editable if table is selected from external schema.
Not Null | No | Displays if the column accepts null value. The checkbox is selected if the constraints to the column in snowflake DB is marked as 'Not Null'. The checkbox is cleared if the constraints to the column in snowflake DB is not marked as 'Not Null'. This checkbox is non-editable.
Dimension | No | Displays the dimension of the column.
Values | Yes | Displays the fields that must be part of the Values clause for an insert SQL statement. By default, Values column is selected for all the fields.
Load Schema | No | When General tab > Create Table from XSD checkbox is selected, this field is enabled. If you click this button, a dialog is opened where you can create or browse XSD present in the workspace. Once you select XSD in the dialog, its details are displayed into 'Metadata' table and the 'Table Name' field is set to name of root complex element of the XSD.

## Advanced

This tab has the following fields:

Field | Module Property? | Description
--- | --- | ---
Override Database Name | Yes | Overrides Database name specified in snowflakejdbcResource.
Override Schema Name | Yes | Overrides Schema name specified in snowflakejdbcResource.
Interpret Empty String as NULL | Yes | If this checkbox is selected, empty string is interpreted as NULL value.
Fault On Batch Failure | Yes | If this checkbox is selected and if an error is encountered in one of the batches, the batch insert process stops and does not continue to the next batch.
Merge | No | If this checkbox is selected and if the record already exists, the insert process updates the record by executing the merge query. Note: Merge is supported only when batch size is 1.
Merge On Columns | Yes | If the Merge checkbox is selected, this field is enabled. Enter column names on which the merge operation should be performed. For more than one column names, enter column names separated by comma. Column names are not case-sensitive.

## Input

All the fields in the SQL Builder tab that have Values column selected are a part of the Input tab. The Input tab displays the input schema of the activity as a tree structure. The information in the schema depends on the fields selected on the SQL Builder tab.

Note: It is necessary to have input data for all columns specified in the Merge On Columns field.

## Output

The Output tab displays the rowsAttempted and rowsAffected fields. The rowsAttempted field holds the count of the number of rows that were attempted by Snowflake Insert activity and the rowsAffected field holds the count of the number of rows inserted successfully after the Snowflake Insert activity is invoked. The difference between these two is the number of rows that failed to insert. The Output tab displays the output schema of the activity as a tree structure. The output is read-only.

Note: If new columns are added in the source table, the target table gets altered during runtime and the new columns are added in the target table.

## Fault

The Fault tab lists exceptions that are thrown by this activity.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO ActiveMatrix BusinessWorks™ Plug-in for Snowflake

Copyright © 2020-2024. Cloud Software Group, Inc. All Rights Reserved.