Una columna generada es una columna que siempre se calcula a partir de otras columnas seguidas. 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 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
. El SQL entre paréntesis se denomina expresión de generación.
Una columna generada se puede marcar como STORED
para ahorrar el costo de evaluar la expresión en el momento de la consulta. Como resultado, el valor de FullName
solo 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.
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)
);
Puedes crear una columna generada sin almacenar si omites el atributo STORED
en el DDL. Este tipo de columna generada se evalúa en el momento de la consulta y puede simplificar una consulta. En PostgreSQL, puedes crear una columna generada sin almacenar 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 de 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 en la misma tabla.
La expresión no puede contener subconsultas.
Las expresiones con funciones no determinísticas, como
PENDING_COMMIT_TIMESTAMP()
,CURRENT_DATE()
yCURRENT_TIMESTAMP()
, no se pueden convertir en una columna generadaSTORED
ni en una columna generada que está indexada.No puedes modificar la expresión de una
STORED
o de una columna generada indexada.
El atributo
STORED
que sigue a la expresión almacena el resultado de esta junto con otras columnas de la tabla. Las actualizaciones posteriores de cualquiera de las columnas a las que se hace referencia hacen que Spanner vuelva a evaluar y almacenar la expresión.Las columnas generadas que no son
STORED
no se pueden marcar comoNOT NULL
.No se permiten las escrituras directas en las columnas generadas.
La opción de columna
allow_commit_timestamp
no se permite en las columnas generadas ni en otras columnas que hagan referencia a columnas generadas.Para
STORED
o las columnas generadas que están indexadas, no puedes cambiar el tipo de datos de la columna ni 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 las columnas de claves generadas:
- API de lectura: Debes especificar por completo las columnas de clave, incluidas las columnas de clave generadas.
- APIs de mutación: para
INSERT
,INSERT_OR_UPDATE
yREPLACE
, Spanner no te permite especificar las columnas de claves generadas. ParaUPDATE
, tienes la opción de especificar las columnas de claves generadas. ParaDELETE
, debes especificar por completo las columnas de claves, incluidas las claves generadas. - DML: no puedes escribir de forma explícita en claves generadas en declaraciones
INSERT
oUPDATE
. - Consulta: En general, recomendamos que uses la columna de clave generada como filtro en tu consulta. De manera opcional, si la expresión para la columna de clave generada usa solo una columna como referencia, la consulta puede aplicar una condición de igualdad (
=
) oIN
a la columna a la que se hace referencia. Para obtener más información y un ejemplo, consulta Crea una clave única derivada de una columna de valor.
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;
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;
Crea un índice en una columna generada
También puedes indexar o usar una columna generada como clave externa.
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);
Agrega una columna generada a una tabla existente
Con la siguiente declaración ALTER TABLE
, podemos agregar 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 agregas una columna generada y almacenada a una tabla existente, se inicia una operación de larga duración para reabastecer los valores de la columna. Durante el reabastecimiento, las columnas generadas almacenadas no se pueden leer ni consultar. El estado de reabastecimiento se refleja en la tabla INFORMATION_SCHEMA.
Crea un índice parcial con una columna generada
¿Y si solo quisiéramos consultar a los usuarios mayores de 18 años? Un análisis completo de la tabla no sería eficiente, por lo que usamos un índice parcial.
Usa la siguiente declaración para agregar otra columna generada que muestre la edad del usuario si tiene más de 18 años; de lo contrario, muestra
NULL
.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;
Crea un índice en esta columna nueva y, luego, inhabilita la indexación de los valores
NULL
con la palabra claveNULL_FILTERED
en GoogleSQL o el predicadoIS NOT NULL
en PostgreSQL. Este índice parcial es más pequeño y eficiente que un índice normal porque excluye a todos los 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;
Para recuperar los valores
Id
yAge
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;
Para filtrar según una edad diferente, por ejemplo, para recuperar todos los usuarios que son mayores de 21, usa el mismo índice y filtra 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;
Una columna indexada generada puede ahorrar el costo de evaluar una expresión en el momento de la consulta y evitar almacenar los valores dos veces (en la tabla base y el índice) en comparación con una columna generada por
STORED
.
Quita una columna generada
La siguiente declaración DDL elimina una columna generada de la tabla Users
:
GoogleSQL
ALTER TABLE Users DROP COLUMN Initials;
PostgreSQL
ALTER TABLE users DROP COLUMN initials;
Modifica 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 permite actualizar la expresión de una columna generada STORED
ni de una columna generada sin almacenar que se indexó.
Crea una clave primaria en una columna generada
En Spanner, puedes usar una columna generada STORED
en la clave primaria.
En el siguiente ejemplo, se muestra una declaración DDL que crea la tabla UserInfoLog
con una columna ShardId
generada. 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 claves. En el ejemplo anterior, esto significaría proporcionar un ShardId
y una UserId
. Sin embargo, Spanner a veces puede inferir el valor de la columna de clave primaria generada si depende de una sola columna y si el valor de la columna de la que depende está determinado por completo. Esto es así si la columna a la que hace referencia la columna de clave primaria generada cumple con una de las siguientes condiciones:
- Es igual a un valor constante o un parámetro vinculado en la cláusula
WHERE
, o - Obtiene el valor establecido por un operador
IN
en la cláusulaWHERE
- Obtiene su valor de una condición de unión equivalente
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 es equivalente a la siguiente consulta después de la optimización de la consulta:
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?
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
para columnas generadas almacenadas, NO
para columnas generadas no almacenadas o NULL
para columnas no generadas.
¿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: Solo se puede acceder a una columna generada que no está almacenada mediante la consulta en SQL. Sin embargo, si está indexado, puedes usar la API de lectura para acceder al valor desde el índice.
Rendimiento
Una columna generada STORED
no afecta el rendimiento de una operación de lectura o consulta. Sin embargo, las columnas generadas no almacenadas que se usan en una consulta pueden afectar su rendimiento debido a la sobrecarga de evaluar la expresión de la columna generada.
El rendimiento de las operaciones de escritura (declaraciones DML y mutaciones) se ve afectado cuando se usa una columna generada STORED
o una columna generada que está indexada. La sobrecarga se debe a que se evalúa la expresión de 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 columna generada. Dado que la sobrecarga varía según la carga de trabajo de escritura para la aplicación, el diseño del esquema y las características del conjunto de datos, te recomendamos que compares tus aplicaciones antes de usar una columna generada.
¿Qué sigue?
Obtén más información sobre el Esquema de información para las bases de datos de dialectos de GoogleSQL y el Esquema de información para las bases de datos de dialectos de PostgreSQL de Spanner.
Obtén más detalles sobre las columnas generadas en los detalles del parámetro CREATE TABLE.