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 a seguir, criamos uma tabela para armazenar informações. sobre os usuários. Temos colunas para FirstName e LastName e definimos 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 no momento da consulta. Como resultado, o valor de FullName é calculado apenas quando uma nova linha é inserida ou quando FirstName ou LastName é atualizado para uma linha existente. O valor calculado é 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 (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 STORED no DDL. Esse tipo de coluna gerada é avaliado no momento da consulta, e simplificar a consulta. No PostgreSQL, é possível criar um bucket coluna gerada 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 atribuíveis 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 pode ser transformada em uma coluna gerada STORED ou uma coluna gerada que está indexado.

    • 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 com outras colunas da tabela. Depois em qualquer uma das colunas referenciadas faz com que o Spanner reavaliar e armazenar 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 allow_commit_timestamp não é permitida nas colunas geradas ou das colunas que geraram referências.

  • Para STORED ou colunas geradas que foram indexadas, não é possível mudar os dados o tipo da coluna ou de qualquer coluna referenciada 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 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 a, no máximo, uma coluna sem chave.

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

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

    • APIs de leitura: é preciso especificar completamente as colunas de chave, incluindo o 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, é possível especificar colunas de chave geradas. Para DELETE, é preciso especificar totalmente as colunas de chave, incluindo o as chaves geradas.
    • DML: não é possível gravar explicitamente nas chaves geradas em INSERT ou UPDATE declarações.
    • Consulta: em geral, recomendamos o uso da coluna de chave gerada como na consulta. Opcionalmente, se a expressão da chave gerada coluna usar apenas uma coluna como referência, a consulta poderá aplicar uma condição (=) ou IN à coluna referenciada. Para mais informações e um exemplo, consulte Crie 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 variável expressão. 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 seguinte instrução ALTER TABLE, podemos adicionar uma coluna gerada a a 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 existente, uma consulta para preencher os valores da coluna é iniciada. Durante o preenchimento, os campos as colunas geradas e armazenadas não podem ser lidas ou consultadas. O estado de preenchimento é refletidas 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 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;

  2. Criar um índice nesta nova coluna e desativar 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 é menor mais eficiente do que um índice normal, pois exclui todos que estão 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 idade diferente, por exemplo, para recuperar todos os usuários que estão acima de 21, 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 indexada gerada pode economizar o custo de avaliação de uma no momento da consulta e evite armazenar os valores duas vezes (na tabela base e í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;

Atualizar a expressão de uma coluna gerada por STORED ou um valor não armazenado indexado gerada não é permitida.

Criar uma chave primária em uma coluna gerada

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

O exemplo a seguir mostra uma instrução DDL que cria a UserInfoLog com uma coluna gerada por ShardId. O valor da coluna ShardId depende de outra coluna. Ele é derivado usando uma função MOD na 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, é preciso definir todas as chaves colunas. No exemplo anterior, isso significaria fornecer um ShardId e UserId. No entanto, às vezes o Spanner pode inferir o valor do coluna de chave primária gerada se depender de uma única outra coluna e se a da coluna da qual ela depende é totalmente determinado. Isso é verdadeiro se referenciada pela coluna de chave primária gerada atende a um dos seguintes conditions:

  • 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.
  • 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 à consulta a seguir após a consulta 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 recupera o campo id da coluna JSON StudentInfo e a 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));

Exibir propriedades de uma coluna gerada

O INFORMATION_SCHEMA do Spanner contém informações sobre os colunas no seu banco de dados. Confira 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 e NO para não armazenadas. colunas geradas, 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 existente, pode querer 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 armazenada só pode ser usando a consulta SQL. No entanto, se ele estiver indexado, você pode usar o ler a API para acessar o valor do índice.

Desempenho

Uma coluna gerada por STORED não afeta o desempenho de uma leitura ou consulta operação No entanto, as colunas não armazenadas usadas em uma consulta podem afetar desempenho devido à sobrecarga de avaliar a 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 indexado. 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 no expressão de coluna gerada. Como a sobrecarga varia de acordo com a gravação carga de trabalho para o aplicativo, design do esquema e características do conjunto de dados, recomendamos comparar seus aplicativos antes de usar uma coluna gerada.

A seguir