Snowflake動態SQL與資料表函式應用

– 7 分鐘閱讀

Snowflake 提供了強大的動態SQL和資料表函式功能，讓開發者能更彈性地操作資料。本文將介紹如何使用 RESULT_SCAN() 函式從儲存程式中檢索結果集，並示範如何利用 EXECUTE IMMEDIATE 陳述式執行動態產生的SQL查詢。此外，我們也將探討如何建立和使用資料表函式，將複雜的SQL邏輯封裝成可重複使用的模組，進一步提升查詢效率和程式碼可讀性。這些技術都能有效簡化資料函式庫操作，提升開發效率。

動態SQL與查詢結果處理

Snowflake 提供兩種機制來查詢由儲存程式傳回的結果集。首先，我們來探討如何利用 result_scan() 函式來檢索結果集。

使用 result_scan() 函式檢索結果集

當一個儲存程式傳回結果集時，可以立即使用 result_scan() 函式來查詢該結果集。以下是一個範例：

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

這個查詢首先透過 LAST_QUERY_ID() 取得最近執行的查詢ID，然後呼叫 RESULT_SCAN() 函式來檢索與該查詢ID相關聯的結果集，最後透過 TABLE() 函式使其能夠在查詢的 FROM 子句中被參照。

此圖示

圖表翻譯： 此圖示展示了使用 result_scan() 函式檢索結果集的流程。首先，透過 LAST_QUERY_ID() 取得最近執行的查詢ID，然後使用 RESULT_SCAN() 函式檢索結果集，接著透過 TABLE() 函式處理結果集，最後進行查詢。

動態SQL

Snowflake 的 Scripting 語法包括用於條件邏輯、迴圈和例外處理的陳述式，以及 SELECT、UPDATE、INSERT、DELETE 和 MERGE 陳述式。然而，在某些情況下，您可能需要執行一個動態生成的查詢，例如根據呼叫者傳遞的引數值建構的查詢。

使用 EXECUTE IMMEDIATE 執行動態SQL

Snowflake 提供了 EXECUTE IMMEDIATE 陳述式，允許您執行一個包含有效SQL陳述式或指令碼塊的字串。

建立動態查詢的儲存程式

以下是一個範例，展示如何建立一個儲存程式，該程式根據輸入引數動態生成查詢並執行：

CREATE PROCEDURE get_key_name_values(p_table_nm VARCHAR, p_key_start NUMBER, p_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;

內容解密：

  1. 引數接收：儲存程式接收三個引數：p_table_nm（表名）、p_key_start 和 p_key_end（鍵值的範圍）。
  2. 列名確定：透過查詢 information_schema.columns 來確定指定表的鍵列和名稱列的名稱。
  3. 動態查詢建立：根據輸入引數和取得的列名，動態生成一個SQL查詢字串。
  4. 執行查詢：使用 EXECUTE IMMEDIATE 陳述式執行動態生成的查詢，並將結果儲存在 v_results 中。
  5. 傳回結果：將結果集傳回給呼叫者。

範例用法

您可以透過以下方式呼叫儲存程式：

CALL get_key_name_values('region', NULL, NULL);
CALL get_key_name_values('nation', 5, 10);
CALL get_key_name_values('part', 100, 300);

這些呼叫將分別傳回 ‘region’、’nation’ 和 ‘part’ 表中指定範圍內的鍵/名稱值。

此圖示

圖表翻譯： 此圖示展示了呼叫儲存程式 get_key_name_values() 的流程，包括動態生成查詢、執行查詢以及傳回結果集的步驟。

資料函式庫程式設計：預存程式與表格函式

在資料函式倉管理系統中，預存程式（Stored Procedure）與表格函式（Table Function）是兩種重要的程式設計工具，用於封裝複雜的SQL邏輯並提供可重複使用的功能。本文將探討這兩種技術，並介紹如何在Snowflake環境中使用它們。

預存程式的基礎

預存程式是一種可執行的SQL程式，可以接受引數並傳回結果集。在Snowflake中，預存程式可以使用Snowflake Scripting語言編寫。以下是一個簡單的預存程式範例，用於計算矩形的面積：

CREATE PROCEDURE rectangle_area(p_width NUMBER, p_length NUMBER)
RETURNS NUMBER
LANGUAGE SQL
AS
BEGIN
    RETURN p_width * p_length;
END;

內容解密：

  • CREATE PROCEDURE：用於建立預存程式的語法。
  • p_width 和 p_length：作為輸入引數，代表矩形的寬度和長度。
  • RETURNS NUMBER：指定預存程式的傳回值型別為數字。
  • LANGUAGE SQL：表示預存程式使用SQL語言編寫。
  • BEGIN ... END：定義預存程式的執行體，其中包含傳回寬度和長度乘積的邏輯。

呼叫預存程式

呼叫上述預存程式的方法如下：

CALL rectangle_area(5, 8);

此呼叫將傳回矩形的面積，即 5 * 8 = 40。

表格函式的介紹

表格函式是一種特殊的使用者自定義函式（User-Defined Function, UDF），它可以傳回一個結果集，並可在SQL查詢的FROM子句中使用。與預存程式不同，表格函式可以直接在SQL查詢中使用，提供更大的靈活性。

以下是一個將預存程式 rectangle_area() 轉換為表格函式 fn_rectangle_area() 的範例：

CREATE FUNCTION fn_rectangle_area(p_width NUMBER, p_length NUMBER)
RETURNS NUMBER
LANGUAGE SQL
AS
'p_width * p_length';

內容解密：

  • CREATE FUNCTION：用於建立使用者自定義函式的語法。
  • RETURNS NUMBER：指定函式傳回值的型別為數字。
  • LANGUAGE SQL：表示函式使用SQL語言編寫。
  • 'p_width * p_length'：定義函式的傳回值表示式，直接計算並傳回寬度和長度的乘積。

在查詢中使用表格函式

表格函式可以在查詢中像普通表格一樣使用，如下所示：

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

內容解密：

  • SELECT：選擇需要的欄位，包括寬度、長度和麵積。
  • rect.x 和 rect.y：分別代表矩形的寬度和長度。
  • fn_rectangle_area(rect.x, rect.y)：呼叫表格函式計算面積。
  • (VALUES (2, 6), (4, 7), (8, 12)) AS rect(x,y)：模擬一個包含多個矩形資料的虛擬表格。

自定義表格函式

自定義表格函式可以封裝複雜的查詢邏輯，並在多個查詢中重複使用。以下是一個範例，展示如何建立一個傳回特定型別零件資訊的表格函式：

CREATE FUNCTION get_parts_by_type(p_type_name VARCHAR)
RETURNS TABLE (p_partkey NUMBER, p_name VARCHAR)
LANGUAGE SQL
AS
'SELECT p_partkey, p_name FROM Part WHERE p_type = p_type_name';

內容解密：

  • RETURNS TABLE：指定函式傳回一個表格，包含 p_partkey 和 p_name 兩個欄位。
  • SELECT p_partkey, p_name FROM Part WHERE p_type = p_type_name：查詢 Part 表格中符合特定零件型別的記錄。

資料表函式的建立與應用

在 Snowflake 中，資料表函式（Table Function）是一種強大的工具，能夠讓使用者自定義傳回表格格式資料的函式。本文將探討如何建立和使用資料表函式，以及其在實際查詢中的靈活應用。

建立資料表函式

首先，我們來看一個具體的例子。假設我們有一個查詢，用於計算特定地區在特定年份的每月銷售額：

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 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.o_orderdate),
  r.r_name,
  n.n_name;

將查詢轉換為資料表函式

要將上述查詢轉換為資料表函式，只需定義傳回的欄位名稱和型別，並將查詢陳述式包裝在 create function 陳述式中：

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.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.o_orderdate),
  r.r_name,
  n.n_name;
$$

資料表函式的使用

建立資料表函式後，我們可以像使用普通表格一樣查詢它：

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

我們還可以對資料表函式的結果進行進一步的篩選、排序和聯接操作：

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;

資料表函式的高階應用

資料表函式不僅可以像普通表格一樣使用，還可以像相關子查詢一樣，根據輸入引數動態執行。這使得資料表函式能夠與其他表格進行交叉聯接，從而實作更複雜的查詢邏輯。

例如，我們可以使用 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;

在這個例子中，資料表函式 get_country_monthly_sales 將為 Region 表中的每個地區名稱呼叫一次，傳回每個地區在指定年份的每月銷售資料。

圖表示例：資料表函式的呼叫過程

此圖示呈現了資料表函式如何與 Region 表進行交叉聯接，並動態傳回銷售資料。

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Snowflake動態SQL與資料表函式應用

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示展示了 Region 表與資料表函式 get_country_monthly_sales 之間的交叉聯接過程，以及最終傳回的銷售資料結果集。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。