Configuring a Job for Synchronizing Data from Oracle to Doris (Internal Test)
Supported Source and Destination Database Versions
Source Database |
Destination Database |
---|---|
Oracle database (versions 10, 11, 12, and 19) |
Doris (Doris1.2 and Doris2.0) |
Database Account Permissions
Before you use DataArts Migration for data synchronization, ensure that the source and destination database accounts meet the requirements in the following table. The required account permissions vary depending on the synchronization task type.
Type |
Required Permissions |
---|---|
Source database connection account |
The account must have the permissions to archive logs, query tables, and parse logs of the Oracle database. For details about how to grant the permissions, see How Do I Grant the Log Archiving, Query, and Parsing Permissions of an Oracle Data Source? |
Destination database connection account |
The account must have the following permissions for each table in the destination database: INSERT, SELECT, UPDATE, DELETE, CONNECT, and CREATE. |

- You are advised to create independent database accounts for DataArts Migration task connections to prevent task failures caused by password modification.
- After changing the account passwords for the source or destination databases, modify the connection information in Management Center as soon as possible to prevent automatic retries after a task failure. Automatic retries will lock the database accounts.
Supported Synchronization Objects
The following table lists the objects that can be synchronized using different links in DataArts Migration.
Type |
Note |
---|---|
Synchronization objects |
|
Notes
In addition to the constraints on supported data sources and versions, connection account permissions, and synchronization objects, you also need to pay attention to the notes in the following table.
Type |
Constraint |
---|---|
Database |
|
Usage |
General:
Full synchronization phase: During task startup and full data synchronization, do not perform DDL operations on the source database. Otherwise, the task may fail. Incremental synchronization phase:
Troubleshooting: If any problem occurs during task creation, startup, full synchronization, incremental synchronization, or completion, rectify the fault by referring to FAQs. |
Other |
|
Procedure
This section uses real-time synchronization from Oracle to Doris as an example to describe how to configure a real-time data migration job. Before that, ensure that you have read the instructions described in Check Before Use and completed all the preparations.
- Create a real-time migration job by following the instructions in Creating a Real-Time Migration Job and go to the job configuration page.
- Select the data connection type. Select Oracle for Source and Doris for Destination.
Figure 1 Selecting the data connection type
- Select a job type. The default migration type is Real-time. The migration scenario is Entire DB.
Figure 2 Setting the migration job type
- Configure network resources. Select the created Oracle and Doris data connections and the migration resource group for which the network connection has been configured.
Figure 3 Selecting data connections and a migration resource group
If no data connection is available, click Create to go to the Manage Data Connections page of the Management Center console and click Create Data Connection to create a connection. For details, see Configuring DataArts Studio Data Connection Parameters.
If no migration resource group is available, click Create to create one. For details, see Buying a DataArts Migration Resource Group Incremental Package.
- Configure source parameters.
Select the Oracle databases and tables to be migrated.Figure 4 Selecting databases and tables
Both databases and tables can be customized. You can select one database and one table, or multiple databases and tables.
- Configure destination parameters.
- Set Database and Table Matching Policy.
- Database Matching Policy
- Same name as the source database: Data will be synchronized to the Doris database with the same name as the source PostgreSQL database.
- Custom: Data will be synchronized to the Doris database you specify.
- Table Matching Policy
- Same name as the source table: Data will be synchronized to the Doris table with the same name as the source PostgreSQL table.
- Custom: Data will be synchronized to the Doris table you specify.
Figure 5 Database and table matching policy in the entire database migration scenario
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}.
- Database Matching Policy
- Set Doris parameters.
You can configure the advanced parameters in the following table to enable some advanced functions.
Table 5 Doris advanced parameters Parameter
Type
Default Value
Unit
Description
doris.request.connect.timeout.ms
int
30000
ms
Doris connection timeout interval
doris.request.read.timeout.ms
int
30000
ms
Doris read timeout interval
doris.request.retries
int
3
-
Number of retries upon a Doris request failure
sink.max-retries
int
3
-
Maximum number of retries upon a data writing failure
sink.batch.interval
string
1s
h/min/s
Interval at which an asynchronous thread writes data
sink.enable-delete
boolean
true
-
Whether to enable the deletion function. If this function is disabled, data deleted from the source will not be deleted from the destination.
sink.batch.size
int
20000
-
Maximum number of rows that can be written (inserted, updated, or deleted) at a time
sink.batch.bytes
long
10485760
bytes
Maximum number of bytes that can be written (inserted, updated, or deleted) at a time
logical.delete.enabled
boolean
false
-
Whether to enable logical deletion. If this function is enabled, the destination must contain the deletion flag column. When data is deleted from the source database, the corresponding data in the destination database will not be deleted. Instead, the deletion flag column is set to true, indicating that the data is not contained at the source.
logical.delete.column
string
logical_is_deleted
-
Name of the logical deletion column. The default value is logical_is_deleted. You can customize the value.
sink.keyby.mode
string
pk
-
Partitioning mode when concurrent writes are performed on Doris. Default value: pk (primary key). If the source is Kafka, and it does not have a primary key, select table to partition data by table name.
doris.sink.flush.tasks
int
1
-
Number of concurrent flushes of a single TaskManager
sink.properties.format
string
json
-
Data format used by Stream Load. The value can be json or csv.
sink.properties.Content-Encoding
string
-
-
Compression format of the HTTP header message body. Currently, only CSV files can be compressed, and the .gzip format is supported.
sink.properties.compress_type
string
-
-
File compression format. Currently, only CSV files can be compressed. The .gz, .lzo, .bz2, .lz4, .lzop, and .deflate compression formats are supported.
- Set Database and Table Matching Policy.
- Refresh and check the mapping between the source and destination tables. In addition, you can modify table attributes, add additional fields, and use the automatic table creation capability to create tables in the destination Doris database.
Figure 6 Mapping between source and destination tables
- Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination Doris 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 Doris table
- Field Type: type of the new field in the destination Doris table
- (Optional) Field Type Length: length of the new field type in the destination Doris table
- Field Value: value source of the new field in the destination Doris table
Table 6 Additional field value obtaining mode Type
Example
Constant
Any character
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 data change time: source.event.timestamp
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.
- Automatic table creation: Click 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.
- 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.
- Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination Doris 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.
- Configure task parameters.
Table 7 Task parameters Parameter
Description
Default Value
Execution Memory
Memory allocated for job execution, which automatically changes with the number of CPU cores
8GB
CPU Cores
Value range: 2 to 32
For each CPU core added, 4 GB execution memory and one concurrency are automatically added.
2
Maximum Concurrent Requests
Maximum number of jobs that can be concurrently executed. This parameter does not need to be configured and automatically changes with the number of CPU cores.
1
Auto Retry
Whether to enable automatic retry upon a job failure
No
Maximum Retries
This parameter is displayed when Auto Retry is set to Yes.
1
Retry Interval (Seconds)
This parameter is displayed when Auto Retry is set to Yes.
120s
Write Dirty Data
Whether to record dirty data. By default, dirty data is not recorded. If there is a large amount of dirty data, the synchronization speed of the task is affected.
- No: Dirty data is not recorded. This is the default value.
Dirty data is not allowed. If dirty data is generated during the synchronization, the task fails and exits.
- Yes: Dirty data is allowed, that is, dirty data does not affect task execution.
When dirty data is allowed and its threshold is set:
- If the generated dirty data is within the threshold, the synchronization task ignores the dirty data (that is, the dirty data is not written to the destination) and is executed normally.
- If the generated dirty data exceeds the threshold, the synchronization task fails and exits.
NOTE:
Criteria for determining dirty data: Dirty data is meaningless to services, is in an invalid format, or is generated when the synchronization task encounters an error. If an exception occurs when a piece of data is written to the destination, this piece of data is dirty data. Therefore, data that fails to be written is classified as dirty data.
For example, if data of the VARCHAR type at the source is written to a destination column of the INT type, dirty data cannot be written to the migration destination due to improper conversion. When configuring a synchronization task, you can configure whether to write dirty data during the synchronization and configure the number of dirty data records (maximum number of error records allowed in a single partition) to ensure task running. That is, when the number of dirty data records exceeds the threshold, the task fails and exits.
No
Dirty Data Policy
This parameter is displayed when Write Dirty Data is set to Yes. The following policies are supported:
- Do not archive: Dirty data is only recorded in job logs, but not stored.
- Archive to OBS: Dirty data is stored in OBS and printed in job logs.
Do not archive
Write Dirty Data Link
This parameter is displayed when Dirty Data Policy is set to Archive to OBS.
Only links to OBS support dirty data writes.
N/A
Dirty Data Directory
OBS directory to which dirty data will be written
N/A
Dirty Data Threshold
This parameter is only displayed when Write Dirty Data is set to Yes.
You can set the dirty data threshold as required.
NOTE:- The dirty data threshold takes effect for each concurrency. For example, if the threshold is 100 and the concurrency is 3, the maximum number of dirty data records allowed by the job is 300.
- Value -1 indicates that the number of dirty data records is not limited.
100
Custom attributes
You can add custom attributes to modify some job parameters and enable some advanced functions. For details, see Job Performance Optimization.
N/A
- No: Dirty data is not recorded. This is the default value.
- Submit and run the job.
After configuring the job, click Submit in the upper left corner to submit the job.
Figure 7 Submitting the jobAfter submitting the job, click Start on the job development page. In the displayed dialog box, set required parameters and click OK.
Figure 8 Starting the jobTable 8 Parameters for starting the job Parameter
Description
Synchronous Mode
- Incremental Synchronization: Incremental data synchronization starts from a specified time point.
- Full and incremental synchronization: All data is synchronized first, and then incremental data is synchronized in real time.
Time
This parameter must be set for incremental synchronization, and it specifies the start time of incremental synchronization.
NOTE:If you set a time that is earlier than the earliest CDC log time, the latest log time is used.
- Monitor 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 9 Monitoring the job
Performance Optimization
If the synchronization speed is too slow, rectify the fault by referring to Job Performance Optimization.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot