SQL tasks are used to schedule and run .sql files from Studio in Workflow, using Spark SQL syntax uniformly. SQL tasks support referencing SQL files from workspaces or Git folders and run on data analysis and data computing Serverless resource groups.
Overview
|
File Type | .sql
|
Syntax | Spark SQL (based on Kyuubi) |
Computing Resource | Data analysis/data computing Serverless resource group |
Multiple Statements | Supports multiple SQL statements in a file (separated by ;), executed in order. |
Output Capability | Can be used as a parameter source for downstream tasks ({{tasks.<name>.output.rows}}). |
For general configurations (task name, upstream dependencies, run conditions, parameters, monitoring metrics, alarms, and failure and timeout policies), see Task General Configuration. Prerequisites
The workspace is bound to a data analysis or data computing Serverless resource group. For more information, see Compute Resource Overview. The target SQL file already exists in Studio, and the current user (or Run As account) has Run permission or higher.
You already have access to the relevant tables in the target Catalog.
Operation Steps
Step 1: Creating a Job
1. On the workflow canvas, click + Add Task.
2. Select SQL as the task type.
Step 2: Configuring Basic Information
Parameter | Description | Required |
Task Name | Unique within the same workflow. | Yes |
Task type | SQL. You can switch to other task types from the dropdown list. After switching, the parameter area will be cleared. | Yes |
Task Sources | Workspace / Git. | Yes |
Path | Path of the referenced .sql file. | Yes |
Step 3: Selecting an SQL File
Job Source: Workspace
1. Click the Path input box to open the drop-down list.
Only SQL files for which the current user has View permission or higher and that have been published are displayed.
The list displays file names and file paths, and supports fuzzy search.
2. After selection, the path is filled into the input box, for example, /Workspace/your_username/daily_summary.sql.
3. After selecting a file, perform operations on the right:
Preview icon: previews the SQL code content in a pop-up window.
Jump icon: opens a new page to open the file in Studio.
Job Source: Git
Note:
If a user selects a file with only View permission, a message below the input box indicates that the current file has only View permission and cannot be used for task running, and the Create and Save buttons are grayed out. Running requires at least Run permission.
Step 4: Configuring Compute Resources
Parameter | Description | Required | Default Value |
Computing Resource | Data analysis or data computing Serverless resource group. The drop-down list displays only these two types. | Yes | Resource selected in the referenced file |
You can override default resources at the task level. Adjustments made in the task are not written back to the SQL file in Studio.
Step 5: Parameters
Workspace SQL File
The task configuration panel automatically pulls parameters from the Parameters dialog of the Studio SQL file. Only parameter names are grayed out, and parameter values can be modified.
You can add task-specific parameters at the task level. Parameter names cannot duplicate parameters delivered from the file.
SQL File in a Remote Git Repository
Parameters in the file are not automatically parsed. You need to manually add parameter names and values.
Referencing Parameters in SQL
SELECT *
FROM your_catalog.your_schema.orders
WHERE dt = '${dp_data_dt}'
LIMIT 100;
Parameter priority (from high to low): Task parameters > Workflow parameters > File parameters. When a task parameter has the same name as a workflow parameter, the task parameter takes effect first. For details, see Parameter Passing. Step 6: Upstream Dependencies, Monitoring Metrics, Alarms, and Failure and Timeout Policies
Refer to General Task Configuration. By default, the failure and timeout policy for SQL tasks is "Retry after execution failure = Disabled". Step 7: Saving
Click Create Task or Save Task.
Parameter Description
SQL Output as Downstream Parameters
The output of an SQL task can be referenced by downstream tasks through parameters:
Blockquote | Description | Limit |
{{tasks.<name>.output.rows}}
| All rows (JSON array) | Up to 1,000 rows / 48 KB. |
{{tasks.<name>.output.first_row}}
| First row (JSON object) | Same as above. |
{{tasks.<name>.output.first_row.<col>}}
| Value of the specified column in the first row | Same as above. |
Note:
If an SQL file contains multiple SQL statements, only the result of the first SELECT statement is returned. Statements that do not return rows, such as INSERT and UPDATE, are not included in the output.
Display Limit for the Run Results tab
The Run Result tab on the SQL task run details page directly displays the result set returned by the SELECT statement. It uses a different limit than the scenario of passing parameters to downstream tasks:
Scenario | Row Limit | Maximum Size |
Display in the execution result tab | 10,000 rows | 5 M |
As a downstream parameter source (output.rows / output.first_row, and so on) | 1,000 rows | 48 KB |
The excess portion is not displayed and is not included in parameter passing. To obtain the complete result set, use INSERT INTO in the SQL to write it to the target table before consuming it.
Default Catalog / Schema
At task runtime, table names without a prefix are resolved based on the default Catalog and default Schema saved in the Studio SQL file. If only table_name is written in the SQL, it is automatically completed as <default Catalog>.<default Schema>.table_name.
Use Limits
Compute resources support only data analysis and data compute Serverless resource groups.
The SQL task file must be in the Published state, meaning it has been saved as a formal version in Studio. Draft files cannot be referenced by tasks.
Multiple statements are executed sequentially and are interrupted when an error occurs. Parallel execution is not supported.
The Run Result tab displays up to 10,000 rows / 5 MB. When used as a downstream parameter source, it displays up to 1,000 rows / 48 KB. For details, see "Run Result tab Display Limits" above.
SQL output is limited to 1,000 rows / 48 KB. Data beyond this limit is not passed downstream.
Task execution requires the Run As account to have the Run or higher permission on the file ACL. Otherwise, the task is immediately set to failed.
When an SQL file referenced in Studio is deleted, the workflow task run is immediately set to failed.
FAQs
Q1: If an SQL file contains INSERT INTO and SELECT, can the downstream obtain the SELECT result?
It depends on the statement order. The system returns the result based on the first SELECT that appears. If the SELECT comes after the INSERT, it can be referenced normally.
Q2: How do I write parameters when referencing an SQL file from Git?
Parameters are not automatically parsed for Git references. You need to:
1. Use the ${param_name} placeholder in SQL.
2. In the task configuration panel, manually add the task parameter param_name and enter its value.
Q3: When publishing a Studio SQL file, are all referenced task parameters updated synchronously?
Add Parameter: Automatically sync to all referencing tasks.
Existing task parameters and workflow parameters: Values in the task are not overwritten.
Q4: What should I do if I want to run only one of multiple SQL statements in a single file?
An SQL task executes the entire file. For fine-grained control, split different statements into separate SQL files and separate tasks.
References