Bulk Database Changes

NineData supports unified modifications and updates to database structures or data in bulk, covering multiple data sources, databases, and tables simultaneously, ensuring that data sources in distributed environments can complete the same changes at the same time.

Background Information

As businesses evolve, enterprises face deployments across multiple environments and regions. In scenarios with large volumes of business data, enterprises may adopt sharding to meet query and storage requirements. As business iterations occur, modifications to the structure or data of the same set of sharded databases may be required. The traditional deployment approach involves performing tasks one by one based on the number of databases, which can lead to issues such as missed changes, inconsistent modifications, and repetitive work for developers and approvers.

In this context, enterprises urgently need an efficient way to make bulk data changes, covering multiple environments, regions, and shards of a single sharded table in a single submission, while ensuring the consistency of changes. This presents significant challenges for enterprises in managing issues after scaling and for operations personnel. Solving this problem requires an innovative change management solution to improve efficiency, reduce risks, and better adapt to the constantly changing business environment.

Prerequisites

• You have created or joined an organization, and this organization has subscribed to either or . Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
• Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.

Create Database Groups

This step is used to group multiple databases that need bulk changes into the same group for subsequent bulk change operations. A change SQL statement will be executed in each database within the same database group.

1. Log in to the NineData console.

2. In the left navigation pane, click > .

3. Click the tab, then click > Target Data Source.

4. Configure the form according to the table below, and click .

   Parameter	Description
   	Enter the name of the database group. Only alphanumeric characters, digits, and underscores are supported, starting with an alphabetic character. For ease of later use and management, please use meaningful names.
   (Optional)	Enter a business description for this database group.
   	Select the name of the environment to which your business belongs. You will filter data sources based on this environment.
   	Click , select the data sources to be added to the group, support multiple selection, select all, and reverse selection operations, and also support entering data source names for searching. After clicking , you also need to select specific databases and, depending on the data source, you may need to select specific schemas.

About Database Group Standards and Processes

After the database group is created, it will use the SQL development specifications and approval process configured in the corresponding environment by default. You can make changes according to business requirements. For more information, see Modify SQL Development Specifications and Approval Process for Database Groups.

Create Table Groups

Compared to database groups, table groups are used for finer-grained bulk data changes, where a change SQL statement will be executed in each table within the same table group. To create table groups, database groups must have been created.

1. Log in to the NineData console.

2. In the left navigation pane, click > .

3. Click the tab, then click the name of the target database group, or click > in the column on the right side of the database group.

4. On the page, click .

5. Configure the form according to the table below, and click .

   Parameter	Description
   	Enter the name of the table group. Only alphanumeric characters, digits, and underscores are supported, starting with an alphabetic character. For ease of later use and management, please use meaningful names.
   	Click , select the data sources to be added to the group, support multiple selection, select all, and reverse selection operations, and also support entering data source names for searching. After clicking , you also need to select specific databases and tables, and, depending on the data source, you may need to select specific schemas.

Modify SQL Development Specifications and Approval Process for Database Groups

When users execute SQL to perform bulk changes in the target database group, the SQL development specifications of that database group will be used as the basis for automatic auditing of the SQL. Additionally, the approval process is used to manage the lifecycle of change requests, including whether manual approval is required and configuring multi-level approvals to enhance change security.

1. Log in to the NineData console.

2. In the left navigation pane, click > .

3. On the page, click the tab, then click in the column on the right side of the target database group.

4. In the window, uncheck the checkbox on the left side of , then select the specifications and processes you need, and click .

   tip

   Re-selecting will restore the default configuration.

Execute Bulk Changes

The actual operation of performing bulk changes in database groups is done through SQL task execution. SQL tasks are mechanisms that operationalize and automate data change operations, and also support scheduled execution of SQL. Based on the configured SQL development specifications, SQL tasks will automatically audit the target change statements and automatically intercept SQL statements that do not comply with the specifications to ensure safe and controllable changes.

1. Log in to the NineData console.

2. In the left navigation pane, click > .

3. On the page, click on the page, then configure the form according to the table below.

   Parameter	Description
   	Enter the name of the SQL task, which typically includes the purpose of executing the SQL statement. To reduce communication costs, please use meaningful names as much as possible. Up to 64 characters are supported.
   	Click the drop-down list on the left and select , then click the drop-down list on the right and select the target database group for the required changes.
   	The person responsible for executing the SQL statements after the current SQL task is approved. Note: The options in the list depend on two scenarios: If the current database group is configured with development specifications: the options in the list are based on the rule configured in the current specifications. This rule is located on the tab. For configuration details, please see Editing Specifications. If the current database group is not configured with development specifications: the options in the list are users who have permission for the current database group. For more information about authorization, please see Configuring User Permissions.
   	Enter the estimated number of rows affected by this change. During the phase of the SQL task, the system will verify whether the actual number of affected rows matches the estimated number entered. If they do not match, a prompt will be displayed.
   (Optional)	Enter any additional notes for this SQL task, such as the reason for executing the task, expected execution time, etc.
   	Enter the SQL statement to be executed or upload a file containing SQL statements. : Enter the SQL statements directly in the text box. : Click , select and upload a file containing the SQL statements. Note: After uploading, move the mouse over the file name to the right of the file name, and you can select (preview file) or (delete file).
   (Optional)	For enterprises that need to provide rollback plans for changes, the rollback SQL entered here will be recorded in the current SQL task. However, it will have no impact on the entire lifecycle of the current SQL task and is only used for compliance operations.

4. Click . The system will perform an advance check on the SQL statements to eliminate syntax errors and other issues that prevent execution. If there are any problems with the SQL statements, prompts will appear below, and you can expand the task in to view specific error information for troubleshooting and modification.

5. Click to enter the page. Based on the SQL development specifications associated with the current database group, the system will perform a pre-approval of the SQL statements. The results may be as follows:

   • Pre-approval passed: Depending on the configuration of the approval process, the task status will change to or . If it's the former, proceed to the next step; if it's the latter, simply click in the upper right corner of the page to proceed. This process ends here.
   • Pre-approval failed: The task status will change to . You can click in the upper right corner of the page to perform pre-approval again, or withdraw the SQL task for editing and resubmission.
   tip

   Issues identified during pre-approval include categories such as , , , and :

   • : Violations of specifications configured in SQL development specifications under .

   • : Violations of specifications configured in SQL development specifications under .

   • : Please pay attention. Syntax issues automatically detected by the system, although they do not block the SQL task process, the SQL may fail to execute. Please check the existence and correctness of databases, tables, and syntax.

   • : Violations of the two rules configured by the administrator in

     SQL development specifications

     :

     • : Used to allow or prohibit structural change types of SQL syntax.
     • : Used to allow or prohibit data change types of SQL syntax.

6. Click in the upper right corner of the page, then select the approver(s) in the pop-up window, and click .

   tip

   Depending on the configuration of the approval process, the number of approvers to be selected here may vary. Please select according to the actual situation.

7. The task status changes to . Before approval, you can perform the following operations:

   • : Withdraw the SQL task. For more information, see Withdraw SQL Task.
   • : Change the approver(s) for this SQL task.

8. After approval, the task status will change to . Click in the upper right corner of the page to perform bulk database changes.

Appendix: Routing Algorithm Description

The main function of routing algorithms is to automatically complete data routing. In routing algorithm configuration, target sharding of databases and tables is defined through the following expressions:

'<dbname_expression>''.<tablename_expression>'

• <dbname_expression>: Database name expression in the format: '<dbname_prefix>'+(<expression>)+'<dbname_suffix>'.

  • '<dbname_prefix>': The prefix of the database name, such as 'logical_db_0'.
  • (<expression>): The dynamic numeric part of the database name composition, e.g., #user_id#%4. Suppose the value of the user_id column is 1. Dividing 1 by 4 and taking the remainder gives 1, resulting in a database name of logical_db_01 when combined with the prefix. NineData specifies using # to enclose field names in routing algorithms for easier parsing.
  • '<dbname_suffix>': The suffix of the database name, which can be configured as needed or left empty, e.g., '_bak'. The final database name would be logical_db_01_bak.

  Example: If the result of #user_id#%4 is 0, the corresponding database routing <dbname_expression> would be written as follows:

  Target Database Name	<dbname_expression> Example
  logical_db_01	'logical_db_0'+(#user_id#%4+1)
  logical_db_01_bak	'logical_db_0'+(#user_id#%4+1)+'_bak'
  logical_db_00	'logical_db_0'+(#user_id#%4)
  logical_db_1	'logicaldb'+(#user_id#%4+1)

• .<tablename_expression>: Table name expression in the format: '.<tablename_prefix>'+(<expression>)+'<tablename_suffix>'.

  • '.<tablename_prefix>': The prefix of the table name, such as '.test_time_0'. The dot (.) indicates the table belongs to the preceding database.
  • (<expression>): The dynamic numeric part of the table name composition, e.g., #user_id#%4. Suppose the value of the user_id column is 1. Dividing 1 by 4 and taking the remainder gives 1, resulting in a table name of test_time_01 when combined with the prefix. NineData specifies using # to enclose field names in routing algorithms for easier parsing.
  • '<tablename_suffix>': The suffix of the table name, which can be configured as needed or left empty, e.g., '_bak'. The final table name would be .test_time_01_bak.

  Example: If the result of #user_id#%4 is 0, the corresponding table routing .<tablename_expression> would be written as follows:

  Target Table Name	<tablename_expression> Example
  test_time_01	'.test_time_0'+(#user_id#%4+1)
  test_time_01_bak	'.test_time_0'+(#user_id#%4+1)+'_bak'
  test_time_00	'.test_time_0'+(#user_id#%4)
  test_time_1	'.testtime'+(#user_id#%4+1)

Using the examples above, if the value of the user_id column is 0, the following routing algorithm will route to the test_time_01 table in the logical_db_01 database:

'logical_db_0'+(#user_id#%4+1)'.test_time_0'+(#user_id#%4+1)