在資料函式庫系統中，資料安全與存取控制至關重要。Snowflake 的視窗功能提供了一個強大的機制，可以有效地管理和保護敏感資料，同時簡化查詢和提升效能。透過視窗，我們可以根據不同的使用者角色和需求，建立不同的資料檢視，限制對敏感欄位的存取，並簡化複雜的查詢邏輯。對於需要頻繁存取的彙總資料，實體化視窗更能大幅提升查詢效能。然而，在使用視窗時，也需要注意效能的影響，例如避免在過大的資料表上建立過於複雜的視窗，並定期監控視窗的查詢效能。
透過 Snowflake 視窗：資料安全與彈性存取策略
在資料倉管理中，**視窗（Views）**扮演著橋樑的角色，它不僅簡化了複雜查詢，更在資料安全上提供了多一層的保護。今天，身為玄貓，我想跟大家分享如何透過 Snowflake 的視窗功能，開發更安全、更彈性的資料存取策略。
視窗的本質：不只是查詢的化身
視窗，你可以把它想像成一個虛擬的表。它根據一個或多個表的查詢結果，但本身不儲存實際資料。這意味著，你可以像操作真實表一樣查詢視窗，但所有操作最終都會反映到基底表上。
先來看看幾個簡單的例子。假設我們有個 Person 表，我們可以建立一個視窗來顯示部分欄位：
CREATE VIEW person_vw AS
SELECT fname, lname, dob, eyes
FROM Person;
這個視窗只顯示了 Person 表中的 fname、lname、dob 和 eyes 欄位。當我們查詢這個視窗時，看到的結果如下：
describe person_vw;
結果會顯示視窗中定義的欄位名稱，而不是原始 Person 表的欄位名稱。
複雜查詢的簡化：開發你的專屬資料儀錶板
視窗的強大之處不僅在於簡化查詢，更在於可以將複雜的查詢邏輯封裝起來。例如，我們可以建立一個視窗來找出 1998 年消費超過 50 萬美元的大客戶：
CREATE VIEW big_spenders_1998_vw (custkey, cust_name, total_order_dollars) AS
SELECT o_custkey, c.c_name, SUM(o_totalprice)
FROM orders AS o
INNER JOIN customer AS c
ON o.o_custkey = c.c_custkey
WHERE 1998 = date_part(year, o.o_orderdate)
GROUP BY o.o_custkey, c.c_name
HAVING SUM(o_totalprice) >= 500000;
這個視窗結合了 orders 和 customer 表，並使用 WHERE、GROUP BY 和 HAVING 子句進行過濾和聚合。行銷和業務部門可以利用這個視窗快速識別潛在的銷售物件。
查詢這個視窗的結果如下：
SELECT * FROM big_spenders_1998_vw;
視窗的應用：無所不在的資料存取
視窗可以像表一樣在查詢中使用，這意味著你可以將視窗與視窗、視窗與表進行連線，甚至在通用表表達式（Common Table Expressions, CTE）中使用。
以下是一個連線 person_vw 和 employee_vw 視窗的例子：
SELECT p.fname, p.lname, e.empid
FROM person_vw AS p
INNER JOIN employee_vw AS e
ON e.emp_name = concat(p.fname,' ',p.lname);
這個查詢將兩個視窗連線起來，檢索員工的姓名和 ID。
你也可以在 CTE 中使用視窗，並將其與表連線：
WITH p AS (
SELECT concat(fname,' ',lname) AS full_name, dob
FROM person_vw
)
SELECT p.full_name, p.dob, e.empid
FROM p
INNER JOIN employee AS e
ON p.full_name = e.emp_name;
雖然 Snowflake 不允許透過視窗直接修改資料，但你可以在 SELECT 陳述式中像使用表一樣靈活地使用視窗。
為何要用視窗？資料安全與存取控制
既然視窗可以像表一樣使用，那麼使用視窗的好處是什麼呢？答案是：資料安全和存取控制。
視窗可以限制對欄位和列的存取。接下來，我將示範如何使用視窗來限制對敏感資料的存取。
首先，我們在 Employee 表中新增一個 salary 欄位：
ALTER TABLE employee ADD salary NUMBER(7,0);
然後，我們為每位員工分配一個薪水，使用 uniform() 函式產生一個 50,000 到 200,000 之間的隨機數：
UPDATE employee
SET salary = uniform(50000, 200000, random());
現在，Employee 表中包含了每位員工的薪水。但並非所有人都應該有權存取這些敏感資訊。假設人資部門希望主管可以看到員工的薪水範圍（而不是實際薪水），而一般員工則無法存取薪水資訊。
為瞭解決這個問題，我們可以建立一個 employee_manager_vw 視窗，讓主管透過這個視窗存取員薪水訊。
利用 Snowflake 視窗化管理敏感資料：玄貓的安全實戰
在資料安全至上的時代，如何精細控制不同使用者對資料的存取許可權，是每個資料倉管理員都必須面對的挑戰。 Snowflake 提供的視窗（Views）功能，不僅能簡化複雜查詢，更能成為保護敏感資料的利器。身為玄貓，我將分享如何運用 Snowflake 視窗，開發多層次的安全防護網。
薪資資訊保密：HR 與主管的許可權分級
假設我們有一個 employee 資料表，其中包含員工的薪資等敏感資訊。為了保護員工隱私，同時滿足不同部門的資料需求，我們可以建立多個視窗：
employee_manager_vw： 這個視窗提供給部門主管，顯示員工的基本資訊和薪資範圍，但不顯示具體薪資數字。
create view employee_manager_vw
(empid, emp_name, mgr_empid, salary_range)
as
select empid, emp_name, mgr_empid,
case
when salary <= 75000 then 'Low'
when salary <= 125000 then 'Medium'
else 'High'
end as salary_range
from employee;
內容解密：
- 這個視窗利用
CASE語法，將薪資分為Low、Medium和High三個範圍，隱藏了實際薪資金額。 empid、emp_name和mgr_empid欄位則提供員工的基本資訊和管理結構。
employee_vw： 這個視窗提供給非主管的員工，僅顯示員工的基本資訊，完全不包含薪資相關的欄位。
create view employee_vw as
select empid, emp_name, mgr_empid, inactive
from employee;
內容解密：
- 這個視窗只選擇了
empid、emp_name、mgr_empid和inactive欄位，完全排除了薪資資訊。 - 透過復原非主管員工對
employee資料表的直接存取權，並授予他們對employee_vw視窗的存取權，我們可以確保敏感資料不外洩。
透過這兩個視窗，我們成功地將薪資資訊的存取許可權限制在人資部門和部門主管，有效降低了資料外洩的風險。
行級存取控制：確保部門主管只能看到自己部門的資料
除了限制欄位存取，有時候我們還需要限制特定使用者只能存取特定列的資料。例如，我們希望部門主管只能看到自己部門的員薪水料。 Snowflake 提供的安全視窗（Secure Views）功能，可以輕鬆實作這個需求。
首先，我們需要建立兩個新的資料表：
department： 這個資料表儲存部門資訊，包括部門 ID、部門名稱和授權使用者名稱。
create table department as
select dept_id, dept_name, auth_username
from (values (1, 'ACCOUNTING', 'JOHNSMITH'),
(2, 'FINANCE', 'ALANBEAU'),
(3, 'SHIPPING', 'JANEDOE'))
as dept(dept_id, dept_name, auth_username);
內容解密：
- 這個資料表使用
VALUES語法，直接插入了三個部門的資料，包括會計部門、財務部門和運輸部門。 auth_username欄位指定了負責管理該部門資料的使用者名稱。
empl_dept： 這個資料表儲存員工與部門的關聯資訊，包括員工 ID 和部門 ID。
create table empl_dept as
select empid, dept_id
from (values (1001, 1), (1002, 2), (1003, 1),
(1004, 3), (1005, 2), (1006, 3))
as empdpt(empid, dept_id);
內容解密：
- 這個資料表也使用
VALUES語法，直接插入了員工與部門的關聯資料。 - 例如，員工 ID 為
1001的員工屬於部門 ID 為1的部門。
接下來，我們可以建立一個安全視窗 my_employees，讓部門主管只能看到自己部門的員薪水料：
create secure view my_employees as
select e.empid, e.emp_name, d.dept_name
from employee e
inner join empl_dept ed on e.empid = ed.empid
inner join department d on ed.dept_id = d.dept_id
where d.auth_username = current_user();
內容解密：
- 這個視窗使用
current_user()函式，取得目前使用者的名稱。 WHERE子句限制了只有department資料表中auth_username欄位與目前使用者名稱相同的資料才會被顯示。- 透過這個安全視窗，每個部門主管只能看到自己部門的員薪水料，有效保護了員工隱私。
玄貓的經驗分享：安全視窗的效能考量
身為玄貓，我必須提醒大家，安全視窗雖然提供了強大的資料保護功能，但也會對查詢效能產生一定的影響。 Snowflake 在執行安全視窗查詢時，需要額外進行許可權檢查，這會增加查詢的執行時間。
因此，在使用安全視窗時，我們需要在資料安全和查詢效能之間取得平衡。以下是一些建議：
- 避免在大型資料表上建立安全視窗： 如果資料表非常大，安全視窗的許可權檢查可能會導致查詢效能大幅下降。
- 盡可能簡化安全視窗的查詢邏輯： 複雜的查詢邏輯會增加安全視窗的執行時間。
- 定期監控安全視窗的查詢效能： 透過 Snowflake 提供的查詢歷史記錄功能，我們可以定期監控安全視窗的查詢效能，並根據需要進行調整。
總而言之， Snowflake 視窗是一個功能強大的資料保護工具，可以幫助我們建立多層次的資料安全防護網。只要我們善用視窗的各種功能，並注意效能考量，就能在確保資料安全的同時，也能維持良好的查詢效能。
資料函式庫視窗的妙用：玄貓的效能、安全與簡化之道
身為一個資料函式庫架構師，我經常需要面對資料存取控制、效能最佳化和複雜度管理等多重挑戰。在眾多工具中，資料函式庫視窗（Views）扮演著一個不可或缺的角色。它們不僅僅是簡單的查詢儲存，更是提升資料函式庫應用程式靈活性和安全性的關鍵。讓玄貓來分享一下，如何善用 Views 來解決實際問題。
隱藏資料，滴水不漏：安全視窗的實戰應用
在資料安全至上的時代，如何確保敏感資料不被未授權的使用者存取至關重要。傳統上，我們可能會透過複雜的許可權管理來限制對底層資料表的直接存取。但這種方式往往難以維護，與容易出錯。
這時，安全視窗（Secure Views）就派上用場了。安全視窗不僅可以限制資料的存取，還可以隱藏視窗的定義，增加安全性。
舉例來說，假設我們有一個 employee 資料表，其中包含所有員工的薪資資訊。為了保護這些敏感資料，我們可以建立一個安全視窗，只允許特定部門的經理檢視其部門員工的資料：
CREATE OR REPLACE SECURE VIEW my_employees AS
SELECT
e.empid,
e.emp_name,
d.dept_name
FROM employee e
INNER JOIN empl_dept ed ON e.empid = ed.empid
INNER JOIN department d ON ed.dept_id = d.dept_id
WHERE d.auth_username = current_user();
這段程式碼中，CREATE OR REPLACE SECURE VIEW 指令建立一個名為 my_employees 的安全視窗。這個視窗只會顯示目前使用者所屬部門的員薪水料。WHERE d.auth_username = current_user() 確保只有當前使用者才能看到與其使用者名稱相符的部門資料。
玄貓在這裡要提醒一下，雖然這個範例為了簡化使用了使用者名稱，但在實際應用中，更常見的做法是使用角色（Roles）來管理許可權。
另一個優點是，安全視窗可以限制誰能看到視窗的定義。只有與建立視窗的角色相同的使用者才能從 Snowflake 檢索視窗定義。以下是如何檢索視窗定義的一個範例：
PUBLIC> SELECT GET_DDL('VIEW','my_employees');
玄貓可以檢索視窗定義，因為我是建立視窗的人，但沒有相同角色的其他使用者將無法成功。隱藏視窗定義是有意義的，因為如果您正在建立一種機制來限制對資料的存取，您可能還應該限制對用於執行此操作的機制的存取。
化繁為簡，洞悉全域性：資料彙總視窗的應用
資料分析和報表通常需要彙總資料。如果資料函式庫設計不佳，這可能會導致非常複雜的查詢。與其讓使用者直接存取資料表，不如建立視窗，讓資料看起來像是已經預先彙總過。
例如，如果零件部門的經理需要執行報表，顯示公司銷售的所有零件的總銷售額和可用數量，您可以建立以下視窗：
CREATE VIEW yearly_part_sales_vw AS
WITH part_supply AS (
SELECT
p.p_partkey AS partkey,
p.p_name AS part_name,
SUM(ps.ps_availqty) AS avail_qty
FROM part p
INNER JOIN partsupp ps ON p.p_partkey = ps.ps_partkey
GROUP BY p.p_partkey, p.p_name
),
part_sales AS (
SELECT
DATE_PART(year, o.o_orderdate) AS order_year,
li.l_partkey AS partkey,
COUNT(*) AS sales_qty,
MAX(li.l_shipdate - o.o_orderdate) max_backlog_days
FROM orders o
INNER JOIN lineitem li ON o.o_orderkey = li.l_orderkey
GROUP BY DATE_PART(year, o_orderdate), li.l_partkey
)
SELECT
p_sply.partkey,
p_sply.part_name,
p_sply.avail_qty,
p_sale.order_year,
p_sale.sales_qty,
p_sale.max_backlog_days
FROM part_supply p_sply
INNER JOIN part_sales p_sale ON p_sply.partkey = p_sale.partkey;
這個視窗將零件供應和銷售資料彙總在一起，讓經理可以輕鬆地查詢所需的資訊。例如，經理可以使用這個視窗來找出 1998 年最暢銷的 10 個零件及其可用數量：
SELECT partkey, sales_qty, avail_qty
FROM yearly_part_sales_vw
WHERE order_year = 1998
ORDER BY sales_qty DESC
LIMIT 10;
如果經理經常使用這個視窗，您可以建立一個資料表來儲存預先彙總的資料，而不是每次都產生分組的成本。
實體化視窗：用空間換取時間的藝術
對於需要頻繁查詢的彙總資料，我們可以考慮使用實體化視窗（Materialized Views）。實體化視窗本質上是一個使用視窗定義建立的資料表，然後透過背景程式自動重新整理。
使用實體化視窗通常會減少查詢的計算時間，但會產生額外的儲存空間/費用和計算費用來維護實體化視窗，因此重要的是要考慮查詢實體化資料的次數與儲存的資料量以及資料變更的頻率。
建立實體化視窗很簡單，只需使用 CREATE MATERIALIZED VIEW 而不是 CREATE VIEW。建立後，Snowflake 會在對視窗定義的基礎資料表進行變更時更新儲存的資料。例如，如果 yearly_part_sales_vw 視窗被刪除並重新建立為實體化視窗，則未來對 Orders、Lineitem、Part 和 Partsupp 資料表所做的任何變更都需要反映在實體化視窗資料中。 Snowflake 會為您進行這些更新，以便對實體化視窗的任何查詢都將傳回目前資料。
隱藏複雜性：簡化資料存取的橋樑
視窗的另一個常見用途是保護您的使用者社群免受複雜的計算或資料關係的影響。例如，計算客戶訂單成本的過程可能包括定價、折扣、促銷價格、州稅計算等。由於向公司收取正確的金額至關重要，因此為業務部門建立一個包含包含各種計算的欄的視窗可能是有益的，例如：
CREATE VIEW order_calculation_vw AS
SELECT
o.o_orderkey,
SUM((li.l_extendedprice * (1 - li.l_discount)) * li.l_quantity) order_total,
SUM((li.l_extendedprice * (1 - li.l_discount)) * li.l_quantity * li.l_tax) state_sales_tax
FROM orders o
INNER JOIN lineitem li ON o.o_orderkey = li.l_orderkey
GROUP BY o.o_orderkey;
