Especifica una columna de identidad

En este documento, se describe cómo crear y usar columnas de identidad, a veces denominadas columnas de incremento automático, que se usan para crear y mantener claves primarias en tus tablas. Cuando insertas una fila en una tabla que tiene una columna de identidad, BigQuery genera un valor entero único para esa columna.

Descripción general

Una columna de identidad es una columna INT64 que se propaga con valores únicos generados por el sistema.

El caso de uso principal para las columnas de identidad es generar claves primarias. También puedes generar claves primarias con la GENERATE_UUID función para generar cadenas únicas, pero, por lo general, se prefieren las columnas de identidad por los siguientes motivos:

  • Los valores enteros requieren menos espacio de almacenamiento que los valores de cadena.
  • Usar números enteros para las uniones de tablas es más eficiente que usar cadenas.

Los valores de una columna de identidad se generan en función de un valor inicial que define el primer valor y un valor de incremento que define la diferencia mínima entre los valores generados de forma sucesiva.

Los valores de la columna de identidad generados tienen las siguientes propiedades:

  • Único. Los valores generados automáticamente son únicos dentro de la tabla.
  • Ordenado de forma flexible. No se garantiza que los valores generados estén en orden estrictamente creciente o decreciente.
  • Disperso. No se garantiza que los valores generados sean consecutivos. Es posible que se omitan algunos valores, pero los valores de una columna de identidad siempre difieren en un múltiplo del incremento que especifiques.

Limitaciones

  • Una tabla puede tener como máximo una columna de identidad.
  • Puedes leer desde las tablas con columnas de identidad mediante SQL heredado, pero no puedes escribir en tablas con columnas de identidad mediante SQL heredado.
  • No puedes usar el agrupamiento en clústeres ni la partición en una columna de identidad.

  • Las siguientes operaciones de copia de tablas no son compatibles si una tabla de origen o de destino tiene una columna de identidad:

    • Copia de tablas con disposición de escritura WRITE_APPEND o WRITE_TRUNCATE
    • Copia de tablas de varias fuentes

  • No se admite la transmisión de datos con la API de Storage Write (gRPC) ni el tabledata.insertAll método de la API para tablas con columnas de identidad.

Crea columnas de identidad

Puedes crear una columna de identidad cuando creas una tabla nueva con la CREATE TABLE instrucción DDL. Usa la cláusula GENERATED AS IDENTITY para designar una columna INT64 como una columna de identidad. Una tabla puede tener como máximo una columna de identidad. Puedes especificar uno de los siguientes modos de generación que determina si puedes insertar valores de forma manual en la columna de identidad:

  • GENERATED ALWAYS AS IDENTITY: Los valores siempre se generan por el sistema. No puedes proporcionar tu propio valor cuando insertas o actualizas datos en esta columna. Si no especificas ALWAYS o BY DEFAULT, se usa ALWAYS.

  • GENERATED BY DEFAULT AS IDENTITY: Puedes insertar o modificar valores en la columna de identidad. BigQuery no aplica la unicidad de los valores que insertas o modificas.

    Si omites la columna o proporcionas NULL cuando insertas datos, BigQuery genera un valor automáticamente. La columna de identidad no puede contener un valor NULL. Si deseas usar un valor generado en una instrucción INSERT, MERGE o UPDATE, puedes usar la palabra clave DEFAULT o NULL.

En el siguiente ejemplo, se crea la tabla mydataset.id_table con una columna de identidad id que comienza en 0 y se incrementa en 5:

CREATE TABLE mydataset.id_table (
  id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 0 INCREMENT BY 5),
  data STRING
);

Agrega la propiedad de columna de identidad a una columna

Para modificar una columna existente para generar valores de identidad, usa la ALTER TABLE ALTER COLUMN SET GENERATED instrucción DDL. Esta instrucción cambia una columna INT64 existente a una columna de identidad. No propaga valores para las filas existentes en la columna de identidad.

Usa instrucciones DML con columnas de identidad

Puedes usar instrucciones DML como INSERT, MERGE y UPDATE con columnas de identidad. En las siguientes secciones, se usa la tabla mydataset.mytable que tiene una columna de identidad llamada id y una columna de cadena llamada data:

CREATE OR REPLACE TABLE mydataset.mytable (
  id INT64 GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 10),
  data STRING
);

Inserta datos

Cuando insertas datos en una tabla con una columna de identidad, puedes omitir la columna de identidad de la lista de columnas para generar un valor para ella. En la siguiente instrucción INSERT, se omite la columna id, BigQuery genera valores para ella:

INSERT mydataset.mytable (data) VALUES ('A'), ('B'), ('C');

El resultado es similar al siguiente, aunque el orden de asignación de valores generados a las filas puede variar:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
+-----+------+

Si una columna de identidad se define con GENERATED BY DEFAULT AS IDENTITY, puedes especificar tu propio valor para la columna. También puedes usar la palabra clave DEFAULT o NULL para que BigQuery genere un valor.

La siguiente instrucción INSERT proporciona un valor para una fila y usa DEFAULT o NULL para generar valores para las otras dos filas:

INSERT mydataset.mytable (id, data)
VALUES (155, 'D'), (DEFAULT, 'E'), (NULL, 'F');

El resultado es similar al siguiente:

+-----+------+
| id  | data |
+-----+------+
| 110 | A    |
| 120 | B    |
| 100 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
+-----+------+

Si una columna de identidad se define con GENERATED ALWAYS AS IDENTITY, solo puedes usar la palabra clave DEFAULT para que BigQuery genere un valor. No puedes proporcionar tu propio valor ni usar NULL.

Combina datos

Puedes usar la MERGE instrucción para combinar datos en una tabla con una columna de identidad. Si tu columna de identidad usa el GENERATED BY DEFAULT AS IDENTITY modo de generación, puedes usar las palabras clave DEFAULT o NULL para generar un valor cuando insertas o actualizas datos como parte de una instrucción MERGE.

En el siguiente ejemplo, se combina mydataset.source_table en mydataset.mytable, se inserta una fila nueva si no hay una coincidencia en la columna data y se actualiza la columna id a un valor generado nuevo si hay una coincidencia:

CREATE OR REPLACE TABLE mydataset.source_table(data STRING)
AS SELECT * FROM UNNEST(['A', 'C', 'G']);

MERGE mydataset.mytable T
USING mydataset.source_table S
ON T.data = S.data
WHEN MATCHED THEN
  UPDATE SET id = DEFAULT
WHEN NOT MATCHED THEN
  INSERT(data)
  VALUES(S.data);

El resultado es similar al siguiente:

+-----+------+
| id  | data |
+-----+------+
| 160 | A    |
| 120 | B    |
| 150 | C    |
| 155 | D    |
| 140 | E    |
| 130 | F    |
| 170 | G    |
+-----+------+

Si tu columna de identidad usa el modo de generación GENERATED ALWAYS AS IDENTITY, no puedes incluir la columna de identidad en ninguna cláusula de actualización de combinación. Para usar una cláusula de inserción de combinación, puedes omitir la columna de identidad de la lista de columnas o usar la palabra clave DEFAULT.

Actualiza datos

Puedes usar la UPDATE instrucción para actualizar valores en una columna de identidad que usa el GENERATED BY DEFAULT AS IDENTITY modo de generación. Puedes usar las palabras clave DEFAULT o NULL para generar un valor nuevo.

En el siguiente ejemplo, se actualizan todos los valores de la columna id a valores generados recientemente:

UPDATE mydataset.mytable
SET id = NULL
WHERE TRUE;

El resultado es similar al siguiente:

+-----+------+
| id  | data |
+-----+------+
| 190 | A    |
| 210 | B    |
| 240 | C    |
| 230 | D    |
| 180 | E    |
| 200 | F    |
| 220 | G    |
+-----+------+

Si tu columna de identidad usa el modo de generación GENERATED ALWAYS AS IDENTITY, no puedes actualizar la columna de identidad.

Agrega datos a 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 tenga una columna de identidad. Si la consulta omite la columna de identidad, se genera un valor para ella.

En el siguiente ejemplo, se agregan datos solo para la columna data a mydataset.mytable:

bq query \
    --nouse_legacy_sql \
    --append_table \
    --destination_table=mydataset.mytable \
    'SELECT "H" AS data'

Se agrega una fila nueva con un valor id generado a mydataset.mytable.

Cargar datos

Puedes cargar datos en una tabla con una columna de identidad con el bq load comando o la LOAD DATA instrucción. Si se omite la columna de identidad de los datos o el esquema de origen, se generan valores para ella. Si la columna de identidad es GENERATED ALWAYS AS IDENTITY, se debe omitir.

En el siguiente ejemplo, se cargan datos de un archivo CSV data.csv en mydataset.mytable. El archivo solo contiene datos para la columna data:

"X"
"Y"

El siguiente comando bq load carga data.csv en mydataset.mytable, omite la fila de encabezado y especifica solo la columna data en el esquema:

bq load --source_format=CSV --skip_leading_rows=0 \
mydataset.mytable data.csv data:STRING

El trabajo de carga genera valores id para las filas nuevas.

Quita la propiedad de columna de identidad

Puedes quitar la propiedad de identidad de una columna con la ALTER TABLE ALTER COLUMN DROP GENERATED instrucción DDL.

En el siguiente ejemplo, se quitan las propiedades de la columna de identidad de la columna id en mydataset.mytable:

ALTER TABLE mydataset.mytable
ALTER COLUMN id DROP GENERATED;

Visualiza información sobre las columnas de identidad

Para ver la configuración de la columna de identidad de una columna, consulta la INFORMATION_SCHEMA.COLUMNS vista.

En el siguiente ejemplo, se muestra la información de la columna de identidad para las columnas en mydataset.mytable:

SELECT
  column_name,
  is_identity,
  identity_generation,
  identity_start,
  identity_increment
FROM
  mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE
  table_name = 'mytable';

El resultado es similar al siguiente:

+-------------+-------------+---------------------+----------------+--------------------+
| column_name | is_identity | identity_generation | identity_start | identity_increment |
+-------------+-------------+---------------------+----------------+--------------------+
| id          | YES         | BY DEFAULT          | 100            | 10                 |
| data        | NO          | NULL                | NULL           | NULL               |
+-------------+-------------+---------------------+----------------+--------------------+

Como alternativa, puedes consultar la columna ddl de la INFORMATION_SCHEMA.TABLES vista para ver la definición de la columna de identidad en la instrucción DDL CREATE TABLE de una tabla.

¿Qué sigue?