Laden von JSON-Daten in eine relationale Tabelle

Für das Hochladen von JSON-Daten in eine Tabelle haben Sie folgende Optionen:

  • Speichern Sie JSON-Objekte nativ in einer Spalte vom Typ VARIANT (wie unter Tutorial: Massenladen aus einem lokalen Dateisystem mit COPY gezeigt).

  • Speichern Sie JSON-Objekte nativ in einer Zwischentabelle, und verwenden Sie dann die Funktion FLATTEN, um JSON-Elemente in separate Spalten einer Tabelle zu extrahieren (wie unter Tutorial: JSON-Grundlagen gezeigt)

  • Transformieren Sie JSON-Elemente direkt in Tabellenspalten, wie in diesem Tutorial gezeigt.

Der Befehl COPY in diesem Tutorial verwendet eine SELECT-Anweisung, um einzelne Elemente in einer JSON-Stagingdatei abzufragen.

Die Beispielbefehle in diesem Tutorial enthalten eine PUT-Anweisung. Wir empfehlen, diese Befehle in SnowSQL auszuführen, das den PUT-Befehl unterstützt. Clients wie Snowsight und die klassische Konsole unterstützen den PUT-Befehl nicht.

Unter diesem Thema:

Voraussetzungen

Für dieses Tutorial müssen Sie Folgendes tun:

  • Herunterladen einer von Snowflake bereitgestellte JSON-Datendatei.

  • Erstellen einer Datenbank, einiger Tabellen und eines virtuellen Warehouses für dieses Tutorial.

Datenbank, Tabelle und virtuelles Warehouse sind grundlegende Snowflake-Objekte, die für die meisten Snowflake-Aktivitäten benötigt werden.

Datendatei zum Laden

Zum Herunterladen der JSON-Beispieldatendatei klicken Sie auf sales.json. Wenn die Datei durch Klicken auf den Link nicht heruntergeladen werden kann, klicken Sie mit der rechten Maustaste auf den Link, und speichern Sie den Link bzw. die Datei in Ihrem lokalen Dateisystem.

Das Tutorial setzt voraus, dass Sie die JSON-Datendateien in die folgenden Verzeichnisse entpackt haben:

  • Linux/macOS: /tmp/load

  • Windows: C:\tempload

Die Datendatei enthält JSON-Musterdaten zu Hausverkäufen. Ein JSON-Beispielobjekt wird angezeigt:

{
   "location": {
      "state_city": "MA-Lexington",
      "zip": "40503"
   },
   "sale_date": "2017-3-5",
   "price": "275836"
}
Copy

Erstellen von Datenbank, Tabelle und virtuellem Warehouse

Mit dem folgenden Skript werden Objekte speziell für die Verwendung mit diesem Tutorial erstellt. Wenn Sie das Tutorial abgeschlossen haben, können Sie die Objekte wieder löschen.

 create or replace database mydatabase;

 use schema mydatabase.public;

CREATE OR REPLACE TEMPORARY TABLE home_sales (
  city STRING,
  zip STRING,
  state STRING,
  type STRING DEFAULT 'Residential',
  sale_date timestamp_ntz,
  price STRING
  );

create or replace warehouse mywarehouse with
  warehouse_size='X-SMALL'
  auto_suspend = 120
  auto_resume = true
  initially_suspended=true;

use warehouse mywarehouse;
Copy

Beachten Sie, dass diese Befehle eine temporäre Tabelle erstellen. Eine temporäre Tabelle bleibt nur für die Dauer der Benutzersitzung bestehen, in der sie erstellt wurde. Sie ist für andere Benutzer nicht sichtbar.

Schritt 1: Dateiformatobjekt erstellen

Führen Sie den Befehl CREATE FILE FORMAT aus, um das Dateiformat sf_tut_json_format zu erstellen.

CREATE OR REPLACE FILE FORMAT sf_tut_json_format
  TYPE = JSON;
Copy

TYPE = 'JSON' gibt den Dateiformattyp der Quelldatei an. CSV ist der Standard-Dateiformattyp.

Schritt 2: Stagingobjekt erstellen

Führen Sie CREATE STAGE aus, um den internen Stagingbereich sf_tut_stage zu erstellen.

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
 FILE_FORMAT = sf_tut_json_format;
Copy

Ähnlich wie bei temporären Tabellen werden temporäre Stagingbereiche am Ende der Sitzung automatisch gelöscht.

Schritt 3: Datendatei im Stagingbereich bereitstellen

Führen Sie den PUT-Befehl aus, um die JSON-Dateien aus Ihrem lokalen Dateisystem in den benannten Stagingbereich hochzuladen.

  • Linux oder macOS

    PUT file:///tmp/load/sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;
    Copy

  • Windows

    PUT file://C:\temp\load\sales.json @sf_tut_stage AUTO_COMPRESS=TRUE;
    Copy

Schritt 4: Daten in die Zieltabelle kopieren

Laden Sie die Staging-Datendatei sales.json.gz in die Tabelle home_sales.

COPY INTO home_sales(city, state, zip, sale_date, price)
   FROM (SELECT SUBSTR($1:location.state_city,4),
                SUBSTR($1:location.state_city,1,2),
                $1:location.zip,
                to_timestamp_ntz($1:sale_date),
                $1:price
         FROM @sf_tut_stage/sales.json.gz t)
   ON_ERROR = 'continue';
Copy

Beachten Sie, dass sich „$1“ in der SELECT-Abfrage auf die einzelne Spalte bezieht, in der JSON gespeichert ist. Die Abfrage verwendet auch die folgenden Funktionen:

  • Die Funktion SUBSTR , SUBSTRING zum Extrahieren der Werte für Ort und Bundesland aus dem JSON-Schlüssel „state_city“.

  • Die Funktion TO_TIMESTAMP / TO_TIMESTAMP_*, um den JSON-Schlüsselwert „sale_date“ in einen Zeitstempel umzuwandeln.

Führen Sie die folgende Abfrage aus, um zu überprüfen, ob die Daten kopiert wurden.

SELECT * from home_sales;
Copy

Schritt 5: Erfolgreich kopierte Datendateien entfernen

Nachdem Sie sich vergewissert haben, dass Sie die Daten erfolgreich aus Ihrem Stagingbereich in die Tabellen kopiert haben, können Sie die Datendateien mit dem Befehl REMOVE aus dem internen Stagingbereich entfernen, um Datenspeicher zu sparen.

REMOVE @sf_tut_stage/sales.json.gz;
Copy

Schritt 6: Bereinigen

Führen Sie die folgenden DROP <Objekt>-Befehle aus, um Ihr System in den Zustand zu versetzen, bevor Sie das Tutorial begonnen haben:

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;
Copy

Wenn Sie die Datenbank löschen, werden automatisch alle untergeordneten Datenbankobjekte wie Tabellen entfernt.

Sprache: Deutsch