参数化视图支持传入任意表或其它变量,定制视图的行为。本文为您介绍MaxCompute SQL引擎支持的参数化视图功能。

功能介绍

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

命令格式

create [or replace] [if not exists] <view_name>( <variable_name> <variable_type> [, <variable_name> <variable_type> ...])
[returns <return_variable> table (<col_name> <col_type> comment <col_comment> [,<col_name> <col_type> comment <col_comment>])]
[comment <view_comment>]
as
{<select_statement> | begin <statements> end}
  • view_name:必填。视图名称。
  • variable_name:必填。视图变量名称。
  • variable_type:必填。视图变量参数类型。
  • return_variable:可选。视图返回的变量名称。
  • col_name:可选。视图返回列的名称。
  • col_type:可选。视图返回列的类型。
  • col_comment:可选。视图返回列的注释。
  • view_comment:可选。视图的注释。
  • select_statement:条件必选。select子句。
  • statements:条件必选。视图脚本。

定义参数化视图

创建带参数的视图,语法如下。
-- view with parameters
-- param @a -a table parameter
-- param @b -a string parameter
-- 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 from src 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 where 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 variable and 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 parameter
    -- 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 comment 'This is the x',y string comment 'This is the y')
    comment 'This is view pv3'
    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和返回变量的视图,看作是此形式的简化形式。