Résoudre les erreurs d’ingestion de Transact-SQL à l'aide de fichiers d’erreur

S’applique à :✅Entrepôt dans Microsoft Fabric

Cet article explique comment résoudre les échecs d’ingestion dans les modèles d’ingestion T-SQL.

L'ingestion dans un entrepôt à l'aide des fonctions COPY INTO, BULK INSERT, OPENROWSET dans les instructions CTAS, INSERT, UPDATE, et MERGE peut échouer pour plusieurs raisons. Les valeurs de fichier source peuvent ne pas correspondre au schéma de table. Les valeurs requises peuvent être manquantes. Les options d’ingestion peuvent également être mal configurées.

Ce guide de résolution des problèmes utilise les informations de diagnostic de lignes rejetées pour résoudre les échecs, capturer les erreurs au niveau des lignes et inspecter les lignes rejetées avec des métadonnées d’erreur.

En examinant les fichiers d’erreur générés par COPY INTO et d’autres commandes d’ingestion, vous pouvez identifier exactement les lignes qui n’ont pas pu être ingérées et pourquoi. Ces informations vous aident à identifier les problèmes de qualité des données ou à ajuster les paramètres d’ingestion, à corriger les données sources et à réexécuter la charge en toute confiance.

Créer la table cible

Avant d’exécuter des commandes d’ingestion, créez une table de destination avec des types stricts et NOT NULL des contraintes strictes afin d’intercepter les problèmes de conversion et de qualité des données dès le départ.

1. Dans votre espace de travail Warehouse, ouvrez votre entrepôt.

2. Sous l’onglet Accueil , sélectionnez Nouvelle requête SQL.

   

3. Exécutez l’instruction suivante :

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

Vous pouvez utiliser plusieurs méthodes prises en charge, notamment l’ingestion avec COPY INTO ou l’ingestion avec Transact-SQL. Choisissez la méthode d’ingestion qui correspond le mieux à vos exigences en matière de source de données, de format et d’automatisation. L’exemple COPY INTO suivant illustre un modèle d’ingestion courant pour le chargement de données à partir de fichiers externes dans une table.

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

Cette instruction peut ne pas ingérer des données si les fichiers sources ne correspondent pas au schéma de la table de destination. Les causes courantes incluent les nombres de colonnes incompatibles, les types de données incompatibles ou les valeurs qui ne peuvent pas être stockés dans la table cible. Si l’ingestion rencontre des valeurs qui ne peuvent pas être converties en schéma de destination, l’instruction retourne une erreur similaire à ce qui suit :

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

Cette erreur indique qu’une ou plusieurs lignes ne peuvent pas être converties en types de colonnes de destination.

Examiner les erreurs avec MAXERRORS et ERRORFILE

Utilisez les options suivantes pour continuer l’ingestion lorsque le nombre d’erreurs au niveau des lignes est inférieur à un seuil défini et pour stocker les détails de diagnostic dans un emplacement spécifié.

• MAXERRORS définit le nombre maximal d’échecs au niveau des lignes tolérés pendant l’ingestion.
• ERRORFILE spécifie où la base de données écrit des lignes rejetées et des détails d’erreur.

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’opération de chargement réussit uniquement lorsque le nombre de lignes rejetées est inférieur à MAXERRORS. Lorsque des erreurs sont capturées, l’opération d’ingestion écrit :

• error.jsonl pour les diagnostics structurés
• row.csv pour les lignes sources rejetées

Localiser et interroger les lignes rejetées

La base de données écrit les informations d’erreur dans une hiérarchie de dossiers structurées sous l’emplacement d’erreur configuré. Ces dossiers vous aident à suivre une exécution spécifique et à mettre en corrélation les diagnostics à une instruction d’ingestion :

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

Utilisez OPENROWSET pour lire les diagnostics structurés dans error.jsonl afin de pouvoir identifier quelle valeur a échoué, quelle colonne de destination a été affectée, ainsi que l'origine de la ligne défaillante.

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

Le jeu de résultats inclut généralement une ligne par enregistrement rejeté, par exemple :

Le error.jsonl fichier contient un objet JSON par ligne. Chaque objet inclut les propriétés répertoriées dans le tableau précédent. Le tableau suivant décrit chaque propriété en détail.

Examiner les lignes rejetées

Après avoir examiné les informations de diagnostic structurées, vous pouvez inspecter les données sources d’origine que la base de données n’a pas pu ingérer. La sortie des lignes rejetées contient des copies des enregistrements sources, conservées exactement comme elles apparaissent dans les fichiers d’entrée. Les diagnostics des lignes rejetées génèrent des fichiers qui contiennent uniquement les enregistrements ayant échoué lors du processus d'ingestion.

• Si vous ingérez des fichiers CSV à l’aide de COPY INTO (FILE_TYPE = 'CSV'), la sortie rejetée inclut un fichier row.csv. Ce fichier correspond à la structure de fichiers source et contient les lignes CSV d’origine avec des valeurs non valides.
• Si vous ingérez des fichiers JSONL à l’aide de OPENROWSET(FORMAT = 'JSONL'), la sortie rejetée inclut un fichier row.jsonl. Ce fichier conserve les objets JSON d’origine qui ont provoqué des échecs d’ingestion.

Utilisez ces fichiers pour valider la cause principale des erreurs, telles que des valeurs incorrectes, des valeurs inattendues NULL ou des lignes d’en-tête qui ont été incorrectement analysées en tant que données.

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

Le row.csv schéma correspond à la forme CSV source et contient uniquement des lignes ayant échoué dans l’ingestion.

Exemple de sortie de ligne rejetée :

En fonction de ces informations de diagnostic, vous pouvez identifier les problèmes d’ingestion suivants :

• La ligne d’en-tête du fichier source est analysée par erreur sous la forme d’une ligne de données. Pour résoudre ce problème, l’instruction COPY INTO doit utiliser l’option FIRSTROW = 2 .
• Une ligne du fichier source de la vendorID colonne (C1) contient des NULL valeurs, mais la colonne correspondante de la table de destination TaxiTrips est définie comme NOT NULL.
• Une ligne du fichier source de la passengerCount colonne contient une valeur non valide (N/A) qui ne peut pas être convertie en colonne int de destination.

Résoudre les problèmes d’ingestion et réingérer les données

Après avoir identifié la cause des échecs d’ingestion, corrigez le problème et ré-ingérez les données affectées. L’approche de correction dépend de l’origine de l’erreur.

Corriger le schéma de la table de destination

Si les données sources ne sont pas conformes au schéma de la table de destination, mettez à jour la définition de la table. Les correctifs courants incluent la modification des types de données de colonne ou la suppression de contraintes restrictives telles que NOT NULL.

Dans certains scénarios, vous devrez peut-être supprimer et recréer la table de destination avant de ingérer à nouveau les données.

Corriger les données sources et réingestionner des fichiers

Si l’ingestion échoue en raison de valeurs non valides ou incohérentes dans les fichiers sources, corrigez ces valeurs et réingestionner les données. Par exemple, remplacez les valeurs d’espace réservé telles que N/A par des valeurs vides ou par des valeurs par défaut valides.

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

Lors de la réingestion de données corrigées, utilisez un chemin d’accès de fichier explicite qui pointe vers le nouveau fichier contenant uniquement des données corrigées, plutôt qu’un chemin d’accès de dossier qui référence les fichiers d’origine. Cette approche empêche la réingestion de lignes qui ont été chargées précédemment et évite les données dupliquées.

Retraiter les lignes rejetées à l’aide d’une table intermédiaire

Vous pouvez charger des lignes rejetées dans une table intermédiaire, corriger les données à l’aide de Transact-SQL instructions de modification de données, puis ingérer à nouveau les lignes corrigées.

L’instruction suivante CREATE TABLE AS SELECT charge les lignes rejetées dans une table pour un traitement supplémentaire :

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

Après avoir corrigé les données, insérez les lignes nettoyées dans la table de destination.

Contenu connexe

• Ingérer des données à l’aide de COPY INTO
• Ingérer des données à l’aide de Transact-SQL