DataWorks:Script template management

Step 1: Define a script template

Component developers can use the Snippets interface in DataStudio to define the procedure body code and the input and output parameters for a common logic. By writing an abstract SQL processing procedure, you can process a specified input table that is passed as an input parameter to generate a business-valuable output table, which is the output parameter. The format for input and output parameters in the code is @@{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 your script templates. Then, right-click the directory and select New SQL Script Template.

   Note

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

   • After a script template is made public, it appears under Public SQL Script Templates.

2. Configure the script template.

   1. Configure the procedure body.

      The procedure body contains the script template's implementation logic. You write an abstract SQL process and use the @@{parameter_name} format for input and output parameters. This design enables the template to process different input tables and produce the corresponding output. When you use the script template later, you can configure different input and output parameters to generate correct, runnable SQL code from the template.

   2. Configure input parameters.

      On the right side of the script template editor, click Parameters. Then, click the icon next to Input Parameters to define the input parameters for the procedure body. The supported parameter types are Table and String.

      Note

      You can also click Parse I/O Parameters at the top of the editor page to automatically identify input and output parameters from the code. Then, you can configure the parameters.

      Table

      Use case

      Use this type for parameters that represent an input table with a defined schema, where the processing logic is consistent.

      Parameters

      • Key parameter: Parameter Definition.

      • Description: This parameter describes the expected schema of the input table, including field names, data types, and comments. It helps users provide an input table with a compatible schema, preventing runtime errors due to mismatched field counts or data types.

        Note

        This definition is for reference only and provides a hint for configuration. No runtime check is performed based on this definition.

      • Example:

        The recommended format for the parameter definition is as follows:

        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 value of an input parameter using a variable, such as a filter condition or a limit value.

      Parameters

      • Key parameter: Default Value.

      • Description: This type allows you to set a default value that is used when the script template is referenced.

      • Examples:

        • Scenario 1: The output table of the script template needs to show the top N cities by sales in each region. You can set N as an input parameter of the String type to control its value.

        • Scenario 2: The output table of the script template needs to show the total sales of a province. You can set a province string as an input parameter to retrieve sales data for different provinces.

   3. Configure output parameters.

      Define the output parameters for the procedure body, which represent the final output table of the script template. For clarity, specify the output table's schema in the output parameter configuration as a reference for other users.

      On the right side of the script template editor, click Parameters. Then, click the icon next to Output Parameters to define the output parameters for the procedure body.

      Note

      You can also click Parse I/O Parameters at the top of the editor page to automatically identify input and output parameters from the code. Then, you can configure the parameters.

      • Key parameter: Parameter Definition.

      • Description: This parameter describes the schema of the output table in text format, including information such as field names, data types, and comments. This informs users that the output table they configure must have the same number of fields and compatible data types as defined here. This helps prevent runtime errors caused by inconsistencies between the configured output table and the defined output schema.

        Note

        This definition is for reference only and provides a hint for configuration. No runtime check is performed based on this definition.

      • Example:

        The recommended format for the parameter definition is as follows:

        Field1_Name Field1_Type Field1_Comment 
        Field2_Name Field2_Type Field2_Comment 
        ...
        FieldN_Name FieldN_Type FieldN_Comment

        In addition, you can add summary fields to the output parameter definition based on your required 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'

3. Click Save and Submit.

   After the script template is created, you can reference it in an SQL script template node to quickly generate the target table. For more information, see Step 2: Reference a script template.

4. (Optional) Click Publish Snippet to publish a globally reusable script template to the entire tenant. The script template is then displayed in the Public SQL Script Templates area. After you publish the script template, all users in the tenant can use it.

Step 2: Reference a script template

You can reference a script template in the following ways.

For script template developers

A developer can edit the script template code and parameter configurations as needed. After saving and committing the changes, you create a new version of the script template. You can view the details of each version on the Version tab.

For script template users

When a referenced script template is updated, you can choose whether to use the new version in your SQL script template node.

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

• If you want to use the new version, confirm whether the new parameter configurations are still valid for your SQL script template node. Make adjustments based on the new version's description, and then commit and publish the node. The commit and publish process is the same as for a regular SQL node.

Upgrade scenario

A script template developer creates version V1 of an SQL script template, and a user references it. Later, the developer upgrades the script template to version V2. The user sees that V2 is available, opens the script template, and compares the versions. After finding that the new version provides better business results, the user updates to the latest version.

Clone a script template

Use the clone feature to quickly create a new script template from an existing one. The cloned content includes the script template code, parameter configuration, and the script parameters from the Run Configuration.

1. In the Workspace Component Management pane on the left, right-click the script template you want to clone and select Cloning.

2. In the dialog box that appears, modify the script template Name and Path, or keep the default values, and click Confirm.

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

Manage versions

Version management lets you restore a script template to a specified historical version. It also provides features for viewing and comparing versions, allowing you to analyze differences and make adjustments.

1. In the Workspace Component Management pane on the left, double-click the script template you want to manage to open its editor page.

2. Click Version on the right side of the editor page. On the Version page, you can view and manage information on the Developer Record and Publish Record tabs.

   • View version:

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

     2. Click View in the Operation column to open the details page and view the script template code and scheduling configuration.

   • Compare version:

     You can compare different versions of a script template on the Developer Record or Publish Record tab. The following example demonstrates the comparison operation using the development history.

     • Compare within a development or deployment environment: On the Developer Record tab, select two versions and click Select Comparison at the top. You can then compare the code and scheduling configurations of the two versions.

     • Compare between development and deployment environments:

       1. On the Developer Record tab, locate a specific version of the script template.

       2. Click Compare in the Operation column. On the details page, select a version from the Publish Record to compare against.

   • Restore version:

     You can only restore a script template to a historical version from its Developer Record. On the Developer Record tab, find the target version and click Restore in the Operation column to revert the script template to that version.