Skip to main content

Bulk database changes

NineData Bulk Database Changes lets you run the same approved SQL change across database groups or table groups. Use it to update sharded databases, multiple environments, or repeated table structures consistently and safely.

Overview​

As applications grow, teams often split data across multiple databases or tables. A single business change may then need to run against every shard or against the same table structure in multiple environments. Running those changes one by one increases the risk of missed databases, inconsistent SQL, and repeated approval work.

With database groups and table groups, you can submit one SQL task and let NineData apply the approved change to all selected targets. SQL development standards and approval processes continue to apply, so bulk changes stay governed while reducing manual repetition.

Prerequisites​

  • You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. 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​

Create a database group when the same change needs to run against multiple databases. During a bulk change, NineData executes the change SQL in each database in the group.

  1. Log in to the NineData Console.

  2. In the left navigation pane, click Datasource > Datasource.

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

  4. Configure the form in the table, and click Create Database Grouping.

    ParameterDescription
    Database Grouping NameEnter the database group name. Use letters, digits, and underscores only, and start with a letter. Use a meaningful name so the group is easy to identify later.
    Description (Optional)Enter a business description for this database group.
    EnvironmentSelect the environment that contains the target data sources. The data source list is filtered by this environment.
    DatabaseClick Add Datasource, select the data sources to add to the group, and then click OK. You can select multiple data sources, select all, reverse the selection, or search by data source name. After you select the data sources, select the target databases and, depending on the data source type, the target schemas.
About database group standards and processes

After a database group is created, it uses the SQL development standards and approval process configured for the corresponding environment by default. You can adjust them for the group if your business process requires it. For details, see Modify SQL development standards and approval process for database groups.

Create table groups​

Use table groups for finer-grained bulk changes. During a table-group change, NineData executes the change SQL against each table in the group. Create the required database group before you create a table group.

  1. Log in to the NineData Console.

  2. In the left navigation pane, click Datasource > Datasource.

  3. Click the Database Grouping tab, then click the name of the target database group, or click more > Details in the Actions column for the database group.

  4. On the Database Grouping Detail page, click Create Table Grouping.

  5. Configure the form in the table, and click Create Table Grouping.

    ParameterDescription
    Table Grouping NameEnter the table group name. Use letters, digits, and underscores only, and start with a letter. Use a meaningful name so the group is easy to identify later.
    DatabaseClick Add Datasource, select the data sources to add to the group, and then click OK. You can select multiple data sources, select all, reverse the selection, or search by data source name. After you select the data sources, select the target databases and tables and, depending on the data source type, the target schemas.

Modify SQL development standards and approval process for database groups​

When a user runs a bulk change against a database group, NineData checks the SQL against the SQL development standards associated with that group. The approval process controls whether manual approval is required and how multi-level approvals are handled.

  1. Log in to the NineData Console.

  2. In the left navigation pane, click Datasource > Policy & Process.

  3. On the Policy & Process page, click the Database Grouping Configuration tab, then click Edit in the Actions column for the target database group.

  4. In the Edit Group Config window, clear the checkbox next to Inheriting Envir. Config, select the required standards and process, and then click OK.

    tip

    Re-selecting Inheriting Envir. Config will restore the default configuration.

Execute bulk changes​

Bulk changes are executed through SQL tasks. A SQL task can pre-check the submitted SQL, apply the configured SQL development standards, route the request through the approval process, and execute the change after approval.

  1. Log in to the NineData Console.

  2. In the left navigation pane, click DevOps > SQL Task.

  3. On the SQL Task page, click Create SQL Task, then configure the form in the table.

    ParameterDescription
    NameEnter the SQL task name. Include the purpose of the change so reviewers can understand the request quickly. Up to 64 characters are supported.
    DatasourceIn the left drop-down list, select Database Grouping. In the right drop-down list, select the target database group.
    ExecutorSelect the user who executes the SQL statements after the SQL task is approved.
    Note: The options in the Executor list depend on two scenarios:
    • If the current database group is configured with development standards, the options are based on the SQL Task Executor Config rule in the current standards. This rule is located on the SQL Task & Console tab. For configuration details, see Edit policy.
    • If the current database group is not configured with development standards, the options are users who have SQL Task - Exec permission for the current database group. For authorization details, see Configure user permissions.
    Estimated Affected RowsEnter the estimated number of rows affected by this change. During Policy Pre-check, NineData compares the actual affected rows with this estimate and displays a prompt if they do not match.
    Note (Optional)Enter additional notes, such as the business reason or expected execution time.
    Alter SQLEnter the SQL statement to execute or upload a file that contains SQL statements.
    • SQL Text: Enter SQL directly in the text box.
    • SQL File: Click Upload File, and then select and upload a SQL file.
      Note: After the file is uploaded, move the pointer over the file name and select review to preview the file or delete to delete it.
    Rollback SQL (Optional)Enter rollback SQL if your change process requires a rollback plan. The rollback SQL is recorded in the SQL task for compliance purposes and does not affect the SQL task lifecycle.

  4. Click Pre-Check. NineData checks the SQL statements for syntax errors and other issues that may prevent execution. If issues are found, prompts appear below the editor. You can expand the task in Task List to view error details and update the SQL.

  5. Click Save and Pre-Check to open the Policy Pre-check page. NineData pre-checks the SQL against the SQL development standards associated with the current database group. The result can be:

    • Pre-check passed: Depending on the approval process configuration, the task status changes to Pending or Approved. If the status is Pending, continue to the next step. If the status is Approved, click Execute in the toolbar to execute the task.

    • Pre-check failed: The task status changes to Pre-Check Failed. Click Check Again in the toolbar to run the pre-check again, or withdraw the SQL task, edit it, and submit it again.

      tip

      Issues identified during pre-check can include Must Modify, Suggested, Syntax, and Permissions:

      • Must Modify: Violations of SQL development standards configured under Must Modify.

      • Suggested: Violations of SQL development standards configured under Suggested.

      • Syntax: Review the issue carefully. Syntax issues automatically detected by the system do not block the SQL task process, but the SQL may fail to execute. Check that the database, table, and syntax exist and are correct.

      • Permissions: Violations of the two rules configured by the administrator in SQL development specifications.

      • Enable SQL task schema update type checking: Used to allow or prohibit structural change types of SQL syntax.

      • Allowed SQL Task Update Data: Used to allow or prohibit data change types of SQL syntax.

  6. Click Submit Approval in the toolbar, select the approver or approvers in the dialog box, and then click OK.

    tip

    The number of approvers depends on the approval process configuration. Select the approvers required by your process.

  7. The task status changes to Pending Approval. Before approval, you can use these actions:

    • Withdraw: Withdraw the SQL task. See Withdraw SQL task.
    • Transfer: Change the approver(s) for this SQL task.

  8. After approval, the task status changes to Approved. Click Execute in the toolbar to run the bulk database change.

Result​

The SQL task executes the configured change across the selected database group or table group. Review task execution status, affected rows, and approval records before treating the bulk change as complete.

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)