Vorbereiten von Datendateien¶

Best Practices und Einschränkungen für Dateigrößen¶

Beachten Sie für eine optimale Ladeleistung und zur Vermeidung von Größenbeschränkungen die folgenden Richtlinien für die Dimensionierung von Dateien. Beachten Sie, dass diese Empfehlungen sowohl für das Laden von Massendaten als auch für das kontinuierliche Laden mit Snowpipe gelten.

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_08');

Max LOB size (16777216) exceeded

COPY INTO <table>
  FROM @~/<file>.json
  FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);

{
  "ID": 1,
  "CustomerDetails": {
    "RegistrationDate": 158415500,
    "FirstName": "John",
    "LastName": "Doe",
    "Events": [
      {
        "Type": "LOGIN",
        "Time": 1584158401,
        "EventID": "NZ0000000001"
      },
      /* ... */
      /* this array contains thousands of elements */
      /* with total size exceeding 16 MB */
      /* ... */
      {
        "Type": "LOGOUT",
        "Time": 1584158402,
        "EventID": "NZ0000000002"
      }
    ]
  }
}

CREATE OR REPLACE TABLE mytable AS
  SELECT
    t1.$1:ID AS id,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR AS RegistrationDate,
    t1.$1:CustomerDetails:FirstName::VARCHAR AS First_Name,
    t1.$1:CustomerDetails:LastName::VARCHAR AS as Last_Name,
    t2.value AS Event
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

+----+-------------------+------------+------------+------------------------------+
| ID | REGISTRATION_DATE | FIRST_NAME | LAST_NAME  | EVENT                        |
|----+-------------------+------------+------------+------------------------------|
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000001", |
|    |                   |            |            |   "Time": 1584158401,        |
|    |                   |            |            |   "Type": "LOGIN"            |
|    |                   |            |            | }                            |
|                     ... thousands of rows ...                                   |
| 1  | 158415500         | John       | Doe        | {                            |
|    |                   |            |            |   "EventID": "NZ0000000002", |
|    |                   |            |            |   "Time": 1584158402,        |
|    |                   |            |            |   "Type": "LOGOUT"           |
|    |                   |            |            | }                            |
+----+-------------------+------------+------------+------------------------------+

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR(16777216),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  event VARCHAR(16777216));

INSERT INTO mytable
  SELECT
    t1.$1:ID,
    t1.$1:CustomerDetails:RegistrationDate::VARCHAR,
    t1.$1:CustomerDetails:FirstName::VARCHAR,
    t1.$1:CustomerDetails:LastName::VARCHAR,
    t2.value
  FROM @json t1,
    TABLE(FLATTEN(INPUT => $1:CustomerDetails:Events)) t2;

<?xml version='1.0' encoding='UTF-8'?>
<osm version="0.6" generator="osmium/1.14.0">
  <node id="197798" version="17" timestamp="2021-09-06T17:01:27Z" />
  <node id="197824" version="7" timestamp="2021-08-04T23:17:18Z" >
    <tag k="highway" v="traffic_signals"/>
  </node>
  <!--  thousands of node elements with total size exceeding 16 MB -->
  <node id="197826" version="4" timestamp="2021-08-04T16:43:28Z" />
</osm>

CREATE OR REPLACE TABLE mytable AS
  SELECT
    value:"@id" AS id,
    value:"@version" AS version,
    value:"@timestamp"::datetime AS TIMESTAMP,
    value:"$" AS tags
  FROM @mystage,
    LATERAL FLATTEN(INPUT => $1:"$")
  WHERE value:"@" = 'node';

+--------+---------+-------------------------+---------------------------------------------+
| ID     | VERSION | TIMESTAMP               | TAGS                                        |
|--------+---------+-------------------------+---------------------------------------------|
| 197798 | 17      | 2021-09-06 17:01:27.000 | ""                                          |
| 197824 | 7       | 2021-08-04 23:17:18.000 | <tag k="highway" v="traffic_signals"></tag> |
|                   ... thousands of rows ...                                              |
| 197826 | 4       | 2021-08-04 16:43:28.000 | ""                                          |
+--------+---------+-------------------------+---------------------------------------------+

CREATE OR REPLACE TABLE mytable AS
  SELECT
    ST_SIMPLIFY($1:geo, 10) AS geo
  FROM @mystage;

CREATE OR REPLACE TABLE mytable (
  id VARCHAR,
  registration_date VARCHAR,
  first_name VARCHAR,
  last_name VARCHAR);

COPY INTO mytable (
  id,
  registration_date,
  first_name,
  last_name
) FROM (
    SELECT
      $1:ID,
      $1:CustomerDetails::OBJECT:RegistrationDate::VARCHAR,
      $1:CustomerDetails::OBJECT:FirstName::VARCHAR,
      $1:CustomerDetails::OBJECT:LastName::VARCHAR
    FROM @mystage
);

Kontinuierliches Laden von Daten (d. h. Snowpipe) und Dateigrößen¶

Snowpipe wurde entwickelt, um neue Daten typischerweise innerhalb einer Minute nach dem Senden einer Dateibenachrichtigung zu laden. Das Laden kann jedoch bei wirklich großen Dateien erheblich länger dauern oder in Fällen, in denen eine ungewöhnliche Menge an Computeressourcen erforderlich ist, um die neuen Daten zu dekomprimieren, zu entschlüsseln und zu transformieren.

Zusätzlich zum Ressourcenverbrauch sind Betriebskosten für die Verwaltung von Dateien in der internen Ladewarteschlange in den Nutzungskosten für Snowpipe enthalten. Dieser Overhead steigt in Abhängigkeit von der Anzahl der Dateien, die der Warteschlange zum Laden hinzugefügt werden. Diese Overhead-Kosten erscheinen auf der Abrechnung als Snowpipe-Gebühren, da Snowpipe für die Ereignisbenachrichtigungen bei der automatischen Aktualisierung der externen Tabellen verwendet wird.

Für ein möglichst effizientes und kostengünstiges Laden mit Snowpipe wird empfohlen, die unter Best Practices und Einschränkungen für Dateigrößen (unter diesem Thema) bereitgestellten Empfehlungen zur Dateigröße zu befolgen. Das Laden von Datendateien, die etwa 100-250 MB oder größer sind, reduziert die Overhead-Kosten im Verhältnis zur Menge der insgesamt bis zu dem Punkt geladenen Daten, an dem die Overhead-Kosten unerheblich werden.

Wenn es länger als eine Minute dauert, MBs von Daten in Ihrer Quellanwendung zusammenzufassen, sollten Sie erwägen, einmal pro Minute eine neue (möglicherweise kleinere) Datendatei zu erstellen. Dieser Ansatz führt in der Regel zu einem ausgewogenen Verhältnis zwischen Kosten (d. h. Ressourcen, die für die Verwaltung der Snowpipe-Warteschlange und den eigentlichen Ladevorgang benötigt werden) und Leistung (d. h. Latenzzeit beim Laden).

Das Erstellen von kleineren Datendateien und deren Staging im Cloudspeicher häufiger als einmal pro Minute hat folgende Nachteile:

• Eine Verkürzung der Latenzzeit zwischen Staging und Laden von Daten kann nicht garantiert werden.

• Die Betriebskosten für die Verwaltung von Dateien in der internen Ladewarteschlange sind in den Nutzungskosten für Snowpipe enthalten. Diese Verwaltungskosten steigen in Abhängigkeit von der Anzahl der Dateien, die zum Laden in die Warteschlange eingefügt werden.

Verschiedene Tools können Datendateien aggregieren und stapeln. Eine praktische Option ist Amazon Data Firehose. Firehose ermöglicht es, sowohl die gewünschte Dateigröße, die sogenannte Puffergröße, als auch das Warteintervall zu definieren, nach dem eine neue Datei gesendet wird (in diesem Fall an den Cloudspeicher), das sogenannte Pufferintervall. Weitere Informationen dazu finden Sie in der Amazon Data Firehose-Dokumentation. Wenn Ihre Quellanwendung normalerweise innerhalb einer Minute genug Daten sammelt, um Dateien zu füllen, die über dem empfohlenen Maximum für eine optimale Parallelverarbeitung liegen, können Sie die Puffergröße verringern, um die Bereitstellung kleinerer Dateien auszulösen. Wenn Sie die Einstellung des Pufferintervalls bei 60 Sekunden (dem Minimalwert) belassen, vermeiden Sie, zu viele Dateien zu erstellen oder die Latenzzeit zu erhöhen.

Vorbereiten von durch Trennzeichen getrennten Textdateien¶

Beachten Sie die folgenden Richtlinien, wenn Sie Dateien mit durch Trennzeichen getrenntem Text (CSV) zum Laden vorbereiten:

• UTF-8 ist der Standardzeichensatz, es werden jedoch auch zusätzliche Codierungen unterstützt. Verwenden Sie die Dateiformatoption ENCODING, um den Zeichensatz für die Datendateien festzulegen. Weitere Informationen dazu finden Sie unter CREATE FILE FORMAT.

• Felder, die Trennzeichen enthalten, sollten in Anführungszeichen (einfach oder doppelt) gesetzt werden. Wenn die Daten einfache oder doppelte Anführungszeichen enthalten, müssen diese Anführungszeichen in Escape-Zeichen eingeschlossen werden.

• Zeilenumbrüche werden auf Windows-Systemen häufig in Verbindung mit einem Zeilenvorschubzeichen verwendet, um das Ende einer Zeile zu markieren (\r \n). Felder, die Zeilenumbrüche enthalten, sollten ebenfalls in Anführungszeichen (einfach oder doppelt) gesetzt werden.

• Die Anzahl der Spalten in jeder Zeile sollte konsistent sein.

Semistrukturierte Datendateien und Spaltenbildung¶

Wenn semistrukturierte Daten in eine VARIANT-Spalte eingefügt werden, verwendet Snowflake bestimmte Regeln, um so viele der Daten wie möglich in eine spaltenweise Form zu extrahieren. Der Rest der Daten wird als eine einzige Spalte in einer geparsten, semistrukturierten Struktur gespeichert.

Standardmäßig extrahiert Snowflake maximal 200 Elemente pro Partition und Tabelle. Wenn Sie dieses Limit erhöhen möchten, wenden Sie sich an den Snowflake-Support.

Richtlinien für numerische Daten¶

• Vermeiden Sie eingebettete Zeichen wie Kommas (z. B. 123,456).

• Wenn eine Zahl eine gebrochene Komponente enthält, sollte sie vom gesamten Zahlenabschnitt durch einen Dezimalpunkt getrennt werden (z. B. 123456.789).

• Nur Oracle. Die Oracle-Typen NUMBER oder NUMERIC erlauben eine beliebige Dezimalstellenzahl, d. h. sie akzeptieren Werte mit dezimalen Komponenten, auch wenn der Datentyp nicht mit einer Genauigkeit oder Dezimalstellenzahl definiert wurde. In Snowflake hingegen müssen Spalten, die für Werte mit Dezimalkomponenten ausgelegt sind, mit einer Dezimalstellenzahl definiert werden, um den Dezimalanteil zu erhalten.

Richtlinien für Datums- und Zeitstempel-Daten¶

• Weitere Informationen zu den unterstützten Formaten für Datums-, Uhrzeit- und Zeitstempeldaten finden Sie unter Eingabe- und Ausgabeformate für Datum und Uhrzeit.

• Nur Oracle. Der Oracle-Datentyp DATE kann Datums- oder Zeitstempelinformationen enthalten. Wenn Ihre Oracle-Datenbank DATE-Spalten enthält, die auch zeitbezogene Informationen speichern, ordnen Sie diese Spalten in Snowflake einem TIMESTAMP-Datentyp statt DATE zu.