DocsICP FilingConsole
Documentation
Home Page DataWorks User Guide Getting started Getting started Advanced: Analyze best-selling product categories

Advanced: Analyze best-selling product categories

Updated at:
Copy as MD
Product
My Favorites

Build a complete data pipeline in DataWorks — from ingesting raw data and orchestrating business workflows to scheduling periodic tasks and visualizing results — using an e-commerce scenario as an example.

Getting started

This tutorial uses an e-commerce scenario to demonstrate how to build a complete data pipeline, from raw data ingestion and processing to data visualization. By following a standardized development process, you can quickly create reusable workflows with reliable scheduling and operational observability.

You will complete the following tasks:

  1. Data synchronization: Use the Data Integration module in DataWorks to create a single-table batch synchronization task that synchronizes business data to a big data platform like MaxCompute.

  2. Data cleansing: Use the Data Studio module in DataWorks to process, analyze, and mine business data.

  3. Data visualization: Use the Data Analysis module in DataWorks to convert analysis results into easy-to-understand charts for business users.

  4. Periodic scheduling: Configure periodic scheduling for the data synchronization and data cleansing workflows.

image

In this tutorial, you will synchronize raw product and order data from a public data source to MaxCompute. You will then use the following workflow to generate a daily ranking of best-selling product categories:

image

Prerequisites

To complete this tutorial, you need an Alibaba Cloud account or a RAM user with the AliyunDataWorksFullAccess permission. For instructions, see Prepare an Alibaba Cloud account or Prepare a RAM user.

Note

DataWorks provides a comprehensive permission system for access control at the product and module levels. If you need more fine-grained access control, see Overview of the DataWorks permission management system.

Prerequisites

(Optional) Activate free trial

DataWorks provides a free trial to help new users experience cloud-native big data development at a low cost. You can claim a resource deduction plan to offset the resource consumption of a serverless resource group.

  1. Go to the Alibaba Cloud Free Trial page.

  2. In the left-side navigation pane, under Products, expand Big Data Computing > Data Development and Services. Find the DataWorks free trial card and click Try Now.

    Important

    • The free trial resource deduction plan is available only to new users of DataWorks. If you are an existing customer, this page will show that you are not eligible for the trial.

    • The DataWorks free trial resource deduction plan can only offset the resource consumption of serverless pay-as-you-go resource groups. After the deduction plan expires, or for other billable items in DataWorks, fees may still be incurred. Read Billing of resource groups and DataWorks billing overview carefully.

  3. Follow the on-screen instructions to claim the free trial resource deduction plan.

    The free trial resource deduction plan must be used together with a serverless pay-as-you-go resource group. After you claim the plan, you must complete the subsequent preparation steps described in Create a resource group and associate it with a workspace. Then use the resource group for big data development, and the system will automatically consume the CU capacity in the deduction plan.

Activate DataWorks

This tutorial uses the China (Shanghai) region as an example. Log on to the DataWorks console, switch to the China (Shanghai) region, and check whether DataWorks is activated in this region.

Note

This tutorial uses China (Shanghai) as an example. In practice, select the region based on the actual location of your business data:

  • If your business data is stored in other Alibaba Cloud services, select the same region as those services.

  • If your business is on-premises and requires public network access, select a region that is geographically close to your location to reduce latency.

New users

If you are a new user using DataWorks for the first time, the page will indicate that DataWorks has not been activated in the current region. Click Purchase Product Portfolio for Free.

  1. Configure the parameters on the combined purchase page.

    Parameter

    Description

    Example

    Region

    Select the region in which you want to activate DataWorks.

    China (Shanghai)

    DataWorks Edition

    Select the DataWorks edition to purchase.

    Note

    This tutorial uses Basic Edition as an example. All editions support the features described in this tutorial. You can refer to Edition comparison and select a DataWorks edition based on your business requirements.

    Basic Edition

    DataWorks Resource Group

    When you use DataWorks to perform Data Integration, data development, and task scheduling, compute resources are consumed. You need to purchase a resource group to ensure that subsequent tasks can run properly.

    • Resource Group Name: Custom.

    • VPC, vSwitch:

      • No existing VPC or vSwitch: If you leave these fields empty, DataWorks will automatically create them. You can also click the corresponding console links in the parameter description to create them manually.

      • Existing VPC and vSwitch: Select the existing VPC and vSwitch.

      Note

      For more information about VPCs and vSwitches, see What is a VPC?.

    • Service-linked Role: Follow the on-screen instructions and click to create the service-linked role.

  2. Click Confirm Order and Pay and complete the payment.

Previously activated but expired

If you previously activated DataWorks in the China (Shanghai) region but the DataWorks edition has expired, the following prompt appears. Click Purchase Edition.

The page shows the message You must have both a DataWorks edition and pay-as-you-go resources. A red cross icon indicates that no DataWorks edition is currently active in this region, and a green check icon indicates that pay-as-you-go resources are already available in this region.

  1. Configure the parameters on the purchase page.

    Parameter

    Description

    Example

    Version

    Select the DataWorks edition to purchase.

    Note

    This tutorial uses Basic Edition as an example. All editions support the features described in this tutorial. You can refer to Edition comparison and select a DataWorks edition based on your business requirements.

    Basic Edition

    Region and Zone

    Select the region in which you want to activate DataWorks.

    China (Shanghai)

  2. Click Buy Now and complete the payment.

Important

After you purchase a DataWorks edition, if you cannot find the edition, try the following:

  • Wait a few minutes and refresh the page. The system update may be delayed.

  • Check whether the current region matches the region in which you purchased the DataWorks edition. Region mismatches may cause the edition to be invisible.

Already activated

If you have already activated DataWorks in the China (Shanghai) region, the DataWorks overview page appears and you can proceed to the next step.

Create a workspace

  1. Go to the DataWorks workspace list page, switch to the China (Shanghai) region, and click Create a workspace.

  2. On the Create a workspace page, specify a custom Workspace Name, turn on Use Data Studio (New Version), and then click Create a workspace.

    Note

    After February 18, 2025, when an Alibaba Cloud account activates DataWorks and creates a workspace in the China (Shanghai) region for the first time, the new version of Data Studio is enabled by default, and the Use Data Studio (New Version) parameter is not displayed on the page.

Create a resource group and associate it with a workspace

  1. Go to the DataWorks resource group list page, switch to the China (Shanghai) region, and click Create Resource Group.

  2. On the resource group purchase page, configure the following parameters.

    Parameter

    Description

    Resource Group Name

    Custom.

    VPC, vSwitch

    Select an existing VPC and vSwitch. If none exists in the current region, click the corresponding console link in the parameter description to create one.

    Service-linked Role

    Follow the on-screen instructions to create the service-linked role.

  3. Click Buy Now and complete the payment.

  4. Go to the DataWorks resource group list page, switch to the China (Shanghai) region, find the resource group you created, and click Associate Workspace in the Operation column.

  5. On the Associate Workspace page, find the workspace you created and click Bind in the Operation column.

Enable public network access for the resource group

The public test business data of the e-commerce platform used in this tutorial must be obtained over the public network. However, the general-purpose resource group created in the previous step does not have public network access by default. You need to configure an Internet NAT Gateway for the VPC associated with the resource group and add an EIP to establish network connectivity with the public data source so that data can be retrieved.

  1. Log on to the VPC console - Internet NAT Gateway, switch to the China (Shanghai) region in the top menu bar, and click Create Internet NAT Gateway. Configure the relevant parameters.

    Note

    Retain the default values for parameters not described in the table.

    Parameter

    Value

    Region

    China (Shanghai).

    Network and Zone

    Select the VPC and vSwitch associated with the resource group.

    You can go to the DataWorks resource group list page, switch to the China (Shanghai) region, find the resource group you created, and then click Network Settings in the Operation column. In the Task Scheduling & Data Integration section, view the VPC and vSwitch. For more information about VPCs and vSwitches, see What is a VPC?.

    Network Type

    Internet NAT Gateway.

    EIP

    Purchase a new EIP.

    Service-linked role creation

    When you create a NAT gateway for the first time, you need to create a service-linked role. Click Create Associated Role.

  2. Click Buy Now and complete the payment to create the NAT gateway instance.

    After the payment is completed, if all three resource operations — Create EIP, Create NAT Gateway, and Bindind EIP — show success, the NAT gateway instance has been purchased and bound with the EIP.

  3. After the NAT gateway instance is purchased, click Return to Console and create an SNAT entry for the newly purchased NAT gateway instance.

    Note

    Only after an SNAT entry is configured can the resource group that uses this VPC access the public network.

    1. Click Management in the Operation column of the newly purchased instance to go to the management page of the NAT gateway instance, and switch to the SNAT Management tab.

    2. In the SNAT Entry List section, click Create SNAT Entry. The following are the key configurations:

      Parameter

      Value

      SNAT entry granularity

      Select VPC granularity to ensure that all resource groups within the VPC of the NAT gateway can access the public network through the configured EIP.

      Select EIP

      Configure the EIP bound to the current NAT gateway instance.

      After configuring the SNAT entry parameters, click OK to create the SNAT entry.

    In the SNAT Entry List section, when the Status of the newly created SNAT entry changes to Enabled, the VPC associated with the resource group has public network access.

Create and associate a MaxCompute compute resource

In this tutorial, you need to create a MaxCompute project and associate it as a DataWorks compute resource to receive data and perform big data analysis.

  1. Go to the DataWorks workspace list page, switch to the China (Shanghai) region, find the workspace you created, click the workspace name, and go to the Workspace Details page.

  2. In the left-side navigation pane, click Computing Resources to go to the compute resource page. Click Associate Computing Resources and select the MaxCompute type. Configure the following key parameters to create a MaxCompute project and associate it as a DataWorks compute resource.

    Note

    Retain the default values for parameters not described in the table.

    Parameter

    Description

    MaxCompute Project

    In the drop-down list, click Create and specify the following parameters.

    • Project Name: Custom. The name must be globally unique.

    • Billing Method of Computing Resources: Select Pay-as-you-go.

      Note

      If Pay-as-you-go is not selectable, click Activate next to Pay-as-you-go to activate the MaxCompute service.

    • Default Quota: Select the default quota from the drop-down list.

    Default Access Identity

    Select Alibaba Cloud Account.

    Computing Resource Instance Name

    The compute resource instance name is used to select the compute resource when tasks are running, making it easy to identify. For example, this tutorial uses MaxCompute_Source.

  3. Click Confirm.

Procedure

The following scenario guides you through the core DataWorks features:

Assume that an e-commerce platform stores product information and order information in a MySQL database. The platform needs to regularly analyze order data and visualize a daily ranking of best-selling product categories.

Step 1: Data synchronization

Create a data source

DataWorks connects to data sources and destinations through data source connections. Create a MySQL data source to connect to the MySQL database that stores the business data used in this tutorial.

Note

You do not need to prepare the raw business data used in this tutorial. For testing and learning purposes, DataWorks provides test datasets. The relevant table data is stored in a public MySQL database. You only need to create a MySQL data source connection.

  1. Go to the DataWorks Administration page, switch to the China (Shanghai) region, select the workspace you created from the drop-down list, and click Go to Management Center.

  2. In the left-side navigation pane, click Data Sources to go to the Data Sources page. Click Add Connection, select the MySQL type, and configure the MySQL data source parameters.

    Note

    • Retain the default values for parameters not described in the table.

    • When you add a data source for the first time, you need to complete Cross-service authorization. Follow the on-screen instructions to grant access to the service-linked role AliyunDIDefaultRole.

    Parameter

    Description

    Data Source Name

    In this example, set this parameter to MySQL_Source.

    Configuration Mode

    Select User-created Data Store with Public IP Addresses.

    Connection Address

    • Host IP address: rm-bp1z69dodhh85z9qa.mysql.rds.aliyuncs.com

    • Port: 3306.

    Important

    The data provided in this tutorial is intended only for hands-on practice with the Alibaba Cloud DataWorks platform. All data is test data and can only be read in the Data Integration module.

    Database Name

    Set this parameter to retail_e_commerce.

    Username

    Enter the username workshop.

    Password

    Enter the password workshop#2017.

  3. In the Connection Configuration section, switch to the Data Integration tab, find the resource group associated with the workspace, and click Test Connectivity in the Connected state column.

    Note

    If the MySQL data source connectivity test fails, perform the following operations:

    • Follow the instructions of the connectivity diagnosis tool.

    • Check whether an EIP is configured for the VPC associated with the resource group. The MySQL data source requires that the resource group has public network access. For more information, see Enable public network access for the resource group.

  4. Click Complete Creation.

Build the synchronization pipeline

Build a synchronization pipeline to synchronize e-commerce product and order data to MaxCompute tables for subsequent data processing.

  1. Click the icon icon in the upper-left corner, and select All Products > Data Development and O&M > DataStudio to go to the data development page.

  2. At the top of the page, switch to the workspace you created for this tutorial. In the left-side navigation pane, click image to go to the Data Development - Project Directory page.

  3. In the Project Directory section, click image and select Create Workflow. Set the workflow name. In this tutorial, the name is set to dw_quickstart.

  4. On the workflow orchestration page, drag a Zero-Load Node and Data Integration nodes from the left side to the canvas to create nodes. Configure the Data Integration nodes as follows:

    • Data Source Type: MySQL.

    • Data Source Type: MaxCompute.

    Set the node names. The following table lists the example node names and their purposes in this tutorial:

    Node type

    Node name

    Node purpose

    image Virtual node

    workshop

    Used to manage the entire workflow. It makes the data flow path clearer. This node is a Dry-run Task and does not require code editing.

    image Single-table batch synchronization node

    ods_item_info

    Used to synchronize the product information source table item_info from MySQL to the MaxCompute table ods_item_info.

    image Single-table batch synchronization node

    ods_trade_order

    Used to synchronize the order information source table trade_order from MySQL to the MaxCompute table ods_trade_order.

    Manually drag lines to connect the nodes and set the workshop node as the upstream node of the two single-table batch synchronization nodes. The final result is as follows:

    image

  5. Workflow schedule settings.

    On the right side of the workflow orchestration page, click Scheduling Settings and configure the relevant parameters. The following are the key parameters for this tutorial. Retain the default values for parameters not described.

    Schedule settings parameter

    Description

    Scheduling Parameter

    Set scheduling parameters for the entire workflow. Internal nodes in the workflow can use these parameters directly.

    In this tutorial, set this parameter to bizdate=$[yyyymmdd-1] to get the date of the previous day.

    Note

    DataWorks provides scheduling parameters that enable dynamic parameter passing in code. You can define variables in SQL code by using the ${variable_name} format and assign values to the variables in Scheduling Settings > Scheduling Parameter. For more information about the supported formats for scheduling parameters, see Configure scheduling parameters.

    Scheduling Frequency

    In this tutorial, set this parameter to Day.

    Data Timestamp

    In this tutorial, set Data Timestamp to 00:30. The workflow will start at 00:30 every day.

    Cross-cycle dependency

    The workflow has no upstream dependency. You can leave this unset. For easier management, you can click Add Root Node to attach the workflow to the workspace root node.

    The workspace root node is named in the format: workspace_name_root.

Configure synchronization tasks

Configure the initial node

  1. On the workflow orchestration page, hover over the workshop node and click Open Node.

  2. On the right side of the workshop node editing page, click Scheduling Settings and configure the relevant parameters. The following are the key parameters for this tutorial. Retain the default values for parameters not described.

    Schedule settings parameter

    Description

    Scheduling Type

    In this tutorial, set this parameter to Dry Run.

    Resource Group for Scheduling

    In this tutorial, select the serverless resource group created in Create a resource group and associate it with a workspace.

    Node dependency configuration

    Because workshop is the initial node and has no upstream dependency, you can click Add Root Node to trigger the workflow from the workspace root node.

    The workspace root node is named: workspace_name_root.

  3. On the node toolbar, click Save to save the node.

Configure the product information synchronization pipeline (ods_item_info)

  1. On the workflow orchestration page, hover over the ods_item_info node and click Open Node.

  2. Configure the data source and resource group.

    Parameter

    Description

    Source

    Data source: MySQL_Source.

    Destination

    Data source: Select the MaxCompute compute resource associated during Create and associate a MaxCompute compute resource. In this example, select MaxCompute_Source.

    Running Resources

    Select the serverless resource group purchased during Create a resource group and associate it with a workspace.

  3. Configure the synchronization solution.

    1. Configure Source and Destination

      Note

      Retain the default values for parameters not described in the table.

      Configuration section

      Parameter

      Configuration description

      Data Source

      Table

      item_info.

      Destination

      Table

      Click Generate Destination Table Schema to quickly create a MaxCompute table. Paste the following DDL statement into the Table Creation Statement section, and click Create Table. This table is used to receive product information from the source.

      DDL statement

      CREATE TABLE IF NOT EXISTS ods_item_info(
  `id`                              BIGINT COMMENT '',
  `cate_id`                         BIGINT COMMENT '',
  `cate_name`                       STRING COMMENT '',
  `commodity_id`                    BIGINT COMMENT '',
  `commodity_name`                  STRING COMMENT '',
  `desc_path`                       STRING COMMENT '',
  `duration`                        BIGINT COMMENT '',
  `features`                        STRING COMMENT '',
  `gmt_create`                      DATETIME COMMENT '',
  `gmt_modified`                    DATETIME COMMENT '',
  `is_deleted`                      BIGINT COMMENT '',
  `is_virtual`                      STRING COMMENT '',
  `item_id`                         BIGINT COMMENT '',
  `item_status`                     BIGINT COMMENT '',
  `last_offline_time`               DATETIME COMMENT '',
  `last_online_quantity`            BIGINT COMMENT '',
  `last_online_time`                DATETIME COMMENT '',
  `pict_url`                        STRING COMMENT '',
  `reserve_price`                   DECIMAL(38,18) COMMENT '',
  `secure_trade_ems_post_fee`       DECIMAL(38,18) COMMENT '',
  `secure_trade_fast_post_fee`      DECIMAL(38,18) COMMENT '',
  `secure_trade_ordinary_post_fee`  DECIMAL(38,18) COMMENT '',
  `shop_id`                         BIGINT COMMENT '',
  `shop_nick`                       STRING COMMENT '',
  `sub_title`                       STRING COMMENT '',
  `title`                           STRING COMMENT ''
)
COMMENT ''
PARTITIONED BY (pt STRING) 
lifecycle 36500;

      Partition Information

      In this tutorial, enter ${bizdate}. During debugging, a constant value is assigned to the bizdate parameter for testing. During scheduled execution, a dynamic value is assigned to the bizdate parameter. For more information about the variable formats and configuration methods supported by Data Studio, see Configure variables.

    2. Verify the Destination Field Mapping and Advanced Settings.

      DataWorks maps source columns to destination columns to write data from specified source columns to specified destination columns. You can also configure task concurrency, dirty data policies, and other settings in the Advanced Settings section on the right. In this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records and retain the defaults for other settings. For more information, see Configure a single-table batch synchronization task.

  4. On the node toolbar, click Save to save the node.

Configure the order data synchronization pipeline ( ods_trade_order)

  1. On the workflow orchestration page, hover over the ods_trade_order node and click Open Node.

  2. Configure the data source and resource group.

    Parameter

    Description

    Source

    Data source: MySQL_Source.

    Destination

    Data source: Select the MaxCompute compute resource associated during Create and associate a MaxCompute compute resource. In this example, select MaxCompute_Source.

    Running Resources

    Select the serverless resource group purchased during Create a resource group and associate it with a workspace.

  3. Click Next step and configure the synchronization task.

    1. Configure Source and Destination

      Note

      Retain the default values for parameters not described in the table.

      Configuration section

      Parameter

      Configuration description

      Data Source

      Table

      trade_order

      Destination

      Table

      Click Generate Destination Table Schema to quickly create a MaxCompute table. Paste the following DDL statement into the Table Creation Statement section, and click Create Table. This table is used to receive product information from the source.

      DDL statement

      CREATE TABLE IF NOT EXISTS ods_trade_order(
  `id`                BIGINT COMMENT '',
  `biz_type`          BIGINT COMMENT '',
  `buy_amount`        BIGINT COMMENT '',
  `buyer_id`          BIGINT COMMENT '',
  `buyer_memo`        STRING COMMENT '',
  `buyer_nick`        STRING COMMENT '',
  `end_time`          DATETIME COMMENT '',
  `gmt_create`        DATETIME COMMENT '',
  `gmt_modified`      DATETIME COMMENT '',
  `ip`                STRING COMMENT '',
  `is_parent`         BIGINT COMMENT '',
  `is_sub`            BIGINT COMMENT '',
  `item_id`           BIGINT COMMENT '',
  `item_price`        DECIMAL(38,18) COMMENT '',
  `logistics_status`  BIGINT COMMENT '',
  `memo`              STRING COMMENT '',
  `parent_order_id`   BIGINT COMMENT '',
  `pay_status`        BIGINT COMMENT '',
  `pay_time`          DATETIME COMMENT '',
  `seller_memo`       STRING COMMENT '',
  `shop_id`           BIGINT COMMENT '',
  `status`            BIGINT COMMENT '',
  `sub_order_id`      BIGINT COMMENT '',
  `total_fee`         DECIMAL(38,18) COMMENT ''
)
COMMENT ''
PARTITIONED BY (pt STRING) 
lifecycle 36500;

      Partition Information

      In this tutorial, enter ${bizdate}. During debugging, a constant value is assigned to the bizdate parameter for testing. During scheduled execution, a dynamic value is assigned to the bizdate parameter. For more information about the variable formats and configuration methods supported by Data Studio, see Configure variables.

    2. Verify the Destination Field Mapping and Advanced Settings.

      DataWorks maps source columns to destination columns to write data from specified source columns to specified destination columns. You can also configure task concurrency, dirty data policies, and other settings in the Advanced Settings section on the right. In this tutorial, set Policy for Dirty Data Records to Disallow Dirty Data Records and retain the defaults for other settings. For more information, see Configure a single-table batch synchronization task.

  4. On the node toolbar, click Save to save the node.

Step 2: Data cleansing

After data is synchronized from MySQL to MaxCompute, two tables are available: the product information table ods_item_info and the order information table ods_trade_order. Use Data Studio to cleanse, process, and analyze this data to generate the daily ranking of best-selling product categories.

Build the data processing pipeline

  1. In the left-side navigation pane of Data Studio, click image to go to the data development page. Then, in the Project Directory section, find the workflow you created, click it to go to the workflow orchestration page, and drag MaxCompute SQL nodes from the left side to the canvas. Set the node names.

    The following table lists the example node names and their purposes in this tutorial:

    Node type

    Node name

    Node purpose

    image MaxCompute SQL

    dim_item_info

    Based on the ods_item_info table, processes product dimension data and produces the product basic information dimension table dim_item_info.

    image MaxCompute SQL

    dwd_trade_order

    Based on the ods_trade_order table, performs initial data cleansing, transformation, and business logic processing on detailed order transaction data and produces the order detail fact table dwd_trade_order.

    image MaxCompute SQL

    dws_daily_category_sales

    Based on the dwd_trade_order table and the dim_item_info table, aggregates the cleansed and standardized detail data from the DWD layer and produces the daily product category sales aggregate table dws_daily_category_sales.

    image MaxCompute SQL

    ads_top_selling_categories

    Based on the dws_daily_category_sales table, produces the daily ranking of best-selling product categories table ads_top_selling_categories.

  2. Manually drag lines to connect the nodes and set the upstream nodes for each node. The final result is as follows:

    image
    Note

    In a workflow, you can set upstream and downstream dependencies between nodes by manually drawing connections. You can also use code parsing to automatically identify node dependencies in child nodes. This tutorial uses manual connections. For more information about code parsing, see Auto-parse dependencies.

Configure data processing nodes

Configure the dim_item_info node

Based on the ods_item_info table, this node processes product dimension data and produces the product basic information dimension table dim_item_info.

  1. On the workflow orchestration page, hover over the dim_item_info node and click Open Node.

  2. Paste the following code into the node editing page.

    CREATE TABLE IF NOT EXISTS dim_item_info (
    gmt_modified                   STRING COMMENT 'Item last modified date',
    gmt_create                     STRING COMMENT 'Item creation time',
    item_id                        BIGINT COMMENT 'Item numeric ID',
    title                          STRING COMMENT 'Item title',
    sub_title                      STRING COMMENT 'Item subtitle',
    pict_url                       STRING COMMENT 'Main image URL',
    desc_path                      STRING COMMENT 'Item description path',
    item_status                    BIGINT COMMENT 'Item status: 1 - approved, 0 - not approved',
    last_online_time               DATETIME COMMENT 'Last listing time, item go-on-sale time',
    last_offline_time              DATETIME COMMENT 'Sale end time, indicates the end of a sales cycle, only applies to auction items',
    duration                       BIGINT COMMENT 'Validity period, sales cycle, only two values: 7 days or 14 days',
    reserve_price                  DOUBLE COMMENT 'Current price',
    secure_trade_ordinary_post_fee DOUBLE COMMENT 'Standard mail shipping fee',
    secure_trade_fast_post_fee     DOUBLE COMMENT 'Express shipping fee',
    secure_trade_ems_post_fee      DOUBLE COMMENT 'EMS shipping fee',
    last_online_quantity           BIGINT COMMENT 'Stock quantity at the last listing time',
    features                       STRING COMMENT 'Item features',
    cate_id                        BIGINT COMMENT 'Item leaf category ID',
    cate_name                      STRING COMMENT 'Item leaf category name',
    commodity_id                   BIGINT COMMENT 'Commodity ID',
    commodity_name                 STRING COMMENT 'Commodity name',
    is_virtual                     STRING COMMENT 'Whether it is a virtual item',
    shop_id                        BIGINT COMMENT 'Shop ID',
    shop_nick                      STRING COMMENT 'Shop nick',
    is_deleted                     BIGINT COMMENT 'Whether the category is deleted'
)
COMMENT 'Item basic information dimension table'
PARTITIONED BY (pt STRING COMMENT 'Business date, yyyymmdd')
LIFECYCLE 365;
-- Insert data into dim_item_info table
INSERT OVERWRITE TABLE dim_item_info PARTITION(pt='${bizdate}')
SELECT
    gmt_create,
    gmt_modified,
    item_id,
    title,
    sub_title,
    pict_url,
    desc_path,
    item_status,
    last_online_time,
    last_offline_time,
    duration,
    cast(reserve_price as DOUBLE),
    cast(secure_trade_ordinary_post_fee as DOUBLE),
    cast(secure_trade_fast_post_fee as DOUBLE),
    cast(secure_trade_ems_post_fee as DOUBLE),
    last_online_quantity,
    features,
    cate_id,
    cate_name,
    commodity_id,
    commodity_name,
    is_virtual,
    shop_id,
    shop_nick,
    is_deleted
FROM ods_item_info
WHERE pt = '${bizdate}';

  3. Configure the debug parameters.

    On the right side of the MaxCompute SQL node editing page, click Run Configuration:

  4. On the node toolbar, click Save to save the node.

Configure the dwd_trade_order node

Based on the ods_trade_order table, this node performs initial data cleansing, transformation, and business logic processing on detailed order transaction data and produces the order detail fact table dwd_trade_order.

  1. On the workflow orchestration page, hover over the dwd_trade_order node and click Open Node.

  2. Paste the following code into the node editing page.

    CREATE TABLE IF NOT EXISTS dwd_trade_order (
    id               BIGINT COMMENT 'Primary key, latest ID after deduplication',
    gmt_create       DATETIME COMMENT 'Creation time',
    gmt_modified     DATETIME COMMENT 'Modification time',
    sub_order_id     BIGINT COMMENT 'Sub-order ID',
    parent_order_id  BIGINT COMMENT 'Parent order ID',
    buyer_id         BIGINT COMMENT 'Buyer numeric ID',
    buyer_nick       STRING COMMENT 'Buyer nickname, null values handled',
    item_id          BIGINT COMMENT 'Item numeric ID',
    item_price       DECIMAL(38,18) COMMENT 'Item price, in cents',
    buy_amount       BIGINT COMMENT 'Purchase quantity',
    biz_type         BIGINT COMMENT 'Transaction type',
    memo             STRING COMMENT 'Memo, null values handled',
    pay_status       BIGINT COMMENT 'Payment status',
    logistics_status BIGINT COMMENT 'Logistics status',
    status           BIGINT COMMENT 'Status',
    seller_memo      STRING COMMENT 'Seller memo for the transaction',
    buyer_memo       STRING COMMENT 'Buyer memo for the transaction',
    clean_ip         STRING COMMENT 'Cleaned buyer IP, invalid formats filtered',
    end_time         DATETIME COMMENT 'Transaction end time',
    pay_time         DATETIME COMMENT 'Payment time',
    is_sub           BIGINT COMMENT 'Whether it is a sub-order, 1 indicates sub-order',
    is_parent        BIGINT COMMENT 'Whether it is a parent order, 1 indicates parent order',
    shop_id          BIGINT COMMENT 'Shop ID',
    total_fee        DECIMAL(38,18) COMMENT 'Sub-order fee after discounts and adjustments',
    is_large_order_flag BOOLEAN COMMENT 'Large order flag'
)
COMMENT 'Trade order detail fact table with preliminary cleansing and business logic processing'
PARTITIONED BY (pt STRING COMMENT 'Business date, yyyymmdd')
LIFECYCLE 365; -- Data lifecycle set to 365 days
INSERT OVERWRITE TABLE dwd_trade_order PARTITION(pt='${bizdate}')
SELECT
    MAX(id) AS id, -- Use the latest ID as the deduplication criterion
    gmt_create,
    gmt_modified,
    sub_order_id,
    parent_order_id,
    buyer_id,
    COALESCE(buyer_nick, '') AS buyer_nick, -- Handle null buyer_nick
    item_id,
    item_price,
    buy_amount,
    biz_type,
    COALESCE(memo, '') AS memo, -- Handle null memo
    pay_status,
    logistics_status,
    status,
    seller_memo,
    buyer_memo,
    CASE 
        WHEN ip LIKE '__.__.__.__' THEN NULL -- Filter invalid IP formats
        ELSE ip 
    END AS clean_ip,
    end_time,
    pay_time,
    is_sub,
    is_parent,
    shop_id,
    total_fee,
    CASE 
        WHEN total_fee >= 10000 THEN TRUE -- Orders over 10000 cents are considered large orders
        ELSE FALSE 
    END AS is_large_order_flag -- Add business logic flag
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY buyer_id, item_id, gmt_create ORDER BY id DESC) AS rn -- Row number for deduplication
    FROM ods_trade_order
    WHERE pt = '${bizdate}'
) AS sub_query
WHERE rn = 1 -- Keep only the first record in each deduplication group
GROUP BY 
    gmt_create,
    gmt_modified,
    sub_order_id,
    parent_order_id,
    buyer_id,
    buyer_nick,
    item_id,
    item_price,
    buy_amount,
    biz_type,
    memo,
    pay_status,
    logistics_status,
    status,
    seller_memo,
    buyer_memo,
    clean_ip,
    end_time,
    pay_time,
    is_sub,
    is_parent,
    shop_id,
    total_fee,
    is_large_order_flag;

  3. Configure the debug parameters.

    On the right side of the MaxCompute SQL node editing page, click Run Configuration:

  4. On the node toolbar, click Save to save the node.

Configure the dws_daily_category_sales node

Based on the dwd_trade_order table and the dim_item_info table, this node aggregates the cleansed and standardized detail data from the DWD layer and produces the daily product category sales aggregate table dws_daily_category_sales.

  1. On the workflow orchestration page, hover over the dws_daily_category_sales node and click Open Node.

  2. Paste the following code into the node editing page.

    CREATE TABLE IF NOT EXISTS dws_daily_category_sales (
    cate_id             BIGINT COMMENT 'Item leaf category ID',
    cate_name           STRING COMMENT 'Item leaf category name',
    total_sales_amount  DECIMAL(38,18) COMMENT 'Total sales amount by item category, in cents',
    order_count         BIGINT COMMENT 'Order count'
)
COMMENT 'Daily item category sales summary table'
PARTITIONED BY (pt STRING COMMENT 'Business date, yyyymmdd')
LIFECYCLE 365;
INSERT OVERWRITE TABLE dws_daily_category_sales PARTITION(pt='${bizdate}')
SELECT
    i.cate_id,
    i.cate_name,
    SUM(t.total_fee) AS total_sales_amount,
    COUNT(DISTINCT t.id) AS order_count
FROM dwd_trade_order t
JOIN dim_item_info i ON t.item_id = i.item_id AND t.pt = i.pt
WHERE t.pt = '${bizdate}'
GROUP BY t.pt, i.cate_id, i.cate_name;

  3. Configure the debug parameters.

    On the right side of the MaxCompute SQL node editing page, click Run Configuration:

  4. On the node toolbar, click Save to save the node.

Configure the ads_top_selling_categories node

Based on the dws_daily_category_sales table, this node produces the daily ranking of best-selling product categories table ads_top_selling_categories.

  1. On the workflow orchestration page, hover over the ads_top_selling_categories node and click Open Node.

  2. Paste the following code into the node editing page.

    CREATE TABLE IF NOT EXISTS ads_top_selling_categories (
    rank                BIGINT COMMENT 'Sales rank',
    cate_id             BIGINT COMMENT 'Item leaf category ID',
    cate_name           STRING COMMENT 'Item leaf category name',
    total_sales_amount  DECIMAL(38,18) COMMENT 'Total sales amount by item category, in cents',
    order_count         BIGINT COMMENT 'Order count'
)
COMMENT 'Daily top-selling item category ranking table'
PARTITIONED BY (pt STRING COMMENT 'Business date, yyyymmdd');
INSERT OVERWRITE TABLE ads_top_selling_categories PARTITION(pt='${bizdate}')
SELECT
    rank,
    cate_id,
    cate_name,
    total_sales_amount,
    order_count
FROM (
    SELECT
        DENSE_RANK() OVER(ORDER BY total_sales_amount DESC) AS rank,
        cate_id,
        cate_name,
        total_sales_amount,
        order_count
    FROM (
        SELECT
            cate_id,
            cate_name,
            SUM(total_sales_amount) AS total_sales_amount,
            SUM(order_count) AS order_count
        FROM dws_daily_category_sales
        WHERE pt = '${bizdate}'
        GROUP BY cate_id, cate_name
    ) agg_sub
) agg_outer
WHERE rank <= 10;

  3. Configure the debug parameters.

    On the right side of the MaxCompute SQL node editing page, click Run Configuration:

  4. On the node toolbar, click Save to save the node.

Step 3: Debug and run

Run the entire workflow to verify that the configuration is correct before deploying it to the production environment.

  1. In the left-side navigation pane of Data Studio, click image to go to the data development page. Then, in the Project Directory section, find the workflow you created.

  2. On the node toolbar, click Run. In Fill in the operation parameters, enter the date of the previous day (for example, 20250416).

    Note

    The workflow node configuration uses the scheduling parameters provided by DataWorks for dynamic parameter passing in code. During debugging, you need to assign a constant value to the parameter for testing.

  3. Click Determine to go to the debug and run page.

  4. Wait for the run to complete. The expected result is as follows:

    image

Step 4: Data query and visualization

The processed results are now in the ads_top_selling_categories table. Query this table to view the analysis results.

  1. Click the image icon in the upper-left corner. In the pop-up page, click All Products > DataAnalysis > SQL Query.

  2. After My Files, click image > Create File. Specify a custom File Name and click Determine.

  3. On the SQL query page, enter the following SQL statement.

    SELECT * FROM ads_top_selling_categories WHERE pt=${bizdate};

  4. In the upper-right corner, select the MaxCompute data source and click Determine.

  5. Click the Run button at the top. On the Estimate Costs page, click Run.

  6. In the query results, click image to view the visual chart results. You can click image in the upper-right corner of the chart to customize the chart style. For more information about customizing chart styles, see Customize chart styles.

    The visual chart results are displayed in the Copilot section in chart format (such as a donut chart) showing the distribution of order count, sales amount, and other metrics by category.

  7. You can also click Save in the upper-right corner of the chart to save the chart as a card. Then, in the left-side navigation pane, click Card (image) to view it.

Step 5: Periodic scheduling

To obtain the latest sales data every day, deploy the workflow to the production environment so that it runs periodically on a schedule.

Note

When configuring data synchronization and data processing, you have already configured the schedule-related parameters for the workflow, synchronization nodes, and data processing nodes. No additional configuration is needed. You only need to deploy the workflow to the production environment. For more information about schedule settings, see Configure schedule settings.

  1. Click the image icon in the upper-left corner. In the pop-up page, click All Products > Data Development and O&M > DataStudio.

  2. In the left-side navigation pane of Data Studio, click image to go to the data development page. Switch to the workspace used in this tutorial. Then, in the Project Directory section, find the workflow you created.

  3. On the node toolbar, click Publish. In the deploy panel, click Start Release Production. After Deploy Package Build and Production Checker are complete, click Create Deploy Task.

  4. After the Deploy to Production status changes to Completed, click Go to Operation Center to go to Operation Center.

  5. In Auto Triggered Task O&M > Auto Triggered Task, you can see the scheduled tasks for the workflow (the workflow in this tutorial is named dw_quickstart).

  6. To view the details of scheduled tasks for child nodes within the workflow, right-click the scheduled task of the workflow and select View Internal Tasks.

    The expected result is as follows:

    image

Next steps

Appendix: Resource release and cleanup

To release the resources created in this tutorial, perform the following steps:

  1. Stop scheduled tasks.

    1. Log on to the DataWorks console. In the target region, click Data Development and O&M > Operation Center in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Operation Center.

    2. In Auto Triggered Task O&M > Auto Triggered Task, select all the scheduled tasks you created previously (the workspace root node does not need to be undeployed), and then click Operation > Undeploy at the bottom.

  2. Delete data development nodes and disassociate the MaxCompute compute resource.

    1. Go to the Workspaces page in the DataWorks console. In the top navigation bar, select a desired region. Find the desired workspace and choose Shortcuts > Data Studio in the Actions column.

    2. In the left-side navigation pane of Data Studio, click image to go to the data development page. Then, in the Project Directory section, find the workflow you created, right-click it, and click Delete.

    3. In the left-side navigation pane, click image > Computing Resource Management. Find the associated MaxCompute compute resource and click Disassociate. In the confirmation dialog, select the options and follow the instructions to complete the disassociation.

  3. Delete the MySQL data source.

    1. Log on to the DataWorks console. In the target region, click More > Management Center in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Management Center.

    2. In the left-side navigation pane, click Data Sources to go to the Data Sources page. Find the MySQL data source you created and click Delete in the Operation column. Follow the instructions to complete the deletion.

  4. Delete the MaxCompute project.

    Go to the MaxCompute project management page. Find the MaxCompute project you created and click Delete in the Operation column. Follow the instructions to complete the deletion.

  5. Delete the Internet NAT Gateway and release the EIP.

    1. Go to the VPC console - Internet NAT Gateway, and switch to the China (Shanghai) region in the top menu bar.

    2. Find the Internet NAT Gateway you created and click image > Delete in the Operation column. In the confirmation dialog, select Forcibly Delete, and then click Determine.

    3. In the left-side navigation pane, click Public Access > Elastic IP Address. Find the EIP you created and click image > Instance Management > Release in the Operation column. In the confirmation dialog, click Determine.

Previous：Simple case: Analysis of house buying groupsNext：Tutorial: User profile analysis