Step 2: Develop Data

Analyze 10 Products Users Like Most

1. Log in to the DataArts Studio console. Locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Factory.
   Figure 1 DataArts Factory
   

2. Create a DLI SQL script used to create data tables by entering DLI SQL statements in the editor.
   Figure 2 Creating a script
   

3. In the SQL editor, enter the following SQL statements and click Execute to calculate 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 key parameters are as follows:

   • Data Connection: DLI data connection created in Step 3
   • Database: database created in step 5
   • Resource Queue: the default resource queue default

4. 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.
5. After the script is saved and executed successfully, you can use the following SQL statement to view 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
   

Analyze 10 Products Users Dislike Most

1. Log in to the DataArts Studio console. Locate an instance and click Access. On the displayed page, locate a workspace and click DataArts Factory.
   Figure 5 DataArts Factory
   

2. Create a DLI SQL script used to create data tables by entering DLI SQL statements in the editor.
   Figure 6 Creating a script
   

3. In the SQL editor, enter the following SQL statements and click Execute to calculate 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 key parameters are as follows:

   • Data Connection: DLI data connection created in Step 3
   • Database: database created in step 5
   • Resource Queue: the default resource queue default

4. After debugging the script, click Save and Submit to save the script and name it top_bad_comment_product. This script will be referenced later in Developing and Scheduling a Job.
5. After the script is saved and executed successfully, you can use the following SQL statement to view 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

Assume that the BI reports in the OBS bucket are changing every day. To update the analysis result every day, use the job orchestration and scheduling functions of DataArts Factory.