半結構化資料查詢

在 Snowflake 進行資料管線自動化

Emily Melhuish

Technical Curriculum Developer, Snowflake

半結構化資料載入

資料載入.png

¹ * Snowflake 學習素材

處理不同資料結構

Carrier A Carrier A 的 JSON

Carrier B

Carrier B 的 JSON

轉換範例

截圖 2026-05-11 5.56.34 pm.png

Snowflake 資料型別

類別	型別	範例
數值	NUMBER, INT, FLOAT	`credits_used NUMBER(10,2)`
文字	VARCHAR, STRING, TEXT	`carrier_name VARCHAR`
日期／時間	DATE, TIME, TIMESTAMP	`dispatch_date DATE`
布林	BOOLEAN	`is_active BOOLEAN`
半結構化	VARIANT, OBJECT, ARRAY	`event_payload VARIANT`

`VARIANT` 型別

VARIANT 以原樣儲存半結構化資料：不需結構定義
可直接載入 JSON、Parquet、Avro、ORC 或 XML
結構在「查詢時」解析，而非載入時（schema-on-read）

COPY INTO shipment_events
FROM @harbr_stage/events/
FILE_FORMAT = (TYPE = 'JSON');

VARIANT 詞雲，顯示 VARIANT 可容納的半結構化型別：JSON、ARRAY、OBJECT、AVRO、PARQUET、SEMI-STRUCTURED

瀏覽 JSON

JSON 範例

{
  "shipment_id": "SHP-001",
  "carrier": { "name": "FastFreight" },
  "stops": [
    { "location": "Rotterdam Port" },
    { "location": "Amsterdam Hub" }
  ]
}

瀏覽 JSON

走訪結構：

<column>:<level1_element>.<level2_element>

SELECT 
event:id::STRING AS shipment_id
, event:carrier.name::STRING AS carrier
, event:stops[0]:location::STRING 
AS first_stop 
FROM shipment_events;

用 FLATTEN 將陣列轉成列

扁平化.png

使用 LATERAL FLATTEN

SELECT 
  s.event_data:shipment_id::STRING AS shipment_id,
  f.value:location::STRING AS stop,
  f.value:arrived_at::TIMESTAMP AS arrived_at 
FROM shipment_events s,
  LATERAL FLATTEN(input => s.event_data:stops) f;

扁平化.png

一起來練習吧！

在 Snowflake 進行資料管線自動化