DataWorks:Component management

The problem script templates solve

In MaxCompute projects, similar SQL processes appear repeatedly across different business tasks. Each process reads from source tables and writes to output tables that share the same schema or compatible data types — only the table names differ. Without script templates, developers copy and paste the same SQL logic into every node that needs it. When the logic changes, every copy must be updated manually.

SQL script templates let you abstract a common SQL process once. Replace the concrete table names with input and output parameters (@@{parameter_name}), and any developer in the workspace can reference the template in an SQL script template node. They configure the specific tables for their task without touching the SQL — the platform generates correct, runnable SQL automatically.

Only the MaxCompute compute engine supports SQL script templates.

When to use script templates

Script templates work best when:

• The same SQL logic (same structure, same operations) must run against different pairs of input and output tables across multiple tasks

• Multiple developers need to reuse a common aggregation, transformation, or filtering pattern without duplicating code

• A central team owns the logic and needs to push updates to all referencing nodes in a controlled, versioned way

Script templates are less suitable when:

• The SQL differs substantially between tasks — parameterizing table names alone does not reduce meaningful duplication

• Only one or two nodes use the logic — the overhead of defining and maintaining a template exceeds the benefit

How it works

1. A developer writes the SQL procedure body once, using @@{parameter_name} to mark variable input and output tables.

2. The developer publishes the template to the workspace (or to all tenant users as a public template).

3. A user creates an SQL script template node, selects the template, and fills in the concrete input and output tables.

4. The platform substitutes the parameter values and generates the final SQL. The node can then be published and scheduled like any standard SQL node.

Example — template to generated SQL:

Template definition (procedure body):

INSERT OVERWRITE @@{output_sales_by_region}
SELECT
  area_id,
  city_id,
  SUM(order_amt) AS total_order_amt,
  RANK() OVER (PARTITION BY area_id ORDER BY SUM(order_amt) DESC) AS rank
FROM @@{input_order_data}
GROUP BY area_id, city_id;

When a user references this template and sets @@{input_order_data} to ods_orders_2024 and @@{output_sales_by_region} to dws_sales_region_2024, the platform generates:

INSERT OVERWRITE dws_sales_region_2024
SELECT
  area_id,
  city_id,
  SUM(order_amt) AS total_order_amt,
  RANK() OVER (PARTITION BY area_id ORDER BY SUM(order_amt) DESC) AS rank
FROM ods_orders_2024
GROUP BY area_id, city_id;

Prerequisites

Before you begin, make sure that:

• Your DataWorks workspace is on Standard Edition or higher

• You have the Development permission in the workspace. For details, see Workspace-level module permission control

Component types

Check the Public SQL Script Templates area before creating a new template — a public template that matches your use case may already exist.

Access script template management

1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select the region you want. Find your workspace and click Shortcuts > Data Studio in the Actions column.

2. In the left navigation pane, click to open the script template management page.

Define a script template

A developer creates the template once. Other workspace members can then reference it in their nodes.

1. In the Workspace SQL Script Templates area, click and select Create SQL Script Template. To organize templates into folders first, select Create Directory, then right-click the folder and select Create SQL Script Template.

   Templates created by workspace members appear under Workspace SQL Script Templates. Published public templates appear under Public SQL Script Templates.

2. Configure the procedure body. Write the SQL logic that the template encapsulates. Use @@{parameter_name} wherever a concrete input or output table name would go.

   Tip: Click Parse I/O Parameters at the top of the editor to automatically detect parameters from the procedure body code.

3. Configure input parameters. On the right side of the editor, click Parameter Configuration, then click next to Input Parameters. Choose a parameter type based on what the parameter represents:

   Table

   Use this type when the parameter refers to a source table. The Parameter Definition field describes the expected schema of that table — field names, data types, and comments — so users know what structure the input table must have. Define parameters in this format:

   Field1_Name Field1_Type Field1_Comment
   Field2_Name Field2_Type Field2_Comment
   ...
   Fieldn_Name Fieldn_Type Fieldn_Comment

   Example:

   area_id STRING 'area ID'
   city_id STRING 'city ID'
   order_amt DOUBLE 'Order amount'

   The parameter definition is a reference hint for users configuring the template. It does not trigger a runtime schema check.

   String

   • The template outputs the top N cities by sales per region. Set N as a string parameter so users can override it per node.

   • The template filters sales data for a specific province. Set the province as a string parameter.

4. Configure output parameters. Click next to Output Parameters. The Parameter Definition field describes the schema of the output table the template produces. Example output parameter definition (with aggregated fields added):

   The parameter definition is a reference hint. It does not trigger a runtime schema check.

   area_id STRING 'area ID'
   city_id STRING 'city ID'
   order_amt DOUBLE 'Order amount'
   rank BIGINT 'rank'

5. Click Save and then Commit.

6. (Optional) Click Make SQL Script Template Public to share the template with all users in the tenant. The template appears in the Public SQL Script Templates area after publishing.

Reference a script template

Once a template is defined and committed, reference it in an SQL script template node to generate the target table for your task.

Upgrade a script template

More operations