返回文章列表
玄貓（BlackCat）

Snowflake 半結構化資料處理實務

本文探討 Snowflake 中處理半結構化資料的技巧，包含 JSON 檔案的建立、儲存與查詢。文章涵蓋了使用 `parse_json()`、`flatten()`、`array_agg()` 等內建函式操作 JSON 資料，以及如何利用 `LATERAL FLATTEN`

資料函式庫 資料工程
Snowflake JSON 半結構化資料 SQL 資料函式庫查詢

Snowflake 提供了完整的 JSON 資料處理能力，讓開發者能輕鬆應對日益增長的半結構化資料需求。除了基本的 JSON 建構與解析，Snowflake 也支援更進階的操作，例如使用 LATERAL FLATTEN 處理複雜的巢狀結構。這對於需要處理大量巢狀 JSON 資料的應用場景至關重要，可以有效提升查詢效率和簡化程式碼複雜度。透過結合 SQL 和內建函式，開發者可以更靈活地操作和分析 JSON 資料，滿足不同業務需求。

深入解析半結構化資料處理

在現代資料處理中，半結構化資料扮演著越來越重要的角色。Snowflake 提供了強大的功能來處理 JSON 檔案等半結構化資料。本篇文章將探討如何在 Snowflake 中建構、儲存和查詢 JSON 檔案。

JSON 資料處理基礎

JSON（JavaScript Object Notation）是一種輕量級的資料交換格式，廣泛應用於 Web 開發和資料儲存。Snowflake 提供了多種內建函式來處理 JSON 資料，包括 parse_json()、flatten() 和 array_agg()。

建立 JSON 檔案

首先，我們需要了解如何將關聯式資料轉換為 JSON 格式。以下是一個範例查詢，用於生成包含特定零件資訊的 JSON 檔案：

SELECT 
    OBJECT_CONSTRUCT(
        'Parts', 
        ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'Partkey', p_partkey,
                'Name', p_name,
                'Brand', p_brand
            )
        )
    ) AS parts_json
FROM 
    part
WHERE 
    p_mfgr = 'Manufacturer#1'
    AND p_type = 'ECONOMY POLISHED STEEL';

內容解密：

  • OBJECT_CONSTRUCT：用於建立 JSON 物件。
  • ARRAY_AGG：將多行資料聚合成一個陣列。
  • 條件篩選：僅選擇 p_mfgr 為 ‘Manufacturer#1’ 且 p_type 為 ‘ECONOMY POLISHED STEEL’ 的零件。

將 JSON 資料插入表格

將生成的 JSON 檔案插入到 my_docs 表格中，可以使用以下 SQL 陳述式：

INSERT INTO my_docs (doc)
VALUES (
    OBJECT_CONSTRUCT(
        'Parts', 
        ARRAY_AGG(
            OBJECT_CONSTRUCT(
                'Partkey', p_partkey,
                'Name', p_name,
                'Brand', p_brand
            )
        )
    )
);

內容解密：

  • INSERT INTO：將資料插入指定的表格中。
  • OBJECT_CONSTRUCT 和 ARRAY_AGG 的組合使用與前述相同，用於生成 JSON 物件。

查詢巢狀的 JSON 資料

對於巢狀的 JSON 資料結構，可以使用 LATERAL FLATTEN 函式來解析。以下是一個範例，用於解析包含多個國家和城市資訊的 JSON 檔案：

WITH city_doc AS (
    SELECT 
        parse_json('{
            "Major_Cities": [
                {
                    "Country": "USA",
                    "Cities": [
                        {"City_Name": "Chicago", "Region_Name": "Illinois"},
                        {"City_Name": "New York City", "Region_Name": "New York"}
                    ]
                },
                {
                    "Country": "Canada",
                    "Cities": [
                        {"City_Name": "Vancouver", "Region_Name": "British Columbia"},
                        {"City_Name": "Toronto", "Region_Name": "Ontario"}
                    ]
                }
            ]
        }') AS d
)
SELECT 
    c.value:"Country"::VARCHAR AS country_name,
    city.value:"City_Name"::VARCHAR AS city_name,
    city.value:"Region_Name"::VARCHAR AS region_name
FROM 
    city_doc,
    LATERAL FLATTEN(input => d:"Major_Cities") c,
    LATERAL FLATTEN(input => c.value:"Cities") city;

內容解密：

  • LATERAL FLATTEN：用於解析巢狀的 JSON 陣列。
  • c.value:"Country"：提取每個國家的名稱。
  • city.value:"City_Name" 和 city.value:"Region_Name"：分別提取城市名稱和所屬區域。

重點回顧

本章節介紹瞭如何在 Snowflake 中處理半結構化資料，包括建立、儲存和查詢 JSON 檔案。透過使用 Snowflake 提供的內建函式，如 parse_json()、flatten() 和 array_agg()，可以有效地管理和分析半結構化資料。

練習題

  1. 生成包含特定零件資訊的 JSON 檔案：

    • 編寫一個查詢，生成一個包含零件鍵（Partkey）、名稱（Name）和品牌（Brand）的 JSON 檔案，僅限於製造商 ‘Manufacturer#1’ 生產的 ‘ECONOMY POLISHED STEEL’ 型別的零件。

  2. 將生成的 JSON 檔案插入到 my_docs 表格中：

    • 使用第一題生成的 JSON 檔案，將其插入到 my_docs 表格中。

  3. 查詢巢狀的 JSON 資料：

    • 給定一個包含多個國家及其主要城市資訊的 JSON 檔案，編寫一個查詢來提取每個國家的名稱、城市名稱和區網域名稱。

資料函式庫查詢與操作練習解答

練習 2-2：客戶資料查詢

查詢客戶資料表中，市場區隔為「MACHINERY」或「FURNITURE」，且帳戶餘額在 -1 至 1 之間的客戶名稱、市場區隔和帳戶餘額。

SQL 查詢陳述式

SELECT c_name, c_mktsegment, c_acctbal
FROM customer
WHERE c_mktsegment IN ('MACHINERY', 'FURNITURE')
AND c_acctbal BETWEEN -1 AND 1;

查詢結果

| C_NAME | C_MKTSEGMENT | C_ACCTBAL | |

-|

|

| | Customer#000038476 | FURNITURE | -0.72 | | Customer#000136930 | MACHINERY | -0.56 | | Customer#000146548 | MACHINERY | -0.98 | | Customer#000103441 | MACHINERY | -0.74 | | Customer#000001141 | MACHINERY | 0.97 | | Customer#000001327 | MACHINERY | 0.97 |

內容解密：

  1. SELECT c_name, c_mktsegment, c_acctbal：選擇客戶名稱、市場區隔和帳戶餘額欄位進行查詢。
  2. FROM customer：指定查詢的資料表為客戶資料表。
  3. WHERE c_mktsegment IN ('MACHINERY', 'FURNITURE')：篩選市場區隔為「MACHINERY」或「FURNITURE」的客戶資料。
  4. AND c_acctbal BETWEEN -1 AND 1：進一步篩選帳戶餘額在 -1 至 1 之間的資料。

練習 3-1：地區與國家資料查詢

查詢地區資料表和國家資料表，取得地區名稱和國家名稱，其中國家名稱以字母「A」開頭。

SQL 查詢陳述式

SELECT r.r_name AS region_name, n.n_name AS nation_name
FROM region AS r
INNER JOIN nation AS n
ON r.r_regionkey = n.n_regionkey
WHERE n.n_name LIKE 'A%';

查詢結果

| REGION_NAME | NATION_NAME | |

–|

–| | AFRICA | ALGERIA | | AMERICA | ARGENTINA |

內容解密：

  1. SELECT r.r_name AS region_name, n.n_name AS nation_name：選擇地區名稱和國家名稱，並使用別名。
  2. FROM region AS r INNER JOIN nation AS n：將地區資料表和國家資料表進行內部連線。
  3. ON r.r_regionkey = n.n_regionkey：指定連線條件為地區程式碼的匹配。
  4. WHERE n.n_name LIKE 'A%'：篩選國家名稱以字母「A」開頭的資料。

練習 4-1：集合運算

給定兩個集合 A 和 B，分別進行聯集、交集和差集運算。

集合 A 和 B

A = {3, 5, 7, 9} B = {4, 5, 6, 7, 8}

運算結果

  • A union B = {3, 4, 5, 6, 7, 8, 9}
  • A union all B = {3, 4, 5, 5, 6, 7, 7, 8, 9}
  • A intersect B = {5, 7}
  • A except B = {3, 9}
  • B except A = {4, 6, 8}

圖表說明

此圖示呈現集合 A 和 B 的不同運算結果。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖表說明

rectangle "聯集" as node1
rectangle "交集" as node2
rectangle "差集" as node3

node1 --> node2
node2 --> node3

@enduml

圖表翻譯： 此圖表展示了集合 A 和 B 之間的聯集、交集和差集運算，分別對應不同的結果集合。

練習 4-2：地區與國家名稱查詢

查詢地區名稱和國家名稱中，以字母「A」開頭的名稱。

SQL 查詢陳述式

SELECT r_name FROM region WHERE r_name LIKE 'A%'
UNION
SELECT n_name FROM nation WHERE n_name LIKE 'A%';

查詢結果

| R_NAME | |

| | AFRICA | | AMERICA | | ASIA | | ALGERIA | | ARGENTINA |

圖表說明

此查詢使用了聯集運算，將地區名稱和國家名稱合併，並去除重複項。

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖表說明

rectangle "查詢" as node1
rectangle "聯集" as node2

node1 --> node2

@enduml

圖表翻譯： 圖表顯示了從地區資料表和國家資料表中查詢以字母「A」開頭的名稱，並進行聯集運算的流程。

資料函式庫查詢與更新練習解答

練習4-4解答：集合運算

給定以下三個集合：

  • A = {3, 5, 7, 9}
  • B = {4, 5, 6, 7, 8}
  • C = {8, 9, 10}

計算 (A except B) intersect C 的結果。

首先，計算 A except B：

  • A except B 表示在 A 中但不在 B 中的元素，因此結果為 {3, 9}。

然後，計算 {3, 9} intersect C：

  • intersect 表示兩個集合的交集，因此結果為 {9}。

最終結果為 {9}。

練習5-1解答：字串查詢

撰寫一個傳回字串 'you can't always get what you want' 的查詢。

SELECT 'you can\'t always get what you want';

輸出結果：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
| 'you can\'t always get what you want' |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
| you can't always get what you want    |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+

內容解密：

此查詢直接傳回指定的字串，注意在 SQL 中單引號內的單引號需要使用兩個單引號表示。

練習5-2解答：陣列建立

撰寫一個傳回包含數字 985、字串 'hello there' 和布林值 true 的陣列的查詢。

SELECT [985, 'hello there', true];

輸出結果：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| [985, 'hello there', true] |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-+
| [                          |
|   985,                     |
|   "hello there",           |
|   true                     |
| ]                          |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-+

內容解密：

此查詢使用陣列語法建立一個包含不同資料型別的陣列。

練習5-3解答：陣列展開

使用練習5-2生成的陣列作為輸入，將陣列展開成三行。

SELECT VALUE
FROM TABLE(FLATTEN(INPUT => [985, 'hello there', true]));

輸出結果：

+
---
-
---
-
---
-
---
+
| VALUE         |
+
---
-
---
-
---
-
---
+
| 985           |
| "hello there" |
| true          |
+
---
-
---
-
---
-
---
+

內容解密：

此查詢使用 FLATTEN 函式將陣列展開成多行，每個元素佔一行。

練習5-4解答：更新 Pet 表的 Owner 欄位

給定 Pet_Owner 和 Pet 表的資料，撰寫一個更新陳述式，將 Pet 表的 Owner 欄位設定為對應的 Pet_Owner 表中的 Owner_Name。

UPDATE Pet AS p
SET p.Owner = po.Owner_Name
FROM Pet_Owner AS po
WHERE p.Owner_ID = po.Owner_ID;

更新後的 Pet 表：

+
---
-
---
-+
---
-
---
---
+
---
-
---
---
+
---
-
---
+
| PET_ID | OWNER_ID | PET_NAME | OWNER |
+
---
-
---
-+
---
-
---
---
+
---
-
---
---
+
---
-
---
+
| 101    | 1        | Fluffy   | John  |
| 102    | 3        | Spot     | Laura |
| 103    | 4        | Rover    | Mark  |
| 104    | NULL     | Rosco    | NULL  |
+
---
-
---
-+
---
-
---
---
+
---
-
---
---
+
---
-
---
+

內容解密：

此更新陳述式根據 Owner_ID 將 Pet 表的 Owner 欄位與 Pet_Owner 表的 Owner_Name 對應起來。

練習6-1解答：字串替換

撰寫一個查詢，使用函式將字串 'cow it maked dende' 中的所有 c 替換為 n，所有 d 替換為 s。

SELECT TRANSLATE('cow it maked dende', 'cd', 'ns');

輸出結果：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
| TRANSLATE(...)                |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+
| now it makes sense            |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
+

內容解密：

此查詢使用 TRANSLATE 函式進行字元替換，將指定的字元對映到新的字元。

練習6-2解答：生成序列數字

撰寫一個查詢，傳回數字1到10，每個數字佔一行。

SELECT SEQ1() + 1
FROM TABLE(GENERATOR(ROWCOUNT => 10));

輸出結果：

+
---
-
---
-
---
-+
| SEQ1() + 1 |
+
---
-
---
-
---
-+
| 1          |
| 2          |
| ...        |
| 10         |
+
---
-
---
-
---
-+

內容解密：

此查詢使用 GENERATOR 函式生成10行資料，並使用 SEQ1() 生成序列數字。

練習6-3解答：計算日期差異

撰寫一個查詢，傳回日期 '01-JAN-2024' 和 '15-AUG-2025' 之間的天數差異。

SELECT DATEDIFF(DAY, '01-JAN-2024', '15-AUG-2025');

輸出結果：

+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| DATEDIFF(DAY, '01-JAN-2024', '15-AUG-2025') |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+
| 592                                        |
+
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
-
---
--+

內容解密：

此查詢使用 DATEDIFF 函式計算兩個日期之間的天數差異。

練習7-1解答：聚合函式使用

撰寫一個查詢，計算 Supplier 表中的行數，以及 s_acctbal 欄位的最小值和最大值。

SELECT COUNT(*), MIN(s_acctbal), MAX(s_acctbal)
FROM Supplier;

輸出結果：

+
---
-
---
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-+
| COUNT(*) | MIN(s_acctbal) | MAX(s_acctbal) |
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-+
| 7400     | -998.22        | 9998.20        |
+
---
-
---
---
+
---
-
---
-
---
-
---
-+
---
-
---
-
---
-
---
-+

內容解密：

此查詢使用聚合函式計算表的行數和指定欄位的最小值與最大值。