Dataphin:Create a database SQL task

Limitations

• Dataphin supports creating and running a stored procedure, but it does not perform syntax validation or parsing.

• To run a database SQL task, you need execution permissions for the data source. Table-level permission checks are not supported. For more information, see Apply for, renew, and return data source permissions.

• SQL data updates from MySQL or Oracle data sources cannot trigger data source quality rules. For more information, see Create data source quality rules.

• You cannot download the full query result. Only sample data can be downloaded.

• The offline compute template node type does not support database SQL tasks.

• In a project that uses the Dev-Prod mode, if you use a data source that is configured only for the production environment, running a data backfill or executing the task in the development environment may alter production data. Exercise caution.

Procedure

1. In the top navigation bar of the Dataphin homepage, choose Develop > Data Development.

2. On the Develop page, select a project from the top navigation bar. If your project uses the Dev-Prod mode, you must also select an environment.

3. In the left-side navigation pane, choose Data Processing > Compute Task. In the Compute Task list, click the icon and select Database SQL.

4. In the Create Database SQL Task dialog box, configure the following parameters.

   Parameter		Description
   Basic information	Task name	Enter a name for the task. The name must be 256 characters or fewer and cannot contain the following characters: | / \ : ? <> * "
   	Scheduling type	Select a Scheduling Type: Scheduled task: The system automatically runs this task on a schedule. Manual task: You must trigger this task manually.
   	Directory	Select the directory where the task is stored. If no directories exist, create one: Above the compute task list on the left, click the icon to open the Create Folder dialog box. In the Create Folder dialog box, enter a folder Name and select its Directory as needed. Click OK.
   	Use template	Click the Use Template switch to select whether to use a code template. If you turn the switch on, you must also select a Template and Template Version. The template version defaults to the latest version of the selected template. Code templates streamline development. The template code is read-only; you only need to configure its parameters. For more information, see Create an offline compute template. Note If a database SQL task uses an offline compute template and the data source is AnalyticDB for PostgreSQL, StarRocks, Doris, SelectDB, or GaussDB (DWS), you cannot change the task type.
   	Description	Enter a brief description of the task, up to 1,000 characters.
   Data source configuration	Data source type	Select the type of data source for the SQL task. For a list of supported data source types, see the Offline Development - Database SQL column in Data sources supported by Dataphin. If you use a template, Dataphin automatically sets the data source type to match the template. This setting cannot be changed.
   	Data source	Select the data source for the SQL task. If no data sources are available, click + Create Data Source to create one. If you use a template, Dataphin automatically sets the data source to match the template, but you can select a different one.
   	Catalog	If the Data Source Type is Presto or Trino, you must also configure the Catalog.
   	Database/Schema	If the data source type is PolarDB, MySQL, PostgreSQL, AnalyticDB for PostgreSQL, Oracle, Presto, GaussDB (DWS), Microsoft SQL Server, ClickHouse, Hologres, Doris, openGauss, StarRocks, DM, OceanBase (Oracle tenant mode), SelectDB, Trino, PolarDB-X 2.0, Lindorm, TDSQL for MySQL, TDSQL for PostgreSQL, TDengine, or GBase 8a, you also need to configure the Database/Schema after you configure the data source. If you use a template, Dataphin automatically sets the Database/Schema to match the template's data source schema, but you can select a different one.

5. Click OK.

6. In the code editor on the current task tab, write the SQL code for the task. Database SQL tasks support cross-node output parameters. For more information, see Assign values to cross-node output parameters and Best practices for cross-node parameters. After you finish writing the code, click Run above the code editor.

7. In the right-side pane, click Properties. On the Properties panel, configure the task's Basic information, Runtime parameters, Scheduling properties (for scheduled tasks), Scheduling dependencies (for scheduled tasks), Runtime configurations, and Resource configurations.

   • Basic information

     Configure the task's name, owner, description, and other basic details. For configuration instructions, see Configure basic information for a task.

   • Runtime parameters

     If your task uses parameter variables, you can assign values to them in the properties. During scheduling, the system automatically replaces these variables with their assigned values. For configuration instructions, see Configure and use node parameters.

   • Scheduling properties (for scheduled tasks)

     If the task's Scheduling Type is Scheduled Task, you must configure its scheduling properties. For configuration instructions, see Configure scheduling properties.

   • Scheduling dependencies (for scheduled tasks)

     If the task's Scheduling Type is Scheduled Task, you must configure its scheduling dependencies. For configuration instructions, see Configure scheduling dependencies.

     Important

     The automatically resolved dependencies may not be accurate. Always verify them against your code.

   • Runtime configurations

     You can configure task-level settings, such as a timeout period and a retry policy for failed runs, based on your business scenario. If you do not configure them, the task inherits the default tenant-level settings. For configuration instructions, see Configure runtime settings for a compute task.

   • Resource configurations

     This setting specifies the resources consumed when the scheduled instance runs. Resources are isolated between different resource groups. A database SQL task is a shared-resource task and cannot be assigned to a custom resource group. For configuration instructions, see Configure resources for a compute task.

8. In the current task tab, save and commit the task.

   1. Click the icon above the code editor to save the code.

   2. Click the icon above the code editor to commit the code.

9. On the Submission Details page, confirm the Content to Be Submitted and the Precheck results, and enter any remarks. For more information, see Submission instructions for offline compute tasks.

10. After confirmation, click Confirm and Commit.

Next steps

• If your project uses the Dev-Prod mode, you must go to the publishing list to publish the task to the production environment after committing it. For more information, see Manage Publishing Tasks.

• If your development mode is Basic mode, a database SQL task can be scheduled in the production environment after it is successfully submitted. You can view the published task in the Operations Center. For more information, see View and manage script tasks and View and manage manual tasks.

Appendix: Modify task type

You can change a database SQL task to a corresponding engine-specific SQL task type, such as AnalyticDB for PostgreSQL SQL or StarRocks SQL, if the task uses a data source of type AnalyticDB for PostgreSQL, StarRocks, Doris, SelectDB, or GaussDB (DWS).

1. In the top navigation bar of the Dataphin homepage, choose Develop > Data Development.

2. On the Develop page, select a project. If your project uses the Dev-Prod mode, you must also select an environment.

3. In the left-side navigation pane, choose Data Processing > Compute Task, and select the target database SQL task from the Compute Task list.

4. Click the icon next to the database SQL task and select Modify Task Type.

5. In the Modify Task Type dialog box, click OK to switch the task type.