All Products
Search
Document Center

:Create and manage a script template

Last Updated:Nov 13, 2024

An SQL code process can be abstracted as a script template for you to reuse SQL code. Before you use a script template, you must create a script template that meets your business processing requirements. This topic describes how to create a script template in the Snippets pane. This topic also describes how to share and upgrade a script template and view the reference records of a script template on the configuration tab of the script template.

Go to the Snippets pane

  1. Go to the DataStudio page.

    Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Development and Governance > Data Development. On the page that appears, select the desired workspace from the drop-down list and click Go to Data Development.

  2. In the left-side navigation pane, click Snippets.

    Note

    If the Snippets module is not displayed in the left-side navigation pane, click the Settings icon icon in the lower-left corner and follow the instructions described in Configure settings in the DataStudio Modules section to add the module.

Create and configure a script template

This section describes how to create a script template in the Snippets pane. In most cases, a script template consists of input parameters, output parameters, and an SQL code process. An SQL code process defines the code that implements the features of a script template. In an SQL code process, variable input tables or input character strings are abstracted as input parameters of the script template, and variable output tables are abstracted as output parameters of the script template. This allows you to reuse SQL code. Input parameters and output parameters are configured in the @@{Variable name} format.

Note

A script template can contain multiple input parameters and output parameters. You can configure the parameters based on your business requirements.

SQL code process

  1. Create a script template.

    In the Snippets pane, you can use one of the methods described in the following figures to create and name a script template. Create a script template

  2. Configure the script template.

    Step 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 control and process an input table based on input parameters to generate an output table with business value. When you use the script template, you need only to configure specific input and output parameters. Then, executable SQL code can be generated.

    Step 2: Configure input parameters

    In this step, you can configure input parameters. Input parameters are of the Table or String type.

    • Table: Use this type if output results are of a single type.

      The following table describes the parameter definition for table-type input parameters.

      Item

      Description

      Example

      Parameter Definition

      The parameter definition is a text definition of the table schema, including the field names, field types, and field descriptions. When you use the script template later, 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. If you do not specify an input table that contains the same number of fields and compatible field types that are defined for the table-type parameter, an error is returned when the script template is run.

      Note

      The parameter definition is only for reference and does not trigger an immediate force check.

      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

      Example:

      area_id string 'Region ID' 
      city_id string 'City ID' 
      order_amt double 'Order amount' 
    • String: Use this type if the values of input parameters are controlled based on variables.

      The following table describes the configurations for string-type input parameters.

      Item

      Description

      Default Value

      You can specify a default value for a string-type parameter. The default value is used by default when the script template is used.

      Sample scenarios

      • 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.

    Step 3: Configure output parameters

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

    The following table describes the parameter definition for output parameters.

    Item

    Description

    Example

    Parameter Definition

    The parameter definition is a text definition of the table schema, including the field names, field types, and field descriptions. When you use the script template later, 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. If you do not specify an output table that contains the same number of fields and compatible field types that are defined for the table-type parameter, an error is returned.

    Note

    The parameter definition is only for reference and does not trigger an immediate force check.

    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.

    Example:

    area_id string 'Region ID' 
    city_id string 'City ID' 
    order_amt double 'Order amount' 
    rank bigint 'Ranking'
  3. Save and commit the script template.

    In the top toolbar on the configuration tab of the script template, click the Save icon to save the script template. Then, click the Commit icon to commit the script template. After the script template is created, you can reference the script template in an SQL Snippet node. This helps generate a table required for your business. For more information, see Reference a script template.

Share a script template and view the reference records of a script template

You can share a script template or view the reference records of a script template based on your business requirements. Share a script template and view the reference records of a script template

  • Share a script template: After a script template is deployed, the script template becomes a workspace-level script template. Only members in the current workspace can use the script template. You can click the Publish Snippet icon in the top toolbar on the configuration tab of a general-purpose script template that you developed to make the script template public to the current tenant. This way, all users within the current tenant can use the script template. The Publish Snippet icon is marked as 1 in the preceding figure.

  • View the reference records of a script template: You can view the nodes that reference the current script template on the Snippet Nodes tab. This way, you can estimate the impacts of a script template change that you want to make. The Snippet Nodes tab is marked as 2 in the preceding figure.

Upgrade a script template

Operator: Developer

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

Impacts imposed by version changes on the usage of a script template

If a script template is upgraded and you want to reference the script template in your SQL Snippet node, you can determine whether to use the latest 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 Snippet node and modify configurations based on the template description. Then, commit and deploy the SQL Snippet node. You can commit and deploy the SQL Snippet node in the same manner in which you commit and deploy other SQL nodes. Use a script template

Sample scenario

Developer C creates a script template of the V1.0 version. User A uses the V1.0 script template. Then, Developer C upgrades the script template from V1.0 to V2.0. User A finds that the script template has the latest version V2.0 during usage of the script template. User A can open the script template and view the details about the latest version. If the latest version of the script template can achieve a better business effect, User A can modify configurations to use the latest version.

What to do next

After a script template is created and shared, users who are granted the permissions to use the script template can create an SQL Snippet node and reference the script template. For more information, see Reference a script template.