文章详情页_MaxCompute 参数化视图

参数化视图支持传入任意表或其它变量，定制视图的行为。

功能介绍

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：条件必选。视图脚本

定义参数化视图

创建带参数的视图，语法如下：

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));

定义视图时，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 * 这种通配符将变长部分的列传递出去
表值参数的列与定义视图时指定的定长列部分不一定会完全一致。如果名字不一致，编译器会自动做重命名；如果类型不一致，编译器会做隐式转换（不能隐式转换时，会发生报错）

注：使用 create view 创建视图后，可以执行 desc 命令获取视图的描述。此描述中包含视图的返回类型信息。视图的返回类型是在调用时重新推算的，它可能与创建视图时不一致，例如 ANY 类型。

不声明返回类型

当参数化视图仅包含单个 SQL 语句时，不需要 begin ... end; 代码块。

使用 ODPS Script 创建参数化视图

create or replace view para_view_noreturn (
    -- 入参
    @input_a table (
        ida     string  comment '主键'
        ,name   string  comment '姓名'
        ,*      any                     -- * 表示任意多个列、any 表示任意数据类型
    )
    ,@input_b table (
        idb     string  comment '主键'
        ,class  string  comment '班级'
        ,*      any
    )
    ,@input_c   string
)
comment '测试视图_不声明返回类型'
as 
select 
    @input_c as web, a.*, b.*
from @input_a a inner join @input_b b
    on a.ida = b.idb;

查看视图描述

desc para_view_noreturn;

+------------------------------------------------------------------------------------+
| TableComment: 测试视图_不声明返回类型                                                |
+------------------------------------------------------------------------------------+
| CreateTime:               2024-07-08 10:34:13                                      |
| LastDDLTime:              2024-07-08 10:50:15                                      |
| LastModifiedTime:         2024-07-08 10:34:13                                      |
+------------------------------------------------------------------------------------+
| VirtualView  : YES  | ViewText: create or replace view para_view_noreturn (
    -- 入参
    @input_a table (
        ida     string  comment '主键'
        ,name   string  comment '姓名'
        ,*      any                     -- * 表示任意多个列、any 表示任意数据类型
    )
    ,@input_b table (
        idb     string  comment '主键'
        ,class  string  comment '班级'
        ,*      any
    )
    ,@input_c   string
)
comment '测试视图_不声明返回类型'
as 
select 
    @input_c as web, a.*, b.*
from @input_a a inner join @input_b b
    on a.ida = b.idb |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| web             | string     |       |                                             |
| ida             | string     |       |                                             |
| name            | string     |       |                                             |
| idb             | string     |       |                                             |
| class           | string     |       |                                             |
+------------------------------------------------------------------------------------+
OK

调用参数化视图

-- 准备数据
with 
tmp_a as (
    select 1 as ida, 'Rick' as name, '30' as age
)
,tmp_b as (
    select 1 as idb, 'A' as class, 'SH' as city
)
-- 调用视图
select * from para_view_noreturn(tmp_a, tmp_b, 'CSDN');

+--------+--------+--------+--------+--------+--------+--------+
| web    | ida    | name   | age    | idb    | class  | city   |
+--------+--------+--------+--------+--------+--------+--------+
| CSDN   | 1      | Rick   | 30     | 1      | A      | SH     |
+--------+--------+--------+--------+--------+--------+--------+

明确声明返回类型

当参数化视图仅包含单个 SQL 语句时，不需要 begin ... end; 代码块。

使用 ODPS Script 创建参数化视图

create or replace view para_view_return (
    -- 入参 
    @input_a table (
        ida     string  comment '主键'
        ,name   string  comment '姓名'
        ,*      any
    )
    ,@input_b table (
        idb     string  comment '主键'
        ,class  string  comment '班级'
        ,*      any
    )
    ,@input_c   any     comment '其他参数'
)
returns @res table (
    id      string comment '主键'
    ,name   string comment '姓名'
    ,class  string comment '班级'
)
comment '测试视图_声明返回类型'
as 
select 
    a.ida       as id
    ,a.name     as name
    ,b.class    as class
from @input_a a inner join @input_b b
    on a.ida = b.idb;

查看视图描述

+------------------------------------------------------------------------------------+
| TableComment: 测试视图_声明返回类型                                                  |
+------------------------------------------------------------------------------------+
| CreateTime:               2024-07-08 14:35:06                                      |
| LastDDLTime:              2024-07-08 14:35:06                                      |
| LastModifiedTime:         2024-07-08 14:35:06                                      |
+------------------------------------------------------------------------------------+
| VirtualView  : YES  | ViewText: create or replace view para_view_return (
-- 入参 
    @input_a table (
        ida     string  comment '主键'
        ,name   string  comment '姓名'
        ,*      any
    )
    ,@input_b table (
        idb     string  comment '主键'
        ,class  string  comment '班级'
        ,*      any
    )
    ,@input_c   any     comment '其他参数'
)
returns @res table (
    id      string comment '主键'
    ,name   string comment '姓名'
    ,class  string comment '班级'
)
comment '测试视图_声明返回类型'
as 
select 
    a.ida       as id
    ,a.name     as name
    ,b.class    as class
from @input_a a inner join @input_b b
    on a.ida = b.idb |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| id              | string     |       | 主键                                        |
| name            | string     |       | 姓名                                        |
| class           | string     |       | 班级                                        |
+------------------------------------------------------------------------------------+
OK

调用参数化视图

-- 准备数据
with tmp_a as (
    select 1 as ida, 'Rick' as name, '30' as age
)
,tmp_b as (
    select 1 as idb, 'A' as class, 'SH' as city
)
-- 调用视图
select * from para_view_return(tmp_a, tmp_b, 'CSDN');

+--------+--------+--------+
| ida    | name   | class  |
+--------+--------+--------+
| 1      | Rick   | A      |
+--------+--------+--------+

参数化视图说明

参数化视图中，脚本中只能使用 DML 语句，不能使用 insert 或 create table 语句，也不能使用屏幕显示语句
参数化视图不一定只有一个 SQL 语句，也可以像脚本一样，包含多个语句

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;

在视图参数匹配时，实参和形参匹配的规则和普通的弱类型语言一样，如果传入的视图参数可以被隐式转换，则可与所定义的参数匹配。例如，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;

参考资料

参数化视图

ODPS 进阶之参数化视图

原创文章，转载请注明出处：http://www.opcoder.cn/article/79/