什么是脚本模式SQL_云原生大数据计算服务 MaxCompute-阿里云帮助中心

添加链接

注册登录

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

善良的拐杖 · 二十五、oracle ...· 5 天前 ·

沉稳的椅子 · 三门峡市财政局：聚焦“高、新、精、严” ...· 1 年前 ·

闯红灯的泡面 · 亿华通-北京清华工业开发研究院· 1 年前 ·

有腹肌的香烟 · 国产翼龙-2H无人机为通信中断区搭建空中移动 ...· 1 年前 ·

细心的佛珠 · How to See Which Apps ...· 2 年前 ·

强健的生姜 · 一口气看完《星甲魂将传》一路开挂造机甲打怪兽 ...· 2 年前 ·

MaxCompute当前的SQL引擎支持脚本模式SQL（Script Mode SQL）。在脚本模式下编译脚本时，一个多语句的SQL脚本文件将被作为一个整体进行编译，无需对单个语句进行编译。在提交运行时，SQL脚本文件会被整体提交，并生成一个执行计划，保证只需排队一次、执行一次，让您能充分利用MaxCompute的资源。

--set
set odps.sql.type.system.odps2=true;
[set odps.stage.reducer.num=xxx;]
[...]
--ddl
create table table1 xxx;
[create table table2 xxx;]
[...]
--dml
@var1 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM table3
        [WHERE where_condition];
@var2 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM table4
        [WHERE where_condition];
@var3 := SELECT [ALL | DISTINCT] var1.select_expr, var2.select_expr, ...
        FROM @var1 join @var2 on ...;
INSERT OVERWRITE|INTO TABLE [PARTITION (partcol1=val1, partcol2=val2 ...)]
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var3;    
[@var4 := SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var1 
        UNION ALL | UNION 
        SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var2;    
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var4;]
[...]

INSERT OVERWRITE table src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;
SELECT * FROM @a;

@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE table src2 SELECT * FROM @a;
SELECT * FROM @a;

```
IF (condition) BEGIN
  statement 1
  statement 2
IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END
```

@date := '20190101';
@row  TABLE(id STRING); --声明变量row，其类型为Table，schema为string. 
IF ( cast(@date  as bigint) % 2 == 0 ) BEGIN 
@row  := SELECT id from src1; 
END ELSE BEGIN
@row  := SELECT id from src2; 
INSERT OVERWRITE TABLE dest SELECT * FROM @row;

@i bigint;
@t table(id bigint, value bigint);
IF ((SELECT count(*) FROM src WHERE a = '5') > 1)  BEGIN
@i := 1;
@t := select @i, @i*2;
END ELSE
BEGIN
@i := 2;
@t := select @i, @i*2;
select id, value from @t;

- ```
@a := SELECT 10; --对@a赋值常量10，或者赋值存在一个单行表t1中，SELECT col1 FROM t1。
@b := SELECT key,value+(SELECT * FROM @a) FROM t2 WHERE key >10000; --t2表中value值与@a中的值进行计算。
SELECT * FROM @b;
```

CREATE TABLE IF NOT EXISTS dest(key STRING, value BIGINT) PARTITIONED BY (d STRING);
CREATE TABLE IF NOT EXISTS dest2(key STRING, value BIGINT) PARTITIONED  BY (d STRING);
@a := SELECT * FROM src WHERE value >0;
@b := SELECT * FROM src2 WHERE key is not null;
@c := SELECT * FROM src3 WHERE value is not null;
@d := SELECT a.key,b.value FROM @a LEFT OUTER JOIN @b ON a.key=b.key AND b.value>0;
@e := SELECT a.key,c.value FROM @a INNER JOIN @c ON a.key=c.key;
@f := SELECT * FROM @d UNION SELECT * FROM @e UNION SELECT * FROM @a;
INSERT OVERWRITE table dest PARTITION (d='20171111') SELECT * FROM @f;
@g := SELECT e.key,c.value FROM @e JOIN @c ON e.key=c.key;
INSERT OVERWRITE TABLE dest2 PARTITION (d='20171111') SELECT * FROM @g;

```
..\bin>odpscmd -s myscript.sql
```

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.aliyun.odps.Instance;
import com.aliyun.odps.Odps;
import com.aliyun.odps.OdpsException;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.data.Record;
import com.aliyun.odps.task.SQLTask;
public class SdkTest {
  public static void main(String[] args) throws OdpsException {
		// 阿里云账号AccessKey拥有所有API的访问权限，风险很高。强烈建议您创建并使用RAM用户进行API访问或日常运维，请登录RAM控制台创建RAM用户
		// 此处以把AccessKey 和 AccessKeySecret 保存在环境变量为例说明。您也可以根据业务需要，保存到配置文件里
		// 强烈建议不要把 AccessKey 和 AccessKeySecret 保存到代码里，会存在密钥泄漏风险
    Account account = new AliyunAccount(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID"), System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET"));
    Odps odps = new Odps(account);
    odps.setDefaultProject("your project_name");
    odps.setEndpoint("your end_point");
    String sqlScript = "@a := SELECT * FROM jdbc_test;\n"
                       + "SELECT * FROM @a;";
    //一定要加这一行配置
    Map<String, String> hints = new HashMap<>();
    hints.put("odps.sql.submit.mode", "script");
    Instance instance = SQLTask.run(odps, "your project_name", sqlScript, hints, null);
    instance.waitForSuccess();
    List<Record> recordList = SQLTask.getResult(instance);
    for (Record record : recordList) {
      System.out.println(record.get(0));
      System.out.println(record.get(1));

语法结构

语法说明

示例

工具支持

使用脚本模式