Especificar valores de columna predeterminados

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

Expresión de valor predeterminado

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

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

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

Las funciones se evalúan justo antes de que los datos se escriban en la tabla durante el procesamiento de la tarea. El tipo del valor predeterminado debe coincidir o convertirse al tipo de la columna a la que se aplica. Si no se define ningún valor predeterminado, el valor predeterminado es NULL.

Establecer valores predeterminados

Puede definir el valor predeterminado de las columnas al crear una tabla. Para ello, utiliza la CREATE TABLE instrucción DDL y añade la palabra clave DEFAULT y la expresión del valor predeterminado después del nombre y el tipo de la 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 y omites 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, debe definir el valor predeterminado de todo el campo STRUCT. No puedes definir el valor predeterminado de un subconjunto de campos. El valor predeterminado de una matriz no puede ser NULL ni contener ningún elemento NULL. En el siguiente ejemplo se crea una tabla llamada complex_table y se asigna un valor predeterminado a 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 definir valores predeterminados que infrinjan una restricción en la columna, como un valor predeterminado que no se ajuste a un tipo parametrizado o un valor predeterminado NULL cuando el modo de la columna sea REQUIRED.

Cambiar los valores predeterminados

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

Definir el valor predeterminado de una columna solo afecta a las futuras inserciones en la tabla. No se modifican los datos de las tablas. En el siguiente ejemplo se asigna el valor predeterminado SESSION_USER() a la columna a:

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 usará 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 |
+------------------+---------+

Quitar los valores predeterminados

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

Usar instrucciones DML con valores predeterminados

Puedes añadir filas con valores predeterminados a una tabla mediante la INSERTdeclaración de DML. 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 todos los valores son los predeterminados:

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 tendrá este aspecto:

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

La columna z no tiene ningún valor predeterminado, por lo que se usa NULL como valor predeterminado. Cuando el valor predeterminado es una función, como CURRENT_TIME(), se evalúa en el momento en que se escribe el valor. Si vuelves a llamar a INSERT con el valor predeterminado de la columna x, se obtendrá un valor diferente para TIME. En el siguiente ejemplo, solo la columna z tiene un valor definido explícitamente. Las columnas omitidas usan sus valores predeterminados:

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

La tabla mytable tendrá este aspecto:

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

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

Añadir una tabla

Puedes usar el comando bq query con la marca --append_table para añadir los resultados de una consulta a una tabla de destino que tenga valores predeterminados. Si la consulta omite una columna con un valor predeterminado, se asigna el valor predeterminado. En el siguiente ejemplo se añade información que especifica 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'

En la tabla mytable se usan los valores predeterminados de 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 |
+-----------------+---+-------+

Cargar datos

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

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

Los formatos de texto, como JSON y CSV, no tienen un esquema de archivo codificado. Para especificar su esquema con la herramienta de línea de comandos bq, puedes usar la marca --autodetect o proporcionar un esquema JSON. Para especificar su esquema mediante la instrucción LOAD DATA, debe proporcionar una lista de columnas. A continuación, se muestra 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 Write

La API Storage Write solo rellena los valores predeterminados cuando falta un campo del esquema de flujo de escritura en el esquema de la tabla de destino. En este caso, el campo que falta se rellena con el valor predeterminado de la columna en cada escritura. Si el campo existe en el esquema del flujo de escritura, pero falta en los datos, se rellenará con NULL. Por ejemplo, supongamos que estás escribiendo 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'"
  }
]

Falta el campo c en el siguiente esquema de flujo de escritura, que sí está presente en la tabla de destino:

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

Supongamos que transmite 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 la secuencia de escritura contiene el campo b, por lo que no se usa el valor predeterminado default_b aunque no se especifique ningún valor para el campo. Como el esquema de la secuencia de escritura no contiene el campo c, cada fila de la columna c se rellena con el valor predeterminado default_c de la tabla de destino.

El siguiente esquema de flujo de escritura coincide con el esquema de la tabla en la que está escribiendo:

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

Supongamos que transmite los siguientes valores a la tabla:

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

El esquema de la secuencia de escritura no tiene ningún campo que falte en la tabla de destino, por lo que no se aplica ningún valor predeterminado de las columnas, independientemente de si los campos se rellenan en los datos transmitidos:

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

Puedes especificar la configuración de los valores predeterminados a nivel de conexión en default_missing_value_interpretation dentro del mensaje AppendRowsRequest. Si el valor es DEFAULT_VALUE, el valor que falta adoptará el valor predeterminado aunque la columna se presente en el esquema de usuario.

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

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

Si un campo no está en este mapa y faltan valores, estos 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.

Usar el método de API insertAll

El método de la API tabledata.insertAll rellena los valores predeterminados a nivel de fila cuando se escriben datos en una tabla. Si faltan columnas con valores predeterminados en una fila, se aplicarán los valores predeterminados a esas columnas.

Por ejemplo, supongamos que tienes el siguiente esquema de tabla:

[
  {
    "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 transmite 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 ningún valor en el campo c, por lo que se escribe el valor predeterminado default_c en la columna c. La segunda fila insertada no contiene valores para los campos b ni c, por lo que sus valores predeterminados se escriben en las columnas b y c. La tercera fila insertada no contiene ningún valor. El valor escrito en la columna a es NULL, ya que no se ha definido ningún otro valor predeterminado. Los valores predeterminados default_b y default_c se escriben en las columnas b y c.

Ver 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 define ningún valor predeterminado, se utiliza 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 datos de tablas con valores predeterminados mediante SQL antiguo, pero no puedes escribir en ellas.
• No puedes añadir una columna nueva con un valor predeterminado a una tabla que ya tengas. Sin embargo, puede añadir la columna sin un valor predeterminado y, a continuación, cambiarlo mediante la instrucción ALTER COLUMN SET DEFAULT DDL.
• No puedes copiar y añadir una tabla de origen a una tabla de destino que tenga más columnas que la tabla de origen y que las columnas adicionales tengan valores predeterminados. En su lugar, puedes ejecutar INSERT destination_table SELECT * FROM source_table para copiar los datos.

Siguientes pasos

• Para obtener más información sobre cómo cargar datos en BigQuery, consulta la introducción a la carga de datos.