Snowflake 資料表操作與 Time Travel 功能

– 8 分鐘閱讀

Snowflake 的 Time Travel 功能允許使用者查詢過去一段時間內的資料狀態，這對於資料還原和稽核追蹤至關重要。當誤刪資料時，可以利用 Time Travel 功能輕鬆還原資料。文章首先介紹瞭如何使用 DELETE 陳述式刪除資料，接著說明如何使用 AT 子句配合 OFFSET 引數查詢特定時間點的資料表狀態，並使用 INSERT INTO 陳述式將過去的資料重新插入資料表，實作資料還原。除了資料還原，文章也涵蓋了 UPDATE 陳述式的用法，包含單表更新和多表連線更新，並提醒開發者注意多表連線更新可能產生的非預期結果。最後，文章詳細講解了 MERGE 陳述式，它能夠在單一操作中完成插入、更新和刪除資料，簡化資料合併流程，並提供一個實際案例說明如何使用 MERGE 陳述式合併兩個資料表。

資料刪除與還原：Snowflake 的 Time Travel 功能

在資料函式倉管理中，刪除資料是一項常見的操作。然而，一旦資料被刪除，要還原它們就變得相當困難。幸運的是，Snowflake 提供了一個名為 Time Travel 的功能，讓使用者能夠檢視資料在特定時間點的狀態。

使用 DELETE 陳述式刪除資料

在 Snowflake 中，可以使用 DELETE 陳述式從資料表中刪除特定的資料列。例如，若要從 Person 資料表中刪除與 Employee 資料表中相同名字的資料列，可以使用以下 SQL 陳述式：

DELETE FROM person
USING employee
WHERE employee.emp_name = CONCAT(person.first_name, ' ', person.last_name);

內容解密：

  • DELETE FROM person 指定了要從 Person 資料表中刪除資料。
  • USING employee 表示使用 Employee 資料表來進行連線操作。
  • WHERE 子句中的條件 employee.emp_name = CONCAT(person.first_name, ' ', person.last_name) 用於匹配兩個資料表中的名字。
  • CONCAT 函式用於將 Person 資料表中的 first_name 和 last_name 合併成一個完整的姓名，以便與 Employee 資料表中的 emp_name 進行比較。

Time Travel：還原被刪除的資料

如果在刪除資料後發現需要還原它們，Snowflake 的 Time Travel 功能可以幫助實作這一點。透過使用 AT 子句，可以查詢特定時間點的資料狀態。例如，若要查詢 10 分鐘前的 Employee 資料表狀態，可以使用以下 SQL 陳述式：

SELECT * FROM employee AT(OFFSET => -600)
WHERE emp_name = 'Greg Carpenter';

內容解密：

  • SELECT * FROM employee AT(OFFSET => -600) 表示查詢 Employee 資料表在 600 秒（即 10 分鐘）前的狀態。
  • WHERE emp_name = 'Greg Carpenter' 用於篩選出特定的員薪水料。

查詢到需要的資料後，可以使用 INSERT INTO 陳述式將這些資料重新插入到資料表中：

INSERT INTO employee
SELECT * FROM employee AT(OFFSET => -600)
WHERE emp_name = 'Greg Carpenter';

內容解密：

  • INSERT INTO employee 指定了要插入資料的目標資料表。
  • 子查詢 SELECT * FROM employee AT(OFFSET => -600) WHERE emp_name = 'Greg Carpenter' 用於檢索 10 分鐘前被刪除的 Greg Carpenter 的資料。

更新資料：使用 UPDATE 陳述式

除了刪除和還原資料外，更新現有的資料也是常見的操作。可以使用 UPDATE 陳述式來修改特定的資料列。例如，若要更新 Kathy Little 的職業和眼睛顏色，可以使用以下 SQL 陳述式：

UPDATE person
SET occupation = 'musician', eye_color = 'grey'
WHERE first_name = 'Kathy' AND last_name = 'Little';

內容解密：

  • UPDATE person 指定了要更新的資料表。
  • SET occupation = 'musician', eye_color = 'grey' 定義了要更新的欄位及其新值。
  • WHERE first_name = 'Kathy' AND last_name = 'Little' 用於指定要更新的特定資料列。

結合其他資料表更新資料

Snowflake 也允許在 UPDATE 陳述式中使用 FROM 子句來結合其他資料表進行更新。例如，若要根據 Employee 資料表中的資訊更新 Person 資料表中 Bob Smith 的職業，可以使用以下 SQL 陳述式：

UPDATE person AS p
SET occupation = 'boss'
FROM employee AS e
WHERE e.emp_name = CONCAT(p.first_name, ' ', p.last_name)
AND e.mgr_empid IS NULL;

內容解密：

  • UPDATE person AS p 指定了要更新的資料表及其別名。
  • SET occupation = 'boss' 定義了要更新的欄位及其新值。
  • FROM employee AS e 表示結合 Employee 資料表進行更新。
  • WHERE 子句中的條件用於匹配兩個資料表中的名字，並進一步篩選出經理員工（即 mgr_empid IS NULL）。

資料更新與合併操作詳解

在資料函式倉管理中，更新和合併資料是常見的操作。本文將詳細介紹如何在 Snowflake 中使用 UPDATE 和 MERGE 陳述式來修改資料表中的資料。

使用 UPDATE 陳述式更新資料

UPDATE 陳述式用於修改資料表中的現有資料。以下是一個簡單的例子，展示如何更新 Person 表中的 occupation 欄位：

UPDATE person AS p
SET p.occupation = 'boss'
FROM employee AS e
WHERE e.first_name = p.first_name
AND e.last_name = p.last_name
AND e.mgr_empid IS NULL;

內容解密：

  • UPDATE person AS p：指定要更新的資料表為 person，並給予別名 p。
  • SET p.occupation = 'boss'：將 occupation 欄位的值設定為 'boss'。
  • FROM employee AS e：指定要連線的資料表為 employee，並給予別名 e。
  • WHERE e.first_name = p.first_name AND e.last_name = p.last_name AND e.mgr_empid IS NULL;：設定連線條件和篩選條件，只有當 employee 表中的 first_name 和 last_name 與 person 表相匹配，且 mgr_empid 為 NULL 時，才更新對應的 occupation 欄位。

多表連線更新的注意事項

當使用多表連線進行更新時，需要注意連線的結果是否符合預期。以下是一個可能導致非預期結果的例子：

UPDATE person AS p
SET p.years_of_education = e.empid - 1000
FROM employee AS e
WHERE e.empid < 1003;

內容解密：

  • 如果 person 表中的某一行與 employee 表中的多行相匹配，則該行將被多次更新，最終的值取決於最後一次更新的結果。
  • 在這個例子中，由於 empid < 1003 的條件導致多行匹配，因此所有 person 表中的行都被更新為相同的值。

使用 MERGE 陳述式合併資料

MERGE 陳述式允許在單一操作中插入、更新或刪除資料。以下是一個例子，展示如何使用 MERGE 陳述式根據 Person_Refresh 表的內容更新 Person 表：

MERGE INTO person AS p
USING person_refresh AS pr
ON p.first_name = pr.fname AND p.last_name = pr.lname
WHEN MATCHED AND pr.remove = 'yes' THEN DELETE
WHEN MATCHED THEN UPDATE SET p.birth_date = pr.dob,
                             p.eye_color = pr.eyes,
                             p.occupation = pr.profession
WHEN NOT MATCHED THEN INSERT (p.first_name, p.last_name, p.birth_date, p.eye_color, p.occupation)
                      VALUES (pr.fname, pr.lname, pr.dob, pr.eyes, pr.profession);

內容解密：

  • MERGE INTO person AS p USING person_refresh AS pr：指定目標表為 person，源表為 person_refresh。
  • ON p.first_name = pr.fname AND p.last_name = pr.lname：設定匹配條件。
  • WHEN MATCHED AND pr.remove = 'yes' THEN DELETE：如果匹配且 remove 欄位為 'yes'，則刪除該行。
  • WHEN MATCHED THEN UPDATE SET ...：如果匹配，則更新指定的欄位。
  • WHEN NOT MATCHED THEN INSERT ...：如果不匹配，則插入新行。

圖表翻譯：

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title Snowflake 資料表操作與 Time Travel 功能

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

圖表翻譯： 此圖示展示了 MERGE 陳述式的流程，首先檢查匹配條件，然後根據條件執行刪除、更新或插入操作。

資料生成、轉換與操作

在前一章中，我們探討了Snowflake中可用的不同資料型別，包括數值、時間和字元。本章將深入研究Snowflake內建的函式，用於生成、轉換和操作這些資料型別的值。

操作字元資料

Snowflake提供了豐富的函式來處理字元資料，無論是連線字串、搜尋子字串、轉換大小寫還是其他操作。

字串生成與操作

建立字串的方法很簡單，只需將字串內容用單引號括起來即可：

PUBLIC>select '這是我的字串';
+
---
-
---
-
---
-
---
-
---
--+
| '這是我的字串'      |
|
---
-
---
-
---
-
---
-
---
--|
| 這是我的字串       |
+
---
-
---
-
---
-
---
-
---
--+

如果需要連線兩個或多個字串，可以使用 || 運算子或內建的 concat() 函式：

PUBLIC>select '字串1' || ' 和 ' || '字串2';
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| '字串1' || ' 和 ' || '字串2'     |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-|
| 字串1 和 字串2                   |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+

PUBLIC>select concat('字串1',' 和 ','字串2');
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| CONCAT('字串1',' 和 ','字串2')    |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--|
| 字串1 和 字串2                    |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+

如果需要包含特殊字元，可以使用 char() 函式，透過ASCII值來插入特定字元。例如，若要在輸出中顯示歐元符號，可以這樣做：

PUBLIC>select concat('我在巴黎花了 ',char(8364),'357');
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+
| CONCAT('我在巴黎花了 ',CHAR(8364),'357')    |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
|
| 我在巴黎花了 €357                          |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
---
+

#### 內容解密：

此範例展示瞭如何使用 char() 函式插入特殊字元。char(8364) 傳回歐元符號（€），然後使用 concat() 將其與其他字串連線起來，形成完整的句子。

Snowflake還提供了多種函式來轉換字串的大小寫，例如轉換為全部大寫、全部小寫或每個單詞首字母大寫：

PUBLIC>select upper(str.val), lower(str.val), initcap(str.val)
from (values ('哪種大小寫最好？'))
as str(val);
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-+
| UPPER(STR.VAL) | LOWER(STR.VAL) | INITCAP(STR.VAL) |
|
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-|
| 哪種大小寫最好？  | 哪種大小寫最好？  | 哪種大小寫最好？  |
+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-+

#### 內容解密：

  • upper(str.val) 將輸入字串轉換為全部大寫。
  • lower(str.val) 將輸入字串轉換為全部小寫。
  • initcap(str.val) 將輸入字串的每個單詞首字母轉換為大寫。

此外，還有函式可以反轉字串的字元順序：

PUBLIC>select reverse('你能讀懂這個嗎？');
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| REVERSE('你能讀懂這個嗎？')    |
|
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-|
| ？麼嗎個這懂讀能你            |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-+

#### 內容解密：

reverse() 函式將輸入的字串字元順序完全反轉。

去除字串前後空格的需求也很常見，相關函式可以幫助實作這一點。

資料操作範例

在處理資料時，經常需要進行各種轉換和操作。以下是一些常見的操作範例。

合併與更新資料

在進行資料合併時，MERGE 陳述式非常有用。例如，將 Person_Refresh 表中的資料合併到 Person 表中：

MERGE INTO person AS p
USING person_refresh AS pr
ON p.first_name = pr.fname AND p.last_name = pr.lname
WHEN MATCHED AND pr.remove = 'yes' THEN DELETE
WHEN MATCHED THEN UPDATE SET
p.birth_date = pr.dob,
p.eye_color = pr.eyes,
p.occupation = pr.profession
WHEN NOT MATCHED THEN INSERT
(first_name, last_name, birth_date, eye_color, occupation)
VALUES (pr.fname, pr.lname, pr.dob, pr.eyes, pr.profession);

#### 內容解密：

此 MERGE 陳述式根據 Person_Refresh 表更新 Person 表的資料。

  • 當匹配到相同記錄且 remove 欄位為 ‘yes’ 時，刪除該記錄。
  • 當匹配到相同記錄時，更新相關欄位。
  • 當在 Person 表中找不到匹配記錄時，將新記錄插入 Person 表。

執行後，Snowflake會傳回插入、更新和刪除的記錄數量。

玄貓 BlackCat

玄貓 BlackCat

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