MySQLDataWriter
We assume that you have already learned what is described in:
If you want to find the right Writer for your purposes, see Writers Comparison.
Short Summary
MySQLDataWriter is a high-speed MySQL table loader. Uses MySQL native client.
| Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL |
|---|---|---|---|---|---|---|---|
| MySQLDataWriter | database | 0-1 | 0-1 | no | no | no | no |
Abstract
MySQLDataWriter quickly loads data into a database table using native MySQL database client.
It reads data either from the input port or from an input file.
You can attach the optional output port and read records which have been reported as rejected.
Reading from input port (input port connected) dumps the data into a temporary file which is then
used by mysql utility. You can set the temporary file explicitly by setting the Loader input file attribute or
leave it blank to use default.
Reading from a file (no input connected) uses "Loader input file" attribute as a path to your data file.
The attribute is mandatory in this case. The file needs to be in a format recognized by mysql utility (see MySQL LOAD DATA).
This component executes MySQL native command-line client (bin/mysql or bin/mysql.exe).
The client must be installed on the same machine as the graph is running on.
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | 1) | Records to be loaded into the database | Any |
| Output | 0 | no | For information about incorrect records | Error Metadata for MySQLDataWriter2) |
Legend:
1): If no file containing data for loading (Loader input file) is specified, input port must be connected.
2): Error Metadata cannot use Autofilling Functions.
Table 46.4. Error Metadata for MySQLDataWriter
| Field number | Field name | Data type | Description |
|---|---|---|---|
| 0 | <any_name1> | integer | Number of incorrect record (records are numbered starting from 1) |
| 1 | <any_name2> | integer | number of incorrect column |
| 2 | <any_name3> | string | Error message |
MySQLDataWriter Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| Path to mysql utility | yes | Name of mysql utility, including path. Must be installed and configured on the same machine where Clover runs. Mysql command line tool must be available. | |
| Host | Host where database server is located. | localhost (default) | other host | |
| Database | yes | Name of the database into which the records should be loaded. | |
| Database table | yes | Name of the database table into which the records should be loaded. | |
| User name | yes | Database user. | |
| Password | yes | Password for database user. | |
| Advanced | |||
| Path to control script | Name of command file containing the LOAD DATA
INFILE statement, including path. See Path to Control Script for more
information. | ||
| Lock database table | By default, database is not locked and multiuser access
is allowed. If set to true, database table
is locked to ensure exclusive access and possibly faster
loading. | false (default) | true | |
| Ignore rows | Number of rows of data file that should be skipped. By default, no records are skipped. Valid only for input file with data. | 0 (default) | 1-N | |
| Column delimiter | Delimiter used for each column in data. Field values must not include this delimiter as their part. By default, tabulator is used. | \t (default) | other character | |
| Loader input file | 1) | Name of input file to be loaded, including path. See Loader input file for more information. | |
| Parameters | All parameters that can be used as parameters by
load method. These values are contained in
a sequence of pairs of the following form:
key=value, or key only
(if the key value is the boolean
true) separated from each other by
semicolon, colon, or pipe. If the value of any parameter
contains the delimiter as its part, such value must be double
quoted. | ||
Legend:
1) If input port is not connected, Loader input file must be specified and contain data. See Loader input file for more information.
Advanced Description
Path to Control Script
Name of command file containing the LOAD DATA
INFILE statement (See MySQL LOAD DATA), including path.
If it is not set, a command file is created in Clover temporary directory and it is deleted after the load finishes.
If it is set, but the specified command file does not exist, temporary command file is created with the specified name and path and it is not deleted after the load finishes.
If it is set and the specified command file exists, this file is used instead of command file created by Clover.
Loader input file
Name of input file to be loaded, including path.
If it is not set, a loader file is created in Clover or OS temporary directory (on Windows) or stdin is used instead of temporary file (on Unix). The file is deleted after the load finishes.
If it is set, specified file is created. It is not deleted after data is loaded and it is overwritten on each graph run.
If input port is not connected, this file must be specified, must exist and must contain data that should be loaded into database. The file is not deleted nor overwritten.