Use Copilot and enhanced analysis for data query and visualization

Notes

• DataWorks Copilot is currently in public preview and has region and version limitations. For more information, see Code Assistant.

• For the SQL code used in this tutorial, see Appendix: SQL code for all cards in the report.

Prerequisites

Use Copilot features in DataWorks

1. Go to the Copilot interface.

   On the DataStudio page, click Copilot on the right side of the top menu bar to open the Copilot interface.

2. View the supported user operations in Copilot.

   In the Copilot dialog box (hereinafter referred to as Copilot Chat), enter "/" to view the supported user operations. You can select an operation or enter a request directly in natural language.

   The following table describes each operation.

   Actions	Description
   Find Tables	In Copilot Chat, enter a command, such as "website user page access log table". In the results from Copilot, select the radio button next to the desired table name. Copilot will use this table for subsequent SQL operations.
   SQL Generation	In Copilot Chat, enter a command, such as "count the number of page views in different time periods". Click the Send button and wait for Copilot to return the result.
   New Node	After generating the SQL, you can insert it into the current node or a new node. Example: In the chat results, click Insert into New Node in the upper-right corner of the SQL snippet. A new ODPS SQL node will be created, and the generated SQL will be inserted into it.
   SQL Rewrite	Click SQL Rewrite and enter a command in the input box, such as "write the result to the partitioned table dws_ali_e_commerce and generate the CREATE TABLE statement for dws_ali_e_commerce". Click the Send button and wait for Copilot to return the result.
   Generate Comments	The DDL statement generated in the previous step may not include COMMENT information for fields, making the table difficult for other users to understand after the task is published. Use the Generate Comments feature to add them. In the editor, right-click the SQL code, choose Copilot > SQL Chat > Generate Comments, click the Send button, and wait for Copilot to return the result.
   Fix SQL	In the editor, right-click the SQL code, choose Copilot > SQL Chat > Fix SQL, click the Send button, and wait for Copilot to return the result.
   Explain SQL	In the editor, select the target SQL, click Copilot at the top of the page, and enter a command in the chat dialog box, such as "Explain this SQL". Click the Send button and wait for Copilot to return the result.
   Scheduling Configuration	Before publishing your code as a scheduled task, you must configure its schedule. In the chat dialog box, enter a command, such as "Set the schedule to run at 2 AM every day, and make it rerunnable". Click the Send button and wait for Copilot to return the result.
   Submit Task	After the data development code is ready and the schedule is configured, you can submit the task. Click Apply and Submit Node below the scheduling configuration card in Copilot Chat to start the task submission workflow.

Query data in DataWorks

1. On the DataStudio page, click the icon in the upper-left corner and choose All Products > Data Analysis > SQL Query.

2. On the SQL Query page in DataWorks, a new SQL query page opens by default. Copy the SQL code from the previous steps into this page.

   SELECT  CASE    WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 0
                    AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 3 THEN '00:00-03:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 3
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 6 THEN '03:00-06:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 6
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 9 THEN '06:00-09:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 9
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 12 THEN '09:00-12:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 12
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 15 THEN '12:00-15:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 15
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 18 THEN '15:00-18:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 18
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 21 THEN '18:00-21:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 21
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 24 THEN '21:00-24:00'
                   ELSE 'Other'
           END AS time_period
           ,COUNT(1) as page_views
   FROM    public_cloud_simple.dwd_ali_e_commerce
   WHERE   behavior_type = 1
   GROUP BY time_period
   order by time_period;

3. Click the icon in the upper-right corner of the SQL query file to set the workspace and engine information.

4. After completing the settings, click the Run button in the SQL query file's action bar. After the query runs successfully, the results are displayed below the SQL file.

Create an enhanced analysis card

To visualize the data in the SQL query results, click the icon. A default chart is automatically generated.

• If the default chart does not meet your expectations, click the icon in the upper-right corner of the chart to open the chart editing page.

  This tutorial demonstrates how to modify the chart's axis titles.

  1. On the right side of the chart editing page, click Style Settings > Chart Style.

  2. Click Axis Title for the X-axis and Y-axis and modify the titles as needed. After modification, click Save in the upper-right corner to return to the SQL query results page.

• If the default or modified chart meets your expectations, click Save in the upper-right corner of the chart to save and share the result. Click the icon in the left-side menu to view the saved card.

Create an enhanced analysis report

To combine and share multiple cards, charts, and descriptions in a logical flow, create a DataWorks report.

1. Click the report icon in the left-side menu. On the report list page, click Create Report in the upper-right corner.

2. In the dialog box that appears, select the cards you want to add to the report.

3. Click OK in the lower-right corner of the dialog box to return to the report editing page.

   • To adjust the display order of the cards in the report, drag them in the card order editing area on the left.

   • To modify the report title, double-click the chart title.

   In this tutorial, the chart title is changed to "User Analysis Report for a Shopping App". On the right side of the page, select a suitable report theme. In the Report Settings panel on the right, you can switch the Report Layout (Long Image or dashboard) and choose a Report Theme with different color schemes. At the bottom of the card order panel on the left, click Add Card to add new cards.

4. Click Create in the upper-right corner to return to the report list page. Hover over the report you want to share, click the icon in the upper-right corner, and copy the sharing link from the dialog box to send to others. The sharing address for the report in this tutorial is "User Analysis Report for a Shopping App".

Upload custom data to MaxCompute

You can upload local files to a compute engine like MaxCompute for large-scale processing or to perform joint analysis with online data. To do so, follow these steps:

1. On the SQL query page, click the icon in the upper-left corner and choose All Products > Data Integration > Upload and Download.

2. Click the Upload icon in the left-side navigation pane to go to the data upload page.

3. Click Data Upload and follow the on-screen instructions to upload your data.

   On the data upload page, you can upload a local .csv file to an existing table in a MaxCompute project, or create a new MaxCompute table during the upload process. This tutorial demonstrates uploading the local file "2013-2020 Global PS4 Game Sales.csv" to a new table named dws_game_salse_nd in the MaxCompute project doc_test_000.

   The main parameter settings are as follows:

   • Specify Data to Upload: Select the file "2013-2020 Global PS4 Game Sales.csv". You can get the file from 2013-2020 Global PS4 Game Sales.csv.

   • Set Target Table: Set MaxCompute Project Name to doc_test_000, Target Table to New Table, Table Name to dws_game_salse_nd, Table Type to non-partitioned table, and Lifecycle to 365 days.

   After configuration, you can check the Uploaded File Data Preview to ensure the data is displayed correctly. If there are encoding issues, you can adjust the file encoding.

4. Click Data Upload in the lower-left corner to start uploading.

   A dialog box appears showing the upload progress. Closing this dialog box does not interrupt the upload. You can also click Return to List to view the progress of this upload or start a new one.

5. After the data upload is complete, you can perform the following actions in the dialog box:

   • View the success or failure status and the end time of the upload task.

   • Click Data Query to query and perform a joint analysis on the uploaded data.

   • Start a new data upload or return to the upload list page to view details.

The preceding steps show how to perform a one-time manual upload to MaxCompute. For regular data synchronization, use DataWorks Data Integration. For details, see Data Integration.

Appendix: SQL code for report cards

• Page views by time period

  SELECT  CASE    WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 0
                   AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 3 THEN '00:00-03:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 3
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 6 THEN '03:00-06:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 6
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 9 THEN '06:00-09:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 9
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 12 THEN '09:00-12:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 12
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 15 THEN '12:00-15:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 15
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 18 THEN '15:00-18:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 18
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 21 THEN '18:00-21:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 21
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 24 THEN '21:00-24:00'
                  ELSE 'Other'
          END AS time_period
          ,COUNT(1) as page_views
  FROM    public_cloud_simple.dwd_ali_e_commerce
  WHERE   behavior_type = 1
  GROUP BY time_period
  order by time_period;

• Peak order time periods

  SELECT  CASE    WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 0
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 3 THEN '00:00-03:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 3
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 6 THEN '03:00-06:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 6
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 9 THEN '06:00-09:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 9
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 12 THEN '09:00-12:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 12
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 15 THEN '12:00-15:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 15
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 18 THEN '15:00-18:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 18
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 21 THEN '18:00-21:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 21
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 24 THEN '21:00-24:00'
                  ELSE 'Other'
          END AS time_period
          ,COUNT(DISTINCT user_id) AS ordering_users
  FROM    public_cloud_simple.dwd_ali_e_commerce
  WHERE   behavior_type = '4'
  GROUP BY time_period
  ORDER BY time_period ASC
  LIMIT   100
  ;

• Number of users who placed orders by time period

  SELECT  CASE    WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 0
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 3 THEN '00:00-03:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 3
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 6 THEN '03:00-06:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 6
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 9 THEN '06:00-09:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 9
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 12 THEN '09:00-12:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 12
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 15 THEN '12:00-15:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 15
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 18 THEN '15:00-18:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 18
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 21 THEN '18:00-21:00'
                  WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 21
                      AND CAST(SUBSTR(behavior_time,12) AS BIGINT) < 24 THEN '21:00-24:00'
                  ELSE 'Other'
          END AS time_period
          ,COUNT(DISTINCT user_id) AS ordering_user_count
  FROM    public_cloud_simple.dwd_ali_e_commerce
  WHERE   behavior_type = '4'
  GROUP BY time_period
  ORDER BY time_period ASC
  LIMIT   100
  ;

• User behavior trend over time

  SELECT  CAST(SUBSTR(behavior_time,12) AS BIGINT) AS time_period
          ,CASE   WHEN behavior_type = '1' THEN 'Product page view'
                  WHEN behavior_type = '2' THEN 'Product favorite'
                  WHEN behavior_type = '3' THEN 'Add to cart'
                  WHEN behavior_type = '4' THEN 'Successful purchase'
                  ELSE behavior_type
          END AS user_action
          ,COUNT(DISTINCT user_id) AS user_count
  FROM    public_cloud_simple.dwd_ali_e_commerce
  WHERE   behavior_type IN ('1','3','4')
  GROUP BY time_period
           ,user_action
  ORDER BY time_period ASC
  LIMIT   10000
  ;

• Number of ordering or browsing users

  SELECT  CASE    WHEN behavior_type = '1' THEN 'Product page view'
                  WHEN behavior_type = '2' THEN 'Product favorite'
                  WHEN behavior_type = '3' THEN 'Add to cart'
                  WHEN behavior_type = '4' THEN 'Successful purchase'
                  ELSE behavior_type
          END AS behavior_type
          ,COUNT(DISTINCT user_id) AS ordering_users
  FROM    public_cloud_simple.dwd_ali_e_commerce
  GROUP BY behavior_type
  ORDER BY ordering_users DESC
  LIMIT   10000
  ;
  

• Distribution of online shopping order times

  SELECT  CAST(SUBSTR(behavior_time,12) AS BIGINT) AS time_period
          ,COUNT(DISTINCT user_id) AS user_count
  FROM    public_cloud_simple.dwd_ali_e_commerce
  WHERE   behavior_type IN ('4')
  GROUP BY time_period
  ORDER BY time_period ASC
  LIMIT   10000
  ;