增量 PDT

在 Looker 中，永久衍生資料表 (PDT) 會寫入資料庫的臨時結構定義。Looker 會根據 持續策略保留並重建 PDT。根據預設，系統會在觸發 PDT 重建作業時，重建整個資料表。

累加型 PDT 是指 Looker 將新資料附加至資料表，而非重新建構整個資料表：

如果方言支援增量 PDT，您可以將下列類型的 PDT 轉換為增量 PDT：

• 匯總資料表
• 以 LookML 為基礎 (原生) 的 PDT
• 以 SQL 為基礎的 PDT

首次對永久累加型衍生資料表執行查詢時，Looker 會建構整個 PDT 來取得初始資料。如果資料表很大，初始建構作業可能需要相當長的時間，就像建構任何大型資料表一樣。建構初始資料表後，後續建構作業會是累加型，如果永久累加型衍生資料表設定得當，所需時間就會較短。

請注意，永久累加型衍生資料表有下列限制：

• 只有使用觸發式持續策略 (datagroup_trigger、sql_trigger_value 或 interval_trigger) 的 PDT 支援永久累加型 PDT。使用 persist_for 持續策略的 PDT 不支援永久累加型 PDT。
• 如果是以 SQL 為基礎的 PDT，必須使用 sql 參數定義資料表查詢，才能做為遞增 PDT。以 sql_create 參數或 create_process 參數定義的以 SQL 為基礎 PDT，無法遞增建構。如本頁的「範例 1」所示，Looker 會使用 INSERT 或 MERGE 指令，為遞增 PDT 建立遞增量。衍生資料表無法使用自訂資料定義語言 (DDL) 陳述式定義，因為 Looker 無法判斷建立準確遞增量所需的 DDL 陳述式。
• 增量 PDT 的來源資料表必須針對時間查詢進行最佳化。具體來說，用於遞增鍵的時間型資料欄必須採用最佳化策略，例如分割、排序鍵、索引，或是方言支援的任何最佳化策略。強烈建議您最佳化來源資料表，因為每次更新增量資料表時，Looker 都會查詢來源資料表，判斷用於增量鍵的時間型資料欄最新值。如果來源資料表未針對這些查詢進行最佳化，Looker 查詢最新值時可能會耗費大量時間和資源。

定義增量 PDT

您可以使用下列參數，將 PDT 設為累加 PDT：

• increment_key (將 PDT 設為增量 PDT 時必須使用)：定義應查詢新記錄的時間範圍。
• {% incrementcondition %} Liquid 篩選器 (將以 SQL 為基礎的衍生資料表設為永久累加型衍生資料表時必須使用，不適用於以 LookML 為基礎的衍生資料表)：將遞增鍵連結至遞增鍵所依據的資料庫時間資料欄。詳情請參閱 increment_key 說明文件頁面。
• increment_offset (選用)：整數，用於定義每個增量建構作業重建的先前時間週期數 (以增量鍵的精細度為準)。如果資料延遲送達，increment_offset 參數就很有用，因為先前時間週期可能會有新資料，而這些資料在最初建構對應增量並附加至 PDT 時並未納入。

如需範例，瞭解如何從永久原生衍生資料表、永久 SQL 衍生資料表和匯總資料表建立增量 PDT，請參閱 increment_key 參數說明文件頁面。

以下是簡單的檢視表檔案範例，定義了以 LookML 為基礎的累加 PDT：

view: flights_lookml_incremental_pdt {
  derived_table: {
    indexes: ["id"]
    increment_key: "departure_date"
    increment_offset: 3
    datagroup_trigger: flights_default_datagroup
    distribution_style: all
    explore_source: flights {
      column: id {}
      column: carrier {}
      column: departure_date {}
    }
  }

  dimension: id {
    type: number
  }
  dimension: carrier {
    type: string
  }
   dimension: departure_date {
    type: date
  }
}

首次對這個資料表執行查詢時，系統會完整建構這個資料表。之後，系統會以一天為增量 (increment_key: departure_date)，回溯三天 (increment_offset: 3)，重新建構 PDT。

遞增鍵是以 departure_date 維度為準，而這個維度實際上是 departure 維度群組的date 時間範圍。(如要瞭解維度群組的運作方式，請參閱 dimension_group 參數說明文件頁面)。維度群組和時間範圍都是在 flights 檢視表檔案中定義，也就是這個 PDT 的 explore_source。以下說明 departure 維度群組在 flights 檢視表檔案中的定義方式：

...
  dimension_group: departure {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.dep_time ;;
  }
...

遞增參數和持續性策略的互動

PDT 的 increment_key 和 increment_offset 設定與 PDT 的持續策略無關：

• 累加 PDT 的持續性策略只會決定 PDT 的累加時間，除非觸發資料表的持續性策略，或在「探索」中透過「重建衍生資料表並執行」選項手動觸發 PDT，否則 PDT 建構工具不會修改遞增 PDT。
• 當 PDT 遞增時，PDT 建構工具會根據最新的時間增量 (由 increment_key 參數定義的時間範圍)，判斷先前將最新資料新增至資料表的時間。根據這項資訊，PDT 建構工具會將資料截斷至資料表中最近的時間增量開頭，然後從該處建構最新的增量。
• 如果 PDT 含有 increment_offset 參數，PDT 建構工具也會重建 increment_offset 參數中指定的先前時間範圍數量。系統會從最新時間增量 (由 increment_key 參數定義的時間範圍) 的開頭開始，回溯先前的時間範圍。

下列範例情境說明永久累加型衍生資料表的更新方式，並顯示 increment_key、increment_offset 和持續性策略的互動。

範例 1

這個範例使用的 PDT 具有下列屬性：

• 增量索引鍵：日期
• 增量偏移：3
• 持續性策略：每月第一天觸發一次

這份表格的更新方式如下：

• 每月持續性策略是指系統每月會自動建構一次資料表。舉例來說，6 月 1 日時，資料表中的最後一列會是 5 月 1 日新增的資料。
• 由於這個 PDT 的遞增鍵是以日期為準，PDT 建立工具會將 5 月 1 日截斷至當天開頭，並重建 5 月 1 日至 6 月 1 日 (當天) 的資料。
• 此外，這個 PDT 的增量偏移量為 3。因此，PDT 建立工具也會重建 5 月 1 日前三個時間週期 (天) 的資料。因此，系統會重建 4 月 28 日、29 日、30 日，以及 6 月 1 日當天的資料。

以 SQL 來說，PDT 建構工具會在 6 月 1 日執行下列指令，判斷應重建現有 PDT 中的哪些資料列：

## Example SQL for BigQuery:
SELECT FORMAT_TIMESTAMP('%F %T',TIMESTAMP_ADD(MAX(pdt_name),INTERVAL -3 DAY))

## Example SQL for other dialects:
SELECT CAST(DATE_ADD(MAX(pdt_name),INTERVAL -3 DAY) AS CHAR)

以下是 PDT 建構工具將在 6 月 1 日執行的 SQL 指令，用來建構最新增量：

## Example SQL for BigQuery:

MERGE INTO [pdt_name] USING (SELECT [columns]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM'))
   AS tmp_name ON FALSE
WHEN NOT MATCHED BY SOURCE AND created_date >= TIMESTAMP('4/28/21 12:00:00 AM')
   THEN DELETE
WHEN NOT MATCHED THEN INSERT [columns]

## Example SQL for other dialects:

START TRANSACTION;
DELETE FROM [pdt_name]
   WHERE created_date >= TIMESTAMP('4/28/21 12:00:00 AM');
INSERT INTO [pdt_name]
   SELECT [columns]
   FROM [source_table]
   WHERE created_at >= TIMESTAMP('4/28/21 12:00:00 AM');
COMMIT;

範例 2

這個範例使用的 PDT 具有下列屬性：

• 保留策略：每天觸發一次
• 增量索引鍵：月份
• 增量偏移：0

6 月 1 日起，這份表格的更新方式如下：

• 每日持續性策略是指系統每天會自動建構資料表一次。6 月 1 日時，表格的最後一列會是 5 月 31 日新增的資料。
• 由於增量鍵是以月份為準，PDT 建構工具會從 5 月 31 日截斷，回到當月初，並重建 5 月和當月 (包括 6 月 1 日) 的所有資料。
• 由於這個 PDT 沒有增量偏移，因此系統不會重建先前的時間週期。

6 月 2 日更新後的表格如下：

• 6 月 2 日時，表格的最後一列會是 6 月 1 日新增的資料。
• 由於 PDT 建構工具會將資料截斷至 6 月初，然後從 6 月 1 日開始重建資料，直到當天為止，因此系統只會重建 6 月 1 日和 6 月 2 日的資料。
• 由於這個 PDT 沒有增量偏移，因此系統不會重建先前的時間週期。

範例 3

這個範例使用的 PDT 具有下列屬性：

• 增量索引鍵：月份
• 增量偏移：3
• 保留策略：每天觸發一次

這個情境說明瞭累加 PDT 的設定不佳，因為這是每天觸發的 PDT，且偏移量為三個月。這表示每天至少會重建三個月的資料，累加 PDT 的使用效率會非常低。不過，這個情境很有趣，可做為瞭解累加 PDT 運作方式的範例。

6 月 1 日起，這份表格的更新方式如下：

• 每日持續性策略是指系統每天會自動建構資料表一次。舉例來說，如果今天是 6 月 1 日，表格的最後一列會在 5 月 31 日新增。
• 由於增量鍵是以月份為準，PDT 建構工具會從 5 月 31 日截斷，回到當月初，並重建 5 月和當月 (包括 6 月 1 日) 的所有資料。
• 此外，這個 PDT 的增量偏移量為 3。這表示 PDT 建構工具也會重建 5 月之前三段時間 (月) 的資料。因此，資料會從 2 月、3 月、4 月重建，直到 6 月 1 日當天為止。

6 月 2 日更新後的表格如下：

• 6 月 2 日時，表格的最後一列會是 6 月 1 日新增的資料。
• PDT 建構工具會將月份截斷至 6 月 1 日，並重建 6 月的資料，包括 6 月 2 日。
• 此外，由於增量偏移，PDT 建立工具會重建 6 月前三個月的資料。因此，資料會從 3 月、4 月、5 月重建，直到 6 月 2 日當天。

在開發模式中測試增量 PDT

將新的累加 PDT 部署至正式環境前，您可以先測試 PDT，確保其建構及累加作業正常運作。如要在開發模式下測試永久累加型衍生資料表，請按照下列步驟操作：

1. 為 PDT 建立探索：

   • 在相關聯的模型檔案中，使用 include 參數將 PDT 的檢視檔案納入模型檔案。
   • 在同一個模型檔案中，使用 explore 參數為增量 PDT 的檢視畫面建立探索。

    include: "/views/e_faa_pdt.view"
    explore: e_faa_pdt {}
   

2. 開啟 PDT 的「探索」。方法是選取「查看檔案動作」按鈕，然後選取「探索」名稱。

1. 在「探索」中選取維度或指標，然後按一下「執行」。Looker 接著會建構整個 PDT。如果這是您首次對增量 PDT 執行查詢，PDT 建構工具會建構整個 PDT，以取得初始資料。如果資料表很大，初始建構作業可能需要相當長的時間，建構任何大型資料表時也是如此。

2. 您可以透過下列方式確認是否已建構初始 PDT：

   • 如果您擁有 see_logs 權限，可以查看 PDT 事件記錄，確認表格是否已建構完成。如果 PDT 事件記錄檔未顯示 PDT 建立事件，請檢查 PDT 事件記錄檔探索頁面頂端的狀態資訊。如果顯示「來自快取」，請選取「清除快取並重新整理」，即可取得最新資訊。
   • 否則，您可以查看「探索」資料列的「SQL」分頁中的註解。「SQL」分頁會顯示查詢，以及在「探索」中執行查詢時會採取的動作。舉例來說，如果「SQL」分頁中的註解顯示 -- generate derived table e_incremental_pdt，表示點選「執行」時會採取該動作。

3. 建立 PDT 的初始建構版本後，請使用「探索」中的「重新建立衍生資料表並執行」選項，提示 PDT 進行累加建構。

4. 您可以採用與先前相同的方法，驗證 PDT 是否會逐步建構：

   • 如果您具備 see_logs 權限，可以使用「PDT 事件記錄」查看遞增 PDT 的 create increment complete 事件。如果 PDT 事件記錄中沒有顯示這項事件，且查詢狀態為「來自快取」，請選取「清除快取並重新整理」，取得最新資訊。
   • 查看「探索」資料列的「SQL」分頁中的註解。在這種情況下，註解會指出 PDT 已遞增。例如：-- increment persistent derived table e_incremental_pdt to generation 2

5. 確認 PDT 已建構完成並正確遞增後，如果不想保留 PDT 專用的「探索」，可以從模型檔案中移除或註解 PDT 的 explore 和 include 參數。

在開發模式中建立 PDT 後，除非您進一步變更資料表的定義，否則部署變更後，系統會將同一個資料表用於正式環境。詳情請參閱「Looker 中的衍生資料表」說明頁面的「開發模式中的永久資料表」一節。

排解增量 PDT 問題

本節說明使用增量 PDT 時可能遇到的一些常見問題，以及排解和解決這些問題的步驟。

結構定義變更後，累加 PDT 無法建構

如果累加 PDT 是以 SQL 為基礎的衍生資料表，且 sql 參數包含 SELECT * 等萬用字元，則基礎資料庫結構定義的變更 (例如新增資料欄、移除資料欄或變更資料欄資料類型)，可能會導致 PDT 失敗並顯示下列錯誤：

SQL Error in incremental PDT: Query execution failed

如要解決這個問題，請編輯 sql 參數中的 SELECT 陳述式，改為選取個別資料欄。舉例來說，如果選取子句為 SELECT *，請改為 SELECT column1, column2, ...。

如果結構定義有所變更，且您想從頭重建累加 PDT，請使用 API 呼叫 start_pdt_build，並加入 full_force_incremental 參數。

支援永久累加型衍生資料表的資料庫方言

如要讓 Looker 專案支援累加 PDT，資料庫方言必須支援可刪除及插入資料列的資料定義語言 (DDL) 指令。

下表列出最新版 Looker 中支援永久累加型衍生資料表的方言 (Databricks 僅支援 Databricks 12.1 以上版本)：