Snowflake 的表函式，對於提升資料處理效率和程式碼可讀性來說，絕對是一大利器。它讓開發者可以將複雜的 SQL 邏輯封裝成可重複使用的函式，就像呼叫一般函式一樣簡單。不同於預存程式，表函式可以直接在 SQL 查詢中使用，大幅提升了程式碼的靈活性和可維護性。
在台灣的資料函式庫開發領域，預存程式一直是處理複雜邏輯的常見做法。但隨著資料量的爆炸性增長和雲端資料倉儲的普及，表函式的優勢越來越明顯。它不僅簡化了 SQL 查詢，更提升了查詢效能。尤其在 Snowflake 環境下，表函式與其他 Snowflake 功能的整合，更能發揮其強大威力。
表函式的應用場景：從資料準備到商業邏輯
表函式的應用場景非常廣泛，從簡單的資料轉換到複雜的商業邏輯，都能輕鬆應付。
- 資料清理與轉換： 可以將常用的資料清理邏輯，例如去除空白、格式轉換等，封裝成表函式，方便重複使用。
- 資料聚合與統計： 可以將複雜的資料聚合和統計邏輯，例如計算移動平均、同比環比等，封裝成表函式，簡化查詢。
- 商業規則的實作： 可以將商業規則，例如價格計算、折扣規則等，封裝成表函式，讓 SQL 查詢更具業務意義。
- 動態資料生成： 可以使用表函式生成測試資料或模擬資料，方便開發和測試。
表函式實戰：開發你的資料分析工具箱
以下幾個例子，將展示如何使用表函式簡化資料查詢和處理。
範例一：計算客戶的平均消費金額
CREATE OR REPLACE FUNCTION calculate_avg_spending(customer_id INT)
RETURNS TABLE (avg_spending FLOAT)
AS
$$
SELECT AVG(order_total)
FROM orders
WHERE customer_id = calculate_avg_spending.customer_id
$$;
SELECT c.customer_name, cas.avg_spending
FROM customers c
JOIN TABLE(calculate_avg_spending(c.customer_id)) cas;
這個例子示範瞭如何使用表函式計算客戶的平均消費金額。函式 calculate_avg_spending 接受客戶 ID 作為輸入，並回傳一個包含平均消費金額的表格。
範例二：根據日期範圍篩選訂單
CREATE OR REPLACE FUNCTION get_orders_by_date(start_date DATE, end_date DATE)
RETURNS TABLE (order_id INT, order_date DATE, order_total FLOAT)
AS
$$
SELECT order_id, order_date, order_total
FROM orders
WHERE order_date BETWEEN start_date AND end_date
$$;
SELECT *
FROM TABLE(get_orders_by_date('2024-01-01', '2024-12-31'));
這個例子示範瞭如何使用表函式根據日期範圍篩選訂單。函式 get_orders_by_date 接受起始日期和結束日期作為輸入，並回傳一個包含符合條件的訂單的表格。
範例三：動態生成月份資料
CREATE OR REPLACE FUNCTION generate_months(start_year INT, end_year INT)
RETURNS TABLE (year INT, month INT)
AS
$$
SELECT year, month
FROM (
SELECT YEAR(DATEADD(month, seq4(), DATE_FROM_PARTS(start_year, 1, 1))) as year,
MONTH(DATEADD(month, seq4(), DATE_FROM_PARTS(start_year, 1, 1))) as month
FROM TABLE(GENERATOR(ROWCOUNT => (end_year - start_year + 1) * 12))
)
WHERE year <= end_year
$$;
SELECT * FROM TABLE(generate_months(2023, 2024));
這個例子示範瞭如何使用表函式動態生成月份資料。函式 generate_months 接受起始年份和結束年份作為輸入，並回傳一個包含年份和月份的表格。
玄貓的建議：善用表函式，提升你的 Snowflake 開發效率
在 Snowflake 中，表函式是一個強大與易於使用的工具，可以簡化資料查詢和處理。玄貓建議各位開發者，在日常開發中，多多嘗試使用表函式，將常用的 SQL 邏輯封裝起來，提升程式碼的可讀性和可維護性，讓你的 Snowflake 開發更有效率。
Snowflake玄貓Stored Procedure實戰：資料倉儲銷售資料整合
在Snowflake中，Stored Procedure是使用Snowflake Scripting編寫並儲存在資料函式庫中的編譯程式。本文將探討如何利用Stored Procedure來增強資料處理能力，並分享我在資料倉儲專案中的實戰經驗。
為何我擁抱Stored Procedure：優勢分析
Stored Procedure具備多項優勢，使其成為資料函式庫開發中不可或缺的一部分：
- 程式碼重用與分享：可重複使用的指令碼儲存在資料函式庫中，方便團隊分享和呼叫。
- 引數化增強靈活性：允許傳入多個引數，客製化指令碼行為，提升靈活性。
- Overload實作：相同名稱的Stored Procedure，只要引數型別不同，即可同時存在。
- 隱藏複雜邏輯：對終端使用者隱藏底層複雜的資料轉換和處理邏輯，提高使用便利性。
從指令碼到Stored Procedure：改造實戰
讓我們從一個簡單的計算平均餘額的指令碼開始，將其轉換為Stored Procedure。
原始指令碼
這個指令碼計算一個月的平均每日餘額。要將其轉換為Stored Procedure，需要進行以下修改：
- 新增
CREATE PROCEDURE陳述式，並提供程式名稱，例如get_avg_balance。 - 提供兩個引數
p_total_bal和p_num_days，分別代表總餘額和天數。 - 定義Stored Procedure的回傳值型別為
NUMBER。 - 指定Stored Procedure使用Snowflake Scripting編寫。
轉換後的Stored Procedure
create or replace procedure get_avg_balance(p_total_bal number, p_num_days number)
returns number
language sql
as
$$
declare
v_avg_bal number;
begin
v_avg_bal := p_total_bal / p_num_days;
return v_avg_bal;
end;
$$;
在這個範例中，LANGUAGE SQL用來指定使用Snowflake Scripting，但因為SQL是預設語言，所以可以省略。
Stored Procedure執行方式：多種選擇
建立Stored Procedure後，有多種執行方式可供選擇。
使用CALL命令
最簡單的方式是使用CALL命令。
call get_avg_balance(12000, 30);
這個命令會執行get_avg_balance()，並將引數值12000和30傳入，結果（400）會顯示在結果區域。
在指令碼中使用CALL並捕捉結果
declare
v_avg_bal number;
begin
call get_avg_balance(12000, 30) into :v_avg_bal;
return :v_avg_bal;
end;
這個版本使用CALL命令的INTO子句，將結果儲存到變數v_avg_bal中。
Stored Procedure實戰：構建資料倉儲
現在，讓我們建立一個更有趣的Stored Procedure，用於資料倉儲中的銷售資料整合。
建立資料倉儲表
首先，建立一個用於儲存跨國家、年份和月份的彙總銷售資料的資料倉儲表。
create table wh_country_monthly_sales (
sales_year number,
sales_month number,
region_name varchar(20),
country_name varchar(30),
total_sales number
);
建立Stored Procedure
接下來，建立一個Stored Procedure來填充這個新表。這個程式應該執行以下操作：
- 開始一個事務。
- 刪除指定區域和年份的任何現有資料。
- 插入指定區域和年份的資料列。
- 提交事務，如果發生錯誤則回復。
- 回傳刪除和插入的資料列數作為一個檔案。
create or replace procedure ins_country_monthly_sales(
p_region_name varchar,
p_year number
)
returns object
language sql
as
$$
declare
v_num_del number;
v_num_ins number;
result object;
begin
begin transaction;
delete from wh_country_monthly_sales
where region_name = :p_region_name and sales_year = :p_year;
v_num_del := sqlrowcount;
insert into wh_country_monthly_sales (sales_year, sales_month, region_name, country_name, total_sales)
select
date_part(year, o.o_orderdate) as sales_year,
date_part(month, o.o_orderdate) as sales_month,
r.r_name as region_name,
n.n_name as country_name,
sum(o.o_totalprice) as total_sales
from
snowflake_sample_data.tpch_sf1.orders o
join snowflake_sample_data.tpch_sf1.customer c on o.o_custkey = c.c_custkey
join snowflake_sample_data.tpch_sf1.nation n on c.c_nationkey = n.n_nationkey
join snowflake_sample_data.tpch_sf1.region r on n.n_regionkey = r.r_regionkey
where r.r_name = :p_region_name and date_part(year, o.o_orderdate) = :p_year
group by 1, 2, 3, 4;
v_num_ins := sqlrowcount;
commit;
result := object_construct('deleted_rows', v_num_del, 'inserted_rows', v_num_ins);
return result;
exception
when others then
rollback;
result := object_construct('status', 'error', 'message', sqlerrm);
return result;
end;
$$;
程式碼解密
- 宣告變數：
v_num_del：用於儲存刪除的資料列數。v_num_ins：用於儲存插入的資料列數。result：用於儲存包含操作結果的物件。
- 開始事務：
begin transaction;：啟動一個事務，確保所有操作要麼全部成功，要麼全部失敗。
- 刪除現有資料：
delete from wh_country_monthly_sales where region_name = :p_region_name and sales_year = :p_year;：從wh_country_monthly_sales表中刪除指定區域和年份的資料。v_num_del := sqlrowcount;：將刪除的資料列數儲存在v_num_del變數中。sqlrowcount是一個Snowflake提供的函式，用於回傳上一個SQL陳述式影響的資料列數。
- 插入新資料：
insert into wh_country_monthly_sales (sales_year, sales_month, region_name, country_name, total_sales) select ...：從Snowflake範例資料函式庫的snowflake_sample_data.tpch_sf1中選取資料，並將其插入到wh_country_monthly_sales表中。- 這個查詢使用多個JOIN操作將
orders、customer、nation和region表連線起來，然後按年份、月份、區域和國家分組，計算總銷售額。 where r.r_name = :p_region_name and date_part(year, o.o_orderdate) = :p_year：篩選出指定區域和年份的資料。v_num_ins := sqlrowcount;：將插入的資料列數儲存在v_num_ins變數中。
- 提交事務：
commit;：提交事務，將所有變更永久儲存到資料函式庫中。
- 構建結果物件：
result := object_construct('deleted_rows', v_num_del, 'inserted_rows', v_num_ins);：建立一個包含刪除和插入資料列數的JSON物件。object_construct是Snowflake提供的函式，用於建立JSON物件。
- 回傳結果：
return result;：回傳包含操作結果的JSON物件。
- 例外處理：
exception when others then：捕捉任何可能發生的異常。rollback;：回復事務，復原所有變更。result := object_construct('status', 'error', 'message', sqlerrm);：建立一個包含錯誤訊息的JSON物件。return result;：回傳包含錯誤訊息的JSON物件。
程式碼總結
這段程式碼實作了一個完整的資料ETL流程，包括資料的刪除、轉換和插入，同時具備完善的錯誤處理機制，確保資料的一致性和可靠性。透過Snowflake的Stored Procedure，我們可以將複雜的資料處理邏輯封裝起來，方便重複使用和管理。
Snowflake 儲存程式：提升資料函式庫操作的彈性與效率
儲存程式是資料函式庫中預先編譯好的一組 SQL 指令和程式邏輯，可以像呼叫函式一樣重複使用。在 Snowflake 中，儲存程式不僅能簡化複雜的資料函式庫操作，還能提高資料處理的效率和安全性。本文將探討 Snowflake 儲存程式的建立、使用場景及一些進階技巧，助你充分利用這項強大功能。
建立你的第一個 Snowflake 儲存程式
讓我們先從一個簡單的例子開始。假設你想要建立一個儲存程式，用於將特定區域和年份的銷售資料插入到一個名為 country_monthly_sales 的資料表中。以下是一個實作此功能的儲存程式範例：
create or replace procedure ins_country_monthly_sales(
p_region_name varchar(20),
p_year number
)
returns object
language sql
as
declare
v_num_del number := 0;
v_num_ins number := 0;
begin
-- 刪除已存在的資料
delete from country_monthly_sales
where region_name = :p_region_name and year = :p_year;
v_num_del := sqlrowcount;
-- 插入新的銷售資料
insert into country_monthly_sales
select
date_part(year, o.o_orderdate) as year,
date_part(month, o.o_orderdate) as month,
r.r_name as region_name,
n.n_name as country_name,
sum(o.o_totalprice) as total_sales
from orders as o
inner join customer as c
on o.o_custkey = c.c_custkey
inner join nation as n
on c.c_nationkey = n.n_nationkey
inner join region as r
on n.n_regionkey = r.r_regionkey
where r.r_name = :p_region_name
and date_part(year, o.o_orderdate) = :p_year
group by date_part(year, o.o_orderdate),
date_part(month, o_orderdate),
r.r_name,
n.n_name;
v_num_ins := sqlrowcount;
commit;
return object_construct('rows deleted',v_num_del,
'rows inserted',v_num_ins);
exception
when other then
rollback;
raise;
end;
內容解密：
create or replace procedure ins_country_monthly_sales(...): 這行程式碼用於建立或替換一個名為ins_country_monthly_sales的儲存程式。create or replace語法確保如果儲存程式已存在，則會被替換；如果不存在，則會建立一個新的。p_region_name varchar(20), p_year number: 這部分定義了儲存程式的輸入引數。p_region_name是一個 VARCHAR 型別的引數，用於指定區網域名稱，最大長度為 20 個字元。p_year是一個 NUMBER 型別的引數，用於指定年份。returns object: 這指定了儲存程式的回傳型別。object是一種半結構化資料型別，允許儲存程式回傳包含多個鍵值對的物件。language sql: 這指定了儲存程式使用的語言為 SQL。Snowflake 支援使用 SQL 或 JavaScript 作為儲存程式的語言。as declare ... begin ... end: 這部分包含了儲存程式的實際邏輯。declare部分用於宣告儲存程式中使用的變數，begin和end之間包含了儲存程式的 SQL 指令。v_num_del number := 0; v_num_ins number := 0;: 這兩行宣告了兩個變數v_num_del和v_num_ins，用於儲存刪除和插入的資料列數。:= 0表示將這些變數初始化為 0。delete from country_monthly_sales ...: 這是一個 DELETE 陳述式，用於從country_monthly_sales資料表中刪除符合指定區網域名稱和年份的資料列。where region_name = :p_region_name and year = :p_year子句使用輸入引數p_region_name和p_year來篩選要刪除的資料列。在 SQL 陳述式中參照引數或變數時，必須在其前面加上:。這被稱為繫結變數，表示變數的值在執行時會繫結到陳述式上。v_num_del := sqlrowcount;: 這行程式碼將sqlrowcount變數的值賦給v_num_del變數。sqlrowcount是 Snowflake 提供的一個變數，用於儲存前一個 SQL 陳述式修改的資料列數。insert into country_monthly_sales ...: 這是一個 INSERT 陳述式，用於將新的銷售資料插入到country_monthly_sales資料表中。select ... from orders ...子查詢用於從orders、customer、nation和region資料表中選取資料，並將其轉換為所需的格式。v_num_ins := sqlrowcount;: 這行程式碼將sqlrowcount變數的值賦給v_num_ins變數，用於儲存插入的資料列數。commit;: 這行程式碼用於提交事務。在 Snowflake 中，預設情況下，每個 SQL 陳述式都在一個單獨的事務中執行。commit陳述式用於將當前事務中的所有變更永久儲存到資料函式庫中。return object_construct('rows deleted',v_num_del, 'rows inserted',v_num_ins);: 這行程式碼使用object_construct函式建立一個物件，其中包含兩個鍵值對：'rows deleted'和'rows inserted'。這些鍵值對分別對應於刪除和插入的資料列數。然後，儲存程式回傳這個物件。object_construct函式用於建立物件，因此儲存程式的回傳型別必須是object。exception when other then ...: 這部分用於處理異常。如果儲存程式在執行過程中發生任何錯誤，exception區塊中的程式碼將會被執行。rollback;: 這行程式碼用於回復事務。如果發生錯誤，rollback陳述式會復原當前事務中的所有變更，以確保資料函式庫的一致性。raise;: 這行程式碼用於引發異常。在回復事務後，raise陳述式會將異常重新引發，以便呼叫者可以知道儲存程式執行失敗。
執行儲存程式並檢視結果
建立完成後，你可以像這樣執行該儲存程式：
call ins_country_monthly_sales('EUROPE', 1997);
執行後，儲存程式會回傳一個包含刪除和插入資料列數的物件。例如：
{
"rows deleted": 0,
"rows inserted": 60
}
這個結果表示在 Europe 區域 1997 年的資料被成功插入，與沒有刪除任何現有資料（因為資料表原本是空的）。
儲存程式多載：提供更彈性的呼叫方式
Snowflake 允許儲存程式多載，這意味著你可以建立多個同名的儲存程式，只要它們的引數型別不同即可。這能讓你的儲存程式更具彈性，以適應不同的使用情境。
例如，你可以建立另一個版本的 ins_country_monthly_sales 儲存程式，它接受區域的 regionkey 而不是 region_name 作為引數。以下是如何實作的範例：
create or replace procedure ins_country_monthly_sales(
p_regionkey number,
p_year number
)
returns object
language sql
as
declare
v_region_name varchar(20);
v_document object;
begin
-- 根據 regionkey 取得 region_name
select r.r_name
into :v_region_name
from region as r
where r.r_regionkey = :p_regionkey;
-- 呼叫原始的儲存程式
call ins_country_monthly_sales(:v_region_name, :p_year)
into :v_document;
return v_document;
end;
內容解密：
create or replace procedure ins_country_monthly_sales(p_regionkey number, p_year number)：這行程式碼定義了一個新的儲存程式，與之前的儲存程式同名，但引數不同。這個版本接受一個p_regionkey引數，型別為NUMBER，用於指定區域的鍵值。returns object language sql as declare ... begin ... end：這部分與之前的儲存程式相同，定義了回傳型別、語言以及程式碼區塊。v_region_name varchar(20); v_document object;：宣告了兩個變數：v_region_name用於儲存從region資料表中查詢到的區網域名稱，v_document用於儲存呼叫另一個儲存程式後的回傳值。select r.r_name into :v_region_name from region as r where r.r_regionkey = :p_regionkey;：這段程式碼從region資料表中選取與輸入的p_regionkey相符的區網域名稱，並將其儲存到v_region_name變數中。into :v_region_name子句用於將查詢結果儲存到變數中。call ins_country_monthly_sales(:v_region_name, :p_year) into :v_document;：這行程式碼呼叫了另一個名為ins_country_monthly_sales的儲存程式，但引數不同。這個呼叫使用了之前查詢到的區網域名稱v_region_name和輸入的年份p_year作為引數。into :v_document子句用於將被呼叫儲存程式的回傳值儲存到v_document變數中。return v_document;：這行程式碼回傳了儲存程式的回傳值，也就是被呼叫儲存程式的回傳值。
現在，你可以使用 regionkey 來呼叫這個儲存程式：
call ins_country_monthly_sales(3, 1997);
這個儲存程式會先根據 regionkey 找到對應的 region_name，然後呼叫原始的 ins_country_monthly_sales 儲存程式。
儲存程式與事務
在 Snowflake 中，儲存程式可以參與事務，但有一些規則需要注意：
- 如果在呼叫儲存程式之前已經開始了一個事務，則儲存程式可以參與該事務，但不能使用
COMMIT或ROLLBACK結束該事務。 - 如果在儲存程式內部開始了一個事務，則必須在同一個儲存程式內部使用
COMMIT或ROLLBACK結束該事務。
回傳結果集：更豐富的回傳資訊
除了回傳純量值或物件之外，Snowflake 儲存程式還可以回傳結果集。要回傳結果集，你需要在 RETURNS 子句中指定 TABLE(column1, column2, ...)，其中 column1, column2 等是結果集的欄位名稱。
以下是一個回傳結果集的儲存程式範例：
create or replace procedure get_country_names(p_regionkey number)
returns table(key number, name varchar)
language sql
as
declare
v_results resultset;
begin
v_results := (
select n_nationkey, n_name
from nation
where n_regionkey = :p_regionkey
);
return table(v_results);
end;
內容解密：
create or replace procedure get_country_names(p_regionkey number)：這行程式碼定義了一個名為get_country_names的儲存程式，它接受一個p_regionkey引數，型別為NUMBER，用於指定區域的鍵值。returns table(key number, name varchar)：這指定了儲存程式的回傳型別為TABLE，表示它將回傳一個結果集。table(key number, name varchar)定義了結果集的結構，包括兩個欄位：key，型別為NUMBER；name，型別為VARCHAR。language sql as declare ... begin ... end：這部分與之前的儲存程式相同，定義了語言以及程式碼區塊。v_results resultset;：宣告了一個名為v_results的變數，型別為resultset。resultset是一種特殊的資料型別，用於儲存查詢的結果集。v_results := (select n_nationkey, n_name from nation where n_regionkey = :p_regionkey);：這段程式碼執行一個查詢，從nation資料表中選取與輸入的p_regionkey相符的國家的鍵值和名稱，並將結果儲存到v_results變數中。return table(v_results);：這行程式碼使用table()函式將v_results變數中的結果集轉換為表格形式，並將其回傳。table()函式是 Snowflake 提供的一個內建函式，用於將結果集轉換為表格形式。
你可以這樣呼叫這個儲存程式：
call get_country_names(3);
儲存程式會回傳一個包含 key 和 name 欄位的結果集，列出 Europe 區域的所有國家。
傳遞陣列作為引數：處理更複雜的輸入
Snowflake 儲存程式支援使用陣列作為引數，這使得你可以一次傳遞多個值給儲存程式。要使用陣列作為引數，你需要使用 ARRAY 資料型別。
以下是一個接受陣列引數的儲存程式範例：
create or replace procedure get_rgn_country_name(p_region_names array)
returns table(rgn varchar, key number, name varchar)
language sql
as
declare
v_results resultset;
begin
v_results := (
select r.r_name, n.n_nationkey, n.n_name
from nation n
inner join region r on r.r_regionkey = n.n_regionkey
where array_contains(r.r_name::variant, :p_region_names)
);
return table(v_results);
end;
內容解密：
create or replace procedure get_rgn_country_name(p_region_names array)：這行程式碼定義了一個名為get_rgn_country_name的儲存程式，它接受一個p_region_names引數，型別為ARRAY，用於指定區網域名稱的陣列。returns table(rgn varchar, key number, name varchar)：這指定了儲存程式的回傳型別為TABLE，表示它將回傳一個結果集。table(rgn varchar, key number, name varchar)定義了結果集的結構，包括三個欄位：rgn，型別為VARCHAR，表示區網域名稱；key，型別為NUMBER，表示國家鍵值；name，型別為VARCHAR，表示國家名稱。language sql as declare ... begin ... end：這部分與之前的儲存程式相同，定義了語言以及程式碼區塊。v_results resultset;：宣告了一個名為v_results的變數，型別為resultset，用於儲存查詢的結果集。v_results := (select r.r_name, n.n_nationkey, n.n_name from nation n inner join region r on r.r_regionkey = n.n_regionkey where array_contains(r.r_name::variant, :p_region_names));：這段程式碼執行一個查詢，從nation和region資料表中選取與輸入的p_region_names陣列中包含的區網域名稱相符的國家的區網域名稱、鍵值和名稱，並將結果儲存到v_results變數中。array_contains(r.r_name::variant, :p_region_names)函式用於檢查r.r_name是否包含在p_region_names陣列中。::variant用於將r.r_name轉換為VARIANT型別，因為array_contains函式需要VARIANT型別的輸入。return table(v_results);：這行程式碼使用table()函式將v_results變數中的結果集轉換為表格形式，並將其回傳。
你可以這樣呼叫這個儲存程式：
call get_rgn_country_name(['EUROPE'::variant, 'ASIA'::variant]);
這個儲存程式會回傳一個結果集，列出 Europe 和 Asia 區域的所有國家。
Snowflake 儲存程式結果集查詢：玄貓的獨到見解
在 Snowflake 中，儲存程式能夠傳回結果集是一項實用的功能。但如果能直接查詢這些結果集，將會更方便。Snowflake 提供了兩種機制來達成這個目標。
透過 result_scan 查詢結果集
第一種機制是在執行傳回結果集的儲存程式後立即使用 result_scan() 函式。如下所示：
select *
from table(result_scan(last_query_id()));
這段程式碼首先使用 last_query_id() 取得最近執行的查詢識別碼，然後呼叫 result_scan() 函式來檢索與該識別碼相關聯的結果集，最後透過 table() 函式使其能在查詢的 from 子句中被參照。
玄貓認為，result_scan() 函式的用途不僅限於儲存程式，它也能在許多不同的命令之後被呼叫。例如，在執行 show tables 命令後，可以使用 result_scan() 函式來檢視結果：
show tables;
select * from table(result_scan(last_query_id()));
更直接的方式：表格函式
雖然 result_scan() 函式提供了一種方便的方式來檢索先前的結果集，但如果有一種更直接的方式來查詢儲存程式的結果，那就更好了。玄貓將在後面的章節中介紹表格函式，這將是另一種更有效的方法。
動態 SQL：靈活的程式碼生成
正如之前所提到的，Snowflake 的 Scripting 語法包含用於條件邏輯、迴圈和例外處理的陳述式，以及 select、update、insert、delete 和 merge 陳述式。然而，有時你可能需要執行不在 Snowflake Scripting 語言語法中的 create table 陳述式，或者執行一個需要在指令碼中根據呼叫者傳入的引數值來建構的查詢。為了滿足這些需求，Snowflake 提供了 execute immediate 陳述式，它允許你執行包含有效 SQL 陳述式或 Scripting 區塊的字串。
範例：動態查詢資料表
讓玄貓來擴充套件本章稍早的儲存程式 get_country_names()，該程式從 Nation 資料表傳回鍵（n_nationkey）和名稱（n_name）值。除了 Nation 之外，範例資料函式庫中還有其他四個具有鍵/名稱欄位的資料表，如 Table 16-1 所示。
| 資料表名稱 | 鍵欄位 | 名稱欄位 | |
- |
- |
- | | Part | p_partkey | p_name | | Supplier | s_suppkey | s_name | | Region | r_regionkey | r_name | | Customer | c_custkey | c_name |
現在，讓玄貓來編寫一個儲存程式，該程式可以從這些資料表中的任何一個傳回鍵/名稱值，方法是使用引數值動態產生查詢，然後使用 execute immediate 執行查詢。以下是新程式的定義：
create procedure get_key_name_values(p_table_nm varchar,
key_start number,
key_end number)
returns table(key number, name varchar)
language sql
as
$$
declare
v_results resultset;
v_query varchar(200);
v_keycol varchar(20);
v_namecol varchar(20);
begin
-- 確定鍵/名稱欄位的名稱
select max(case when ordinal_position = 1 then column_name else null end) key_col,
max(case when ordinal_position = 2 then column_name else null end) name_col
into v_keycol, v_namecol
from information_schema.columns
where table_schema = 'PUBLIC'
and table_name = upper(:p_table_nm)
and ordinal_position < 3;
-- 建構查詢字串
v_query := concat('select ', v_keycol,', ',v_namecol,
' from ', p_table_nm,
' where ', v_keycol,' between ',
case when p_key_start is null then '0' else p_key_start end,
' and ',
case when p_key_end is null then '999999' else p_key_end end);
-- 執行查詢並傳回結果集
v_results := (execute immediate :v_query);
return table(v_results);
end;
$$;
這個儲存程式的第一個引數 p_table_nm 包含先前列出的五個資料表之一，而 key_start 和 key_end 引數允許呼叫者從資料表中檢索一個子集。例如，執行 get_key_name_values('supplier', 1, 100) 將傳回 s_suppkey 值介於 1 和 100 之間的列。
玄貓說明，這個儲存程式需要執行以下步驟：
- 使用
information_schema.columns識別需要傳回的欄位名稱。鍵欄位始終是第一個欄位，名稱欄位始終是第二個欄位。 - 建立包含查詢的字串。
- 使用
execute immediate執行查詢並檢索結果集。 - 將結果集傳回給呼叫者。
程式碼解密
create procedure get_key_name_values(...): 定義一個名為get_key_name_values的儲存程式，接受三個引數：p_table_nm(資料表名稱),p_key_start(鍵的起始值),p_key_end(鍵的結束值)。returns table(key number, name varchar): 定義儲存程式的回傳值是一個包含key(數字型別) 和name(字串型別) 的表格。language sql: 指定儲存程式使用的語言是 SQL。declare ...: 宣告儲存程式中使用的變數：v_results resultset: 用於儲存查詢結果的結果集變數。v_query varchar(200): 用於儲存動態建構的 SQL 查詢字串。v_keycol varchar(20): 用於儲存鍵欄位名稱。v_namecol varchar(20): 用於儲存名稱欄位名稱。
select ... into v_keycol, v_namecol ...: 從information_schema.columns系統視窗查詢指定資料表的鍵欄位和名稱欄位，並將結果儲存到v_keycol和v_namecol變數中。information_schema.columns: 包含資料函式庫中所有資料表的欄位資訊。where table_schema = 'PUBLIC': 篩選出PUBLIC綱要下的資料表。and table_name = upper(:p_table_nm): 篩選出與輸入引數p_table_nm(轉換為大寫) 相符的資料表。and ordinal_position < 3: 只選擇前兩個欄位 (假設鍵欄位和名稱欄位是前兩個欄位)。max(case when ordinal_position = 1 then column_name else null end) key_col: 選擇第一個欄位作為鍵欄位。max(case when ordinal_position = 2 then column_name else null end) name_col: 選擇第二個欄位作為名稱欄位。
v_query := concat(...): 使用concat函式建構動態 SQL 查詢字串。'select ', v_keycol,', ',v_namecol: 選擇鍵欄位和名稱欄位。' from ', p_table_nm: 指定查詢的資料表。' where ', v_keycol,' between ', ...: 增加WHERE子句，根據輸入引數p_key_start和p_key_end篩選鍵欄位的值。case when p_key_start is null then '0' else p_key_start end: 如果p_key_start為空，則使用 ‘0’ 作為預設起始值。case when p_key_end is null then '999999' else p_key_end end: 如果p_key_end為空，則使用 ‘999999’ 作為預設結束值。
v_results := (execute immediate :v_query): 使用execute immediate執行動態建構的 SQL 查詢，並將結果儲存到v_results變數中。return table(v_results): 將結果集作為表格傳回。
執行結果
執行時，儲存程式會建構一個類別似以下的字串：
"select p_partkey, p_name from part
where p_partkey between 1 and 50"
然後使用 execute immediate 執行該字串，並捕捉結果集並傳回給呼叫者。以下是使用每個五個資料表的一些結果：
call get_key_name_values('region',null,null);
| KEY | NAME | | — |
- | | 0 | AFRICA | | 1 | AMERICA | | 2 | ASIA | | 3 | EUROPE | | 4 | MIDDLE EAST |
call get_key_name_values('nation',5,10);
| KEY | NAME | | — |
– | | 5 | ETHIOPIA | | 6 | FRANCE | | 7 | GERMANY | | 8 | INDIA | | 9 | INDONESIA | | 10 | IRAN |
call get_key_name_values('part',100,300);
玄貓認為，動態 SQL 提供了一種在儲存程式中執行各種 SQL 陳述式的靈活方式，特別是在需要根據輸入引數建構查詢時。透過使用 execute immediate，開發者可以建立更通用和可重複使用的儲存程式，以滿足不同的需求。
總之，本文探討了 Snowflake 中查詢儲存程式結果集的兩種方法，並深入研究了動態 SQL 的使用。這些技術為開發者提供了強大的工具，可以更有效地利用 Snowflake 的功能。
OK，我將扮演玄貓（BlackCat），一位擁有國際技術經驗的台灣頂尖技術專家與作者，為你重寫並最佳化提供的技術內容。我會嚴格遵守所有規則，確保輸出內容的原創性、技術深度和台灣本地化風格。
### 透過動態 SQL 玩轉 Snowflake 儲存程式
如同你可能想像的，在執行階段動態地建構和執行 SQL 陳述式開啟了許多可能性。
#### 測驗你的知識
以下練習旨在測試你對儲存程式的理解程度。請參閱附錄 B 中的「第 16 章」以取得解答。
**練習 16-1**
將以下匿名指令碼轉換為名為 `rectangle_area()` 的儲存程式，並帶有數值引數 `p_width` 和 `p_length`：
```sql
declare
v_width number := 5;
v_height number := 8;
begin
return v_width * v_height;
end;
解密：
這段程式碼展示瞭如何將一個簡單的匿名 SQL 指令碼轉換為 Snowflake 中的儲存程式。這個儲存程式接受兩個數值引數，p_width 和 p_length，並傳回它們的乘積，即矩形的面積。
練習 16-2
編寫一個名為 get_parts_by_type() 的儲存程式，並帶有一個引數 p_type_name（型別為 varchar）。該儲存程式應傳回一個結果集，其中包含 Part 表格中的 p_partkey 和 p_name 欄位，針對 p_type 欄位符合引數值的列。使用引數值 ‘SMALL PLATED NICKEL’ 或 ‘PROMO BRUSHED STEEL’ 呼叫該程式，兩者都應傳回 23 列。
表函式：Snowflake 資料存取的瑞士刀
本章將探討表函式，它提供了一種靈活的方式來存取你的 Snowflake 資料。
使用者定義函式（UDF）
在第 16 章中，你學習瞭如何使用 Snowflake Scripting 建立儲存程式，並且玄貓指出，即使儲存程式可以傳回結果集，它們也無法從 SQL 陳述式中呼叫。幸運的是，Snowflake 也允許你使用 create function 陳述式建立使用者定義函式。使用者定義函式（也稱為 UDF）可以從 SQL 陳述式中呼叫，但與儲存程式不同，它們僅限於傳回單一表達式，並且無法充分利用 Snowflake Scripting 語言。UDF 可以用 Python、Java、JavaScript、Scala 和 SQL 編寫，本章將重點介紹根據 SQL 的 UDF。
首先，玄貓將採用簡單的儲存程式 rectangle_area()，它是第 16 章其中一個練習的重點，並將其轉換為儲存函式。以下是儲存程式的樣子：
create procedure rectangle_area(p_width number, p_length number)
returns number
language sql
as
begin
return p_width * p_length;
end;
解密：
這段程式碼展示瞭如何建立一個名為 rectangle_area 的儲存程式，它接受兩個引數 p_width 和 p_length，並傳回它們的乘積。returns number 子句指定了儲存程式傳回值的資料型別。
這個儲存程式僅傳回兩個數值引數的乘積。以下是它作為名為 fn_rectangle_area() 的 UDF 的樣子：
create function fn_rectangle_area(p_width number, p_length number)
returns number
language sql
as
'p_width * p_length';
解密：
這段程式碼展示瞭如何建立一個名為 fn_rectangle_area 的使用者定義函式（UDF），它接受兩個引數 p_width 和 p_length，並傳回它們的乘積。與儲存程式不同，UDF 的主體是一個簡單的表示式 'p_width * p_length'，它定義了函式的傳回值。
該函式指定一個表示式，在本例中為 p_width * p_length。不允許迴圈、條件邏輯或例外處理。但是，可以從查詢中呼叫該函式：
PUBLIC>select rect.x as width, rect.y as length,
fn_rectangle_area(rect.x, rect.y) as area
from (values (2, 6), (4, 7), (8, 12)) as rect(x,y);
+
---
-
---
+
---
-
---
-+
---
---
+
| WIDTH | LENGTH | AREA |
|
---
-
---
+
---
-
---
-+
---
---
|
| 2 | 6 | 12 |
| 4 | 7 | 28 |
| 8 | 12 | 96 |
+
---
-
---
+
---
-
---
-+
---
---
+
解密：
這段 SQL 程式碼示範瞭如何呼叫剛剛建立的 UDF fn_rectangle_area。它首先建立一個名為 rect 的臨時表格，其中包含 x 和 y 兩欄，分別代表矩形的寬度和長度。然後，它使用 SELECT 陳述式從 rect 表格中選取寬度、長度以及透過呼叫 fn_rectangle_area(rect.x, rect.y) 計算出的面積。
像這樣的 UDF 被稱為純量使用者定義函式，因為它傳回單一值。Snowflake 還允許你建立傳回結果集的表格使用者定義函式，這將是本章的重點。
什麼是表函式？
表函式是一種使用者定義函式，它傳回結果集，因此可以包含在查詢的 from 子句中，並且其他表格甚至其他表函式聯結。必須使用 create function 命令產生表函式，並且必須包含 returns table 子句，指定傳回的欄位的名稱和型別。此外，表函式的主體必須包含單一查詢，並且不能使用 Snowflake Scripting 語言的其他功能，例如指令碼區塊和迴圈。
雖然這可能看起來像是一個嚴重的限制，但表函式可以在你的資料存取策略中扮演關鍵角色。例如，如果你的組織中有許多人從 Snowflake 資料建立報表和儀錶板，你可以建立一組表函式來保護他們免受複雜的 SQL 陳述式的影響，並可能避免在此過程中產生昂貴的效能問題。將其推向極致，你可以強制所有資料函式庫使用者透過一組儲存程式和表函式（也稱為資料存取層）與你的資料函式庫互動，而不是允許直接存取表格。
編寫你自己的表函式
首先，讓玄貓使用上一章中 ins_country_monthly_sales() 程式中的 select 陳述式來建立一個表函式。以下是查詢的樣子：
告別傳統：用 Snowflake 表函式提升資料分析效率
在資料分析的日常中，我們經常需要針對特定條件篩選、彙總資料。如果這些邏輯需要重複使用，你會怎麼做？傳統上，我們可能會選擇建立預存程式（Stored Procedure）。但今天，玄貓想介紹一個更優雅、更強大的選擇：Snowflake 的表函式（Table Function）。
表函式是什麼？為何玄貓推薦它？
簡單來說，表函式就像一個可以回傳「表格」的函式。這表示，你可以像使用一般資料表一樣，對表函式的結果進行查詢、篩選、連線等操作。
玄貓認為，表函式相較於預存程式，有以下優勢：
- 更高的靈活性： 表函式可以像資料表一樣，與其他資料表進行 JOIN 操作，也能使用 WHERE、GROUP BY、ORDER BY 等子句。
- 更好的可讀性： 表函式的定義更簡潔，更容易理解其功能。
- 更強大的組合性： 表函式可以像 Correlated Subquery 一樣，根據外部資料表的值動態執行多次。
從 SQL 查詢到表函式：一個例項
讓玄貓用一個實際的例子來說明。假設我們想要查詢特定區域、特定年份的各國每月銷售額。傳統的 SQL 查詢可能會是這樣：
select
date_part(year, o.o_orderdate) as sales_year,
date_part(month, o_orderdate) as sales_month,
r.r_name as region_name,
n.n_name as country_name,
sum(o.o_totalprice) as total_sales
from snowflake_sample_data.tpch_sf1.orders as o
inner join snowflake_sample_data.tpch_sf1.customer as c
on o.o_custkey = c.c_custkey
inner join snowflake_sample_data.tpch_sf1.nation as n
on c.c_nationkey = n.n_nationkey
inner join snowflake_sample_data.tpch_sf1.region as r
on n.n_regionkey = r.r_regionkey
where r.r_name = :p_region_name
and date_part(year, o.o_orderdate) = :p_year
group by
date_part(year, o.o_orderdate),
date_part(month, o_orderdate),
r.r_name,
n.n_name
現在，讓我們把它改寫成表函式：
create function get_country_monthly_sales(
p_region_name varchar,
p_year number
)
returns table(
sales_year number,
sales_month number,
region_name varchar,
country_name varchar,
total_sales number
)
language sql
as
'select
date_part(year, o.o_orderdate) as sales_year,
date_part(month, o_orderdate) as sales_month,
r.r_name as region_name,
n.n_name as country_name,
sum(o.o_totalprice) as total_sales
from snowflake_sample_data.tpch_sf1.orders as o
inner join snowflake_sample_data.tpch_sf1.customer as c
on o.o_custkey = c.c_custkey
inner join snowflake_sample_data.tpch_sf1.nation as n
on c.c_nationkey = n.n_nationkey
inner join snowflake_sample_data.tpch_sf1.region as r
on n.n_regionkey = r.r_regionkey
where r.r_name = p_region_name
and date_part(year, o.o_orderdate) = p_year
group by
date_part(year, o.o_orderdate),
date_part(month, o_orderdate),
r.r_name,
n.n_name';
程式碼解密
create function get_country_monthly_sales(...): 定義一個名為get_country_monthly_sales的函式。p_region_name varchar, p_year number: 宣告函式的輸入引數，分別是區網域名稱（字串）和年份（數字）。returns table(...): 定義函式的回傳值是一個表格，並宣告表格的欄位名稱和資料型別。language sql: 指定函式使用的語言是 SQL。as '...': 將原本的 SQL 查詢語法包在單引號內，作為函式的主體。
可以看到，除了將原本的 SQL 查詢包裝起來，並定義輸入、輸出格式外，幾乎沒有做任何修改。
如何使用表函式？
使用表函式就像使用一般的資料表一樣。例如，要查詢歐洲（EUROPE）1997 年的每月銷售額，可以這樣寫：
select sales_month, country_name, total_sales
from table(get_country_monthly_sales('EUROPE',1997));
結果
| SALES_MONTH | COUNTRY_NAME | TOTAL_SALES | | :
- | :
| :
- | | 11 | GERMANY | 98033670 | | 7 | ROMANIA | 116342292 | | 6 | UNITED KINGDOM | 108143171 | | 2 | UNITED KINGDOM | 107067404 | | 2 | RUSSIA | 105272625 | | 10 | FRANCE | 117884280 | | 8 | GERMANY | 106333217 | | 12 | RUSSIA | 117657079 | | … | … | … | | 5 | ROMANIA | 110093370 | | 6 | RUSSIA | 116585712 | | 6 | ROMANIA | 119433165 | | 3 | ROMANIA | 120104796 | | 11 | UNITED KINGDOM | 112680947 | | 1 | ROMANIA | 123156412 | | 10 | ROMANIA | 118360369 |
你還可以加上 WHERE、GROUP BY、ORDER BY 等子句，讓查詢更靈活：
select cms.sales_month, cms.country_name, cms.total_sales
from table(get_country_monthly_sales('EUROPE',1997)) as cms
where cms.sales_month = 8
order by 2;
結果
| SALES_MONTH | COUNTRY_NAME | TOTAL_SALES | | :
- | :
| :
- | | 8 | FRANCE | 125059627 | | 8 | GERMANY | 106333217 | | 8 | ROMANIA | 119066485 | | 8 | RUSSIA | 118034351 | | 8 | UNITED KINGDOM | 109899097 |
甚至，你還可以將表函式的結果與其他資料表進行 JOIN：
select cms.sales_month, cms.country_name, cms.total_sales,
n.n_nationkey
from table(get_country_monthly_sales('EUROPE',1997)) as cms
inner join nation n on cms.country_name = n.n_name
where cms.sales_month = 8
order by 2;
結果
| SALES_MONTH | COUNTRY_NAME | TOTAL_SALES | N_NATIONKEY | | :
- | :
| :
- | :
- | | 8 | FRANCE | 125059627 | 6 | | 8 | GERMANY | 106333217 | 7 | | 8 | ROMANIA | 119066485 | 19 | | 8 | RUSSIA | 118034351 | 22 | | 8 | UNITED KINGDOM | 109899097 | 23 |
表函式的進階應用：Correlated Subquery
表函式最有趣的地方在於，它可以像 Correlated Subquery 一樣，根據外部資料表的值動態執行多次。
例如，以下查詢會針對 region 資料表中的每一個區域，都呼叫一次 get_country_monthly_sales() 表函式：
select cms.sales_month, cms.country_name, cms.total_sales,
r.r_name as region_name
from region r
cross join
table(get_country_monthly_sales(r.r_name,1997)) as cms
order by 2;
結果
| SALES_MONTH | COUNTRY_NAME | TOTAL_SALES | REGION_NAME | | :
- | :
| :
- | :
- | | 4 | ALGERIA | 114709091 | AFRICA | | 3 | ALGERIA | 111769639 | AFRICA | | 1 | ALGERIA | 112621466 | AFRICA | | 8 | ALGERIA | 122789085 | AFRICA | | 7 | ALGERIA | 105171743 | AFRICA | | 9 | ALGERIA | 110396345 | AFRICA | | 12 | ALGERIA | 118305298 | AFRICA | | 5 | ALGERIA | 129927011 | AFRICA | | … | … | … | … | | 9 | VIETNAM | 111210164 | ASIA | | 12 | VIETNAM | 122431346 | ASIA | | 2 | VIETNAM | 109812362 | ASIA | | 6 | VIETNAM | 118923739 | ASIA | | 5 | VIETNAM | 114240789 | ASIA | | 7 | VIETNAM | 118306908 | ASIA | | 8 | VIETNAM | 116167259 | ASIA | | 1 | VIETNAM | 111268444 | ASIA |
在這個例子中，region 資料表有 5 筆資料，因此 get_country_monthly_sales() 表函式會被呼叫 5 次。由於每個區域有 25 個國家，每個國家有 12 個月的資料，因此最終結果會有 300 筆資料。
這種做法的好處是，你可以建立一個表函式來回傳特定業務邏輯的資料，然後根據不同的輸入引數，動態地產生不同的結果。
總而言之，Snowflake 的表函式是一個非常強大的工具，可以幫助你更有效率地進行資料分析。下次當你需要重複使用某些資料查詢邏輯時，不妨考慮使用表函式，讓你的 SQL 更簡潔、更靈活！
Snowflake內建表函式：資料生成與行扁平化
Snowflake 提供了多種內建表函式，讓資料處理更具彈性。玄貓將探討如何使用這些函式來生成測試資料、處理字串，以及扁平化 JSON 檔案。
資料生成：generator()
有時，我們需要建立測試資料或生成特定格式的資料，例如一整年的日期。generator() 函式就是為此而生。它能產生指定數量或時間範圍內的資料列。
以下範例展示如何使用 generator() 產生 2023 年每一天的日期：
PUBLIC> SELECT DATEADD(DAY, SEQ4() + 1, '2022-12-31'::DATE) cal_date
FROM TABLE(GENERATOR(ROWCOUNT => 365));
程式碼解密
DATEADD(DAY, SEQ4() + 1, '2022-12-31'::DATE):DATEADD函式將天數加到指定日期。SEQ4()產生從 0 開始的序列，每天都會加 1。'2022-12-31'::DATE定義起始日期為 2022 年 12 月 31 日。TABLE(GENERATOR(ROWCOUNT => 365)):GENERATOR(ROWCOUNT => 365)產生 365 列資料，TABLE()函式將其轉換為可查詢的表。
seq4() 函式產生從零開始的序列，用於從 2023 年 1 月 1 日到 2023 年 12 月 31 日生成一組日期。
若需要建構一組測試資料，可以使用 uniform()、random() 和 randstr() 函式來建立隨機資料值：
PUBLIC> SELECT UNIFORM(1, 9999, RANDOM()) rnd_num,
RANDSTR(10, RANDOM()) rnd_str
FROM TABLE(GENERATOR(ROWCOUNT => 10));
程式碼解密
UNIFORM(1, 9999, RANDOM()) rnd_num:UNIFORM函式產生指定範圍內的均勻分佈隨機數。這裡產生 1 到 9999 之間的隨機整數。RANDOM()函式用於提供隨機數種子。RANDSTR(10, RANDOM()) rnd_str:RANDSTR函式產生指定長度的隨機字串。這裡產生長度為 10 的隨機字串。RANDOM()函式用於提供隨機數種子。TABLE(GENERATOR(ROWCOUNT => 10)):GENERATOR(ROWCOUNT => 10)產生 10 列資料，TABLE()函式將其轉換為可查詢的表。
這個範例會產生 10 個介於 1 到 9999 之間的隨機數字，以及隨機的 10 字元字串。
行扁平化：split_to_table() 和 flatten()
Snowflake 提供了多個表函式，可用於將字串或檔案中的一組值扁平化為一組列。以下是如何使用 split_to_table() 函式來扁平化一組以管道分隔的字串：
PUBLIC> SELECT INDEX, VALUE
FROM TABLE(SPLIT_TO_TABLE('a1|b2|c3', '|'));
程式碼解密
SPLIT_TO_TABLE('a1|b2|c3', '|'):SPLIT_TO_TABLE函式將字串'a1|b2|c3'以'|'作為分隔符號分割成多列。TABLE(...):TABLE()函式將分割後的結果轉換為可查詢的表。SELECT INDEX, VALUE: 選取分割後的每一列的索引和值。
split_to_table() 函式在需要解析字串時非常方便。
如果需要處理檔案，則需要更複雜的工具。首先，讓玄貓結合 Snowflake 的 object_construct() 和 array_agg() 函式來產生一個包含 Employee 表內容的簡單 JSON 檔案：
PUBLIC> SELECT OBJECT_CONSTRUCT(
'employees', ARRAY_AGG(
OBJECT_CONSTRUCT(
'empid', empid,
'name', emp_name))) emp_doc
FROM employee;
程式碼解密
OBJECT_CONSTRUCT('empid', empid, 'name', emp_name):OBJECT_CONSTRUCT函式建立包含員工 ID 和姓名的 JSON 物件。ARRAY_AGG(...):ARRAY_AGG函式將所有員工的 JSON 物件聚合成一個 JSON 陣列。OBJECT_CONSTRUCT('employees', ...):OBJECT_CONSTRUCT函式建立一個包含名為'employees'的 JSON 陣列的 JSON 物件。FROM employee: 指定從employee表中選取資料。
這個查詢會產生一個包含 Employee 表中所有成員的 ID 和姓名的單一檔案。如果需要將此檔案轉換回一組列和欄，可以使用 Snowflake 的 flatten() 表函式。玄貓可以使用此檔案作為 flatten() 函式的輸入，該函式與 table() 函式一起使用時，將傳回七列，每列對應一位員工：
PUBLIC> SELECT VALUE
FROM TABLE(FLATTEN(INPUT => PARSE_JSON(
'{"employees": [
{"empid": 1001, "name": "Bob Smith"},
{"empid": 1002, "name": "Susan Jackson"},
{"empid": 1003, "name": "Greg Carpenter"},
{"empid": 1004, "name": "Robert Butler"},
{"empid": 1005, "name": "Kim Josephs"},
{"empid": 1006, "name": "John Tyler"},
{"empid": 1007, "name": "John Sanford"}
]}'), PATH => 'employees'));
程式碼解密
PARSE_JSON(...):PARSE_JSON函式將 JSON 字串解析為 JSON 物件。FLATTEN(INPUT => ..., PATH => 'employees'):FLATTEN函式將 JSON 物件中的'employees'陣列扁平化為多列。TABLE(...):TABLE()函式將扁平化後的結果轉換為可查詢的表。SELECT VALUE: 選取扁平化後的每一列的值，即每個員工的 JSON 物件。
總之，Snowflake 內建的表函式提供了強大的資料處理能力。無論是生成測試資料、解析字串還是扁平化 JSON 檔案，這些函式都能簡化操作，提高效率。玄貓建議大家多多探索這些工具，讓資料處理工作更加得心應手。