Hantera semistrukturerad data

Introduktion till Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Strukturerad kontra semistrukturerad

Exempel på strukturerad data

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

Exempel på semistrukturerad data

Kundens semistrukturerade data i JSON-format där kund 2 har två e-postadresser

Introduktion till JSON

JavaScript Object Notation
Vanliga användningsområden: Webb-API:er och konfigurationsfiler
JSON-datastruktur:
- Nyckel-värde-par, t.ex. cust_id: 1

Exempel på JSON-poster med kund-id, namn, ålder och e-post

JSON i Snowflake

Inbyggt JSON-stöd
Flexibelt för föränderliga scheman

Jämförelser:

Postgres: Använder JSONB
Snowflake: Använder VARIANT

Hur Snowflake lagrar JSON-data

VARIANT stöder datatyperna OBJECT och ARRAY
- OBJECT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Skapa en Snowflake-tabell för JSON-data

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

Funktioner för semistrukturerad data

PARSE_JSON
- expr: JSON-data i strängformat
- Returnerar: typen VARIANT, giltigt JSON-objekt

PARSE_JSON

Exempel:

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

Resultat av PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Syntax: OBJECT_CONSTRUCT( [<key1>, <value1> [, <keyN>, <valueN> ...]] )
- Returnerar: JSON-objekt

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

Tabell med kolumnen `customer_info` där data lagras i JSON-format

Fråga JSON-data i Snowflake

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

Frågeresultat med kundålder, kundnamn och e-post som separata kolumner

Fråga nästlad JSON-data i Snowflake

Exempel på nästlad JSON

Nästlad JSON-data där address är ett objekt med nyckel-värde-paren street, city och state

Kolon: :
Punkt: .

Fråga nästlad JSON med kolon- och punktnotation

Åtkomst med kolonnotation

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Frågeresultat med street_name

Åtkomst med punktnotation

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Frågeresultat med street_name

Nu kör vi en övning!

Introduktion till Snowflake SQL