Develop and submit an SQL script - MaxCompute - Alibaba Cloud Documentation Center

This topic describes how to develop and submit an SQL script in MaxCompute Studio. The SQL script development includes writing and running an SQL script.

Prerequisites

A MaxCompute project is connected. For more information, see Manage project connections.
A MaxCompute script module is created. For more information, see Create a MaxCompute script module.

Write an SQL script

In the Project tool window, click a project name, right-click scripts, and then choose New > MaxCompute SQL Script.
In the New MaxCompute SQL Script dialog box, configure the required parameters and click OK.
- Script Name: the name of the script.
- MaxCompute Project: the MaxCompute project in which you want to write an SQL script. You can click + to establish a connection to another MaxCompute project. For more information, see Manage project connections.
Write an SQL script in the editor. For information about the SQL syntax, see Overview of MaxCompute SQL.
Sample script:
```
create table table_5(
col1 bigint,
col2 bigint,
col3 bigint,
ds datetime
);
insert into table table_5 values(1,2,3,DATETIME'2017-11-11 00:00:00');
select * from table_5 where ds='${bizdate}';
```
Note
- You can configure cross-project resource sharing. For example, you can use a script that is bound to Project A to access table1 in Project B.
- MaxCompute Studio allows you to configure the SQL script editor. For more information, see Overview.

Submit an SQL script

Before you submit an SQL script, you must configure compilation parameters based on your business requirements. MaxCompute Studio provides various compilation parameters. You can configure the parameters in the toolbar above the editor. You can configure the following compilation parameters:

Editor mode
- Statement Mode: In this mode, the editor separates the SQL statements in the script with semicolons (;) and submits the statements one by one to the MaxCompute server for execution.
- Script Mode: This is the most recent development mode. In this mode, the editor submits the whole script to the MaxCompute server at a time for overall optimization. We recommend that you use this mode to improve the execution efficiency.
Type System: You can configure this parameter to prevent compatibility issues when you execute SQL statements. Valid values:
- Legacy TypeSystem: indicates the MaxCompute V1.0 data type edition.
- MaxCompute TypeSystem: indicates the MaxCompute V2.0 data type edition.
- Hive Compatible TypeSystem: indicates the Hive-compatible data type edition.
Execution mode
- Default Version: indicates that a stable version is used.
- MaxCompute Query Acceleration: indicates that the MaxCompute query acceleration (MCQA) feature is enabled to run a job.
- Rerun When Acceleration Fails: indicates that the system reruns a job if query acceleration fails.

In the toolbar or side bar, click the icon to submit an SQL script to the MaxCompute server for execution.
Note
If a variable, such as ${bizdate} in the preceding code, exists in the SQL script, a dialog box appears, which prompts you to enter the value of the variable. In this example, you can enter 2017-11-11 00:00:00 to query the data in the current partition.
Before MaxCompute runs the SQL script, IntelliJ IDEA displays the estimated cost of the SQL script. Confirm the estimated cost and click OK in the Confirmation message.
Note
- In the toolbar, you can click the icon to update the metadata that is used in the SQL script, such as tables and user-defined functions (UDFs). If MaxCompute Studio cannot detect the tables and functions in MaxCompute, you can use this feature.
- SQL scripts are compiled based on the metadata that you add in the Project Explorer window. If no errors are detected, the SQL scripts are submitted to the MaxCompute server. Then, the MaxCompute server executes the SQL scripts.
- Run logs are generated when SQL scripts are executed. When SQL scripts are executed in MaxCompute, the job details tab appears. You can view the execution details.
- If you want to display the cost estimation of SQL jobs, you can select Show sql cost confirm dialog when script submitted from the MaxCompute SQL configuration items.
On the Result tab, view the execution results.
If multiple statements are executed one by one, the execution result of each statement is displayed.