Solucionar problemas de erros de ingestão de Transact-SQL com ficheiros de erro

Aplica-se para:✅ Armazém no Microsoft Fabric

Este artigo descreve como resolver falhas de ingestão em padrões de ingestão T-SQL.

A ingestão num armazém usando COPY INTO, BULK INSERT, OPENROWSET funções em CTAS, INSERT, UPDATE, e MERGE declarações pode falhar por várias razões. Os valores dos ficheiros fonte podem não corresponder ao esquema da tabela. Os valores necessários podem estar em falta. As opções de ingestão também podem estar mal configuradas.

Este guia de resolução de problemas utiliza a informação de diagnóstico das linhas rejeitadas para resolver falhas, capturar erros ao nível das linhas e inspecionar linhas rejeitadas com metadados de erro.

Ao examinar os ficheiros de erro gerados por COPY INTO e outros comandos de ingestão, pode identificar exatamente quais as linhas que falharam e porquê. Esta informação ajuda-o a identificar problemas de qualidade dos dados ou a ajustar as definições de ingestão, corrigir os dados de origem e reexecutar a carga com confiança.

Criar a tabela de destino

Antes de executar comandos de ingestão, crie uma tabela de destino com tipos e NOT NULL restrições rigorosas para detetar cedo problemas de conversão e qualidade dos dados.

1. No seu espaço de trabalho do armazém, abra o seu armazém.

2. No separador Início, selecione Nova consulta SQL.

   

3. Execute a seguinte declaração:

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

Pode usar múltiplos métodos suportados, incluindo ingerir com COPY INTO ou ingerir com Transact-SQL. Escolha o método de ingestão que melhor se adequa à sua fonte de dados, formato e requisitos de automação. O exemplo seguinte de COPY INTO ilustra um padrão comum de ingestão para carregar dados de ficheiros externos numa tabela.

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

Esta instrução pode falhar em ingerir dados se os ficheiros de origem não corresponderem ao esquema da tabela de destino. As causas comuns incluem contagens de colunas incompatíveis, tipos de dados incompatíveis ou valores que não podem ser armazenados na tabela alvo. Se a ingestão encontrar valores que não podem ser convertidos para o esquema de destino, a instrução devolve um erro semelhante ao seguinte:

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 erro indica que uma ou mais linhas não podem ser convertidas para os tipos de coluna de destino.

Investigar erros com MAXERRORS e ERRORFILE

Use as seguintes opções para continuar a ingestão quando o número de erros ao nível da linha estiver abaixo de um limiar definido e para armazenar detalhes de diagnóstico numa localização especificada.

• MAXERRORS define o número máximo de falhas toleradas a nível de linha durante o processo de ingestão.
• ERRORFILE especifica onde a base de dados escreve linhas rejeitadas e detalhes de erro.

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

A operação de carregamento só tem sucesso quando o número de linhas rejeitadas é inferior a MAXERRORS. Quando são capturados erros, a operação de ingestão escreve:

• error.jsonl para diagnósticos estruturados
• row.csv para linhas de fonte rejeitadas

Localizar e consultar linhas rejeitadas

A base de dados escreve informações de erro numa hierarquia estruturada de pastas sob a localização de erro configurada. Estas pastas ajudam-no a rastrear uma execução específica e a correlacionar diagnósticos com uma única instrução de ingestão:

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

Use OPENROWSET para ler os diagnósticos estruturados em error.jsonl para identificar qual valor falhou, qual a coluna de destino afetada e onde a linha com falha se originou:

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

O conjunto de resultados inclui tipicamente uma linha por cada registo rejeitado, por exemplo:

O error.jsonl ficheiro contém um objeto JSON por linha. Cada objeto inclui as propriedades listadas na tabela anterior. A tabela seguinte descreve cada propriedade em detalhe.

Ver linhas rejeitadas

Depois de analisar as informações de diagnóstico estruturadas, pode inspecionar os dados originais que a base de dados não conseguiu processar. A saída das linhas rejeitadas contém cópias dos registos de origem, preservados exatamente como apareciam nos ficheiros de entrada. Os diagnósticos das linhas rejeitadas geram ficheiros que contêm apenas os registos que falharam a ingestão:

• Se ingerir ficheiros CSV usando COPY INTO (FILE_TYPE = 'CSV'), a saída rejeitada inclui um row.csv ficheiro. Este ficheiro corresponde à estrutura do ficheiro de origem e contém as linhas CSV originais com valores inválidos.
• Se ingerir ficheiros JSONL usando OPENROWSET(FORMAT = 'JSONL'), a saída rejeitada inclui um row.jsonl ficheiro. Este ficheiro preserva os objetos JSON originais que causaram falhas de ingestão.

Use estes ficheiros para validar a causa raiz dos erros, como valores malformados, valores inesperados NULL ou linhas de cabeçalho que foram incorretamente analisadas como dados.

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

O row.csv esquema corresponde à forma CSV de origem e contém apenas linhas que falharam a ingestão.

Exemplo de saída para linha rejeitada:

Com base nesta informação de diagnóstico, pode identificar os seguintes problemas de ingestão:

• A linha de cabeçalho no ficheiro de origem é erroneamente analisada como uma linha de dados. Para resolver, a COPY INTO instrução deve usar a FIRSTROW = 2 opção.
• Uma linha no ficheiro fonte da vendorID coluna (C1) contém NULL valores, mas a coluna correspondente na tabela de destino TaxiTrips é definida como NOT NULL.
• Uma linha no ficheiro fonte da passengerCount coluna contém um valor inválido (N/A) que não pode ser convertido para a coluna int de destino.

Corrigir problemas de ingestão e reingestar dados

Depois de identificar a causa das falhas de ingestão, corrija o problema e volte a ingerir os dados afetados. A abordagem de remediação depende de onde o erro se origina.

Corrigir o esquema da tabela de destino

Se os dados de origem não corresponderem ao esquema da tabela de destino, atualize a definição da tabela. Soluções comuns incluem alterar os tipos de dados das colunas ou remover restrições como NOT NULL.

Em alguns cenários, pode ser necessário remover e recriar a tabela de destino antes de voltar a ingerir os dados.

Corrija os dados de origem e reingira os ficheiros

Se a ingestão falhar devido a valores inválidos ou inconsistentes nos ficheiros de origem, corrige esses valores e volta a ingerir os dados. Por exemplo, substitua valores provisórios, como N/A, por valores vazios ou padrões válidos.

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

Ao voltar a ingerir dados corrigidos, use um caminho de ficheiro explícito que aponte para o novo ficheiro contendo apenas os dados corrigidos, em vez de um caminho de pasta que referencia os ficheiros originais. Esta abordagem impede a re-ingesta de linhas que já foram carregadas com sucesso e evita dados duplicados.

Reprocessar linhas rejeitadas usando uma tabela de preparação

Podes carregar linhas rejeitadas numa tabela de staging, corrigir os dados usando Transact-SQL instruções de modificação de dados e depois voltar a ingerir as linhas corrigidas.

A seguinte CREATE TABLE AS SELECT instrução carrega as linhas rejeitadas numa tabela para processamento adicional:

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

Depois de corrigires os dados, insere as linhas limpas na tabela de destino.

Conteúdo relacionado

• Ingerir dados usando COPY INTO
• Ingerir dados usando Transact-SQL