Criar e gerenciar colunas geradas

Uma coluna gerada é sempre calculada a partir de outras colunas em uma linha. Essas colunas podem simplificar uma consulta, economizar o custo da avaliação de uma expressão no momento da consulta e ser indexadas ou usadas como chave externa. Neste artigo, descreveremos como gerenciar esse tipo de coluna no banco de dados.

Adicionar uma coluna gerada a uma nova tabela

No snippet de CREATE TABLE a seguir, criamos uma tabela para armazenar informações sobre os usuários. Temos colunas para FirstName e LastName e definimos uma coluna gerada para FullName, que é a concatenação de FirstName e 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)
);

O valor de FullName é computado quando uma nova linha é inserida ou quando FirstName e/ou LastName são atualizados para uma linha existente. O valor computado é armazenado junto com outras colunas da tabela. O SQL entre parênteses é chamado de expressão de geração.

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

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

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

  • A expressão não pode conter funções não determinísticas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() e CURRENT_TIMESTAMP().

  • Não é possível modificar a expressão de uma coluna gerada.

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

• O Spanner não permite colunas geradas, a menos que usem o atributo STORED.

• Não são permitidas gravações diretas nas colunas geradas.

• A opção de coluna allow_commit_timestamp não é permitida em colunas geradas ou em qualquer coluna que gerou referência de colunas.

• Não é possível alterar o tipo de dados de uma coluna gerada ou de qualquer coluna que a coluna gerada faz referência.

• Não é possível descartar uma coluna que faz referências a uma coluna gerada.

• Você pode usar uma coluna gerada como uma chave primária 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 primária gerada não pode depender de uma coluna sem chave com uma cláusula DEFAULT.

• As regras a seguir se aplicam ao usar colunas de chave geradas:

  • APIs de leitura: é necessário especificar totalmente as colunas de chave, incluindo as colunas de chave geradas.
  • APIs de mutação: para INSERT, INSERT_OR_UPDATE e REPLACE, o Spanner não permite especificar colunas de chave geradas. Para UPDATE, você tem a opção de especificar colunas de chave geradas. Para DELETE, é necessário especificar completamente as colunas de chave, incluindo as chaves geradas.
  • DML: não é possível gravar explicitamente em chaves geradas em instruções INSERT ou UPDATE.
  • Consulta: em geral, recomendamos usar a coluna de chave gerada como um filtro na consulta. Opcionalmente, se a expressão da coluna de chave gerada usar apenas uma coluna como referência, a consulta poderá aplicar uma condição de igualdade (=) ou IN à coluna referenciada. Para mais informações e um exemplo, consulte Criar uma chave exclusiva derivada de uma coluna de valor.

A coluna gerada pode ser consultada como qualquer outra coluna, conforme mostrado no exemplo a seguir.

GoogleSQL

SELECT Id, FullName
FROM Users;

PostgreSQL

SELECT id, fullname
FROM users;

Isso é equivalente à instrução a seguir, que não usa a coluna gerada armazenada.

GoogleSQL

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

PostgreSQL

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

Além de simplificar a consulta e economizar o custo da avaliação da expressão no momento da consulta, uma coluna gerada também pode ser indexada ou usada como chave externa.

Criar um índice em uma coluna gerada

Para ajudar com as pesquisas na coluna FullName gerada, podemos criar um índice secundário, conforme mostrado no snippet a seguir.

GoogleSQL

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL

CREATE INDEX UserByFullName ON users (fullname);

Adicionar uma coluna gerada a uma tabela atual

Usando a instrução ALTER TABLE a seguir, adicionamos uma coluna gerada à tabela Users para gerar e armazenar as iniciais do usuário.

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;

Adicionar uma coluna gerada armazenada a uma tabela atual é uma operação de longa duração para preencher os valores da coluna. Durante o preenchimento, as colunas armazenadas geradas não podem ser lidas ou consultadas. O estado de preenchimento é refletido no INFORMATION_ utilizadas.

Criar um índice parcial usando uma coluna gerada

E se quisermos consultar apenas usuários com mais de 18 anos? Uma verificação completa da tabela seria ineficiente, por isso, usamos um índice parcial.

1. Use a instrução a seguir para adicionar outra coluna gerada que retornará a idade do usuário se ele tiver mais de 18 anos. Caso contrário, retornará 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. Crie um índice nessa nova coluna e desative a indexação de valores NULL com a palavra-chave NULL_FILTERED no GoogleSQL ou o predicado IS NOT NULL no PostgreSQL. Esse índice parcial é menor e mais eficiente do que um índice normal, porque exclui todos 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 recuperar Id e Age de todos os usuários que têm mais de 18, 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. Se quiser filtrar uma idade diferente, por exemplo, para recuperar todos os usuários maiores de 21 anos, use o mesmo índice e filtre a coluna gerada da seguinte maneira:

   GoogleSQL

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

   PostgreSQL

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

Remover uma coluna gerada

A instrução DDL a seguir descarta uma coluna gerada da tabela Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modificar uma expressão de coluna gerada

Não é permitido modificar a expressão de uma coluna gerada. Em vez disso, você precisa descartar a coluna existente e criar uma nova coluna gerada com a nova expressão.

Criar uma chave primária em uma coluna gerada

No Spanner, é possível usar uma coluna gerada na chave primária.

O exemplo a seguir mostra uma instrução DDL que cria a tabela UserInfoLog com uma coluna gerada por ShardId. O valor da coluna ShardId depende de outra coluna. Ele é derivado usando uma função MOD na coluna UserId. ShardId é declarado como parte da chave primária.

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 acessar com eficiência uma linha específica, é necessário definir todas as colunas-chave. No exemplo anterior, isso significaria fornecer um ShardId e um UserId. No entanto, às vezes, o Spanner pode inferir o valor da coluna de chave primária gerada se ele depender de uma única outra coluna e se o valor da coluna de que ela depende for totalmente determinado. Isso acontecerá se a coluna referenciada pela coluna da chave primária gerada atender a uma das seguintes condições:

• for igual a um valor constante ou parâmetro vinculado na cláusula WHERE; ou
• Ela recebe o valor definido por um operador IN na cláusula WHERE
• Ela recebe o valor de uma condição equi-join

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 com base no 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 próximo exemplo mostra como criar a tabela Students e usar uma expressão que recupera o campo id da coluna JSON StudentInfo e o usa como a chave primária:

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

Exibir propriedades de uma coluna gerada

O INFORMATION_SCHEMA do Spanner contém informações sobre as colunas geradas no seu banco de dados. Veja a seguir alguns exemplos de perguntas que podem ser respondidas ao consultar o esquema de informações.

Quais colunas geradas são definidas no meu banco de dados?

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

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

Se você adicionou uma coluna gerada a uma tabela atual, convém transmitir SPANNER_STATE em uma consulta para descobrir o estado atual da coluna. SPANNER_STATE retorna os seguintes valores:

• COMMITTED: a coluna pode ser totalmente utilizada.
• WRITE_ONLY: a coluna está sendo preenchida. Nenhuma leitura é permitida.

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;

Observação: uma coluna gerada não afeta o desempenho de uma operação de leitura ou consulta. No entanto, isso pode afetar o desempenho das operações de gravação ("instruções DML" e "Mutações") devido à sobrecarga de avaliar a expressão de uma coluna gerada caso uma operação de gravação modifique qualquer uma das colunas referenciadas na expressão de coluna gerada. Como a sobrecarga varia de acordo com a carga de trabalho de gravação do aplicativo, o design do esquema e as características do conjunto de dados, recomendamos que você compare seus aplicativos antes de usar uma coluna gerada.

A seguir

• Saiba mais sobre o Esquema de informações do Spanner.

• Veja mais detalhes sobre as colunas geradas nos detalhes do parâmetro CREATE TABLE.