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 un en el momento de la consulta y puede indexarse o usarse como una clave externa. En este te 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 en el momento de la consulta. Como resultado, solo se calcula el valor de FullName cuando se inserta una nueva fila o cuando se actualiza FirstName o LastName para un fila existente. El valor calculado se almacena junto con otras columnas en la desde una tabla de particiones.

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 almacenamiento si omites STORED en el DDL. Este tipo de columna generada se evalúa en el momento de la consulta y simplificar las consultas. En PostgreSQL, puedes crear una red no almacenada columna generada 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 asignables 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.

    • Expresiones con funciones no determinísticas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() y CURRENT_TIMESTAMP() no se puede convertir en una columna generada en STORED 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 del junto con otras columnas de la tabla. Posterior las actualizaciones a cualquiera de las columnas a las que se hace referencia hacen que Spanner volver 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 las columnas generadas.

  • No se permite la opción de columna allow_commit_timestamp en las columnas generadas o cualquier columna que generó referencias de columnas.

  • No puedes cambiar los datos para STORED o las columnas generadas que están indexadas de columna o de cualquier columna a la que haga 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 lo siguiente 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 DEFAULT.

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

    • Read APIs: Debes especificar por completo las columnas de claves, incluida la columnas de claves generadas.
    • APIs de mutación: para INSERT, INSERT_OR_UPDATE y REPLACE, Spanner no te permite especificar columnas de claves generadas. Para UPDATE, de forma opcional, puedes especificar las columnas de claves generadas. Para DELETE, debes especificar completamente las columnas de clave, incluida la generadas.
    • DML: No puedes escribir de forma explícita en claves generadas en INSERT o UPDATE declaraciones.
    • 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 clave generada usa solo una columna como referencia, la consulta puede aplicar un (=) o la condición IN 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 equivale a la consulta con la consulta generada expresión. 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 sentencia 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 almacenada a una tabla existente, se crea una para reabastecer los valores de la columna. Durante el reabastecimiento, el las columnas generadas y almacenadas no se pueden leer ni consultar. El estado de reabastecimiento es reflejado 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, así que usamos un índice parcial.

  1. Usa la siguiente declaración para agregar otra columna generada que dé como resultado la edad del usuario si es mayor 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;

  2. Crea un índice en esta columna nueva y, luego, inhabilita la indexación de 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 todas las personas que 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 recuperar los valores Id y Age de todos los usuarios mayores de 18 años, ejecuta el comando 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 una edad diferente, por ejemplo, para recuperar todos los usuarios que están mayor a 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 durante la consulta y evita almacenar los valores dos veces (en la tabla base) y el índice) en comparación con una columna generada 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;

Actualiza la expresión de una columna generada por STORED o de una columna indexada no almacenada no se permite la columna generada.

Crea una clave primaria en una columna generada

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

En el siguiente ejemplo, se muestra una declaración DDL que crea el UserInfoLog tabla con una columna ShardId generada. El valor de la columna ShardId depende de otra columna. Se deriva usando una función MOD en el 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 claves columnas. En el ejemplo anterior, esto significaría proporcionar un ShardId y UserId Sin embargo, a veces, Spanner puede inferir el valor del clave primaria generada si depende de una única columna y si la valor de la columna de la que depende está completamente determinado. Esto es así si el 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áusula WHERE
  • 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 consulta Optimización:

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 un expresión que recupera el campo id de la columna JSON StudentInfo y lo usa 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 el elemento columnas en tu base de datos. Los siguientes son algunos ejemplos de las preguntas que 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 las columnas generadas almacenadas, NO para las columnas no almacenadas columnas generadas, 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 pasa 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 que no está almacenada solo puede a los que se accede usando la consulta en SQL. Sin embargo, si está indexado, puedes usar el read para acceder al valor del índice.

Rendimiento

Una columna generada en STORED no afecta el rendimiento de una lectura o una consulta una sola operación. Sin embargo, las columnas generadas que no se almacenan y que se usan en una consulta pueden afectar su 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 por STORED o una columna generada que sea de forma manual. 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 expresión de columna generada. Dado que la sobrecarga varía según el modelo la carga de trabajo para la aplicación, el diseño del esquema y las características del conjunto de datos, recomendarle que compare tus aplicaciones antes de usar una columna generada.

¿Qué sigue?