DataWorks:Script template management

Step 1: Define a script template

Script template developers use the Snippets interface in Data Studio to define the procedure body code and the input and output parameters for a common logic. The abstract SQL procedure processes a specified input table (passed as an input parameter) to generate a business-valuable output table (the output parameter). Input and output parameters use the format @@{parameter_name}.

1. In the Workspace SQL Script Templates area, click the icon and select New SQL Script Template. You can also select Create Directory first to organize the script template directory, and then right-click the directory and select New SQL Script Template.

   Note

   • Script templates created by members of the current workspace are listed under Workspace Component Management.

   • Script templates that have been made public are listed under Public SQL Script Templates.

2. Configure the script template information.

   1. Configure the procedure body.

      The procedure body is the implementation code of the script template. Write abstract SQL code using @@{parameter_name} to reference input and output parameters. The SQL procedure processes specified input tables through input parameters and generates output tables. When you use the script template later, configure different input and output parameters to produce correct, runnable SQL code from the template.

   2. Configure input parameters.

      Click Parameters on the right side of the script template editing page, and then click the icon next to Input Parameters to define the input parameters for the procedure body. The supported types are Table and String, meaning the input parameter is a table or a string.

      Note

      You can also click the input/output parameter parsing button at the top of the script template editing page to use the auto-parsing feature to automatically identify input and output parameters from the code, and then configure the relevant parameters based on the following table.

      Table

      Use case

      Use this type when the output result category is fixed and does not change during processing.

      Parameter configuration and description

      • Key parameter: Parameter Definition.

      • Description: Describes the input table structure in text form, including column names, types, and descriptions. This tells template users to configure an input table with matching column counts and compatible types, preventing execution errors from schema mismatches.

        Note

        This definition is for reference only and provides hints for output parameter configuration. It is not enforced in real time.

      • Example:

        The recommended format for the parameter definition is:

        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' 

      String

      Use case

      Use this type when you need to control the input parameter values through variables during processing.

      Parameter configuration and description

      • Key parameter: Default Value.

      • Description: Supports setting a default value. When set, the default value is used automatically when the script template is referenced.

      • Example:

        • Scenario 1: The output table of the script template process needs to output the sales amounts of the top N cities in each region. You can set N as an input parameter of this script template and control the value of N through a string-type parameter.

        • Scenario 2: The output table of the script template process needs to output the total sales amount of a province. You can set a province string parameter as an input parameter of this script template and obtain sales data for different provinces by specifying different province values.

   3. Configure output parameters.

      Define the output parameters for the procedure body, which represent the final output tables. You can specify the output table structure in the parameter configuration for reference.

      Click Parameters on the right side of the script template editing page, and then click the icon next to Output Parameters to define the output parameters for the procedure body.

      Note

      You can also click the input/output parameter parsing button at the top of the script template editing page to use the auto-parsing feature to automatically identify input and output parameters from the code, and then configure the relevant parameters based on the following table.

      • Key parameter: Parameter Definition.

      • Description: Describes the output table structure in text form, including column names, types, and descriptions. This tells template users to configure an output table with matching column counts and compatible types, preventing execution errors from schema mismatches.

        Note

        This definition is for reference only and provides hints for output parameter configuration. It is not enforced in real time.

      • Example:

        The recommended format for the parameter definition is:

        field1_name field1_type field1_comment 
        field2_name field2_type field2_comment 
        ……
        fieldN_name fieldN_type fieldN_comment

        In addition, you can add aggregated output result columns to the output parameter definition based on the processing results you need, such as ranking and total revenue. For example:

        area_id STRING 'area id' 
        city_id STRING 'city id' 
        order_amt DOUBLE 'order amount'
        rank BIGINT 'rank'

3. Click Save and Submit.

   After the script template is created, you can reference it in SQL script template nodes to quickly generate the target tables your business requires. For more information, see Step 2: Reference a script template.

4. (Optional) Click Publish Snippet to publish the script template to the entire tenant (displayed in the Public SQL Script Templates area). After sharing, all tenant users can use it.

Step 2: Reference a script template

You can reference a script template in the following ways:

Upgrade operation: Script template developer

Script template developers can edit the code and parameter configurations as needed. After saving and submitting, the script template is upgraded to a new version. You can view version details in Version.

Referenced script template version upgrade: Script template user

After a script template is upgraded, if your SQL script template node references it, you can choose whether to adopt the latest version.

• If you do not need the new version, you can continue to reference the original version.

• If you want to use the new version, verify whether the parameter configuration of your SQL script template node is still valid and adjust as needed. After editing, submit and deploy the changes. The submit and deploy process is the same as for a regular SQL node.

Upgrade scenario example

A developer creates V1 of an SQL script template, and a user references V1. Later, the developer upgrades the template to V2. The user discovers that V2 is available, opens the template, compares versions, finds that V2 delivers better business results, and upgrades.

Clone a script template

Clone an existing script template to quickly create a new one. The cloned content includes the script template code, parameter settings, and the scheduling parameters in Run Configuration.

1. In the Workspace Component Management area on the left side, right-click the script template that you want to clone and select Cloning from the context menu.

2. In the dialog, modify the script template Name and Path (or keep the default values), and then click Confirm to start cloning.

3. After cloning is complete, you can view the newly generated script template in Workspace Component Management.

Version management

Restore a script template to a specified historical version. Version viewing and comparison features help you analyze differences and make adjustments.

1. In the Workspace Component Management area on the left side, double-click the script template whose versions you want to view and manage to open its editing page.

2. Click Version on the right side of the script template editing page. On the Version page, view and manage the Developer Record and Publish Record information.

   • View a version:

     1. In the Developer Record or Publish Record tab, find the script template version that you want to view.

     2. Click View in the Operation column to go to the details page and view the script template code and schedule settings.

   • Compare versions:

     In the Developer Record or Publish Record tab, compare different versions. The following example uses the development record.

     • Compare within the development or production environment: In the Developer Record tab, select two versions and click Select Comparison to compare the code and schedule settings.

     • Compare between the development and production environments:

       1. In the Developer Record tab, locate the desired version of the script template.

       2. Click Compare in the Operation column, and on the details page, select a version from Publish Record to compare with.

   • Restore a version:

     You can only restore from Developer Record. In the Developer Record tab, find the target version and click Restore in the Operation column to restore the script template.