Bulk load into MySQL

Description

The Bulk load into MySQL action loads data from a text file into a MySQL table.

This action uses the MySQL LOAD DATA command.

Options

Option Description

Option	Description
Action name	The name of the workflow action.
Connection	The connection to the MySQL database to use.
Target schema	The schema of the table to load.
Target table name	The name of the table to load into.
Source file name	The name of the file to load.
Local	Checked (default): Hop reads the file and sends it to MySQL (`LOAD DATA LOCAL`). The connection needs the option `allowLoadLocalInfile=true`, and the server needs `local_infile` enabled: without them the load fails. Unchecked: the MySQL server reads the file, so the path must exist on the database server.
Priority	The priority to load the file with. The available options are: NORMAL (default): no priority is set on the `LOAD DATA` command LOW PRIORITY: With the LOW_PRIORITY modifier, execution of the LOAD DATA statement is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE). CONCURRENT: With the CONCURRENT modifier and a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. This modifier affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.
Fields terminated by	The field terminator to use.
Fields enclosed by	The field enclosure to use.
Fields escaped by	The escape character to use.
Lines started by	The line start string to use.
Lines terminated by	The line termination string to use.
Fields	The fields to load, separated by comma (,).
Replace data	Checked (default): a row whose key already exists in the table replaces the existing row. Unchecked: the existing row is kept, and the row from the file is skipped.
Ignore the…first lines	Ignores this number of lines at the start of the file, for example a header line.
Add file to result filenames	Adds the source file to the result files, for use in a next workflow action.

Action name

The name of the workflow action.

Connection

The connection to the MySQL database to use.

Target schema

The schema of the table to load.

Target table name

The name of the table to load into.

Source file name

The name of the file to load.

Local

Checked (default): Hop reads the file and sends it to MySQL (LOAD DATA LOCAL). The connection needs the option allowLoadLocalInfile=true, and the server needs local_infile enabled: without them the load fails. Unchecked: the MySQL server reads the file, so the path must exist on the database server.

Priority

The priority to load the file with. The available options are:

NORMAL (default): no priority is set on the LOAD DATA command
LOW PRIORITY: With the LOW_PRIORITY modifier, execution of the LOAD DATA statement is delayed until no other clients are reading from the table. This affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).
CONCURRENT: With the CONCURRENT modifier and a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other threads can retrieve data from the table while LOAD DATA is executing. This modifier affects the performance of LOAD DATA a bit, even if no other thread is using the table at the same time.

Fields terminated by

The field terminator to use.

Fields enclosed by

The field enclosure to use.

Fields escaped by

The escape character to use.

Lines started by

The line start string to use.

Lines terminated by

The line termination string to use.

Fields

The fields to load, separated by comma (,).

Replace data

Checked (default): a row whose key already exists in the table replaces the existing row. Unchecked: the existing row is kept, and the row from the file is skipped.

Ignore the…first lines

Ignores this number of lines at the start of the file, for example a header line.

Add file to result filenames

Adds the source file to the result files, for use in a next workflow action.

Result

What this action passes on to the next action. See Action results for how results travel through a workflow.

Result	Value
True	The file was loaded, with 0 errors.
False	The file doesn’t exist, the table doesn’t exist, Source File name is empty, the connection is unknown or can’t be opened, or MySQL refuses the load. The number of errors is 1.
Result files	The source file is added when Add file to result filenames is checked and the load succeeded. Otherwise passed on unchanged.
Result rows	Passed on unchanged.

Result

Value

True

The file was loaded, with 0 errors.

False

The file doesn’t exist, the table doesn’t exist, Source File name is empty, the connection is unknown or can’t be opened, or MySQL refuses the load. The number of errors is 1.

Result files

The source file is added when Add file to result filenames is checked and the load succeeded. Otherwise passed on unchanged.

Result rows

Passed on unchanged.

When the table doesn’t exist, the reason is only logged at the Detailed log level: at the Basic level, the log shows a false action without an error message.