Especifica los valores predeterminados de la columna

En esta página, se describe cómo configurar un valor predeterminado para una columna en una tabla de BigQuery. Cuando agregas una fila a una tabla que no contiene datos en una columna con un valor predeterminado, el valor predeterminado se escribe en la columna.

Expresión de valor predeterminada

La expresión de valor predeterminada para una columna debe ser un literal o una de las siguientes funciones:

Puedes redactar un valor predeterminado STRUCT o ARRAY con estas funciones, como [CURRENT_DATE(), DATE '2020-01-01'].

Las funciones se evalúan cuando se escriben los datos en la tabla. El tipo del valor predeterminado debe coincidir o convertirse al tipo de columna a la que se aplica. Si no se establece ningún valor predeterminado, el valor predeterminado es NULL.

Configura valores predeterminados

Puedes establecer el valor predeterminado para las columnas cuando creas una tabla nueva. Usa la declaración DDL CREATE TABLE y agrega la palabra clave DEFAULT y la expresión de valor predeterminada después del nombre y el tipo de columna. En el siguiente ejemplo, se crea una tabla llamada simple_table con dos columnas STRING, a y b. La columna b tiene el valor predeterminado 'hello'.

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

Cuando insertas datos en simple_table que omite la columna b, se usa el valor predeterminado 'hello', por ejemplo:

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

La tabla simple_table contiene los siguientes valores:

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

Si una columna tiene el tipo STRUCT, debes establecer el valor predeterminado para todo el campo STRUCT. No puedes establecer el valor predeterminado para un subconjunto de los campos. El valor predeterminado para un arreglo no puede ser NULL ni contener ningún elemento NULL. En el siguiente ejemplo, se crea una tabla llamada complex_table y se establece un valor predeterminado para la columna struct_col, que contiene campos anidados, incluido un tipo 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()])
);

No puedes establecer valores predeterminados que violen una restricción en la columna, como un valor predeterminado que no cumple con un tipo con parámetro o un valor predeterminado NULL cuando el modo de la columna es REQUIRED.

Cambia los valores predeterminados

Para cambiar el valor predeterminado de una columna, selecciona una de las siguientes opciones:

Consola

En la consola de Google Cloud, ve a la página de BigQuery.

Ir a BigQuery
En el panel Explorador, expande el proyecto y conjunto de datos y, luego, elige la tabla.
En el panel de detalles, haga clic en la pestaña Esquema.
Haz clic en Editar esquema. Es posible que debas desplazarte para ver este botón.
En la página Esquema actual, busca el campo de nivel superior que deseas cambiar.
Escribe el valor predeterminado para ese campo.
Haz clic en Guardar.

SQL

Usa la declaración DDL ALTER COLUMN SET DEFAULT.

En la consola de Google Cloud, ve a la página de BigQuery.

Ir a BigQuery

En el editor de consultas, escribe la siguiente sentencia:

ALTER TABLE mydataset.mytable
ALTER COLUMN column_name SET DEFAULT default_expression;

Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Configurar el valor predeterminado para una columna solo afecta las inserciones futuras en la tabla y no cambia ningún dato de la tabla existente. En el siguiente ejemplo, se establece el valor predeterminado de la columna a en SESSION_USER();

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

Si insertas una fila en simple_table que omite la columna a, se usa el usuario de la sesión actual.

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

La tabla simple_table contiene los siguientes valores:

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

Quita los valores predeterminados

Para quitar el valor predeterminado de una columna, selecciona una de las siguientes opciones:

Consola

En la consola de Google Cloud, ve a la página de BigQuery.

Ir a BigQuery
En el panel Explorador, expande el proyecto y conjunto de datos y, luego, elige la tabla.
En el panel de detalles, haga clic en la pestaña Esquema.
Haz clic en Editar esquema. Es posible que debas desplazarte para ver este botón.
En la página Esquema actual, busca el campo de nivel superior que deseas cambiar.
Ingresa NULL para el valor predeterminado.
Haz clic en Guardar.

SQL

Usa la declaración DDL ALTER COLUMN DROP DEFAULT.

En la consola de Google Cloud, ve a la página de BigQuery.

Ir a BigQuery
En el editor de consultas, ingresa la siguiente sentencia:
```
ALTER TABLE mydataset.mytable ALTER COLUMN column_name DROP DEFAULT;
```
También puedes quitar el valor predeterminado de una columna si cambias su valor a NULL con la declaración DDL ALTER COLUMN SET DEFAULT.
Haz clic en Ejecutar.

Si deseas obtener información sobre cómo ejecutar consultas, visita Ejecuta una consulta interactiva.

Usa declaraciones DML con valores predeterminados

Puedes agregar filas con valores predeterminados a una tabla mediante la declaración DML INSERT. El valor predeterminado se usa cuando no se especifica el valor de una columna o cuando se usa la palabra clave DEFAULT en lugar de la expresión de valor. En el siguiente ejemplo, se crea una tabla y se inserta una fila en la que cada valor es el predeterminado:

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 tabla mytable se ve de la siguiente manera:

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

La columna z no tiene un valor predeterminado, por lo que se usa NULL como predeterminado. Cuando el valor predeterminado es una función, como CURRENT_TIME(), se evalúa en el momento en que se escribe el valor. Llamar a INSERT con el valor predeterminado para la columna x vuelve a generar un valor diferente para TIME. En el siguiente ejemplo, solo la columna z tiene un valor establecido de forma explícita, y las columnas omitidas usan sus valores predeterminados:

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

La tabla mytable se ve de la siguiente manera:

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

Puedes actualizar una tabla con valores predeterminados mediante la declaración DML MERGE. En el siguiente ejemplo, se crean dos tablas y se actualiza una de ellas con una declaración 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);

El resultado es el siguiente:

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

Puedes actualizar una tabla con valores predeterminados mediante la declaración DML UPDATE. En el siguiente ejemplo, se actualiza la tabla source_table para que cada fila de la columna b sea igual a su valor predeterminado:

UPDATE mydataset.source_table
SET b =  DEFAULT
WHERE TRUE;

El resultado es el siguiente:

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

Agrega una tabla

Puedes usar el comando bq query con la marca --append_table para agregar los resultados de una consulta a una tabla de destino que tiene valores predeterminados. Si la consulta omite una columna con un valor predeterminado, este se asigna. En el siguiente ejemplo, se agregan datos que especifican valores solo para la columna z:

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

La tabla mytable usa valores predeterminados para las columnas x y 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 |
+-----------------+---+-------+

Carga de datos

Puedes cargar datos en una tabla con valores predeterminados mediante el comando bq load o la declaración LOAD DATA. Los valores predeterminados se aplican cuando los datos cargados tienen menos columnas que la tabla de destino. Los valores NULL en los datos cargados no se convierten en valores predeterminados.

Los formatos binarios, como AVRO, ORC o Parquet, tienen esquemas de archivos codificados. Cuando el esquema del archivo omite algunas columnas, se aplican los valores predeterminados.

Los formatos de texto, como JSON y CSV, no tienen esquema de archivo codificado. Para especificar su esquema mediante la herramienta de línea de comandos de bq, puedes usar la marca --autodetect o proporcionar un esquema JSON. Para especificar su esquema mediante la declaración LOAD DATA, debes proporcionar una lista de columnas. El siguiente es un ejemplo que carga solo la columna a de un archivo CSV:

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

API de Write

La API de Storage Write solo propaga valores predeterminados cuando al esquema de transmisión de escritura le falta un campo que está contenido en el esquema de la tabla de destino. En este caso, el campo faltante se propaga con el valor predeterminado en la columna para cada escritura. Si el campo existe en el esquema de transmisión de escritura, pero falta en los datos, el campo faltante se propaga con NULL. Por ejemplo, supongamos que escribes datos en una tabla de BigQuery con el siguiente esquema:

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

Al siguiente esquema de transmisión de escritura le falta el campo c que está presente en la tabla de destino:

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

Supongamos que transmites los siguientes valores a la tabla:

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

El resultado es el siguiente:

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

El esquema de transmisión de escritura contiene el campo b, por lo que el valor predeterminado default_b no se usa incluso cuando no se especifica ningún valor para el campo. Dado que el esquema de transmisión de escritura no contiene el campo c, cada fila de la columna c se propaga con el valor predeterminado de la tabla de destino default_c.

El siguiente esquema de flujo de escritura coincide con el esquema de la tabla en la que escribes:

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

Supongamos que transmites los siguientes valores a la tabla:

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

Al esquema de transmisión de escritura no le falta ningún campo contenido en la tabla de destino, por lo que no se aplica ninguno de los valores predeterminados de las columnas, sin importar si los campos se propagan en los datos transmitidos:

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

Puedes especificar la configuración de valores predeterminados a nivel de conexión en default_missing_value_interpretation dentro del mensaje AppendRowsRequest. Si el valor se establece en DEFAULT_VALUE, el valor faltante detectará el valor predeterminado, incluso cuando la columna esté presente en el esquema del usuario.

También puedes especificar valores predeterminados a nivel de solicitud en el mapa missing_value_interpretations dentro del mensaje AppendRowsRequest. Cada clave es el nombre de una columna y su valor indica cómo interpretar los valores faltantes.

Por ejemplo, el mapa {'col1': NULL_VALUE, 'col2': DEFAULT_VALUE} significa que todos los valores faltantes en col1 se interpretan como NULL y todos los valores faltantes en col2 se interpretan como el valor predeterminado configurado para col2 en el esquema de tabla.

Si un campo no está en este mapa y tiene valores faltantes, los valores faltantes se interpretan como NULL.

Las claves solo pueden ser nombres de columnas de nivel superior. Las claves no pueden ser subcampos de struct, como col1.subfield1.

Usa el método de API `insertAll`

El método de API tabledata.insertAll propaga los valores predeterminados a nivel de fila cuando los datos se escriben en una tabla. Si a una fila le faltan columnas con valores predeterminados, estos se aplican a esas columnas.

Por ejemplo, supón que tienes el esquema de tabla siguiente:

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

Supongamos que transmites los siguientes valores a la tabla:

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

El resultado es el siguiente:

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

La primera fila insertada no contiene un valor para el campo c, por lo que el valor predeterminado default_c se escribe en la columna c. La segunda fila insertada no contiene valores para los campos b o c, por lo que sus valores predeterminados se escriben en las columnas b y c. La tercera fila insertada no contiene valores. El valor escrito en la columna a es NULL, ya que no se configuró ningún otro valor predeterminado. Los valores predeterminados default_b y default_c se escriben en las columnas b y c.

Visualiza los valores predeterminados

Para ver el valor predeterminado de una columna, consulta la vista INFORMATION_SCHEMA.COLUMNS. El campo de columna column_default contiene el valor predeterminado de la columna. Si no se establece ningún valor predeterminado, el valor es NULL. En el siguiente ejemplo, se muestran los nombres de las columnas y los valores predeterminados de la tabla mytable:

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

El resultado es similar al siguiente:

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

Limitaciones

Puedes leer desde las tablas con valores predeterminados mediante SQL heredado, pero no puedes escribir en tablas con valores predeterminados mediante SQL heredado.
No puedes agregar una columna nueva con un valor predeterminado a una tabla existente. Sin embargo, puedes agregar la columna sin un valor predeterminado y, luego, cambiarla con la declaración DDL ALTER COLUMN SET DEFAULT.
No puedes copiar y agregar una tabla de origen a una tabla de destino que tenga más columnas que la tabla de origen, y las columnas adicionales tienen valores predeterminados. En su lugar, puedes ejecutar INSERT destination_table SELECT * FROM source_table para copiar los datos.

¿Qué sigue?

Para obtener más información sobre la carga de datos en BigQuery, consulta Introducción a la carga de datos.

Especifica los valores predeterminados de la columna

Expresión de valor predeterminada

Configura valores predeterminados

Cambia los valores predeterminados

Consola

SQL

Quita los valores predeterminados

Consola

SQL

Usa declaraciones DML con valores predeterminados

Agrega una tabla

Carga de datos

API de Write

Usa el método de API insertAll

Visualiza los valores predeterminados

Limitaciones

¿Qué sigue?

Usa el método de API `insertAll`