Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This article outlines how to use the copy activity in a pipeline to copy data from and to MySQL.
This connector supports MySQL version 5.5, 5.6, 5.7, 8.0, 8.1 and 8.2.
Supported configuration
For the configuration of each tab under copy activity, go to the following sections respectively.
General
Refer to the General settings guidance to configure the General settings tab.
Source
The following properties are supported for MySQL under the Source tab of a copy activity.
The following properties are required:
- Connection: Select a MySQL connection from the connection list. If no connection exists, then create a new MySQL connection by selecting New.
- Use query: Select from Table or Query.
If you select Table:
Table: Specify the name of the table in the MySQL database to read data. Select the table from the drop-down list.
If you select Query:
Query: Specify the custom SQL query to read data. For example:
SELECT * FROM MyTable.
Under Advanced, you can specify the following fields:
- Additional columns: Add additional data columns to store source files' relative path or static value. Expression is supported for the latter.
Destination (Preview)
The following properties are supported for MySQL under the Destination tab of a copy activity.
The following properties are required:
Connection: Select a MySQL connection from the connection list. If no connection exists, then create a new MySQL connection.
Table: Select the name of the table in the MySQL database.
Under Advanced, you can specify the following fields:
Pre-copy script: Specify a SQL query for the copy activity to execute before writing data into MySQL in each run. You can use this property to clean up the preloaded data.
Write batch timeout: Specify the wait time for the batch insert operation to complete before it times out. The allowed value is timespan. The default value is
00:00:30.Write batch size: Specify the number of rows to insert into the MySQL table per batch. The allowed value is integer (number of rows). A new batche will be created when the current batch reaches the write batch size. The default value is
10000.Max concurrent connections: Specify the upper limit of concurrent connections established to the data store during the activity run. Specify a value only when you want to limit concurrent connections. The allowed value is integer.
Mapping
For Mapping tab configuration, see Configure your mappings under mapping tab.
Settings
For Settings tab configuration, go to Configure your other settings under settings tab.
Data type mapping for MySQL
When copying data from MySQL, the following mappings are used from MySQL data types to interim data types used by the service internally.
| MySQL data type | Interim service data type |
|---|---|
| BIGINT | Int64 |
| BIGINT UNSIGNED | UInt64 |
| BIT(1) | UInt64 |
| BIT(M), M>1 | UInt64 |
| BLOB | Byte[] |
| BOOL | Boolean (If TreatTinyAsBoolean=false, it is mapped as SByte. TreatTinyAsBoolean is true by default) |
| CHAR | String |
| DATE | Datetime |
| DATETIME | Datetime |
| DECIMAL | Decimal |
| DOUBLE | Double |
| DOUBLE PRECISION | Double |
| ENUM | String |
| FLOAT | Single |
| INT | Int32 |
| INT UNSIGNED | Int64 |
| INTEGER | Int32 |
| INTEGER UNSIGNED | UInt32 |
| JSON | String |
| LONG VARBINARY | Byte[] |
| LONG VARCHAR | String |
| LONGBLOB | Byte[] |
| LONGTEXT | String |
| MEDIUMBLOB | Byte[] |
| MEDIUMINT | Int32 |
| MEDIUMINT UNSIGNED | UInt32 |
| MEDIUMTEXT | String |
| NUMERIC | Decimal |
| REAL | Double |
| SET | String |
| SMALLINT | Int16 |
| SMALLINT UNSIGNED | UInt16 |
| TEXT | String |
| TIME | TimeSpan |
| TIMESTAMP | Datetime |
| TINYBLOB | Byte[] |
| TINYINT | SByte |
| TINYINT unsigned | Int16 |
| TINYTEXT | String |
| VARCHAR | String |
| YEAR | Int |
Table summary
The following tables contain more information about the copy activity in MySQL.
Source information
| Name | Description | Value | Required | JSON script property |
|---|---|---|---|---|
| Connection | Your connection to the source data store. | < your MySQL connection > | Yes | connection |
| Use query | The way to read data from MySQL. Apply Table to read data from the specified table or apply Query to read data using SQL queries. | • Table • Query |
Yes | / |
| Table | Name of the table in the MySQL database. | < table name > | No | tableName |
| Query | Use the custom SQL query to read data. For example: SELECT * FROM MyTable. |
< SQL queries > | No | query |
| Additional columns | Add additional data columns to store source files' relative path or static value. Expression is supported for the latter. | • Name • Value |
No | additionalColumns: • name • value |
Destination information (Preview)
| Name | Description | Value | Required | JSON script property |
|---|---|---|---|---|
| Connection | Your connection to the destination data store. | < your MySQL connection > | Yes | connection |
| Table | Name of the table in the MySQL database. | < table name > | Yes | tableName |
| Pre-copy script | A SQL query for the copy activity to execute before writing data into MySQL in each run. You can use this property to clean up the preloaded data. | < your pre-copy script > | No | preCopyScript |
| Write behavior | Defines the write behavior. | insert (default) | No | writeBehavior |
| Write batch size | The number of rows to insert into the MySQL table per batch. The allowed value is integer (number of rows). | < integer >10000 (default) |
No | writeBatchSize |
| Write batch timeout | The wait time for the batch insert operation to finish before it times out. The allowed value is timespan. | < timespan >00:00:30 (default) |
No | writeBatchTimeout |
| Max concurrent connections | The upper limit of concurrent connections established to the data store during the activity run. The allowed value is integer. | < integer > | No | maxConcurrentConnections |