Aplatizarea datelor semi-structurate

Tipuri de date și funcții în Snowflake

Jake Roach

Field Data Engineer

Date structurate

     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
                |        }
                |    }
Tipuri de date și funcții în Snowflake

Date semi-structurate

Datele stocate între acolade ca perechi cheie-valoare au tipul de date VARIANT

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

$$

$$

$$

  • Similar cu un dicționar Python sau un obiect JSON
  • Permite stocarea datelor în format „brut"
  • Obiecte imbricate, precum address
  • Recuperarea datelor în două moduri diferite
Tipuri de date și funcții în Snowflake

Notația cu punct

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

SELECT

    my_column:my_first_key         -- Top-level

    my_column:my_second_key.a   -- Nested
    my_column:my_second_key.b   -- Nested

...

Facilitează recuperarea valorilor de nivel superior și imbricate din datele VARIANT

$$

  • Două puncte separă <column-name>:<top-level-key>
  • Adăugați . urmat de câmpul imbricat, <column-name>:<top-level-key>.<nested-key>
  • Recuperați valori profund imbricate
Tipuri de date și funcții în Snowflake

Notația cu punct

SELECT
    address_info:school_name,                              -- Top-level, dot-notation

    address_info:address.street_number AS street_number,   -- Nested, dot-notation
    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.
Tipuri de date și funcții în Snowflake

Notația cu paranteze drepte

Oferă o tehnică suplimentară pentru recuperarea valorilor de nivel superior și imbricate

$$

  • <column-name>['<top-level-key']['...']
  • Niveluri multiple de imbricare
  • Similar cu accesarea unui dicționar Python
  • Utilizați ghilimele simple (')!
                    my_column  
 ------------------------------------------------
          {
              "my_first_key": 2025,
               my_second_key": {
                  "a": "alpha",
                  "b": "bravo"
              }
          }

SELECT

    my_column['my_first_key'],       -- Top-level

    my_column['my_second_key']['a']   -- Nested
    my_column['my_second_key']['b']   -- Nested

...
Tipuri de date și funcții în Snowflake

Notația cu paranteze drepte

SELECT

    address_info['school_name'],                      -- Top-level, bracket-notation


    address_info['address']['city'] AS city,           -- Nested, bracket-notation
    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
Tipuri de date și funcții în Snowflake

Transformarea datelor semi-structurate

SELECT
    school_id,

    address_info:school_name AS school_name,               -- Top-level, dot-notation

    address_info:address.street_number AS street_number,   -- Nested, dot-notation
    address_info:address.street_name AS street_name,
    address_info:address.suffix AS suffix,

    address_info['address']['city'] AS city,               -- Nested, bracket-notation
    address_info['address']['zip_code'] AS zip_code

FROM SCHOOLS.school_info;
Tipuri de date și funcții în Snowflake

Transformarea datelor semi-structurate

     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
Tipuri de date și funcții în Snowflake

Să exersăm!

Tipuri de date și funcții în Snowflake

Preparing Video For Download...