Build Reusable Parameterized Views with TABLE Parameters - MaxCompute

Parameterized views extend standard MaxCompute views by accepting table inputs and scalar values as parameters. Unlike traditional views, parameterized views let you pass filter conditions, table references, and other arguments at call time — reusing complex SQL logic across different datasets without duplicating code.

When to use parameterized views

Goal	Recommended approach
Encapsulate SQL logic once and call it with different datasets	Parameterized view
Apply the same join or filter logic to multiple input tables	Parameterized view
Create a simple, non-parameterized SQL shortcut	Traditional view

Note

Parameterized views must be defined using Script Mode SQL.

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}

Parameters

Parameter	Required	Description
`view_name`	Yes	Name of the view
`variable_name`	Yes	Name of a view parameter
`variable_type`	Yes	Data type of a view parameter
`return_variable`	No	Name of the variable that holds the return value
`col_name`	No	Name of a column in the return table
`col_type`	No	Data type of a column in the return table
`col_comment`	No	Comment for a column in the return table
`view_comment`	No	Comment for the view
`select_statement`	Yes (or `statements`)	A `SELECT` clause that defines the view body
`statements`	Yes (or `select_statement`)	A multi-statement script between `BEGIN` and `END`

Define and call a parameterized view

Scalar parameters

Scalar parameters accept a single value — for example, a STRING or BIGINT constant.

The following example creates view pv1 that joins the srcp table with a table parameter @a on matching keys, filtered by a string parameter @b, then calls it three ways in a single Script Mode SQL session:

-- Create the prerequisite table (skip if it already exists)
CREATE TABLE srcp (key STRING, value BIGINT, p STRING);

-- Create a parameterized view
-- @a: a table parameter with schema (k STRING, v BIGINT)
-- @b: a string scalar parameter
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;

-- Create prerequisite tables for calling the view (skip if they already exist)
CREATE TABLE src  (key STRING, value BIGINT);
CREATE TABLE src2 (key STRING, value BIGINT);
CREATE TABLE src3 (key STRING, value BIGINT);

-- Pass a table variable and a string constant
@a := SELECT * FROM src WHERE value > 0;
@b := SELECT * FROM pv1(@a, '20170101');

-- Pass a physical table name and a scalar variable
@another_day := '20170102';
@c := SELECT * FROM pv1(src2, @another_day);

-- Pass a CTE alias
@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);

The table parameter accepts:

A physical table or view name (for example, src2)
A table variable (for example, @a)
A subquery enclosed in parentheses (for example, (SELECT * FROM src WHERE value > 0))
A CTE alias (for example, t)

Scalar parameters accept variables or constants.

Table parameters

Table parameters accept a table reference — a physical table, a view, a table variable, or a common table expression (CTE) alias. The table schema must match the schema declared in the parameter definition.

Using the `any` type

Set a column type to any when the parameterized view does not need to inspect or compute that column's value — for example, when passing it through unchanged with SELECT *. The any type cannot be used in operations that require a specific data type, such as + or AND.

CREATE TABLE students (name STRING, id BIGINT, age BIGINT);

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 paramed_view ((SELECT name, id, age FROM students));

Note

Run DESC <view_name> to inspect the return type of a view. The return type is recalculated each time the view is called, so it may differ from the type specified at creation (for example, any at definition time may resolve to a concrete type at call time).

Using a wildcard (*) in a table parameter

Use * in a table parameter to accept any additional columns beyond the fixed ones. The wildcard section is called the variable-length part.

CREATE TABLE school (name STRING, address STRING);
CREATE TABLE student (name STRING, school STRING, age STRING, address STRING);

-- @a: first column is STRING, followed by any columns of any type
-- @b: first column is STRING, followed by any columns of STRING type
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;

-- Call the view
SELECT name, address FROM paramed_view1 (
    (SELECT school, name, age, address FROM student),
    school
) WHERE age < 20;

The variable-length part has these constraints:

It must appear at the end of the table schema — no fixed columns can follow the *.
Each table parameter can have only one variable-length part.
The variable-length part has no name, so you cannot reference or compute its columns directly. Use SELECT * to pass data through.
If column names differ between the input table and the parameter definition, the compiler renames them automatically. If data types differ, the compiler performs an implicit conversion. If the conversion fails, an error is returned.

Multi-statement views

A parameterized view can contain multiple SQL statements in a BEGIN...END block. The last assignment to the implicit table variable — the variable with the same name as the view — acts as the return value.

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

@pv2 := ... is the return assignment. It is equivalent to a RETURN statement — it assigns the result to an implicit table variable that has the same name as the view.

Declare an explicit return type

Declare the RETURNS clause to document the output schema and make the view easier to use.

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;

RETURNS @ret TABLE (x STRING, y STRING) defines two things:

Return schema — TABLE (x STRING, y STRING) is the type returned to the caller. Use it to enforce a consistent output schema.
Return variable — @ret is the named variable assigned in the view script. Assigning to @ret is the return statement for this view.

A view with no BEGIN...END block and no explicit RETURNS clause is a simplified parameterized view.

Usage notes

Only DML statements are allowed in the view script. SELECT statements that return output directly, INSERT, and CREATE TABLE are not supported.
Parameter matching follows weakly-typed language rules: if an actual parameter can be implicitly converted to the declared type, it matches. For example, BIGINT matches a DOUBLE parameter. For table parameters, if the schema of the input table is insertable into the declared schema, the parameter matches.
Define the view body using a SELECT clause (simplified form) or a BEGIN...END script block (multi-statement form).