CREATE MATERIALIZED VIEW

適用於： Databricks SQL

具體化檢視 是一種檢視，其中預先計算的結果可供查詢使用，並可更新以反映輸入中的變更。 每次重新整理具體化檢視時，都會重新計算查詢結果，以反映上游資料集中的變更。 所有具體化檢視都由 ETL 管線提供支援。 您可以手動或依排程刷新具體化檢視。

若要深入瞭解如何執行手動重新整理，請參閱 REFRESH （MATERIALIZED VIEW 或 STREAMING TABLE）。

若要深入瞭解如何排程重新整理，請參閱 範例 或 ALTER MATERIALIZED VIEW。

具體化檢視只能使用 Pro 或無伺服器 SQL 倉儲或在管線內建立。

注意

具體化檢視和串流資料表的建立和重新整理作業是由無伺服器 Lakeflow Spark 宣告式管線提供支援。 您可以使用目錄總管來檢視 UI 中備份管線的詳細數據。 請參閱 什麼是目錄總管？。

語法

{ CREATE OR REPLACE MATERIALIZED VIEW | CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] }
  view_name
  [ column_list ]
  [ view_clauses ]
  AS query

column_list
   ( { column_name column_type column_properties } [, ...]
      [ , table_constraint ] [...])

   column_properties
      { NOT NULL | COMMENT column_comment | column_constraint | MASK clause } [ ... ]

view_clauses
  { PARTITIONED BY (col [, ...]) |
    CLUSTER BY clause |
    COMMENT view_comment |
    DEFAULT COLLATION UTF8_BINARY |
    TBLPROPERTIES clause |
    SCHEDULE [ REFRESH ] schedule_clause |
    schedule |
    WITH { ROW FILTER clause } } [...]

schedule
  { SCHEDULE [ REFRESH ] schedule_clause |
    TRIGGER ON UPDATE [ AT MOST EVERY trigger_interval ] }

schedule_clause
  { EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } |
    CRON cron_string [ AT TIME ZONE timezone_id ] }

參數

• 取代

  如果已指定，且該檢視及其內容已經存在，則會進行替換。

• 若不存在

  如果不存在，則建立檢視。 如果此名稱的檢視已經存在，則會忽略 CREATE MATERIALIZED VIEW 陳述式。

  您最多可以指定 IF NOT EXISTS 或 OR REPLACE 中的一個。

• view_name

  新建立的檢視的名稱。 完整檢視名稱必須唯一。

• column_list

  選擇性地在檢視的查詢結果中標記數據行。 如果您提供資料行清單，數據行別名的數目必須符合查詢中的表達式數目。 如果未指定任何欄位清單，別名會從視圖的內容中衍生。

  • 欄位名稱

    數據行名稱必須是唯一的，且對應至查詢的輸出數據行。

  • 欄位類型

    指定數據行的數據類型。 具體化檢視不支援 Azure Databricks 支援的所有數據類型。

  • column_comment

    描述欄的可選 STRING 常值。 此選項必須與 column_type 一起指定。 如果未指定數據行類型，則會略過數據行批注。

  • column_constraint

    將資訊主鍵或資訊外鍵約束新增至具現化檢視表中的欄位。 如果未指定數據行類型，則會略過數據行條件約束。

  • MASK 子句

    新增數據行遮罩函式來匿名敏感數據。 該列的所有後續查詢都會接收將該函式應用於該列所得到的結果，取代該列的原始值。 這對於細緻的訪問控制用途非常有用，其中函式可以檢查調用者的身份識別或群組成員資格，以判斷是否要隱藏該值。 如果未指定欄位類型，則會略過欄位遮罩。

• 資料表限制

  將描述性主鍵或描述性外鍵約束加入具象化檢視的資料表中。 如果未指定數據行類型，則會略過數據表條件約束。

• view_clauses

  可選擇指定新具體化檢視表的分區、批註、使用者定義屬性以及重新整理排程。 每個次子句只能指定一次。

  • 分區依據 (PARTITIONED BY)

    用來根據表格的欄位來分割該表格的選擇性欄位清單。

    注意

    Liquid clustering 提供彈性且優化的叢集解決方案。 請考慮針對具體化檢視使用 CLUSTER BY，而不是 PARTITIONED BY。

  • CLUSTER BY

    以欄位的子集進行叢集的選擇性子句。 使用 CLUSTER BY AUTO 的自動叢集功能，Databricks 會智能地選擇叢集索引鍵，以優化查詢效能。 請參閱 針對數據表使用液體叢集。

    液體聚類不能與 PARTITIONED BY結合使用。

  • 批注view_comment

    描述數據表的 STRING 文字常數。

  • 預設排序規則 UTF8_BINARY

    適用於： Databricks SQL Databricks Runtime 17.1 和更新版本

    強制將物化視圖的預設定序設為UTF8_BINARY。 如果建立檢視的結構描述具有除 UTF8_BINARY 之外的預設排序規則，則此子句是必需的。 具體化的檢視的預設排序會作為檢視內容中的預設排序。

  • TBLPROPERTIES

    選擇性地設定一個或多個使用者定義的屬性。

    使用此設定來指定 Lakeflow Spark 宣告式管線的執行階段通道，用來執行此陳述式。 將 pipelines.channel 屬性的值設定為 "PREVIEW" 或 "CURRENT"。 預設值是 "CURRENT"。 如需 Lakeflow Spark 宣告式管線通道的詳細資訊，請參閱 Lakeflow Spark 宣告式管線執行階段通道。

  • 附表

    排程可以是 SCHEDULE 陳述式或 TRIGGER 陳述式。

    • 時間表 [ REFRESH ] 時間表條款

      • EVERY number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

        若要排程週期性地發生的重新整理，請使用 EVERY 語法。 如果指定了 EVERY 語法，串流數據表或實體化檢視會根據所提供的值在指定的時間間隔定期重新整理，例如 HOUR、HOURS、DAY、DAYS、WEEK或 WEEKS。 下表列出 number接受的整數值。

        時間單位	整數值
        HOUR or HOURS	1 <= 高 <= 72
        DAY or DAYS	1 <= D <= 31
        WEEK or WEEKS	1 <= 寬 <= 8

        注意

        內含時間單位的單數和複數形式在語意上相等。

      • CRON cron_string [ AT TIME ZONE timezone_id ]

        使用 quartz cron 值來排程重新整理。 接受有效的 time_zone_values 。 不支援 AT TIME ZONE LOCAL。

        如果 AT TIME ZONE 不存在，則會使用工作階段時區。 如果 AT TIME ZONE 不存在且未設定會話時區，則會擲回錯誤。 SCHEDULE 在語意上相當於 SCHEDULE REFRESH。

    • UPDATE 觸發 [ 最多每trigger_interval ]

      重要

      該 TRIGGER ON UPDATE 功能處於 測試階段。

      選擇性地將表格設定為在更新上游資料來源時重新整理，最多每分鐘一次。 設定值 ， AT MOST EVERY 以要求重新整理之間至少有最短時間。

      上游資料來源必須是外部或受控 Delta 資料表 （包括具體化檢視或串流資料表），或相依性僅限於支援資料表類型的受控檢視。

      啟用 檔案事件 可以讓觸發程式更有效率，並增加觸發程式更新的一些限制。

      是 trigger_interval 至少 1 分鐘的 INTERVAL 陳述式。

      TRIGGER ON UPDATE 有以下限制

      • 使用 TRIGGER ON UPDATE時，每個具體化檢視的上游資料來源不超過 10 個。
      • 最多可以使用 TRIGGER ON UPDATE指定 1000 個串流資料表或具體化檢視。
      • 子 AT MOST EVERY 句預設為 1 分鐘，且不能小於 1 分鐘。

  • 與 ROW FILTER 子句

    將數據列篩選函式加入至數據表。 該數據表的所有後續查詢都會接收函數計算結果為布林 TRUE 的資料列子集。 這對於精細訪問控制用途很有用，其中函式可以檢查叫用使用者的身分識別或群組成員資格，以判斷是否要篩選特定數據列。

• AS 查詢

  從基表或其他檢視建構檢視的查詢。

所需的權限

建立具體化檢視 (MV) 的使用者是 MV 擁有者，且必須擁有下列權限：

• SELECT 對 MV 所參考的基表的權限。
• 父目錄的USE CATALOG許可權及父架構的USE SCHEMA許可權。
• CREATE MATERIALIZED VIEW 對 MV 結構描述的權限。

用戶必須具備能夠更新MV的條件：

• 父目錄的USE CATALOG許可權及父架構的USE SCHEMA許可權。
• MV 的擁有權或對 MV 的 REFRESH 權限。
• MV 的擁有者必須具有MV所參考基表的 SELECT 許可權。

使用者若要查詢 MV，他們需要：

• 父目錄的USE CATALOG許可權及父架構的USE SCHEMA許可權。
• 具體化檢視的 SELECT 權限。

列篩選和欄位遮罩

資料列篩選可讓您指定一個函式，每當表格掃描擷取資料列時，該函式會作為篩選器被套用。 這些篩選條件可確保後續查詢只會傳回篩選條件述詞評估為 true 的資料列。

每當數據表掃描擷取數據列時，數據行遮罩可讓您遮罩數據行的值。 涉及該數據行的所有未來查詢都會收到評估數據行函式的結果，並取代數據行的原始值。

如需如何使用數據列篩選和數據行遮罩的詳細資訊，請參閱 數據列篩選和數據行遮罩。

管理行篩選和列遮罩

具化檢視上的列篩選和行遮罩應該透過 CREATE 語句新增。

行為

• 重新整理為定義者：當 REFRESH MATERIALIZED VIEW 語句重新整理具體化檢視時，資料列篩選函式會以定義者權限執行（作為資料表擁有者）。 這表示資料表的更新會使用建立實體化檢視之使用者的安全性內容。
• 查詢：雖然大部分篩選都會以定義器的許可權執行，但檢查用戶內容 （例如 CURRENT_USER 和 IS_MEMBER） 的函式是例外狀況。 這些函式會以調用者的身分執行。 此方法會根據目前使用者的內容強制執行使用者特定的數據安全性和訪問控制。
• 在包含數據列篩選和數據行遮罩的源數據表上建立具體化檢視時，具體化檢視的重新整理一律是完整重新整理。 完整重新整理會使用最新的定義，重新處理來源中可用的所有數據。 這可確保來源資料表上的安全策略會以最新的數據和定義進行評估並套用。

可檢視性

使用 DESCRIBE EXTENDED、INFORMATION_SCHEMA或目錄總管來檢查套用至指定具體化檢視的現有行篩選和列遮罩。 此功能可讓使用者進行稽核和檢閱具現化視圖的資料存取和保護措施。

限制

• 當具體化檢視中的可為 NULL 資料行有 sum 聚合，且在該欄位中最後一個非 NULL 值被移除後，只剩下 NULL 值，則具體化檢視的結果匯總值會傳回零，而非 NULL。
• 欄位參考不需要別名。 非欄位參考表達式需要別名，如下列範例所示：
  • 允許：SELECT col1, SUM(col2) AS sum_col2 FROM t GROUP BY col1
  • 不允許：SELECT col1, SUM(col2) FROM t GROUP BY col1
• 必須手動指定 NOT NULL 和 PRIMARY KEY，才能成為有效的陳述式。
• 具體化檢視不支援識別欄或代理索引鍵。
• 具體化檢視不支援 OPTIMIZE 和 VACUUM 命令。 自動進行維護。
• 具體化檢視不支援定義數據品質限制的預期。

範例

-- Create a materialized view if it doesn't exist
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed whenever the
-- upstream data is updated
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies
  TRIGGER ON UPDATE
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create and schedule a materialized view to be refreshed every day.
-- Note: All columns in a GROUP BY need to be explicitly aliased
> CREATE MATERIALIZED VIEW daily_sales
  COMMENT 'Daily sales numbers'
  SCHEDULE EVERY 1 DAY
  AS SELECT date AS date, sum(sales) AS sumOfSales
       FROM table1
       GROUP BY date;

-- Sets the runtime channel to "PREVIEW"
> CREATE MATERIALIZED VIEW mv_preview
  TBLPROPERTIES(pipelines.channel = "PREVIEW")
  AS SELECT * FROM RANGE(10)

-- Create a materialized view with a table constraint
> CREATE MATERIALIZED VIEW IF NOT EXISTS subscribed_movies(
    member_id int NOT NULL,
    full_name string,
    movie_title string,
    CONSTRAINT movie_pk PRIMARY KEY(member_id)
  )
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create or replace the materialized view to remove the table constraint and add a partition
> CREATE OR REPLACE MATERIALIZED VIEW subscribed_movies
  PARTITIONED BY (member_id)
  AS SELECT mo.member_id, mb.full_name, mo.movie_title
       FROM movies AS mo INNER JOIN members AS mb ON mo.member_id = mb.id;

-- Create a materialized view with a row filter and a column mask
> CREATE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;

相關文章

• ALTER MATERIALIZED VIEW
• 約束子句
• DROP VIEW
• SHOW CREATE TABLE
• 使用數據列篩選和數據行遮罩篩選敏感數據
• DESCRIBE TABLE

• REFRESH