Help Center/ DataArts Studio/ Getting Started/ Beginners: DLI-powered Data Development Based on E-commerce BI Reports/ Step 2: Develop Data
Updated on 2026-08-31 GMT+08:00
View PDF
Share

Step 2: Develop Data

When you want to quickly understand user preferences and pain points, but there is a huge amount of raw BI report data, it is difficult to manually extract key insights from the data in real time and make timely decisions. Through periodic job scheduling, you can analyze and export the 10 products users like most and the 10 products users dislike most to data tables for efficient information analysis.

Script Development

This section describes how to develop scripts for analyzing the 10 products users like most and the 10 products users dislike most.

  1. Log in to the DataArts Studio console.

    For details, see Accessing the DataArts Studio Instance Console.

  2. On the DataArts Studio console, locate a workspace and click DataArts Factory.
  3. Create a DLI SQL script by referring to Figure 1. With the script, you can create data tables using DLI SQL statements.

    Figure 1 Creating a script

  4. In the displayed SQL editor, set key parameters.

    Table 1 Key parameters

    Parameter

    Description

    Connection

    DLI data connection created in Step 5

    Database

    Database created in Step 7

    Queue

    The default resource queue default can be used.
    Figure 2 Key parameters

    Enter the following SQL statements and click Execute to obtain the 10 products users like most from the original data table in the OBS bucket and save the result to the top_like_product table. 
    INSERT
  OVERWRITE table top_like_product
SELECT
  product.brand as brand,
  COUNT(product.brand) as like_count
FROM
  action
  JOIN product ON (action.product_id = product.product_id)
WHERE
  action.type = 'like'
group by
  brand
ORDER BY
  like_count desc
LIMIT
  10
    Figure 3 Script for analyzing the 10 products users like most
    • The version of the default Spark component of the default DLI queue is not up-to-date, and an error may be reported indicating that a table creation statement cannot be executed. In this case, you are advised to create a queue to run your tasks. If you want to execute table creation statements in the default queue, contact the DLI customer service or technical support.

    • The default queue default of DLI is only used for trial. It may be occupied by multiple users at a time. Therefore, it is possible that you fail to obtain the resource for related operations. If the execution takes a long time or fails, you are advised to try again during off-peak hours or use a self-built queue to run the job.

  5. After debugging the script, click Save to save the script and name it top_like_product. Click Submit to submit the script version. This script will be referenced later in Developing and Scheduling a Job.
  6. After the script is saved and executed successfully, create a DLI SQL script by referring to step 3 and run the following SQL statement to view the data in the top_like_product table. You can also download or dump the table data by referring to Figure 4. 

    SELECT * FROM top_like_product
    Figure 4 Viewing the data in the top_like_product table

  1. Log in to the DataArts Studio console.

    For details, see Accessing the DataArts Studio Instance Console.

  2. On the DataArts Studio console, locate a workspace and click DataArts Factory.
  3. Create a DLI SQL script by referring to Figure 5. With the script, you can create data tables using DLI SQL statements.

    Figure 5 Creating a script

  4. In the displayed SQL editor, set key parameters.

    Table 2 Key parameters

    Parameter

    Description

    Connection

    DLI data connection created in Step 5

    Database

    Database created in Step 7

    Queue

    The default resource queue default can be used.
    Figure 6 Key parameters

    Enter the following SQL statements and click Execute to obtain the 10 products users dislike most from the original data table in the OBS bucket and save the result to the top_bad_comment_product table. 
    INSERT
  OVERWRITE table top_bad_comment_product
SELECT
  DISTINCT product_id,
  comment_num,
  bad_comment_rate 
FROM 
  comment 
WHERE 
  comment_num > 3 
ORDER BY
  bad_comment_rate desc 
LIMIT
  10
    Figure 7 Script for analyzing the 10 products users dislike most
    • The version of the default Spark component of the default DLI queue is not up-to-date, and an error may be reported indicating that a table creation statement cannot be executed. In this case, you are advised to create a queue to run your tasks. If you want to execute table creation statements in the default queue, contact the DLI customer service or technical support.

    • The default queue default of DLI is only used for trial. It may be occupied by multiple users at a time. Therefore, it is possible that you fail to obtain the resource for related operations. If the execution takes a long time or fails, you are advised to try again during off-peak hours or use a self-built queue to run the job.

  5. After debugging the script, click Save to save the script and name it top_bad_comment_product. Click Submit to submit the script version. This script will be referenced later in Developing and Scheduling a Job.
  6. After the script is saved and executed successfully, create a DLI SQL script by referring to step 3 and run the following SQL statement to view the data in the top_bad_comment_product table. You can also download or dump the table data by referring to Figure 8. 

    SELECT * FROM top_bad_comment_product
    Figure 8 Viewing the data in the top_bad_comment_product table

Developing and Scheduling a Job

The raw BI reports stored in OBS are usually updated daily. Business users want to obtain the latest analysis results in a timely manner to support decision-making. However, manually synchronizing updated data every day is not only inefficient, but also prone to data inconsistency or loss. You can use the job orchestration and scheduling functions provided by DataArts Factory to enable automatic updates of the results every day.

  1. Log in to the DataArts Studio console.

    For details, see Accessing the DataArts Studio Instance Console.

  2. On the DataArts Studio console, locate a workspace and click DataArts Factory.
  3. Create a batch processing job named BI_analysis.

    Figure 9 Creating a job
    Figure 10 Configuring the job

  4. On the data development page, drag two Dummy nodes and two DLI SQL nodes to the canvas and select and drag to orchestrate the job shown in Figure 11.

    Figure 11 Connecting nodes and configuring node properties
    Table 3 Key nodes

    Key Node

    Description

    Begin (Dummy node)

    Identifies the start and does not perform any operation.

    top_like_product (DLI SQL node)

    In the node properties, associate it with the DLI SQL script top_like_product developed in Analyzing the 10 Products Users Like Most.

    top_bad_comment_product (DLI SQL node)

    In the node properties, associate it with the DLI SQL script top_bad_comment_product developed in Analyzing the 10 Products Users Dislike Most.

    Finish (Dummy node)

    Identifies the end and does not perform any operation.

  5. After orchestrating the job, click Test () to test it.

    Figure 12 Testing the Job

  6. If the job runs properly, click Scheduling Setup in the right pane and configure the scheduling policy for the job.

    Figure 13 Configuring scheduling

    Table 4 Scheduling parameters

    Parameter

    Description

    Schedule Type

    Select Run periodically.

    Scheduling Properties

    Set the job scheduling time, for example, 01:00 every day from February 9, 2022 to February 28, 2022.

    Dependency Properties

    You can configure a dependency job for this job. You do not need to configure it in this example.

    Cross-Cycle Dependency

    The options include Independent on the previous schedule cycle and Self-dependent. Select Independent on the previous schedule cycle in this example.

  7. Click Save, Submit, and Execute in sequence. The job will be automatically executed every day and the BI report analysis results are automatically saved to the top_like_product and top_bad_comment_product tables.

    Figure 14 Executing the job

  8. Click Save, Submit (), and Execute (). Then the job will be automatically executed every day and the BI report analysis result is automatically saved to the top_like_product and top_bad_comment_product tables, respectively.
  9. In the left navigation pane on the DataArts Factory page, choose Monitoring > Monitor Instance and view the job execution result, as shown in Figure 15.

    Figure 15 Viewing the job execution result

You can also configure notifications to be sent through SMS messages or emails, when a job encounters exceptions or fails.

Now you have learned the data development process based on e-commerce BI reports. In addition, you can analyze the age distribution and gender ratio of users and their browsing, purchase, and evaluation of products to provide valuable information for marketing decision-making, advertising, credit rating, brand monitoring, and user behavior prediction.

Parent Topic: Beginners: DLI-powered Data Development Based on E-commerce BI Reports