DataWorks:Develop a script template

Step 1: Open the Snippets pane

1. Log on to the DataWorks console. In the top navigation bar, select the target region. In the left-side navigation pane, choose Data Development and O&M > Data Development. Select the target workspace and click Go to Data Development.

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

   If Snippets is not visible, click the icon in the lower-left corner and follow the instructions in Configure settings in the DataStudio Modules section to add it.

Step 2: Create the template

In the Snippets pane, use the actions shown in the following figure to create and name a script template.

Templates created by member accounts in the current workspace appear on the Workspace-Specific tab. Templates created within the tenant appear on the Public tab.

Step 3: Configure the template

The configuration tab has three parts: the SQL code process, input parameters, and output parameters.

SQL code process

Write the SQL logic in the code editor. Replace variable table names and string values with @@{parameter_name} placeholders. DataWorks substitutes these placeholders with the actual values when an SQL Snippet node runs.

Input parameters

Add one or more input parameters. Each parameter has one of the following types:

• Table: Binds a source table. Define the expected schema as a text block so that users know what schema to supply. Define the schema 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: Binds a string value, such as a numeric threshold or a filter condition. Optionally set a default value that is used when no value is supplied at reference time. Use cases:

  • Export the top N cities by sales: use a String parameter to pass the value of N.

  • Filter by province: use a String parameter to pass the province name.

Output parameters

Add one or more output parameters of type Table. Define the expected output schema for reference.

Example:

area_id   string  'Region ID'
city_id   string  'City ID'
order_amt double  'Order amount'
rank      bigint  'Ranking'

A template can contain multiple input and output parameters.

Step 4: Save and commit the template

In the toolbar on the configuration tab, click the icon to save, then click the icon to commit. After the template is committed, it is available for SQL Snippet nodes to reference.

Prerequisites

Before you begin, ensure that you have:

• A created script template. See Define a script template.

• An SQL Snippet node. See Create and manage ODPS nodes.

Reference the template

On the configuration tab of the SQL Snippet node, follow the steps in the figure below to select and configure a script template.

1. Select the script template to reference.

   • If a newer version of the template is available, click Update Code to pull it in.

   • To inspect the template definition, click Edit Snippet.

2. Configure the input and output parameter values based on your pipeline's tables and requirements.

Publish a template to all tenant users

After deployment, a template is workspace-level by default. To make it available to all users in the tenant, click the Publish Snippet icon (marked 1 in the figure) in the toolbar on the template's configuration tab.

View reference records

On the Snippet Nodes tab (marked 2 in the figure), see all nodes that reference the current template. Review this list before modifying a template to understand the downstream impact.

Upgrade a template

Edit the SQL code or parameter settings in the template, then save and recommit. Each recommit creates a new version. View the version history on the Versions tab (marked 3 in the figure) in the right-side navigation pane.

Impact on referencing nodes

When a template is upgraded, you can determine whether to use the latest version of the template in your SQL Snippet node. To adopt the new version, open the SQL Snippet node, click Update Code, verify that the new parameter settings work for your pipeline, and then commit and deploy the node.

Example scenario

Developer C creates version V1.0 of a template. User A references V1.0. Developer C later upgrades to V2.0. User A sees that a newer version is available, reviews the V2.0 details, and decides whether to update their node to use V2.0.

Configuration tab reference

Prerequisites

Before you begin, ensure that you have:

• An SQL Snippet node. See Create and manage ODPS nodes.

• An input table and an output table created in an ODPS SQL node.

Step 1: Define the get_top_n template

Create a script template named get_top_n. See Define a script template for the full procedure. Use the following configuration.

Parameter settings

SQL code process

The template uses @@{myinputtable}, @@{myoutput}, and @@{topn} as placeholders. DataWorks substitutes them with the bound values when the node runs.

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_1505468133991_sum,
   profit_amt_1505468134000_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_1505468133993_sum,
   order_number_1505468133991_sum,
   profit_amt_1505468134000_sum
FROM
   (SELECT
   area AS area_id,
   city AS city_id,
   SUM(order_amt) AS order_amt_1505468133993_sum,
   SUM(order_number) AS order_number_1505468133991_sum,
   SUM(profit_amt) AS profit_amt_1505468134000_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;

Step 2: Reference the template from an SQL Snippet node

Create an SQL Snippet node named xc_Referenced script template_get_top_n, select the get_top_n template, and configure parameters as shown below.

Parameter configuration

• myinputtable: bind to company_sales_record, a partitioned table with a 365-day lifecycle.

  CREATE TABLE IF NOT EXISTS company_sales_record
  (
      order_id         STRING COMMENT 'Order ID (PK)',
      report_date      STRING COMMENT 'Order generation date',
      customer_name    STRING COMMENT 'Customer name',
      order_level      STRING COMMENT 'Order level',
      order_number     DOUBLE COMMENT 'Number of orders',
      order_amt        DOUBLE COMMENT 'Order amount',
      back_point       DOUBLE COMMENT 'Discount',
      shipping_type    STRING COMMENT 'Transportation method',
      profit_amt       DOUBLE COMMENT 'Amount of profit',
      price            DOUBLE COMMENT 'Unit price',
      shipping_cost    DOUBLE COMMENT 'Cost of transportation',
      area             STRING COMMENT 'Region',
      province         STRING COMMENT 'Province',
      city             STRING COMMENT 'City',
      product_type     STRING COMMENT 'Product type',
      product_sub_type STRING COMMENT 'Product subtype',
      product_name     STRING COMMENT 'Product name',
      product_box      STRING COMMENT 'Product packaging',
      shipping_date    STRING COMMENT 'Date of transportation'
  )
  COMMENT 'Detailed sales data'
  PARTITIONED BY
  (
      pt               STRING
  )
  LIFECYCLE 365;

• topn: set to 10 to return the top 10 cities by total sales in each region.

• myoutput: bind to company_sales_top_n.

  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;