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 CREATE TABLE abaixo, criamos uma tabela para armazenar informações sobre usuários. 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 é chamado de expressão de geração.

Uma coluna gerada pode ser marcada como STORED para economizar o custo da avaliação da expressão no momento da consulta. Como resultado, o valor de FullName só é calculado quando uma nova linha é inserida ou quando FirstName ou LastName é atualizado para uma linha existente. O valor computado é armazenado junto 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)
);

É possível criar uma coluna gerada não armazenada omitindo o atributo STORED no DDL. Esse tipo de coluna gerada é avaliado no momento da consulta e pode simplificar uma consulta. No PostgreSQL, é possível criar uma coluna gerada não armazenada usando 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 possa ser atribuída 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.

    • Expressões com funções não determinísticas, como PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() e CURRENT_TIMESTAMP(), não podem ser transformadas em uma coluna gerada STORED ou uma coluna gerada que é indexada.

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

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

  • As colunas geradas que não são STORED não podem ser marcadas como NOT NULL.

  • Gravações diretas em colunas geradas não são permitidas.

  • A opção de coluna allow_commit_timestamp não é permitida em colunas geradas ou em colunas referenciadas por colunas geradas.

  • Para STORED ou colunas geradas indexadas, não é possível mudar o tipo de dados da coluna ou de qualquer coluna referenciada pela coluna gerada.

  • Não é possível excluir uma coluna referenciada por uma coluna gerada.

  • É possível usar uma coluna gerada como uma chave primária com as seguintes restrições:

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

    • A chave primária gerada pode referenciar, no máximo, uma coluna sem 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 geradas.
    • APIs Mutation: para INSERT, INSERT_OR_UPDATE e REPLACE, o Spanner não permite especificar colunas de chave geradas. Para UPDATE, é possível especificar colunas de chave geradas. Para DELETE, é necessário especificar totalmente as colunas de chave, incluindo as geradas.
    • DML: não é possível gravar explicitamente em chaves geradas em instruções INSERT ou UPDATE.
    • Consulta: em geral, recomendamos que você use a coluna de chave gerada como um filtro na consulta. 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;

A consulta que usa Fullname é equivalente à consulta com a expressão gerada. Portanto, 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;

Criar um índice em uma coluna gerada

Também é possível indexar ou usar uma coluna gerada como uma chave externa.

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

Usando a instrução ALTER TABLE a seguir, podemos adicionar 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;

Se você adicionar uma coluna gerada armazenada a uma tabela atual, uma operação de longa duração para preencher os valores da coluna será iniciada. Durante o preenchimento, as colunas geradas armazenadas não podem ser lidas ou consultadas. O estado de preenchimento é refletido na tabela INFORMATION_SCHEMA.

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 retorne a idade do usuário, caso tenha mais de 18 anos, e retorne 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. Crie um índice nessa nova coluna e desative a indexação dos 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 todas as pessoas que tenham 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. Para filtrar por uma idade diferente, por exemplo, para recuperar todos os usuários com mais de 21 anos, use o mesmo índice e filtro na 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 com índice pode economizar o custo da avaliação de uma expressão no momento da consulta e evitar o armazenamento de valores duas vezes (na tabela de base e no índice) em comparação com uma coluna gerada STORED.

Remover uma coluna gerada

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

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Modificar 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 uma coluna gerada não armazenada e indexada.

Criar uma chave primária em uma coluna gerada

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

O exemplo a seguir mostra uma instrução DDL que cria a tabela UserInfoLog com uma coluna gerada 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 uma linha específica de maneira eficiente, é necessário especificar 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 ela depender de uma única coluna e se o valor da coluna em que ela depende for totalmente determinado. Isso é verdadeiro se a coluna referenciada pela coluna de chave primária gerada atender a uma das seguintes condições:

  • É igual a um valor constante ou parâmetro vinculado na cláusula WHERE ou
  • O valor é definido por um operador IN na cláusula WHERE.
  • Ele recebe o valor de uma condição de uniã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 com base no UserId fornecido. A consulta anterior é equivalente à consulta a seguir após a otimização:

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 extrai o campo id da coluna JSON StudentInfo e o usa como 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));

Conferir as propriedades de uma coluna gerada

O INFORMATION_SCHEMA do Spanner contém informações sobre as colunas geradas no seu banco de dados. Confira a seguir alguns exemplos de perguntas que você pode responder ao consultar o esquema de informações.

Quais colunas geradas são definidas no meu banco 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 você adicionou uma coluna gerada a uma tabela, 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 consulta a seguir 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 que não é armazenada só pode ser acessada usando a consulta SQL. No entanto, se ele for indexado, você poderá usar a API de leitura para acessar o valor do índice.

Desempenho

Uma coluna gerada por STORED não afeta o desempenho de uma operação de leitura ou consulta. No entanto, as colunas geradas não armazenadas usadas em uma consulta podem afetar a performance devido à sobrecarga de avaliar a expressão da coluna gerada.

O desempenho das operações de gravação (instruções DML e mutações) é afetado ao usar uma coluna gerada STORED ou uma coluna gerada que é indexada. O overhead é devido à avaliação da expressão de coluna gerada quando a operação de gravação insere ou modifica 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ê faça um comparativo de seus aplicativos antes de usar uma coluna gerada.

A seguir