Tutorial: Massenladen aus einem lokalen Dateisystem mit COPY¶

Herunterladen der Beispieldatendateien¶

Für dieses Tutorial müssen Sie die von Snowflake bereitgestellten Beispieldatendateien herunterladen.

So laden Sie die Beispieldatendateien herunter und entpacken diese:

1. Klicken Sie mit der rechten Maustaste auf den Namen der Archivdatei data-load-internal.zip, und speichern Sie den Link/die Datei in Ihrem lokalen Dateisystem.

2. Entpacken Sie die Beispieldateien. Das Tutorial setzt voraus, dass Sie die Dateien in die folgenden Verzeichnisse entpackt haben:

• Linux/macOS: /tmp/load

• Windows: C:\tempload

Diese Datendateien enthalten Beispielkontaktdaten 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 a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

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

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

Copy

Die Anweisung CREATE WAREHOUSE erstellt das 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;

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;

Hinweis: Wenn Sie die FILE_FORMAT-Option beim Erstellen des Stagingbereichs angeben, ist es nicht erforderlich, dieselbe FILE_FORMAT-Option im COPY-Befehl anzugeben, der zum Laden von Daten aus dem Stagingbereich verwendet wird.

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;

Staging der CSV-Beispieldatendateien¶

Führen Sie den PUT-Befehl aus, um die CSV-Dateien aus Ihrem lokalen Dateisystem hochzuladen.

• Linux oder macOS

  PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

Lassen Sie uns einen genaueren Blick auf den Befehl werfen:

• file://<file-path>[/]contacts*.csv gibt den vollständigen Verzeichnispfad und die Namen der Dateien auf Ihrem lokalen Computer an, die im Stagingbereich bereitgestellt werden sollen. Beachten Sie, dass Platzhalter für Dateisysteme erlaubt sind.

• @my_csv_stage ist der Name des Stagingbereichs, in dem die Daten bereitgestellt werden sollen.

• auto_compress=true; weist den Befehl an, die Daten im Stagingbereich zu komprimieren. Dies ist auch die Standardeinstellung.

Der Befehl liefert das folgende Ergebnis, das die Stagingdateien anzeigt:

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts1.csv | contacts1.csv.gz |         694 |         506 | NONE               | GZIP               | UPLOADED |         |
| contacts2.csv | contacts2.csv.gz |         763 |         565 | NONE               | GZIP               | UPLOADED |         |
| contacts3.csv | contacts3.csv.gz |         771 |         567 | NONE               | GZIP               | UPLOADED |         |
| contacts4.csv | contacts4.csv.gz |         750 |         561 | NONE               | GZIP               | UPLOADED |         |
| contacts5.csv | contacts5.csv.gz |         887 |         621 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

Staging der JSON-Beispieldatendateien¶

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/contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

Der Befehl liefert das folgende Ergebnis, das die Stagingdateien anzeigt:

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts.json | contacts.json.gz |         965 |         446 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

Liste der Stagingdateien (optional)¶

Sie können die Stagingdateien mit dem Befehl LIST auflisten.

CSV¶

So laden Sie die Daten aus den CSV-Beispieldateien:

1. Laden Sie zunächst die Daten aus einer der Dateien (contacts1.csv.gz). Führen Sie den folgenden Befehl aus:

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts1.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

   Wobei:

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

   • Die ON_ERROR-Klausel gibt an, was zu tun ist, wenn der COPY-Befehl Fehler in den Dateien feststellt. Standardmäßig stoppt der Befehl das Laden von Daten beim ersten Auftreten eines Fehlers. Wir haben den Befehl jedoch so angepasst, dass jede Datei mit einem Fehler übersprungen und mit dem Laden der nächsten Datei fortgefahren wird. Beachten Sie, dass dies nur zur Veranschaulichung dient. Keine der Dateien in diesem Tutorial enthält Fehler.

   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 |
   |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts1.csv.gz | 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 allen Dateien, die mit dem regulären Ausdruck .*contacts[1-5].csv.gz übereinstimmen, in die Tabelle mycsvtable zu laden.

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     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 (.*employees0[1-5].csv.gz) ü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 |
   |-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts3.csv.gz | 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]        |
   | mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

   Das Ergebnis liefert die folgenden Informationen:

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

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

   • Die Daten in contacts3.csv.gz 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.gz in die Tabelle myjsontable.

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

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 |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Beispieldatendateien validieren und alle Fehler abrufen¶

Sie benötigen zunächst die Abfrage-ID, 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¶

1. Beheben Sie die Fehler in den Datensätze manuell in contacts3.csv in Ihrer lokalen Umgebung.

2. Verwenden Sie den Befehl PUT, um die geänderte Datendatei in den Stagingbereich hochzuladen. Die geänderte Datei überschreibt die bestehende Stagingdatei.

   • Linux oder macOS:

     PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

   • Windows:

     PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

3. Kopieren Sie die Daten aus den Stagingdateien in die Tabellen.

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts3.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

Snowflake liefert die folgenden Ergebnisse, die anzeigen, dass die Daten in contacts3.csv.gz erfolgreich geladen wurden.

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts3.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Copy

Überprüfen der geladenen Daten¶

Führen Sie eine SELECT-Abfrage 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 Amazon S3 mit COPY