Risolvere gli errori di ingestione Transact-SQL con i file di errore

si applica a:✅ Magazzino di dati in Microsoft Fabric

Questo articolo descrive come risolvere gli errori di inserimento nei modelli di inserimento T-SQL.

L'inserimento in un magazzino tramite le funzioni COPY INTO, BULK INSERT, OPENROWSET e le istruzioni CTAS, INSERT, UPDATE e MERGE può fallire per diversi motivi. I valori dei file di origine potrebbero non corrispondere allo schema della tabella. I valori obbligatori potrebbero non essere presenti. Le opzioni di inserimento potrebbero anche essere configurate in modo errato.

Questa guida alla risoluzione dei problemi usa le informazioni di diagnostica delle righe rifiutate per risolvere gli errori, acquisire errori a livello di riga ed esaminare le righe rifiutate con metadati di errore.

Esaminando i file di errore generati da COPY INTO e altri comandi di inserimento, è possibile individuare esattamente quali righe non sono riuscite a inserire e perché. Queste informazioni consentono di identificare i problemi di qualità dei dati o di regolare le impostazioni di inserimento, correggere i dati di origine ed eseguire di nuovo il carico con sicurezza.

Creare la tabella di destinazione

Prima di eseguire i comandi di inserimento, creare una tabella di destinazione con tipi e NOT NULL vincoli rigorosi in modo da intercettare tempestivamente i problemi di conversione e qualità dei dati.

1. Nell'area di lavoro del tuo magazzino, apri il magazzino.

2. Nella scheda Home selezionare Nuova query SQL.

   

3. Eseguire l'istruzione seguente:

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

È possibile usare più metodi supportati, tra cui l'inserimento con COPY INTO o l'inserimento con Transact-SQL. Scegliere il metodo di inserimento più adatto ai requisiti di origine dati, formato e automazione. Nell'esempio COPY INTO seguente viene illustrato un modello di inserimento comune per il caricamento di dati da file esterni in una tabella.

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

Questa istruzione può non riuscire ad inserire dati se i file di origine non corrispondono allo schema della tabella di destinazione. Le cause comuni includono conteggi di colonne non corrispondenti, tipi di dati incompatibili o valori che non possono essere archiviati nella tabella di destinazione. Se l'inserimento rileva valori che non possono essere convertiti nello schema di destinazione, l'istruzione restituisce un errore simile al seguente:

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

Questo errore indica che una o più righe non possono essere convertite nei tipi di colonna di destinazione.

Analizzare gli errori con MAXERRORS e ERRORFILE

Usare le opzioni seguenti per continuare l'inserimento quando il numero di errori a livello di riga è inferiore a una soglia definita e per archiviare i dettagli di diagnostica in una posizione specificata.

• MAXERRORS imposta il numero massimo di errori a livello di riga tollerati durante l'inserimento.
• ERRORFILE specifica dove il database scrive le righe rifiutate e i dettagli dell'errore.

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

L'operazione di caricamento ha esito positivo solo quando il numero di righe rifiutate è inferiore a MAXERRORS. Quando vengono acquisiti errori, l'operazione di inserimento scrive:

• error.jsonl per la diagnostica strutturata
• row.csv per le righe di origine rifiutate

Individuare e interrogare le righe scartate

Il database scrive le informazioni sugli errori in una gerarchia di cartelle strutturata nel percorso configurato per gli errori. Queste cartelle consentono di tracciare un'esecuzione specifica e correlare la diagnostica a un'istruzione di inserimento:

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

Usare OPENROWSET per leggere la diagnostica strutturata in error.jsonl in modo da identificare il valore non riuscito, la colonna di destinazione interessata e la posizione in cui ha avuto origine la riga con errori:

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

Il set di risultati include in genere una riga per ogni record rifiutato, ad esempio:

Il error.jsonl file contiene un oggetto JSON per riga. Ogni oggetto include le proprietà elencate nella tabella precedente. Nella tabella seguente vengono descritte in dettaglio ogni proprietà.

Esaminare le righe rifiutate

Dopo aver esaminato le informazioni di diagnostica strutturate, è possibile esaminare i dati di origine originali che il database non è riuscito ad inserire. L'output delle righe rifiutate contiene copie dei record di origine, mantenuti esattamente come apparivano nei file di input. La diagnostica delle righe rifiutate genera file che contengono solo i record che hanno avuto esito negativo nell'inserimento:

• Se si inseriscono file CSV usando COPY INTO (FILE_TYPE = 'CSV'), l'output rifiutato include un row.csv file. Questo file corrisponde alla struttura del file di origine e contiene le righe CSV originali con valori non validi.
• Se si inseriscono file JSONL usando OPENROWSET(FORMAT = 'JSONL'), l'output rifiutato include un row.jsonl file. Questo file mantiene gli oggetti JSON originali che hanno causato errori di inserimento.

Usare questi file per convalidare la causa radice degli errori, ad esempio valori in formato non valido, valori imprevisti NULL o righe di intestazione che sono state analizzate erroneamente come dati.

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

Lo row.csv schema corrisponde alla forma CSV di origine e contiene solo le righe che hanno avuto esito negativo nell'inserimento.

Esempio di output di riga rifiutata:

In base a queste informazioni di diagnostica, è possibile identificare i problemi di inserimento seguenti:

• La riga di intestazione nel file di origine viene erroneamente analizzata come riga di dati. Per risolvere il problema, l'istruzione COPY INTO deve usare l'opzione FIRSTROW = 2 .
• Una riga nel file di origine per la vendorID colonna (C1) contiene NULL valori, ma la colonna corrispondente nella tabella di destinazione TaxiTrips è definita come NOT NULL.
• Una riga nel file di origine per la passengerCount colonna contiene un valore non valido (N/A) che non può essere convertito nella colonna int di destinazione.

Risolvere problemi di ingestione e reingestire i dati

Dopo aver identificato la causa degli errori di inserimento, risolvere il problema e re-ingestire i dati interessati. L'approccio di correzione dipende dalla posizione in cui ha origine l'errore.

Correggere lo schema della tabella di destinazione

Se i dati di origine non sono conformi allo schema della tabella di destinazione, aggiornare la definizione della tabella. Le correzioni comuni includono la modifica dei tipi di dati delle colonne o la rimozione di vincoli restrittivi, NOT NULLad esempio .

In alcuni scenari potrebbe essere necessario eliminare e ricreare la tabella di destinazione prima di reinsezionare i dati.

Correggere i dati di origine e reinsezionare i file

Se l'inserimento non riesce a causa di valori non validi o incoerenti nei file di origine, correggere tali valori e reinserire i dati. Ad esempio, sostituire i valori segnaposto, ad N/A esempio con valori vuoti o valori predefiniti validi.

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

Quando si rigestisci i dati corretti, usare un percorso di file esplicito che punta al nuovo file contenente solo dati corretti, anziché un percorso di cartella che fa riferimento ai file originali. Questo approccio impedisce la reinsezione di righe caricate in precedenza correttamente ed evita i dati duplicati.

Rielaborare le righe rifiutate usando una tabella di staging

È possibile caricare le righe rifiutate in una tabella di staging, correggere i dati usando Transact-SQL istruzioni di modifica dei dati e quindi reinsezionare le righe corrette.

L'istruzione seguente CREATE TABLE AS SELECT carica le righe rifiutate in una tabella per un'ulteriore elaborazione:

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

Dopo aver corretto i dati, inserire le righe pulite nella tabella di destinazione.

Contenuti correlati

• Inserire dati con COPY INTO
• Inserire dati con Transact-SQL