Snowflake 資料表操作：新增、刪除與更新實戰技巧

– 11 分鐘閱讀

Snowflake 提供了強大的資料表操作功能，讓資料管理更靈活高效。新增、刪除和更新資料是資料倉管理的日常，但操作不慎可能造成資料遺失或錯誤。本文將深入講解 Snowflake 的 INSERT、DELETE 和 UPDATE 陳述式，並分享一些玄貓的實戰技巧，讓你避免踩坑，提升資料倉管理效率。

在 Snowflake 中，新增資料使用 INSERT 陳述式。除了逐筆插入，更有效率的做法是批次插入多筆資料，尤其在資料遷移或大量資料載入的場景下，能大幅縮短操作時間。此外，INSERT OVERWRITE 陳述式可以清空資料表並插入新資料，適合重置資料表或全量更新資料。更進階的用法是結合 SELECT 陳述式，從其他資料表篩選或轉換資料後插入目標表，實作資料同步或轉換。

刪除資料則使用 DELETE 陳述式，搭配 WHERE 子句精確指定刪除條件，避免誤刪。USING 子句能連線其他資料表，根據關聯條件刪除資料，例如刪除特定員工在人事資料表中的紀錄。Snowflake 的 Time Travel 功能是資料安全的最後一道防線，即使誤刪資料，也能透過指定時間點查詢歷史資料並還原。

UPDATE 陳述式用於修改現有資料，SET 子句指定要更新的欄位和值，WHERE 子句限定更新範圍。同樣地，FROM 子句可以結合其他資料表，根據關聯條件更新資料，例如根據員工職等表更新人事資料表中的職稱。更新操作也需謹慎，務必確認 WHERE 子句的正確性，避免非預期修改。

數值資料的處理： Snowflake 的獨特之道

在 Snowflake 中，處理數值資料的方式相當簡潔，它提供了一種名為 number 的資料型別，能應付幾乎所有型別的數值需求。這種資料型別非常彈性，無論是帶有小數點的浮點數，或是沒有小數點的整數，都能輕鬆儲存。

  • 精確度與範圍：number 型別的欄位最多可以儲存 38 位數字。
  • 範例數值：
    • 0
    • –1
    • 99,999,999,999,999,999,999,999,999,999,999,999,999
    • 0.00000000000000000000000000000000001
    • 1,234,567.123456789012345678901234567890

精確度（precision）與比例（scale）

  • 精確度 指的是總位數，而 比例 則是小數點後面的位數。
  • 預設值：如果沒有明確指定精確度和比例，Snowflake 預設為 number(38,0)。
  • 客製化：可以根據實際需求調整，例如儲存介於 -999.99 到 999.99 之間的數字，可以設定欄位為 number(5,2)。
  • 儲存效率：Snowflake 的數值儲存是變動的，取決於實際使用的位數，因此將所有數值欄位都定義為 number(38,0) 並不會造成額外的資源浪費。

相容性考量

為了簡化從其他資料函式庫系統遷移資料的過程，Snowflake 接受以下資料型別：

  • decimal, numeric, real
  • tinyint, smallint, int, integer, bigint
  • double, float, float4, float8

不過，玄貓建議在定義新欄位時，整數使用 integer，浮點數使用 number，這樣可以提高程式碼的可讀性與維護性。

時間資料的藝術： Snowflake 的時間處理

除了字串和數字，時間資料在資料函式庫中也扮演著核心角色。時間資料記錄了日期和時間的資訊，應用範圍廣泛：

  • 生日：記錄一個人的出生年月日。
  • 事件時間：記錄事件發生的時間，例如工作班次的開始或結束時間。
  • 精確時間戳記：記錄汽車透過收費站的精確時間，包含日期、小時、分鐘、秒，甚至毫秒。
  • 提醒事項：記錄日曆應用程式中提醒事項的日期、時間和時區。

Snowflake 支援三種主要的時間資料型別，如表 5-1 所示。

表 5-1. Snowflake 時間資料型別

資料型別 允許的範圍
date 1582-01-01 到 9999-12-31
time 00:00:00 到 23:59:59.999999999
timestamp 1582-01-01 00:00:00 到 9999-12-31 23:59:59.999999999

時間範圍

Snowflake 的日期範圍從 1582 年到 9999 年，時間則精確到奈秒等級（小數點後 9 位）。timestamp 型別結合了日期和時間，並提供三種變體：

  • timestamp_ntz：不含時區資訊。
  • timestamp_ltz：使用當前會話的時區。
  • timestamp_tz：允許指定時區。

時區設定

通常玄貓都知道自己身處哪個時區，但可能不清楚資料函式庫的時區設定。可以使用 show parameters 指令來查詢：

PUBLIC>show parameters like 'timez%';
+----------+---------------------+---------------------+...
| key      | value               | default             |...
|----------+---------------------+---------------------+...
| TIMEZONE | America/Los_Angeles | America/Los_Angeles |...
+----------+---------------------+---------------------+...

如果需要變更預設時區，可以使用 alter session 指令（變更當前會話的時區）或 alter account 指令（永久變更時區）。以下是如何在會話層級變更時區的範例：

PUBLIC>alter session set timezone='America/New_York';
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+

PUBLIC>show parameters like 'timez%';
+----------+------------------+---------------------+...
| key      | value            | default             |...
|----------+------------------+---------------------+...
| TIMEZONE | America/New_York | America/Los_Angeles |...
+----------+------------------+---------------------+...

內建函式

以下查詢示範如何使用 Snowflake 的內建函式來取得當前日期、時間和時間戳記：

PUBLIC>select current_date, current_time, current_timestamp;
+--------------+--------------+-------------------------------+
| CURRENT_DATE | CURRENT_TIME | CURRENT_TIMESTAMP             |
|--------------+--------------+-------------------------------|
| 2022-10-12   | 12:59:41     | 2022-10-12 12:59:41.598 -0400 |
+--------------+--------------+-------------------------------+

時間戳記值結尾的 -0400 表示當前時區比格林威治標準時間（GMT）慢 4 小時。請注意，這個值可能會因為日光節約時間而有所不同。

日期格式的彈性

Snowflake 在處理日期字面值時非常彈性，可以接受多種常見格式。例如，以下字串都可以插入到 date 欄位中：

  • '24-OCT-2022'
  • '10/24/2022'
  • '2022-10-24'

不過，日期值的預設輸出格式由 date_output_format 引數決定：

PUBLIC>show parameters like 'date_output%';
+--------------------+------------+------------+-------+...
| key                | value      | default    | level |...
|--------------------+------------+------------+-------+...
| DATE_OUTPUT_FORMAT | YYYY-MM-DD | YYYY-MM-DD |       |...
+--------------------+------------+------------+-------+...

當查詢傳回日期值時，將會使用 date_output_format 引數所定義的格式：

PUBLIC>select current_date;
+--------------+
| CURRENT_DATE |
|--------------|
| 2022-10-24   |
+--------------+

總結來說，Snowflake 提供了強大與彈性的資料型別來處理數值和時間資料。透過理解這些資料型別和相關的設定，玄貓可以更有效地在 Snowflake 中儲存和查詢資料。

Snowflake 資料型別深度解析：從日期格式到半結構化資料處理

在 Snowflake 中，資料型別是構建高效能資料函式庫的根本。除了常見的字串、數值和時間型別外，Snowflake 還提供了一些獨特的資料型別，特別是在處理半結構化資料（如 JSON）時非常有用。讓玄貓帶領大家深入瞭解這些資料型別，並分享一些實際應用中的經驗。

客製化日期與時間格式：提升資料可讀性

Snowflake 預設的時間戳記格式可能不符合所有人的偏好。幸運的是，你可以使用 timestamp_output_format 引數進行調整。例如，若要將日期格式更改為 MM/DD/YYYY，可以執行以下 SQL 命令：

alter session set date_output_format='MM/DD/YYYY';

這個小技巧在資料視覺化或產生報表時特別有用，可以根據不同的地區或客戶需求調整日期格式。

Boolean 的多樣面貌：不只 True 或 False

Boolean 型別在 Snowflake 中用於儲存邏輯值，接受 true 或 false。但 Snowflake 在指定時非常靈活，允許使用字串（如 'true'、'yes'）或數字（0 代表 false，非零值代表 true）。這種靈活性在處理來自不同系統的資料時非常方便。

例如：

select true, false, true = true, true = false;

Boolean 欄位也允許 NULL 值，這在處理缺失資料時非常重要。

Variant：半結構化資料的瑞士刀

Variant 型別是 Snowflake 的一大亮點，它能儲存任何型別的資料，包括半結構化資料。你可以使用 :: 運算元將字串、數字或日期轉換為 Variant 型別。

select 1::variant, 'abc'::variant, current_date::variant;

使用 typeof() 函式可以查詢 Variant 欄位中儲存的資料型別：

select typeof('this is a character string'::variant);

Variant 型別為處理 JSON 檔案提供了極大的便利（詳見第 18 章）。

Array：靈活的變長陣列

Array 型別允許儲存變長的 Variant 值陣列。陣列可以使用方括號 [ 和 ] 來建立。

select [123, 'ABC', current_time] as my_array;

若要將陣列扁平化為多列，可以使用 table() 和 flatten() 函式：

select value
from table(flatten(input=>[123, 'ABC', current_time]));

Array 型別在處理多值屬性或時間序列資料時非常有用。

Object：鍵值對的集合

Object 型別用於儲存鍵值對，其中鍵是 varchar 型別，值是 Variant 型別。Object 使用大括號 { 和 } 來建立。

select {'new_years' : '01/01',
'independence_day' : '07/04',
'christmas' : '12/25'}
as my_object;

同樣，可以使用 flatten() 和 table() 函式將 Object 扁平化：

select key, value
from table(flatten(
{'new_years' : '01/01',
'independence_day' : '07/04',
'christmas' : '12/25'}));

若要檢索特定鍵的值，可以增加 WHERE 子句：

select value
from table(flatten(
{'new_years' : '01/01',
'independence_day' : '07/04',
'christmas' : '12/25'}))
where key = 'new_years';

Object 型別在儲存和查詢組態資料或具有動態屬性的資料時非常有用。

建立 Person 表：綜合應用

為了演示這些資料型別的應用，讓玄貓們來建立一個名為 Person 的表，包含以下欄位：

  • first_name：名字
  • last_name：姓氏
  • birth_date：出生日期
  • eye_color：眼睛顏色
  • occupation：職業
  • children：子女姓名（陣列）
  • years_of_education：教育年限

以下是建立表的 SQL 語法：

create table person
(first_name varchar(50),
last_name varchar(50),
birth_date date,
eye_color varchar(10),
occupation varchar(50),
children array,
years_of_education number);

這個表結合了多種 Snowflake 資料型別，展示了 Snowflake 在處理不同型別資料時的靈活性。

Snowflake 的多樣資料型別為處理各種資料提供了強大的工具。Variant、Array 和 Object 型別使得處理半結構化資料變得輕而易舉。透過靈活運用這些資料型別，你可以構建出高效、可擴充套件的資料函式庫解決方案。

深入 Snowflake：資料表新增與刪除操作實戰

在資料倉管理中，新增與刪除資料是基本與重要的操作。本文將以 Snowflake 為例，展示如何透過 INSERT 和 DELETE 語法，靈活地管理資料表中的資料。我將分享一些實戰技巧，幫助你更有效地操作 Snowflake 資料函式庫。

如何在 Snowflake 資料表新增資料？

在 Snowflake 中，INSERT 語法是用於新增資料列的主要方式。最簡單的 INSERT 語法如下：

insert into person (first_name, last_name, birth_date,
eye_color, occupation, children, years_of_education)
values ('Bob','Smith','22-JAN-2000','blue','teacher',
null, 18);

這個語法會將一筆新的資料列插入到 person 資料表中，並為指定的欄位指定。如果某些欄位沒有提供值，則會預設為 NULL。

玄貓解密： 在實際應用中，並非所有欄位都需要提供值。你可以省略某些欄位，讓資料函式庫使用預設值或 NULL。例如，如果 Bob Smith 沒有小孩，你可以省略 children 欄位：

insert into person (first_name, last_name, birth_date,
eye_color, occupation, years_of_education)
values ('Gina','Peters','03-MAR-2001','brown',
'student', 12);

一次新增多筆資料：效率提升技巧

Snowflake 允許在同一個 INSERT 語法中新增多筆資料，只需使用逗號分隔不同的 VALUES 集合即可。這在需要大量新增資料時非常有用。

insert into person (first_name, last_name, birth_date,
eye_color, occupation, years_of_education)
values
('Tim','Carpenter','09-JUL-2002','green','salesman', 16),
('Kathy','Little','29-AUG-2001','blue','professor', 20),
('Sam','Jacobs','13-FEB-2003','brown','lawyer', 18);

玄貓的經驗分享： 批次新增資料可以顯著提升效率，尤其是在處理大量資料時。但要注意，單個 INSERT 語法的大小也有限制，過大的語法可能會導致效能問題。

OVERWRITE 語法：快速重置資料表

Snowflake 提供了 OVERWRITE 選項，可以在新增資料前先清空資料表。這對於需要重置資料表或替換全部資料的場景非常方便。

insert overwrite into person
(first_name, last_name, birth_date,
eye_color, occupation, years_of_education)
values
('Bob','Smith','22-JAN-2000','brown','teacher', 18),
('Gina','Peters','03-MAR-2001','green','student', 12),
('Tim','Carpenter','09-JUL-2002','blue','salesman', 16),
('Kathy','Little','29-AUG-2001','brown','professor', 20),
('Sam','Jacobs','13-FEB-2003','blue','lawyer', 18);

玄貓提醒： 使用 OVERWRITE 選項時務必小心，因為這會永久刪除資料表中的所有資料。在執行前，請務必確認你的操作是正確的。

從其他資料表新增資料：SELECT 語法的妙用

除了直接提供值，你還可以使用 SELECT 語法從其他資料表（甚至是同一個資料表）中選取資料，並將其插入到目標資料表中。

insert into person (first_name, last_name, birth_date,
eye_color, occupation, children, years_of_education)
select 'Sharon' as first_name,
last_name, birth_date, eye_color,
'doctor' as occupation,
['Sue'::variant, 'Shawn'::variant] as children,
20 as years_of_education
from person
where first_name = 'Tim' and last_name = 'Carpenter';

玄貓的深入解析： 這個技巧在資料轉換和複製時非常有用。你可以透過 SELECT 語法對資料進行篩選、轉換和計算，然後將結果插入到目標資料表中。

如何在 Snowflake 資料表刪除資料？

DELETE 語法用於從 Snowflake 資料表中刪除資料列。最常用的 DELETE 語法包含 WHERE 子句，用於指定要刪除的資料列。

delete from person
where first_name = 'Sam' and last_name = 'Jacobs';

玄貓的經驗之談： WHERE 子句是 DELETE 語法中最重要的部分。如果省略 WHERE 子句，將會刪除資料表中的所有資料列，這是一個非常危險的操作。

根據其他資料表刪除資料：進階技巧

在某些情況下，你可能需要根據其他資料表的資料來刪除目標資料表的資料列。這可以透過 JOIN 或子查詢來實作。

玄貓的實戰經驗： 這種技巧在資料清理和同步時非常有用。你可以根據其他資料表的狀態，自動刪除目標資料表中不再需要的資料列。

希望這些實戰技巧能幫助你更有效地管理 Snowflake 資料函式庫中的資料。掌握這些基本操作，你就能更輕鬆地面對各種資料管理挑戰。

玄貓解密：資料刪除與時光回溯的藝術

在資料倉管理中，刪除資料是一項常見但需謹慎操作的任務。有時，我們需要從一個表中刪除與另一個表相關的資料。Snowflake 提供了 using 子句，允許我們在 delete 陳述式中進行表的連線，從而實作更精確的資料刪除。

例如，假設我們想從 Person 表中刪除所有在 Employee 表中也有記錄的人員。以下是如何使用 using 子句來完成這個任務：

PUBLIC>delete from person
using employee
where employee.emp_name =
concat(person.first_name, ' ', person.last_name);

這個例子中，concat() 函式將 Person 表中的 first_name 和 last_name 欄位合併成一個字串，然後與 Employee 表中的 emp_name 欄位進行比較。如果找到比對的記錄，Person 表中的相應行將被刪除。

玄貓解密：concat() 函式

concat() 函式用於將兩個或多個字串連線成一個單一的字串。在資料函式庫操作中，這非常有用，特別是當需要比較來自不同表的欄位時。

哎呀！「還原」按鈕在哪裡？

有時候，我們可能會不小心刪除了資料，然後才意識到這是個錯誤。在這種情況下，如果我們使用的是試算表或文書處理應用程式，只需點選「還原」按鈕即可。但是，在資料函式庫中，一旦 delete 陳述式被提交，資料就消失了。

幸運的是，Snowflake 提供了一個名為「時光回溯 (Time Travel)」的功能，允許我們檢視資料在特定時間點的狀態。如果使用的是 Snowflake Standard 版本，可以檢視最多 24 小時前的資料。而 Snowflake Enterprise 版本則允許組態資料保留期，最長可達 90 天。

以下是一個快速範例，展示如何使用時光回溯功能來還原已刪除的資料：

PUBLIC>select * from employee at(offset => -600)
where emp_name = 'Greg Carpenter';

這個查詢將我們帶回到 10 分鐘前（-600 秒），找到了 Greg Carpenter 的資料。現在，我們可以將這個查詢與 insert 陳述式結合，將 Greg 的資料重新增加到 Employee 表中：

PUBLIC>insert into employee
select * from employee at(offset => -600)
where emp_name = 'Greg Carpenter';

玄貓解密：時光回溯的原理

Snowflake 的時光回溯功能根據其獨特的架構。Snowflake 不會覆寫現有的資料，而是將所有更改作為新版本儲存。這使得我們可以輕鬆地回溯到之前的任何時間點，檢視當時的資料狀態。

玄貓經驗談：資料修改的藝術

除了刪除資料外，修改現有資料也是資料倉管理中常見的操作。要修改表中的資料，我們需要使用 update 陳述式。這個陳述式需要指定要修改的行（使用 where 子句），以及要進行的更改（使用 set 子句）。

以下是一個範例，展示如何使用 update 陳述式來更改 Person 表中 Kathy Little 的職業和眼睛顏色：

PUBLIC>update person
set occupation = 'musician', eye_color = 'grey'
where first_name = 'Kathy' and last_name = 'Little';

Snowflake 的回應告訴我們有一行被更新了。如果我們想使用另一個表中的值來應用更改，可以使用 from 子句。

例如，以下是如何使用 Employee 表中的資料來更新 Person 表中 Bob Smith 的行：

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

這個例子中，我們將 Person 表中的 occupation 欄位設定為 ‘boss’，但僅針對那些在 Employee 表中 emp_name 與 Person 表中的名字比對，與 mgr_empid 為空的記錄。

玄貓提醒：update 陳述式的風險

在使用 update 陳述式時，請務必小心。如果 where 子句不夠精確，可能會意外地修改了多行資料。因此，在執行 update 陳述式之前，最好先使用 select 陳述式來驗證 where 子句是否正確。

總結，資料函式庫的資料修改和刪除是日常管理的重要部分。Snowflake 的 using 子句和時光回溯功能為我們提供了強大的工具，可以更輕鬆、更安全地管理資料。

玄貓

玄貓

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