使用 TABLE 參數構建可重複使用的參數化視圖 - 雲原生大數據計算服務 MaxCompute

參數化視圖支援傳入任意表或其它變數，定製視圖的行為。本文為您介紹MaxCompute SQL引擎支援的參數化視圖功能。

功能介紹

MaxCompute傳統的視圖（VIEW）中，底層封裝一段邏輯複雜的SQL指令碼，調用者可以像讀普通表一樣調用視圖，無需關心底層的實現。傳統的視圖實現了一定程度的封裝與重用，因此被廣泛地使用。但是傳統的視圖並不接受調用者傳遞的任何參數（例如調用者無法對視圖讀取的底層表進行資料過濾或傳遞其他參數），導致代碼重用能力低下。MaxCompute當前的SQL引擎支援帶參數的視圖，支援傳入任意表或者其它變數，定製視圖的行為。

命令格式

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：必填。視圖名稱。
variable_name：必填。視圖變數名稱。
variable_type：必填。視圖變數參數類型。
return_variable：可選。視圖返回的變數名稱。
col_name：可選。視圖返回列的名稱。
col_type：可選。視圖返回列的類型。
col_comment：可選。視圖返回列的注釋。
view_comment：可選。視圖的注釋。
select_statement：條件必選。select子句。
statements：條件必選。視圖指令碼。

定義參數化視圖

建立帶參數的視圖，文法如下。

-- 執行定義視圖SQL前提條件，先建立對應表（若存在請忽略）。
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;

文法說明：

因為定義了參數，所以定義參數化視圖需要通過指令碼模式操作。
建立的視圖pv1有兩個參數，即表參數和STRING參數，參數可以是任意的表或基礎資料型別 (Elementary Data Type)。
支援使用子查詢作為參數的值，例如SELECT * FROM view_name( (SELECT 1 FROM src WHERE a > 0), 1);。
定義視圖時，您可以為參數指定ANY類型，表示任意資料類型。例如CREATE VIEW paramed_view (@a any) AS SELECT * FROM src WHERE case WHEN @a is null then key1 else key2 end = key3;，定義了視圖的第一個參數可以接受任意類型。
但是ANY類型不能參與類似+、and需要明確類型才能執行的運算。ANY類型通常用在Table參數中作為PassThrough列，樣本如下。
```
-- 執行定義視圖SQL前提條件，先建立對應表（若存在請忽略）。
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;
--調用樣本。
SELECT * FROM paramed_view ((SELECT name, id, age FROM students));
```
說明
使用create view建立視圖後，可以執行desc命令擷取視圖的描述。此描述中包含視圖的傳回型別資訊。
視圖的傳回型別是在調用時重新推算的，它可能與建立視圖時不一致，例如ANY類型。
定義視圖時，Table的參數還支援使用星號（*）表示任意多個列。星號（*）可以指定資料類型，也可以使用ANY類型，樣本如下。
```
-- 執行定義視圖SQL前提條件，先建立對應表（若存在請忽略）。
CREATE TABLE school(name STRING, address STRING);
CREATE TABLE student(name STRING, school STRING, age STRING, address STRING);

-- 定義視圖。
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;
--調用樣本。
SELECT name, address FROM paramed_view1 ((SELECT school, name, age, address FROM student), school) WHERE age < 20;
```
樣本中的視圖接受兩個表值參數。第一個表值參數第一列是STRING類型，後面可以是任意多個任意類型的列；第二個表值參數的第一列是STRING類型，後面可以是任意多個STRING類型的列。注意事項如下：
- 變長部分必須寫在表值參數定義語句的最後位置，即在星號（*）的後面不允許再出現其它列。因此，一個表值參數中最多隻有一個變長列列表。
- 由於變長部分必須寫在表值參數定義語句的最後位置，有時輸入表的列不一定是按照這種順序排列的，這時需要重排輸入表的列，可以以子查詢作為參數（參考上述樣本），子查詢外面必須加一層括弧。
- 因為表值參數中變長部分沒有名字，所以在視圖定義過程中無法獲得對這部分資料的引用，也無法對這些資料做運算。
- 雖然無法對變長部分做運算，但可以使用select *這種萬用字元將變長部分的列傳遞出去。
- 表值參數的列與定義視圖時指定的定長列部分不一定會完全一致。如果名字不一致，編譯器會自動做重新命名；如果類型不一致，編譯器會做隱式轉換（不能隱式轉換時，會發生報錯）。

調用參數化視圖

調用已經定義的pv1視圖的樣本如下。

-- 執行調用視圖SQL前提條件，先建立對應表（若存在請忽略）。
CREATE TABLE src (key STRING, value BIGINT);
CREATE TABLE src2 (key STRING, value BIGINT);
CREATE TABLE src3 (key STRING, value BIGINT);

-- 調用樣本。
@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);

說明

您可以使用不同的參數調用pv1：

表參數可以是物理表、VIEW、表變數或者CTE中的表別名。
普通參數可以是變數或常量。

參數化視圖說明

參數化視圖中，指令碼中只能使用DML語句，不能使用INSERT或CREATE TABLE 語句，也不能使用螢幕顯示語句。

參數化視圖不一定只有一個SQL語句，也可以像指令碼一樣，包含多個語句。

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

說明

BEGIN到END之間的語句，就是這個視圖的指令碼。@pv2 :=...語句相當於其他語言中的RETURN語句，用於向一個與視圖同名的隱含表變數賦值。

在視圖參數匹配時，實參和形參匹配的規則和普通的弱類型語言一樣，如果傳入的視圖參數可以被隱式轉換，則可與所定義的參數匹配。例如，BIGINT的值可以匹配DOUBLE類型的參數。對於表變數，如果表a的Schema可以被用於插入到表b中，則意味著表a可以用來匹配和表b的Schema相同的表型別參數。
您可以明確地聲明傳回型別，以提升代碼的可讀性。
```
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)定義了：
- 傳回型別為TABLE (x STRING, y STRING)，即返回給調用者的類型，可以在此處定製表的Schema。
- 返回參數為@ret，為它賦值的操作會在視圖的指令碼中進行，此處相當於定義了返回的參數名。
您可以將沒有BEGIN/END和返回變數的視圖，看作是此形式的簡化形式。