The parameterized view feature allows you to import tables or variables to customize views. This topic describes the parameterized view feature that is supported by the MaxCompute SQL engine.

Background information

In the traditional views of MaxCompute, complex SQL scripts are encapsulated at the underlying layer. You can call views like reading a standard table without the need to understand the implementation mechanism at the underlying layer. Traditional views are widely used because they implement code encapsulation and reuse. However, traditional views do not support parameters. For example, you cannot pass filter conditions to filter data in the underlying tables of a view or pass other parameters to the view. This makes code reuse efficiency low. The MaxCompute SQL engine supports parameterized views and allows you to import tables or variables to customize views.

Syntax

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: required. This parameter specifies the name of the view.
  • variable_name: required. This parameter specifies the name of the view variable.
  • variable_type: required. This parameter specifies the type of the view variable.
  • return_variable: optional. This parameter specifies the name of the variable returned by the view.
  • col_name: optional. This parameter specifies the name of the column returned by the view.
  • col_type: optional. This parameter specifies the type of the column returned by the view.
  • col_comment: optional. This parameter specifies the comment of the column returned by the view.
  • view_comment: optional. This parameter specifies the comment of the view.
  • select_statement: the SELECT clause. You must specify this clause or the statements clause.
  • statements: the script of the view. You must specify this clause or the select_statement clause.

Define a parameterized view

To create a parameterized view, use the following syntax:
-- 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;
Description:
  • Parameters are defined. Therefore, you must use Script Mode SQL to define a parameterized view.
  • The created pv1 view has two parameters, table and string. You can set the table parameter to a table name and the string parameter to a basic data type.
  • You can also use a subquery, such as select * from view_name( (select 1 from src where a > 0), 1);, as a parameter value.
  • When you create a view, you can set a parameter to any, which indicates any data type. For example, use create view paramed_view (@a any) as select * from src where case when @a is null then key1 else key2 end = key3; to define a view. In this example, the first parameter of the view can be a value of any data type.
    However, the value any cannot be used in operations, such as + or AND, that require specific data types. A field of the any type is often used as a PassThrough column in the table specified by the table parameter. Example:
    create view paramed_view (@a table(name string, id any, age bigint)) as select * from @a where name = 'foo' and age < 25;
    -- Call the view.
    select * from param_view((select name, id, age from students));
    Note After you execute the CREATE VIEW statement to create a view, you can execute the DESC statement to obtain the description of the view. The description contains the return type of the view.

    The return type of a view is recalculated when the view is called. The return type may be different from the return type, such as any, that you specify when you create the view.

  • When you create a view, you can use an asterisk (*) in the table parameter to retrieve any columns. If an asterisk (*) is used to indicate any columns, you can specify a specific data type for the columns or use the value any. Example:
    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; 
    -- Call the view.
    select name, address from param_view((select school, name, age, address from student), school) where age < 20;
    In this example, the view uses two table parameters. In the table specified by the first table parameter, the first column is of the STRING type and can be followed by any columns of any data types. In the table specified by the second table parameter, the first column is of the STRING type and can be followed by any columns of the STRING type. Take note of the following points:
    • The varied-length part must be placed at the end of the table that is specified by the table parameter. This means that the columns indicated by an asterisk (*) cannot be followed by other columns. Therefore, the table that is specified by the table parameter can contain only one varied-length list.
    • The varied-length part must be placed at the end of the table that is specified by the table parameter. However, the columns of an input table may not be arranged in this sequence. In this case, the columns need to be rearranged. A subquery can be used as a parameter value and must be enclosed in a pair of parentheses ().
    • No name is specified for the varied-length part of the table that is specified by the table parameter. As a result, data in the varied-length part cannot be referenced or calculated when you define the view.
    • Although you cannot calculate the varied-length part, you can use the SELECT * statement to transfer data in the varied-length part out of the table.
    • The columns of the table that is specified by the table parameter may be different from the fixed-length columns that you specify when you define the view. If the column names are different, the compiler automatically renames them. If the data types are different, the compiler performs an implicit conversion. If the implicit conversion fails, an error occurs.

Call a parameterized view

You can execute the following statement to call the pv1 view that you defined:
@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);
Note You can use different parameters to call the pv1 view.
  • The value of the table parameter can be the name of a physical table, view, or table variable. You can also set the table parameter to a table alias in a common table expression (CTE).
  • The values of common parameters can be variables or constants.

Usage notes

  • In a parameterized view, only DML statements can be used in scripts. The SELECT statements that directly return outputs, INSERT, or CREATE TABLE cannot be used.
  • A parameterized view can contain multiple SQL 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 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;
    Note Content between begin and end is the script of this view. @pv2 :=... is similar to the RETURN statement in other programming languages. @pv2 is used to assign a value to an implicit table variable that has the same name as the view.
  • The matching rules for actual and formal view parameters are the same as those specified in a common weakly-typed language. If a view parameter can be implicitly converted, it can match the defined parameter. For example, a value of the BIGINT type can match parameters of the DOUBLE type. For table variables, if the schema of Table a can be inserted into Table b, Table a can be used to match the table-type parameters that have the same schema as Table b.
  • You can explicitly declare the return type to make the code easier to read.
    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;
    Note returns @ret table (x string,y string) defines the following information:
    • The return type is table (x string,y string), which indicates the type returned to the caller. You can use this parameter to customize the table schema.
    • The response parameter is @ret. In the view script, the parameter is assigned a value. This parameter defines the name of the response parameter.

    You can consider a view that contains no begin/end or return variables as a simplified parameterized view.