Resolver erros de esquema

Este documento ajuda a diagnosticar e resolver erros de HTTP 400 Bad Request que ocorrem quando você modifica esquemas de tabela do BigQuery ou quando define ou carrega estruturas de esquema profundamente aninhadas. Para saber como usar as visualizações INFORMATION_SCHEMA para resolver problemas de desempenho do BigQuery, consulte Resolver problemas com o esquema de informações.

Resolver erros de incompatibilidade de esquema

O BigQuery retorna um erro Provided Schema does not match Table (reason: invalid) quando uma das seguintes operações fornece um esquema que entra em conflito com o esquema atual da tabela de destino:

  • Um job de carregamento
  • Um job de consulta com uma tabela de destino
  • Um job de cópia
  • Uma instrução DDL ALTER TABLE
  • Uma atualização de metadados de tabela (tables.update ou tables.patch)

Para identificar a causa e a resolução específicas, compare sua mensagem de erro com um dos cenários a seguir.

O campo está ausente no novo esquema

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME is missing in new schema

Diagnóstico

Uma das seguintes operações fornece uma definição de esquema que omite uma ou mais colunas que já existem na tabela de destino:

  • Um job de carregamento
  • Uma operação de gravação do DataFrame
  • Uma chamada de API tables.update ou tables.patch

Resolução

O BigQuery não permite descartar colunas implicitamente omitindo-as de um payload de esquema de API ou de uma configuração de job de carregamento. Para resolver esse erro, faça o seguinte:

  • Atualize o pipeline ou a solicitação de API para que o esquema fornecido inclua todas as colunas existentes na tabela de destino.

  • Se você pretende remover a coluna da tabela, execute uma instrução DDL DROP COLUMN em vez de omitir a coluna de uma solicitação de carregamento ou patch:

    ALTER TABLE `PROJECT_ID.DATASET.TABLE`
DROP COLUMN FIELD_NAME;

    Substitua:

    • PROJECT_ID: o ID do projeto que contém a tabela.
    • DATASET: o nome do conjunto de dados onde está a tabela.
    • TABLE: o nome da tabela.
    • FIELD_NAME: o nome da coluna a ser descartada.

O tipo do campo mudou

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME has changed type from EXISTING_TYPE to NEW_TYPE

Diagnóstico

Sua solicitação tenta alterar o tipo de dados de uma coluna atual (por exemplo, de STRING para INT64) durante um job de carregamento ou uma atualização de esquema.

Resolução

Para resolver esse erro, siga um destes procedimentos:

  • Se a alteração de tipo for uma conversão de tipo de dados de coluna compatível (como INT64 para NUMERIC, INT64 para FLOAT64 ou NUMERIC para BIGNUMERIC), use uma instrução DDL ALTER TABLE ... ALTER COLUMN:

    ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DATA TYPE NEW_TYPE;

    Substitua:

    • PROJECT_ID: o ID do projeto que contém a tabela.
    • DATASET: o nome do conjunto de dados onde está a tabela.
    • TABLE: o nome da tabela.
    • FIELD_NAME: o nome da coluna a ser modificada.
    • NEW_TYPE: o tipo de dados de destino.

  • Se a conversão de tipo não for compatível, consulte os dados atuais com uma expressão CAST explícita e grave os resultados em uma nova tabela ou substitua a tabela atual. Para mais informações, consulte Alterar o tipo de dados de uma coluna.

O campo mudou de modo

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME has changed mode from EXISTING_MODE to NEW_MODE

Diagnóstico

Sua solicitação tenta uma alteração de modo de coluna não compatível (por exemplo, alterar NULLABLE para REQUIRED ou REPEATED para NULLABLE). Esse erro também ocorre se você usa uma definição de esquema inline na ferramenta de linha de comando bq ao flexibilizar uma coluna REQUIRED em uma tabela que contém colunas REPEATED, porque as definições de esquema inline na ferramenta de linha de comando bq não são compatíveis com a modificação de tabelas com colunas REPEATED.

Resolução

A única mudança de modo compatível com as atualizações de esquema do BigQuery no local é relaxar o modo de uma coluna de REQUIRED para NULLABLE. Para resolver esse erro, siga um destes procedimentos:

  • Se você estiver relaxando uma coluna de REQUIRED para NULLABLE, execute uma instrução DDL ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL no GoogleSQL ou forneça um arquivo de esquema JSON se estiver atualizando o esquema com o comando bq update ou o método de API tables.patch. Para instruções, consulte Alterar o modo de uma coluna.
  • Para alterar o modo de uma coluna de NULLABLE para REQUIRED ou de REPEATED para NULLABLE, crie uma tabela com o esquema atualizado e copie ou carregue os dados na nova tabela. Para instruções, consulte Como modificar esquemas de tabelas.

Não é possível adicionar campos

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Cannot add fields (field: FIELD_NAME)

Diagnóstico

Um job de carregamento ou de consulta inclui uma coluna (FIELD_NAME) que não existe na tabela de destino, e a adição de esquema não está ativada na configuração do job.

Resolução

Ative a adição de esquema no job de carregamento ou de consulta:

  • Na API REST ou nas bibliotecas de cliente, inclua ALLOW_FIELD_ADDITION na propriedade schemaUpdateOptions da configuração do job de carregamento ou consulta.
  • Na ferramenta de linha de comando bq, transmita a flag --schema_update_option=ALLOW_FIELD_ADDITION.
  • Como alternativa, adicione a coluna antes de executar o job usando ALTER TABLE ... ADD COLUMN.

Não é possível adicionar campos obrigatórios a um esquema atual

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Cannot add required fields to an existing schema. (field: FIELD_NAME)

Diagnóstico

Sua solicitação anexa uma nova coluna a uma tabela atual com o modo de coluna definido como REQUIRED (NOT NULL). Como as linhas atuais na tabela receberiam valores NULL para a coluna recém-adicionada, o BigQuery rejeita a adição de colunas REQUIRED diretamente por meio de atualizações de esquema.

Resolução

Para resolver esse erro, siga um destes procedimentos:

  • Adicione a nova coluna com o modo NULLABLE em vez de REQUIRED e execute uma instrução DML UPDATE para preencher os valores das linhas atuais.

  • Para adicionar a coluna e atribuir um valor padrão, siga estas etapas:

    1. Adicione a coluna como uma coluna NULLABLE.
    2. Defina o valor padrão da coluna para que as inserções futuras recebam um valor automaticamente.

    3. Atualize as linhas atuais para preencher o valor padrão:

      ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ADD COLUMN FIELD_NAME DATA_TYPE;

ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DEFAULT DEFAULT_EXPRESSION;

UPDATE `PROJECT_ID.DATASET.TABLE`
SET FIELD_NAME = DEFAULT_EXPRESSION
WHERE TRUE;

      Substitua:

      • PROJECT_ID: o ID do projeto que contém a tabela.
      • DATASET: o nome do conjunto de dados onde está a tabela.
      • TABLE: o nome da tabela.
      • FIELD_NAME: o nome da nova coluna.
      • DATA_TYPE: o tipo de dados da coluna.
      • DEFAULT_EXPRESSION: a expressão de valor padrão para a coluna.

O campo já existe no esquema

Mensagem de erro

Field FIELD_NAME already exists in schema

Diagnóstico

O esquema fornecido na solicitação de API, no arquivo de esquema JSON ou no cabeçalho do arquivo de origem (como CSV ou Parquet) contém nomes de coluna duplicados. Os nomes de colunas no BigQuery não diferenciam maiúsculas de minúsculas. Portanto, especificar Field_A e field_a no mesmo esquema aciona esse erro.

Resolução

Inspecione o esquema de origem e remova ou renomeie os nomes de colunas duplicados, incluindo as colisões de nomes que não diferenciam maiúsculas de minúsculas.

Não é possível atualizar a ordenação de campos

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Invalid schema update. Updating field collation is not supported. Field FIELD_NAME has changed collation from "EXISTING_COLLATION" to "NEW_COLLATION"

Diagnóstico

Não é possível modificar ou remover a especificação de ordenação de uma coluna atual durante uma atualização de esquema. Esse erro geralmente ocorre quando um aplicativo busca o esquema de uma tabela, modifica outro campo e grava o esquema de volta usando uma versão mais antiga da biblioteca de cliente que não preserva a propriedade do campo collation (enviando "" em vez de "und:ci").

Resolução

Para resolver esse erro, faça o seguinte:

  • Faça upgrade da biblioteca de cliente do BigQuery para a versão mais recente para que os atributos collation atuais sejam preservados ao ler e corrigir metadados de tabela.
  • Se você criar payloads de esquema JSON manualmente, verifique se a propriedade collation nas colunas atuais corresponde ao esquema atual da tabela.

A restrição de parâmetros de tipo não é compatível

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Narrowing type parameters is not compatible

Diagnóstico

A instrução ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE tenta reduzir o comprimento, a precisão ou a escala máxima de um tipo de dados parametrizado, por exemplo, alterando STRING(100) para STRING(50) ou reduzindo os parâmetros NUMERIC(P, S).

Resolução

O BigQuery só permite ampliar os parâmetros de tipo em uma coluna existente (como aumentar STRING(50) para STRING(100)). Para aplicar parâmetros de tipo mais restritos, crie uma nova tabela definida com o parâmetro de tipo mais restrito e copie ou carregue os dados na nova tabela usando uma consulta SELECT que faça com que os valores se ajustem ao tipo mais restrito (por exemplo, LEFT(FIELD_NAME, 50) para uma coluna STRING). Gravar um valor STRING ou BYTES maior que o comprimento máximo da coluna ou um valor NUMERIC com mais dígitos do que a precisão permite retorna um erro. Os valores NUMERIC com mais dígitos decimais do que a escala da coluna são arredondados. Para mais informações, consulte Como modificar esquemas de tabelas.

Não é possível adicionar um campo com um valor padrão

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Add field with default value to an existing table schema is not supported; Field: FIELD_NAME. ...

Diagnóstico

O job de carregamento ou a solicitação de API tables.patch ou tables.update tenta adicionar uma nova coluna a uma tabela atual ao especificar defaultValueExpression no payload do esquema de API. Não é possível adicionar uma nova coluna com um valor padrão a uma tabela em uma única atualização de esquema de API.

Resolução

Em vez de especificar defaultValueExpression ao adicionar a coluna na solicitação de API, adicione a coluna primeiro e defina o valor padrão dela. Para mais informações, consulte Limitações:

  • Se você ainda não adicionou a coluna, execute as seguintes instruções e retome os carregamentos sem defaultValueExpression:

    ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ADD COLUMN FIELD_NAME DATA_TYPE;

ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DEFAULT DEFAULT_EXPRESSION;

UPDATE `PROJECT_ID.DATASET.TABLE`
SET FIELD_NAME = DEFAULT_EXPRESSION
WHERE TRUE;

    Substitua:

    • PROJECT_ID: o ID do projeto que contém a tabela.
    • DATASET: o nome do conjunto de dados onde está a tabela.
    • TABLE: o nome da tabela.
    • FIELD_NAME: o nome da nova coluna.
    • DATA_TYPE: o tipo de dados da coluna.
    • DEFAULT_EXPRESSION: a expressão de valor padrão para a coluna.

  • Se você já adicionou a coluna reenviando a solicitação sem defaultValueExpression, execute apenas as instruções ALTER COLUMN ... SET DEFAULT e UPDATE do exemplo anterior.

Não é possível atualizar os atributos de uma coluna de identidade atual.

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Updating an existing identity column's attributes is not supported. Field FIELD_NAME already has identity column info.

Diagnóstico

Sua solicitação tenta modificar os atributos de uma coluna de identidade atual por meio de uma atualização de esquema ou patch de API.

Resolução

As propriedades da coluna de identidade não podem ser modificadas no local após a criação. Mantenha a definição da coluna de identidade atual inalterada no payload de atualização do esquema ou crie uma nova tabela com a configuração atualizada da coluna de identidade e copie ou carregue os dados na nova tabela. Para instruções, consulte Como modificar esquemas de tabelas.

Mudança de campo incompatível na coluna RECORD

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Incompatible field change within RECORD column

Diagnóstico

A instrução ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE especifica um tipo STRUCT que não é compatível com o tipo atual da coluna. Por exemplo, o novo tipo adiciona ou remove subcampos, altera um subcampo para um tipo não compatível ou restringe os parâmetros de tipo de um subcampo.

Resolução

Compare o tipo STRUCT na instrução ALTER TABLE com o esquema atual da coluna e siga um destes procedimentos:

O limite máximo de esquemas físicos divergentes foi atingido

Mensagem de erro

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Maximum limit on diverging physical schemas reached.

Diagnóstico

Algumas modificações de esquema de tabela, como descarte ou renomeação de colunas, podem fazer com que as tabelas tenham esquemas lógicos idênticos na API, mas representações de esquema físico subjacentes diferentes. Quando você copia dados para uma tabela de destino em muitas versões de esquema físico divergentes, o job de cópia de tabela pode falhar com esse erro. Para mais informações, consulte Limitações na cópia de tabelas.

Resolução

Use a instrução DDL CREATE TABLE LIKE para recriar a tabela de destino de modo que a representação do esquema físico corresponda ao esquema da tabela de origem e, em seguida, copie ou insira os dados:

CREATE OR REPLACE TABLE `PROJECT_ID.DATASET.DESTINATION_TABLE`
LIKE `PROJECT_ID.DATASET.SOURCE_TABLE`;

INSERT INTO `PROJECT_ID.DATASET.DESTINATION_TABLE`
SELECT * FROM `PROJECT_ID.DATASET.SOURCE_TABLE`;

Substitua:

  • PROJECT_ID: o ID do projeto que contém as tabelas.
  • DATASET: o nome do conjunto de dados que contém as tabelas.
  • DESTINATION_TABLE: o nome da tabela de destino a ser recriada.
  • SOURCE_TABLE: o nome da tabela de origem.

Resolver problemas de limite de aninhamento de esquemas, arquivos e rotinas

O BigQuery aplica uma profundidade máxima de aninhamento de 15 níveis para colunas STRUCT (RECORD), arquivos de carregamento estruturados e tipos de retorno e argumento de rotina aninhados. Exceder esse limite de 15 níveis retorna um erro HTTP 400 Bad Request com uma das seguintes mensagens (em que MAX_DEPTH e MAX_LEVEL são 15). Para erros causados por exceder o limite de aninhamento de 16 níveis em visualizações lógicas ou funções definidas pelo usuário (UDFs) persistentes, consulte Muitas visualizações aninhadas ou funções definidas pelo usuário persistentes.

Mensagens de erro

  • Schema nested too deeply.
  • Schema nested too deeply for field FIELD_NAME, maximum allowed depth is MAX_DEPTH.
  • Field 'FIELD_NAME' is nested too deeply. It has definition level LEVEL while maximum allowed definition level is MAX_LEVEL.
  • Schema nested too deeply, at least LEVEL compared to the maximum allowed level of 15.
  • Routine TYPE_DESCRIPTION nested too deeply; max allowed depth: MAX_DEPTH

Diagnóstico

As colunas do tipo STRUCT (RECORD) podem conter campos RECORD aninhados até uma profundidade máxima de 15 níveis, independentemente de os campos serem escalares (NULLABLE ou REQUIRED) ou baseados em matriz (REPEATED). Da mesma forma, os jobs de carregamento e as tabelas externas JSON, Parquet e Avro aplicam o limite de aninhamento ao analisar estruturas aninhadas. Para arquivos Parquet, o limite é expresso como um nível máximo de definição e um nível máximo de repetição.

Em erros de rotina, TYPE_DESCRIPTION identifica o tipo aninhado: argument type, return type ou return table type.

Resolução

Não é possível aumentar esse limite. Para resolver esse erro, siga um destes procedimentos:

  • Mescle o esquema ou a definição do tipo de rotina. Divida hierarquias STRUCT ou ARRAY profundamente aninhadas no esquema da tabela ou no argumento de rotina e nos tipos de retorno em tabelas relacionadas separadas e mescle-as no momento da consulta ou promova atributos profundamente aninhados para colunas de nível superior.
  • Use o tipo de dados integrado JSON. Se os dados exigirem mais de 15 níveis de aninhamento, armazene as subárvores profundamente aninhadas em uma coluna do tipo JSON (ou STRING). Uma coluna JSON é armazenada como uma única coluna no esquema da tabela, evitando o limite de esquema RECORD de 15 níveis, mas ainda permitindo que você extraia caminhos profundamente aninhados no momento da consulta usando operadores e funções de acesso de campo JSON.
  • Pré-processe os arquivos de entrada antes de carregar. Se o erro ocorrer durante um job de carregamento de arquivos JSON, Parquet ou Avro no Cloud Storage, adicione uma etapa de pré-processamento upstream para nivelar ou serializar estruturas com mais de 15 níveis antes de carregá-las no BigQuery.