Crie e faça a gestão de colunas geradas

Uma coluna gerada é uma coluna que é sempre calculada a partir de outras colunas numa linha. Estas colunas podem simplificar uma consulta, poupar o custo de avaliação de uma expressão no momento da consulta e podem ser indexadas ou usadas como uma chave externa. Esta página descreve como gerir este tipo de coluna na sua base de dados para bases de dados com o dialeto GoogleSQL e bases de dados com o dialeto PostgreSQL.

Adicione uma coluna gerada a uma nova tabela

No seguinte fragmento CREATE TABLE, criamos uma tabela para armazenar informações sobre os utilizadores. Temos colunas para FirstName e LastName e definimos uma coluna gerada para FullName, que é a concatenação de FirstName e LastName. O SQL entre parênteses é denominado expressão de geração.

Uma coluna gerada pode ser marcada como STORED para guardar o custo de avaliação da expressão no momento da consulta. Como resultado, o valor de FullName só é calculado quando é inserida uma nova linha ou quando FirstName ou LastName é atualizado para uma linha existente. O valor calculado é armazenado juntamente com outras colunas na tabela.

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

Pode criar uma coluna gerada não armazenada omitindo o atributo STORED no DDL. Este tipo de coluna gerada é avaliado no momento da consulta e pode simplificar uma consulta. No PostgreSQL, pode criar uma coluna gerada não armazenada com o atributo VIRTUAL.

GoogleSQL 

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

PostgreSQL 

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

  • expression pode ser qualquer expressão SQL válida que seja atribuível ao tipo de dados da coluna com as seguintes restrições.

    • A expressão só pode referenciar colunas na mesma tabela.

    • A expressão não pode conter subconsultas.

    • Não é possível transformar expressões com funções não determinísticas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() e CURRENT_TIMESTAMP() numa coluna gerada STORED ou numa coluna gerada que seja indexada.

    • Não pode modificar a expressão de uma coluna gerada STORED ou indexada.

  • O atributo STORED que segue a expressão armazena o resultado da expressão juntamente com outras colunas da tabela. As atualizações subsequentes a qualquer uma das colunas referenciadas fazem com que o Spanner reavalie e armazene a expressão.

  • Não é possível marcar como NOT NULL as colunas geradas que não sejam STORED.

  • Não são permitidas escritas diretas em colunas geradas.

  • A opção de coluna allow_commit_timestamp não é permitida em colunas geradas nem em colunas às quais as colunas geradas fazem referência.

  • Para colunas STORED ou geradas que são indexadas, não pode alterar o tipo de dados da coluna nem de nenhuma coluna a que a coluna gerada faça referência.

  • Não pode eliminar uma coluna que seja referenciada por uma coluna gerada.

  • Pode usar uma coluna gerada como chave principal com as seguintes restrições adicionais:

    • A chave primária gerada não pode fazer referência a outras colunas geradas.

    • A chave primária gerada pode fazer referência, no máximo, a uma coluna não chave.

    • A chave principal gerada não pode depender de uma coluna sem chave com uma cláusula DEFAULT.

  • Aplicam-se as seguintes regras quando usa colunas de chaves geradas:

    • APIs de leitura: tem de especificar totalmente as colunas de chaves, incluindo as colunas de chaves geradas.
    • APIs de mutação: para INSERT, INSERT_OR_UPDATE e REPLACE, o Spanner não permite especificar colunas de chaves geradas. Para UPDATE, pode especificar opcionalmente colunas de chaves geradas. Para DELETE, tem de especificar totalmente as colunas de chaves, incluindo as chaves geradas.
    • DML: não pode escrever explicitamente em chaves geradas em declarações INSERT ou UPDATE.
    • Consulta: em geral, recomendamos que use a coluna de chave gerada como um filtro na sua consulta. Opcionalmente, se a expressão da coluna de chave gerada usar apenas uma coluna como referência, a consulta pode aplicar uma condição de igualdade (=) ou IN à coluna referenciada. Para mais informações e um exemplo, consulte Crie uma chave única derivada de uma coluna de valor.

Pode consultar a coluna gerada tal como qualquer outra coluna, conforme mostrado no exemplo seguinte.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

A consulta que usa Fullname é equivalente à consulta com a expressão gerada. Assim, uma coluna gerada pode simplificar a consulta.

GoogleSQL 

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

PostgreSQL 

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

Crie um índice numa coluna gerada

Também pode indexar ou usar uma coluna gerada como chave externa.

Para ajudar com as pesquisas na nossa coluna gerada FullName, podemos criar um índice secundário, conforme mostrado no fragmento seguinte.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Adicione uma coluna gerada a uma tabela existente

Usando a seguinte declaração ALTER TABLE, podemos adicionar uma coluna gerada à tabela Users para gerar e armazenar as iniciais do utilizador.

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;

Se adicionar uma coluna gerada armazenada a uma tabela existente, é iniciada uma operação de longa duração para preencher os valores das colunas. Durante o preenchimento, não é possível ler nem consultar as colunas geradas armazenadas. O estado do preenchimento é refletido na tabela INFORMATION_SCHEMA.

Crie um índice parcial com uma coluna gerada

E se quisermos consultar apenas utilizadores com mais de 18 anos? Uma leitura completa da tabela seria ineficiente, por isso, usamos um índice parcial.

  1. Use a seguinte declaração para adicionar outra coluna gerada que devolve a idade do utilizador se tiver mais de 18 anos e devolve NULL caso contrário.

    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. Crie um índice nesta nova coluna e desative a indexação de NULL valores com a palavra-chave NULL_FILTERED no GoogleSQL ou o predicado IS NOT NULL no PostgreSQL. Este índice parcial é mais pequeno e mais eficiente do que um índice normal porque exclui todas as pessoas com 18 anos ou menos.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

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

  3. Para obter o Id e o Age de todos os utilizadores com mais de 18 anos, execute a seguinte 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 uma idade diferente, por exemplo, para obter todos os utilizadores com mais de 21 anos, use o mesmo índice e filtre pela coluna gerada da seguinte forma:

    GoogleSQL 

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

    PostgreSQL 

    SELECT Id, Age
FROM users /*@ FORCE_INDEX = UsersAbove18ByAge */
WHERE AgeAbove18 > 21;

    Uma coluna gerada indexada pode poupar o custo de avaliação de uma expressão no momento da consulta e evitar o armazenamento dos valores duas vezes (na tabela base e no índice) em comparação com uma coluna gerada STORED.

Remova uma coluna gerada

A seguinte declaração DDL elimina uma coluna gerada da tabela Users:

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Modifique uma expressão de coluna gerada

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;

Não é permitido atualizar a expressão de uma coluna gerada STORED ou de uma coluna gerada não armazenada indexada.

Crie uma chave principal numa coluna gerada

No Spanner, pode usar uma STOREDcoluna gerada na chave primária.

O exemplo seguinte mostra uma declaração DDL que cria a tabela com uma coluna gerada.UserInfoLogShardId O valor da coluna ShardId depende de outra coluna. É derivado através da utilização de uma função MOD na coluna UserId. ShardId é declarado como parte da chave 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 aceder de forma eficiente a uma linha específica, tem de especificar todas as colunas de chave. No exemplo anterior, isto significaria fornecer um ShardId e um UserId. No entanto, o Spanner pode, por vezes, inferir o valor da coluna de chave primária gerada se depender de outra coluna única e se o valor da coluna da qual depende estiver totalmente determinado. Isto é verdade se a coluna referenciada pela coluna de chave principal gerada cumprir uma das seguintes condições:

  • É igual a um valor constante ou a um parâmetro associado na cláusula WHERE ou
  • O valor é definido por um operador IN na cláusula WHERE
  • Obtém o respetivo valor de uma condição de junção igual

Por exemplo, para a seguinte consulta:

GoogleSQL 

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

PostgreSQL 

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

O Spanner pode inferir o valor de ShardId a partir do UserId fornecido. A consulta anterior é equivalente à seguinte consulta após a otimização 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;

O exemplo seguinte mostra como criar a tabela Students e usar uma expressão que obtenha o campo id da coluna JSON StudentInfo e o use como chave primária:

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

Veja as propriedades de uma coluna gerada

O Spanner INFORMATION_SCHEMA contém informações sobre as colunas geradas na sua base de dados. Seguem-se alguns exemplos das perguntas às quais pode responder quando consulta o esquema de informações.

Que colunas geradas estão definidas na minha base de dados?

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 é YES para colunas geradas armazenadas, NO para colunas geradas não armazenadas ou NULL para colunas não geradas.

Qual é o estado atual das colunas geradas na tabela Users?

Se adicionou uma coluna gerada a uma tabela existente, pode querer transmitir SPANNER_STATE numa consulta para saber o estado atual da coluna. SPANNER_STATE devolve os seguintes valores:

  • COMMITTED: a coluna é totalmente utilizável.
  • WRITE_ONLY: a coluna está a ser preenchida. Não é permitida a leitura.

Use a seguinte consulta para encontrar o estado de uma coluna:

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: só é possível aceder a uma coluna gerada não armazenada através da consulta SQL. No entanto, se estiver indexado, pode usar a API de leitura para aceder ao valor do índice.

Desempenho

Uma STOREDcoluna gerada não afeta o desempenho de uma operação de leitura ou consulta. No entanto, as colunas geradas não armazenadas usadas numa consulta podem afetar o respetivo desempenho devido à sobrecarga da avaliação da expressão da coluna gerada.

O desempenho das operações de escrita (declarações DML e mutações) é afetado quando usa uma STOREDcoluna gerada ou uma coluna gerada que está indexada. A sobrecarga deve-se à avaliação da expressão da coluna gerada quando a operação de escrita insere ou modifica qualquer uma das colunas referenciadas na expressão da coluna gerada. Uma vez que a sobrecarga varia consoante a carga de trabalho de escrita para a aplicação, a estrutura do esquema e as caraterísticas do conjunto de dados, recomendamos que faça testes de referência das suas aplicações antes de usar uma coluna gerada.

O que se segue?