Develop SQL components-DataWorks(DataWorks)-阿里云帮助中心

Overview of script templates

Introduction

In many data processing scenarios, you may write similar SQL code where only the names of the input and output tables differ, while their schemas remain identical or compatible. To avoid redundant work, you can encapsulate this logic into a script template. By defining variable table names as input and output parameters, you can reuse the same SQL code for different data sources.

After creating an SQL Snippet node, you can deploy and schedule it just like any other SQL node. This approach significantly improves development efficiency by promoting code reuse.

Permissions

You must have the Development role in a DataWorks workspace to create and use script templates. For more information about how to assign roles, see Add a workspace member and assign roles to the member.

Limitations

The SQL Snippet node feature is available only in DataWorks Standard Edition and later. For more information, see DataWorks editions and features.
Script templates created by members of the current workspace are displayed under the Component tab.
Script templates created by tenant members are displayed under the Public tab.

Script template types

Script templates are categorized as workspace-level or public. The developer who creates a script template can define its type.

Workspace-level script template: After a workspace-level script template is published, it can be used only by users within the same DataWorks workspace. To use this type of template, you must be a member of the workspace. For more information, see Add a workspace member and assign roles to the member.
Public script template: A developer can use the Publish Snippet feature to make a general-purpose script template available to the entire tenant. After it is published, the script template is available to all users within the tenant.

Workflow

Component created in DataWorks must be used with SQL Snippet. The workflow is as follows:

Define a script template

In the Component Management pane of DataStudio, a developer defines the SQL code process and its input and output parameters. This abstract SQL process takes specified input tables as input parameters, processes them, and generates output tables. In the code, input and output parameters use the format @@{ParameterName}.
- The following types of input parameters are supported:
  - Table: Use this type for table-based inputs.
  - String: Use this type to pass variable values, such as filter conditions, into your SQL.
- In practice, output parameters should be configured as the Table type.
Reference a script template

On the DataStudio page, a user creates an SQL Snippet node, references the required script template, and configures the input and output parameters to reuse the code.

Define a script template

Go to the Snippets page

Go to the DataStudio page.

Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.
In the left-side navigation pane, click Component Management to go to the snippet management page.

Note
If Component Management is not displayed in the left-side navigation pane, click the icon in the lower-left corner and add it in the Module Management section.

Create and configure a script template

This section describes how a developer can create a script template in Component Management to abstract a specific SQL process into a reusable template. A script template typically consists of an SQL code process, input parameters, and output parameters. The SQL code process defines the implementation code for the template's functionality. Within this process, you use the @@{VariableName} format to define placeholders for input tables, strings, and output tables. These placeholders serve as the template's input and output parameters, enabling SQL code reuse.

Note

A script template can have multiple input and output parameters.

For example, the process body code can be INSERT OVERWRITE TABLE @@{my_output} SELECT ... FROM @@{my_input} WHERE col1 = @@{my_input_parameter1} AND col2 = @@{my_input_parameter2}, where @@{my_output} is the output table parameter, @@{my_input} is the input table parameter, and @@{my_input_parameter1} and @@{my_input_parameter2} are input string parameters.

Step 1: Create a script template

On the Snippets page, you can create a script template in one of the following ways:

Method 1: In the left-side Snippets panel, right-click the Snippets node and choose New > Snippet.
Method 2: In the toolbar at the top of the Snippets panel, click the New icon and choose New > Snippet.

Note

Script templates created by members of the current workspace are displayed under the Component tab.
Script templates created by tenant members are displayed under the Public tab.

Step 2: Configure the script template

Configure the SQL code process.

The SQL code process contains the implementation code of the script template. By writing abstract SQL code and using the @@{ParameterName} format to introduce input and output parameters, you can process a specified input table and generate a valuable output table. When you use the template later, you configure different input and output parameters to generate valid SQL code from this template.

Configure input parameters.

Define the input parameters for the SQL code process. The supported types are Table and String.

Table: Use this type for table-based inputs.

The following table describes the main configurations for this type.

Parameter	Description	Example
Parameter definition	Defines the expected schema for the input table, including column names, data types, and comments. To prevent runtime errors, the table you provide should have the same number of columns and compatible data types as defined here. Note This definition serves as a guideline and does not trigger an immediate or mandatory check.	The recommended format is: `Column1Name Column1Type 'Column1Comment' Column2Name Column2Type 'Column2Comment' ... ColumnNName ColumnNType 'ColumnNComment'` Example: `area_id string ‘Area ID’ city_id string ‘City ID’ order_amt double ‘Order amount’`

String: Use this type to pass variable values, such as filter conditions, into your SQL.

The following table describes the main configurations for this type.

Item	Description
Default value	This value is used automatically unless overridden.
Example scenarios	Scenario 1: If the output table needs to show the sales of the top N cities in each region, you can set N as an input parameter. The value of N can be controlled by using a string-type parameter. Scenario 2: If the output table needs to show the total sales of a specific province, you can set a province string parameter as an input parameter. By specifying different provinces, you can retrieve the corresponding sales data.

Configure output parameters.

Define the output parameters for the SQL code process, which represent the final output table of the script template. To help other users, you can specify the schema of the output table in the parameter configuration for their reference.

The following table describes the main configurations for output parameters:

Parameter	Description	Example
Parameter definition	Defines the expected schema for the output table, including column names, data types, and comments. To prevent runtime errors, the table you provide should have the same number of columns and compatible data types as defined here. Note This definition serves as a guideline and does not trigger an immediate or mandatory check.	The recommended format is: `Column1Name Column1Type 'Column1Comment' Column2Name Column2Type 'Column2Comment' ... ColumnNName ColumnNType 'ColumnNComment'` Additionally, you can add summary columns to the output parameter definition based on the desired processing results, such as rank or total revenue. Example: `area_id string ‘Area ID’ city_id string ‘City ID’ order_amt double ‘Order amount’ rank bigint ‘Rank’`

Step 3: Save and commit the script template

Click the icon to save the script template, and then click the icon to commit it. After the script template is created, you can reference it in an SQL Snippet node to quickly generate the tables required for your business. For more information, see Reference a script template.

Reference a script template

Prerequisites

A script template is created and ready to use. For more information, see Define a script template.
Create an SQL Snippet node. For more information, see Create and manage MaxCompute nodes.

Reference the script template

On the configuration tab of the SQL Snippet node, reference a script template. In this example, select the get_top_n (V1) script template and configure the parameter values in the right-side Parameter Configuration panel:

Input parameter myinputtable: type is table, value is company_sales_record
Input parameter topn: type is string, value is 10
Output parameter myoutput: type is table, value is company_sales_top_n

Select the script template that you want to use.

If no script templates are available, create one. For more information, see Define a script template.
- If a newer version of the selected script template exists, you can click Update Code to use the latest version.
- Click Edit Snippet to view the details of the script template.
Configure the parameter values for the script template based on your use case.

Next steps

After you finish developing the node, you can perform the following operations:

Configure scheduling properties: If the node requires periodic execution, configure its scheduling properties, such as rerun settings and dependencies. For more information, see Overview of task scheduling configuration.
Perform node debugging: Run the node to test its code and verify the logic. For more information, see Node debugging process.
Deploy the task: After you finish development, deploy the task. Once deployed, the node runs periodically according to its scheduling configuration. For more information, see Deploy tasks.

Manage script templates

Publish and view usage

You can publish a script template or view its usage records as needed.

Publish Snippet: This feature promotes a workspace-level script template to a public script template, making it available to all users in the tenant. By default, a new script template is available only within its workspace.
Snippet Nodes: You can see which nodes are using the current script template. This helps you assess the impact before making changes to the template.

Upgrade a script template

Upgrade process for developers

A developer can edit the script template's code and parameter configurations as needed. After saving and committing the changes, the template is upgraded to a new version. You can view the details of each version in the version history.

Write the following parameterized SQL code in the script template editor. Use @@{} to reference input and output parameters, and use ${} to reference scheduling parameters:

insert overwrite table @@{my_output_table}
partition (ds='${bizdate}')
select
    *
from
    @@{my_input_table}
where   category in ('@@{my_input_parameter1}', '@@{my_input_parameter2}')
  AND   substr(pt, 1, 8) in ('${bizdate}')
;

Version upgrade impact on users

After a script template is upgraded, if your SQL Snippet node references it, you can choose whether to use the latest version. If not, you can continue using the old version. If you choose to upgrade, you must verify that the new parameter configuration is still valid for your SQL Snippet node and make any necessary adjustments based on the new version's description. After editing, commit and deploy the node. The deployment process is the same as for a regular SQL node. To upgrade, perform the following steps:

In the script template selector at the top of the code editor, select the template version (e.g. Snippet (V1)), and determine whether you need to click Update Code Version to use the new version.
In the right-side Parameter Configuration panel, configure the Input Parameters (e.g. parameter name table1, type string) and Output Parameters (e.g. parameter name table2, type string) with appropriate values.

Upgrade example

Developer C creates version V1.0 of a script template, which User A starts using. Later, Developer C upgrades the template to V2.0. When using the node, User A notices that a new version V2.0 is available. User A can open the template, view the details of different versions, and compare them. If the new version offers better business results, User A can update the node to use the latest version of the script template.

References

UI features

Feature	Description
Save	Saves the current settings of the script template.
Steal lock editing	Allows you to take editing control of a node locked by another user.
Submission	Submits the current script template to the development environment.
Publish Snippet	Publishes a general-purpose script template to the entire tenant, making it visible and usable for all users in the tenant.
Parse I/O Parameters	Parses the input and output parameters from the current code.
Running	Runs the script template in the development environment.
Stop	Stops the running script template.
Format	Formats the script template code based on SQL keywords.
Parameter Configuration	Configure the script template information, input parameters, and output parameters.
Version	Shows the commit history of the script template.
Snippet Nodes	Lists all nodes that use this script template.

Best practices

Prerequisites

An SQL Snippet node is created. For more information, see Create and manage MaxCompute nodes.
Create an input table and an output table in an ODPS SQL node.

Define script template

First, create a script template named get_top_n as described in Define a script template. The following are the configuration details.

Parameter configuration

Category	Parameter name	Type	Description	Parameter definition
Input parameter	myinputtable	Table	The specified sales details data table.	`area_id string city_id string order_amt double rank bigint`
Input parameter	topn	String	The number of top sales rankings to retrieve.	N/A
Output parameter	myoutput	Table	The output table that stores the top N city rankings for each region.	`area_id string city_id string order_amt double rank bigint`

SQL code process definition

INSERT OVERWRITE TABLE  @@{myoutput}  PARTITION (pt='${bizdate}')
 SELECT r3.area_id,
 r3.city_id,
 r3.order_amt,
 r3.rank
from (
SELECT
   area_id,
   city_id,
   rank,
   order_amt_1505468133993_sum as order_amt ,
   order_number_1505468133991_sum,
   profit_amt_1505468134000_sum
FROM
   (SELECT
   area_id,
   city_id,
   ROW_NUMBER() OVER (PARTITION BY r1.area_id ORDER BY r1.order_amt_1505468133993_sum DESC) AS rank,
   order_amt_1505468133993_sum,
   order_number_1505468133991_sum,
   profit_amt_1505468134000_sum
FROM
   (SELECT
   area AS area_id,
   city AS city_id,
   SUM(order_amt) AS order_amt_1505468133993_sum,
   SUM(order_number) AS order_number_1505468133991_sum,
   SUM(profit_amt) AS profit_amt_1505468134000_sum
FROM
  @@{myinputtable}
WHERE
   SUBSTR(pt, 1, 8) IN ( '${bizdate}' )
GROUP BY
   area,
   city )
   r1 ) r2
WHERE
   r2.rank >= 1 AND r2.rank <= @@{topn}
ORDER BY
   area_id,
   rank limit 10000) r3;

Use the script template

Create an SQL Snippet node named xc_ref_snippet_get_top_n, reference the get_top_n script template created in Step 1, and configure its parameters. For more information, see Reference a script template.

In the code component editor, select the get_top_n (V1) script template. The SQL logic in the left-side code editor uses INSERT OVERWRITE TABLE @@{myoutput} to write results to the output table, uses the ROW_NUMBER() OVER (PARTITION BY ...) window function to rank cities by sales within each region, and calculates total sales using SUM(order_amt). Configure the input and output parameters in the right-side Parameter Configuration panel. The parameters are configured as follows:

Input parameter myinputtable: Specify an input table named company_sales_record as the data source. The schema of the input table is as follows:

company_sales_record

CREATE TABLE IF NOT EXISTS company_sales_record
(
    order_id         STRING COMMENT 'Order ID (primary key)',
    report_date      STRING COMMENT 'Order date',
    customer_name    STRING COMMENT 'Customer name',
    order_level      STRING COMMENT 'Order level',
    order_number     DOUBLE COMMENT 'Order quantity',
    order_amt        DOUBLE COMMENT 'Order amount',
    back_point       DOUBLE COMMENT 'Discount points',
    shipping_type    STRING COMMENT 'Shipping method',
    profit_amt       DOUBLE COMMENT 'Profit amount',
    price            DOUBLE COMMENT 'Unit price',
    shipping_cost    DOUBLE COMMENT 'Shipping cost',
    area             STRING COMMENT 'Area',
    province         STRING COMMENT 'Province',
    city             STRING COMMENT 'City',
    product_type     STRING COMMENT 'Product type',
    product_sub_type STRING COMMENT 'Product sub-type',
    product_name     STRING COMMENT 'Product name',
    product_box      STRING COMMENT 'Product package',
    shipping_date    STRING COMMENT 'Shipping date'
) 
COMMENT 'Detailed sales information'
PARTITIONED BY
(
    pt               STRING
)
LIFECYCLE 365;

Input parameter topn: Specify the number of top-ranked cities to retrieve for each region, sorted by sales amount. In this example, the value is 10.

Output parameter myoutput: Specify the name of the table for the processed output, company_sales_top_n. The schema of the output table is as follows:

company_sales_top_n

CREATE TABLE IF NOT EXISTS company_sales_top_n
( 
area STRING COMMENT 'Area', 
city STRING COMMENT 'City', 
sales_amount DOUBLE COMMENT 'Sales amount', 
rank BIGINT COMMENT 'Rank'
)
COMMENT 'Company sales ranking'
PARTITIONED BY (pt STRING COMMENT '')
LIFECYCLE 365;