处理半结构化数据

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 有两个邮箱

认识 JSON

JavaScript Object Notation
常见用例：Web API 与配置文件
JSON 数据结构：
- 键值对，如 cust_id: 1

客户 JSON 示例：包含客户 id、姓名、年龄和邮箱

Snowflake 中的 JSON

原生支持 JSON
适应演进的模式，灵活

对比：

Postgres：使用 JSONB
Snowflake：使用 VARIANT

Snowflake 如何存储 JSON 数据

VARIANT 支持 OBJECT 与 ARRAY 类型
- OBJECT：{ "key": "value"}
- ARRAY：["list", "of", "values"]

创建处理 JSON 的 Snowflake 表

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, -- 使用冒号从列中取 cust_age
  customer_info:cust_name,
  customer_info:cust_email,
FROM 
  cust_info_json_data;

查询结果：将年龄、姓名和邮箱作为独立列

在 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

Passons à la pratique !

Snowflake SQL 入门