處理半結構化資料

Snowflake SQL 入門

George Boorman

Senior Curriculum Manager, DataCamp

結構化 vs. 半結構化

結構化資料範例

| cust_id | cust_name | cust_age | cust_email            |
|---------|-----------|----------|-----------------------|
| 1       | cust1     | 40       | cust1***@gmail.com    |
| 2       | cust2     | 35       | cust2***@gmail.com    |
| 3       | cust3     | 42       | cust3***@gmail.com    |

半結構化資料範例

以 JSON 呈現的客戶半結構化資料，其中客戶 2 有兩個 email

認識 JSON

JavaScript Object Notation
常見用法：Web API 與設定檔
JSON 資料結構：
- 鍵值配對，例如 cust_id: 1

客戶 JSON 紀錄範例，包含 id、name、age、email

Snowflake 與 JSON

原生支援 JSON
架構演進更彈性

比較：

Postgres：使用 JSONB
Snowflake：使用 VARIANT

Snowflake 如何儲存 JSON

VARIANT 支援 OBJECT 與 ARRAY 型別
- OBJECT：{ "key": "value"}
- ARRAY：["list", "of", "values"]

建立 Snowflake 資料表以處理 JSON

CREATE TABLE cust_info_json_data (
  customer_id INT, 
  customer_info VARIANT -- VARIANT data type
);

半結構化資料函式

PARSE_JSON
- expr：字串格式的 JSON 資料
- 傳回：VARIANT 型別、有效的 JSON 物件

PARSE_JSON

範例：

SELECT PARSE_JSON(
  -- Enclosed in strings      
  '{
  "cust_id": 1,
  "cust_name": "cust1",
  "cust_age": 40,
  "cust_email":"cust1***@gmail.com"
  }
  '-- Enclosed in strings
) AS customer_info_json

PARSE_JSON 的結果

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- 語法：OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- 傳回：JSON 物件

    SELECT OBJECT_CONSTRUCT(
      -- Comma separated values rather than : notation
          'cust_id', 1,
          'cust_name', 'cust1',
          'cust_age', 40,
          'cust_email', 'cust1***@gmail.com'
    )

`customer_info` 欄以 JSON 格式儲存資料的資料表

在 Snowflake 查詢 JSON 資料

簡單 JSON

:

SELECT
  customer_info:cust_age, -- Use colon to access cust_age from column
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

查詢結果將年齡、姓名、email 拆成獨立欄位

在 Snowflake 查詢巢狀 JSON

巢狀 JSON 範例

巢狀 JSON，其中 address 是物件，含 street、city、state 鍵值

冒號：:
點號：.

以冒號／點號查詢巢狀 JSON

用冒號表示法存取值

<column>:<level1_element>:<level2_element>:<level3_element>

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

查詢結果顯示 street_name

用點號表示法存取值

<column>:<level1_element>.<level2_element>.<level3_element>

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

查詢結果顯示 street_name

一起來練習吧！

Snowflake SQL 入門