Crea y administra columnas generadas

Una columna generada es una columna que siempre se calcula a partir de otras columnas en una fila. Estas columnas pueden simplificar una consulta, ahorrar el costo de evaluar una expresión en el momento de la consulta y se pueden indexar o usar como una clave externa. En este artículo, describiremos cómo administrar este tipo de columna en tu base de datos.

Agrega una columna generada a una tabla nueva

En el siguiente fragmento de CREATE TABLE, creamos una tabla para almacenar información sobre los usuarios. Tenemos columnas para FirstName y LastName, y definimos una columna generada para FullName, que es la concatenación de FirstName y LastName

CREATE TABLE Users (
  Id STRING(20) NOT NULL,
  FirstName STRING(50),
  LastName STRING(50),
  Age INT64 NOT NULL,
  FullName STRING(100) AS (ARRAY_TO_STRING([FirstName, LastName], " ")) STORED,
) PRIMARY KEY (Id);

El valor de FullName se calcula cuando se inserta una nueva fila o cuando se actualiza FirstName o LastName para una fila existente. El valor calculado se almacena junto con otras columnas de la tabla. El SQL en paréntesis se denomina expresión de generación.

  • expression puede ser cualquier expresión de SQL válida que se pueda asignar al tipo de datos de la columna con las siguientes restricciones.

  • El atributo STORED que sigue a la expresión provoca que el resultado de la función se almacene junto con otras columnas de la tabla. Las actualizaciones posteriores de cualquiera de las columnas a las que se hace referencia hacen que la expresión se vuelva a evaluar y se almacene.

  • No se permiten las columnas generadas sin el atributo STORED.

  • No se permiten las escrituras directas a columnas generadas.

  • Las columnas generadas no se pueden usar ni como parte de una clave primaria. Sin embargo, pueden ser claves de índice secundarias.

  • No se permite la opción de columna allow_commit_timestamp en las columnas generadas ni las columnas a las que se hace referencia en las columnas generadas.

  • No puedes cambiar el tipo de datos de una columna generada ni las columnas a las que hace referencia la columna generada.

  • No puedes descartar una columna a la que se hace referencia en una columna generada.

La columna generada se puede consultar como cualquier otra columna, como se muestra en el siguiente ejemplo.

SELECT Id, FullName
FROM Users;

Esto es equivalente a la siguiente declaración, que no usa la columna generada almacenada.

SELECT Id, ARRAY_TO_STRING([FirstName, LastName], " ") as FullName
FROM Users;

Además de simplificar la consulta y ahorrar el costo de evaluar la expresión en el momento de la consulta, una columna generada también se puede indexar o usar como una clave externa.

Crea un índice en una columna generada

Para ayudar con las búsquedas en nuestra columna generada FullName, podemos crear un índice secundario como se muestra en el siguiente fragmento.

CREATE INDEX UsersByFullName ON Users (FullName);

Agrega una columna generada a una tabla existente

Con la siguiente declaración ALTER TABLE, agregamos una columna generada a la tabla Users para generar y almacenar las iniciales del usuario.

ALTER TABLE Users ADD COLUMN Initials STRING(2)
AS (ARRAY_TO_STRING([SUBSTR(FirstName, 0, 1), SUBSTR(LastName, 0, 1)], "")) STORED;

Agregar una columna generada almacenada a una tabla existente es una operación de larga duración para reabastecimientos de los valores de la columna. Durante el reabastecimiento, no se pueden leer ni consultar las columnas generadas almacenadas. El estado de reabastecimiento se refleja en el INFORMATION_SCHEMA.

Crea un índice parcial con una columna generada

¿Qué tal si solo quisiéramos consultar a usuarios mayores de 18 años? El análisis completo de la tabla sería ineficiente, por lo que usamos un índice parcial.

  1. Usa la siguiente instrucción para agregar otra columna generada que muestre la edad del usuario si es mayor de 18 años y muestra NULL de otra manera.

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
    AS (IF(Age > 18, Age, NULL)) STORED;
    
  2. Crea un índice en esta columna nueva y, luego, inhabilita la indexación de los valores NULL con la palabra clave NULL_FILTERED. Este índice parcial es más pequeño y más eficiente que un índice normal porque excluye a todas las personas mayores de 18 años.

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
    ON Users (AgeAbove18);
    
  3. Para recuperar Id y Age de todos los usuarios mayores de 18 años, ejecuta la siguiente consulta.

    SELECT Id, Age
        FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
        WHERE AgeAbove18 IS NOT NULL;
    
  4. Si deseas filtrar con una edad diferente, por ejemplo, para recuperar todos los usuarios mayores de 21 años, usa el mismo índice y filtra en la columna generada de la siguiente manera:

    SELECT Id, Age
        FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
        WHERE AgeAbove18 > 21;
    

Quita una columna generada

La siguiente declaración DDL descarta una columna generada de la tabla Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modifica una expresión de columna generada

No se permite modificar la expresión de una columna generada. En su lugar, debes descartar la columna existente y crear una nueva columna generada con la expresión nueva.

Visualiza las propiedades de una columna generada

INFORMATION_SCHEMA de Cloud Spanner contiene información sobre las columnas generadas en tu base de datos. Estos son algunos ejemplos de las preguntas que puedes responder si consultas el esquema de información.

¿Qué columnas generadas se definen en mi base de datos?

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.GENERATION_EXPRESSION IS NOT NULL;

¿Cuál es el estado actual de las columnas generadas en la tabla Users?

Si agregaste una columna generada a una tabla existente, es posible que desees ver su estado actual para determinar, por ejemplo, si la columna se reasignó y se usó por completo. Si SPANNER_STATE muestra WRITE_ONLY en la siguiente consulta, significa que Cloud Spanner aún está en proceso de rellenar la columna y que no se permiten las lecturas.

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.SPANNER_STATE
FROM INFORMATION_SCHEMA.COLUMNS as c
WHERE c.TABLE_NAME="Users" AND c.GENERATION_EXPRESSION IS NOT NULL;

¿Qué sigue?

  • Obtén más información sobre INFORMATION SCHEMA de Cloud Spanner.

  • Obtén más detalles sobre las columnas generadas en los detalles del parámetro CREATE TABLE.