Configuring a Job for Synchronizing Data from MySQL to LakeFormation

• Click Source Configuration. The system will test the connectivity of the entire migration job.
• Click Test in the source and destination and migration resource group.
  

  If the network connectivity is abnormal, see How Do I Troubleshoot a Network Disconnection Between the Data Source and Resource Group?

Select the MySQL databases and tables to be migrated.

Figure 3 Selecting databases and tables

You can also click Import to import data from a file that you have edited based on the template. You can download the template as prompted on the console. Only a XLSX file no larger than 10 MB can be imported, and a maximum of 500 tables can be imported at a time. To ensure correct parsing, edit the file in strict accordance with the template format.

• Configure the mapping between the source database and table and the destination database and table.
  • Catalog: Select the required catalog.
  • Database Matching Policy
    • Same name as the source database: Data will be synchronized to the destination LakeFormation database with the same name as the source MySQL database.
    • Custom: Data will be synchronized to the destination LakeFormation table you specify.
  • Table Matching Policy
    • Same name as the source table: Data will be synchronized to the LakeFormation table with the same name as the source MySQL table.
    • Custom: Data will be synchronized to the LakeFormation table you specify.
      Figure 4 Configuring the mapping between the source database and table and the destination database and table
      
      

      When you customize a matching policy, you can use built-in variables #{source_db_name} and #{source_table_name} to identify the source database name and table name. The table matching policy must contain #{source_table_name}.

• Configure the destination table.
  • Table Type: Select the required table type, for example, iceberg.
  • Store Format: Select the required table file format, for example, parquet.
  • Write Mode
    • copy-on-write
    • merge-on-read
  • Configure Table Attribute

    Custom table attributes: You can configure some advanced functions of the Iceberg table by setting parameters. To add table attributes, click Edit Table Attribute in the Operation column. For details about the table attributes supported by Iceberg, see the table attribute parameters on the official website.

Figure 5 Mapping between source and destination tables

• Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination LakeFormation table. For a new table, you can add additional fields to the existing fields in the source table. You can customize the field name, select the field type, and enter the field value.
  • Field Name: name of the new field in the destination LakeFormation table
  • Field Type: type of the new field in the destination LakeFormation table
  • Field Value: value source of the new field in the destination LakeFormation table

    Table 5 Additional field value obtaining mode

    Type	Example
    Constant	Digits, letters, and special characters are supported. Color emoticons may cause a job submission failure.
    Built-in variable	Source host IP address: source.host Source schema name: source.schema Source table name: source.table Destination schema name: target.schema Destination table name: target.table
    Source table field	Any field in the source table Do not change the name of the source table field when the job is running. Otherwise, the job may be abnormal.
    UDF	substring(#col, pos[, len]): obtains a substring of a specified length from the source column name. The substring range is [pos, pos+len). date_format(#col, time_format[, src_tz, dst_tz]): formats the source column name based on a specified time format. The time zone can be converted using src_tz and dst_tz. now([tz]): obtains the current time in a specified time zone. if(cond_exp, str1, str2): returns str1 if the condition expression cond_exp is met and returns str2 otherwise. concat(#col[, #str, ...]): concatenates multiple parameters, including source columns and strings. from_unixtime(#col[, time_format]): formats a Unix timestamp based on a specified time format. unix_timestamp(#col[, precision, time_format]): converts a time into a Unix timestamp of a specified time format and precision. time_format must be the same as that in the source data.

• Edit table metadata: Click Edit Table Metadata in the Operation column to edit the metadata of a single table, mainly including the column information and attributes of the destination table. After editing the information, you can save the changes and create the table.
  Figure 6 Editing table metadata
  

  You can configure table partitions in the mapping configuration.

  Table 6 Table partition parameters that can be configured

  Transform Name	Description	Source Type	Result Type
  identity	Keeps the original value.	geometry except for geography and variant	Source type
  bucket[N]	Hashes the value and takes the result modulo N.	int,long,decimal,date,time,timestamp,timestamptz,timestamp_ns,timestamptz_ns,string,uuid,fixedbinary	int
  truncate[W]	Truncates the value to width W.	int,long,decimal,string,binary	Source type
  year	Extracts the year from a date or timestamp, for example, a year starting from 1970	date, timestamp, timestamptz, timestamp_ns, timestamptz_ns	int
  month	Extracts the month from a date or timestamp, for example, a month starting from 1970-01-01	date, timestamp, timestamptz, timestamp_ns, timestamptz_ns	int
  day	Extracts a date or timestamp, for example, a date starting from 1970-01-01	date, timestamp, timestamptz, timestamp_ns, timestamptz_ns	int
  hour	Extracts the hour from a timestamp, with 1970-01-01 00:00:00 as the basis.	timestamp,timestamptz,timestamp_ns,timestamptz_ns	int

  For details about Iceberg partitions, see the official website. You can configure table attributes. For details about the table attributes supported by Iceberg, see the official website.

• Automatic table creation: Click Enable Auto Table Creation to automatically create tables in the destination database based on the configured mapping policy. After the tables are created, Existing table is displayed for them.
  Figure 7 Automatic Table Creation
  

  

  • DataArts Migration supports only automatic table creation. You need to manually create databases and schemas at the destination before using this function.
  • For details about the field type mapping for automatic table creation, see Field Type Mapping.

Real-time migration jobs can synchronize data manipulation language (DML) operations, such as adding, deleting, and modifying data, as well as some table structure changes using the data definition language (DDL). You can set the processing policy for a DDL operation to Normal processing, Ignore, or Error.

• Normal processing: When a DDL operation on the source database or table is detected, the operation is automatically synchronized to the destination.
• Ignore: When a DDL operation on the source database or table is detected, the operation is ignored and not synchronized to the destination.
• Error: When a DDL operation on the source database or table is detected, the migration job throws an exception.
  Figure 8 DDL configuration
  

After configuring the job, click Submit in the upper left corner to submit the job.

Figure 9 Submitting the job

After submitting the job, click Start on the job development page. In the displayed dialog box, set required parameters and click OK.

Figure 10 Starting the job

On the job development page, click Monitor to go to the Job Monitoring page. You can view the status and log of the job, and configure alarm rules for the job. For details, see Real-Time Migration Job O&M.

Figure 11 Monitoring the job