DataWorks:Script template management

Step 1: Define a script template

In the SQL SCRIPT TEMPLATES pane of Data Studio, developers can abstract an SQL code process and define input and output parameters. This way, the SQL code process can process a specific input table based on the input parameters to generate an output table with business value based on output parameters. The input and output parameters are configured in the @@{Parameter name} format.

1. In the Workspace SQL Script Templates section, click the icon and select Create SQL Script Template or Create Directory. If you select Create Directory, create a directory, right-click the directory name, and then select Create SQL Script Template. In the Create SQL Script Template dialog box, configure the parameters and click OK.

   Note

   • The script templates created by the members of the current workspace are displayed in the Workspace SQL Script Templates section.

   • After you make the script templates public, the script templates are displayed in the Public SQL Script Templates section.

2. Configure the script template.

   1. Configure an SQL code process.

      An SQL code process defines the code that implements the features of a script template. You can define an abstract SQL code process to introduce input parameters and output parameters in the @@{Parameter name} format to a script template. This way, the script template can process an input table based on input parameters to generate an output table with business value. When you use the script template, you need to only configure specific input and output parameters to generate executable SQL code.

   2. Configure input parameters.

      In the right-side navigation pane of the configuration tab of the script template, click Parameter Configuration. On the Parameter Configurations tab, click the icon to the right of Input Parameters. In the Input Parameters section, define input parameters for the SQL code process. The input parameters of the Table or String type are supported, which indicates that an input parameter of the SQL code process is a table or specific string.

      Note

      You can also click Parse I/O Parameters in the top toolbar of the configuration tab of the script template. This way, the system automatically identifies input and output parameters from code by using the automatic parsing feature and configures the input and output parameters for the SQL code process.

      Table

      Use scenarios

      If you want the output results to be generated after you use the script template to process source table data are of a single type, you can use this type.

      Key parameter and configuration description

      • Key parameter: Parameter Definition

      • Configuration description: The value of the Parameter Definition parameter is a text definition of the schema of an input table, including the field names, field types, and field descriptions. When you use the script template in subsequent operations, you must specify an input table that contains the same number of fields and compatible field types that are defined for the table-type parameter. Otherwise, an error is returned when the script template is run.

        Note

        The parameter definition is for reference only and does not immediately trigger a forcible check.

      • Example:

        We recommend that you define parameters in the following format:

        Name of Field 1 Type of Field 1 Description of Field 1 
        Name of Field 2 Type of Field 2 Description of Field 2 
        ...
        Name of Field n Type of Field n Description of Field n

        Sample configuration:

        area_id STRING 'Region ID' 
        city_id STRING 'City ID' 
        order_amt DOUBLE 'Order amount' 

      String

      Use scenarios

      If you need to use variables to control the values of input parameters when you use the script template to process source table data, you can use this type.

      Key parameter and configuration description

      • Key parameter: Default Value

      • Configuration description: You can specify a value as the default value of a string-type parameter in the Default Value field. The default value is used by default when the script template is used.

      • Example:

        • Scenario 1: To export only the sales amount of the top N cities in each region in an output table of an SQL code process, you can use a string-type parameter to specify the value of N.

        • Scenario 2: To export the total sales amount of a province in an output table of an SQL code process, you can use a string-type parameter to specify the name of the province and obtain the sales data of the specified province.

   3. Configure output parameters.

      In this step, you can configure output parameters to define a table that you want to generate in an SQL code process. To facilitate the use of a script template, you can specify the schema of an output table for reference.

      In the right-side navigation pane of the configuration tab of the script template, click Parameter Configuration. On the Parameter Configurations tab, click the icon to the right of Output Parameters. In the Output Parameters section, define the output parameters for the SQL code process.

      Note

      You can also click Parse I/O Parameters in the top toolbar of the configuration tab of the script template. This way, the system automatically identifies input and output parameters from code by using the automatic parsing feature and configures the input and output parameters for the SQL code process.

      • Key parameter: Parameter Definition

      • Configuration description: The value of the Parameter Definition parameter is a text definition of the schema of an output table, including the field names, field types, and field descriptions. When you use the script template in subsequent operations, you must specify an output table that contains the same number of fields and compatible field types that are defined for the table-type parameter. Otherwise, an error is returned.

        Note

        The parameter definition is for reference only and does not immediately trigger a forcible check.

      • Example:

        We recommend that you define parameters in the following format:

        Name of Field 1 Type of Field 1 Description of Field 1 
        Name of Field 2 Type of Field 2 Description of Field 2 
        ...
        Name of Field n Type of Field n Description of Field n

        In addition, you can add fields that indicate aggregated output results to the parameter definition based on your business requirements. For example, you can add a ranking field or a revenue field. Sample configuration:

        area_id STRING 'Region ID' 
        city_id STRING 'City ID' 
        order_amt DOUBLE 'Order amount'
        rank BIGINT 'Ranking'

3. In the top toolbar of the configuration tab, click Save and Commit.

   After the script template is created, you can reference the script template in an SQL Script Template node to generate the required table for your business. For more information, see the Step 2: Reference a script template section in this topic.

4. Optional. In the top toolbar of the configuration tab, click Make SQL Script Template Public to make the script template public within the current tenant. Then, the script template is displayed in the Public SQL Script Templates section. This way, all users within the current tenant can use the script template.

Step 2: Reference a script template

You can use one of the following methods to reference a script template.

Upgrade operation: Developer

You can modify the code of a script template that you developed and the parameter settings of the script template based on your business requirements. After the modifications are saved and the script template is recommitted, the script template is upgraded to a new version. On the Version tab in the right-side navigation pane of the configuration tab of the script template, you can view the details of each version. The Versions tab is marked as 5 in the following figure.

Use of the upgraded script template: User

If a script template is upgraded and you want to reference the script template in your SQL Script Template node, you can determine whether to use the latest version of the script template.

• If you do not want to use the latest version of the script template, you can select the original version of the script template.

• If you want to use the latest version of the script template, check whether the parameter settings in the latest version of the script template take effect for your SQL Script Template node and modify configurations based on the template description. Then, commit and deploy the SQL Script Template node. You can commit and deploy the SQL Script Template node in the same manner in which you commit and deploy other SQL nodes.

Sample scenario of using an upgraded script template

A developer creates a script template whose version is V1, and a user uses the script template. In subsequent operations, the developer upgrades the version of the script template to V2. The user finds that the script template has the latest version V2 during the script template usage. The user can open the script template and view the details about the latest version. If the latest version of the script template can provide a better business effect, the user can modify the configurations to use the latest version.