Overview

You can use this Snap to execute an SQL Merge statement. Each document in the input view is expected to be merged or created with/as a new record in the specified table on the target database.

MySQL - Merge Snap does not have the Merge condition property because MySQL operates merge on its primary key only. Therefore, input data must contain the value of the primary key for the merge operation to work.
When the target table has a primary key, and if a primary key exists for the record, the row is updated with the new values. If the primary key does not exist for the record, a new row is inserted.
When the target table does not have a primary key, the Insert statement is executed, and duplicate records are inserted into the table on every execution.

Merges will be batched up until the account's batch size property or until the structure of the update statement changes. An update statement will change if an incoming document contains different keys than a previous document.

Snap Type

MySQL - Merge Snap is a WRITE-type Snap.

Support for Ultra Pipelines

Works in Ultra Tasks.

Limitations

Snap Views

Type	Format	Number of Views	Examples of Upstream and Downstream Snaps	Description

Type

Format

Number of Views

Examples of Upstream and Downstream Snaps

Description

Input

Document

Min: 1
Max: 1

Mapper

The Snap can have at the most one input document that provides the value for the project ID or Dataset ID

Output

Document

Min: 0
Max: 1

File Writer
Mapper

If an output view is available, then the original document that was used to create the statement is displayed in the output with the status of the merge executed.

Queries produced by the Snap are in the following format:

 MERGE INTO tablename USING table_reference ON (condition)
    WHEN MATCHED THEN
    UPDATE SET column1 = value1 [, column2 = value2 ...]
    WHEN NOT MATCHED THEN
    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

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 while running the Pipeline by choosing one of the following options from the When errors occur list under the Views tab:

Stop Pipeline Execution: Stops the current pipeline execution if the Snap encounters an error.
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

Asterisk (*): Indicates a mandatory field.
Suggestion icon (): Indicates a list that is dynamically populated based on the configuration.
Expression icon (): Indicates the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.
Add icon (): Indicates that you can add fields in the fieldset.
Remove icon (): Indicates that you can remove fields from the fieldset.

Field Name	Field Type	Description

Field Name	Field Type	Description
Label*	String	Specify a unique 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 Default Value: MySQL - Merge Example: MySQL - Merge
Schema name	String/Expression	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 suggestible and will retrieve available database schemas during suggest values. Default value: N/A Example: SYS
Table name	String/Expression	Required.The name of the table to execute the insert on. You can pass the values can using the pipeline parameters but not the upstream parameter. Default value: N/A Example: people
Number of retries	Integer/Expression	Specify the maximum number of attempts to 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 resulted in an exception. Default value: 1 Example: 10
Snap Execution	Dropdown list	Select one of the following three modes in which the Snap executes: 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: Example: Validate & Execute

Example

In this pipeline, the records from two different input streams are merged into a MySQL table using the MySQL Merge Snap. The upstream input from the Mapper Snap and the CSV Generator Snap are joined by the Join Snap. The new MySQL table will now have the records with columns passed from both the input sources.

The MySQl Select Snap retrieves the records from the table, `pqadb`.`TAM_SAMPLE_PERSONS` to be merged into a table `pqadb`.`TAM_SAMPLE_PERSONS_MERGE`.

The Snap settings and the output preview is as displayed below:

The Mapper Snap maps the records to be merged with the MySQL table, `pqadb`.`TAM_SAMPLE_PERSONS_MERGE`.

The CSV Generator passes the records to be merged into the MySQL table, `pqadb`.`TAM_SAMPLE_PERSONS_MERGE`.

The output preview at the CSV Generator Snap is as displayed below:

The Join Snap joins both the input streams and the output preview is as displayed below:

Note that the input from the CSV Generator Snap had three columns only and hence the remaining columns are displayed as null.

The MySQL Merge Snap merges the input documents into the table `pqadb`.`TAM_SAMPLE_PERSONS_MERGE`.

The output preview at the MySQL Merge Snap is as displayed below:

Related links: