Snowflake 作為雲端資料倉儲解決方案，其交易和檢視機制對於資料管理至關重要。交易確保資料操作的原子性、一致性、隔離性和永續性，而檢視則提供資料存取的抽象層，簡化查詢並提升安全性。理解這些機制，能有效提升資料函式庫操作效率和資料品質。本文將詳細解析 Snowflake 中交易的運作方式，包含明確交易、隱含交易、自動提交模式，以及交易隔離級別和鎖定機制，並探討如何避免死鎖和設定鎖定等待時間。此外，文章也將介紹檢視的建立方法和使用場景，說明如何透過檢視簡化資料查詢和提升資料安全性。
交易（Transactions）深度解析
交易是將一系列SQL陳述式作為單一工作單元執行的機制，確保所有變更要麼全部成功，要麼全部失敗。本章將探討交易的概念及其在Snowflake中的運作方式。
交易的本質與重要性
交易是一組在單一資料函式庫會話中執行的SQL陳述式，旨在確保資料的一致性和完整性。考慮一個典型的銀行轉帳場景：從儲蓄帳戶轉出資金並存入支票帳戶。如果轉出成功但轉入失敗，資金將會遺失。交易機制透過確保這兩個操作作為一個整體執行，避免了這種情況的發生。
交易運作流程範例
Begin Transaction
Update Savings_Account (remove $100 as long as balance >= $100)
Update Checking_Account (add $100)
If errors then
Rollback Transaction
Else
Commit Transaction
End If
此範例展示了交易如何確保資金轉移的原子性。
明確交易與隱含交易
Snowflake支援兩種交易模式：明確交易和隱含交易。
明確交易
明確交易需要使用BEGIN TRANSACTION陳述式開始，並在完成後使用COMMIT或ROLLBACK陳述式結束。以下是一個明確交易的範例：
PUBLIC>begin transaction;
+
---
-
---
-
---
-
---
-
---
-
---
--+
| status |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| Statement executed successfully. |
+
---
-
---
-
---
-
---
-
---
-
---
--+
PUBLIC>insert into person (first_name, last_name, birth_date, eye_color, occupation)
values ('John','Sanford','2002-03-22'::date, 'brown','analyst');
+
---
-
---
-
---
-
---
-
---
-
---
--+
| number of rows inserted |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| 1 |
+
---
-
---
-
---
-
---
-
---
-
---
--+
PUBLIC>insert into employee (empid, emp_name, mgr_empid)
values (1007, 'John Sanford',1002);
+
---
-
---
-
---
-
---
-
---
-
---
--+
| number of rows inserted |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| 1 |
+
---
-
---
-
---
-
---
-
---
-
---
--+
PUBLIC>commit;
+
---
-
---
-
---
-
---
-
---
-
---
--+
| status |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| Statement executed successfully. |
+
---
-
---
-
---
-
---
-
---
-
---
--+
隱含交易
如果在未啟動交易的情況下執行INSERT、UPDATE、DELETE或MERGE陳述式，Snowflake將自動啟動一個隱含交易。隱含交易的結束取決於會話是否處於自動提交（autocommit）模式。
自動提交模式解析
自動提交模式是Snowflake的預設設定。在此模式下，每個修改資料函式庫的操作將被個別提交或回復。可以使用SHOW PARAMETERS LIKE 'autocommit';命令檢查當前會話的自動提交設定：
PUBLIC>show parameters like 'autocommit';
+
---
-
---
-
---
-+
---
-
---
+
---
-
---
--+
---
-
---
--+
---
-
---
-
---
--+
| key | value | default | level | description |
|
---
-
---
-
---
-+
---
-
---
+
---
-
---
--+
---
-
---
--+
---
-
---
-
---
--|
| AUTOCOMMIT | true | true | SESSION | ... |
+
---
-
---
-
---
-+
---
-
---
+
---
-
---
--+
---
-
---
--+
---
-
---
-
---
--+
若要停用自動提交，可以使用以下命令：
PUBLIC>alter session set autocommit = false;
+
---
-
---
-
---
-
---
-
---
-
---
--+
| status |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| Statement executed successfully. |
+
---
-
---
-
---
-
---
-
---
-
---
--+
交易控制與管理
在非自動提交模式下，隱含交易的結束取決於多種情況，包括執行DDL命令、更改自動提交設定、顯式啟動新交易、結束會話或執行COMMIT/ROLLBACK陳述式。
交易行為控制關鍵點
- 執行DDL命令（如
CREATE TABLE）會觸發隱含的COMMIT。 - 更改自動提交設定會觸發隱含的
COMMIT。 - 顯式啟動新交易會觸發隱含的
COMMIT。 - 結束會話會回復任何未提交的變更。
內容解密：
上述範例與說明展示了Snowflake中交易的運作原理，包括明確交易和隱含交易的差異，以及如何透過控制自動提交模式來管理交易的行為。開發者在設計資料函式庫操作時，應充分考慮交易的原子性，以確保資料的一致性和可靠性。
圖表翻譯：
此圖示展示了Snowflake中交易流程的邏輯關係，包括明確交易與隱含交易的控制路徑以及自動提交模式的影響。
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title Snowflake 交易與檢視深度解析
package "SQL 資料庫架構" {
package "資料定義 DDL" {
component [CREATE] as create
component [ALTER] as alter
component [DROP] as drop
}
package "資料操作 DML" {
component [SELECT] as select
component [INSERT] as insert
component [UPDATE] as update
component [DELETE] as delete
}
package "進階查詢" {
component [JOIN 連接] as join
component [GROUP BY 分組] as group
component [子查詢] as subquery
component [窗口函數] as window
}
database [資料表] as table
}
create --> table : 建立結構
select --> join : 多表查詢
join --> group : 聚合統計
group --> window : 分析函數
insert --> table : 新增資料
update --> table : 修改資料
delete --> table : 刪除資料
note right of join
INNER JOIN 交集
LEFT JOIN 左表全部
RIGHT JOIN 右表全部
end note
@enduml圖表翻譯： 此圖示詳細闡述了Snowflake中的交易流程，包括明確交易與隱含交易的分支，以及不同模式下的處理邏輯。其中，明確交易需要開發者手動控制交易的開始與結束，而隱含交易則由Snowflake根據會話狀態自動管理。自動提交模式進一步影響了隱含交易的行為。透過理解這些控制流程，開發者可以更有效地設計和管理資料函式庫操作。
Snowflake 交易處理最佳實踐
Snowflake 的交易處理機制對於資料函式倉管理至關重要。瞭解如何有效地使用交易，可以避免許多潛在的問題。
交易處理最佳實踐
為了確保資料的一致性和完整性，建議遵循以下最佳實踐：
- 明確開始交易：使用
BEGIN TRANSACTION陳述式明確開始交易。 - 解決交易：在結束會話之前，使用
COMMIT或ROLLBACK陳述式解決所有交易。 - 關閉開放交易：在發出 DDL 命令之前，明確關閉任何開放交易。
查詢開放交易
Snowflake 提供了 SHOW TRANSACTIONS 陳述式來列出任何開放交易。以下是一個範例：
PUBLIC>BEGIN TRANSACTION;
PUBLIC>UPDATE employee SET mgr_empid = 1004 WHERE empid = 1007;
PUBLIC>SHOW TRANSACTIONS;
輸出結果將顯示當前開放交易的相關資訊，包括交易 ID、使用者、會話 ID 等。
程式碼解密：
BEGIN TRANSACTION;用於開始一個新的交易。UPDATE employee SET mgr_empid = 1004 WHERE empid = 1007;更新了employee表中的一筆記錄，將empid為 1007 的員工的mgr_empid更新為 1004。SHOW TRANSACTIONS;用於查詢當前開放的交易。
交易隔離級別
Snowflake 的隔離級別為 READ COMMITTED，這意味著一個陳述式只能看到已提交的更改。即使查詢需要一個小時才能完成，伺服器也必須保證在陳述式開始執行後所做的更改對查詢不可見。
需要注意的事項：
- 在同一個交易中執行的 SQL 陳述式可以看到未提交的更改。
- 在同一個交易中的多個陳述式可能會看到不同的資料檢視，因為其他交易可能會提交更改。
鎖定機制
Snowflake 使用鎖定機制來防止多個會話修改相同的資料。當一個使用者更新表中的一行時，鎖定將一直保持到交易結束，以防止其他交易修改相同的行。
Snowflake 的鎖定方案介於表級鎖定和頁級鎖定之間。Snowflake 自動將表分成稱為微分割槽（micropartitions）的部分，每個微分割槽包含 50MB 到 500MB 的未壓縮資料。如果多個交易嘗試修改或刪除同一微分割槽中的資料，一個會話將被阻塞，必須等待其他會話的交易完成。
鎖定等待時間
當一個會話被阻塞等待鎖定釋放時，它將等待一段可組態的時間，然後如果鎖定尚未釋放，則會失敗。可以使用 LOCK_TIMEOUT 引數設定最大等待時間（以秒為單位）。
PUBLIC>ALTER SESSION SET lock_timeout=600;
這條陳述式將最大鎖定等待時間設定為 10 分鐘。
死鎖
死鎖是一種情況，其中會話 A 正在等待由會話 B 持有的鎖定，而會話 B 正在等待由會話 A 持有的鎖定。Snowflake 將選擇具有最新陳述式的會話作為受害者，從而允許其他交易繼續進行。
如果遇到死鎖情況，可以使用 SYSTEM$ABORT_TRANSACTION() 系統函式來終止特定的交易。
SYSTEM$ABORT_TRANSACTION(transaction_id);
程式碼解密：
ALTER SESSION SET lock_timeout=600;用於設定當前會話的鎖定超時時間為 600 秒（10 分鐘）。SYSTEM$ABORT_TRANSACTION(transaction_id);用於終止指定的交易，transaction_id是要終止的交易的 ID。
檢視（Views）
在設計良好的應用程式中，通常會暴露公共介面同時保持實作細節的私密性，以隔離終端使用者與複雜性及未來變更的影響。在 Snowflake 中，資料儲存在表格中，但可以透過一組檢視（及/或表格函式，第 17 章將會討論）提供對資料的存取。本章將討論檢視是什麼、如何建立檢視，以及何時及如何使用它們。
什麼是檢視？
檢視是一種類別似於表格的資料函式庫物件，但檢視只能被查詢。檢視不涉及任何資料儲存（除了物化檢視，將在後面討論）。一種思考檢視的方式是將其視為儲存在資料函式庫中的命名查詢，以便於使用。如果您在每個月的最後一個工作日執行報告，您可以建立一個包含用於產生報告的查詢的檢視，然後每個月查詢該檢視。這只是檢視的幾種用途之一，本章將討論其他用途。
建立檢視
檢視是使用 CREATE VIEW 陳述式建立的，該陳述式本質上是一個名稱後跟一個查詢。以下是建立包含 Employee 表格中四個欄位的簡單檢視範例：
CREATE VIEW employee_vw
AS
SELECT empid, emp_name, mgr_empid, inactive
FROM employee;
內容解密：
CREATE VIEW employee_vw：建立一個名為employee_vw的檢視。AS SELECT empid, emp_name, mgr_empid, inactive FROM employee;：定義檢視的查詢，從employee表格中選取指定的欄位。
執行結果：
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| status |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-|
| View EMPLOYEE_VW successfully created. |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
employee_vw 的輸出結果與表格的輸出結果相同：
DESCRIBE employee_vw;
內容解密：
DESCRIBE employee_vw;：描述employee_vw檢視的結構。- 輸出結果顯示了檢視中的欄位名稱和資料型別，這些是從基礎表格
Employee衍生而來的。
執行結果：
+
---
-
---
-
---
+
---
-
---
-
---
--+
---
-
---
-+
---
-
---
+
---
-
---
--+
| name | type | kind | null? | default |
|
---
-
---
-
---
+
---
-
---
-
---
--+
---
-
---
-+
---
-
---
+
---
-
---
--|
| EMPID | NUMBER(4,0) | COLUMN | Y | NULL |
| EMP_NAME | VARCHAR(14) | COLUMN | Y | NULL |
| MGR_EMPID | NUMBER(4,0) | COLUMN | Y | NULL |
| INACTIVE | VARCHAR(1) | COLUMN | Y | NULL |
+
---
-
---
-
---
+
---
-
---
-
---
--+
---
-
---
-+
---
-
---
+
---
-
---
--+
同樣地，可以像查詢表格一樣查詢檢視：
SELECT * FROM employee_vw;
內容解密：
SELECT * FROM employee_vw;：從employee_vw檢視中選取所有欄位。- 輸出結果顯示了檢視中的資料。
執行結果：
+
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
---
+
| EMPID | EMP_NAME | MGR_EMPID | INACTIVE |
|
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
---
|
| 1001 | Bob Smith | NULL | NULL |
| 1002 | Susan Jackson | 1001 | Y |
| 1003 | Greg Carpenter | 1001 | Y |
| 1004 | Robert Butler | 1002 | Y |
| 1005 | Kim Josephs | 1003 | Y |
| 1006 | John Tyler | 1004 | Y |
| 9999 | Tim Traveler | 1006 | NULL |
| 1007 | John Sanford | 1002 | NULL |
+
---
-
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
+
---
-
---
---
+
在定義檢視時，您可以選擇為檢視欄位提供自己的名稱，而不是從基礎表格中衍生它們。以下是另一個建立在 Person 表格上的檢視範例，並為每個欄位提供了替代名稱：
CREATE VIEW person_vw (fname, lname, dob, eyes)
AS
SELECT first_name, last_name, birth_date, eye_color
FROM person;
內容解密：
CREATE VIEW person_vw (fname, lname, dob, eyes)：建立一個名為person_vw的檢視，並指定自定義的欄位名稱。AS SELECT first_name, last_name, birth_date, eye_color FROM person;：定義檢視的查詢，從person表格中選取指定的欄位。
執行結果：
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| status |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
|
| View PERSON_VW successfully created. |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
