On this Page
Table of Contents | ||||
---|---|---|---|---|
|
Snap type: | Write | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap executes a SQL Merge with given properties. The documents that are provided on the input view will be merged into the provided table on the provided database. Each document on the input view is expected to be merged or created with/as a new record in the table. Unlike Oracle - Merge and SQL Server - Merge Snaps, MySQL - Merge Snap does not have Merge condition property. This is 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.
Queries produced by the Snap have the format:
More info on SQL Merges can be found at http://en.wikipedia.org/wiki/Merge_(SQL) and http://www.h2database.com/html/grammar.html#merge | |||||||||||||
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: |
| |||||||||||||
Settings | ||||||||||||||
Label | Required. 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 | 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.
Example: SYS | |||||||||||||
Table name | Required.The name of table to execute the insert on.
Default value: [None] | |||||||||||||
Number of retries | Specifies 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. Example: 3 Default value: 0
| |||||||||||||
Retry interval (seconds) | Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. Example: 10 Default value: 1
| |||||||||||||
|
|
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:
Insert excerpt | ||||||
---|---|---|---|---|---|---|
|