Versions Compared

Old Version 26

changes.mady.by.user Cindy Hall

Saved on

compared with

New Version Current

changes.mady.by.user Lakshmi Manda

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

On this Page

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Snap type:

Write


Description:

This Snap executes 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.

Table Creation

If the table does not exist when the Snap tries to do the insert, and the Create table property is set, the table will be created with the columns and data types required to hold the values in the first input document. If you would like the table to be created with the same schema as a source table, you can connect the second output view of a Select Snap to the second input view of this Snap. The extra view in the Select and Bulk Load Snaps are used to pass metadata about the table, effectively allowing you to replicate a table from one database to another.
 

The table metadata document that is read in by the second input view contains a dump of the JDBC DatabaseMetaData class. The document can be manipulated to affect the CREATE TABLE statement that is generated by this Snap. For example, to rename the name column to full_name, you can use a Mapper (Data) Snap that sets the path $.columns.name.COLUMN_NAME to full_name.  The document contains the following fields:

  • columns - Contains the result of the getColumns() method with each column as a separate field in the object. Changing the COLUMN_NAME value will change the name of the column in the created table. Note that if you change a column name, you do not need to change the name of the field in the row input documents. The Snap will automatically translate from the original name to the new name. For example, when changing from name tofull_name, the name field in the input document will be put into the "full_name" column. You can also drop a column by setting the COLUMN_NAME value to null or the empty string.  The other fields of interest in the column definition are:

    • TYPE_NAME - The type to use for the column.  If this type is not known to the database, the DATA_TYPE field will be used as a fallback.  If you want to explicitly set a type for a column, set the DATA_TYPE field.

    • _SL_PRECISION - Contains the result of the getPrecision() method.  This field is used along with the _SL_SCALE field for setting the precision and scale of a DECIMAL or NUMERIC field.

    • _SL_SCALE - Contains the result of the getScale() method.  This field is used along with the _SL_PRECISION field for setting the precision and scale of a DECIMAL or NUMERIC field.

  • primaryKeyColumns - Contains the result of the getPrimaryKeys() method with each column as a separate field in the object.

  • declaration - Contains the result of the getTables() method for this table. The values in this object are just informational at the moment.  The target table name is taken from the Snap property.

  • importedKeys - Contains the foreign key information from the getImportedKeys() method. The generated CREATETABLE statement will include FOREIGN KEY constraints based on the contents of this object. Note that you will need to change the PKTABLE_NAME value if you changed the name of the referenced table when replicating it.

  • indexInfo - Contains the result of the getIndexInfo() method for this table with each index as a separated field in the object.  Any UNIQUE indexes in here will be included in the CREATE TABLE statement generated by this Snap.

Note

If you create multiple tables with an overlapping column name, the data replication occurs in a different column having an unsuitable structure. It results in a value-exceeding-data-type-length error, causing failure to the Pipeline execution.

When such two tables are joined, the Snap renames one of the two columns names for smooth execution of the Pipeline.

Example: 

A table named Owner has columns – firstname, lastname, ownerid; another table named Visit has columns – date and ownerid.

The column name ownerid is overlapping in both the tables.

As a fix, the Snap would name the columns as firstname, lastname, ownerid, date, visit.ownerid to join the two tables.


Note

The Snap will not automatically fix errors encountered during table creation, since they may require user intervention to be resolved correctly. For example, if the source table contains a column with a type that does not have a direct mapping in the target database, the Snap will fail to execute. You will then need to add a Mapper (Data) Snap to change the metadata document to explicitly set the values needed to produce a valid CREATE TABLE statement.


Expected Input and Output

  • Expected upstream Snaps: Any Snap that provides a document output view, such as a Mapper or PostgreSQL Execute Snap.
  • Expected downstream Snaps: [None] or any Snap that has a document input view such as a Filter or a Data or a JSON Formatter.
  • Expected input:  A table name and record details.
  • Expected output:  Inserts a record.
Prerequisites:

[None]


Support and limitations:

Works in Ultra Task Pipelines if batching is disabled.

Known Issue: If database metadata from an upstream Snap contains geography column data such as modifiers, those modifiers may not be written to the target table. 
Workaround: To write geographic data to the target table, create the table using the PostgreSQL - Execute Snap.

Account: 

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring PostgreSQL Accounts for information on setting up this type of account.


Views:


InputThis Snap has one input view by default that 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 will then be used to guide the creation of the table in the database.
OutputThis Snap has at most one document output view.
Error

This Snap has at most one error view and produces zero or more documents in the view.

Note
Database Write Snaps output all records of a batch (as configured in your account settings) to the error view if the write fails during batch processing.



Settings

Label*


Specify the name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Schema name


Specify the database schema name. Selecting a schema filters the Table name list to show only those tables within the selected schema.

Note

The values can be passed using the pipeline parameters but not the upstream parameter.

Example: SYS 

Table name*


Specify the table that the rows will be inserted into.

Note

The values can be passed using the pipeline parameters but not the upstream parameter.


Example: users 

Note

You must check the datatype 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 datatype, see The Basics of PostgreSQL UUID Data Type.


Create table if not present


Multiexcerpt include macro
nameME_Create_Table_Automatically_2_Inputs
pageRedshift - Bulk Load


Warning

If database metadata from an upstream Snap contains geography column data such as modifiers, those modifiers may not be written to the target table. For example, if the column metadata contains a polygon modifier, that modifier will not be written to the target table. 
Workaround: Create the table using the PostgreSQL - Execute Snap.

Default value: Not selected 

Preserve case sensitivity

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.

Number of retries

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)

Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. 

Default value: 1
Example:  10

Multiexcerpt include macro
nameSnap Execution
pageSOAP Execute

Multiexcerpt include macro
nameExecution_Detail_Write
pageSOAP Execute

 

Insert excerpt
Azure SQL - Bulk Load
Azure SQL - Bulk Load
nopaneltrue

Insert excerpt
PostgreSQL Snap Pack
PostgreSQL Snap Pack
nopaneltrue

Example

Inserting the Records Into an Existing Table in the Database

This example pipeline demonstrates how to insert the records into an existing table in the database using the PostgreSQL Insert Snap.

Image Added

Step 1: Configure the Mapper Snap with the static or dynamic values you want to insert in the database for different variables.

Image Added

Step 2: Configure the PostgreSQL Insert Snap to insert the values in a table in the database.

Image Added

Step 3: Validate the pipeline. A success message appears with all the values inserted in the database.

Image Added