# MySql Bulk Loader

## <img src="https://hop.apache.org/manual/2.14.0/_images/transforms/icons/mysqlbulkloader.svg" alt="MySql Bulk loader transform Icon" data-size="line"> MySql Bulk Loader

### Description <a href="#description" id="description"></a>

The MySql Bulk Loader transform uses the copy command to load data as opposed to sending individual insert statements.

It will create a local file which will then be loaded using the `LOAD DATA` command. More information [here](https://dev.mysql.com/doc/refman/9.2/en/load-data.html).

| Hop Engine | <sup>✓</sup> |
| ---------- | ------------ |
| Spark      | ?            |
| Flink      | ?            |
| Dataflow   | ?            |

### Options

| Note | Bulk loading must be enabled on both server and client in the client connection add following option `allowLoadLocalInfile=true`. On the server side the following query should return "ON" `show global variables like 'local_infile';` |
| ---- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

#### General

| option                        | description                                                                                                              |
| ----------------------------- | ------------------------------------------------------------------------------------------------------------------------ |
| Connection                    | The database connection to use when bulk loading                                                                         |
| Target Schema                 | (Optional) The schema containing the table being loaded.                                                                 |
| Target Table                  | The name of the table being loaded.                                                                                      |
| Fifo file                     | Temporary file location                                                                                                  |
| Delimiter                     | Delimiter that determines the field.                                                                                     |
| Enclosure                     | You can specify an enclosure string which when placed around a value allows delimiters to be present in it.              |
| Escape character              | To include delimiter characters in values sometimes an escape string is used like backslash, double backslash and so on. |
| Character set (load command)  | The used character set in the load command (optional).                                                                   |
| Character set (file creation) | The used character set when creating the file (optional).                                                                |
| Bulk size (rows)              | This will split the data load in multiple chucks.                                                                        |
| Use replace clause            | With REPLACE, new rows that have the same value as a unique key value in an existing row replace the existing row.       |
| Use Ignore clause             | With IGNORE, new rows that duplicate an existing row on a unique key value are discarded.                                |
| Local data                    | If LOCAL is not specified, the file must be located on the server host.                                                  |

#### Fields

| option           | description                                                                                                                                                                                                                                                                                                 |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Table field      | Name of the field in the table.                                                                                                                                                                                                                                                                             |
| Stream field     | Name of the field in the stream.                                                                                                                                                                                                                                                                            |
| Field format OK? | <p>You can decide if the format should be kept (Don’t change formatting) or changed:</p><ul><li>Format as Date (yyyy-MM-dd)</li><li>Format as a timestamp (yyyy-MM-dd HH:mm:ss)</li><li>Format as Number (grouping symbol is "," - decimal is ".")</li><li>Escape enclosure characters when found</li></ul> |


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.primeur.com/data-shaper-1.21/knowing-the-data-shaper-designer/pipelines/transforms/mysqlbulkloader.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.