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
. O SQL entre parênteses é chamado de expressão de geração.
Uma coluna gerada pode ser marcada como STORED
para economizar o custo de 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 calculado é armazenado 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 (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)
);
É possível criar uma coluna gerada não armazenada omitindo o atributo STORED
no DDL. Esse tipo de coluna gerada é avaliada no momento
da consulta e pode simplificá-la. 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 restrições a seguir.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()
eCURRENT_TIMESTAMP()
, não podem ser transformadas em uma coluna gerada peloSTORED
ou em uma coluna gerada que é indexada.Não é possível modificar a expressão de um
STORED
ou uma coluna gerada indexada.
O atributo
STORED
que segue a expressão armazena o resultado da expressão com outras colunas da tabela. As atualizações subsequentes 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 comoNOT 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 que geraram referências de colunas.Para
STORED
ou colunas geradas que são indexadas, não é possível mudar o tipo de dados da coluna ou de quaisquer colunas referenciadas pela coluna gerada.Não é possível descartar uma coluna referenciada por uma coluna gerada.
É possível usar uma coluna gerada como uma chave primária com as seguintes restrições extras:
A chave primária gerada não pode fazer referência a outras colunas geradas.
A chave primária gerada pode fazer referência a, no máximo, uma coluna sem chave.
A chave primária gerada não pode depender de uma coluna que não seja de chave com uma cláusula
DEFAULT
.
As regras a seguir se aplicam ao usar colunas de chave geradas:
- APIs de leitura: é preciso especificar totalmente as colunas de chave, incluindo aquelas de chave geradas.
- APIs de mutação: para
INSERT
,INSERT_OR_UPDATE
eREPLACE
, o Spanner não permite especificar colunas de chave geradas. ParaUPDATE
, é possível especificar colunas de chave geradas. ParaDELETE
, é preciso especificar totalmente as colunas de chave, incluindo as chaves geradas. - DML: não é possível gravar explicitamente nas chaves geradas em instruções
INSERT
ouUPDATE
. - Consulta: em geral, recomendamos usar a coluna de chave gerada como um
filtro na consulta. Opcionalmente, se a expressão para a coluna de chave gerada usar apenas uma coluna como referência, a consulta poderá aplicar uma condição de igualdade (
=
) ouIN
à 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 tornar a consulta mais simples.
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
Você também pode 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 existente
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.
Use a instrução a seguir para adicionar outra coluna gerada que retorna a idade do usuário se ele tiver mais de 18 anos. Caso contrário, retorna
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;
Crie um índice nessa nova coluna e desative a indexação de valores
NULL
com a palavra-chaveNULL_FILTERED
no GoogleSQL ou o predicadoIS NOT NULL
no PostgreSQL. Esse índice parcial é menor 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;
Para recuperar
Id
eAge
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;
Para filtrar por uma idade diferente, por exemplo, para recuperar todos os usuários com mais de 21 anos, use o mesmo índice e filtre na 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;
Uma coluna gerada indexada pode economizar o custo de avaliar uma expressão no momento da consulta e evitar armazenar os valores duas vezes (na tabela base e no índice) em comparação com uma coluna gerada por
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 por STORED
ou uma coluna não armazenada
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 de forma eficiente uma linha específica, você precisa 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 ele depender de uma única outra coluna e se o valor da coluna da qual 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:
- 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áusulaWHERE
. - Recebe o valor de uma condição de 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 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?
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 atual, convém passar 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 que não está armazenada só pode ser acessada usando a consulta SQL. No entanto, se ele estiver indexado, será possível 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 o desempenho devido à sobrecarga da avaliação da expressão de coluna gerada.
O desempenho das operações de gravação (instruções e mutações DML) é afetado ao usar uma coluna gerada por STORED
ou uma coluna gerada que é indexada. A sobrecarga se deve à 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 comparar os aplicativos antes de usar uma coluna gerada.
A seguir
Saiba mais sobre o Esquema de informações para bancos de dados do dialeto GoogleSQL e o Esquema de informações para bancos de dados do dialeto PostgreSQL.
Saiba mais sobre as colunas geradas nos detalhes do parâmetro CREATE TABLE.