本文为您介绍MaxCompute当前的SQL引擎支持的参数化视图功能。

MaxCompute传统的视图(VIEW)中,底层可以封装一段逻辑复杂的SQL脚本,调用者可以像读普通表一样调用视图,无需关心底层的实现。传统的视图实现了一定的封装与重用,因此被广泛地使用。但是传统的视图并不接受调用者传递的任何参数(例如调用者无法对视图读取的底层表进行数据过滤或传递其它参数),导致代码重用能力低下。MaxCompute当前的SQL引擎支持带参数的视图,支持传入任意表或者其它变量,定制视图的行为。

参数化视图定义

创建带参数的视图,语法如下。
-- view with parameters
-- param @a -a table parameter
-- param @b -a string parmeter
-- returns a table with schema (key string,value string)

create view if not exists pv1(@a table (k string,v bigint), @b string) 
as 
select srcp.key,srcp.value from srcp join @a on srcp.key=a.k and srcp.p=@b;
语法说明:
  • 因为定义了参数,所以需要通过脚本模式操作。
  • 创建的视图pv1有两个参数,即表参数和STRING参数,参数可以是任意的表或者基本类型。
  • 支持使用子查询作为参数的值,例如select * from view_name( (select 1 fromsrc where a > 0), 1);
  • 定义视图时,参数可以使用ANY关键字,表示任意类型。例如CREATE VIEW paramed_view (@a ANY) AS SELECT * FROM src WHERE case when @a is null then key1 else key2 end = key3;,定义了视图的第一个参数可以接受任意类型。但是ANY类型不能参与如+AND之类的需要明确类型才能做的运算。ANY类型通常用在TABLE参数中做PassThrough列,示例如下。
    CREATE VIEW paramed_view (@a TABLE(name STRING, id ANY, age BIGINT)) AS SELECT * FROM @a WHRER name = 'foo' and age < 25;
    -- 调用示例。
    SELECT * FROM param_view((SELECT name, id, age from students));
    说明 CREATE VIEW创建了视图后,可以用DESC来获取视图的描述。这个描述会包含视图的返回类型信息。视图的返回类型是在调用时重新推算的,它可能与创建视图时推导出来的不一致,例如ANY类型。
  • 定义视图时,Table值参数还支持使用*表示任意多个列。这个*可以带类型,也可以使用ANY类型,示例如下。
    CREATE VIEW paramed_view (@a TABLE(key STRING, * ANY), @b TABLE(key STRING, * STRING)) AS SELECT a.* FROM @a JOIN @b ON a.key = b.key; 
    -- 调用示例。
    SELECT name, address FROM param_view((SELECT school, name, age, address FROM student), school) WHERE age < 20;
    示例中的视图接受两个表值参数。第一个表值参数第一列是STRING类型,后面可以是任意多个任意类型的列;第二个表值参数的第一列是STRING类型,后面可以是任意多个STRING类型的列。注意事项如下:
    • 变长部分必须写在表值参数定义语句的最后位置,即在*的后面不允许再出现其它列。因此,一个表值参数中最多只有一个变长列列表。
    • 由于变长部分必须写在表值参数定义语句的最后位置,有时输入表的列不一定是按照这种顺序排列的,这时需要重排输入表的列,可以以子查询作为参数(参考上述示例),子查询外面必须加一层括号。
    • 因为表值参数中变长部分没有名字,所以在视图定义过程中无法获得对这部分数据的引用,也无法对这些数据做运算。
    • 虽然无法对变长部分做运算,但可以使用SELECT *这种通配符将变长部分的列传递出去。
    • 表值参数的列与视图声明时指定的定长列部分不一定会完全一致。如果名字不一致,编译器会自动做重命名;如果类型不一致,编译器会做隐式转换(不能隐式转换时,会发生报错)。

调用参数化视图

调用上述定义中的pv1视图的示例如下。
@a := select * from src where value >0;
--call view with table varable an scalar
@b := select * from pv1(@a,'20170101');
@another_day := '20170102';
--call view with table name and scalar variable
@c := select * from pv1(src2, @another_day);
@d := select * from @c union all select * from @b;
with 
t as(select * from src3)
select * from @c 
union all 
select * from @d 
union all 
select * from pv1(t,@another_day);
说明 您可以使用不同的参数调用pv1。表参数可以是物理表、VIEW、表变量或者CTE中的表别名。普通参数可以是变量或者常量。

参数化视图的其它说明

  • 参数化视图时,脚本中只能使用DML语句,不能使用insertcreate table语句,也不能使用屏幕显示语句。
  • 参数化视图不一定只有一个SQL语句,也可以像脚本一样,包含多个语句。
    -- view with parameters
    -- param @a -a table parameter
    -- param @b -a string parmeter
    -- returns a table with schema (key string,value string)
    create view if not exists pv2(@a table (k string,v bigint), @b string) as
    BEGIN
    @srcp := select * from srcp where p=@b;
    @pv2 := select srcp.key,srcp.value from @srcp join @a on srcp.key=a.k;
    end;
    说明 beginend之间的语句,就是这个视图的脚本。@pv2 :=...语句相当于其它语言中的return语句,方式为向一个与视图同名的隐含的表变量赋值。
  • 在视图参数匹配时,实参和形参匹配的规则和普通的弱类型语言一样,如果传入的视图参数可以被隐式转换,则可与所定义的参数匹配,例如BIGINT的值可以匹配DOUBLE类型的参数。对于表变量,如果表a的Schema可以被用于插入到表b中,则意味着表a可以用来匹配和表b的Schema相同的表类型参数。
  • 您可以明确地声明返回类型,以提升代码的可读性。
    create view if not exists pv3(@a table (k string,v bigint), @b string)
    returns @ret table (x string,y string)
    AS
    begin
        @srcp := select * from srcp where p=@b;
        @ret := select srcp.key,srcp.value from @srcp join @a on srcp.key=a.k;
    end;
    说明 returns @ret table (x string,y string)定义了:
    • 返回类型为table (x string, y string),即返回给调用者的类型,可以在此处定制表的Schema。
    • 返回参数为@ret,为它赋值的操作会在视图的脚本中进行,此处相当于定义了返回的参数名。

    实际上,您可以把没有begin/end和返回变量的视图,看作是此形式的简化形式。