在現代資料工程領域,半結構化資料已經成為不可忽視的重要資料型態。相較於傳統關聯式資料庫嚴格的表格結構,半結構化資料如 JSON、XML、Avro 等格式提供更大的彈性,能有效處理資料模式頻繁變化的場景。然而這種彈性也帶來查詢與處理的挑戰,需要特殊的技術與工具支援。Snowflake 作為領先的雲端資料倉儲平台,對半結構化資料提供原生且強大的支援能力。透過 VARIANT 資料型別、路徑表示法、FLATTEN 函式等核心功能,Snowflake 讓開發者能以類似處理關聯式資料的方式,對半結構化資料進行高效查詢與轉換。本文將深入探討 Snowflake 處理半結構化資料的完整技術體系,從基礎的資料結構探索到進階的複雜查詢實作,從單層陣列處理到多層巢狀結構解析,從與關聯式資料的整合到 JSON 格式的動態生成,協助資料工程師充分掌握 Snowflake 的半結構化資料處理能力。
VARIANT 資料型別與路徑表示法
Snowflake 處理半結構化資料的核心機制建立在 VARIANT 資料型別之上。這是一種特殊設計的資料型別,能儲存任意複雜度的半結構化資料,包含 JSON、XML、Avro、Parquet 等格式。VARIANT 型別的設計讓 Snowflake 能在保持資料彈性的同時,提供接近關聯式資料的查詢效能。理解 VARIANT 型別的特性與操作方式,是掌握 Snowflake 半結構化資料處理的基礎。
VARIANT 資料型別的儲存特性
VARIANT 型別採用壓縮的二進位格式儲存資料,這種格式針對查詢效能最佳化。當資料載入 Snowflake 時,系統會自動解析 JSON 或其他半結構化格式,轉換為內部的 VARIANT 表示。這個轉換過程包含資料壓縮、索引建立、統計資訊收集等步驟,為後續的高效查詢奠定基礎。
VARIANT 型別能儲存的資料大小有一定限制,單一 VARIANT 值最大為 16 MB。對於超過這個限制的資料,需要考慮分割策略,將大型 JSON 文件拆分為多個較小的片段。實務上,大多數 API 回應、日誌記錄、事件資料都遠小於這個限制,16 MB 的空間足以應付絕大部分應用場景。
VARIANT 型別的另一個重要特性是自動型別推斷。當從 VARIANT 中提取資料時,Snowflake 會根據實際的資料內容自動推斷型別。數字會被識別為 NUMBER,字串識別為 VARCHAR,布林值識別為 BOOLEAN 等。這種自動型別推斷簡化查詢撰寫,但也需要注意型別轉換的正確性,特別是在資料可能包含多種型別的情況下。
路徑表示法的語法與應用
存取 VARIANT 型別中的資料使用路徑表示法,這是一種直觀的點記號語法。路徑表示法讓開發者能像存取物件屬性一樣,存取 JSON 中的欄位。基本語法是 column_name:path.to.field,其中冒號後面是 JSON 結構的路徑。
SELECT
doc:customer_id,
doc:order_date,
doc:items[0]:product_name,
doc:items[0]:quantity
FROM orders_json;
這個查詢展示路徑表示法的基本用法。doc:customer_id 存取 JSON 文件中的 customer_id 欄位,doc:items[0]:product_name 則存取 items 陣列第一個元素的 product_name 欄位。陣列索引使用方括號表示,索引從 0 開始計數,這與大部分程式語言的慣例一致。
路徑表示法支援多層巢狀結構的存取。對於複雜的 JSON 結構,只需要依照路徑逐層存取即可。例如 doc:customer:address:city 會依序進入 customer 物件、address 物件,最後取得 city 欄位的值。這種語法簡潔明瞭,讓複雜結構的查詢變得相對容易。
型別轉換是路徑表示法的重要環節。從 VARIANT 中提取的值預設仍然是 VARIANT 型別,在許多情況下需要明確轉換為特定型別。使用雙冒號語法進行型別轉換,如 doc:customer_id::VARCHAR 將客戶 ID 轉換為字串,doc:order_total::NUMBER(10,2) 將訂單金額轉換為指定精度的數值。
SELECT
doc:customer_id::VARCHAR AS customer_id,
doc:order_date::DATE AS order_date,
doc:order_total::NUMBER(10,2) AS order_total,
doc:status::VARCHAR AS status
FROM orders_json
WHERE doc:order_total::NUMBER(10,2) > 1000;
這個查詢示範完整的型別轉換應用。每個欄位都明確轉換為適當的型別,確保資料的正確性與查詢的效能。WHERE 子句中的型別轉換讓 Snowflake 能使用數值比較,而非字串比較,提升查詢效率。
FLATTEN 函式的深入應用
FLATTEN 是 Snowflake 處理半結構化資料最核心的函式,能將巢狀的陣列或物件結構展開為關聯式的列。這個函式的設計哲學是將半結構化資料的層次結構轉換為關聯式資料庫熟悉的平面表格,讓開發者能使用標準 SQL 技術進行查詢與分析。掌握 FLATTEN 函式的各種用法,是處理複雜半結構化資料的關鍵。
FLATTEN 函式的基礎語法
FLATTEN 函式的基本語法是 FLATTEN(input => expression),其中 input 參數指定要展開的陣列或物件。這個函式通常與 LATERAL 關鍵字配合使用,透過 LATERAL JOIN 的機制讓 FLATTEN 能參照主表格的欄位。這種設計讓每一列的 JSON 資料都能獨立展開,而不會與其他列混淆。
SELECT
o.order_id,
i.value:product_name::VARCHAR AS product_name,
i.value:quantity::INTEGER AS quantity,
i.value:unit_price::NUMBER(10,2) AS unit_price
FROM orders o,
LATERAL FLATTEN(input => o.items) i;
這個查詢展示 FLATTEN 的基本應用場景。orders 表格的每一列包含一個訂單,其中 items 欄位是 VARIANT 型別,儲存訂單項目的陣列。FLATTEN 函式將這個陣列展開,每個項目變成一個獨立的列。結果是原本一列的訂單資料,展開為多列,每列代表一個訂單項目。
FLATTEN 函式回傳的結果包含多個欄位,最常用的是 value 欄位,包含陣列元素的實際內容。其他欄位如 seq 提供元素在原陣列中的序號,index 提供陣列索引,key 在展開物件時提供鍵名等。這些欄位提供額外的上下文資訊,在某些場景下特別有用。
遞迴展開與結構探索
FLATTEN 函式支援遞迴展開模式,能一次性展開多層巢狀結構。這個功能在資料結構探索階段特別有用,可以快速了解 JSON 文件的完整結構。啟用遞迴模式只需要設定 recursive 參數為 true。
SELECT DISTINCT
f.key AS field_name,
TYPEOF(f.value) AS field_type,
f.path AS field_path
FROM orders_json,
LATERAL FLATTEN(input => doc, recursive => true) f
WHERE TYPEOF(f.value) != 'OBJECT' AND TYPEOF(f.value) != 'ARRAY'
ORDER BY field_path, field_name;
這個查詢使用遞迴 FLATTEN 探索 JSON 文件的完整結構。遞迴模式會持續展開所有層級的巢狀結構,直到遇到純量值為止。TYPEOF 函式判斷每個欄位的型別,WHERE 子句過濾掉中間的物件與陣列節點,只保留最終的純量欄位。path 欄位提供從根節點到當前欄位的完整路徑,協助理解欄位在結構中的位置。
結構探索的結果能協助設計後續的查詢邏輯。了解資料包含哪些欄位、每個欄位的型別、欄位的巢狀層級等資訊,能讓查詢撰寫更有針對性。這個技術特別適合處理來源不明或文件不完整的 JSON 資料,透過自動化的方式快速掌握資料特徵。
多層陣列的連續展開
實務中的 JSON 資料經常包含多層巢狀陣列,處理這種結構需要連續使用多次 FLATTEN。每次 FLATTEN 處理一層陣列,透過串接多個 LATERAL FLATTEN 可以逐層展開複雜的巢狀結構。這種技術雖然看似繁複,但邏輯清晰,容易理解與維護。
SELECT
doc:region_name::VARCHAR AS region_name,
doc:region_total::NUMBER(15,2) AS region_total,
c.value:country_name::VARCHAR AS country_name,
c.value:country_total::NUMBER(15,2) AS country_total,
city.value:city_name::VARCHAR AS city_name,
city.value:city_total::NUMBER(15,2) AS city_total
FROM sales_hierarchy,
LATERAL FLATTEN(input => doc:countries) c,
LATERAL FLATTEN(input => c.value:cities) city;
這個查詢處理三層結構,區域包含國家陣列,國家包含城市陣列。第一個 FLATTEN 展開國家陣列,第二個 FLATTEN 展開每個國家的城市陣列。最終結果是完全扁平化的列,每列包含完整的區域、國家、城市資訊。這種結構讓後續的聚合分析變得簡單,可以直接使用標準的 GROUP BY 與聚合函式。
連續 FLATTEN 的順序很重要,必須按照巢狀層級由外而內進行。第二個 FLATTEN 參照第一個 FLATTEN 的結果,第三個 FLATTEN 參照第二個 FLATTEN 的結果,依此類推。錯誤的順序會導致查詢失敗或產生非預期的結果。
半結構化資料的結構探索策略
在開始處理半結構化資料前,充分了解資料結構是關鍵的第一步。不同於關聯式資料有明確的表格定義,半結構化資料的結構往往不明確或是動態變化。建立有效的結構探索流程,能大幅提升後續查詢開發的效率,避免因為誤解資料結構而撰寫錯誤的查詢。
欄位清單與型別分析
探索資料結構的第一步是了解資料包含哪些欄位以及這些欄位的型別。結合遞迴 FLATTEN 與 TYPEOF 函式,可以自動化產生完整的欄位清單。這個技術特別適合處理複雜或文件不完整的 JSON 資料。
WITH field_analysis AS (
SELECT
f.key AS field_name,
f.path AS field_path,
TYPEOF(f.value) AS field_type,
COUNT(*) AS occurrence_count,
COUNT(DISTINCT f.value) AS distinct_values
FROM source_data,
LATERAL FLATTEN(input => data_column, recursive => true) f
WHERE TYPEOF(f.value) NOT IN ('OBJECT', 'ARRAY')
GROUP BY f.key, f.path, TYPEOF(f.value)
)
SELECT
field_path,
field_name,
field_type,
occurrence_count,
distinct_values,
ROUND(distinct_values / occurrence_count * 100, 2) AS cardinality_pct
FROM field_analysis
ORDER BY field_path, field_name;
這個查詢不僅列出所有欄位與型別,還計算每個欄位的出現次數與唯一值數量。cardinality_pct 指標反映欄位值的多樣性,接近 100% 表示每列都有不同的值如主鍵,接近 0% 表示值高度重複如類別欄位。這些統計資訊協助理解資料特性,指導查詢最佳化與索引策略。
陣列結構的深度分析
陣列是半結構化資料常見的結構,了解陣列的大小分佈與元素特性對查詢設計很重要。過大的陣列可能導致效能問題,需要特殊處理策略。過小的陣列可能適合直接索引存取,而不需要使用 FLATTEN。
SELECT
ARRAY_SIZE(doc:items) AS item_count,
COUNT(*) AS order_count,
MIN(ARRAY_SIZE(doc:items)) AS min_items,
MAX(ARRAY_SIZE(doc:items)) AS max_items,
AVG(ARRAY_SIZE(doc:items)) AS avg_items,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ARRAY_SIZE(doc:items)) AS median_items
FROM orders_json
GROUP BY ARRAY_SIZE(doc:items)
ORDER BY item_count;
這個查詢分析訂單的項目數量分佈。了解大部分訂單包含幾個項目,有多少訂單是大批量訂單,能協助決定是否需要對大型陣列進行特殊最佳化。中位數與平均值的差異反映資料的偏態,若中位數遠小於平均值,表示存在少數極大值的訂單。
陣列元素的型別一致性也值得檢查。理論上陣列應該包含相同型別的元素,但實務中可能因為資料品質問題出現型別不一致的情況。檢查型別一致性能及早發現資料品質問題,避免查詢時的型別錯誤。
半結構化資料與關聯式資料的整合
Snowflake 的強大之處在於能無縫整合半結構化資料與傳統關聯式資料。這種整合能力讓組織在引入半結構化資料時,不需要放棄既有的關聯式資料資產。透過 JOIN 操作,可以將 JSON 資料與維度表格、事實表格結合,進行全面的資料分析。
維度資料的關聯查詢
常見的整合場景是將 JSON 中的識別碼與維度表格關聯,取得完整的描述資訊。JSON 資料通常只包含識別碼以節省空間,詳細的屬性資訊儲存在關聯式表格中。透過 JOIN 操作可以豐富 JSON 資料,提供更完整的分析視角。
SELECT
o.doc:order_id::VARCHAR AS order_id,
o.doc:order_date::DATE AS order_date,
c.customer_name,
c.customer_segment,
c.customer_region,
i.value:product_id::INTEGER AS product_id,
p.product_name,
p.product_category,
i.value:quantity::INTEGER AS quantity,
i.value:unit_price::NUMBER(10,2) AS unit_price,
i.value:quantity::INTEGER * i.value:unit_price::NUMBER(10,2) AS line_total
FROM orders_json o
JOIN customers c ON o.doc:customer_id::INTEGER = c.customer_id
LATERAL FLATTEN(input => o.doc:items) i
JOIN products p ON i.value:product_id::INTEGER = p.product_id;
這個查詢整合訂單 JSON、客戶維度表格、產品維度表格。FLATTEN 展開訂單項目,每個項目與產品表格關聯取得產品資訊。最終結果是完整的訂單明細,包含客戶與產品的所有屬性,可以進行各種維度的分析如按客戶群分析、按產品類別分析等。
整合查詢的效能考量很重要。JOIN 的順序影響查詢效能,一般建議先完成維度表格的 JOIN,再進行 FLATTEN 操作。這樣能在展開陣列前就過濾掉不需要的資料,減少 FLATTEN 處理的資料量。適當的索引與叢集鍵設定也能顯著提升整合查詢的效能。
事實資料的聚合分析
另一種整合場景是將 JSON 資料作為事實資料,與維度表格結合進行聚合分析。這種模式在處理事件資料、日誌資料、感測器資料時特別常見。JSON 記錄原始事件,關聯式表格提供分析維度。
SELECT
c.customer_segment,
p.product_category,
DATE_TRUNC('month', o.doc:order_date::DATE) AS order_month,
COUNT(DISTINCT o.doc:order_id) AS order_count,
SUM(i.value:quantity::INTEGER) AS total_quantity,
SUM(i.value:quantity::INTEGER * i.value:unit_price::NUMBER(10,2)) AS total_amount
FROM orders_json o
JOIN customers c ON o.doc:customer_id::INTEGER = c.customer_id
LATERAL FLATTEN(input => o.doc:items) i
JOIN products p ON i.value:product_id::INTEGER = p.product_id
GROUP BY c.customer_segment, p.product_category, DATE_TRUNC('month', o.doc:order_date::DATE)
ORDER BY order_month, customer_segment, product_category;
這個查詢計算不同客戶群與產品類別的月度銷售統計。GROUP BY 子句整合來自多個來源的維度,提供多角度的分析視角。這種查詢模式讓組織能保持 JSON 資料的彈性,同時享受關聯式分析的強大功能。
JSON 資料的生成與轉換
除了查詢既有的 JSON 資料,Snowflake 也提供完整的功能將關聯式資料轉換為 JSON 格式。這個能力在資料交換、API 回應生成、報表輸出等場景特別有用。透過 OBJECT_CONSTRUCT 與 ARRAY_AGG 等函式,可以靈活建構任意複雜度的 JSON 結構。
OBJECT_CONSTRUCT 的應用
OBJECT_CONSTRUCT 函式能從多個欄位建立 JSON 物件。語法是提供成對的鍵與值,函式會將它們組合成 JSON 物件。這個函式是將關聯式資料轉換為 JSON 的基礎工具。
SELECT
OBJECT_CONSTRUCT(
'order_id', order_id,
'order_date', order_date,
'customer', OBJECT_CONSTRUCT(
'customer_id', customer_id,
'customer_name', customer_name,
'customer_email', customer_email
),
'order_status', order_status,
'order_total', order_total
) AS order_json
FROM orders
WHERE order_date >= '2025-01-01';
這個查詢將訂單表格的資料轉換為 JSON 格式。巢狀的 OBJECT_CONSTRUCT 建立客戶物件,展示如何建構階層式的 JSON 結構。結果可以直接輸出給前端應用或外部 API,不需要額外的格式轉換。
ARRAY_AGG 與複雜結構生成
ARRAY_AGG 函式將多個值聚合為陣列,結合 OBJECT_CONSTRUCT 可以建立包含陣列的複雜 JSON 結構。這種組合特別適合產生一對多關係的 JSON 表示,如訂單與訂單項目、客戶與訂單等。
SELECT
OBJECT_CONSTRUCT(
'order_id', o.order_id,
'order_date', o.order_date,
'customer_name', c.customer_name,
'items', ARRAY_AGG(
OBJECT_CONSTRUCT(
'product_name', p.product_name,
'quantity', oi.quantity,
'unit_price', oi.unit_price,
'line_total', oi.quantity * oi.unit_price
)
),
'order_total', SUM(oi.quantity * oi.unit_price)
) AS order_json
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, o.order_date, c.customer_name;
這個查詢產生完整的訂單 JSON,包含客戶資訊與訂單項目陣列。ARRAY_AGG 將所有訂單項目聚合為陣列,每個項目是由 OBJECT_CONSTRUCT 建立的物件。GROUP BY 確保每個訂單只產生一列結果,所有項目都包含在陣列中。
生成的 JSON 結構完全由查詢控制,可以根據需求調整。需要更多欄位就在 OBJECT_CONSTRUCT 中加入,需要不同的陣列結構就調整 ARRAY_AGG 的內容。這種彈性讓 Snowflake 能適應各種 JSON 生成需求。
結語
Snowflake 對半結構化資料的支援代表資料倉儲技術的重要演進,打破傳統關聯式資料庫的限制,提供處理彈性資料的強大能力。透過 VARIANT 資料型別、路徑表示法、FLATTEN 函式等核心功能,Snowflake 讓半結構化資料的查詢變得直觀且高效。理解這些功能的運作原理與應用技巧,能大幅提升資料工程師處理現代資料的能力。
半結構化資料不應該被視為關聯式資料的替代品,而是互補的存在。在適當的場景使用適當的資料結構,結合兩者的優勢,才能建立最有效的資料架構。Snowflake 提供的整合能力讓這種混合架構成為可能,組織不需要在彈性與結構之間做出取捨。
隨著資料來源的多樣化與資料量的持續成長,半結構化資料處理能力將越來越重要。掌握 Snowflake 的半結構化資料技術,不僅能應對當前的需求,也為未來的資料挑戰做好準備。持續探索新的應用場景,最佳化查詢效能,建立可重用的處理模式,將能充分發揮 Snowflake 在半結構化資料處理方面的強大優勢。
