資料函式庫檢視與中繼資料管理應用

– 8 分鐘閱讀

資料函式庫檢視是根據 SQL 陳述式結果集的可命名虛擬表，簡化複雜查詢、提升資料安全性。本文除了介紹檢視的建立語法、使用方法和效能提升技巧（如物化檢視）外，也涵蓋檢視更新、命名規範和效能監控等注意事項。此外，文章也說明如何利用 Snowflake 的 information_schema 檢視表，查詢資料函式庫中繼資料，包含資料表、檢視表、欄位等資訊，以及如何應用這些中繼資料進行 Schema 探索、自動化管理、安全性稽核和檔案生成等工作，提供 SQL 查詢範例和詳細的程式碼說明，幫助讀者深入理解並有效運用資料函式庫檢視和中繼資料。

檢視的使用與注意事項

檢視（Views）是資料函式庫中一個重要的功能，能夠簡化複雜的查詢並提高資料安全性。在本章中，我們將探討檢視的使用方法及其相關的注意事項。

檢視的優點

檢視可以隱藏底層的複雜查詢，使用者無需瞭解複雜的查詢邏輯即可取得所需的資料。此外，檢視還可以用於實作資料列級別的安全性控制。

使用檢視改善效能

如果某些檢視需要聚合大量資料，可以考慮將其轉換為物化檢視（Materialized Views），以提高查詢效能。

-- 建立一個簡單的檢視
CREATE VIEW region_totalsales_vw AS
SELECT 
    r.R_NAME AS REGION_NAME, 
    SUM(o.O_TOTALPRICE) AS SUM_TOTALPRICE
FROM 
    Region r
JOIN 
    Nation n ON r.R_REGIONKEY = n.N_REGIONKEY
JOIN 
    Customer c ON n.N_NATIONKEY = c.C_NATIONKEY
JOIN 
    Orders o ON c.C_CUSTKEY = o.O_CUSTKEY
GROUP BY 
    r.R_NAME;

檢視的使用範例

-- 查詢檢視
SELECT * FROM region_totalsales_vw;
+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+
| REGION_NAME | SUM_TOTALPRICE |
|
---
-
---
-
---
--+
---
-
---
-
---
-
---
-|
| ASIA        | 4378591175.90  |
| AMERICA     | 4321075685.27  |
| EUROPE      | 4391712838.03  |
| AFRICA      | 4239225325.42  |
| MIDDLE EAST | 4322198235.40  |
+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+

使用檢視的注意事項

  1. 檢視定義的更新: 當建立檢視時，Snowflake 會根據當時的資料函式庫狀態收集相關資訊。如果之後對底層表進行了修改（如新增、修改或刪除欄位），檢視的定義不會自動更新。如果刪除了一個被檢視使用的欄位，檢視將被標記為無效，需要重新建立。

  2. 命名規範: 由於檢視和表分享相同的名稱空間，因此不能有相同名稱的表和檢視。為了區分，可以在檢視名稱後面加上 _vw 字尾。

  3. 效能問題: 雖然檢視可以簡化查詢，但如果使用者將多個複雜的檢視進行連線查詢，可能會導致效能問題。因此，需要監控查詢效能。

-- 查詢執行時間較長的查詢
SELECT 
    query_id, 
    total_elapsed_time AS runtime, 
    SUBSTR(query_text, 1, 40)
FROM 
    TABLE(learning_sql.information_schema.query_history())
WHERE 
    total_elapsed_time > 5000
ORDER BY 
    start_time;

程式碼解密：

  1. query_id：查詢的唯一識別碼。
  2. total_elapsed_time：查詢執行的總時間，以毫秒為單位。
  3. SUBSTR(query_text, 1, 40)：擷取查詢陳述式的前40個字元，用於識別查詢內容。
  4. TABLE(learning_sql.information_schema.query_history())：從 Snowflake 的資訊架構中取得查詢歷史記錄。
  5. WHERE total_elapsed_time > 5000：篩選出執行時間超過5000毫秒（即5秒）的查詢。
  6. ORDER BY start_time：按照查詢開始時間排序結果。

練習題

練習 12-1

根據給定的查詢結果，撰寫 region_totalsales_vw 檢視的定義。需要連線 Region、Nation、Customer 和 Orders 表，並確保欄位名稱與結果集一致。

練習 12-2

根據給定的 Supplier 表結構，建立一個名為 supplier_vw 的檢視，包括以下欄位：

  • s_suppkey 為 keyval
  • s_name 為 supplier_name
  • partial_phone（隱藏 s_phone 中除最後四位數字外的所有數字）
  • acct_status（若 s_acctbal 小於0，則為負值，否則為正值）
-- 建立 supplier_vw 檢視
CREATE VIEW supplier_vw AS
SELECT 
    s_suppkey AS keyval,
    s_name AS supplier_name,
    REGEXP_REPLACE(s_phone, '\\d(?=\\d{4})', '*') AS partial_phone,
    CASE WHEN s_acctbal < 0 THEN 'negative' ELSE 'positive' END AS acct_status
FROM 
    Supplier;

程式碼解密：

  1. s_suppkey AS keyval：將 s_suppkey 欄位別名為 keyval。
  2. s_name AS supplier_name：將 s_name 欄位別名為 supplier_name。
  3. REGEXP_REPLACE(s_phone, '\\d(?=\\d{4})', '*') AS partial_phone：使用正規表示式替換 s_phone 中除最後四位數字外的所有數字為 *。
  4. CASE WHEN s_acctbal < 0 THEN 'negative' ELSE 'positive' END AS acct_status：根據 s_acctbal 的值決定 acct_status 是 ’negative’ 或 ‘positive’。
  5. FROM Supplier：從 Supplier 表中選擇資料。
-- 測試 supplier_vw 檢視
SELECT * FROM supplier_vw WHERE keyval BETWEEN 1 AND 50;
+
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
--+
---
-
---
-
---
--+
| KEYVAL | SUPPLIER_NAME      | PARTIAL_PHONE   | ACCT_STATUS |
|
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
--+
---
-
---
-
---
--|
| 1      | Supplier#000000001 | **-***-***-1736 | positive    |
| 4      | Supplier#000000004 | **-***-***-7479 | positive    |
| ...    | ...                | ...             | ...         |
+
---
-
---
-+
---
-
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
--+
---
-
---
-
---
--+

資料函式庫中繼資料的管理與應用

Snowflake 資料函式庫系統中，中繼資料（Metadata）扮演著至關重要的角色，用於追蹤和管理資料函式庫中的各類別物件，如資料表、欄位、檢視表等。本章將探討 Snowflake 如何管理和利用中繼資料，並介紹相關的查詢與應用方法。

information_schema 的作用

Snowflake 提供了一個特殊的結構，稱為 information_schema，它是一個包含多個檢視表的集合，用於展示資料函式庫的中繼資料。這些檢視表涵蓋了資料函式庫物件的各個方面，包括資料表結構、安全性設定、資料移動操作等。

information_schema 檢視表清單

在 Snowsight 或 SnowSQL 中，可以瀏覽或查詢 information_schema 下的檢視表。這些檢視表大致可分為以下幾類別：

  1. 資料函式庫物件資訊：如 Tables、Views、Columns 等，用於查詢資料表、檢視表及欄位的相關資訊。
  2. 安全性資訊：如 Applicable_Roles、Object_Privileges 等，用於瞭解角色許可權及物件存取許可權。
  3. 資料移動相關：如 External_Tables、Load_History、Pipes 等，提供外部表格、載入歷史及管道操作的資訊。
  4. 程式相關：如 Functions、Stored Procedures，列出自定義函式及預存程式。

查詢 information_schema

使用 SnowSQL，可以直接將當前綱要（Schema）設定為 information_schema 並進行查詢。例如：

-- 切換到 information_schema
USE SCHEMA learning_sql.information_schema;

-- 查詢目前綱要下的資料表與檢視表資訊
SELECT table_name, table_type, row_count 
FROM tables 
WHERE table_schema = 'PUBLIC';

查詢結果範例

| TABLE_NAME | TABLE_TYPE | ROW_COUNT | |

|

-|

| | DATE_EXAMPLE | BASE TABLE | 3 | | EMPLOYEE | BASE TABLE | 8 | | NATION | BASE TABLE | 25 | | ORDER_CALCULATION_VW | VIEW | NULL | | SUPPLIER | BASE TABLE | 7400 |

此查詢結果顯示了 PUBLIC 綱要下的所有資料表及檢視表的基本資訊，包括名稱、型別及列數。

查詢特定檢視表的定義

若要了解特定檢視表的定義，可以查詢 information_schema.views：

SELECT table_name, 
       SUBSTR(view_definition, 1, 100) AS view_def 
FROM views 
WHERE table_schema = 'PUBLIC';

查詢結果範例

| TABLE_NAME | VIEW_DEF | |

|

| | BIG_SPENDERS_1998_VW | CREATE VIEW big_spenders_1998_vw … | | EMPLOYEE_VW | CREATE VIEW employee_vw AS SELECT … | | ORDER_CALCULATION_VW | CREATE VIEW order_calculation_vw AS SELECT …|

此查詢擷取了檢視表的名稱及其定義的前100個字元，能夠幫助瞭解檢視表的建立邏輯。

內容解密：

  • table_name：檢視表的名稱。
  • view_definition：檢視表的定義陳述，顯示建立檢視表的 SQL 命令。
  • SUBSTR 函式用於擷取字串的一部分，此處用來顯示定義的前100字元。

中繼資料的應用場景

  1. 自動化管理：透過查詢中繼資料，可以動態取得資料函式庫物件資訊，便於撰寫自動化指令碼進行例行性管理。
  2. 稽核與安全性檢查：利用中繼資料檢查物件許可權設定，確保安全性組態正確。
  3. 檔案化與報表生成：自動生成資料函式庫檔案或報表，幫助團隊瞭解資料函式庫結構。

Plantuml 圖表示範：中繼資料查詢流程圖

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title 資料函式庫檢視與中繼資料管理應用

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

圖表翻譯： 此圖展示了使用 Snowflake 時，查詢中繼資料的基本流程：

  1. 連線到 Snowflake 資料函式庫。
  2. 將當前綱要切換至 information_schema。
  3. 對特定的檢視表（如 Tables 或 Views）進行查詢。
  4. 處理並分析查詢結果。
  5. 結束操作。

使用Metadata進行資料函式倉管理

在資料函式倉管理中，瞭解如何檢索schema物件的資訊是一項非常重要的技能。本篇文章將探討如何使用INFORMATION_SCHEMA來查詢資料函式庫的metadata，並展示其在實際工作中的應用。

查詢檢視表的定義

首先，我們來看看如何查詢檢視表的名稱及其定義的前100個字元。

SELECT table_name, LEFT(view_definition, 100)
FROM views;

內容解密：

  • SELECT table_name：選擇檢視表的名稱。
  • LEFT(view_definition, 100)：擷取檢視表定義的前100個字元。
  • FROM views：從views表中查詢資料。

查詢資料表的欄位資訊

接下來，我們查詢learning_sql.public schema中所有資料表的欄位資訊。

SELECT table_name, column_name,
       CONCAT(data_type,
              CASE
                  WHEN data_type = 'TEXT' THEN CONCAT('(', character_maximum_length, ')')
                  WHEN data_type = 'NUMBER' THEN CONCAT('(', numeric_precision, ',', numeric_scale, ')')
                  ELSE ''
              END) AS column_def
FROM columns
WHERE table_schema = 'PUBLIC'
ORDER BY table_name, ordinal_position;

內容解密：

  • SELECT table_name, column_name：選擇資料表的名稱和欄位名稱。
  • CONCAT(data_type, ...)：根據資料型別，構建欄位定義字串。
    • 當資料型別為TEXT時，新增字元最大長度。
    • 當資料型別為NUMBER時，新增數字精確度和小數位數。
  • FROM columns：從columns表中查詢資料。
  • WHERE table_schema = 'PUBLIC'：篩選出PUBLIC schema中的資料表。
  • ORDER BY table_name, ordinal_position：按資料表名稱和欄位順序排序。

使用Metadata進行Schema探索

使用INFORMATION_SCHEMA可以方便地探索資料函式庫的schema。例如，查詢所有包含lname或last_name欄位的資料表。

SELECT table_name, data_type, character_maximum_length
FROM columns
WHERE table_schema = 'PUBLIC'
  AND column_name IN ('LNAME', 'LAST_NAME');

內容解密：

  • SELECT table_name, data_type, character_maximum_length：選擇資料表的名稱、欄位資料型別和字元最大長度。
  • WHERE table_schema = 'PUBLIC'：篩選出PUBLIC schema中的資料表。
  • AND column_name IN ('LNAME', 'LAST_NAME')：篩選出欄位名稱為LNAME或LAST_NAME的欄位。

查詢資料表的最後修改時間

我們還可以查詢資料表的最後修改時間，以瞭解schema物件的變更歷史。

SELECT table_name, last_altered
FROM tables
WHERE table_schema = 'PUBLIC'
ORDER BY 2 DESC;

內容解密：

  • SELECT table_name, last_altered：選擇資料表的名稱和最後修改時間。
  • FROM tables：從tables表中查詢資料。
  • WHERE table_schema = 'PUBLIC'：篩選出PUBLIC schema中的資料表。
  • ORDER BY 2 DESC：按最後修改時間降序排序。
玄貓 BlackCat

玄貓 BlackCat

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