Creare e gestire le colonne generate

Una colonna generata è una colonna che viene sempre calcolata da altre colonne in una riga. Queste colonne possono semplificare una query, risparmiare il costo della valutazione di un'espressione al momento della query e possono essere indicizzate o utilizzate come chiave esterna. In questo articolo, spiegheremo come gestire questo tipo di colonna nel database.

Aggiungere una colonna generata a una nuova tabella

Nel seguente snippet CREATE TABLE, creiamo una tabella per archiviare le informazioni sugli utenti. Abbiamo colonne per FirstName e LastName e definiamo una colonna generata per FullName, che è la concatenazione di 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)
);

Il valore di FullName viene calcolato quando viene inserita una nuova riga o quando FirstName e/o LastName vengono aggiornati per una riga esistente. Il valore calcolato viene archiviato insieme ad altre colonne della tabella. L'SQL tra parentesi è chiamato espressione di generazione.

  • expression può essere qualsiasi espressione SQL valida assegnabile al tipo di dati della colonna con le seguenti restrizioni.

  • L'attributo STORED che segue l'espressione archivia il risultato dell'espressione insieme ad altre colonne della tabella. I successivi aggiornamenti a una qualsiasi delle colonne di riferimento portano Spanner a rivalutare e archiviare l'espressione.

  • Spanner non consente le colonne generate a meno che non utilizzino l'attributo STORED.

  • Non sono consentite scritture dirette nelle colonne generate.

  • L'opzione di colonna allow_commit_timestamp non è consentita sulle colonne generate o su qualsiasi colonna che ha generato un riferimento a colonne.

  • Non puoi modificare il tipo di dati di una colonna generata o di qualsiasi colonna a cui fa riferimento la colonna generata.

  • Non puoi rilasciare una colonna a cui fa riferimento una colonna generata.

  • Puoi utilizzare una colonna generata come chiave primaria con le seguenti limitazioni aggiuntive:

    • La chiave primaria generata non può fare riferimento ad altre colonne generate.

    • La chiave primaria generata può fare riferimento al massimo a una colonna non chiave.

    • La chiave primaria generata non può dipendere da una colonna non chiave con una clausola DEFAULT.

  • Quando utilizzi le colonne chiave generate, si applicano le seguenti regole:

    • API di lettura: devi specificare in modo completo le colonne chiave, incluse le colonne chiave generate.
    • API Mutation: per INSERT, INSERT_OR_UPDATE e REPLACE, Spanner non consente di specificare le colonne chiave generate. Per UPDATE, puoi facoltativamente specificare le colonne chiave generate. Per DELETE, devi specificare completamente le colonne delle chiavi, incluse le chiavi generate.
    • DML: non puoi scrivere esplicitamente in chiavi generate nelle istruzioni INSERT o UPDATE.
    • Query: in generale, ti consigliamo di utilizzare la colonna della chiave generata come filtro nella query. Facoltativamente, se l'espressione per la colonna chiave generata utilizza una sola colonna come riferimento, la query può applicare una condizione di uguaglianza (=) o IN alla colonna di riferimento. Per ulteriori informazioni e un esempio, consulta Creare una chiave univoca derivata da una colonna di valore.

È possibile eseguire query sulla colonna generata come qualsiasi altra colonna, come mostrato nell'esempio seguente.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

Questa istruzione equivale alla seguente istruzione, che non utilizza la colonna generata archiviata.

GoogleSQL 

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

PostgreSQL 

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

Oltre a semplificare la query e risparmiare il costo della valutazione dell'espressione al momento della query, una colonna generata può anche essere indicizzata o utilizzata come chiave esterna.

Crea un indice su una colonna generata

Per facilitare le ricerche nella colonna FullName generata, possiamo creare un indice secondario come mostrato nello snippet che segue.

GoogleSQL 

CREATE INDEX UsersByFullName ON Users (FullName);

PostgreSQL 

CREATE INDEX UserByFullName ON users (fullname);

Aggiungere una colonna generata a una tabella esistente

Utilizzando la seguente istruzione ALTER TABLE, aggiungiamo una colonna generata alla tabella Users per generare e memorizzare le iniziali dell'utente.

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;

L'aggiunta di una colonna generata archiviata a una tabella esistente è un'operazione a lunga esecuzione per eseguire il backfill dei valori della colonna. Durante il backfill, non è possibile leggere o eseguire query sulle colonne generate dall'archivio. Lo stato del backfill si riflette in INFORMATION_SCHEMA.

Crea un indice parziale utilizzando una colonna generata

E se volessimo inviare query solo agli utenti che hanno più di 18 anni? Un'analisi completa della tabella sarebbe inefficiente, quindi utilizziamo un indice parziale.

  1. Utilizza la seguente istruzione per aggiungere un'altra colonna generata che restituisca l'età dell'utente se ha più di 18 anni, e in caso contrario restituisce 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. Crea un indice su questa nuova colonna e disattiva l'indicizzazione dei valori NULL con la parola chiave NULL_FILTERED in GoogleSQL o il predicato IS NOT NULL in PostgreSQL. Questo indice parziale è più piccolo e più efficiente di un indice normale perché esclude tutti gli utenti che hanno almeno 18 anni.

    GoogleSQL 

    CREATE NULL_FILTERED INDEX UsersAbove18ByAge
ON Users (AgeAbove18);

    PostgreSQL 

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

  3. Per recuperare i valori Id e Age di tutti gli utenti maggiori di 18 anni, esegui la query seguente.

    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. Ad esempio, per applicare un filtro in base a un'età diversa e recuperare tutti gli utenti che hanno più di 21 anni, utilizza lo stesso indice e lo stesso filtro nella colonna generata nel seguente modo:

    GoogleSQL 

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

    PostgreSQL 

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

Rimuovere una colonna generata

La seguente istruzione DDL elimina una colonna generata dalla tabella Users.

ALTER TABLE Users
DROP COLUMN Initials;

Modificare un'espressione di colonna generata

Non è consentita la modifica dell'espressione di una colonna generata. Devi invece eliminare la colonna esistente e creare una nuova colonna generata con la nuova espressione.

Creare una chiave primaria su una colonna generata

In Spanner, puoi utilizzare una colonna generata nella chiave primaria.

L'esempio seguente mostra un'istruzione DDL che crea la tabella UserInfoLog con una colonna ShardId generata. Il valore della colonna ShardId dipende da un'altra colonna. Viene ottenuto utilizzando una funzione MOD nella colonna UserId. ShardId è stato dichiarato come parte della chiave primaria.

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, per accedere in modo efficiente a una riga specifica, devi specificare tutte le colonne chiave. Nell'esempio precedente, questo significa fornire sia un ShardId sia un UserId. Tuttavia, a volte Spanner può dedurre il valore della colonna di chiave primaria generata se dipende da una singola colonna e se il valore della colonna da cui dipende è completamente determinato. Questo vale se la colonna a cui fa riferimento la colonna di chiave primaria generata soddisfa una delle seguenti condizioni:

  • È uguale a un valore costante o a un parametro associato nella clausola WHERE oppure
  • Recupera il valore impostato da un operatore IN nella clausola WHERE
  • Recupera il suo valore da una condizione di equi-join

Ad esempio, per la seguente query:

GoogleSQL 

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

PostgreSQL 

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

Spanner può dedurre il valore di ShardId dal valore UserId fornito. Dopo l'ottimizzazione della query, la query precedente è equivalente alla seguente:

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;

L'esempio successivo mostra come creare la tabella Students e utilizzare un'espressione che recupera il campo id della colonna JSON StudentInfo e lo utilizza come chiave primaria:

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

Visualizzare le proprietà di una colonna generata

INFORMATION_SCHEMA di Spanner contiene informazioni sulle colonne generate sul tuo database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere quando esegui query sullo schema di informazioni.

Quali colonne generate vengono definite nel mio database?

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

Qual è lo stato attuale delle colonne generate nella tabella Users?

Se hai aggiunto una colonna generata a una tabella esistente, potresti voler passare SPANNER_STATE in una query per scoprire lo stato attuale della colonna. SPANNER_STATE restituisce i seguenti valori:

  • COMMITTED: la colonna è completamente utilizzabile.
  • WRITE_ONLY: è in corso il backfill della colonna. Nessuna lettura consentita.

Utilizza la seguente query per trovare lo stato di una colonna:

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: una colonna generata non influisce sulle prestazioni di un'operazione di lettura o query. Tuttavia, potrebbe influire sulle prestazioni delle operazioni di scrittura (istruzioni e mutazioni DML) a causa dell'overhead associato alla valutazione dell'espressione di colonna di una colonna generata se un'operazione di scrittura modifica una qualsiasi delle colonne indicate nell'espressione di colonna generata. Poiché l'overhead varia a seconda del carico di lavoro di scrittura per l'applicazione, della progettazione dello schema e delle caratteristiche del set di dati, ti consigliamo di eseguire il benchmark delle tue applicazioni prima di utilizzare una colonna generata.

Passaggi successivi

  • Scopri di più sullo strumento INFORMATION SCHEMA di Spanner.

  • Ulteriori dettagli sulle colonne generate sono disponibili nei dettagli del parametro CREATE TABLE.