MaxCompute:Parameterized view

To create a parameterized view, use the following syntax:

-- prerequisite for defining a view SQL: create tables(ignore if it already exists)
CREATE TABLE srcp (key STRING, value BIGINT, p STRING);

-- 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:

  -- prerequisite for defining a view SQL: create tables(ignore if it already exists)
  CREATE TABLE students (name STRING, id BIGINT, age BIGINT);
  
  -- define a view
  CREATE VIEW paramed_view  (@a TABLE (name STRING, id ANY, age BIGINT)) 
  AS SELECT * FROM @a WHERE name = 'foo' AND age< 25;
  -- example
  SELECT * FROM paramed_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:

  -- prerequisite for defining a view SQL: create tables(ignore if it already exists)
  CREATE TABLE school(name STRING, address STRING);
  CREATE TABLE student(name STRING, school STRING, age STRING, address STRING);
  
  -- define a view
  CREATE VIEW paramed_view1 (@a TABLE(key STRING, * ANY), @b TABLE(key STRING, * STRING)) 
  AS SELECT a.* FROM @a JOIN @b ON a.key = b.key;
  -- example
  SELECT name, address FROM paramed_view1 ((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.

You can execute the following statement to call the pv1 view that you defined:

-- prerequisite for calling a view SQL: create tables(ignore if it already exists)
CREATE TABLE src (key STRING, value BIGINT);
CREATE TABLE src2 (key STRING, value BIGINT);
CREATE TABLE src3 (key STRING, value BIGINT);

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