Fehlerbehebung von Transact-SQL Ingestionsfehlern mit Fehlerdateien

Gilt für:✅ Warehouse in Microsoft Fabric

In diesem Artikel wird beschrieben, wie Sie Fehler bei der Erfassung in T-SQL-Erfassungsmustern beheben.

Der Import in ein Lager unter Verwendung der Funktionen COPY INTO, BULK INSERT, OPENROWSET in den Anweisungen CTAS, INSERT, UPDATE und MERGE kann aus mehreren Gründen fehlschlagen. Quelldateiwerte stimmen möglicherweise nicht mit dem Tabellenschema überein. Erforderliche Werte fehlen möglicherweise. Aufnahmeoptionen sind möglicherweise auch falsch konfiguriert.

In diesem Handbuch zur Problembehandlung werden die Diagnoseinformationen für abgelehnte Zeilen verwendet, um Fehler auf Zeilenebene zu beheben und abgelehnte Zeilen mit Fehlermetadaten zu prüfen.

Indem Sie die von COPY INTO und anderen Aufnahmebefehlen generierten Fehlerdateien untersuchen, können Sie genau bestimmen, welche Zeilen nicht aufgenommen werden konnten und warum. Diese Informationen helfen Ihnen, Probleme mit der Datenqualität zu identifizieren oder die Einstellungen für die Datenaufnahme anzupassen, die Quelldaten zu korrigieren und die Ausführung zuversichtlich erneut durchzuführen.

Erstellen der Zieltabelle

Erstellen Sie vor dem Ausführen von Aufnahmebefehlen eine Zieltabelle mit strengen Typen und NOT NULL Einschränkungen, sodass Sie Konvertierungs- und Datenqualitätsprobleme frühzeitig abfangen.

1. Öffnen Sie In Ihrem Lagerarbeitsbereich Ihr Lager.

2. Wählen Sie auf der Registerkarte " Start " die Option "Neue SQL-Abfrage" aus.

   

3. Führen Sie die folgende Anweisung aus:

   DROP TABLE IF EXISTS dbo.TaxiTrips;
   GO
   CREATE TABLE dbo.TaxiTrips
   (
       vendorID         int    NOT NULL,
       startLat         float  NOT NULL,
       startLon         float  NOT NULL,
       endLat           float  NOT NULL,
       endLon           float  NOT NULL,
       passengerCount   int    NOT NULL,
       tripDistance     float  NOT NULL,
       fareAmount       float  NOT NULL,
       mtaTax           float  NOT NULL,
       totalAmount      float  NOT NULL
   );
   

Sie können mehrere unterstützte Methoden verwenden, z. B. das Aufnehmen mit COPY INTO oder das Aufnehmen mit Transact-SQL. Wählen Sie die Aufnahmemethode aus, die ihren Anforderungen an Datenquelle, Format und Automatisierung am besten entspricht. Das folgende COPY INTO-Beispiel veranschaulicht ein allgemeines Aufnahmemuster zum Laden von Daten aus externen Dateien in eine Tabelle.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH ( FILE_TYPE = 'CSV' );

Diese Anweisung kann keine Daten aufnehmen, wenn die Quelldateien nicht mit dem Zieltabellenschema übereinstimmen. Häufige Ursachen sind nicht übereinstimmende Spaltenanzahlen, inkompatible Datentypen oder Werte, die nicht in der Zieltabelle gespeichert werden können. Wenn bei der Aufnahme Werte gefunden werden, die nicht in das Zielschema konvertiert werden können, gibt die Anweisung einen Fehler ähnlich dem folgenden zurück.

Msg 13812, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage)
for row starting at byte offset 0, column 1 (vendorID).
Underlying data description:
file 'https://....blob.core.windows.net/Files/yellow/tripdata.csv'.

Dieser Fehler gibt an, dass mindestens eine Zeile nicht in die Zielspaltentypen konvertiert werden kann.

Untersuchen von Fehlern mit MAXERRORS und ERRORFILE

Verwenden Sie die folgenden Optionen, um die Aufnahme fortzusetzen, wenn die Anzahl der Fehler auf Zeilenebene unter einem definierten Schwellenwert liegt und Diagnosedetails an einem bestimmten Speicherort gespeichert werden soll.

• MAXERRORS legt die maximale Anzahl tolerierter Fehler auf Zeilenebene während der Aufnahme fest.
• ERRORFILE Gibt an, wo die Datenbank abgelehnte Zeilen und Fehlerdetails schreibt.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
WITH (
    FILE_TYPE = 'CSV',
    MAXERRORS = 10,
    ERRORFILE = 'https://{storage-path}.blob.core.windows.net/Files/yellow/'
);

Der Ladevorgang ist nur erfolgreich, wenn die Anzahl der abgelehnten Zeilen niedriger als MAXERRORSist. Wenn Fehler erfasst werden, schreibt der Einspeisevorgang Folgendes:

• error.jsonl für strukturierte Diagnosen
• row.csv für abgelehnte Quellzeilen

Suchen und Abfragen abgelehnter Zeilen

Die Datenbank schreibt Fehlerinformationen in eine strukturierte Ordnerhierarchie unter dem konfigurierten Fehlerspeicherort. Diese Ordner helfen Ihnen, eine bestimmte Ausführung nachzuverfolgen und Diagnosedaten mit einem spezifischen Ingestion-Befehl zu korrelieren.

ERRORFILE/
+-- _rejectedrows/
    +-- <timestamp>/
        +-- <statement_id>/
            +-- error.jsonl
            +-- row.csv or rows.jsonl

Verwenden Sie OPENROWSET, um die strukturierte Diagnose in error.jsonl zu lesen, sodass Sie ermitteln können, welcher Wert fehlgeschlagen ist, welche Zielspalte betroffen war und woher die fehlerhafte Zeile stammt:

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/error.jsonl'
);

Das Resultset enthält in der Regel eine Zeile pro abgelehnten Datensatz, z. B.:

Die error.jsonl Datei enthält ein JSON-Objekt pro Zeile. Jedes Objekt enthält die in der vorherigen Tabelle aufgeführten Eigenschaften. In der folgenden Tabelle werden die einzelnen Eigenschaften ausführlich beschrieben.

Abgelehnte Zeilen überprüfen

Nachdem Sie die strukturierten Diagnoseinformationen überprüft haben, können Sie die ursprünglichen Quelldaten prüfen, die die Datenbank nicht aufnehmen konnte. Die Ausgabe der abgelehnten Zeilen enthält Kopien der Quelldatensätze, die genau so erhalten bleiben, wie sie in den Eingabedateien angezeigt wurden. Die Diagnose der abgelehnten Zeilen generiert Dateien, die nur die Datensätze enthalten, die fehlgeschlagen sind:

• Wenn Sie CSV-Dateien mit COPY INTO (FILE_TYPE = 'CSV') importieren, enthält die abgelehnte Ausgabe eine row.csv-Datei. Diese Datei entspricht der Quelldateistruktur und enthält die ursprünglichen CSV-Zeilen mit ungültigen Werten.
• Wenn Sie JSONL-Dateien mithilfe von OPENROWSET(FORMAT = 'JSONL') ingestieren, enthält die abgelehnte Ausgabe eine row.jsonl Datei. Diese Datei behält die ursprünglichen JSON-Objekte bei, die zu Aufnahmefehlern geführt haben.

Verwenden Sie diese Dateien, um die Ursache der Fehler zu überprüfen, z. B. falsch formatierte Werte, unerwartete NULL Werte oder Kopfzeilen, die falsch als Daten analysiert wurden.

SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

Das row.csv Schema stimmt mit dem CSV-Quell-Shape überein, und es enthält nur Zeilen, bei denen die Erfassung fehlgeschlagen ist.

Beispiel für abgelehnte Zeilenausgabe:

Basierend auf diesen Diagnoseinformationen können Sie die folgenden Aufnahmeprobleme identifizieren:

• Die Kopfzeile in der Quelldatei wird versehentlich als Datenzeile analysiert. Um dies zu beheben, sollte die COPY INTO Anweisung die FIRSTROW = 2 Option verwenden.
• Eine Zeile in der Quelldatei für die vendorID Spalte (C1) enthält NULL Werte, aber die entsprechende Spalte in der Zieltabelle TaxiTrips ist definiert als NOT NULL.
• Eine Zeile in der Quelldatei für die passengerCount Spalte enthält einen ungültigen Wert (N/A), der nicht in die Zielspalte konvertiert werden kann.

Beheben von Aufnahmeproblemen und erneutes Aufnehmen von Daten

Nachdem Sie die Ursache für Datenübernahmefehler identifiziert haben, beheben oder korrigieren Sie das Problem und übernehmen Sie die betroffenen Daten erneut. Der Lösungsansatz hängt davon ab, wo der Fehler entsteht.

Korrigieren des Zieltabellenschemas

Wenn die Quelldaten nicht dem Zieltabellenschema entsprechen, aktualisieren Sie die Tabellendefinition. Häufige Korrekturen umfassen das Ändern von Spaltendatentypen oder das Entfernen restriktiver Einschränkungen wie NOT NULL.

In einigen Szenarien müssen Sie die Zieltabelle möglicherweise ablegen und neu erstellen, bevor Sie die Daten erneut aufnehmen.

Korrektur von Quelldaten und Wiedereinlesen von Dateien

Wenn die Aufnahme aufgrund ungültiger oder inkonsistenter Werte in den Quelldateien fehlschlägt, korrigieren Sie diese Werte, und nehmen Sie die Daten erneut auf. Ersetzen Sie beispielsweise Platzhalterwerte, wie N/A, durch leere Werte oder gültige Standardwerte.

COPY INTO [dbo].[TaxiTrips]
FROM 'https://{storage-path}.blob.core.windows.net/Files/yellow/tripdata_corrected.csv'
WITH ( FILE_TYPE = 'CSV' );

Verwenden Sie beim erneuten Aufnehmen korrigierter Daten einen expliziten Dateipfad, der auf die neue Datei verweist, die nur korrigierte Daten enthält, anstatt einen Ordnerpfad, der auf die ursprünglichen Dateien verweist. Dieser Ansatz verhindert das erneute Aufnehmen von Zeilen, die zuvor erfolgreich geladen wurden, und verhindert doppelte Daten.

Erneutes Verarbeiten abgelehnter Zeilen mithilfe einer Stagingtabelle

Sie können abgelehnte Zeilen in eine Stagingtabelle laden, die Daten mithilfe Transact-SQL Datenänderungsanweisungen korrigieren und dann die korrigierten Zeilen erneut aufnehmen.

Die folgende CREATE TABLE AS SELECT Anweisung lädt abgelehnte Zeilen in eine Tabelle zur weiteren Verarbeitung:

CREATE TABLE TaxiTrip_RejectedRows AS
SELECT *
FROM OPENROWSET(
    BULK 'https://{storage-path}.blob.core.windows.net/Files/yellow/_rejectedrows/*/*/row.csv'
);

Nachdem Sie die Daten korrigiert haben, fügen Sie die bereinigten Zeilen in die Zieltabelle ein.

Verwandte Inhalte

• Aufnehmen von Daten mithilfe von COPY INTO
• Erfassen von Daten mithilfe von Transact-SQL