On this Page

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.

Prerequisites:

None.

Support and limitations:
Account: 

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


Views:


Input

This Snap has two input views but by default it has one input view.
You can add a second view for metadata for the table as a document so that the table is created in MySQL with a similar schema as the source table. This schema is usually from the second output of a database Select Snap. If the schema is from a different database, the data types might not be properly handled.

OutputThis Snap has at most one document output view. If an output view is available, then the original document that was used to create the statement will be output with the status of the insert executed.
Error

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

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. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names of all schemas. The property is suggestable and will retrieve available database schemas during suggest values.

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


Default value: None
Example: SYS

Table name*

Specify the name of table to execute insert on.

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


Default value: None
Example: people

Create table if not present


Default value:  Not selected


Use MySQL INSERT IGNORE option
Select an option to instruct the Snap to ignore the SQL errors encountered during execution.


If the target table contains no primary key constraint, and the 'Use MySQL INSERT IGNORE option' is set to ignore, then the Snap inserts null values into MySQL when you attempt to insert invalid data.

To ensure that all data truncation / validation errors are caught in the error view, set the 'Use Mysql INSERT IGNORE option' to Fail.


Snap Execution

Select 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


  • If MySQL Insert batch fails due to a duplicate primary key, the error message from MySQL contains a reference to the first row failed, e.g. "Duplicate entry '10000' for key 'PRIMARY'". Those rows before the first failed row in the batch get successfully inserted and the ones after the failed row are not processed, but MySQL returns with a mark of all rows failed. You will see the same error message with the reference to the first failed row in all rows in the error view. If you want to see accurate referencing to the failed row in the error message, you can change the Batch size to one in the account and execute or preview the pipeline. You will see successful rows go to the output view and failed rows to the error view with accurate reference.
  • There are difference in the MySQL DB version when reporting errors, such as v5.5xx will return the expected error documents when e.g. the insert fails with a duplicate key, whereas a lower version such as v5.0xx does not.

Example

In this Pipeline, we inserted the records to an existing table, via the JSON Generator Snap and mapped them to the MySQL table 'Hues' using the Mapper and the MySQL Insert Snaps respectively. The failed records are routed to the error view.



The JSON Generator Snap passes the values to the Mapper Snap.


The Mapper Snap maps the records to the respective columns on the table.

The output preview of the Mapper Snap that displays the added records.


The MYSQL Insert Snap inserts the records into the table 'Hues' on the 'Snaplogic' Schema.

The failed records are routed to the error view, which displays the reason that the column 'rgb' is unknown in the field list (meaning the column rgb is absent in the existing table and hence the two records that has the RGB values are routed to the error view).