summary
Help Page for ETL Configuration of Data Setting to MySQL.
Setting items
STEP1 Basic settings
| item name | indispensable | default value | Contents |
|---|---|---|---|
| MySQL Connection Configuration | Yes | - | Select the previously registered Connection Configuration that has the necessary permissions for this ETL Configuration. |
| Database Name | Yes | - | Specify the name of the database where Data Destination MySQL resides. It is also possible to load a list of databases by pressing the button below the entry field. |
| table | Yes | - | Enter the name of the Data Destination table. If it does not exist, a new one will be created at the time of transfer. |
| transfer mode | Yes | insert | Please see below about transfer modes. |
| Queries to be executed before transfer | No | - | Enter the SQL you wish to execute before transferring data from MySQL. Note that this query will not be executed if the transfer mode "replace" is selected. |
| Query to be executed after transfer is complete | No | - | Enter the SQL you wish to execute after the data transfer from MySQL is complete. |
About transfer mode
| mode (musical mode, mode of probability distribution, state of physical system) | system | Details |
|---|---|---|
| insert | PS | Append to the table. First, a temporary table is created and data is populated into it, and finally, an INSERT is performed from the temporary table to the Data Destination table. Therefore, half-finished data will not remain in the Data Destination table when a transfer fails in midstream. |
| insert_direct | PS | Append to the table. No temporary tables, etc., are created, and data is submitted directly. Therefore, data may remain in a half-finished state when a transfer fails in the middle of the process. |
| truncate_insert | reassessing | The table will be washed. First, a temporary table is generated and data is submitted to it, Finally, an INSERT is performed from the temporary table to the Data Destination table. Note that the truncate is performed immediately before the INSERT, so it is a wash. Data Destination table index information is maintained. |
| replace | reassessing | The table will be washed. First, a temporary table is generated and data is submitted to it, Finally, the transfer is completed by swapping the table names in the Data Destination and Temporary tables. Data Destination table index information, etc. will be lost. |
| merge | UPSERT | UPSERT the table. First, a temporary table is created and all data is populated into it. Finally, the following query is executed to append & update the Data Destination table with the primary key by ON DUPLICATE KEY UPDATE. INSERT INTO <target_table> SELECT * FROM <intermediate_table_1> UNION ALL SELECT * FROM <intermediate_table_2> UNION ALL ... ON DUPLICATE KEY UPDATE ... |
| merge_direct | UPSERT | UPSERT the table. No temporary tables, etc., are created, and data is submitted directly. ON DUPLICATE KEY UPDATE for Data Destination table to append & update by primary key. |