Skript: Laden von JSON-Daten in eine relationale Tabelle

Mit dem kommentierten Skript in diesem Tutorial werden JSON-Beispieldaten direkt aus Staging-Datendateien in separate Spalten einer relationalen Tabelle geladen, wodurch die Notwendigkeit einer Stagingtabelle entfällt.

Das Skript verwendet die folgenden Funktionen, um die bereitgestellten Daten während des Ladens zu ändern:

Beachten Sie, dass das Skript eine PUT-Anweisung enthält, sodass es nicht auf der Seite Worksheets Worksheet tab der Snowflake-Weboberfläche (oder auf anderen Oberflächen, die PUT nicht unterstützen) ausgeführt werden kann. Stattdessen empfehlen wir die Verwendung von SnowSQL, dem Snowflake-CLI-Client, um das Skript auszuführen.

Unter diesem Thema:

Voraussetzungen

  • Erfordert ein aktives virtuelles Warehouse.

  • JSON-Beispieldatendatei (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.

    Kopieren Sie dann die Datei in Ihren temporären Ordner:

    • macOS oder Linux: //tmp

    • Windows: Öffnen Sie ein Explorer-Fenster, und geben Sie in der Adressleiste %TEMP% ein.

Beispieldaten

Im Folgenden wird eine repräsentative Zeile aus der JSON-Beispieldatei angezeigt:

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

SQL-Skript

Bemerkung

Die PUT-Anweisung in diesem Skript geht davon aus, dass Sie eine macOS- oder Linux-Umgebung verwenden. Wenn Sie Windows verwenden, passen Sie die Anweisung an, wie in den Kommentaren angegeben.

/* Create a target relational table for the JSON data. The table is temporary, meaning it persists only for   */
/* the duration of the user session and is not visible to other users.                                        */

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

/* Create a named file format with the file delimiter set as none and the record delimiter set as the new     */
/* line character.                                                                                            */
/*                                                                                                            */
/* When loading semi-structured data (e.g. JSON), you should set CSV as the file format type (default value). */
/* You could use the JSON file format, but any error in the transformation would stop the COPY operation,     */
/* even if you set the ON_ERROR option to continue or skip the file.                                          */

CREATE OR REPLACE FILE FORMAT sf_tut_csv_format
  FIELD_DELIMITER = NONE
  RECORD_DELIMITER = '\\n';

/* Create a temporary internal stage that references the file format object.                                  */
/* Similar to temporary tables, temporary stages are automatically dropped at the end of the session.         */

CREATE OR REPLACE TEMPORARY STAGE sf_tut_stage
  FILE_FORMAT = sf_tut_csv_format;

/* Stage the data file.                                                                                       */
/*                                                                                                            */
/* Note that the example PUT statement references the macOS or Linux location of the data file.               */
/* If you are using Windows, execute the following statement instead:                                         */

-- PUT %TEMP%/sales.json @sf_tut_stage;

PUT file:///tmp/sales.json @sf_tut_stage;

/* Load the JSON data into the relational table.                                                              */
/*                                                                                                            */
/* A SELECT query in the COPY statement identifies a numbered set of columns in the data files you are        */
/* loading from. Note that all JSON data is stored in a single column ($1).                                   */

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

/* Query the relational table                                                                                 */

SELECT * from home_sales;