Làm phẳng dữ liệu bán cấu trúc

Kiểu dữ liệu và Hàm trong Snowflake

Jake Roach

Field Data Engineer

Dữ liệu có cấu trúc

     school_id  |    school_name    |  street_number  |  street_name  |  suffix   |      city      |  zip_code
    ----------- | ----------------- | --------------- | ------------- | --------- | -------------- | ----------
      s_19219   |  West Aurora HS   |       879       |    Main       |    St.    |  West Aurora   |   25041
      s_77465   |  Springtown HS    |      1645       |    Cherry     |    Rd.    |  Springtown    |   14556

     school_id  |                address_info        
    ----------- | ------------------------------------------
      s_19219   |    {
                |        "school_name": "West Aurora HS",
                |        "address": {
                |            "street_number": 879,
                |            "street_name": "Main",
                |            "suffix":  "St."
                |            "city": "West Aurora",
                |            "zip_code": 25041
                |        }
                |    }

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

Dữ liệu lưu bằng ngoặc nhọn theo cặp khóa-giá trị có kiểu VARIANT

{
    "school_name": "West Aurora HS",
    "address": {  -- Nested object
        "street_number": 879,
        "street_name": "Main",
        "suffix": "St.",
        "city": "West Aurora",
        "zip_code": 25041
    }
}

Giống dict Python hoặc đối tượng JSON
Cho phép lưu dữ liệu dạng “thô”
Có thể lồng đối tượng, như address
Truy xuất theo hai cách

Ký pháp dấu chấm

                  my_column  
 -------------------------------------------
        {
            "my_first_key": 2025,
            "my_second_key": {
                "a": "alpha",
                "b": "bravo"
            }
        }

SELECT

    my_column:my_first_key         -- Cấp cao

    my_column:my_second_key.a   -- Lồng nhau
    my_column:my_second_key.b   -- Lồng nhau

...

Giúp truy xuất dễ dàng giá trị cấp cao và lồng nhau từ dữ liệu VARIANT

Dấu hai chấm phân tách <column-name>:<top-level-key>
Thêm . rồi trường lồng: <column-name>:<top-level-key>.<nested-key>
Lấy giá trị lồng sâu

Ký pháp dấu chấm

SELECT
    address_info:school_name,                              -- Cấp cao, ký pháp dấu chấm

    address_info:address.street_number AS street_number,   -- Lồng nhau, ký pháp dấu chấm
    address_info:address.street_name AS street_name,
    address_info:address.suffix AS suffix

FROM SCHOOLS.school_info;

              school_name    |  street_number  |  street_name  |  suffix  
           ----------------- | --------------- | ------------- | ---------
            West Aurora HS   |       879       |    Main       |    St.   
            Springtown HS    |      1645       |    Cherry     |    Rd.

Ký pháp ngoặc vuông

Cung cấp thêm cách lấy giá trị cấp cao và lồng nhau

<column-name>['<top-level-key']['...']
Nhiều lớp lồng nhau
Giống truy xuất từ dict Python
Nhớ dùng dấu nháy đơn (')!

                    my_column  
 ------------------------------------------------
          {
              "my_first_key": 2025,
               my_second_key": {
                  "a": "alpha",
                  "b": "bravo"
              }
          }

SELECT

    my_column['my_first_key'],       -- Cấp cao

    my_column['my_second_key']['a']   -- Lồng nhau
    my_column['my_second_key']['b']   -- Lồng nhau

...

Ký pháp ngoặc vuông

SELECT

    address_info['school_name'],                      -- Cấp cao, ký pháp ngoặc


    address_info['address']['city'] AS city,           -- Lồng nhau, ký pháp ngoặc
    address_info['address']['zip_code'] AS zip_code


FROM SCHOOLS.school_info;

                     school_name    |      city      |  zip_code
                  ----------------- | -------------- | ----------
                   West Aurora HS   |  West Aurora   |   25041
                   Springtown HS    |  Springtown    |   14556

Biến đổi dữ liệu bán cấu trúc

SELECT
    school_id,

    address_info:school_name AS school_name,               -- Cấp cao, ký pháp dấu chấm

    address_info:address.street_number AS street_number,   -- Lồng nhau, ký pháp dấu chấm
    address_info:address.street_name AS street_name,
    address_info:address.suffix AS suffix,

    address_info['address']['city'] AS city,               -- Lồng nhau, ký pháp ngoặc
    address_info['address']['zip_code'] AS zip_code

FROM SCHOOLS.school_info;

Biến đổi dữ liệu bán cấu trúc

     school_id  |                address_info        
    ----------- | ------------------------------------------
      s_19219   |    {
                |        "school_name": "West Aurora HS",
                |        "address": {
                |            "street_number": 879,
                |            "street_name": "Main",
                |            "suffix":  "St."
                |            "city": "West Aurora",
                |            "zip_code": 25041
                |        }
                |    }

     school_id  |    school_name    |  street_number  |  street_name  |  suffix   |      city      |  zip_code
    ----------- | ----------------- | --------------- | ------------- | --------- | -------------- | ----------
      s_19219   |  West Aurora HS   |       879       |    Main       |    St.    |  West Aurora   |   25041
      s_77465   |  Springtown HS    |      1645       |    Cherry     |    Rd.    |  Springtown    |   14556

Ayo berlatih!

Kiểu dữ liệu và Hàm trong Snowflake