AlloyDB Insert

Overview

You can use this Snap to execute a SQL INSERT statement using the document's keys as the columns to insert to and the document's values as the values to insert into the columns.

Write-type Snap
Works in Ultra Tasks

Behavior changes

The following are the behavior changes caused by the JDBC driver upgrade for PostgreSQL Snap Pack and Google AlloyDB Snap Pack
Some error messages will now include the data type for certain values, for example:
- Old: "...where (badId = 1) was aborted:..."
- New:
```
"...where (badId = ('1'::numeric)) was
    aborted:..."
```

There are some minor text changes to a few of the error messages, for example:

Old:

"Hint: No operator matches the given name and argument
      types."

New:

"Hint: No operator matches the given name and argument
    type(s)."

Snap views


View	Description	Examples of upstream and downstream Snaps
Input	A table name and record details. By default the input view accepts the rows to insert into the table. A second input view can be added that accepts the table metadata document from a database Select Snap. The table metadata is used to create the target table in the database.	Mapper AlloyDB Execute
Output	Inserts a record.	Filter JSON Formatter
Error	Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are: Stop Pipeline Execution Stops the current pipeline execution when an error occurs. Discard Error Data and Continue Ignores the error, discards that record, and continues with the remaining records. Route Error Data to Error View Routes the error data to an error view without stopping the Snap execution. Learn more about Error handling in Pipelines.

Snap settings

Legend:

Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
Upload : Uploads files. Learn more.

Learn more about the icons in the Snap settings dialog.


Field / Field set	Type	Description
Label	String	Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline. Default value: AlloyDB Insert Example: AlloyDB Insert
Schema name	String/Expression/ Suggestion	Specify the database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema. Warning: You can pass values using the Pipeline parameters but not through the upstream Snap. Default value: N/A Example: SYS
Table name	String/Expression/ Suggestion	Required. Specify the table that the rows will be inserted into. Note: You must check the data type of each column in the table into which you want to insert the input data, and structure your data accordingly; else the endpoint generates an error. For example, if the column `contact_id` expects data in the UUID datatype, you must provide UUID data. For details on the UUID data type, see The Basics of PostgreSQL UUID Data Type. Default value: N/A Example: users
Create table if not present	Checkbox	Select this checkbox to automatically create the target table if it does not exist. If a second input view is configured for the Snap and it contains a document with schema (metadata) of the source table, the Snap creates the new (target) table using the same schema (metadata). However, if the schema comes from a different database, the Snap might fail with an `Unable to create table: "<table_name>"`error due to data type incompatibility. In the absence of a second input view (the schema/metadata document), the Snap creates a table based on the data types of the columns generated from the first row of the input document (first input view). Default status: Deselected
Preserve case sensitivity	Checkbox	Select this checkbox to preserve the case sensitivity of the column names while performing the insert operation. If you do not select this option, then Snap converts the column names in the input document to match the column names in the target database table before inserting the values. The Snap checks for the following three conditions: If the column name in the table is in lower case, it modifies the column label to lower case. If the column name in the table is in the upper case, it modifies the column label to upper case. If the conditions mentioned above are not met, it takes the column label as is. On the contrary, if you select this checkbox, the Snap does not convert the case of the column names and inserts the names as-is in the target database. Therefore, ensure that the column labels in the input document match the column labels in the target database table. Else, the Snap does not insert the values and displays an error. For example, if the column names (ID, Name) in the input document do not match the column names (Id, name) in the database, then the Snap does not insert the values and displays an error. Default status: Deselected
Number of retries	Integer/Expression	Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response. Default value: 0 Example: 3
Retry interval (seconds)	Integer/Expression	Specify the time interval between two successive retry requests. A retry happens only when the previous attempt results in an exception. Default value: 1 Example: 10
Snap execution	Dropdown list	Choose one of the three modes in which the Snap executes. Available options are: Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime. Execute only: Performs full execution of the Snap during pipeline execution without generating preview data. Disabled: Disables the Snap and all Snaps that are downstream from it. Default value: Execute only Example: Validate & Execute

Note: In a scenario where the Auto commit on the account is set to True, and the downstream Snap depends on the data processed on an upstream Database Bulk Load Snap, use the Script Snap to add a delay for the data to be available. For example, when performing a create, insert, and a delete function sequentially on a pipeline, using a Script Snap helps in creating a delay between the insert and delete function. Otherwise, it may turn out that the delete function is triggered even before inserting the records on the table.