增量 PDT

在 Looker 中,永久衍生資料表 (PDT) 會寫入資料庫的臨時結構定義。Looker 會根據永久保存策略,永久保存及重建 PDT。根據預設,觸發 PDT 重新建構時,Looker 會重新建構整個資料表。

永久累加型衍生資料表 (PDT) 是 Looker 建立的 PDT,會將最新資料附加至資料表,而非重新建構整個資料表:

大型表格,底部三列醒目顯示,表示表格中新增了少量新列。

如果您的方言支援永久累加型衍生資料表,則可將下列類型的 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 永久衍生資料表,無法以漸進方式建立。如本頁範例 1 所示,Looker 會使用 INSERT 或 MERGE 指令,為永久累加型衍生資料表建立增量。衍生資料表無法使用自訂資料定義語言 (DDL) 陳述式定義,因為 Looker 無法判斷需要哪些 DDL 陳述式,才能建立準確的增量。
  • 您必須針對時間查詢,最佳化累加 PDT 的來源資料表。具體來說,用於增量鍵的時間型資料欄必須有最佳化策略,例如分區、排序鍵、索引,或方言支援的任何最佳化策略。強烈建議您最佳化來源資料表,因為每次更新累加資料表時,Looker 都會查詢來源資料表,判斷用於累加鍵的時間型資料欄最新值。如果來源資料表未針對這些查詢進行最佳化,Looker 查詢最新值的速度可能會變慢,且費用較高。
  • SQL 視窗函式不支援直接用於增量 PDT。如要搭配使用視窗函式和永久累加型衍生資料表,請將永久累加型衍生資料表建構為不含視窗函式的基礎或中繼資料表,然後在套用視窗函式的獨立衍生資料表或永久衍生資料表中,參照該永久累加型衍生資料表。

定義增量 PDT

您可以使用下列參數,將 PDT 轉換為增量 PDT:

  • increment_key (必須提供,才能將 PDT 設為增量 PDT):定義要查詢新記錄的時間範圍。
  • {% incrementcondition %} Liquid 篩選器 (必須將 SQL 型 PDT 設為永久累加型衍生資料表,不適用於 LookML 型 PDT):將累加鍵連結至累加鍵所依據的資料庫時間欄。詳情請參閱 increment_key 說明文件頁面。
  • increment_offset (選用):整數,定義每次增量建構時,要重新建構的先前時間範圍數量 (以增量鍵的精細程度為準)。increment_offset 參數適用於資料延遲抵達的情況,也就是先前的時間範圍可能有新資料,但這些資料在對應的增量最初建立並附加至 PDT 時並未納入。

請參閱 increment_key 參數說明文件頁面,瞭解如何從永久原生衍生資料表、以 SQL 為基礎的永久衍生資料表和匯總表格建立增量 PDT。

以下是定義 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
  }
}

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

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

...
  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 具備下列屬性:

  • 保留策略:每天觸發一次
  • 增量索引鍵:month
  • 增量偏移: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 具備下列屬性:

  • 增量索引鍵:month
  • 增量偏移: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,確保其建構和遞增。如要在開發模式中測試永久累加型 PDT,請按照下列步驟操作:

  1. 為 PDT 建立探索:

    • 在相關聯的模型檔案中,使用 include 參數,將 PDT 的檢視表檔案納入模型檔案。
    • 在同一個模型檔案中,使用 explore 參數為增量 PDT 的檢視表建立探索。
     include: "/views/e_faa_pdt.view"
     explore: e_faa_pdt {}
    
  2. 開啟 PDT 的「探索」頁面。方法是選取「查看檔案動作」按鈕,然後選取探索名稱。

  1. 在「探索」中選取維度或測量指標,然後點選「執行」。Looker 接著會建構整個 PDT。如果這是您第一次對永久累加型衍生資料表執行查詢,PDT 建構工具會建構整個 PDT,以取得初始資料。如果資料表很大,初始建構作業可能需要相當長的時間,就像建構任何大型資料表一樣。

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

    • 如果您具備 see_logs 權限,可以查看永久衍生資料表事件記錄,確認資料表是否已建立。如果 PDT 事件記錄中沒有 PDT 建立事件,請查看 PDT 事件記錄探索頂端的狀態資訊。如果顯示「來自快取」,請選取「清除快取並重新整理」,取得最新資訊。
    • 或者,您也可以在「探索」的「資料」列中,查看「SQL」分頁中的註解。「SQL」分頁會顯示查詢,以及在「探索」中執行查詢時會採取的動作。舉例來說,如果「SQL」分頁中的註解顯示 -- generate derived table e_incremental_pdt,則點選「執行」時就會執行該動作。
  3. 建立 PDT 的初始版本後,請在「探索」中使用「重新建立衍生資料表並執行」選項,提示系統建立 PDT 的累加版本。

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

    • 如果您具備 see_logs 權限,可以使用 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 參數。

視窗函式在永久累加型衍生資料表中傳回非預期結果

不支援在累加 PDT 中直接使用 SQL 視窗函式。由於增量建構作業只會處理最新時間增量的資料列,以及任何設定的 increment_offset,因此直接在增量 PDT 中定義視窗函式 (例如在 derived_column 或 sql 參數中),可能會導致資料表遞增時,跨分區邊界遺漏值或出現非預期的計算結果。

如要搭配使用視窗函式和增量 PDT,請將增量 PDT 定義為不含視窗函式的基本或中繼 PDT,然後在套用視窗函式的獨立衍生資料表或一般 PDT 中,參照該增量 PDT。

增量 PDT 支援的資料庫方言

如要讓 Looker 支援 Looker 專案中的增量 PDT,資料庫方言必須支援資料定義語言 (DDL) 指令,才能刪除及插入資料列。

下表列出最新版 Looker 中,哪些方言支援永久累加型衍生資料表 (如果是 Databricks,則只有 Databricks 12.1 以上版本支援永久累加型衍生資料表):

方言 是否支援?
Actian Avalanche
Amazon Athena
Amazon Aurora MySQL
Amazon Redshift
Amazon Redshift 2.1+
Amazon Redshift Serverless 2.1+
Apache Druid
Apache Druid 0.13.x - 0.17.x
Apache Druid 0.18+
Apache Hive 2.3+
Apache Hive 3.1.2+
Apache Spark 3+
ClickHouse
ClickHouse 26+
Cloudera Impala 3.1+
Cloudera Impala 3.1+ with Native Driver
Cloudera Impala with Native Driver
DataVirtuality
Databricks
Denodo 7
Denodo 8 & 9
Dremio
Dremio 11+
Dremio 26+ with Arrow Flight SQL
Exasol
Google BigQuery Legacy SQL
Google BigQuery Standard SQL
Google Cloud AlloyDB for PostgreSQL
Google Cloud PostgreSQL
Google Cloud SQL
Google Spanner
Greenplum
HyperSQL
IBM Netezza
MariaDB
Microsoft Azure PostgreSQL
Microsoft Azure SQL Database
Microsoft Azure Synapse Analytics
Microsoft SQL Server 2008+
Microsoft SQL Server 2012+
Microsoft SQL Server 2016
Microsoft SQL Server 2017+
MongoBI
MongoSQL
MySQL
MySQL 8.0.12+
Oracle
Oracle ADWC
PostgreSQL 9.5+
PostgreSQL pre-9.5
PrestoDB
PrestoSQL
SAP HANA
SAP HANA 2+
SingleStore
SingleStore 7+
Snowflake
Teradata
Trino
Vector
Vertica