Data Destination - MySQL
  • 11 Apr 2022
  • 2 Minutes to read
  • Dark
    Light

Data Destination - MySQL

  • Dark
    Light

Overview

Supported Protocol

Data Loader: Embulk

Settings

Step 1: General Settings

Field Name
Required
Default Value
Description
MySQL Connection Configuration Yes - From the MySQL Connection Configuration created in advance, select the one with the required permissions for this transfer.
If you have not previously created any Connection Configurations, click the Add Connection Configuration button to set up a new one.
Database Yes - Enter the MySQL database name that you wish to transfer to.
By pressing the Load Database List button, you can load a list of MySQL database names used to auto-complete the field.
Table Yes - Enter the table name for the destination.
If a table with the name you entered does not exist at the destination, a new one will be created.
Transfer Mode Yes insert Please refer to the Transfer Modes section below for information regarding each mode.
Query Before Transfer No - Write an SQL query to execute before the data is transferred to MySQL.
Note: If you choose the transfer mode replace, the query cannot be executed.
Query After Transfer No - Write an SQL query to execute after the data has been transferred to MySQL.

Transfer Modes

Mode
Method
Details
insert APPEND APPEND a table with the insert mode.
The transfer data is first loaded into a temporary table generated by trocco, then added to the table at the destination using the insert mode.
insert_direct APPEND APPEND a table with the insert_direct mode.
Directly load data without creating a temporary table.
If the transfer happens to fail during execution, there is a chance that incomplete data will be left in the destination.
truncate_insert OVERWRITE OVERWRITE a table with the truncate_insert mode.
The transfer data is first loaded into a temporary table generated by trocco, then added to the table at the destination using the insert mode.
Note: before insert, truncate will execute, overwriting the existing table.
replace OVERWRITE OVERWRITE a table with the replace mode.
At first, the transfer data is loaded into a temporary table generated by trocco.
If successful, the target table is dropped and the name of the temporary table is changed to the name of the destination table.
The destination table index will be lost.
merge UPSERT UPSERT into a table with the merge_direct mode.
At first the transfer data is loaded into a temporary table generated by trocco.
If successful, the query shown below will be run.
INSERT INTO <target_table> SELECT * FROM <intermediate_table_1> UNION ALL SELECT * FROM <intermediate_table_2> UNION ALL ... ON DUPLICATE KEY UPDATE ...
If a primary key of a record in the intermediate table already exists at the destination table, the destination table record will be updated.
If a primary key of a record does not exist, the record will be inserted into the destination table.
merge_direct UPSERT UPSERT into a table with the merge_direct mode.
In this mode, without creating a temporary table, the data will be inserted directly into the destination table.
If a primary key of a record in the intermediate table already exists in the destination table, the destination table record will be updated.
If a primary key of a record does not exist, the record will be inserted into the destination table.

Was this article helpful?