Quick start

Prerequisite

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 query results.

1. Go to SQL Query.

   You can access SQL Query in the following ways:

   • On the Data Analysis homepage, in the Shortcuts section, click SQL Query to go to the SQL Query page.

   • In the Data Analysis module, click SQL Query in the left-side navigation pane to go to the SQL Query page.

2. Create a Temporary File.

   In the left-side pane, click the icon next to My Files, and select Create File to create an SQL query file as prompted. For more ways to create SQL query files, see SQL Query.

   Note

   • This topic uses the public datasets provided by DataWorks. When you access SQL Query for the first time, you can click Go to DataAnalysis > MaxCompute on the Welcome Page to generate a query for the Alibaba e-commerce dataset (commerce_ali_e_commerce).

   • For more public datasets, go to Data Analysis > SQL Query and view them in the Public Data directory.

3. Select the SQL execution Data Source.

   On the temporary file editing page, click the icon in the upper-right corner to select the workspace, engine type, and data source for the current SQL query. In this example, select the MaxCompute data source that you have created.

4. Write and run the task code.

   In the code editor of the temporary query file, write and run the following code.

   Based on the public dataset, count and sort the number of Taobao orders during different time periods.

   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 '0:00-3:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 4
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 7 THEN '4:00-7:00'
                   WHEN CAST(SUBSTR(behavior_time,12) AS BIGINT) >= 8
                       AND CAST(SUBSTR(behavior_time,12) AS BIGINT) <= 11 THEN '8: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
   ;

5. View the query results.

   Click the icon to run the SQL code.

   The query results table contains two columns: Order Time and Order Count, with 6 rows of data.

   Order Time	Order Count
   20:00-23:00	376582343
   16:00-19:00	226263195
   12:00-15:00	222907927
   08:00-11:00	186475432
   00:00-03:00	95900494
   04:00-07:00	57393434

Step 2: Analyze data

On the query result page from Step 1, click the icon in the left-side navigation pane, and then click the edit icon to go to the chart editing page. You can edit chart information based on your business requirements.

Step 3: Share data

You can share SQL query results with other users as spreadsheets to enable online data sharing.

1. Export the query results.

   On the query result page from Step 1, click the icon on the right side, and select Workbook and share from the drop-down menu.

2. Go to the Spreadsheet page.

   On the Spreadsheet page, you can synchronize query results. For more information about spreadsheet operations, see Spreadsheet operations.

3. Share the query results.

   Click Share in the upper-right corner of the page to share the data with specific users. Shared users can access the result table through a URL or an access code. You can specify whether shared users can edit or only view the result table.

Next steps

• For more information about SQL Query, see SQL Query.

• For more information about spreadsheet operations, see Spreadsheet operations.