Xử lý dữ liệu bán cấu trúc

Nhập môn Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Dữ liệu có cấu trúc vs. bán cấu trúc

Ví dụ dữ liệu có cấu trúc

| 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    |

Ví dụ dữ liệu bán cấu trúc

Dữ liệu khách hàng bán cấu trúc dạng JSON, khách hàng 2 có hai email

Giới thiệu JSON

JavaScript Object Notation
Trường hợp dùng phổ biến: Web API và tệp cấu hình
Cấu trúc dữ liệu JSON:
- Cặp khóa-giá trị, ví dụ: cust_id: 1

Ví dụ bản ghi JSON của khách hàng có id, tên, tuổi và email

JSON trong Snowflake

Hỗ trợ JSON gốc
Linh hoạt với lược đồ thay đổi

So sánh:

Postgres: Dùng JSONB
Snowflake: Dùng VARIANT

Cách Snowflake lưu trữ dữ liệu JSON

VARIANT hỗ trợ kiểu OBJECT và ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Tạo bảng Snowflake để xử lý dữ liệu JSON

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

Hàm cho dữ liệu bán cấu trúc

PARSE_JSON
- expr: Dữ liệu JSON ở dạng chuỗi
- Trả về: kiểu VARIANT, đối tượng JSON hợp lệ

PARSE_JSON

Ví dụ:

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

Kết quả của PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Cú pháp: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Trả về: đối tượng JSON

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

Bảng với cột `customer_info` chứa dữ liệu ở dạng JSON

Truy vấn dữ liệu JSON trong Snowflake

JSON đơn giản

:

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;

Kết quả truy vấn hiển thị tuổi, tên và email thành các cột riêng

Truy vấn JSON lồng nhau trong Snowflake

Ví dụ JSON lồng nhau

Dữ liệu JSON lồng nhau: address là một object có các cặp khóa-giá trị street, city, state

Dấu hai chấm: :
Dấu chấm: .

Truy vấn JSON lồng nhau bằng dấu hai chấm/dấu chấm

Truy cập giá trị bằng ký hiệu dấu hai chấm

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Kết quả truy vấn hiển thị street_name

Truy cập giá trị bằng ký hiệu dấu chấm

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Kết quả truy vấn hiển thị street_name

Ayo berlatih!

Nhập môn Snowflake SQL