Data comparison

The NineData database comparison function supports the comparison of user data (UserData), for purpose of data consistency, in case such as, backup and recovery, data migration, and data synchronization. This article describes how to perform data comparison.

Prerequisite

• The data sources that need to be compared have been added to NineData. For more information, see Manage Datasources.

• The supported types of source and target data sources can be found in the Introduction to Database Comparison.

Steps

1. Log in to NineData console.

2. In the left navigation bar, click Comparison > Data.

3. In the upper right corner of the Data Comparison page, click Create Data Comparison.

4. On the Source & Target tab, configure according to the following table, and click Next.

   Parameters	Desciption
   Name	Enter the name of the comparison task. For the convenience of subsequent search and management, please try to use a meaningful name. Up to 64 characters are supported.
   Source	The source of the task, and the system will compare based on the structure of the data source.
   Target	The target for the comparison task.
   Frequency	Select how often the comparison is performed. One-time : Perform the comparison task only once. User can manually perform the comparison task multiple times. Periodic : Periodically perform a compare task.
   Periodic (Periodic comparison only)	Choose how often to compare, user can choose any one or more days from Monday to Sunday, or check Every day to compare daily.
   Time Zone (Periodic comparison only)	Select the time zone according to the actual business situation. This parameter affects the time displayed in the comparison task.
   Launch Time ( periodic comparison only)	Select the time to launch the comparison task, from 00:00 to 23:59. User can also click Now to select the current time as the start time.
   Method	Choose how the data will be compared. Full Data : Compare all selected data. Quick Comparison : Choose one or more of the following comparisons. Compare the total number of records in the table. Find all columns of numeric typeDifferent database types have different numeric types. MySQL: BIT, TINYINT, BOOL, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, FLOAT, DOUBLE SQLServer: BIGINT, NUMERIC, BIT, SMALLINT, DECIMAL, SMALLMONEY, INT, TINYINT, MONEY, FLOAT, REAL and bool type in the table, and compare the maximum value, minimum value, and average value. According to the configuration you set, a random sample of corresponding proportions of records is compared in each table. Note: For Redis data sources, options related to BigKey are also provided. If the "" option is selected and a BigKeyrefers to a key in Redis that occupies a large amount of space, usually because the corresponding value is large. is encountered, only the length of the value is compared, not its content. You can customize the length of the BigKey value by configuring , which defaults to 10000. If the length of the value corresponding to a key exceeds 10000, the key is considered a BigKey. The unit of the threshold varies depending on the data type, as follows: For string types: the length of the value corresponding to a key exceeds 10,000 characters. For list, set, sorted set, stream types: the length of the value corresponding to a key exceeds 10,000 elements.

5. On the Objects tab, select the content to be compared and click Next. Both and are supported.

   • : Select all databases and objects in the source instance.
   • : Customize the database and objects to be compared. In the **Source** list on the left, select the databases and objects to be compared, and click **>** add to **Target** list.

6. On the tab, configure the mapping relationship between the source and target databases and object names, and click Save and Pre-Check.

   tip

   If the database or object name in the target is different from that in the source, configuration is required. For MySQL-to-MySQL and SQL Server-to-SQL Server comparisons, you can also click on the right side of the table list and configure the mapping between the target field name and the source field name.

7. On the Pre-check tab, wait for the system to complete the pre-check. After the pre-check passes, click Launch.

   tip

   • If the pre-check fails, user need to click the Details in the Actions column to the right of the target check item to correct the failure, and then perform the pre-check again.

     • Warnings can be repaired or ignored according to specific circumstances.

:::

8. On the Launch tab, a message is displayed indicating that Launch Successfully. User can do the following:

   • If it is a Periodic comparison task , click Compare to execute a comparison task immediately.
   • Click View Details to view the execution of the comparison task.
   • Click Back to List to return to the Data Comparison list.

View comparison results

1. Log in to NineData console.

2. In the left navigation bar, click Comparison > Data.

3. On the Data Comparison page, click the Task ID. The page description is as follows.

   Serial number	Function	Description
   1	Task status	Compare the current state of the task.
   2	Configure alerts	After configuring alerts, the system notifies the user in case of a task failure.
   3	Compare now	Only in-progress tasks are displayed, and a comparison task begins immediately. Option Description: : Perform a full data comparison. : Perform a quick comparison. : Compare inconsistent data from the previous comparison. : Ignore differences in data type and format between source and target, and only compare the valid numerical values.
   4	More	Pause : Pause the task, only applicable to the task whose status is Running. Edit : Edit the configuration information of the task. Terminate : End the task. Delete : Delete the task. The action is irreversible, please operate with caution.
   5	Filter items	Filter the comparison results by task status , task results , and table names.
   6	Log	Records all logs during the execution of the comparison task, and support quick filtering and positioning of target logs by log type , log generation time , keywords and other items.
   7	Monitor	Monitoring information of the task, showing the number of comparison records per second. For MySQL and SQL Server, a flow control function is also provided. After the flow control is enabled, when the thread_running parameter of the source database reaches the threshold you configured, the comparison will stop until the value of this parameter drops below the threshold before resuming the comparison, ensuring the stability of the data source.
   8	Refresh	Get the latest status of comparison tasks.
   9	List of comparison results	Show the results of the data comparison between the source and target. Click the icon in the Actions column on the right side of the comparison list (displayed in case of inconsistency): View the comparison details of the table definition DDL statements on the source and target ends. Click the icon in the Actions column on the right side of the comparison list (displayed in case of inconsistency): generate change SQL, user can apply SQL on target instance for execution, and correct the inconsistent content.
   10	History of comparison task results	Click to view a list of all comparison results. Click on a target list item to switch to the details of that comparison result.

Related Documentation

• Introduction to Database Comparison
• Execution structure comparison