How to use Data Analysis in DataWorks - DataWorks - Alibaba Cloud Documentation Center

The Data Analysis feature in DataWorks provides tools for analyzing and sharing data. You can connect to various data sources, run SQL queries, and use spreadsheets to manage daily data extraction and analysis. This topic shows you how to use the MaxCompute engine with SQL Query to analyze a public dataset, introducing the basic features of Data Analysis.

Features

DataWorks provides public datasets for various business scenarios. This topic uses the Alibaba e-commerce dataset (the commerce_ali_e_commerce table) to help you get started with the Data Analysis feature.

Alibaba e-commerce dataset: Contains statistics on Taobao order details from different time periods.
- The table records random user actions, including clicks, purchases, cart additions, and favorites, from approximately 1 million users between November 25, 2017 and December 3, 2017.
- Number of users: 987,994. Number of products: 4,162,024. Total number of actions: 100,150,807.
To learn more about the Data Analysis feature, see Data Analysis.
Public datasets are available in the following regions: China (Shanghai), China (Beijing), China (Shenzhen), China (Hangzhou), China (Chengdu), China (Zhangjiakou), and China (Ulanqab).

Permissions

You must have the required permissions for Data Analysis. For a complete list of permissions, see Permissions of preset roles for Data Analysis.
To grant a role to a user, see Add a workspace member and manage their role and permissions.

Prerequisites

You have configured a MaxCompute data source. For more information, see Bind MaxCompute compute resources.

Access SQL Query

Log on to the DataWorks console. In the target region, click Data Analysis and Service > DataAnalysis in the left-side navigation pane. Click Go to DataAnalysis to open the Data Analysis page. In the left-side navigation pane, click SQL Query.

Step 1: Query data

This example uses the Alibaba e-commerce dataset (commerce_ali_e_commerce), which contains Taobao order details from different time periods. You will use the SQL Query feature to query and sort the dataset, and then analyze and share the results.

Go to SQL Query.
You can access it in one of the following ways:
- On the Data Analysis homepage, in the Shortcuts area, click SQL Query to open the SQL Query page.
- In the Data Analysis module, click SQL Query in the left-side navigation pane to open the SQL Query page.
Create a Temporary File.
In the pane on the left, click the icon next to My Files, select Create File, and create an SQL query file as prompted. For more ways to create an SQL query file, see SQL Query (Old).
Note
- This topic uses a public dataset provided by DataWorks. When you access SQL Query for the first time, the Welcome Page page appears. You can then click Go to DataAnalysis > MaxCompute to generate a query for the Alibaba e-commerce dataset (commerce_ali_e_commerce).
- To find more public datasets, go to Data Analysis > SQL Query and look in the Public Data directory.
Select a Data Source for the query.
In the temporary file editor, click the icon in the upper-right corner. Select the workspace, engine, and data source for the query. For this example, select the MaxCompute data source you configured earlier.

Edit and run the code.

In the code editor, edit and run the following code. 编辑代码

This query uses the public dataset to count and sort Taobao orders by time period.

SET odps.namespace.schema = true
;

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) >= 4
                    AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 7 THEN '04:00-07:00'
                WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 8
                    AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 11 THEN '08:00-11: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) >= 16
                    AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 19 THEN '16:00-19:00'
                WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 20
                    AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 23 THEN '20:00-23:00'
        END AS order_time
        ,COUNT(*) AS order_count
FROM    bigdata_public_dataset.commerce.commerce_ali_e_commerce
GROUP BY order_time
ORDER BY COUNT(*) DESC
LIMIT   100
;

View the query results.
Click the icon to run the SQL code.

Step 2: Analyze data

On the query results page from Step 1, click the Display icon in the left navigation bar, and then click the Edit icon to open the chart editing page. You can edit the chart information based on your business requirements.

Step 3: Share data

You can share SQL query results with other users by exporting them to a spreadsheet for online collaboration.

Export the query results.
In Step 1, on the query results page, click the icon on the right and select Workbook and share from the drop-down menu.
Go to the Spreadsheet page.
On the Spreadsheet page, you can sync the query results. For more information about spreadsheet operations, see Spreadsheet.
Share the query results.
In the upper-right corner of the page, click Share. Recipients can access the spreadsheet with a URL or an access code. You can grant them either edit or view-only permissions.

分享数据

Next steps

For more information about SQL Query, see SQL Query (Old).
For more information about spreadsheet operations, see Spreadsheet.