Problemen met Transact-SQL opnamefouten met foutbestanden oplossen

Van toepassing op:✅ Warehouse in Microsoft Fabric

In dit artikel wordt beschreven hoe u opnamefouten in T-SQL-opnamepatronen oplost.

Opname in een magazijn met behulp van COPY INTO, BULK INSERT, OPENROWSET functie in CTAS, INSERT, UPDATE en MERGE instructies kan om verschillende redenen mislukken. Bronbestandswaarden komen mogelijk niet overeen met het tabelschema. Vereiste waarden ontbreken mogelijk. Opnameopties kunnen ook onjuist zijn geconfigureerd.

In deze gids voor probleemoplossing worden de diagnostische gegevens voor geweigerde rijen gebruikt om fouten op te lossen, fouten op rijniveau vast te leggen en geweigerde rijen met foutmetagegevens te inspecteren.

Door de foutbestanden te onderzoeken die zijn gegenereerd door COPY INTO en andere opnameopdrachten, kunt u precies bepalen welke rijen niet kunnen worden opgenomen en waarom. Deze informatie helpt u bij het identificeren van problemen met de gegevenskwaliteit, het aanpassen van de importinstellingen, het oplossen van de brongegevens en het proces opnieuw uitvoeren met vertrouwen.

Maak de doeltabel

Voordat u opnameopdrachten uitvoert, maakt u een doeltabel met strikte typen en NOT NULL beperkingen, zodat u vroeg conversie- en gegevenskwaliteitsproblemen kunt ondervangen.

1. Open uw magazijn in uw magazijnwerkruimte.

2. Selecteer op het tabblad Startde optie Nieuwe SQL-query.

   

3. Voer de volgende instructie uit:

   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
   );
   

U kunt meerdere ondersteunde methoden gebruiken, waaronder opnemen met COPY INTO of opnemen met Transact-SQL. Kies de opnamemethode die het beste past bij uw gegevensbron, indeling en automatiseringsvereisten. Het volgende COPY INTO-voorbeeld illustreert een gemeenschappelijk opnamepatroon voor het laden van gegevens uit externe bestanden in een tabel.

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

Deze instructie kan geen gegevens opnemen als de bronbestanden niet overeenkomen met het doeltabelschema. Veelvoorkomende oorzaken zijn niet-overeenkomende kolomaantallen, incompatibele gegevenstypen of waarden die niet kunnen worden opgeslagen in de doeltabel. Als de opname waarden tegenkomt die niet kunnen worden geconverteerd naar het doelschema, retourneert de instructie een fout die vergelijkbaar is met de volgende:

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'.

Deze fout geeft aan dat een of meer rijen niet kunnen worden geconverteerd naar de doelkolomtypen.

Fouten onderzoeken met MAXERRORS en ERRORFILE

Gebruik de volgende opties om door te gaan met opnemen wanneer het aantal fouten op rijniveau onder een gedefinieerde drempelwaarde ligt en diagnostische gegevens op een opgegeven locatie wilt opslaan.

• MAXERRORS stelt het maximum aantal getolereerde fouten op rijniveau in tijdens opname.
• ERRORFILE geeft aan waar de database geweigerde rijen en foutdetails schrijft.

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/'
);

De laadbewerking slaagt alleen wanneer het aantal geweigerde rijen lager is dan MAXERRORS. Wanneer fouten worden vastgelegd, schrijft de opnamebewerking het volgende:

• error.jsonl voor gestructureerde diagnostische gegevens
• row.csv voor geweigerde bronrijen

Geweigerde rijen zoeken en er query's op uitvoeren

De database schrijft foutinformatie naar een gestructureerde maphiërarchie onder de geconfigureerde foutlocatie. Met deze mappen traceer je een specifiek uitvoeringsproces en correleer je diagnostische gegevens met één invoeropdracht.

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

Gebruik OPENROWSET om de gestructureerde diagnostische gegevens in error.jsonl te lezen, zodat u kunt vaststellen welke waarde is mislukt, welke doelkolom is beïnvloed en waar de mislukte rij vandaan komt.

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

De resultatenset bevat doorgaans één rij per geweigerde record, bijvoorbeeld:

Het error.jsonl bestand bevat één JSON-object per regel. Elk object bevat de eigenschappen die in de voorgaande tabel worden vermeld. In de volgende tabel worden alle eigenschappen gedetailleerd beschreven.

Geweigerde rijen controleren

Nadat u de gestructureerde diagnostische gegevens hebt bekeken, kunt u de oorspronkelijke brongegevens inspecteren die de database niet kon opnemen. De uitvoer van geweigerde rijen bevat kopieën van de bronrecords, die exact behouden blijven zoals ze in de invoerbestanden worden weergegeven. De diagnostiek voor geweigerde rijen genereert bestanden die alleen de records bevatten die niet zijn ingevoerd.

• Als u CSV-bestanden opneemt met behulp van COPY INTO (FILE_TYPE = 'CSV'), bevat de geweigerde uitvoer een row.csv bestand. Dit bestand komt overeen met de bronbestandsstructuur en bevat de oorspronkelijke CSV-rijen met ongeldige waarden.
• Als u JSONL-bestanden opneemt met behulp van OPENROWSET(FORMAT = 'JSONL'), bevat de geweigerde uitvoer een row.jsonl bestand. Dit bestand behoudt de oorspronkelijke JSON-objecten die opnamefouten hebben veroorzaakt.

Gebruik deze bestanden om de hoofdoorzaak van de fouten te valideren, zoals onjuiste waarden, onverwachte NULL waarden of veldnamenrijen die onjuist zijn geparseerd als gegevens.

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

Het row.csv schema komt overeen met de csv-bronshape en bevat alleen rijen waarvoor de opname is mislukt.

Voorbeeld van uitvoer voor geweigerde rijen:

Op basis van deze diagnostische gegevens kunt u de volgende opnameproblemen identificeren:

• De koprij in het bronbestand wordt per ongeluk als gegevensrij geparsed. Om dit op te lossen, moet de COPY INTO instructie de FIRSTROW = 2 optie gebruiken.
• Een rij in het bronbestand voor de vendorID kolom (C1) bevat NULL waarden, maar de bijbehorende kolom in de doeltabel TaxiTrips wordt gedefinieerd als NOT NULL.
• Een rij in het bronbestand voor de passengerCount kolom bevat een ongeldige waarde (N/A) die niet kan worden geconverteerd naar de doelkolom int .

Opnameproblemen oplossen en gegevens opnieuw opnemen

Nadat u de oorzaak van opnamefouten hebt geïdentificeerd, corrigeert u het probleem en neemt u de betrokken gegevens opnieuw op. De herstelbenadering is afhankelijk van waar de fout vandaan komt.

Herstel het schema van de doeltabel

Als de brongegevens niet voldoen aan het doeltabelschema, werkt u de tabeldefinitie bij. Veelvoorkomende oplossingen zijn het wijzigen van kolomgegevenstypen of het verwijderen van beperkende beperkingen, zoals NOT NULL.

In sommige scenario's moet u mogelijk de doeltabel verwijderen en opnieuw creëren voordat u de gegevens opnieuw opneemt.

Brongegevens corrigeren en bestanden opnieuw opnemen

Als de opname mislukt vanwege ongeldige of inconsistente waarden in de bronbestanden, corrigeert u deze waarden en neemt u de gegevens opnieuw op. Vervang bijvoorbeeld tijdelijke aanduidingen, zoals N/A, door lege waarden of geldige standaardwaarden.

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

Wanneer u gecorrigeerde gegevens opnieuw opneemt, gebruikt u een expliciet bestandspad dat verwijst naar het nieuwe bestand dat alleen gecorrigeerde gegevens bevat, in plaats van een mappad dat verwijst naar de oorspronkelijke bestanden. Met deze methode voorkomt u dat rijen die eerder zijn geladen, opnieuw worden opgenomen en dubbele gegevens worden voorkomen.

Geweigerde rijen opnieuw verwerken met behulp van een faseringstabel

U kunt geweigerde rijen laden in een faseringstabel, de gegevens herstellen met behulp van Transact-SQL instructies voor het wijzigen van gegevens en vervolgens de gecorrigeerde rijen opnieuw opnemen.

Met de volgende CREATE TABLE AS SELECT instructie worden geweigerde rijen in een tabel geladen voor verdere verwerking:

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

Nadat u de gegevens hebt gecorrigeerd, voegt u de opgeschoonde rijen in de doeltabel in.

Verwante inhoud

• Gegevens opnemen met COPY INTO
• Gegevens opnemen met Behulp van Transact-SQL