This topic describes how to develop and submit an SQL script in MaxCompute Studio.

Prerequisites

Write an SQL script

  1. In the Project tool window, click a project name, right-click scripts, and then choose New > MaxCompute SQL Script.
    Create
  2. In the New MaxCompute SQL Script dialog box, configure the parameters and click OK.
    Script Name
    • 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.
  3. Write an SQL script in the editor. For information about the SQL syntax, see Overview of MaxCompute SQL.
    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. MaxCompute executes the statements one by one.
    • 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.
  1. In the toolbar or sidebar, click the Run icon to submit an SQL script to MaxCompute.
    Sample SQL script: 
    create table table_5(
col1 bigint,
col2 bigint,
col3 bigint
);
insert into table table_5 values (1,2,3);
select * from ods_user_info_d where ds=`${bizdate}`;
    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.
  2. 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.
    Confirmation
    Note
    • In the toolbar, you can click the Refresh 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 MaxCompute. Then, MaxCompute executes the SQL scripts.
    • Operational 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.
  3. On the Result tab, view the execution results.

    If multiple statements are executed one by one, the execution result of each statement is displayed.

    Result