Pentaho ETL 實作技巧：SQL 動態內容引用 Table input & Execute SQL scripts

Pentaho 技術專欄 2023 年 11 月 17 日 最後更新時間 : 2024 年 3 月 13 日

Linden 資料工程師歐立威科技

內容目錄

對於使用 Pentaho（或者類似產品 Kettle / Hop）這類 ETL 工具的開發人員來說，總有一些藏在角落、鮮為人知的方便功能。筆者在此講解有關 Pentaho 流程中，使用 Table input 與 Execute SQL scripts 兩種流程步驟的動態內容引用。

什麼是動態內容?

儘管 Pentaho 各項流程步驟中，Table input 與 Execute SQL script 都能填寫 SQL 語句，固定條件值卻需要 ETL 流程設計者隨時調整設定值，不僅降低效率、還有發生異常的風險；SQL 語句設定「動態內容」後，流程將變成函式（function）結構，替代手動調整，透過使用者輸入內容、上游步驟查詢資料獲得輸出結果，效率更高、不容易出錯。

由於各家資料庫產品特性不完全相同，Pentaho 也有提供兩種模式，將「動態內容」引入 SQL 查詢語句當中；本文將使用「變數」和「參數」區分，詳細說明這些引入做法。

定義「變數」與「參數」

解說做法之前，為避免使用 Pentaho 介面帶來的名詞混淆，在此定義 Pentaho 應用範圍內，「變數」和「參數」的不同之處：

變數 (variables)

通過流程步驟 Set variables 與 Get variables 載入的 SQL 動態內容，稱為「變數」。「變數」可以在 Transformation / Job 流程的屬性 (Properties) 設定中，利用「Parameters」區域進行定義（如欄位名稱、預設值、欄位說明等）

※在畫面空白處可用滑鼠右鍵（或左鍵連續點擊）點選「Properties…」選項進入屬性設定（上圖紅框處），選擇「Parameters」區域即可定義「變數」（下圖）

「變數」的利用方法有二：

呼叫 Transformation / Job 流程時指定內容

利用流程步驟 Set variables 與 Get variables 指定流程中產生的目標內容

受到 Pentaho 當中 Transformation 流程特性[1]影響，所有步驟「幾乎同時執行」，當一個 Transformation 設定「變數」後，該流程無法使用「變數」，必須透過 Job 流程傳送到另一個 Transformation，才能利用先前設定的「變數」。

※Set variables 步驟確認離開時，會出現提示：同一 Transformation 內無法使用新增「變數」

參數（parameters）

通過 Transformation 流程中繼資料欄位 (fields) 取用的 SQL 動態內容，稱為「參數」；「參數」出現在 Spoon 執行流程後、執行結果（Execution Results）的 Preview data 頁籤：

※ （圖A）Pentaho 流程執行後，下方的執行結果可選 Preview data 查看不同步驟的「參數」呈現

和「變數」不同的是，「參數」可以直接在同一 Transformation 當中進行利用，由於「參數」本質仍是 ETL 流程的中繼資料，故經過其它步驟，內容仍會因為篩選而變化（如 Select values、Filter rows）或者遺失（如 Table input）。

※ （圖B）相對於圖 A，「參數」經過流程步驟 Select values，欄位可能減少或更換名稱

（圖C）相對於圖 A，「參數」經過流程步驟 Filter rows，受到篩選條件影響、產生資料列數量變化

※ （圖D）相對於圖 A，「參數」經過流程步驟 Execute SQL scripts，由於該項步驟不輸出內容，故不會變化

※ （圖E）相對於圖 A，「參數」經過流程步驟 Table input，由於該項步驟必定輸出內容，故內容被步驟輸出替代

「變數」文字替代

變數文字替代（Variable substitution）需要在 SQL 語法中填寫 ${variable} 標記[2]，括號內名稱自訂，以流程屬性定義為主，且字串內容必須追加單引號；之所以叫做「文字替代」，是因為動態內容會直接替代變數標記，才送出語法執行。

勾選選項「Replace variables in script?」啟用變數文字替代[3]

SQL 語法動態內容位置填寫「${variable}」標記（字串內容必須添加單引號，數值、布林值除外）

Execute SQL script 變數文字替代方法

勾選選項「Variable substitution」啟用變數文字替代[4]

SQL語法動態內容位置填寫「${variable}」標記（字串內容必須添加單引號，數值、布林值除外）

「參數」綁定

相對於變數文字替代，參數綁定（Bind parameters）只要在SQL語法中填寫「?」標記即可，不過需要「參數」的數量、順序，必須與語法結構保持一致。

以效能角度比較，變數文字替代產生的SQL語法，儘管架構相同，資料庫掃描語法時，卻因為動態內容「一字之差」，視為不同的查詢，每次查詢便重做執行計畫，屬於美中不足；

參數綁定產生的 SQL 語法不同，語法、動態內容分開送出，資料庫掃描後，即可套用首次掃描的執行計畫，才引入動態內容，大幅增進查詢效能。實際設計流程時，流程步驟 Table input 與 Execute SQL script 對「參數」的影響也有所不同：
前者會傳回查詢結果、替代「參數」欄位，後者則保持原樣，「參數」可以繼續利用[5]。

Table input 參數綁定方法

屬性「Insert data from step」選擇流程上一步驟，啟用參數綁定[6]

勾選選項「Execute for each row?」確保動態內容通過「資料列逐一輸入」執行

SQL語法動態內容位置填寫「?」標記即可
- 注意：由於參數綁定對數量、順序要求，上一步驟應使用Select values進行欄位排序、複製與重新命名

Execute SQL script參數綁定方法

先後勾選選項「Execute for each row?」以及「Bind parameters?」，啟用參數綁定[7]（必須勾選「Execute for each row?」，選項「Bind parameters?」才能操作）

SQL語法動態內容位置填寫 ? 標記即可，參數可在左下角”Parameters」表格填寫（須按照標記順序；欄位可重複填寫）

「參數」綁定SQL語法的限制

利用參數綁定執行 SQL 腳本時，可能遭遇下面的狀況：

腳本存在多段分號間隔語法

儘管參數綁定可以增進查詢效能，但不代表可以處理結構複雜的腳本。
如果有一段SQL腳本具有下面的結構：

“

UPDATE… SET… WHERE <columnA> = ? AND <columnB> = ? ;

SELECT… FROM… WHERE <columnA> = ? AND <columnB> = ? ;

“

按照前述解說，參數可能按照「ABAB」格式做綁定、順利完成作業，然而實際執行流程後，Pentaho 可能會回覆異常訊息：

“

<data type>：Unable to set value on prepared statement on index 4.

欄位索引超過許可範圍：3，欄位數：2。

“

或者

“

Error setting value #1 <data type> on prepared statement

欄位索引超過許可範圍：1，欄位數：0。

“

這是因為 Execute SQL scripts 步驟只會執行分號間隔最後一段語法，如果指派參數數量超過最後一段語法指定數量，或者最後一段語法沒有指定參數，就會出現上述異常訊息。

腳本需要利用暫存表 (temp table)

腳本內容在 Pentaho 執行時，可能遭遇效能較差，需要利用暫存表的情形。需要注意的是，前述 Transformation 流程特性當中，不只所有步驟「幾乎同時執行」，而且是各個步驟「執行完畢即釋放資源」。

因此，不僅建置暫存表、查詢暫存表的語法無法寫在同一步驟，即使分開執行，由於ETL步驟的工作階段(session)完成後馬上結束，暫存表建立完成的當下，也會馬上被消滅[8]。