Resolver errores de ingesta de Transact-SQL con archivos de error

Esto se aplica a:✅ Almacén en Microsoft Fabric

En este artículo se describe cómo solucionar errores de ingesta en patrones de ingesta de T-SQL.

La ingesta en un almacén mediante COPY INTO, BULK INSERT, la OPENROWSET función de CTAS, INSERTUPDATE, y MERGE las instrucciones pueden producir errores por varias razones. Es posible que los valores del archivo de origen no coincidan con el esquema de la tabla. Es posible que falten valores necesarios. Las opciones de ingesta también podrían estar mal configuradas.

En esta guía de solución de problemas se usa la información de diagnóstico de fila rechazada para resolver errores, capturar errores de nivel de fila e inspeccionar filas rechazadas con metadatos de error.

Al examinar los archivos de error generados por COPY INTO y otros comandos de ingesta, puede identificar exactamente qué filas no se pudieron ingerir y por qué. Esta información le ayuda a identificar problemas de calidad de datos o ajustar la configuración de ingesta, corregir los datos de origen y volver a ejecutar la carga con confianza.

Creación de la tabla de destino

Antes de ejecutar comandos de ingesta, cree una tabla de destino con restricciones y NOT NULL tipos estrictos para detectar problemas de conversión y calidad de datos al principio.

1. En el área de trabajo de tu almacén, abre tu almacén.

2. En la pestaña Inicio , seleccione Nueva consulta SQL.

   

3. Ejecute la siguiente instrucción:

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

Puede usar varios métodos admitidos, incluida la ingesta con COPY INTO o la ingesta con Transact-SQL. Elija el método de ingesta que mejor se adapte a los requisitos de origen de datos, formato y automatización. En el siguiente ejemplo COPY INTO se muestra un patrón de ingesta común para cargar datos de archivos externos en una tabla.

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

Esta instrucción puede no ingerir datos si los archivos de origen no coinciden con el esquema de la tabla de destino. Entre las causas comunes se incluyen recuentos de columnas no coincidedas, tipos de datos incompatibles o valores que no se pueden almacenar en la tabla de destino. Si la ingesta encuentra valores que no se pueden convertir en el esquema de destino, la instrucción devuelve un error similar al siguiente:

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

Este error indica que una o varias filas no se pueden convertir a los tipos de columna de destino.

Investigación de errores con MAXERRORS y ERRORFILE

Use las siguientes opciones para continuar la ingesta cuando el número de errores de nivel de fila está por debajo de un umbral definido y para almacenar los detalles de diagnóstico en una ubicación especificada.

• MAXERRORS establece el número máximo de fallos a nivel de fila que se toleran durante la ingestión.
• ERRORFILE especifica dónde la base de datos escribe filas rechazadas y detalles de error.

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

La operación de carga solo se realiza correctamente cuando el número de filas rechazadas es inferior a MAXERRORS. Cuando se capturan errores, la operación de incorporación escribe:

• error.jsonl para diagnósticos estructurados
• row.csv para las filas de origen rechazadas

Buscar y consultar filas rechazadas

La base de datos escribe la información de error en una jerarquía estructurada de carpetas en la ubicación de error configurada. Estas carpetas le ayudan a realizar un seguimiento de una ejecución específica y correlacionar los diagnósticos con una instrucción de ingesta:

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

Use OPENROWSET para leer los diagnósticos estructurados en error.jsonl para que pueda identificar qué valor produjo un error, qué columna de destino se vio afectada y dónde se originó la fila con errores:

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

El conjunto de resultados suele incluir una fila por registro rechazado, por ejemplo:

El error.jsonl archivo contiene un objeto JSON por línea. Cada objeto incluye las propiedades enumeradas en la tabla anterior. En la tabla siguiente se describe cada propiedad con detalle.

Revisar filas rechazadas

Después de revisar la información de diagnóstico estructurada, puede inspeccionar los datos de origen originales que la base de datos no pudo ingerir. La salida de filas rechazadas contiene copias de los registros de origen, preservadas exactamente tal como aparecieron en los archivos de entrada. Los diagnósticos de filas rechazadas generan archivos que contienen solo los registros que no se pudieron ingerir:

• Si ingiere archivos CSV mediante COPY INTO (FILE_TYPE = 'CSV'), la salida rechazada incluye un row.csv archivo. Este archivo coincide con la estructura del archivo de origen y contiene las filas CSV originales con valores no válidos.
• Si ingiere archivos JSONL mediante OPENROWSET(FORMAT = 'JSONL'), la salida rechazada incluye un row.jsonl archivo. Este archivo conserva los objetos JSON originales que provocaron errores de ingesta.

Use estos archivos para validar la causa principal de los errores, como valores con formato incorrecto, valores inesperados NULL o filas de encabezado que se analizaron incorrectamente como datos.

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

El row.csv esquema coincide con la forma CSV de origen y solo contiene filas que no se pudieron ingerir.

Salida de ejemplo de fila rechazada

En función de esta información de diagnóstico, puede identificar los siguientes problemas de ingesta:

• La fila de encabezado del archivo de origen se analiza erróneamente como una fila de datos. Para resolverlo, la COPY INTO instrucción debe usar la FIRSTROW = 2 opción .
• Una fila del archivo de origen de la vendorID columna (C1) contiene NULL valores, pero la columna correspondiente de la tabla de destino TaxiTrips se define como NOT NULL.
• Una fila del archivo de origen de la passengerCount columna contiene un valor no válido (N/A) que no se puede convertir en la columna int de destino.

Corregir problemas de ingesta y volver a ingerir datos

Después de identificar la causa de errores de ingesta, corrija el problema y vuelva a ingerir los datos afectados. El enfoque de corrección depende de dónde se origine el error.

Corrección del esquema de la tabla de destino

Si los datos de origen no se ajustan al esquema de la tabla de destino, actualice la definición de la tabla. Las correcciones comunes incluyen el cambio de tipos de datos de columna o la eliminación de restricciones restrictivas, como NOT NULL.

En algunos escenarios, es posible que tenga que quitar y volver a crear la tabla de destino antes de volver a ingerir los datos.

Corregir los datos de origen y volver a ingerir archivos

Si se produce un error en la ingesta debido a valores no válidos o incoherentes en los archivos de origen, corrija esos valores y vuelva a ingerir los datos. Por ejemplo, reemplace los valores de marcador de posición, como N/A por valores vacíos o valores predeterminados válidos.

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

Al volver a ingerir datos corregidos, use una ruta de acceso de archivo explícita que apunte al nuevo archivo que solo contiene datos corregidos, en lugar de una ruta de acceso de carpeta que haga referencia a los archivos originales. Este enfoque impide volver a ingerir filas que se cargaron correctamente y evita datos duplicados.

Reproceso de filas rechazadas mediante una tabla de etapa

Puede cargar filas rechazadas en una tabla de almacenamiento provisional, corregir los datos mediante Transact-SQL instrucciones de modificación de datos y, a continuación, volver a ingerir las filas corregidas.

La siguiente CREATE TABLE AS SELECT instrucción carga las filas rechazadas en una tabla para su posterior procesamiento:

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

Después de corregir los datos, inserte las filas limpiadas en la tabla de destino.

Contenido relacionado

• Ingesta de datos mediante COPY INTO
• Ingesta de datos mediante Transact-SQL