SQL tasks

The SQL task is the task of initiating SQL operations on the database, mainly including SQL statements such as DDL (Database Definition Language) and DML (Database Manipulation Language). This article describes how to use SQL tasks.

Feature

To ensure data security, users who do not have write permissions to the target data source cannot make changes to its contents. If you have no permission or certain operations are governed by SQL development standards and have a change request, you can submit an SQL task ticket to achieve it. NineData's SQL task function provides full-cycle security control, including submission, approval, execution, rollback, and other steps.

To prevent unforeseen circumstances or data errors caused by operational mistakes, the system automatically backs up the current data state before executing the corresponding SQL task. In case of an error, you can download the backup data and manually roll back the changes to ensure data safety.

Precautions

• In the commercial versions (, ), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOpsservice. 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

• The data source type must be one of the following: MySQL, SQL Server, Oracle, OceanBase Oracle, DB2, PostgreSQL, Doris, SelectDB, Redis, MongoDB, DaMeng, KingBase, Klustron, DWS, openGauss, GaussDB, TiDB

• To use the automatic backup feature, the data source type must be MySQL, SQL Server, Oracle, PostgreSQL.

Submit SQL Change Task

Prerequisite

• An organization has been created or joined. For more information, see Managing Organizations.
• The NineData console is in organization mode. How to switch from personal mode to organization mode, see Switching to Organization.
• The current user has access to the SQL Tasks module. For more information, see the Preset role permissions.

Steps

1. Log in to NineData console.

2. In the left navigation bar, click > .

   tip

   If no are found, verify that the console is in organization mode. How to switch from personal mode to organization mode, see Switching to Organization.

3. On the page, click in the upper right corner.

   tip

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

4. On the page, configure the ticket according to the table below.

   Parameter	Description
   	Enter a name for the SQL task, which usually includes the purpose of the SQL statement. To reduce communication costs, use a meaningful name as much as possible. Supports up to 64 characters.
   	The data source where the target database to be changed is located.
   |Schema	The database or schema where the SQL change needs to be executed.
   	The person who will execute the SQL statement after the current SQL task is approved. Note: The options in the list depend on two situations: If the current data source is configured with SQL Dev Policy, the options in the list are based on the rule configured in the current policy. This rule is located on the tab. For information on how to configure SQL Dev Policy, see Edit Policy. If the current data source is not configured with SQL Dev Policy, the options in the list are for users who have permission for the current data source. For more information about authorization, see Configure User Permissions.
   	Enter the estimated number of rows that this change is expected to affect. During the stage of the SQL task, the system will verify whether there is a discrepancy between the actual number of rows affected by SQL and the estimated number. If they are not the same, a prompt will be given.
   (optional)	Notes for the current SQL task that are not yet completed, such as the reason for executing the task, the expected execution time, etc.
   	Enter the SQL statement to be executed or upload a file containing the SQL statement. : Enter the SQL statement to be executed directly in the text box. : Click and select and upload a file containing the SQL statement. Note: After the upload is complete, move the mouse to the file name, and you can select (preview file) or (delete file) on the right side of the file name.
   (optional)	For enterprises that require rollback plans for changes, the rollback SQL input provided here will be recorded within the current SQL task. However, it will not have any impact during the entire lifecycle of the current SQL task and is solely used for compliance purposes.

5. Click , and the system will check the SQL statement in advance to rule out syntax errors and other situations that cannot be executed. If there is a problem with the SQL statement, a prompt will appear below. Click in the prompt to view the specific error information, for user to locate and modify.

6. After is passed, click on to go to the page. The system will perform a pre-audit of the SQL statement based on the SQL development specification associated with the current data source, and there will be several results:

   • Pre-audit passed: Depending on the configuration of the approval process, the task status will be changed to or . If it is the former, please proceed to the next step; if it is the latter, the process ends.
   • Pre-audit failed: The task status will be changed to . You can click on in the upper right corner of the page to pre-audit again, or you can withdraw the SQL task and edit and submit it again.
   tip

   The pre-audit issues include four categories: , , , and :

   

   • : Violation of the highest level specification configured by the administrator.
   • : Violation of the suggested improvement specification configured by the administrator.
   • : Attention is required for syntax issues automatically detected by the system. Although it does not block the SQL task process, the SQL may fail to execute. Please check the existence of databases and tables, as well as the correctness of the syntax.
   • : Triggered two rules configured by the administrator in the SQL Development Guidelines, causing disruption in the SQL task workflow.
     • : Used to enable or disable SQL syntax related to structural changes.
     • : Used to enable or disable SQL syntax related to data modification.

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

   tip

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

   • If the feature is enabled in the current approval process, this section will display . 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.

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

   • : Withdraw the SQL task. For more information, please refer to Withdraw the SQL task.
   • : Change the approver of the SQL task.

Withdraw SQL Tasks

When the status of the SQL task is , , , or , user can withdraw the application for the SQL task. After the withdrawal, the task will no longer be executed, and can re-edit the task list and submit it again.

Prerequisite

• The status of the SQL task is , , , or .
• User is the of the SQL task or .

Steps

1. Log in to NineData console.

2. In the left navigation bar, click > .

   tip

   If no are found, verify that the console is in organization mode. How to switch from personal mode to organization mode, see Switching to Organization.

3. On the page, find the target SQL task, and click the task ID or in the column to the right of the task. Click in the upper right corner of the page.

   tip

   User can also click > in the column to the right of the task.

4. In the confirmation window that pops up, click or .

   • : Task status changed to . To re-edit the task, find the SQL task in the list, and click in the column to the right of the task.
   • : The page automatically jumps to the edit page of the SQL task.

Approve SQL task

Prerequisites

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

Steps

1. Log in to the NineData console.

2. In the left navigation pane, click > .

   tip

   If you cannot find , please make sure your console is in organization mode. For instructions on switching to organization mode from personal mode, see Switch to an organization.

3. On the page, find the target SQL task and click its task ID or the in the column to the right of the task.

4. On the page, review the contents of the and tabs, and select , , or as appropriate.

   Operation	Description
   	Transfer the task to another user for approval.
   	Approve the request. After clicking , you can enter as needed and click .
   	Reject the request and do not execute the SQL task. After clicking , you also need to enter and click .

Execute SQL Tasks

You can execute a SQL task when its status is .

Prerequisites

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

Steps

1. Log in to the NineData Console.

2. In the left navigation pane, click > .

   tip

   If you cannot find , make sure that your console is in organization mode. For instructions on how to switch from personal mode to organization mode, see Switch to Organization.

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

4. On the page, click in the upper-right corner.

5. In the pop-up window, select and , and click .

   Operation	Description
   	: Stop the execution immediately if an error occurs during SQL execution. : Ignore the error during SQL execution and continue to execute.
   	By default, SQL tasks are backed up for the changes before execution to prevent any adverse impact on business in case of execution errors. The following two options are supported: : Regardless of whether the backup is successful or not, the current SQL task will continue execution. : After a backup failure, the current SQL task will not be executed.
   	: Execute the SQL statement immediately. : Select a time to execute the SQL statement. : Manually execute the SQL in the SQL task, and click . You can also click to manually execute the SQL.

6. The task status has been changed to . If you do not need the system to backup your data before executing, you can click on the button in the upper right corner and then click . Wait for the task status to change to .

Pause SQL Task

You can pause an SQL task when its status is .

Prerequisites

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

Procedure

1. Log in to the NineData Console.

2. In the left navigation pane, click > .

   tip

   If is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, refer to Switch to Organization.

3. On the page, locate the target SQL task and click on the task ID or the in the column on the right side of the task. Click on in the upper right corner of the page.

Restart SQL Task

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

Prerequisites

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

Procedure

1. Log in to the NineData Console.

2. In the left navigation pane, click > .

   tip

   If is not found, ensure that your console is in organization mode. To switch from personal mode to organization mode, refer to Switch to Organization.

3. On the page, locate the target SQL task and click on the task ID or the in the column on the right side of the task. Click on in the upper right corner of the page.

4. In the pop-up window, select , , and , then click .

   Operation	Description
   	: If an error occurs during the execution of SQL, the execution will be terminated immediately. : If an error occurs during the execution of SQL, the error will be ignored and the execution will continue.
   	SQL tasks are backed up for changes before execution by default to prevent any impact on business in case of execution errors. The following options are supported: : Continue executing the current SQL task regardless of the success of backup. : Do not execute the current SQL task if the backup fails.
   	: Execute the SQL statement immediately. : Schedule the execution of the SQL statement at a specific time.

Downloading Backup Data

When an SQL task encounters an error and you need to restore the data to its pre-execution state, you can download the backup data of 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 .
• You are either or the 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 on >.

   tip

   If you cannot find , please ensure that your console is in organization mode. To switch from personal mode to organization mode, refer to Switching to Organization.

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

4. Click on in the upper part of the page, and then click on on the right side of .

5. The downloaded backup file is a .zip compressed file that contains the SQL files for rollback. You can upload this file by submitting another SQL task for data rollback. For more information, refer to Submit SQL Task.

View SQL Task List

1. Log in to the NineData Console.

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

   tip

   If you cannot find , please make sure that your console is in organization mode. For instructions on how to switch to organization mode from personal mode, please refer to Switching to Organization.

3. On the page, you can view all SQL tasks. If there are too many tasks, you can use the following search methods to filter the tasks and quickly locate the target task.

   • 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

NineData OnlineDDL

NineData's SQL tasks support the NineData OnlineDDL feature, which allows for database schema changes to be made without affecting normal database usage. For more information, see Perform non-locking structure changes to the table using NineData OnlineDDL.