ODPS视图使用方案研究

视图（View）是在表之上建立的虚拟表，它的结构和内容都来自表。一个视图可以对应一个表或多个表。如果您想保留查询结果，但不想创建表占用存储，可以通过视图实现。

基础视图

语法

create [or replace] view [if not exists] view_name
[(col_name [comment col_comment], ...)]
[comment view_comment]
[as select_statement]

说明:

• 创建视图时，必须有对视图所引用表的读权限。目前提供的视图不是物化视图，执行时会访问视图引用表的数据。需要注意，引用表的权限的变更会影响视图的访问。
• 视图只能包含一个有效的select语句。
• 视图可以引用其他视图，但不能引用自己，也不能循环引用。
• 不可以向视图写入数据。例如：使用insert into或者insert overwrite操作视图。
• 当视图建好以后，如果视图的引用表发生了变更，有可能导致视图无法访问。例如：删除被引用表。用户需要自己维护引用表及视图之间的对应关系。
• 如果没有指定if not exists，在视图已经存在时用create view会导致异常。这种情况可以用 create or replace view来重建视图，重建后视图本身的权限保持不变。

样例

-- 1. 创建视图
create view if not exists sale_detail_view (
      store_name  comment '门店名称'
    , customer_id comment '客户ID'
    , price comment '价格'
    , sale_date comment '销售日期'
    , region comment '所属区域'
) comment 'a view for table sale_detail'
as 
select store_name 
    , customer_id
    , price
    , sale_date
    , region
from sale_detail
;

-- 2. 重命名视图
alter view view_name rename to new_view_name;

-- 3. 删除视图
drop view if exists sale_detail_view;

-- 4. 修改视图的所有人
alter view <view_name> changeowner to <new_owner>;

-- 5. 查看视图
desc sale_detail_view;

参数化视图

ODPS(MaxCompute) 里面也有一个阉割版的存储过程—参数化视图。我们知道视图是将固定的计算逻辑存储下来，通过调用视图简化代码。而参数化视图就是可以在视图代码里调用入参变量，从而达到更灵活的使用视图，一个通用逻辑供多种场景使用。

语法

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

样例

-- 1. 创建视图案例-固定返回格式
create or replace view test_view1 (
    -- 入参 (可以是任意类型任意个数)
    @input_a table (
          ida string comment '主键'
        , name  string comment '姓名'
        , * ANY -- 必须大写
    ),
    @input_b table (
          idb string comment '主键'
        , class string comment '班级'
        , * ANY  -- 必须大写
    ),
    @www string comment '其他参数'
)
-- 定义固定出参类型
returns @res table (
     web     string comment '网站'
    ,id      string comment '主键'
    ,name    string comment '姓名'
    ,class   string comment '班级'
)
comment '测试视图_固定返回'
as 
select @www as web
    , a.ida
    , a.name
    , b.class
from @input_a a -- 例子里的表都是入参，也可以是指定的表名
join @input_b b on a.ida = b.idb
;

-- 2. 调用视图案例
-- 准备数据
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 test_view1(tmp_a,tmp_b,'TEST1');

-- 3. 创建视图案例-不定返回格式
create or replace view test_view2 (
    -- 入参 (可以是任意类型任意个数)
    @input table (
        * ANY
    ),
    @www string comment '其他参数'
)
as 
select @www as web
    , *
from @input
;

-- 4. 调用视图案例
select * from test_view2((select '1' as ida,'Rick' as name,'30' as age),'TEST2');

根据排序字段进行排序

-- 创建视图
create or replace  view orderbyfirstcol(
    @a table(
        columnfororder bigint,
        * ANY
    )
) 
as 
select `(columnfororder)?+.+` 
from (
    select * from @a order by columnfororder desc
) t
;

-- 调用示例
with base as (
    select 1 id, 'test1' name,'I' opt,131012310 dts_time union all
    select 1 id, 'odps' name,'U' opt ,131012316 dts_time union all
    select 2 id, 'test2' name,'I' opt,131012318 dts_time union all
    select 3 id, 'test3' name,'I' opt,131012357 dts_time
),
src as (
    select dts_time as dd, * from base
)
select * from orderbyfirstcol(src);

根据主键合并数据

经常有用户误用参数化视图，将参数化视图的参数当做是宏替换参数来使用。这里说明一下。参数化视图实际上是函数调用，而不是宏替换。

列表达式使用正则表达式可参考SELECT语法介绍>列表达式关于正则表达式的说明

-- 创建合并视图
-- params: @tb1 需要合并的表1
-- params: @tb2 需要合并的表2
-- params: tb__pk1 表的主键，如果是联合主键，建议MD5(CONCAT(PK1,[PK2,[...]]))
-- params: tb__sort_col 排序字段，这里用时间戳的数字类型，根据实际的情况，进行对应调整
create or replace view tools__odps_table_union_all (
    @tb1 table (
        tb__pk1 string,
        tb__sort_col string,
        * ANY 
    ),
    @tb2 table (
        tb__pk1 string,
        tb__sort_col string,
        * ANY
    )
) as
select `(tb__pk1|tb__sort_col|rn)?+.+` 
from (
    select *
        , row_number() over(distribute by tb__pk1 sort by tb__sort_col desc) as rn
    from (
        select * from @tb1
        union all
        select * from @tb2
    )
)
where rn=1
;

-- 合并全表逻辑
with base as (
    select md5(concat(__PK_ID__)) as tb__pk1
        , '00' as tb__sort_col
        , a.*
        , 0 as new_dts_sync_record_id
        , 'I' as new_dts_sync_operation_flag
        , 0 as new_dts_sync_utc_timestamp
        , 'N' as new_dts_sync_before_flag
        , 'Y' as new_dts_sync_after_flag
    from __TABLE_NAME___base a
),
log as (
    select md5(concat(__PK_ID__)) as tb__pk1
        , concat(new_dts_sync_record_id,case when new_dts_sync_after_flag='Y' then '1' else '0' end) as tb__sort_col
        , `(new_dts_sync_.*)?+.+`
        , new_dts_sync_record_id
        , new_dts_sync_operation_flag
        , new_dts_sync_utc_timestamp
        , new_dts_sync_before_flag
        , new_dts_sync_after_flag
    from __TABLE_NAME___log
)
select *
from tools__odps_table_union_all(base,log)
-- where new_dts_sync_after_flag='Y'
;

-- 调用视图案例
with src1 as (
    select 1 id,1 pid, 'test1' name,'I' opt,131012310 dts_time union all
    select 1 id,1 pid, 'odps' name,'U' opt ,131012316 dts_time union all
    select 2 id,1 pid, 'test2' name,'I' opt,131012318 dts_time union all
    select 3 id,1 pid, 'test3' name,'I' opt,131012357 dts_time
),
src2 as (
    select 2 id,1 pid, 'test1' name,'D' opt,131012411 dts_time union all
    select 3 id,1 pid, 'odps' name,'U' opt ,131015123 dts_time union all
    select 4 id,1 pid, 'test4' name,'I' opt,131017318 dts_time
),
base as (
    select md5(concat(id,pid)) as tb__pk1
        , cast(dts_time as string) as tb__sort_col
        , * 
    from src1
),
log as (
    select md5(concat(id,pid)) as tb__pk1
        , cast(dts_time as string) as tb__sort_col
        , * 
    from src2
)
select *
from tools__odps_table_union_all(base,log)
;