Crear y gestionar columnas generadas

Una columna generada es una columna que siempre se calcula a partir de otras columnas de una fila. Estas columnas pueden simplificar una consulta, ahorrar el coste de evaluar una expresión en el momento de la consulta y se pueden indexar o usar como clave externa. En esta página se describe cómo gestionar este tipo de columna en tu base de datos para bases de datos con dialecto GoogleSQL y con dialecto PostgreSQL.

Añadir 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. El SQL entre paréntesis se denomina expresión de generación.

Una columna generada se puede marcar como STORED para ahorrar el coste de evaluar la expresión en el momento de la consulta. Por lo tanto, el valor de FullName solo se calcula cuando se inserta una fila nueva o cuando se actualiza FirstName o LastName en una fila ya existente. El valor calculado se almacena junto con otras columnas en la tabla.

GoogleSQL 

CREATE TABLE Users (
Id STRING(20) NOT NULL,
FirstName STRING(50),
LastName STRING(50),
Age INT64 NOT NULL,
FullName STRING(100) AS (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)
);

Para crear una columna generada no almacenada, omite el atributo STORED en el DDL. Este tipo de columna generada se evalúa en el momento de la consulta y puede simplificarla. En PostgreSQL, puedes crear una columna generada no almacenada con el atributo VIRTUAL.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

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

    • La expresión solo puede hacer referencia a columnas de la misma tabla.

    • La expresión no puede contener subconsultas.

    • Las expresiones con funciones no deterministas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() y CURRENT_TIMESTAMP(), no se pueden convertir en una columna generada STORED ni en una columna generada que esté indexada.

    • No puedes modificar la expresión de una columna generada STORED o indexada.

  • El atributo STORED que sigue a la expresión almacena el resultado de la expresión junto con otras columnas de la tabla. Si se actualiza alguna de las columnas a las que se hace referencia, Spanner vuelve a evaluar y almacenar la expresión.

  • Las columnas generadas que no son STORED no se pueden marcar como NOT NULL.

  • No se permiten las escrituras directas en columnas generadas.

  • La opción de columna allow_commit_timestamp no se permite en las columnas generadas ni en ninguna columna a la que hagan referencia las columnas generadas.

  • En el caso de las columnas STORED o generadas que estén indexadas, no puedes cambiar el tipo de datos de la columna ni de ninguna de las columnas a las que haga referencia la columna generada.

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

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

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

    • La clave principal generada puede hacer referencia, como máximo, a una columna que no sea clave.

    • La clave principal generada no puede depender de una columna que no sea clave con una cláusula DEFAULT.

  • Se aplican las siguientes reglas al usar columnas de clave generadas:

    • APIs de lectura: debe especificar completamente las columnas de clave, incluidas las columnas de clave generadas.
    • APIs de mutación: en INSERT, INSERT_OR_UPDATE y REPLACE, Spanner no permite especificar columnas de clave generadas. En el caso de UPDATE, puedes especificar de forma opcional las columnas de clave generadas. En el caso de DELETE, debe especificar por completo las columnas de claves, incluidas las claves generadas.
    • DML: no puedes escribir explícitamente en claves generadas en instrucciones INSERT o UPDATE.
    • Consulta: en general, te recomendamos que uses la columna de clave generada como filtro en tu consulta. Opcionalmente, si la expresión de la columna de clave generada solo usa 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 un ejemplo, consulte el artículo Crear una clave única derivada de una columna de valores.

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

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

La consulta que usa Fullname es equivalente a la consulta con la expresión generada. Por lo tanto, una columna generada puede simplificar la consulta.

GoogleSQL 

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

PostgreSQL 

SELECT id, firstname || ' ' || lastname as fullname
FROM users;

Crear un índice en una columna generada

También puede indexar o usar una columna generada como clave externa.

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

Añadir una columna generada a una tabla

Con la siguiente instrucción ALTER TABLE, podemos añadir 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;

Si añades una columna generada almacenada a una tabla, se inicia una operación de larga duración para rellenar los valores de la columna. Durante el relleno retroactivo, no se pueden leer ni consultar las columnas generadas almacenadas. El estado de relleno se refleja en la tabla INFORMATION_SCHEMA.

Crear un índice parcial con una columna generada

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

  1. Usa la siguiente instrucción para añadir otra columna generada que devuelva la edad del usuario si es mayor de 18 años y NULL en caso contrario.

    GoogleSQL 

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

    PostgreSQL 

    ALTER TABLE Users ADD COLUMN AgeAbove18 BIGINT
GENERATED ALWAYS AS (nullif( Age , least( 18, Age) )) VIRTUAL;

  2. Crea un índice en esta nueva columna e 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 eficiente que un índice normal porque excluye a todos los usuarios que tienen 18 años o menos.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

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

  3. Para obtener 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 por otra edad (por ejemplo, para obtener todos los usuarios mayores de 21 años), usa el mismo índice y filtra por 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;

    Una columna generada indexada puede ahorrar el coste de evaluar una expresión en el momento de la consulta y evitar almacenar los valores dos veces (en la tabla base y en el índice) en comparación con una columna generada STORED.

Quitar una columna generada

La siguiente instrucción DDL elimina una columna generada de la tabla Users:

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Modificar una expresión de columna generada

GoogleSQL 

ALTER TABLE Users ALTER COLUMN FullName STRING(100)
AS (ARRAY_TO_STRING(ARRAY_TO_STRING([LastName, FirstName ], " ")));

PostgreSQL 

ALTER TABLE users ADD COLUMN Initials VARCHAR(2)
GENERATED ALWAYS AS (lastname || ' ' || firstname) VIRTUAL;

No se puede actualizar la expresión de una columna generada STORED ni de una columna generada no almacenada indexada.

Crear una clave principal en una columna generada

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

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

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

Normalmente, para acceder de forma eficiente a una fila específica, debe especificar todas las columnas clave. En el ejemplo anterior, esto significaría proporcionar tanto ShardId como UserId. Sin embargo, Spanner puede inferir el valor de la columna de clave principal generada si depende de otra columna y si el valor de la columna de la que depende está totalmente determinado. Esto ocurre si la columna a la que hace referencia la columna de clave principal generada cumple una de las siguientes condiciones:

  • Es igual a un valor constante o a un parámetro enlazado en la cláusula WHERE, o
  • Su valor se asigna mediante un operador IN en la cláusula WHERE.
  • Obtiene su valor de una condición de unión equitativa

Por ejemplo, en 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 equivale 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 obtiene el campo id de la columna JSON StudentInfo y lo usa como clave principal:

GoogleSQL 

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

PostgreSQL 

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

Ver las propiedades de una columna generada

INFORMATION_SCHEMA de Spanner contiene información sobre las columnas generadas de tu base de datos. A continuación, se incluyen algunos ejemplos de las preguntas que puedes responder al consultar el esquema de información.

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

GoogleSQL 

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

PostgreSQL 

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

IS_STORED es YES en el caso de las columnas generadas almacenadas, NO en el de las columnas generadas no almacenadas o NULL en el de las columnas no generadas.

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

Si has añadido una columna generada a una tabla, puedes pasar SPANNER_STATE en una consulta para averiguar el estado actual de la columna. SPANNER_STATE devuelve los siguientes valores:

  • COMMITTED: la columna se puede usar por completo.
  • WRITE_ONLY: la columna se está rellenando. No se permite la lectura.

Usa la siguiente consulta para buscar 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: Solo se puede acceder a una columna generada que no esté almacenada mediante la consulta SQL. Sin embargo, si está indexado, puedes usar la API de lectura para acceder al valor del índice.

Rendimiento

Una columna generada STORED no afecta al rendimiento de una operación de lectura o de consulta. Sin embargo, las columnas generadas no almacenadas que se usan en una consulta pueden afectar a su rendimiento debido a la sobrecarga que supone evaluar la expresión de la columna generada.

El rendimiento de las operaciones de escritura (instrucciones DML y mutaciones) se ve afectado al usar una columna generada por STORED o una columna generada que esté indexada. La sobrecarga se debe a la evaluación de la expresión de la columna generada cuando la operación de escritura inserta o modifica cualquiera de las columnas a las que se hace referencia en la expresión de la columna generada. Dado que la sobrecarga varía en función de la carga de trabajo de escritura de la aplicación, el diseño del esquema y las características del conjunto de datos, le recomendamos que realice pruebas comparativas de sus aplicaciones antes de usar una columna generada.

Siguientes pasos