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

In traditional views of MaxCompute, complex SQL scripts are encapsulated at the underlying layer. Callers can call views in the same way as reading a common table without understanding the underlying implementation. Traditional views are widely used because they implement encapsulation and reuse. However, traditional views cannot accept parameters from callers. This reduces code reuse efficiency. For example, a caller cannot filter data in the underlying table that is read by a view nor pass other parameters. The MaxCompute SQL engine supports parameterized views and allows you to import tables or variables to customize views.

Define a parameterized view

You can use the following syntax to create a parameterized view:
-- 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;
Syntax description:
  • To create a parameterized view, you must specify parameters. Therefore, you must use Script Mode SQL to create a parameterized view.
  • The created pv1 view has two parameters, namely, the TABLE and STRING parameters. The parameter value can be any table or be of a basic data type.
  • The parameter value can also be a subquery, for example, SELECT * FROM view_name( (SELECT 1 FROM src WHERE a > 0), 1);.
  • When you create a view, you can set ANY for a parameter, which indicates any data type. For example, CREATE VIEW paramed_view (@a ANY) AS SELECT * FROM src WHERE CASE WHEN @a IS NULL THEN key1 ELSE key2 END = key3; defines that the first parameter of the view can be a value of any data type.
    However, the ANY type cannot be used in operation, such as + or AND, that requires specific data types. A field of the ANY type is often used as a PassThrough column in 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 example
    SELECT * FROM param_view((SELECT name, id, age FROM students));
    Note After you execute the CREATE VIEW statement to create a view, you can run the DESC command to obtain the description of the view. This description contains the return type of the view.

    The return type of a view is recalculated when the view is called. It 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. The asterisk (*) can represent a specific data type or the ANY type. 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 example
    SELECT name, address FROM param_view((SELECT school, name, age, address FROM student), school) WHERE age < 20;
    In this example, the view accepts two TABLE parameters. In the table that is specified by the first TABLE parameter, data in the first column is of the STRING type, and data in other columns can be of the ANY type. In the table that is specified by the second TABLE parameter, data in the first and other columns is all of the STRING type. You must take note of the following limits and guidelines:
    • The varied-length part must be placed at the end of the table that is specified by the TABLE parameter, that is, the * column cannot be followed by other columns. Therefore, the table that is specified by the TABLE parameter can contain only one varied-length column.
    • 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 used for operation when you define the view.
    • Although you cannot use the varied-length part for operation, you can use the SELECT * statement to transfer data in the varied-length part out of the table.
    • The column of the table that is specified by the TABLE parameter may be different from the fixed-length column that you specify when you define the view. If the names are different, the compiler automatically renames the column of the table that is specified by the TABLE parameter. 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 common table expressions (CTEs).
  • The values of common parameters can be variables or constants.

Usage notes

  • The script of a parameterized view can contain only DML statements, but not the INSERT, CREATE TABLE, or PRINT statement.
  • 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. The @pv2 :=... statement is similar to the RETURN statement in other programming languages. You can use the @pv2 :=... statement 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. Specifically, 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,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;
    Note RETURNS @ret TABLE (x STRING,y STRING) defines the following information:
    • The return type, that is, the type returned to the caller. The return type is specified by the TABLE (x STRING,y STRING) parameter. You can use this parameter to customize the table schema.
    • The response parameter. The @ret parameter defines the name of the response parameter. The operation of assigning a value to the response parameter is performed in the view script.

    You can consider the view that contains no BEGIN/END or return variables as a simplified parameterized view.