Tutorial: Massenladen aus Amazon S3 mit COPY¶

Allgemeine Informationen zu den Beispieldatendateien¶

Snowflake bietet Beispieldateien, die in einem öffentlichen S3-Bucket bereitgestellt werden.

Die Beispieldatendateien enthalten Beispiel-Kontaktinformationen in den folgenden Formaten:

• CSV-Dateien, die eine Kopfzeile und fünf Datensätze enthalten. Das Feldtrennzeichen ist das Pipe-Zeichen (|). Das folgende Beispiel zeigt eine Kopfzeile und einen Datensatz:

  ID|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode
  6|Reed|Moses|Neque Corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|Ap #225-4351 Dolor Ave|Titagarh|62631
  

  Copy

• Eine einzelne Datei im JSON-Format, die ein Array und drei Objekte enthält. Es folgt ein Beispiel für ein Array, das eines der Objekte enthält:

  [
   {
     "customer": {
       "address": "509 Kings Hwy, Comptche, Missouri, 4848",
       "phone": "+1 (999) 407-2274",
       "email": "blankenship.patrick@orbin.ca",
       "company": "ORBIN",
       "name": {
         "last": "Patrick",
         "first": "Blankenship"
       },
       "_id": "5730864df388f1d653e37e6f"
     }
   },
  ]
  

  Copy

Erstellen von Datenbank, Tabellen und Warehouse¶

Führen Sie die folgenden Anweisungen aus, um eine Datenbank, zwei Tabellen (für CSV- und JSON-Daten) und ein virtuelles Warehouse zu erstellen, die für dieses Tutorial benötigt werden. Nachdem Sie das Tutorial abgeschlossen haben, können Sie diese Objekte wieder löschen.

CREATE OR REPLACE DATABASE mydatabase;

CREATE OR REPLACE TEMPORARY TABLE mycsvtable (
     id INTEGER,
     last_name STRING,
     first_name STRING,
     company STRING,
     email STRING,
     workphone STRING,
     cellphone STRING,
     streetaddress STRING,
     city STRING,
     postalcode STRING);

CREATE OR REPLACE TEMPORARY TABLE myjsontable (
     json_data VARIANT);

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
     WAREHOUSE_SIZE='X-SMALL'
     AUTO_SUSPEND = 120
     AUTO_RESUME = TRUE
     INITIALLY_SUSPENDED=TRUE;

Beachten Sie Folgendes:

• Die CREATE DATABASE-Anweisung erstellt eine Datenbank. Die Datenbank enthält automatisch ein Schema namens „public“.

• Die CREATE TABLE-Anweisungen erstellen Zieltabellen für CSV- und JSON-Daten. Die Tabellen sind temporär, d. h. sie bleiben nur für die Dauer der Benutzersitzung bestehen und sind für andere Benutzer nicht sichtbar.

• Die CREATE WAREHOUSE-Anweisung erstellt ein Warehouse, das zunächst angehalten ist. Die Anweisung legt auch AUTO_RESUME = true fest, wodurch das Warehouse automatisch gestartet wird, wenn Sie SQL-Anweisungen ausführen, die Computeressourcen benötigen.

Erstellen eines Dateiformatobjekts für CSV-Daten¶

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

CREATE OR REPLACE FILE FORMAT mycsvformat
   TYPE = 'CSV'
   FIELD_DELIMITER = '|'
   SKIP_HEADER = 1;

Wobei:

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

• FIELD_DELIMITER = '|' gibt an, dass das Zeichen „| “ ein Feldtrennzeichen ist. Der Standardwert ist „,“.

• SKIP_HEADER = 1 zeigt an, dass die Quelldatei eine Kopfzeile enthält. Der COPY-Befehl überspringt diese Kopfzeilen beim Laden von Daten. Der Standardwert ist 0.

Erstellen eines Dateiformatobjekts für JSON-Daten¶

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

CREATE OR REPLACE FILE FORMAT myjsonformat
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE;

Copy

Wobei:

• TYPE = 'JSON' gibt den Dateiformattyp der Quelldatei an.

• STRIP_OUTER_ARRAY = TRUE weist den COPY-Befehl an, die Wurzelklammern ([]) beim Laden von Daten in die Tabelle wegzulassen.

Erstellen eines Stagingbereichs für CSV-Datendateien¶

Führen Sie CREATE STAGE aus, um den Stagingbereich my_csv_stage zu erstellen:

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://snowflake-docs';

Erstellen eines Stagingbereichs für JSON-Datendateien¶

Führen Sie CREATE STAGE aus, um den Stagingbereich my_json_stage zu erstellen:

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat
  URL = 's3://snowflake-docs';

CSV¶

So laden Sie die Daten aus den CSV-Beispieldateien:

1. Laden Sie zunächst die Daten aus einer der Dateien im Ordner /tutorials/dataloading/ mit dem Namen contacts1.csv in die Tabelle mycsvtable. Führen Sie den folgenden Befehl aus:

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/contacts1.csv
     ON_ERROR = 'skip_file';
   

   Copy

   Wobei:

   • Die FROM-Klausel gibt den Speicherort der Staging-Datendatei an (Stagingbereichsname gefolgt vom Dateinamen).

   • Die ON_ERROR = 'skip_file'-Klausel gibt an, was zu tun ist, wenn der COPY-Befehl Fehler in den Dateien feststellt. Wenn in diesem Fall der Befehl in einem der Datensätze einer Datei auf einen Datenfehler stößt, wird diese Datei übersprungen. Wenn Sie keine ON_ERROR-Klausel angeben, wird standardmäßig abort_statement verwendet, wodurch der COPY-Befehl beim ersten Fehler abgebrochen wird, der bei einem der Datensätze einer Datei auftritt.

   Der COPY-Befehl gibt als Ergebnis die Namen der kopierten Dateien und zugehörige Informationen zurück:

   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
   |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

2. Laden Sie den Rest der Stagingdateien in die Tabelle mycsvtable.

   Im folgenden Beispiel wird die Mustererkennung verwendet, um Daten aus Dateien, die mit dem regulären Ausdruck .*contacts[1-5].csv übereinstimmen, in die Tabelle mycsvtable zu laden.

   COPY INTO mycsvtable
     FROM @my_csv_stage/tutorials/dataloading/
     PATTERN='.*contacts[1-5].csv'
     ON_ERROR = 'skip_file';
   

   Copy

   Dabei gibt die PATTERN-Klausel an, dass der Befehl Daten aus Dateien mit Namen laden soll, die mit dem regulären Ausdruck .*contacts[1-5].csv übereinstimmen.

   Der COPY-Befehl gibt als Ergebnis die Namen der kopierten Dateien und zugehörige Informationen zurück:

   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | file                                                    | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
   |---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts3.csv | LOAD_FAILED |           5 |           0 |           1 |           2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error |                3 |                     1 | "MYCSVTABLE"[11]        |
   | s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

   Das Ergebnis liefert die folgenden Informationen:

   • Die Daten in contacts1.csv werden ignoriert, da Sie die Daten bereits erfolgreich geladen haben.

   • Die Daten folgender Dateien wurden erfolgreich geladen: contacts2.csv, contacts4.csv und contacts5.csv.

   • Die Daten in contacts3.csv wurden aufgrund von 2 Datenfehlern übersprungen. Der nächste Schritt in diesem Tutorial befasst sich mit der Validierung und Behebung der Fehler.

JSON¶

Laden Sie die Staging-Datendatei contacts.json in die Tabelle myjsontable.

COPY INTO myjsontable
  FROM @my_json_stage/tutorials/dataloading/contacts.json
  ON_ERROR = 'skip_file';

Copy

COPY gibt ein Ergebnis zurück, das den Namen der kopierten Datei und zugehörige Informationen enthält:

+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts.json | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Beispieldatendateien validieren und alle Fehler abrufen¶

Sie müssen zunächst die Abfrage-ID abrufen, die mit dem zuvor ausgeführten COPY INTO-Befehl verbunden ist. Rufen Sie dann die Funktion VALIDATE auf, und geben Sie die Abfrage-ID an.

1. Rufen Sie die Abfrage-ID ab.

   1. Melden Sie sich bei Snowsight an.

   2. Stellen Sie sicher, dass die Rolle in Snowsight mit der Rolle übereinstimmt, die Sie in SnowSQL zum Ausführen der SQL-Anweisungen dieses Tutorials verwenden.

   3. Wählen Sie Monitoring » Query History aus.

   4. Wählen Sie die Zeile für den spezifischen COPY INTO-Befehl aus, um den Abfrageinformationsbereich zu öffnen.

   5. Kopieren Sie den Query ID-Wert.

2. Überprüfen Sie die Ausführung des COPY INTO-Befehls, der mit dieser Abfrage-ID verbunden ist, und speichern Sie die Fehler in einer neuen Tabelle namens save_copy_errors.

   1. Führen Sie den folgenden Befehl in SnowSQL aus. Ersetzen Sie query_id durch den Wert Query ID.

      CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
      

      Copy

   2. Fragen Sie die Tabelle save_copy_errors ab.

      SELECT * FROM SAVE_COPY_ERRORS;
      

      Copy

      Die Abfrage gibt die folgenden Ergebnisse zurück:

      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      | ERROR                                                                                                                                                                | FILE                                | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |   CODE | SQL_STATE | COLUMN_NAME                   | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD                                                                                                                                     |
      |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------|
      | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | mycsvtable/contacts3.csv.gz         |    3 |         1 |         234 | parsing  | 100080 |     22000 | "MYCSVTABLE"[11]              |          1 |              2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 |
      | Field delimiter '|' found while expecting record delimiter '\n'                                                                                                      | mycsvtable/contacts3.csv.gz         |    5 |       125 |         625 | parsing  | 100016 |     22000 | "MYCSVTABLE"["POSTALCODE":10] |          4 |              5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Copy

Das Ergebnis zeigt zwei Datenfehler in mycsvtable/contacts3.csv.gz an:

• Number of columns in file (11) does not match that of the corresponding table (10)

  In Zeile 1 wurde ein Bindestrich versehentlich durch einen senkrechten Strich (|), das Trennzeichen der Datendatei, ersetzt, wodurch eine zusätzliche Spalte im Datensatz erstellt wurde.

  

• Field delimiter '|' found while expecting record delimiter 'n'

  In Zeile 5 wurde nach einem Bindestrich ein zusätzlicher senkrechter Strich (|) eingefügt, was zum Unterbrechen des Datensatzes führte.

  

Fehler beheben und Datendateien erneut laden¶

Bei regelmäßigem Gebrauch würden Sie die problematischen Datensätze manuell korrigieren und in eine neue Datendatei schreiben. Sie würden dann die festen Datendateien im S3-Bucket bereitstellen und versuchen, die Daten aus den Dateien neu zu laden. In diesem Tutorial verwenden Sie die von Snowflake bereitgestellten Beispieldaten, die Sie nicht korrigieren müssen.

Überprüfen der geladenen Daten¶

Führen Sie eine SELECT-Anweisung aus, um sicherzustellen, dass die Daten erfolgreich geladen wurden.

Tutorialdaten bereinigen (optional)¶

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.

Weitere Tutorials zum Laden von Daten¶

• Snowflake in 20 Minuten

• Tutorial: Massenladen aus einem lokalen Dateisystem mit COPY