Spécifier les valeurs de colonne par défaut

Cette page explique comment définir une valeur par défaut pour une colonne dans une table BigQuery. Lorsque vous ajoutez une ligne à une table qui ne contient pas de données pour une colonne avec une valeur par défaut, la valeur par défaut est écrite dans la colonne.

Expression de valeur par défaut

L'expression de valeur par défaut d'une colonne doit être un littéral ou l'une des fonctions suivantes :

• CURRENT_DATE
• CURRENT_DATETIME
• CURRENT_TIME
• CURRENT_TIMESTAMP
• GENERATE_UUID
• RAND
• SESSION_USER
• ST_GEOGPOINT

Vous pouvez composer une valeur par défaut STRUCT ou ARRAY à l'aide de ces fonctions, telles que [CURRENT_DATE(), DATE '2020-01-01'].

Les fonctions sont évaluées lorsque les données sont écrites dans la table. Le type de la valeur par défaut doit correspondre ou forcer le type de la colonne à laquelle il s'applique. Si aucune valeur par défaut n'est définie, la valeur par défaut est NULL.

Définir les valeurs par défaut

Vous pouvez définir la valeur par défaut des colonnes lorsque vous créez une table. Vous utilisez l'instruction LDD CREATE TABLE, et ajoutez le mot clé DEFAULT et l'expression de valeur par défaut après le nom et le type de la colonne. L'exemple suivant crée une table appelée simple_table contenant deux colonnes STRING, a et b. La valeur par défaut de la colonne b est 'hello'.

CREATE TABLE mydataset.simple_table (
  a STRING,
  b STRING DEFAULT 'hello');

Lorsque vous insérez des données dans simple_table qui omet la colonne b, la valeur par défaut 'hello' est utilisée à la place. Exemple :

INSERT mydataset.simple_table (a) VALUES ('val1'), ('val2');

La table simple_table contient les valeurs suivantes :

+------+-------+
| a    | b     |
+------+-------+
| val1 | hello |
| val2 | hello |
+------+-------+

Si une colonne est de type STRUCT, vous devez définir la valeur par défaut pour tout le champ STRUCT. Vous ne pouvez pas définir la valeur par défaut d'un sous-ensemble de champs. La valeur par défaut d'un tableau ne peut pas être NULL ni contenir d'éléments NULL. L'exemple suivant crée une table appelée complex_table et définit une valeur par défaut pour la colonne struct_col, qui contient des champs imbriqués, y compris un type ARRAY :

CREATE TABLE mydataset.complex_table (
  struct_col STRUCT<x STRUCT<x1 TIMESTAMP, x2 NUMERIC>, y ARRAY<DATE>>
    DEFAULT ((CURRENT_TIMESTAMP(), NULL),
             [DATE '2022-01-01', CURRENT_DATE()])
);

Vous ne pouvez pas définir de valeurs par défaut qui ne respectent pas une contrainte sur la colonne, telles qu'une valeur par défaut qui ne correspond pas à un type paramétré ou une valeur NULL par défaut lorsque le mode de la colonne est REQUIRED.

Modifier les valeurs par défaut

Pour modifier la valeur par défaut d'une colonne, sélectionnez l'une des options suivantes:

La définition de la valeur par défaut d'une colonne n'affecte que les futures insertions dans la table. Elle ne modifie aucune donnée de table existante. L'exemple suivant définit la valeur par défaut de la colonne a sur SESSION_USER().

ALTER TABLE mydataset.simple_table ALTER COLUMN a SET DEFAULT SESSION_USER();

Si vous insérez une ligne dans simple_table qui omet la colonne a, l'utilisateur de session actuel est utilisé à la place.

INSERT mydataset.simple_table (b) VALUES ('goodbye');

La table simple_table contient les valeurs suivantes :

+------------------+---------+
| a                | b       |
+------------------+---------+
| val1             | hello   |
| val2             | hello   |
| user@example.com | goodbye |
+------------------+---------+

Supprimer les valeurs par défaut

Pour supprimer la valeur par défaut d'une colonne, sélectionnez l'une des options suivantes :

Utiliser des instructions LMD avec des valeurs par défaut

Vous pouvez ajouter des lignes avec des valeurs par défaut à une table à l'aide de l'instruction LMD INSERT. La valeur par défaut est utilisée lorsque la valeur d'une colonne n'est pas spécifiée, ou lorsque le mot clé DEFAULT est utilisé à la place de l'expression de valeur. L'exemple suivant crée une table et insère une ligne dans laquelle chaque valeur est la valeur par défaut :

CREATE TABLE mydataset.mytable (
  x TIME DEFAULT CURRENT_TIME(),
  y INT64 DEFAULT 5,
  z BOOL);

INSERT mydataset.mytable (x, y, z) VALUES (DEFAULT, DEFAULT, DEFAULT);

La table mytable ressemble à ceci :

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
+-----------------+---+------+

La colonne z n'a pas de valeur par défaut. NULL est donc utilisé comme valeur par défaut. Lorsque la valeur par défaut est une fonction, telle que CURRENT_TIME(), elle est évaluée au moment de l'écriture de la valeur. Si vous appelez à nouveau INSERT avec la valeur par défaut de la colonne x, la valeur de TIME est différente. Dans l'exemple suivant, seule la colonne z possède une valeur définie explicitement, et les colonnes omises utilisent leurs valeurs par défaut :

INSERT mydataset.mytable (z) VALUES (TRUE);

La table mytable ressemble à ceci :

+-----------------+---+------+
| x               | y | z    |
+-----------------+---+------+
| 22:13:24.799555 | 5 | null |
| 22:18:29.890547 | 5 | true |
+-----------------+---+------+

Vous pouvez mettre à jour une table avec des valeurs par défaut à l'aide de l'instruction LMD MERGE. L'exemple suivant crée deux tables et met à jour l'une d'entre elles avec une instruction MERGE :

CREATE TABLE mydataset.target_table (
  a STRING,
  b STRING DEFAULT 'default_b',
  c STRING DEFAULT SESSION_USER())
AS (
  SELECT
    'val1' AS a, 'hi' AS b, '123@google.com' AS c
  UNION ALL
  SELECT
    'val2' AS a, 'goodbye' AS b, SESSION_USER() AS c
);

CREATE TABLE mydataset.source_table (
  a STRING DEFAULT 'default_val',
  b STRING DEFAULT 'Happy day!')
AS (
  SELECT
    'val1' AS a, 'Good evening！' AS b
  UNION ALL
  SELECT
    'val3' AS a, 'Good morning!' AS b
);

MERGE mydataset.target_table T
USING mydataset.source_table S
ON T.a = S.a
WHEN NOT MATCHED THEN
  INSERT(a, b) VALUES (a, DEFAULT);

Le résultat est le suivant :

+------+-----------+--------------------+
| a    | b         | c                  |
+------+-----------+--------------------+
| val1 | hi        | 123@google.com     |
| val2 | goodbye   | default@google.com |
| val3 | default_b | default@google.com |
+------+-----------+--------------------+

Vous pouvez mettre à jour une table avec des valeurs par défaut à l'aide de l'instruction LMD UPDATE. L'exemple suivant met à jour la table source_table afin que chaque ligne de la colonne b soit égale à sa valeur par défaut :

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

Le résultat est le suivant :

+------+------------+
| a    | b          |
+------+------------+
| val1 | Happy day! |
| val3 | Happy day! |
+------+------------+

Ajouter une table

Vous pouvez utiliser la commande bq query avec l'option --append_table pour ajouter les résultats d'une requête à une table de destination qui possède des valeurs par défaut. Si la requête omet une colonne avec une valeur par défaut, la valeur par défaut est attribuée. L'exemple suivant ajoute des données qui spécifient uniquement des valeurs pour la colonne z :

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT FALSE AS z UNION ALL SELECT FALSE AS Z'

La table mytable utilise les valeurs par défaut des colonnes x et y :

+-----------------+---+-------+
|        x        | y |   z   |
+-----------------+---+-------+
| 22:13:24.799555 | 5 |  NULL |
| 22:18:29.890547 | 5 |  true |
| 23:05:18.841683 | 5 | false |
| 23:05:18.841683 | 5 | false |
+-----------------+---+-------+

Charger les données

Vous pouvez charger des données dans une table avec des valeurs par défaut à l'aide de la commande bq load ou de l'objet LOAD DATA. Les valeurs par défaut sont appliquées lorsque les données chargées ont moins de colonnes que la table de destination. Les valeurs NULL dans les données chargées ne sont pas converties en valeurs par défaut.

Les formats binaires, tels que AVRO, Parquet ou ORC, possèdent des schémas de fichiers encodés. Lorsque le schéma de fichiers omet certaines colonnes, les valeurs par défaut sont appliquées.

Les formats de texte, tels que JSON et CSV, n'ont pas de schéma de fichier encodé. Pour spécifier leur schéma à l'aide de l'outil de ligne de commande bq, vous pouvez utiliser l'option --autodetect ou fournir un schéma JSON. Pour spécifier leur schéma à l'aide de l'instruction LOAD DATA, vous devez fournir une liste de colonnes. L'exemple suivant charge uniquement la colonne a à partir d'un fichier CSV :

LOAD DATA INTO mydataset.insert_table (a)
FROM FILES(
  uris = ['gs://test-bucket/sample.csv'],
  format = 'CSV');

API Write

L'API Storage Write ne renseigne les valeurs par défaut que lorsqu'il manque un champ contenu dans le schéma de la table de destination dans le schéma. Dans ce cas, le champ manquant est renseigné avec la valeur par défaut sur la colonne pour chaque écriture. Si le champ existe dans le schéma de flux d'écriture, mais est manquant dans les données elles-mêmes, le champ manquant est renseigné avec NULL. Par exemple, supposons que vous écriviez des données dans une table BigQuery avec le schéma suivant :

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

Le schéma du flux d'écriture suivant ne contient pas le champ c présent dans la table de destination :

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
]

Supposons que vous insérez les valeurs suivantes dans la table :

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

Le résultat est le suivant :

+-------+-------+-----------+
| a     | b     | c         |
+-------+-------+-----------+
| val_a | val_b | default_c |
| val_a | NULL  | default_c |
+-------+-------+-----------+

Le schéma du flux d'écriture contient le champ b. Par conséquent, la valeur par défaut default_b n'est pas utilisée même lorsqu'aucune valeur n'est spécifiée pour le champ. Étant donné que le schéma de flux d'écriture ne contient pas le champ c, chaque ligne de la colonne c est renseignée avec la valeur par défaut default_c.

Le schéma de flux d'écriture suivant correspond au schéma de la table dans laquelle vous écrivez :

[
  {
    "name": "a",
    "type": "STRING",
  },
  {
    "name": "b",
    "type": "STRING",
  }
  {
    "name": "c",
    "type": "STRING",
  }
]

Supposons que vous insérez les valeurs suivantes dans la table :

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}

Le schéma de flux d'écriture ne manque aucun champ contenu dans la table de destination. Par conséquent, aucune des valeurs par défaut des colonnes n'est appliquée, que les champs soient renseignés ou non dans les données diffusées :

+-------+-------+------+
| a     | b     | c    |
+-------+-------+------+
| val_a | val_b | NULL |
| val_a | NULL  | NULL |
+-------+-------+------+

Vous pouvez spécifier des paramètres de valeurs par défaut au niveau de la connexion dans le mappage default_missing_value_interpretation, au sein du message AppendRowsRequest. Si la valeur est définie sur DEFAULT_VALUE, la valeur manquante récupère la valeur par défaut même lorsque la colonne est présente dans le schéma utilisateur.

Vous pouvez également spécifier des valeurs par défaut au niveau de la requête dans le mappage missing_value_interpretations, au sein du message AppendRowsRequest. Chaque clé correspond au nom d'une colonne, et sa valeur indique comment interpréter les valeurs manquantes.

Par exemple, le mappage {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} signifie que toutes les valeurs manquantes dans la colonne col1 vont être interprétées comme des valeurs NULL, et toutes les valeurs manquantes dans la colonne col2 vont être interprétées comme étant la valeur par défaut définie pour la colonne col2, dans le schéma de la table.

Si un champ ne figure pas dans ce mappage et présente des valeurs manquantes, celles-ci sont interprétées comme des valeurs NULL.

Les clés ne peuvent être que des noms de colonne de premier niveau. Les clés ne peuvent pas être des sous-champs d'un élément struct, tels que col1.subfield1.

Utiliser la méthode d'API insertAll

La méthode API tabledata.insertAll renseigne les valeurs par défaut au niveau de la ligne lorsque des données sont écrites dans une table. Si une ligne ne contient pas de colonnes contenant des valeurs par défaut, les valeurs par défaut sont appliquées à ces colonnes.

Par exemple, supposons que vous disposiez du schéma de table suivant :

[
  {
    "name": "a",
    "mode": "NULLABLE",
    "type": "STRING",
  },
  {
    "name": "b",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_b'"
  },
  {
    "name": "c",
    "mode": "NULLABLE",
    "type": "STRING",
    "defaultValueExpression": "'default_c'"
  }
]

Supposons que vous insérez les valeurs suivantes dans la table :

{'a': 'val_a', 'b': 'val_b'}
{'a': 'val_a'}
{}

Le résultat est le suivant :

+-------+------------+-----------+
| a     | b          | c         |
+-------+------------+-----------+
| val_a | val_b      | default_c |
| val_a | default_b  | default_c |
| NULL  | default_b  | default_c |
+-------+------------+-----------+

La première ligne insérée ne contient pas de valeur pour le champ c. Par conséquent, la valeur par défaut default_c est écrite dans la colonne c. La deuxième ligne insérée ne contient pas de valeurs pour les champs b ou c. Leurs valeurs par défaut sont donc écrites dans les colonnes b et c. La troisième ligne insérée ne contient aucune valeur. La valeur écrite dans la colonne a est NULL, car aucune autre valeur par défaut n'est définie. Les valeurs par défaut default_b et default_c sont écrites dans les colonnes b et c.

Afficher les valeurs par défaut

Pour afficher la valeur par défaut d'une colonne, interrogez la vue INFORMATION_SCHEMA.COLUMNS. Le champ de colonne column_default contient la valeur par défaut de la colonne. Si aucune valeur par défaut n'est définie, la valeur est NULL. L'exemple suivant montre les noms de colonnes et les valeurs par défaut pour la table mytable :

SELECT
  column_name,
  column_default
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

Le résultat ressemble à ce qui suit :

+-------------+----------------+
| column_name | column_default |
+-------------+----------------+
| x           | CURRENT_TIME() |
| y           | 5              |
| z           | NULL           |
+-------------+----------------+

Limites

• Vous pouvez lire des tables avec des valeurs par défaut en ancien SQL, mais vous ne pouvez pas écrire dans des tables avec des valeurs par défaut en ancien SQL.
• Vous ne pouvez pas ajouter une colonne avec une valeur par défaut à une table existante. Cependant, vous pouvez ajouter la colonne sans valeur par défaut, puis modifier sa valeur par défaut à l'aide de l'instruction LDD ALTER COLUMN SET DEFAULT.
• Vous ne pouvez pas copier et ajouter une table source à une table de destination qui contient plus de colonnes que la table source. Les colonnes supplémentaires ont des valeurs par défaut. Au lieu de cela, vous pouvez exécuter INSERT destination_table SELECT * FROM source_table pour copier les données.

Étapes suivantes

• Pour en savoir plus sur le chargement de données dans BigQuery, consultez la page Présentation du chargement des données.