This topic describes the definition of a script template, the content of a script template, and how to create a script template.

Limits

You can use only DataWorks Standard Edition or a more advanced edition to create a script template. You must activate DataWorks Standard Edition or a more advanced edition before you create a script template. For more information, see Billing of DataWorks advanced editions.

Definition

A script template defines an SQL code process that includes multiple input and output parameters. Each SQL code process references one or more source tables. You can use an SQL code process to filter source table data, join source tables, and aggregate the source tables to generate a new result table required for business.

Value

In actual business scenarios, a large number of SQL code processes are similar. The input tables or output tables in these processes may have the same schema or compatible data types but different table names. In this case, developers can abstract an SQL code process as a script template to reuse the SQL code. The script template extracts input parameters from input tables and output parameters from output tables.

To create an SQL script template, you need to only select an existing script template from the script template list based on your business process and configure specific parameters for input tables and output tables in your business for the selected script template. This way, you do not need to repeatedly copy the code. This helps improve the development efficiency and avoid repeated operations during development. You can deploy and run the SQL script templates that are created in the same manner in which you deploy and run other SQL nodes.

Template contents

Similar to a function, a script template consists of input parameters, output parameters, and an SQL code process.

Input parameters

The input parameters of a script template include properties such as the parameter name, parameter type, parameter description, and parameter definition. The parameter type can be table or string.
  • A table-type parameter specifies the table that you want to reference in an SQL code process. When you use a script template, you can specify the input table that is required for the specific business.
  • A string-type parameter specifies the variable control parameter in an SQL code process. For example, to export only the sales amount of the top N cities in each region in a result table of an SQL code process, you can use a string-type parameter to specify the value of N.

    To export the total sales amount of a province in a result 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.

  • The parameter description specifies the functionality of a parameter in an SQL code process.
  • The parameter definition is a text definition of the table schema and is required only for table-type parameters. When you specify the parameter definition for a table-type parameter, specify an input table that contains the same field names and compatible filed types that are defined by using the table-type parameter. This way, the SQL code process can properly run. If you do not specify an input table that contains the same field names and compatible field types that are defined by using the table-type parameter, an error is returned because the specified field name cannot be found in the input table when the SQL code process runs. The input table must contain the field names and field types that are defined by using the table-type parameter. The input table can also contain other fields. The field names and field types in the input table can be specified in an order based on your business requirements. In this topic, the parameter definition is provided only for reference.
  • 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'

Output parameters

  • The output parameters of a script template include properties such as the parameter name, parameter type, parameter description, and parameter definition. The parameter type must be table. String-type output parameters are not supported.
  • A table-type parameter specifies the table to be generated in an SQL code process. When you use a script template, you can specify the result table that the SQL code process generates for the specific business.
  • The parameter description specifies the functionality of a parameter in an SQL code process.
  • The parameter definition is a text definition of the table schema. When you specify the parameter definition for a table-type parameter, specify an output table that contains the same number of fields and compatible field types that are defined by using the table-type parameter. This way, the SQL code process can properly run. If you do not specify an output table that contains the same number of fields and compatible field types that are defined by using the table-type parameter, an error is returned because the number of fields does not match the specified value or the field types are incompatible when the SQL code process runs. The field names of the output table do not need to be the same as the field names that are defined by using the table-type parameter. In this topic, the parameter definition is provided only for reference.
  • 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' 
    rank bigint 'Ranking'

SQL code process

The parameters in an SQL code process are referenced in the following format: @@{Parameter name}.

By defining an abstract SQL code process, a script template controls and processes an input table based on input parameters to generate an output table with business value.

Before you develop an SQL code process, correctly configure the input and output parameters. In this case, correct SQL code can be generated and run during the process.

Create a script template

  1. Log on to the DataWorks console. In the left-side navigation pane, click Workspaces. On the Workspaces page, find the workspace that you want to manage and click DataStudio in the Actions column.
  2. In the left-side navigation pane of the DataStudio page, click Snippets.
  3. In the Snippets pane, move the pointer over Create and choose Create > Snippet.
  4. In the Create Snippet dialog box, configure the Snippet Name, Description, and Location parameters.
  5. After you complete the configuration, click Commit.

Source table schema

The following table describes the schema of a source MySQL table that contains sales data.
Field name Data type Description
order_id varchar The ID of the order.
report_date datetime The date on which the order is generated.
customer_name varchar The name of the customer.
order_level varchar The level of the order.
order_number double The number of orders.
order_amt double The amount of the order.
back_point double The discount.
shipping_type varchar The transportation method.
profit_amt double The amount of the profit.
price double The unit price.
shipping_cost double The cost of transportation.
area varchar The region.
province varchar The province.
city varchar The city.
product_type varchar The type of the product.
product_sub_type varchar The subtype of the product.
product_name varchar The name of the product.
product_box varchar The packaging of the product.
shipping_date datetime The date of transportation.

Business implication

Script template name: get_top_n

This script template uses the specified sales data table as the table-type input parameter, the number of top cities as the string-type input parameter, and the total sales amount of the cities for ranking. You can obtain the rankings of the specified top cities in each region with ease by using this SQL code process.

Script template parameters

Input parameter 1
  • Parameter name: myinputtable
  • Type: table
Input parameter 2
  • Parameter name: topn
  • Type: string
Output parameter 3
  • Parameter name: myoutput
  • Type: table
Parameter definition:
  • area_id string
  • city_id string
  • order_amt double
  • rank bigint
You can execute the following statement to create a table to store the sales data of a specified number of top cities:
CREATE TABLE IF NOT EXISTS company_sales_top_n
( 
area STRING COMMENT 'Region', 
city STRING COMMENT 'City', 
sales_amount DOUBLE COMMENT 'Sales amount', 
rank BIGINT COMMENT 'Ranking'
)
COMMENT 'Company sales rankings'
PARTITIONED BY (pt STRING COMMENT '')
LIFECYCLE 365;

Example on how to define an SQL code process

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_150546813****_sum,
    profit_amt_15054681****_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_15054681****_sum, 
    order_number_15054681****sum,
    profit_amt_1505468****_sum
FROM     
    (SELECT area AS area_id,
     city AS city_id,
     SUM(order_amt) AS order_amt_1505468****_sum,
     SUM(order_number) AS order_number_15054681****_sum,
     SUM(profit_amt) AS profit_amt_1505468****_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;

Sharing scope

Script templates can be shared within a workspace or made public.

By default, a script template that is deployed can be used by users within the current workspace. The developer of a script template can click the Publish Snippet icon to make the general-purpose script template public to the current tenant. This way, all users of the tenant can view and use the script template.

You can check whether the Publish Snippet icon that is shown in the following figure is clickable on the configuration tab of a script template. If the icon is clickable, the script template can be made public.公开组件

Use a script template

For information about how to use a script template, see Use a script template.

Reference records

In the Components section, double-click a script template. On the configuration tab that appears, click the Snippet Nodes tab in the right-side navigation pane to view the reference records of the script template. Snippet Nodes