Configuring a Job for Synchronizing Data from SQL Server to MRS Hudi

Supported Source and Destination Database Versions

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.

Table 2 Database account permissions

Type	Required Permissions
Source database account	sysadmin or view server state permissions, and db_datareader or db_owner permissions of the database to be synchronized Enable CDC for a database and a table. Enable CDC for a database. USE YourDatabaseName; EXEC sys.sp_cdc_enable_db; -- Check whether CDC is enabled for a database. SELECT is_cdc_enabled, name FROM sys.databases WHERE name = 'YourDatabaseName' Enable CDC for a table. EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', -- Schema @source_name = N'YourTable',-- Table name @role_name = NULL,-- (Optional) CDC access role name @supports_net_changes = 0; -- Check whether CDC is enabled for the table. SELECT name,is_tracked_by_cdc FROM sys.tables WHERE name = 'YourTable'; The following permissions for the SQL Server must be granted to the user configured in the data connection: Grant the CONNECT and VIEW DATABASE STATE permissions to the user. USE YourDatabaseName; GRANT CONNECT, VIEW DATABASE STATE TO [YourUserName]; Grant the SELECT permissions on the CDC schema to the user. USE YourDatabaseName; GRANT SELECT ON SCHEMA::[cdc] TO [YourUserName]; Grant the SELECT permissions on the table to the user. USE YourDatabaseName; GRANT SELECT ON OBJECT::[YourSchema].[YourTable] TO [YourUserName];
Destination database account	The MRS user must have read and write permissions for the Hadoop and Hive components. You are advised to assign the roles and user groups shown in the following figure to the MRS user. Figure 1 Minimal permissions for MRS Hudi For details, see MRS Cluster User Permission Model.

Supported Synchronization Objects

The following table lists the objects that can be synchronized using different links in DataArts Migration.

Important 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.

Procedure

This section uses real-time synchronization from Microsoft SQL Server to MRS Hudi 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.

1. Create a real-time migration job by following the instructions in Creating a Real-Time Migration Job and go to the job configuration page.
2. Select the data connection type. Select SQLServer for Source and Hudi for Destination.
   Figure 3 Selecting the data connection type
   

3. Select a job type. The default migration type is Real-time. The migration scenario is Entire DB.
   Figure 4 Setting the migration job type
   
   NOTE:

   For details about synchronization scenarios, see Synchronization Scenarios.

4. Configure network resources. Select the created SQL Server and MRS Hudi data connections and the resource group for which the network connection has been configured.
   Figure 5 Selecting data connections and a resource group
   
   NOTE:

   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 resource group is available, click Create to create one. For details, see Buying a DataArts Migration Resource Group Incremental Package.

5. Check the network connectivity. After the data connections and resource group are configured, perform the following operations to check the connectivity between the data sources and the resource group.
   • Click Source Configuration. The system will test the connectivity of the entire migration job.
   • Click Test in the source and destination and resource group.
     NOTE:

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

6. Configure source parameters.
   • Select the SQL Server databases and tables to be migrated.
     Figure 6 Selecting databases and tables
     

     Both databases and tables can be customized. You can select one database and one table, or multiple databases and tables.

7. Configure destination parameters.
   • Set Database and Table Matching Policy.

     For details about the matching policy between source and destination databases and tables in each synchronization scenario, see the following table.

     Table 5 Database and table matching policy

     Synchronization Scenario

     Configuration Method

     Entire DB

     Database Matching Policy Same name as the source database: Data will be synchronized to the Hudi database with the same name as the source SQL Server schema. Custom: Data will be synchronized to the Hudi database you specify. Table Matching Policy Same name as the source table: Data will be synchronized to the Hudi table with the same name as the source SQL Server schema. Custom: Data will be synchronized to the Hudi table you specify. Figure 7 Database and table matching policy in the entire database migration scenario NOTE: 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}.

   • Set Hudi parameters.

     For details, see the following table.

     Figure 8 Hudi destination parameters
     

     Table 6 Hudi destination parameters

     Parameter	Default Value	Unit	Description
     Data Storage Path	N/A	N/A	Warehouse path when tables are automatically created in Hudi. A subdirectory is created in the warehouse path for each table. You can enter an HDFS or OBS path. The path format is as follows: OBS path: obs://bucket/warehouse HDFS path: /tmp/warehouse
     Global Configuration of Hudi Table Attributes	N/A	N/A	Some advanced functions can be configured using parameters. For details, see Hudi advanced parameters.
     Compaction Job	N/A	N/A	An independent SparkSQL job. If this parameter is not specified, Flink performs compaction.

     Table 7 Hudi advanced parameters

     Parameter	Type	Default Value	Unit	Description
     index.type	string	BLOOM	N/A	Index type of the Hudi table BLOOM and BUCKET indexes are supported. If a large amount of data need to be migrated, BUCKET indexes are recommended for better performance.
     hoodie.bucket.index.num.buckets	int	256	Count	Number of buckets within a Hudi table partition NOTE: When using Hudi BUCKET tables, you need to set the number of buckets for a table partition. The number of buckets affects the table performance. Number of buckets for a non-partitioned table = MAX(Data volume of the table (GB)/2 GB x 2, rounded up, 4) Number of buckets for a partitioned table = MAX(Data volume of a partition (GB)/2 GB x 2, rounded up, 1) Pay attention to the following: The total data volume of a table, instead of the compressed size, is used. Setting an even number of buckets is recommended. The minimum number of buckets should be 4 for a non-partitioned table and 1 for a partitioned table.
     changelog.enabled	boolean	false	N/A	Whether to enable the Hudi ChangeLog function. If this function is enabled, the migration job can output DELETE and UPDATE BEFORE data.
     logical.delete.enabled	boolean	true	N/A	Whether to enable logical deletion. If the ChangeLog function is enabled, logical deletion must be disabled.
     hoodie.write.liststatus.optimized	boolean	true	N/A	Whether to enable liststatus optimization when log files are written. If the migration job involves large tables or a large amount of partitioned data, the list operation is time-consuming during startup, which may cause job startup timeout. You are advised to disable this function.
     hoodie.index.liststatus.optimized	boolean	false	N/A	Whether to enable liststatus optimization during data locating. If the migration job involves large tables or a large amount of partitioned data, the list operation is time-consuming during startup, which may cause job startup timeout. You are advised to disable this function.
     compaction.async.enabled	boolean	true	N/A	Whether to enable asynchronous compaction. The compaction operation affects the writing performance of real-time jobs. If you use an external compaction operation, you can set this parameter to false to disable compaction for real-time processing migration jobs.
     compaction.schedule.enabled	boolean	true	N/A	Whether to generate compaction plans. Compaction plans must be generated by this service and can be executed by Spark.
     compaction.delta_commits	int	5	Count	Frequency of generating compaction requests. Lowering the compaction request generation frequency reduces the compaction frequency and improves job performance. If there is a small volume of incremental data to be synchronized to Hudi, you can set a larger value for this parameter. NOTE: For example, if this parameter is set to 40, a compaction request is generated every 40 commits. Since DataArts Migration generates a commit every minute, the interval between compaction requests is 40 minutes.
     clean.async.enabled	boolean	true	N/A	Whether to clear data files of historical versions
     clean.retain_commits	int	30	Count	Number of recent commits to retain. Data files related to these commits will be retained for a period calculated by multiplying the number of specified commits by the interval between commits. You are advised to set this parameter to twice the value of compaction.delta_commits. NOTE: For example, if this parameter is set to 80 and since DataArts Migration generates a commit every minute, data files related to commits generated 80 minutes earlier are cleaned, and data files related to the recent 80 commits are retained.
     hoodie.archive.automatic	boolean	true	N/A	Whether to age Hudi commit files
     archive.min_commits	int	40	Count	Number of recent commits to keep when historical commits are archived to log files You are advised to set this parameter to one greater than clean.retain_commits. NOTE: For example, if this parameter is set to 81, the files related to the recent 81 commits are retained when an archive operation is triggered.
     archive.max_commits	int	50	Count	Number of commits that triggers an archive operation You are advised to set this parameter to 20 greater than archive.min_commits. NOTE: For example, if the parameter is set to 101, an archive operation is triggered when the files of 101 commits are generated.

     NOTE:

     • To achieve optimal performance for the migration job, you are advised to use an MOR table that uses Hudi BUCKET indexes and configure the number of buckets based on the actual data volume.
     • To ensure the stability of the migration job, you are advised to split the Hudi Compaction job into Spark jobs and execute them by MRS, and enable compaction plans to be generated for this migration job. For details, see How Do I Configure a Spark Periodic Task for Hudi Compaction?

8. 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 Hudi database.
   Figure 9 Mapping between source and destination tables
   
   • Synchronization Primary Key

     The primary key must be set for Hudi tables. If the source table has no primary key, you must manually select the primary key during field mapping.

   • Edit Table Attribute

     Click Edit Table Attributes in the Operation column to configure Hudi table attributes, including the table type, partition type, and custom attributes.

     Figure 10 Configuring the Hudi table attributes
     
     • Table Type: Hudi table type. Select MERGE_ON_READ or COPY_ON_WRITE.
     • Partition Type: partition type of the Hudi table. Select No partition, Time, or Custom.
       NOTE:

       For Time, you need to specify a source table name and select a time conversion format.

       For example, you can specify the source table name src_col_1 and select a time conversion format, for example, day(yyyyMMdd), month(yyyyMM), or year(yyyy). During automatic table creation, a cdc_partition_key field is created in the Hudi table by default. The system formats the value of the source field (src_col_1) based on the configured time conversion format and writes the value to cdc_partition_key.

     • Customize table attributes. Some advanced functions of a single table can be configured using parameters. For details about the parameters, see the table that lists Hudi advanced configurations.
   • Edit additional fields: Click Additional Field in the Operation column to add custom fields to the destination Hudi 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 Hudi table
     • Field Type: Type of the new field in the destination Hudi table
     • Field Value: Value source of the new field in the destination Hudi table

       Table 8 Additional field value obtaining mode

       Type	Example
       Constant	Any character
       Built-in variable	Source host IP address: source.host Source schema name: mgr.source.schema Source table name: mgr.source.table Destination schema name: mgr.target.schema Destination table name: mgr.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.

   • 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.
     Figure 11 Automatic table creation
     
     NOTE:

     • 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.
     • An automatically created Hudi table contains three audit fields: cdc_last_update_date, logical_is_deleted, and _hoodie_event_time. The _hoodie_event_time field is used as the pre-aggregation key of the Hudi table.

9. Configure task parameters.

   Table 9 Task parameters

   Parameter	Description	Default Value
   Execution Memory	Memory allocated for job execution, which automatically changes with the number of CPU cores.	8 GB
   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.	120
   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. Whether dirty data can be written depends on the data connection. 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. Dirty data can only be written to OBS links.	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 only 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
   Add Custom Attribute	You can add custom attributes to modify some job parameters and enable some advanced functions. For details, see Job Performance Optimization.	N/A

10. Submit and run the job.

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

    Figure 12 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 13 Starting the job
    

    Table 10 Parameters for starting the job

    Parameter	Description
    Offset Parameter	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.

11. 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 14 Monitoring the job