Der Umgang mit semi-strukturierten Daten

Einführung in Snowflake SQL

George Boorman

Senior Curriculum Manager, DataCamp

Strukturiert versus halbstrukturiert

Beispiel für strukturierte Daten

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

Beispiel für halbstrukturierte Daten

Halbstrukturierte Kundendaten im JSON-Format, wobei Kunde 2 zwei E-Mail-Adressen hat.

JSON vorstellen

JavaScript -Objekt notation
Häufige Anwendungsfälle: Web-APIs und Konfigurationsdateien
JSON-Datenstruktur:
- Schlüssel-Wert-Paare, z. B. cust_id: 1

Beispiel für JSON-Datensätze von Kunden mit Kunden-ID, Name, Alter und E-Mail-Adresse

JSON in Snowflake

Native JSON-Unterstützung
Flexibel für sich entwickelnde Schemata

Vergleiche

Postgres: Verwendung JSONB
Snowflake: Verwendung VARIANT

Wie Snowflake JSON-Daten speichert

VARIANT Unterstützt die Datentypen OBJECT und ARRAY
- OBJEKT: { "key": "value"}
- ARRAY: ["list", "of", "values"]

Erstellen einer Snowflake-Tabelle für die Verarbeitung von JSON-Daten

CREATE TABLE cust_info_json_data (
  customer_id
  customer_info VARIANT -- Datentyp VARIANT
);

Halbstrukturierte Datenfunktionen

PARSE_JSON
- expr: JSON-Daten im String-Format
- Gibt zurück: Typ „ VARIANT “, gültiges JSON-Objekt

PARSE_JSON

Beispiel:

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

Ergebnis von PARSE_JSON

OBJECT_CONSTRUCT

OBJECT_CONSTRUCT
- Syntax
- Retouren 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'
    )

Tabelle mit der Spalte „customer_info” mit Daten im JSON-Format gespeichert

Abfragen von JSON-Daten in Snowflake

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

Abfrageergebnis mit Kundenalter, Kundenname und E-Mail-Adresse in separaten Spalten

Abfragen von verschachtelten JSON-Daten in Snowflake

Beispiel für verschachtelte JSON-Daten

Eine verschachtelte JSON-Datei, in der „address“ ein Objekt mit Schlüssel-Wert-Paaren für „street“, „city“ und „state“ ist

Doppelpunkt: :
Punkt: .

Abfragen von verschachtelten JSON-Daten mit Doppelpunkt-/Punkt-Notationen

Auf Werte mit Doppelpunkt-Notation zugreifen

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

SELECT 
    customer_info:address:street AS street_name 
FROM 
    cust_info_json_data

Abfrageergebnis mit Straßennamen

Auf Werte mit der Punktnotation zugreifen

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

SELECT
    customer_info:address.street AS street_name
FROM
    cust_info_json_data

Abfrageergebnis mit Straßennamen

Lass uns üben!

Einführung in Snowflake SQL