Diagnostyka błędów pobierania danych Transact-SQL za pomocą plików z błędami

Dotyczy:✅ Magazyn w systemie Microsoft Fabric

Artykuł opisuje, jak rozwiązywać problemy z błędami integracji danych w wzorcach integracji danych T-SQL.

Pozyskiwanie do magazynu przy użyciu funkcji COPY INTO, BULK INSERT, OPENROWSET w instrukcjach CTAS, INSERT, UPDATE i MERGE może zakończyć się niepowodzeniem z kilku powodów. Wartości pliku źródłowego mogą nie być zgodne ze schematem tabeli. Brak wymaganych wartości. Opcje importowania mogą być również nieprawidłowo skonfigurowane.

Ten przewodnik rozwiązywania problemów używa informacji diagnostycznych odrzuconych wierszy do rozwiązywania błędów, przechwytywania błędów na poziomie wiersza i sprawdzania odrzuconych wierszy z metadanymi błędów.

Poprzez sprawdzenie plików błędów wygenerowanych przez COPY INTO i inne polecenia pozyskiwania, można dokładnie wykazać, które wiersze nie zostały przetworzone i dlaczego. Te informacje ułatwiają identyfikowanie problemów z jakością danych lub dostosowywanie ustawień pozyskiwania, naprawianie danych źródłowych i ponowne uruchamianie obciążenia z ufnością.

Tworzenie tabeli docelowej

Przed uruchomieniem poleceń pozyskiwania utwórz tabelę docelową z rygorystycznymi typami i NOT NULL ograniczeniami, aby wcześnie przechwytywać problemy z konwersją i jakością danych.

1. W obszarze roboczym otwórz swój magazyn.

2. Na karcie Narzędzia główne wybierz pozycję Nowe zapytanie SQL.

   

3. Uruchom następującą instrukcję:

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

Można użyć wielu obsługiwanych metod, w tym pozyskiwania z funkcją COPY INTO lub pozyskiwania za pomocą języka Transact-SQL. Wybierz metodę pozyskiwania, która najlepiej pasuje do źródła danych, formatu i wymagań automatyzacji. Poniższy przykład COPY INTO ilustruje typowy wzorzec importowania na potrzeby ładowania danych z plików zewnętrznych do tabeli.

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

Ta instrukcja nie może pozyskać danych, jeśli pliki źródłowe nie są zgodne ze schematem tabeli docelowej. Typowe przyczyny to niedopasowane liczby kolumn, niezgodne typy danych lub wartości, których nie można przechowywać w tabeli docelowej. Jeśli pozyskiwanie napotka wartości, których nie można przekonwertować na schemat docelowy, instrukcja zwraca błąd podobny do następującego:

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

Ten błąd wskazuje, że nie można przekonwertować co najmniej jednego wiersza na typy kolumn docelowych.

Badanie błędów za pomocą funkcji MAXERRORS i ERRORFILE

Użyj poniższych opcji, aby kontynuować pozyskiwanie, gdy liczba błędów na poziomie wiersza jest poniżej zdefiniowanego progu i do przechowywania szczegółów diagnostycznych w określonej lokalizacji.

• MAXERRORS Ustawia maksymalną liczbę tolerowanych błędów na poziomie wiersza podczas przyjmowania danych.
• ERRORFILE określa, gdzie baza danych zapisuje odrzucone wiersze i szczegóły błędu.

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

Operacja ładowania kończy się powodzeniem tylko wtedy, gdy liczba odrzuconych wierszy jest niższa niż MAXERRORS. Gdy błędy są przechwytywane, operacja ładowania danych zapisuje:

• error.jsonl do diagnostyki ustrukturyzowanej
• row.csv dla odrzuconych wierszy źródłowych

Znajdowanie i odpytywanie odrzuconych wierszy

Baza danych zapisuje informacje o błędach w uporządkowanej hierarchii folderów w skonfigurowanej lokalizacji dla błędów. Te foldery ułatwiają śledzenie konkretnych wykonań i korelowanie diagnostyki z jedną instrukcją dotyczącą pozyskiwania danych.

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

Użyj OPENROWSET, aby odczytać ustrukturyzowaną diagnostykę w error.jsonl, określając, która wartość nie powiodła się, która kolumna docelowa została dotknięta i skąd pochodzi wiersz z błędem.

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

Zestaw wyników zazwyczaj zawiera jeden wiersz na odrzucony rekord, na przykład:

Plik error.jsonl zawiera jeden obiekt JSON na wiersz. Każdy obiekt zawiera właściwości wymienione w poprzedniej tabeli. W poniższej tabeli opisano szczegółowo każdą właściwość.

Przegląd odrzuconych wierszy

Po przejrzeniu ustrukturyzowanych informacji diagnostycznych można sprawdzić oryginalne dane źródłowe, których baza danych nie mogła pozyskać. Dane wyjściowe dotyczące odrzuconych wierszy zawierają kopie rekordów źródłowych, zachowane dokładnie w takiej formie, w jakiej występowały w plikach wejściowych. Diagnozowanie odrzuconych wierszy generuje pliki zawierające tylko rekordy, które nie zostały poprawnie pozyskane.

• Jeśli pozyskujesz pliki CSV przy użyciu polecenia COPY INTO (FILE_TYPE = 'CSV'), odrzucone dane zawierają row.csv-plik. Ten plik pasuje do struktury pliku źródłowego i zawiera oryginalne wiersze CSV z nieprawidłowymi wartościami.
• Jeśli pozyskujesz pliki JSONL przy użyciu OPENROWSET(FORMAT = 'JSONL'), odrzucone dane wyjściowe zawierają plik row.jsonl. Ten plik zachowuje oryginalne obiekty JSON, które spowodowały błędy importu.

Użyj tych plików, aby zweryfikować główną przyczynę błędów, takich jak źle sformułowane wartości, nieoczekiwane NULL wartości lub wiersze nagłówka, które zostały niepoprawnie przeanalizowane jako dane.

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

row.csv Schemat pasuje do źródłowego kształtu CSV i zawiera tylko wiersze, które zakończyły się niepowodzeniem pozyskiwania.

Przykładowe dane wyjściowe odrzuconych wierszy:

Na podstawie tych informacji diagnostycznych można zidentyfikować następujące problemy z wprowadzaniem danych:

• Wiersz nagłówka w pliku źródłowym jest błędnie analizowany jako wiersz danych. Aby rozwiązać, instrukcja COPY INTO powinna korzystać z opcji FIRSTROW = 2.
• Wiersz dla kolumny vendorID w pliku źródłowym (C1) zawiera NULL wartości, ale odpowiednia kolumna w tabeli docelowej TaxiTrips jest zdefiniowana jako NOT NULL.
• Wiersz w pliku źródłowym dotyczącym kolumny passengerCount zawiera nieprawidłową wartość (N/A), której nie można przekonwertować do docelowej kolumny int.

Napraw problemy z wczytywaniem i ponownie wczytaj dane

Po zidentyfikowaniu przyczyny niepowodzeń ingestii, usuń problem i ponownie załaduj dane, których dotyczy problem. Podejście korygujące zależy od tego, skąd pochodzi błąd.

Naprawianie schematu tabeli docelowej

Jeśli dane źródłowe nie są zgodne ze schematem tabeli docelowej, zaktualizuj definicję tabeli. Typowe poprawki obejmują zmienianie typów danych kolumn lub usuwanie restrykcyjnych ograniczeń, takich jak NOT NULL.

W niektórych scenariuszach może być konieczne usunięcie i ponowne utworzenie tabeli docelowej przed ponownym pozyskiwaniem danych.

Popraw dane źródłowe i ponownie wczytaj pliki

Jeśli pozyskiwanie nie powiedzie się z powodu nieprawidłowych lub niespójnych wartości w plikach źródłowych, popraw te wartości i ponownie pozyskaj dane. Na przykład zastąp wartości symboli zastępczych, takich jak N/A, pustymi wartościami lub prawidłowymi wartościami domyślnymi.

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

Podczas ponownego pozyskiwania poprawionych danych użyj jawnej ścieżki pliku wskazującej nowy plik zawierający tylko poprawione dane, a nie ścieżkę folderu odwołującą się do oryginalnych plików. Takie podejście zapobiega ponownemu wczytywaniu wierszy, które zostały wcześniej pomyślnie załadowane, i unika duplikowania danych.

Ponowne przetwarzanie odrzuconych wierszy przy użyciu tabeli przejściowej

Można załadować odrzucone wiersze do tabeli przejściowej, naprawić dane przy użyciu instrukcji Transact-SQL modyfikacji danych, a następnie ponownie pozyskać poprawione wiersze.

Poniższa CREATE TABLE AS SELECT instrukcja ładuje odrzucone wiersze do tabeli w celu dalszego przetwarzania:

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

Po skorygowaniu danych wstaw oczyszczone wiersze do tabeli docelowej.

Treści powiązane

• Pozyskiwanie danych przy użyciu funkcji COPY INTO
• Pozyskiwanie danych przy użyciu języka Transact-SQL