Use ad hoc queries to run SQL (Optional) - - Alibaba Cloud Documentation Center

If you have a DataWorks workspace associated with a MaxCompute project, you can use the ad hoc query feature in DataWorks to write and run SQL statements on MaxCompute.

Prerequisites

You have created a MaxCompute data source and bound it to DataStudio.

Before you create nodes and develop tasks for MaxCompute, you must add your MaxCompute project as a MaxCompute data source to your DataWorks workspace and bind it to DataStudio to serve as the underlying engine. For more information, see Associate a MaxCompute compute resource and Prerequisites.

Create an ad hoc query node

Log on to the DataWorks console. In the target region, click Data Development and O&M > Data Development in the left-side navigation pane. Select a workspace from the drop-down list and click Go to Data Development.
In the left-side navigation pane, click the icon.
In the Ad Hoc Query pane, right-click Ad Hoc Query and choose Create Node > ODPS SQL.
In the Create Node dialog box, enter a Name for the node.

Note
The node name can be up to 128 characters long.
Click Confirm.

Run SQL statements

You can run SQL statements supported by MaxCompute in the new ad hoc query node. For more information, see SQL overview.

Note

When you run a MaxCompute task, a cost estimation is displayed. This fee is charged by MaxCompute. The estimation is for reference only, and the actual cost appears on your bill. For more billing details, see MaxCompute billable items and billing methods.
If an error occurs during cost estimation, it might be because the table does not exist or you lack the necessary permissions. You can ignore this error for now and run the node. After the node runs, you can resolve the issue using the error message in the log.

For example, to run a DDL statement for a table operation, enter the CREATE TABLE statement and click the icon.

create table if not exists sale_detail
(
shop_name     string,
customer_id   string,
total_price   double
)
partitioned by (sale_date string,region string);
-- Create a partitioned table named sale_detail.

Click Run to view the cost estimation. In the Param. dialog box, select a Resource Group and click Run.

You can view the execution status and final result in the log pane at the bottom. If the statement runs successfully, the result is OK.

You can use the same method to run statements that follow the SELECT syntax.