SQL Tasks

NineData SQL tasks help teams submit, review, approve, execute, and recover database changes through a governed workflow. Use SQL tasks for DDL and DML changes that require policy pre-checks, approval workflows, execution controls, or automatic backup before execution.

When to use SQL tasks

To protect data security, users without write permissions to the target data source cannot directly change its data or structure. If a change is governed by SQL development policies or requires approval, submit a SQL task. NineData then manages the full lifecycle of the change, including submission, approval, execution, rollback planning, and backup.

Before executing a SQL task, NineData automatically backs up the current data state to reduce the impact of operational mistakes or unexpected errors. If an error occurs, you can download the backup data and manually roll back the change.

Notes

• In the commercial versions (DevOps Pro, DevOps Enterprise), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOps service. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

  

• The system automatically retains the backed-up data for 7 days, after which it expires automatically.

Prerequisites

• Supported data source types are MySQL, SQL Server, Oracle, OceanBase Oracle, OceanBase MySQL, Db2, PostgreSQL, Doris, SelectDB, Redis, MongoDB, DaMeng, KingBase, Klustron, DWS, MaxCompute, openGauss, GaussDB, TiDB, GreatSQL, GBase, GaiaDB, GaiaDB-X, TDSQL MySQL, Lindorm, and PolarDB-X.

• To use the automatic backup feature, the data source type must be MySQL, SQL Server, Oracle, PostgreSQL, Greenplum, KingbaseES, PolarDB-X.

Procedure

Use these procedures to submit, approve, execute, pause, terminate, restart, and recover SQL tasks.

Submit a SQL change task

Prerequisite

• An organization has been created or joined. See Manage organizations.
• The NineData console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.
• The current user has access to the SQL Tasks module. See Preset role permissions.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, verify that the console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, click Create SQL Task in the toolbar.

   tip

   If you haven't created SQL task before, a blank page will be displayed. In this case, please click on Create SQL Task on the page.

4. On the Create SQL Task page, configure the SQL task.

   Parameter	Description
   Name	Enter a SQL task name. Use a meaningful name that describes the purpose of the SQL statement. Up to 64 characters are supported.
   Datasource	Select the data source or database group that contains the target database.
   Database|Schema	Select the database or schema where the SQL change will be executed.
   Executor	Select the user who executes the SQL statement after the SQL task is approved. Note: The options in the Executor list depend on whether the current data source uses SQL Dev Policy: If the current data source is configured with SQL Dev Policy, the options are based on the SQL Task Executor Config rule configured in the current policy. This rule is located on the SQL Task & Console tab. For configuration details, see Edit Policy. If the current data source is not configured with SQL Dev Policy, the options are users who have SQL Task - Exec permission for the current data source. For permission configuration, see Configure User Permissions.
   Estimated Affected Rows	Enter the estimated number of rows affected by the change. During Policy Pre-check, NineData compares the actual affected rows with the estimate. If they differ, NineData shows a prompt.
   Note (optional)	Add notes for the SQL task, such as the reason for the change or the expected execution time.
   Submission Method	Enter the SQL statement to execute, or upload a file that contains the SQL statement. SQL Text: Enter SQL directly in the text box. SQL File: Click Upload File, then select and upload a file that contains the SQL statement. Note: After the upload is complete, hover over the file name and select to preview the file or to delete it.
   Rollback SQL (optional)	Enter rollback SQL for compliance records when your organization requires a rollback plan. This rollback SQL is recorded in the SQL task but does not affect the task lifecycle.

5. Click Save and Pre-Check to go to the Policy Pre-check page. NineData pre-audits the SQL statement based on the SQL development policy associated with the current data source. One of these outcomes occurs:

   • Pre-audit passed: Depending on the approval process configuration, the task status changes to Pending or Approved. If the status is the former, continue to the next step. If the status is the latter, the submission process ends.

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

     tip

     The pre-audit issues include four categories: Must Modify, Suggested, Syntax, and Permissions:

     

     • Must Modify: Violation of the highest level specification configured by the administrator.
     • Suggested: Violation of the suggested improvement specification configured by the administrator.
     • Syntax: Syntax issues automatically detected by the system. These issues 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: Triggered two rules configured by the administrator in the SQL Development Guidelines, causing disruption in the SQL task workflow.
     • Enable SQL task schema update type checking: Used to enable or disable SQL syntax related to structural changes.
     • Allowed SQL Task Update Data: Used to enable or disable SQL syntax related to data modification.

6. Click Submit Approval in the toolbar, select the process approver(s) in the pop-up window, and click OK.

   tip

   • Depending on the approval process configuration, the number of approvers to select here may vary. Select the approver(s) required by your process.

   • If the Not Specifying Approvers feature is enabled in the current approval process, this section will display After submission, all approvers can review. Please check the results in the approval process.. You do not need to manually specify an approver; all personnel authorized to approve the current work order will receive approval notifications and be able to approve it.

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

   • Withdraw: Withdraw the SQL task. See Withdraw the SQL task.
   • Transfer: Change the approver of the SQL task.

Withdraw a SQL task

When the SQL task status is Pre-Checking, Pending Approval, Approved, or Scheduled, you can withdraw the SQL task. After withdrawal, the task is no longer executed. You can edit the task and submit it again.

Prerequisite

• The status of the SQL task is Pre-Checking, Pending Approval, Approved, or Scheduled.
• You are the Submitter of the SQL task or Administrator.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, verify that the console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, find the target SQL task. Click the task ID, or click Details in the Actions column. On the Details page, click Withdraw in the toolbar.

   tip

   You can also click > Withdraw in the Actions column of the task.

4. In the confirmation window that appears, click Withdraw or Withdraw and Edit.

   • Withdraw: The task status changes to Withdraw. To edit the task again, find the SQL task in the SQL Task list and click Edit in the Actions column of the task.
   • Withdraw and Edit: NineData opens the SQL task edit page.

Approve SQL tasks

Prerequisites

• The status of the SQL task is Pending Approval.
• You are the Approver of the target SQL task or Administrator.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, make sure your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, find the target SQL task and click its task ID or Details in the Actions column of the task.

4. On the Details page, review the contents of the Policy Pre-check and SQL Details tabs, and select Transfer, Approved, or Rejected as appropriate.

   Operation	Description
   Transfer	Transfer the task to another user for approval.
   Approved	Approve the request. After you click Approved, enter Reason if needed, then click OK.
   Rejected	Reject the request and do not execute the SQL task. After you click Rejected, enter Reason for rejection, then click OK.

Execute SQL tasks

You can execute a SQL task when its status is Approved.

Prerequisites

• The status of the SQL task is Approved.
• You are the Executor of the target SQL task or Administrator.

Procedure

1. Log in to the NineData Console.

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

   tip

   If you cannot find SQL Task, make sure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, click the task ID whose status is Approved.

4. On the Details page, click Execute in the toolbar.

5. In the pop-up Execute SQL Tasks window, select Errors Handling and Execution Method, and click OK.

   Operation	Description
   Errors Handling	If an error occurs, terminate the task.: Stop execution immediately when an error occurs during SQL execution. Ignore errors, and Continue: Ignore the error and continue execution. Rollback Task on Execution Error (MySQL only): Optional when the SQL task contains only DML statements. NineData executes the task transactionally. If any DML statement fails, the entire task is automatically rolled back to ensure atomicity and consistency.
   Backup Failed Policy	SQL tasks are backed up before execution by default to reduce business impact if execution fails. These options are supported: Failed and Continue Task: Continue executing the current SQL task regardless of whether backup succeeds. Failed and STOP Task: Do not execute the current SQL task if backup fails.
   Execution Method	Execute Now: Execute the SQL statement immediately. Scheduled Execution: Select a time to execute the SQL statement. Manual Execution: Manually execute the SQL in the SQL task, and click Executed, marked successful. You can also click Go to SQL Console to execute the SQL manually.

6. The task status changes to Running. If you do not need NineData to back up data before execution, click Skip Backup in the toolbar, then click Skip and Continue. Wait until the task status changes to Success.

Pause a SQL task

You can pause an SQL task when its status is Executing.

Prerequisites

• The SQL task status is Executing.
• You are an Administrator or the Executor of the target SQL task.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, find the target SQL task. Click the task ID, or click Details in the Actions column. On the Details page, click Pause in the toolbar.

Terminate a SQL task

You can terminate a SQL task when its status is not in a terminal state, such as Success or Terminated. After termination, all ongoing operations stop, and no further actions can be performed on the task.

Prerequisites

• The SQL task must not be in a terminal state.
• You are a Administrator or the Executor of the target SQL task.

Procedure

1. Log in to the NineData Console.

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

   If SQL Task is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, find the target SQL task. Click the task ID, or select Details from the Actions column. On the Details page, click Pause in the toolbar.

Restart a SQL task

You can restart an SQL task when its status is Executing or Failed.

Prerequisites

• The SQL task status is Executing or Failed.
• You are an Administrator or the Executor of the target SQL task.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, find the target SQL task. Click the task ID, or click Details in the Actions column. On the Details page, click Restart in the toolbar.

4. In the pop-up Restart window, select Errors Handling, Backup Failed Policy, and Execution Method, then click OK.

   Operation	Description
   Errors Handling	If an error occurs, terminate the task.: Stop execution immediately when an error occurs during SQL execution. Ignore errors, and Continue: Ignore the error and continue execution.
   Backup Failed Policy	SQL tasks are backed up before execution by default to reduce business impact if execution fails. These options are supported: Failed and Continue Task: Continue executing the current SQL task regardless of whether backup succeeds. Failed and STOP Task: Do not execute the current SQL task if backup fails.
   Execution Method	Execute Now: Execute the SQL statement immediately. Scheduled Execution: Schedule the execution of the SQL statement at a specific time.

Download backup data

When a SQL task encounters an error and data must be restored to its pre-execution state, download the backup data for the target SQL task and manually roll back the data.

Prerequisites

• The object data source types for SQL tasks are MySQL and Oracle.
• The data backup status for the target SQL task is Finished.
• You are a Administrator or the Executor of the target SQL task.
• The backup file is not older than 7 days.

Procedure

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, click the target SQL task ID.

4. Click More in the upper part of the page, and then click Download next to Data Backup.

5. The downloaded backup file is a .zip file that contains the SQL files for rollback. To roll back data, upload this file by submitting another SQL task. See Submit a SQL change task.

View the SQL task list

1. Log in to the NineData Console.

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

   tip

   If SQL Task is not found, make sure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to an organization.

3. On the SQL Task page, view all SQL tasks. To find a task faster, filter tasks by:

   • Based on the workflow node of the task
   • Based on the status of the task
   • Based on the data source
   • Based on the date
   • Based on the task name or ID
   • Based on the database name

Result

SQL tasks provide a controlled workflow for database changes. After a task is submitted, it can move through pre-check, approval, backup, execution, pause, restart, termination, and backup download based on the task status and your permissions.

NineData OnlineDDL

NineData SQL tasks support NineData OnlineDDL, which lets you make database schema changes without affecting normal database usage. See Perform non-locking structure changes to the table using NineData OnlineDDL.

Scope of Syntax for SQL Task Automatic Backup

To ensure accurate backup of data that is about to be modified before executing SQL tasks, NineData identifies SQL statements that may cause data or structural changes based on different database types. When an SQL task is submitted and enters the execution process, the system automatically backs up the affected tables, views, or objects to ensure safe rollback in case of misoperations or exceptions.

The scope of backup syntax for different databases is as follows.

PostgreSQL, PostgreSQL-like, Oracle

Category	Syntax Triggering Automatic Backup
DML	UPDATE, DELETE
Table	ALTER TABLE, DROP TABLE, RENAME
View	ALTER VIEW, DROP VIEW
Function	ALTER FUNCTION, DROP FUNCTION
Procedure	ALTER PROCEDURE, DROP PROCEDURE
Trigger	ALTER TRIGGER, DROP TRIGGER

MySQL

Category	Syntax Triggering Automatic Backup
DML	UPDATE, DELETE
Table	ALTER TABLE, DROP TABLE, RENAME
View	ALTER VIEW, DROP VIEW
Function	ALTER FUNCTION, DROP FUNCTION
Procedure	ALTER PROCEDURE, DROP PROCEDURE
Trigger	ALTER TRIGGER, DROP TRIGGER
Event	ALTER EVENT, DROP EVENT

SQL Server

Category	Syntax Triggering Automatic Backup
DML	UPDATE, DELETE
Table	ALTER TABLE, DROP TABLE, RENAME
View	ALTER VIEW, DROP VIEW
Function	ALTER FUNCTION, DROP FUNCTION
Procedure	ALTER PROCEDURE, DROP PROCEDURE
Trigger	ALTER TRIGGER, DROP TRIGGER