第九弹 - 脚本模式与参数视图

MaxCompute（原ODPS）是阿里云自主研发的具有业界领先水平的分布式大数据处理平台, 尤其在集团内部得到广泛应用，支撑了多个BU的核心业务。 MaxCompute除了持续优化性能外，也致力于提升SQL语言的用户体验和表达能力，提高广大ODPS开发者的生产力。

MaxCompute基于ODPS2.0新一代的SQL引擎，显著提升了SQL语言编译过程的易用性与语言的表达能力。我们在此推出MaxCompute(ODPS2.0)重装上阵系列文章

第一弹 - 善用MaxCompute编译器的错误和警告
第二弹 - 新的基本数据类型与内建函数
第三弹 - 复杂类型
第四弹 - CTE，VALUES，SEMIJOIN
第五弹 - SELECT TRANSFORM
第六弹 - User Defined Type
第七弹 - Grouping Set, Cube and Rollup
第八弹 - 动态类型函数

第八弹中提到了参数化视图，本篇就向您介绍MaxCompute 脚本模式和参数化视图。

• 场景1
  项目有个业务处理逻辑很复杂，需要读多个表，某些表之间需要join，得出结果后再join，且要从不同的运算阶段输出多个表，即使用CTE也无法表达，只好将此业务逻辑拆分为多个作业，按顺序提交，如此一来使得作业又复杂性能也不好。
• 场景2
  当前Project有个数据表需要分享给多个团队使用，考虑安全性做成了视图， 视图是通过一个算法模式实现。其他团队发现此算法挺好也想用，但是底层访问的数据表不一样，一些模式识别的参数也不一样，没办法只好再做一个新的视图提供。若后续发现原来的视图有bug，那么只能一个个的修复。

MaxCompute 提供的脚本模式与参数化视图，基本解决了上述场景中的问题。

脚本模式

此文中采用MaxCompute Studio作展示，首先，安装MaxCompute Studio，导入测试MaxCompute项目，创建工程，建立一个新的MaxCompute脚本文件,　如下

注意，要选取MaxCompute Studio的脚本模式。这样编译器就会将整个文件作为一个整体编译，而不是一个一个语句的编译，提交的时候，也是整体提交。

观察此脚本，可以看到其从多个表src, src1, src2读取数据，中间可以做任意的filter, union, join等操作，最后输出到多个表，dest和dest1中。多个语句以@开始的表变量连接。编译后提交脚本运行，可以通过MaxCompute Studio的作业试图察看执行计划，如下：

右键点击图中任意task，弹出菜单点击“expand all”，可以在一个视图中看到执行计划所有operator及其相互关系，如下：

可以看到虽然有多个语句，但是执行计划是互相连通的一个DAG。这样的执行计划，用传统的SQL语句，不管是CTE还是multi INSERT，都无法生成。一个执行计划可以保证一次排队，一次执行，充分利用了MaxCompute的资源。且书写起来也更自然，只需要按照业务逻辑，用类似于普通编程语言的方式书写，不用分心考虑如何组织语句，比如使用CTE和MULTI INSERT等，来优化性能。

同时，MaxCompute的优化器，比起多个语句分别执行，在脚本模式下能够看到更完整的执行计划，也就可以有更多的优化机会，生成全局最优的物理执行计划，提高性能。

脚本模式也支持SET语句和部分DDL语句。例如：

一个脚本的完整形式是先SET语句，然后DDL语句，最后DML语句。每个部分都可以有0到多个语句，但是不同类型的语句不能交错。

需要注意的是，在脚本模式下，如果一个表先被写，又被读，会报告错误，避免混淆。例如以下语句会报错

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;

这样就可以打印出会写入src2中的内容了。

脚本模式下目前还有一些限制

• 一个脚本，目前最多支持一个屏显语句，否则会报告错误。不建议在脚本中执行屏幕显示的SELECT语句。
• 一个脚本，目前最多支持一个CREATE TABLE AS语句并且必须是最后一句。我们推荐您将建表语句与INSERT语句分开写，如上面提到的DDL和DML混合的脚本。

通过MaxCompute CMD使用脚本模式

脚本模式是一种新的模式，如果使用MaxCompute CMD来提交脚本，需要使用0.27以上的版本。建议您下载安装最新版本。安装后，请使用新的 -s 参数来提交 。

如编辑脚本模式的源码myscript.sql文件，调用odpscmd命令执行如下。

odpscmd -s myscript.mysql;

说明 -s 为odpscmd的命令行选项，类似于-f、-e，而非交互环境中的命令。odpscmd的交互环境中暂不支持脚本模式与表变量。

如果希望传递一些setting，但是又不想写在脚本里面，可以按照如下的方，使用-e参数来单独传递setting

odpscmd -e 'set odps.sql.setting1=a;odps.sql.setting2=b='-s myscript.sql

通过DataWorks使用脚本模式

在DataWorks中可以建立脚本模式的节点ODPS Script，如下图所示。

在此节点中进行脚本模式编辑，编辑完成后单击工具栏的运行按钮，提交脚本到MaxCompute执行。从输出信息的Logview URL中可以查看执行计划图和结果。

什么时候不应将多个小SQL语句组合为一个大模式脚本SQL

脚本模式能够支持复杂的逻辑，一个脚本可以有很多行，编译为一个执行计划，这样很容易将原来在多个SQL节点中的语句通过table variable衔接起来，拼装为一个大的脚本模式SQL。但是，并不是所有场景都适合。

例如，如果上游数据来源于两个每日更新的表，一个凌晨1点数据准备好，一个凌晨7点准备好，原来一个SQL节点1点的时候处理第一个数据，结果存为一个中间表。另一个SQL节点在7点的时候将此中间表和第二个数据源作进一步运算得到结果数据。这种场景就不适合将两个节点中的SQL组合为一个大脚本。因为一个SQL节点本来可以更早的做好数据预处理，如果合成一个大脚本，就只能在7点所有数据都到达后生成一个作业，本来可以预处理的也要在大作业中完成，反而造成全链路E2E时间更长，出错的概率也更高。

脚本模式更适合用来改写本来要用层层嵌套子查询的单个语句，或者因为脚本复杂性而不得不拆成多个语句的脚本。

参数化的视图 (VIEW)

MaxCompute传统的VIEW，可以封装一段SQL脚本，底层的逻辑可以很复杂，比如访问多个表，做复杂的运算，但是调用者可以像读普通表一样调用VIEW，不关心底层实现。这样的VIEW实现了一定的封装与重用，使用非常广泛。

但是传统的VIEW也有一个限制，就是不能接受任何调用者传递的参数。例如某个VIEW读取一个底层表，希望调用者可以提供一个表对底层数据进行过滤，或者传递任何其他参数，这些需求传统VIEW都不支持。如此便限制了传统VIEW在代码重用方面的能力，而开发中仍经常需要多次复制类似的代码。

MaxCompute支持带参数的VIEW，可以传入任意表或者其他变量，定制VIEW的行为。

首先在MaxCompute建立一个新的MaxCompute脚本文件,　如下:

可以看到，pv有两个参数，一个是表参数，一个是string参数，参数可以是任意的表或者基本类型。创建另一个脚本调用此VIEW，如下：

可以看到pv1可以用不同的参数调用，表参数可以是个物理表，view，表变量或者CTE中的表别名，普通参数可以是变量或者常量。

点击此脚本的graph页 (上图中最下边的红框)，可以看到此脚本对应的执行计划：

可以看到，不管调用多少次，用什么样的参数，MaxCompute编译器都会将整个脚本编译为一个执行计划。

实际上，参数化VIEW不一定是一个SQL语句，也可以像脚本一样，包含多个语句。例如：

其中BEGIN到END;之间的语句，就是这个VIEW的脚本。注意其中最后一句，@pv2 := ...，这个语句相当于是其他语言中的RETURN语句，方式为向一个与VIEW同名的隐含的表变量赋值。

在VIEW参数匹配的时候，实参和形参匹配的规则和普通语言一样。如果可以隐式转换，就可以匹配，例如bigint的值可以匹配类型为double的参数。表变量相对复杂一些，但是规则也很简单，如果一个表的Schema，能够用来插入到另一个表中，那也可以用来匹配同样表Schema的表类型参数。

对于返回的类型，有的时候希望明确声明，例如为了可读性。可以明确地声明返回类型，例如：

其中的RETURNS @ret TABLE (x string, y string), 定义了：

• 返回类型为TABLE (x string, y string)。也就是返回给调用者的类型，注意可以在此定制表的Schema
• 返回参数为@ret，在VIEW的脚本中对其赋值，就相当于返回。

实际上，对于没有BEGIN/END和没有返回变量的VIEW，都可以认为是这种形式的简化形式。

在参数化VIEW的时候，对于脚本有一些其他限制。

• 脚本中只能由DML语句，并且不能有INSERT与CREATE TABLE AS语句。
• 脚本中不能有屏幕显示语句。

最后，MaxCompute提供了新的脚本模式与参数化视图，可以明显提高开发者的编程效率，提高代码的可重用性，与此同时，也提高了性能！我们推荐您尽量使用脚本模式和参数化视图。

原文链接
本文为云栖社区原创内容，未经允许不得转载。