Overview
Table-to-table sync is the most common offline sync pipeline: it syncs table data from structured data sources (such as MySQL, PostgreSQL, Oracle, and SQL Server) to target tables in the DataBuddy data lakehouse on a schedule. This document uses MySQL-to-DataBuddy Catalog table sync as an example to describe the configuration process.
Prerequisites
Before you start this operation, make sure the following prerequisites are met:
You have activated the DataBuddy service and completed workspace initialization.
Compute resources for data ingestion (data ingestion type) have been created.
The target Catalog has been planned. If the target Schema or table does not exist, it can be automatically created through the one-click table creation feature of this task, which requires the Catalog creation permission.
Use Limits
Limit | Description |
Computing Resource | Only Platform Cluster computing resources are supported. |
Source Type | |
Target Type | Tables under the DataBuddy data lakehouse Catalog (Iceberg native tables) |
Split Key | Integer fields only; primary key or index columns recommended. |
Concurrency Number | When the value is greater than 1, a split key must be configured. |
Operation Steps
Step 1: Entering the Task Creation Page
2. Go to Data Integration > Task Management and click Create Sync Task.
Step 2: Filling in Basic Information
Enter the task name, sync method, owner, and description. For details, see the general configuration in Create a Single-Table Sync Task. Click Next.
Step 3: Configuring the Source
Parameter | Description | Required |
Data Source Type | Select the source database type, such as MySQL. | Yes |
Data Source Connection | Select a registered data source. In the drop-down list, click Add Data Connection to go to the creation page. | Yes |
Source Database | Supports two modes: Select Database and Enter Database Name. Parameter variables can be inserted. | Yes |
Source Data Table | Supports two modes: Select Data Table and Enter Data Table Name. Parameter variables can be inserted. | Yes |
Add Sharded Databases and Tables | Multiple data sources + tables can be added for the same source type. | No |
Split Key | Integer field. Required when concurrency > 1; otherwise, a prompt to complete it will appear upon saving. | Depends on Concurrency Number |
Filtering Condition | Standard WHERE condition (without the where keyword); supports time parameters and scheduling parameters. | No |
Advanced Settings | Data source-specific parameters (see the corresponding data source section for details) | No |
Tip
Data Source Connectivity Test: After you select a data source connection, "To be tested" is displayed. You can click Test to initiate a connectivity test. For details, see Data Source Connectivity Test. Database and Table Selection Mode
Mode | Action | Scenario |
Select Database/Table. | Literal exact selection | Synchronize fixed databases and tables. |
Enter Database Name/Table Name. | Can be matched through rule expressions or use parameter variables. | Scenarios with multiple sharded tables (such as table_[0-99]) or synchronization based on reading the current day's partition data |
Sharding Configuration
Click Add Sharding to add multiple data sources of the same source type. Each data source is configured independently with the data source connection, source database, and source table. Added sources can be deleted.
Requirement: The table schemas of all sources must be consistent.
Step 4: Configuring the Target
Parameter | Description | Required | Default Value |
Target Catalog | Select Target Catalog. | Yes | - |
Target Schema | Specify Schema under the selected Catalog. | Yes | - |
Target Table | Specify the target table. If it does not exist, click Create Target Table to create it immediately. | Yes | - |
Write Mode | | Yes | append |
Advanced Settings | Target-side extended parameters | No | - |
Creating the Target Table
Click the drop-down list, and then click Create Target Table to open the table creation dialog box.
1. Select the compute resource for executing the table creation statement (interactive cluster type). If the resource is not started, it will be started automatically after submission.
2. The platform generates a default table creation SQL statement based on the source Schema, which can be adjusted in the editor.
3. Click Submit:
On success, the dialog box closes and a message appears indicating that the target table ${TargetTableName} was created successfully.
On failure, the dialog box remains open and displays the specific failure cause. You can adjust the SQL statement and retry.
Note:
Some data sources do not support automatic generation of table creation DDL. You can manually write the table creation SQL statement.
Write Mode
Mode | Description |
append | Insert mode; write fails when a primary key conflict occurs. |
overwrite | Overwrite mode; when a primary key conflict occurs, delete the original row and then insert a new row. |
upsert | Update mode; when a primary key conflict occurs, update the specified fields. |
Step 5: Configuring Field Mapping
Field mapping is populated by default based on the same-name and same-row rules. The following operations are supported:
Operation | Description |
Same Name Mapping | Automatically align fields with the same name in the source/target. |
Same Row Mapping | Align in order of row numbers |
Clear | Clear all mappings |
Pin Mapped | Mapped fields are listed first. |
Moving | Drag the move icon before a field to move the row. |
Mapping Switch | Click the arrow switch in the Mapping column to toggle the mapping relationship of the row. |
Field Operations:
Operation | Description |
Add Field | Manually add a field mapping row. |
Delete Field | Manually delete a field mapping row. |
Adjust Field Type | Adjust the field type. Supported types: field, constant, function, and parameter. When field is selected, you can select the source field type, such as string. |
Settings for various field types:
Field: You can select a source field type, such as "string" or "int".
Constant: You can enter a constant value as the field name, such as "1" or "true".
Function: You can enter a database function as the field name, such as "concat(first_name,last_name)" or "sum(amount)".
Parameter: You can enter a parameter variable as the field name, such as "${plan_date}". Parameter definitions can be configured in Create a Single-Table Sync Task > Task Parameters.
Step 6: Configuring Runtime Settings
For details, see Create a Single-Table Sync Task > Run Settings.
Step 7: Debugging, Saving, and Publishing
For details, see Create a Single-Table Sync Task > Debug, Save, and Publish.
FAQs
Q: Why does it say "Set a split key first" when a task is saved?
A: When the concurrency is greater than 1, a split key must be configured. Add the split key in the Source & Target step, or set the concurrency to 1.
Q: Why does table creation fail with a permission denied error?
A: The current account does not have table creation permission on the target Catalog. Contact the workspace administrator or Catalog owner to grant the Manageable permission or the permission required for table creation. For details, see Member and Permission Management. Q: How do I ensure all source connections pass the connectivity test in a sharding scenario?
A: When multiple data sources are selected, test each connection separately.
Q: Does a debug run write actual data?
A: Yes. Debugging and publishing use the same data transfer link and write data to the target based on the current configuration. Before debugging, confirm the target table and write mode to avoid overwriting business data.
Q: How long does it take for task changes to take effect?
A: Saving only generates a new version and does not affect scheduling. You need to click Publish to set the current version as the published version. The new version will be used in the next workflow scheduling.
References