Snowflake Metadata：探索資料函式庫的資訊寶藏

– 10 分鐘閱讀

Metadata 就像資料函式庫的地圖，引導我們理解資料的結構、內容和來源。在 Snowflake 中，這個地圖就叫做 information_schema，它包含許多檢視，揭示了資料函式庫的各種資訊，從表格和檢視的定義到安全性設定和資料移動的紀錄，應有盡有。透過 Snowsight，你可以在每個資料函式庫下找到 information_schema，即使是 Snowflake 的範例資料函式庫也不例外。在 SnowSQL 中，你可以直接使用 SQL 查詢 information_schema 中的檢視。例如，要檢視 learning_sql.public schema 中的所有表格和檢視，可以先用 use schema learning_sql.information_schema; 切換到 information_schema，然後執行 select table_name, table_type, row_count from tables where table_schema = 'PUBLIC'; 就能得到結果。想更進一步瞭解檢視的定義，可以查詢 information_schema.views，使用 select table_name, substr(view_definition, 1, 100) view_def from views where table_schema = 'PUBLIC'; 就能看到檢視的程式碼片段。總之，information_schema 就像一座寶函式庫，儲存著 Snowflake 資料函式庫的各種 Metadata，善用它就能掌握資料函式庫的全貌，讓資料治理和分析工作更加得心應手。

玄貓結語

總而言之，資料函式庫視窗是一個功能強大的工具，可以幫助我們簡化資料存取、提高資料安全性，並最佳化查詢效能。無論是隱藏敏感資料、彙總複雜資料，還是簡化複雜計算，視窗都能在資料函式庫應用程式中發揮重要作用。下次在設計資料函式庫時，不妨考慮一下如何善用視窗，讓你的資料函式庫應用程式更上一層樓。

轉換成實體化視窗：提升資料聚合檢視的效能

若您的視窗需要處理大量資料聚合，將其轉換為實體化視窗是提升效能的有效方法。

一旦業務部門開始使用某個視窗，您可以進一步加入季節性銷售額、客戶忠誠度計畫以及其他折扣和促銷活動的計算。這樣可以確保所有業務人員都能產生正確的客戶發票。

使用視窗的考量點

雖然視窗在資料存取策略中扮演重要角色，但仍有一些潛在缺點需要考慮。首先，當建立視窗時，Snowflake 會根據當時資料函式庫的狀態收集視窗的相關資訊（又稱為元資料，將在第 13 章中介紹）。如果您稍後在視窗查詢中使用的某個資料表中新增、修改或刪除欄位，視窗定義不會自動變更。此外，如果您刪除視窗使用的欄位，該視窗將在內部被標記為無效，您需要修改視窗定義並重新建立視窗，才能再次使用它。

第二個考量點是視窗的命名。雖然視窗和資料表不同，但它們分享一個名稱空間，因此您不能擁有同名的資料表和視窗。玄貓通常會在所有視窗名稱中加入 _vw 字尾，例如 abc 和 abc_vw，這樣可以清楚地向使用者社群表明其用途。

第三個也是更微妙的考量點是，雖然視窗非常適合隱藏複雜性，但使用視窗的人通常不會意識到這種複雜性。這既是好事，也可能是壞事。如果使用者使用單個視窗編寫查詢，通常不會有問題，但有些喜歡冒險的使用者可能會開始將多個視窗連線在一起、在子查詢中使用視窗，以及執行您在建立視窗時從未考慮過的其他操作。將兩個或三個具有複雜底層查詢的視窗連線在一起可能會導致效能不佳，進而增加計算成本和讓使用者不滿意。

監控 Snowflake 中長時間執行的查詢

雖然資料函式庫效能是一個廣泛而重要的主題，但本章通常不涵蓋。不過，既然玄貓提到了效能是視窗的一個問題，讓玄貓向您展示一種在 Snowflake 中尋找長時間執行查詢的方法：

PUBLIC>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;

此查詢使用 information_schema（將在第 13 章中介紹）來尋找任何耗時超過 5,000 毫秒才能完成的查詢。查詢傳回單個結果，顯示玄貓在本章前面針對 big_spenders_1998_vw 視窗執行的查詢耗時超過 9 秒才能完成。您可以使用此類別查詢來監控長時間執行的查詢，無論它們是否使用視窗。

玄貓對 Snowflake 視窗的經驗分享

在為某金融科技公司設計資料倉儲時，玄貓發現過度使用複雜視窗會導致查詢效能顯著下降。為瞭解決這個問題，玄貓團隊採取了以下措施：

  1. 視窗複雜度評估： 定期審查視窗的底層查詢，識別潛在的效能瓶頸。
  2. 實體化視窗策略： 對於需要頻繁查詢與資料變動不大的視窗，轉換為實體化視窗，減少重複計算。
  3. 查詢最佳化教育： 提升團隊成員的 SQL 編寫能力，避免在查詢中過度使用視窗巢狀。

透過這些措施，玄貓成功地提升了資料倉儲的整體效能，並降低了計算成本。

驗證您的知識

以下練習旨在測試您對視窗的理解程度。請參閱附錄 B 中的「第 12 章」以取得解答。

練習 12-1

考慮以下針對視窗的查詢和結果集：

PUBLIC>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  |
+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+

撰寫 region_totalsales_vw 的視窗定義。您需要連線 Region、Nation、Customer 和 Orders 資料表。您的欄位名稱應與結果集中顯示的名稱相符。

練習 12-2

Supplier 資料表如下所示：

PUBLIC>desc supplier;
+
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
+
| name        | type         | kind   | null? |
|
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
|
| S_SUPPKEY   | NUMBER(38,0) | COLUMN | Y     |
| S_NAME      | VARCHAR(25)  | COLUMN | Y     |
| S_ADDRESS   | VARCHAR(40)  | COLUMN | Y     |
| S_NATIONKEY | NUMBER(38,0) | COLUMN | Y     |
| S_PHONE     | VARCHAR(15)  | COLUMN | Y     |
| S_ACCTBAL   | NUMBER(12,2) | COLUMN | Y     |
| S_COMMENT   | VARCHAR(101) | COLUMN | Y     |
+
---
-
---
-
---
--+
---
-
---
-
---
---
+
---
-
---
-+
---
-
---
+

建立一個名為 supplier_vw 的視窗，其中包含以下欄位：

  • s_suppkey as keyval
  • s_name as supplier_name
  • partial_phone（隱藏 s_phone 的所有數字，但最後四位數字除外）
  • acct_status（如果 s_acctbal < 0，則為負數，否則為正數）

為了進行測試，您可以使用 1 到 50 之間的 s_suppkey。為了隱藏電話號碼，請將任何數字（最後四位數字除外）替換為 *。有許多可能的 Snowflake 函式可用於隱藏電話號碼的一部分，包括 substr()、right()、length()、translate()、regexp_replace() 等。結果應如下所示：

PUBLIC>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    |
| 7      | Supplier#000000007 | **-***-***-2201 | positive    |
| 9      | Supplier#000000009 | **-***-***-8662 | positive    |
| 10     | Supplier#000000010 | **-***-***-8585 | positive    |
| 11     | Supplier#000000011 | **-***-***-1505 | positive    |
| 12     | Supplier#000000012 | **-***-***-7181 | positive    |
| 13     | Supplier#000000013 | **-***-***-7813 | positive    |
| 14     | Supplier#000000014 | **-***-***-5058 | positive    |
| 15     | Supplier#000000015 | **-***-***-6394 | positive    |
| 16     | Supplier#000000016 | **-***-***-4215 | positive    |
| 17     | Supplier#000000017 | **-***-***-9219 | positive    |
| 18     | Supplier#000000018 | **-***-***-1115 | positive    |

透過本章，玄貓與大家一同學習了視窗的相關知識，以及如何利用安全視窗來實作資料列層級的安全性。這些技術有助於保護敏感資料，並簡化使用者社群的資料存取。

Snowflake Metadata：解鎖資料洞察的隱藏之門

在 Snowflake 中，當你建立用來儲存客戶、產品、實驗結果或任何對組織至關重要的資訊的 Schema 時，Snowflake 需要追蹤所有表格、欄位、叢集、檢視和其他資料函式庫物件。不出所料，Snowflake 將這些資訊儲存在一個資料函式庫中供內部使用，同時也將這些資訊（稱為 Metadata）提供給資料函式庫使用者。

Metadata 的重要性

Metadata 就像是資料的說明書，它提供了關於資料的結構、內容、品質和來源等資訊。透過 Metadata，我們可以更容易地理解、管理和使用資料。

Snowflake 的 Metadata 儲存方式

Snowflake 使用 information_schema 這個 Schema 來儲存 Metadata。如果你使用 Snowsight，你可以在每個資料函式庫下看到 information_schema，包括 Snowflake 的範例資料函式庫。

information_schema：Metadata 的寶函式庫

information_schema 包含了一系列的檢視，涵蓋了 Schema 物件（例如 Databases、Tables、Views、Columns）、安全性（例如 Applicable_Roles、Enabled_Roles、Object_Privileges、Usage_Privileges）、資料移動（例如 External_Tables、File_Formats、Load_History、Pipes、Replication_Databases）和程式（例如 Functions、Stored Procedures）。

使用 SnowSQL 查詢 Metadata

當使用 SnowSQL 時，你可以將你的 Schema 設定為 information_schema，並直接查詢這些檢視。例如，以下查詢顯示了我在 learning_sql.public Schema 中建立的所有表格和檢視：

PUBLIC>use schema learning_sql.information_schema;
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| status                               |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
|
| Statement executed successfully. |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+

這個 SQL 指令是用於切換到 Snowflake 資料函式庫中特定的 schema，讓我們可以查詢該 schema 下的 metadata。

  • PUBLIC> use schema learning_sql.information_schema;: 這行程式碼是 SQL 指令，用於切換到名為 information_schema 的 schema。這個 schema 位於 learning_sql 資料函式庫中。PUBLIC> 可能是表示目前使用者或角色的提示符號。
  • status: 顯示指令的執行狀態。
  • Statement executed successfully: 表示 SQL 指令已成功執行，並且目前的工作環境已切換到指定的 schema。

接下來，我們可以執行以下的 SQL 查詢來取得表格的 metadata：

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 |
| PARTSUPP                 | BASE TABLE |     16000 |
| WH_COUNTRY_MONTHLY_SALES | BASE TABLE |        60 |
| EMPLOYEE_VW              | VIEW       |      NULL |
| LINEITEM                 | BASE TABLE |    119989 |
| ORDERS                   | BASE TABLE |    115269 |
| PERSON                   | BASE TABLE |         5 |
| YEARLY_PART_SALES_VW     | VIEW       |      NULL |
| BIG_SPENDERS_1998_VW     | VIEW       |      NULL |
| PERSON_VW                | VIEW       |      NULL |
| CUSTOMER                 | BASE TABLE |     66076 |
| EMPLOYEE_MANAGER_VW      | VIEW       |      NULL |
| PERSON_REFRESH           | BASE TABLE |         6 |
| REGION                   | BASE TABLE |         5 |
| SUPPLIER                 | BASE TABLE |      7400 |
| PART                     | BASE TABLE |      4000 |
+
---
-
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-+
---
-
---
-
---
+

這個 SQL 查詢是用於從 information_schema.tables 檢視中檢索關於表格的 metadata。

  • INFORMATION_SCHEMA> select table_name, table_type, row_count: 這行程式碼是 SQL 查詢，用於選擇表格的名稱 (table_name)、表格型別 (table_type) 和列數 (row_count)。
  • from tables: 指定從 tables 檢視中檢索資料。在 Snowflake 中，tables 檢視位於 information_schema schema 中，包含了資料函式庫中所有表格的 metadata。
  • where table_schema = ‘PUBLIC’: 這個 WHERE 子句用於篩選結果，只顯示 table_schema 欄位為 'PUBLIC' 的表格。table_schema 欄位表示表格所屬的 schema 名稱。

從查詢結果可以看出，information_schema.tables 檢視同時傳回了表格和檢視的資訊。如果你只想查詢檢視的資訊，你可以查詢 information_schema.views：

INFORMATION_SCHEMA>select table_name,
substr(view_definition, 1, 100) view_def
from views
where table_schema = 'PUBLIC';
+
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| TABLE_NAME           | VIEW_DEF                                |
|
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--|
| BIG_SPENDERS_1998_VW | create view big_spenders_1998_vw       |
|                      | (custkey, cust_name,                   |
|                      | total_order_dollars) as                 |
|                      | select o_custkey, c.                    |
| EMPLOYEE_MANAGER_VW  | create view employee_manager_vw          |
|                      | (empid, emp_name, mgr_empid,            |
|                      | salary_range) as                        |
|                      | select empid, emp_nam                   |
| EMPLOYEE_VW          | create view employee_vw                   |
|                      | as                                      |
|                      | select empid, emp_name, mgr_empid,      |
|                      | inactive from employee;                 |
| ORDER_CALCULATION_VW | create view order_calculation_vw          |
|                      | as                                      |
|                      | select o.o_orderkey,                    |
|                      | sum((li.l_extendedprice * (1 - li.l_d   |
| PERSON_VW            | create view person_vw (fname, lname,     |
|                      | dob, eyes) as                          |
|                      | select first_name, last_name, birth_dat |
| YEARLY_PART_SALES_VW | create view yearly_part_sales_vw          |
|                      | as                                      |
|                      | with part_supply as                     |
|                      | (select p.p_partkey as partkey,         |
|                      | p.p_nam                                 |
+
---
-
---
-
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+

這個 SQL 查詢是用於從 information_schema.views 檢視中檢索關於檢視的 metadata。

  • INFORMATION_SCHEMA> select table_name, substr(view_definition, 1, 100) view_def: 這行程式碼是 SQL 查詢，用於選擇檢視的名稱 (table_name) 和檢視定義 (view_definition) 的前 100 個字元。substr(view_definition, 1, 100) 用於擷取檢視定義的前 100 個字元，以避免顯示過長的字串。
  • from views: 指定從 views 檢視中檢索資料。在 Snowflake 中，views 檢視位於 information_schema schema 中，包含了資料函式庫中所有檢視的 metadata。
  • where table_schema = ‘PUBLIC’: 這個 WHERE 子句用於篩選結果，只顯示 table_schema 欄位為 'PUBLIC' 的檢視。table_schema 欄位表示檢視所屬的 schema 名稱。

善用 Metadata，提升資料價值

透過 information_schema，你可以輕鬆地查詢 Snowflake 中的 Metadata，瞭解資料函式庫的結構和內容。這些資訊對於資料管理、資料治理和資料分析都非常重要。

玄貓建議，善用 Snowflake 的 Metadata 功能，可以幫助你更好地理解和管理你的資料，從而提升資料的價值。

希望這篇文章對你有所幫助！

玄貓 BlackCat

玄貓 BlackCat

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