Résoudre les erreurs de schéma

Ce document vous aide à diagnostiquer et à résoudre les erreurs HTTP 400 Bad Request qui se produisent lorsque vous modifiez des schémas de table BigQuery, ou lorsque vous définissez ou chargez des structures de schéma profondément imbriquées. Pour savoir comment utiliser les vues INFORMATION_SCHEMA afin de résoudre les problèmes de performances de BigQuery, consultez Résoudre les problèmes avec le schéma d'informations.

Résoudre les erreurs d'incompatibilité de schéma

BigQuery renvoie une erreur Provided Schema does not match Table (reason: invalid) lorsque l'une des opérations suivantes fournit un schéma qui entre en conflit avec le schéma existant de la table cible :

  • Un job de chargement
  • Un job de requête avec une table de destination
  • Un job de copie
  • Une instruction LDD ALTER TABLE
  • Une mise à jour des métadonnées de la table (tables.update ou tables.patch)

Pour identifier la cause et la solution spécifiques, faites correspondre votre message d'erreur à l'un des scénarios suivants.

Champ manquant dans le nouveau schéma

Message d'erreur

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

Diagnostic

L'une des opérations suivantes fournit une définition de schéma qui omet une ou plusieurs colonnes déjà présentes dans la table de destination :

  • Un job de chargement
  • Une opération d'écriture de DataFrame
  • Un appel d'API tables.update ou tables.patch

Solution

BigQuery ne permet pas de supprimer implicitement des colonnes en les omettant d'une charge utile de schéma de l'API ou d'une configuration de job de chargement. Pour résoudre cette erreur, effectuez l'une des opérations suivantes :

  • Mettez à jour votre pipeline ou votre requête API afin que le schéma fourni inclue toutes les colonnes existantes de la table cible.

  • Si vous avez l'intention de supprimer la colonne de la table, exécutez une instruction LDD DROP COLUMN au lieu d'omettre la colonne d'une requête de chargement ou de correction :

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

    Remplacez les éléments suivants :

    • PROJECT_ID : ID du projet contenant la table.
    • DATASET : nom de l'ensemble de données contenant la table.
    • TABLE : nom de la table
    • FIELD_NAME : nom de la colonne à supprimer.

Le type du champ a été modifié

Message d'erreur

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

Diagnostic

Votre requête tente de modifier le type de données d'une colonne existante (par exemple, de STRING à INT64) lors d'un job de chargement ou d'une mise à jour de schéma.

Solution

Pour résoudre cette erreur, effectuez l'une des opérations suivantes :

  • Si le changement de type est une conversion de type de données de colonne compatible (par exemple, de INT64 à NUMERIC, de INT64 à FLOAT64 ou de NUMERIC à BIGNUMERIC), utilisez une instruction LDD ALTER TABLE ... ALTER COLUMN :

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

    Remplacez les éléments suivants :

    • PROJECT_ID : ID du projet contenant la table.
    • DATASET : nom de l'ensemble de données contenant la table.
    • TABLE : nom de la table
    • FIELD_NAME : nom de la colonne à modifier.
    • NEW_TYPE : type de données cible.

  • Si la conversion de type n'est pas possible sur place, interrogez les données existantes avec une expression CAST explicite et écrivez les résultats dans une nouvelle table ou écrasez la table existante. Pour en savoir plus, consultez Modifier le type de données d'une colonne.

Le champ a changé de mode

Message d'erreur

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

Diagnostic

Votre requête tente de modifier un mode de colonne non compatible (par exemple, en remplaçant NULLABLE par REQUIRED ou REPEATED par NULLABLE). Cette erreur se produit également si vous utilisez une définition de schéma intégrée dans l'outil de ligne de commande bq lorsque vous assouplissez une colonne REQUIRED sur une table contenant des colonnes REPEATED, car les définitions de schéma intégrées dans l'outil de ligne de commande bq ne permettent pas de modifier les tables avec des colonnes REPEATED.

Solution

La seule modification de mode acceptée par les mises à jour de schéma BigQuery sur place consiste à assouplir le mode d'une colonne de REQUIRED à NULLABLE. Pour résoudre cette erreur, effectuez l'une des opérations suivantes :

  • Si vous assouplissez une colonne de REQUIRED à NULLABLE, exécutez une instruction LDD ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL dans GoogleSQL ou fournissez un fichier de schéma JSON si vous mettez à jour le schéma avec la commande bq update ou la méthode API tables.patch. Pour obtenir des instructions, consultez Modifier le mode d'une colonne.
  • Pour modifier le mode d'une colonne de NULLABLE à REQUIRED ou de REPEATED à NULLABLE, créez une table avec le schéma mis à jour, puis copiez ou chargez les données dans la nouvelle table. Pour obtenir des instructions, consultez Modifier des schémas de table.

Impossible d'ajouter des champs

Message d'erreur

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

Diagnostic

Un job de chargement ou de requête inclut une colonne (FIELD_NAME) qui n'existe pas dans la table de destination, et l'ajout de schéma n'est pas activé dans la configuration du job.

Solution

Activez l'ajout de schéma sur la tâche de chargement ou de requête :

  • Dans l'API REST ou les bibliothèques clientes, incluez ALLOW_FIELD_ADDITION dans la propriété schemaUpdateOptions de la configuration de votre job de chargement ou de requête.
  • Dans l'outil de ligne de commande bq, transmettez l'option --schema_update_option=ALLOW_FIELD_ADDITION.
  • Vous pouvez également ajouter la colonne avant d'exécuter le job à l'aide de ALTER TABLE ... ADD COLUMN.

Impossible d'ajouter des champs obligatoires à un schéma existant

Message d'erreur

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

Diagnostic

Votre requête ajoute une colonne à une table existante avec le mode de colonne défini sur REQUIRED (NOT NULL). Étant donné que les lignes existantes de la table recevraient des valeurs NULL pour la colonne nouvellement ajoutée, BigQuery refuse l'ajout de colonnes REQUIRED directement via les mises à jour de schéma.

Solution

Pour résoudre cette erreur, effectuez l'une des opérations suivantes :

  • Ajoutez la nouvelle colonne avec le mode NULLABLE au lieu de REQUIRED, puis exécutez une instruction LMD UPDATE pour renseigner les valeurs des lignes existantes.

  • Pour ajouter la colonne et attribuer une valeur par défaut, procédez comme suit :

    1. Ajoutez la colonne en tant que colonne NULLABLE.
    2. Définissez la valeur par défaut de la colonne afin que les futures insertions reçoivent automatiquement une valeur.

    3. Mettez à jour les lignes existantes pour renseigner la valeur par défaut :

      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;

      Remplacez les éléments suivants :

      • PROJECT_ID : ID du projet contenant la table.
      • DATASET : nom de l'ensemble de données contenant la table.
      • TABLE : nom de la table
      • FIELD_NAME : nom de la nouvelle colonne.
      • DATA_TYPE : type de données de la colonne.
      • DEFAULT_EXPRESSION : expression de valeur par défaut pour la colonne.

Le champ existe déjà dans le schéma

Message d'erreur

Field FIELD_NAME already exists in schema

Diagnostic

Le schéma fourni dans la requête API, le fichier de schéma JSON ou l'en-tête du fichier source (tel que CSV ou Parquet) contient des noms de colonnes en double. Les noms de colonnes dans BigQuery ne sont pas sensibles à la casse. Par conséquent, spécifier à la fois Field_A et field_a dans le même schéma déclenche cette erreur.

Solution

Inspectez le schéma source et supprimez ou renommez les noms de colonnes en double, y compris les collisions de noms non sensibles à la casse.

La mise à jour du classement des champs n'est pas possible

Message d'erreur

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"

Diagnostic

Vous ne pouvez pas modifier ni supprimer la spécification de classement d'une colonne existante lors d'une mise à jour de schéma. Cette erreur se produit généralement lorsqu'une application récupère le schéma d'une table, modifie un autre champ et réécrit le schéma à l'aide d'une ancienne version de la bibliothèque cliente qui ne conserve pas la propriété du champ collation (envoi de "" au lieu de "und:ci").

Solution

Pour résoudre cette erreur, procédez comme suit :

  • Mettez à niveau votre bibliothèque cliente BigQuery vers la dernière version afin que les attributs collation existants soient conservés lors de la lecture et de l'application de correctifs aux métadonnées de la table.
  • Si vous créez manuellement des charges utiles de schéma JSON, assurez-vous que la propriété collation des colonnes existantes correspond au schéma actuel de la table.

La restriction des paramètres de type n'est pas compatible

Message d'erreur

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

Diagnostic

Votre instruction ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE tente de réduire la longueur, la précision ou l'échelle maximales d'un type de données paramétré (par exemple, en remplaçant STRING(100) par STRING(50) ou en réduisant les paramètres NUMERIC(P, S)).

Solution

BigQuery n'autorise que l'élargissement des paramètres de type sur une colonne existante (par exemple, l'augmentation de STRING(50) à STRING(100)). Pour appliquer des paramètres de type plus étroits, créez une table définie avec le paramètre de type plus étroit, puis copiez ou chargez les données dans la nouvelle table à l'aide d'une requête SELECT qui adapte les valeurs au type plus étroit (par exemple, LEFT(FIELD_NAME, 50) pour une colonne STRING). L'écriture d'une valeur STRING ou BYTES plus longue que la longueur maximale de la colonne, ou d'une valeur NUMERIC comportant plus de chiffres que sa précision ne le permet, renvoie une erreur. Les valeurs NUMERIC comportant plus de chiffres décimaux que l'échelle de la colonne sont arrondies. Pour en savoir plus, consultez Modifier des schémas de table.

L'ajout d'un champ avec une valeur par défaut n'est pas possible

Message d'erreur

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

Diagnostic

Votre job de chargement ou votre requête API tables.patch ou tables.update tente d'ajouter une colonne à une table existante tout en spécifiant defaultValueExpression dans la charge utile du schéma de l'API. Vous ne pouvez pas ajouter une colonne avec une valeur par défaut à une table existante en une seule mise à jour du schéma de l'API.

Solution

Au lieu de spécifier defaultValueExpression lorsque vous ajoutez la colonne dans la requête API, ajoutez d'abord la colonne, puis définissez sa valeur par défaut (pour en savoir plus, consultez Limites) :

  • Si vous n'avez pas encore ajouté la colonne, exécutez les instructions suivantes, puis reprenez vos chargements sans 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;

    Remplacez les éléments suivants :

    • PROJECT_ID : ID du projet contenant la table.
    • DATASET : nom de l'ensemble de données contenant la table.
    • TABLE : nom de la table
    • FIELD_NAME : nom de la nouvelle colonne.
    • DATA_TYPE : type de données de la colonne.
    • DEFAULT_EXPRESSION : expression de valeur par défaut pour la colonne.

  • Si vous avez déjà ajouté la colonne en renvoyant la requête sans defaultValueExpression, n'exécutez que les instructions ALTER COLUMN ... SET DEFAULT et UPDATE de l'exemple précédent.

La mise à jour des attributs d'une colonne d'identité existante n'est pas possible.

Message d'erreur

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.

Diagnostic

Votre requête tente de modifier les attributs d'une colonne d'identité existante via une mise à jour de schéma ou un correctif d'API.

Solution

Les propriétés des colonnes d'identité ne peuvent pas être modifiées sur place après leur création. Conservez la définition de la colonne d'identité existante inchangée dans votre charge utile de mise à jour du schéma, ou créez une table avec la configuration de la colonne d'identité mise à jour, puis copiez ou chargez les données dans la nouvelle table. Pour obtenir des instructions, consultez Modifier des schémas de table.

Modification de champ incompatible dans la colonne RECORD

Message d'erreur

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

Diagnostic

Votre instruction ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE spécifie un type STRUCT qui n'est pas compatible avec le type existant de la colonne. Par exemple, le nouveau type ajoute ou supprime des sous-champs, remplace un sous-champ par un type non compatible ou restreint les paramètres de type d'un sous-champ.

Solution

Comparez le type STRUCT de votre instruction ALTER TABLE avec le schéma existant de la colonne, puis effectuez l'une des opérations suivantes :

Limite maximale de schémas physiques divergents atteinte

Message d'erreur

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

Diagnostic

Certaines modifications de schéma de table, telles que la suppression ou le changement de nom de colonnes, peuvent entraîner des schémas logiques identiques dans l'API, tout en ayant des représentations de schéma physique sous-jacentes différentes. Lorsque vous copiez des données dans une table de destination sur un trop grand nombre de versions de schéma physique divergentes, la tâche de copie de table peut échouer avec cette erreur. Pour en savoir plus, consultez Limites de la copie de tables.

Solution

Utilisez l'instruction LDD CREATE TABLE LIKE pour recréer la table de destination de sorte que sa représentation de schéma physique corresponde au schéma de la table source, puis copiez ou insérez les données :

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

Remplacez les éléments suivants :

  • PROJECT_ID : ID du projet contenant les tables.
  • DATASET : nom de l'ensemble de données contenant les tables.
  • DESTINATION_TABLE : nom de la table de destination à recréer.
  • SOURCE_TABLE : nom de la table source.

Résoudre les erreurs de limite d'imbrication de schémas, de fichiers et de routines

BigQuery applique une profondeur d'imbrication maximale de 15 niveaux pour les colonnes STRUCT (RECORD), les fichiers de chargement structurés, ainsi que les types d'arguments et les types renvoyés de routine imbriqués. Si vous dépassez cette limite de 15 niveaux, une erreur HTTP 400 Bad Request est renvoyée avec l'un des messages suivants (où MAX_DEPTH et MAX_LEVEL sont 15). Pour les erreurs causées par le dépassement de la limite d'imbrication de 16 niveaux sur les vues logiques ou les fonctions persistantes définies par l'utilisateur (UDF), consultez Trop de vues imbriquées ou de fonctions persistantes définies par l'utilisateur.

Messages d'erreur

  • 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

Diagnostic

Les colonnes de type STRUCT (RECORD) peuvent contenir des champs RECORD imbriqués jusqu'à une profondeur maximale de 15 niveaux, que les champs soient scalaires (NULLABLE ou REQUIRED) ou basés sur un tableau (REPEATED). De même, les jobs de chargement et les tables externes JSON, Parquet et Avro appliquent la limite d'imbrication lorsqu'ils analysent des structures imbriquées. Pour les fichiers Parquet, la limite est exprimée sous la forme d'un niveau de définition maximal et d'un niveau de répétition maximal.

Dans les erreurs de routine, TYPE_DESCRIPTION identifie le type imbriqué : argument type, return type ou return table type.

Solution

Cette limite ne peut pas être augmentée. Pour résoudre cette erreur, effectuez l'une des opérations suivantes :

  • Aplatissez le schéma ou la définition du type de routine. Divisez les hiérarchies STRUCT ou ARRAY profondément imbriquées dans votre schéma de table ou dans les types d'arguments et les types renvoyés de routine en tables associées distinctes, puis joignez-les au moment de la requête, ou promouvez les attributs profondément imbriqués en colonnes de niveau supérieur.
  • Utilisez le type de données JSON intégré. Si vos données nécessitent plus de 15 niveaux d'imbrication, stockez les sous-arborescences profondément imbriquées dans une colonne de type JSON (ou STRING). Une colonne JSON est stockée en tant que colonne unique dans le schéma de la table, ce qui évite la limite de 15 niveaux du schéma RECORD, tout en vous permettant d'extraire des chemins profondément imbriqués au moment de la requête à l'aide d'opérateurs et de fonctions d'accès aux champs JSON.
  • Prétraitez les fichiers d'entrée avant de les charger. Si l'erreur se produit lors d'un job de chargement à partir de fichiers JSON, Parquet ou Avro dans Cloud Storage, ajoutez une étape de prétraitement en amont pour aplatir ou sérialiser les structures de plus de 15 niveaux avant de les charger dans BigQuery.