This topic describes the parameterized view feature supported by the current MaxCompute SQL engine.
In the traditional views of MaxCompute, complex SQL scripts are encapsulated at the underlying layer. Callers call views like reading a common table without understanding the implementation mechanism at the underlying layer. 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 read by a view nor pass other parameters. The current MaxCompute SQL engine supports parameter-based views and allows you to import any tables or other variables to customize views.
Define a parameterized view
The syntax is described as follows:
-- 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;
- Parameters are defined, so you must perform operations by using Script Mode SQL.
- The created
pv1view 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 fromsrc where a > 0), 1);.
- When you define a view, you can set
ANYfor 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 calculations that require specific data types, such as
AND. Fields of the ANY type are often used as a PassThrough column in the TABLE parameter. The following is an 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 VIEWstatement and create a view, you can run the
DESCcommand to obtain the description of the view. This description contains the return type of the view.
The return type of a view is recalculated during a call. It may be different from that defined when you created a view, for example, ANY.
- When you define a view, you can use the wildcard character (
*) in the TABLE parameter to retrieve any columns.
*can be a specific data type or ANY type. The following is an 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 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 specified by the second TABLE parameter, data in the first column is of the string type, and data in other columns is also of the string type. You need to follow these limits and guidelines:
- The varied-length part must be placed at the end of the table specified by the TABLE
parameter, that is, the
*column cannot be followed by other columns. Therefore, the table 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 specified by the TABLE parameter. However, the columns of an imported 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.
- There is no name for the varied-length part of the table 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 column out of the table.
- The column of the table specified by the TABLE parameter may not be exactly the same as the fixed-length column specified in the view statement. If the names are inconsistent, the compiler automatically renames them. If the data types are inconsistent, the compiler performs an implicit conversion. If the implicit conversion fails, an error occurs.
- The varied-length part must be placed at the end of the table specified by the TABLE parameter, that is, the
Call a parameterized view
@a := select * from src where value >0; --call view with table variable an 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);
pv1view. The TABLE parameter can be a physical table, view, table variable, or table alias in common table expressions (CTEs). Common parameters can be variables or constants.
- In a parameterized view, only DML statements can be used in scripts. The
CREATE TABLE, or statement displayed on the screen cannot be used.
- A parameterized view like a script 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
endis the script of this view. The
@pv2 :=...statement is similar to the
RETURNstatement in other programming languages, which is 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 normal weakly-typed language. Specifically, if a view parameter can be implicitly converted, it can match the defined parameter. For example, the BIGINT value 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 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 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. The operation of assigning a value to the parameter is performed in the view script. This parameter defines the name of the response parameter.
You can think of the view that contains no
begin/endor return variables as a simplified parameterized view.
- The return type is