Cómo crear y administrar 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, además, se pueden indexar o usar como 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 la 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

GoogleSQL 

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);

PostgreSQL 

CREATE TABLE users (
id VARCHAR(20) NOT NULL,
firstname VARCHAR(50),
lastname VARCHAR(50),
age BIGINT NOT NULL,
fullname VARCHAR(100) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
PRIMARY KEY(id)
);

El valor de FullName se calcula cuando se inserta una fila nueva o cuando se actualizan FirstName o LastName para una fila existente. El valor calculado se almacena junto con otras columnas de la tabla. El SQL entre 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 almacena el resultado de la expresión junto con otras columnas de la tabla. Las actualizaciones posteriores en cualquiera de las columnas a las que se hace referencia hacen que Spanner vuelva a evaluar y almacene la expresión.

  • Spanner no permite columnas generadas, a menos que se usen el atributo STORED.

  • No se permiten las escrituras directas en las columnas generadas.

  • No se permite la opción de columna allow_commit_timestamp en las columnas generadas o en cualquier columna a la que hagan referencia las columnas generadas.

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

  • No puedes descartar una columna a la que haga referencia una columna generada.

  • Puedes usar una columna generada como clave primaria con las siguientes restricciones adicionales:

    • La clave primaria generada no puede hacer referencia a otras columnas generadas.

    • La clave primaria generada puede hacer referencia, como máximo, a una columna sin clave.

    • La clave primaria generada no puede depender de una columna sin clave con una cláusula DEFAULT.

  • Las siguientes reglas se aplican cuando se usan columnas de claves generadas:

    • APIs de lectura: debes especificar por completo las columnas de clave, incluidas las que se generaron.
    • APIs de mutación: para INSERT, INSERT_OR_UPDATE y REPLACE, Spanner no te permite especificar columnas de claves generadas. Para UPDATE, tienes la opción de especificar columnas de claves generadas. Para DELETE, debes especificar por completo las columnas de clave, incluidas las claves generadas.
    • DML: No puedes escribir de forma explícita en claves generadas en declaraciones INSERT o UPDATE.
    • Consulta: Por lo general, te recomendamos que uses la columna de clave generada como un filtro en tu consulta. De manera opcional, si la expresión para la columna de claves generada usa solo una columna como referencia, la consulta puede aplicar una condición de igualdad (=) o IN a la columna a la que se hace referencia. Para obtener más información y ver un ejemplo, consulta Crea una clave única derivada de una columna de valores.

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

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

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

GoogleSQL 

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

PostgreSQL 

SELECT id, 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 clave externa.

Crea un índice en una columna generada

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

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Cómo agregar 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.

GoogleSQL 

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

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (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 reabastecer los valores de la columna. Durante el reabastecimiento, las columnas generadas y almacenadas no se pueden leer ni consultar. El estado de reabastecimiento se refleja en INFORMATION_SCHEMA.

Crea un índice parcial con una columna generada

¿Y si solo quisiéramos consultar a usuarios mayores de 18 años? Un escaneo completo de la tabla no sería eficiente, 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, de lo contrario, muestra NULL.

    GoogleSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 INT64
AS (IF(Age > 18, Age, NULL)) STORED;

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) 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 en GoogleSQL o el predicado IS NOT NULL en PostgreSQL. Este índice parcial es más pequeño y más eficiente que un índice normal porque excluye a todos los usuarios menores de 18 años.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

    CREATE INDEX UsersAbove18ByAge ON users (AgeAbove18)
WHERE AgeAbove18 IS NOT NULL;

  3. Para recuperar el Id y el Age de todos los usuarios mayores de 18 años, ejecuta la siguiente consulta.

    GoogleSQL 

    SELECT Id, Age
FROM Users@{FORCE_INDEX=UsersAbove18ByAge}
WHERE AgeAbove18 IS NOT NULL;

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 IS NOT NULL;

  4. Para filtrar según una edad diferente, por ejemplo, para recuperar todos los usuarios mayores de 21, usa el mismo índice y filtro en la columna generada de la siguiente manera:

    GoogleSQL 

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

    PostgreSQL 

    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 columna generada nueva con la expresión nueva.

Crea una clave primaria en una columna generada

En Spanner, puedes usar una columna generada en la clave primaria.

En el siguiente ejemplo, se muestra una declaración DDL que crea la tabla UserInfoLog con una columna generada ShardId. El valor de la columna ShardId depende de otra columna. Se deriva mediante una función MOD en la columna UserId. ShardId se declara como parte de la clave primaria.

GoogleSQL 

CREATE TABLE UserInfoLog (
  ShardId INT64 NOT NULL
  AS (MOD(UserId, 2048)) STORED,
  UserId INT64 NOT NULL,
  FullName STRING(1024) NOT NULL,
) PRIMARY KEY (ShardId, UserId);

PostgreSQL 

CREATE TABLE UserInfoLog (
  ShardId BIGINT GENERATED ALWAYS
  AS (MOD(UserId, '2048'::BIGINT)) STORED NOT NULL,
  UserId BIGINT NOT NULL,
  FullName VARCHAR(1024) NOT NULL,
  PRIMARY KEY(ShardId, UserId));

Por lo general, para acceder de manera eficiente a una fila específica, debes especificar todas las columnas de clave. En el ejemplo anterior, esto significaría proporcionar un ShardId y un UserId. Sin embargo, a veces Spanner puede inferir el valor de la columna de clave primaria generada si depende de otra columna y si el valor de la columna de la que depende está completamente determinado. Esto es cierto si la columna a la que hace referencia la columna de clave primaria generada cumple una de las siguientes condiciones:

  • Es igual a un valor constante o un parámetro vinculado en la cláusula WHERE.
  • Un operador IN establece su valor en la cláusula WHERE.
  • Obtiene su valor de una condición de equiunión

Por ejemplo, haz lo siguiente para la siguiente consulta:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.UserId=1;

Spanner puede inferir el valor de ShardId a partir del UserId proporcionado. La consulta anterior es equivalente a la siguiente consulta después de la optimización de consultas:

GoogleSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

PostgreSQL 

SELECT * FROM UserInfoLog
AS T WHERE T.ShardId = MOD(1, 2048)
AND T.UserId=1;

En el siguiente ejemplo, se muestra cómo crear la tabla Students y usar una expresión que recupere el campo id de la columna JSON StudentInfo y lo use como clave primaria:

GoogleSQL 

CREATE TABLE Students (
  StudentId INT64 NOT NULL
  AS (CAST(JSON_VALUE(StudentInfo, "$.id") AS INT64)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS (((StudentInfo ->> 'id'::TEXT))::BIGINT) STORED NOT NULL,
  StudentInfo JSONB NOT NULL,
  PRIMARY KEY(StudentId));

Visualiza las propiedades de una columna generada

El INFORMATION_SCHEMA de Spanner contiene información sobre las columnas generadas en tu base de datos. Los siguientes son algunos ejemplos de las preguntas que puedes responder cuando 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 pasar SPANNER_STATE en una consulta para averiguar el estado actual de la columna. SPANNER_STATE muestra los siguientes valores:

  • COMMITTED: la columna es completamente utilizable.
  • WRITE_ONLY: La columna se está reabasteciendo. No se permite la lectura.

Usa la siguiente consulta para encontrar el estado de una columna:

GoogleSQL 

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;

PostgreSQL 

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;

Nota: Una columna generada no afecta el rendimiento de una operación de lectura o consulta. Sin embargo, podría afectar el rendimiento de las operaciones de escritura (declaraciones “DML” y “Mutaciones”) debido a la sobrecarga de evaluar la expresión de columna de una columna generada si una operación de escritura modifica alguna de las columnas a las que se hace referencia en la expresión de columna generada. Dado que la sobrecarga varía según la carga de trabajo de escritura de la aplicación, el diseño del esquema y las características del conjunto de datos, te recomendamos que compares las aplicaciones antes de usar una columna generada.

¿Qué sigue?