Platform For AI:Use SQL File to query MaxCompute tables

Billing

When you execute SQL statements to query data, you are charged only computing and storage fees of MaxCompute. DSW is free of charge.

Prerequisites

Before you perform the operations that are described in this topic, make sure that the following requirements are met:

• MaxCompute is activated. For more information, see Activate MaxCompute and DataWorks.

• A RAM user has the permissions to perform operations on MaxCompute projects. If you use your Alibaba Cloud account to log on to a DSW instance, you do not need to grant permissions. If you use a RAM user to log on to a DSW instance, you need to grant the RAM user permissions by performing the following steps:

  Procedure

  1. Log on to the MaxCompute console by using your Alibaba Cloud account. In the upper-left corner of the console, select a region.

  2. In the left-side navigation pane, choose Workspace > Projects.

  3. On the Projects page, find the desired project and click Manage in the Actions column.

  4. On the Parameter Configuration tab of the project details page, click the Role Permissions tab. On the tab that appears, find the role_project_dev role and click Manage Members. In the Manage Members dialog box, add the desired RAM user.

  For more information about MaxCompute permission management, see Manage user permissions in the MaxCompute console.

Procedure

1. Go to the DSW development environment.

   1. Log on to the PAI console.

   2. In the left-side navigation pane, click Workspaces. On the Workspaces page, click the name of the workspace that you want to view.

   3. In the upper-left corner of the workspace page, select the region in which you want to use the service.

   4. In the left-side navigation pane, choose Model Training > Data Science Workshop (DSW).

   5. Optional. On the Interactive Modeling (DSW) page, enter an instance name or a keyword in the search box to search for the DSW instance that you want to view.

   6. Find the DSW instance and click Open in the Actions column.

2. On the Launcher page of the Notebook tab, click SQL File in the Text and terminal section to create an SQL query file.

   

3. Configure a MaxCompute data source.

   1. In the upper-right corner of the main workbench of the SQL file, click the data source icon.

   2. In the Data Source Settings panel, configure the parameters that are described in the following table and click Select.

      Parameter	Description
      MaxCompute Endpoint	The endpoint of the MaxCompute project. For more information, see Endpoints. For example, if your MaxCompute project is deployed in the China (Hangzhou) region, the endpoint is http://service.cn-hangzhou.maxcompute.aliyun.com/api.
      Tunnel Endpoint	The endpoint of the Tunnel service of the MaxCompute project. For more information, see Endpoints. For example, if your MaxCompute project is deployed in the China (Hangzhou) region, the endpoint is http://dt.cn-hangzhou.maxcompute.aliyun.com.
      MaxCompute Project	The name of the MaxCompute project.

4. Execute statements.

   In the SQL editor, write the statements that you want to execute to query the data of the MaxCompute data source. Then, click the Run icon in the toolbar and view the query results.

   • Execute statements: You can click the Run icon on the left side of a statement to execute the statement. You can also select the statements that you want to execute and click the icon in the toolbar. After you execute a statement, you can view the execution results.

   • Format statements: You can click the Format icon in the toolbar to write your statements in the standard format.

5. View the query results.

   After you execute the SQL statements, you can perform various operations on the query results. The following table describes the operations.

   Operation	Screenshot	Description
   View the query results		You can view the query results of SQL statements.
   Visualize the query results		You can view the query results in line charts, column charts, bar charts, pie charts, and cross tables. You can drag fields to the X-axis and Y-axis to create charts and tables. The fields on the X-axis are dimensions, and the fields on the Y-axis are metrics. You can click a field to change the field type and specify an alias for the field.
   View the SQL statements that correspond to a query result		You can view the SQL statements that correspond to the query result. You can also copy and paste the SQL statements to the code editor.
   View the operation logs that are generated during the query process		You can view the operation logs that are generated for the query statements. If you fail to execute the SQL statements, you can click the logview link in the generated logs to troubleshoot the issue.

References

To read MaxCompute data, you can also use PyODPS. For more information, see Use PyODPS to read data from and write data to MaxCompute tables.