Creare e gestire 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 di valutazione di un'espressione al momento della query e possono essere indicizzate o utilizzate come chiave esterna. Questa pagina descrive come gestire questo tipo di colonna nel database per i database di dialetto GoogleSQL e i database di dialetto PostgreSQL.

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. L'SQL tra parentesi è chiamato espressione di generazione.

Una colonna generata può essere contrassegnata come STORED per risparmiare il costo di valutazione dell'espressione al momento della query. Di conseguenza, il valore di FullName viene calcolato solo quando viene inserita una nuova riga o quando FirstName o LastName viene aggiornato per una riga esistente. Il valore calcolato viene memorizzato insieme ad altre colonne nella tabella.

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

Puoi creare una colonna generata non archiviata omettendo l'attributo STORED nel DDL. Questo tipo di colonna generata viene valutata al momento della query e può semplificare una query. In PostgreSQL, puoi creare una colonna generata non archiviata utilizzando l'attributo VIRTUAL.

GoogleSQL 

FullName STRING(MAX) AS (CONCAT(FirstName, " ", LastName))

PostgreSQL 

fullname text GENERATED ALWAYS AS (firstname || ' ' || lastname) VIRTUAL

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

    • L'espressione può fare riferimento solo alle colonne della stessa tabella.

    • L'espressione può utilizzare solo funzioni immutabili. Una funzione immutabile è una funzione che restituisce gli stessi risultati ogni volta che viene chiamata con gli stessi valori degli argomenti. Per saperne di più, consulta Volatilità delle funzioni.

    • L'espressione deve essere scalare, il che significa che restituisce un singolo valore.

    • L'espressione non può contenere sottoquery.

    • Non puoi modificare l'espressione di una colonna generata STORED o indicizzata.

  • Per i database di dialetto GoogleSQL, una colonna generata non archiviata di tipo STRING o BYTES deve avere una lunghezza di MAX.

  • Per i database di dialetto PostgreSQL, una colonna generata non archiviata o virtuale di tipo VARCHAR deve avere una lunghezza di MAX.

  • L'attributo STORED che segue l'espressione memorizza il risultato dell'espressione insieme ad altre colonne della tabella. Gli aggiornamenti successivi a una delle colonne a cui viene fatto riferimento fanno sì che Spanner rivaluti e memorizzi l'espressione.

  • Le colonne generate che non sono STORED non possono essere contrassegnate come NOT NULL.

  • Non sono consentite scritture dirette nelle colonne generate.

  • L'opzione della colonna allow_commit_timestamp non è consentita nelle colonne generate o in qualsiasi colonna a cui fanno riferimento le colonne generate.

  • Per le colonne STORED o generate indicizzate, non puoi modificare il tipo di dati della colonna o di qualsiasi colonna a cui fa riferimento la colonna generata.

  • Non puoi eliminare 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 a una sola colonna non chiave.

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

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

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

La colonna generata può essere sottoposta a query come qualsiasi altra colonna, come mostrato nell'esempio seguente.

GoogleSQL 

SELECT Id, FullName
FROM Users;

PostgreSQL 

SELECT id, fullname
FROM users;

La query che utilizza Fullname è equivalente alla query con l'espressione generata. Pertanto, una colonna generata può semplificare la query.

GoogleSQL 

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

PostgreSQL 

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

Creare un indice su una colonna generata

Puoi anche indicizzare o utilizzare una colonna generata come chiave esterna.

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

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, possiamo aggiungere una colonna generata alla tabella Users per generare e archiviare 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;

Se aggiungi una colonna generata archiviata a una tabella esistente, viene avviata un'operazione a lunga esecuzione per eseguire il backfill dei valori della colonna. Durante il backfill, le colonne generate archiviate non possono essere lette o sottoposte a query. Lo stato di backfill viene riportato nella tabella INFORMATION_SCHEMA.

Creare un indice parziale utilizzando una colonna generata

Cosa succede se volessimo eseguire query solo sugli utenti di età superiore ai 18 anni? Una scansione completa della tabella sarebbe inefficiente, quindi utilizziamo un indice parziale.

  1. Utilizza la seguente istruzione per aggiungere un'altra colonna generata che restituisce l'età dell'utente se ha più di 18 anni e NULL in caso contrario.

    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. Crea un indice su questa nuova colonna e disattiva l'indicizzazione dei NULL valori con la NULL_FILTERED parola chiave in GoogleSQL o il IS NOT NULL predicato in PostgreSQL. Questo indice parziale è più piccolo ed efficiente di un indice normale perché esclude tutti gli utenti di età pari o inferiore a 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 Id e Age di tutti gli utenti di età superiore ai 18 anni, esegui la seguente query.

    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. Per filtrare in base a un'età diversa, ad esempio per recuperare tutti gli utenti di età superiore ai 21 anni, utilizza lo stesso indice e filtra la colonna generata come segue:

    GoogleSQL 

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

    PostgreSQL 

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

    Una colonna generata indicizzata può risparmiare il costo di valutazione di un'espressione al momento della query ed evitare di archiviare i valori due volte (nella tabella di base e nell'indice) rispetto a una colonna generata STORED.

Rimuovere una colonna generata

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

GoogleSQL 

  ALTER TABLE Users DROP COLUMN Initials;

PostgreSQL 

  ALTER TABLE users DROP COLUMN initials;

Modificare un'espressione di colonna generata

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;

Non è consentito aggiornare l'espressione di una colonna generata STORED o di una colonna generata non archiviata indicizzata.

Creare una chiave primaria su una colonna generata

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

L'esempio seguente mostra un'istruzione DDL che crea la tabella UserInfoLog con una colonna generata ShardId. Il valore della colonna ShardId dipende da un'altra colonna. Viene derivato utilizzando una funzione MOD sulla colonna UserId. ShardId viene dichiarata 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));

In genere, per accedere in modo efficiente a una riga specifica, devi specificare tutte le colonne delle chiavi. Nell'esempio precedente, ciò significherebbe fornire sia un ShardId sia un UserId. Tuttavia, a volte Spanner può dedurre il valore della colonna della chiave primaria generata se dipende da un'altra colonna e se il valore della colonna da cui dipende è completamente determinato. Questo è vero se la colonna a cui fa riferimento la colonna della chiave primaria generata soddisfa una delle seguenti condizioni:

  • È uguale a un valore costante o a un parametro associato nella clausola WHERE oppure
  • Il suo valore viene impostato da un operatore IN nella clausola WHERE
  • Il suo valore proviene 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 UserId fornito. La query precedente è equivalente alla seguente query dopo l'ottimizzazione della query:

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 (INT64(StudentInfo.id)) STORED,
  StudentInfo JSON NOT NULL,
) PRIMARY KEY (StudentId);

PostgreSQL 

CREATE TABLE Students (
  StudentId BIGINT GENERATED ALWAYS
  AS ((StudentInfo ->> 'id')::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 nel database. Di seguito sono riportati alcuni esempi di domande a cui puoi rispondere quando esegui query sullo schema informativo.

Quali colonne generate sono definite nel mio database?

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 per le colonne generate archiviate, NO per le colonne generate non archiviate o NULL per le colonne non generate.

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: la colonna è in fase di backfill. Non è consentita alcuna lettura.

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: è possibile accedere a una colonna generata non archiviata solo utilizzando la query SQL. Tuttavia, se è indicizzata, puoi utilizzare l' API di lettura per accedere al valore dall'indice.

Prestazioni

Una colonna generata STORED non influisce sul rendimento di un'operazione di lettura o query. Tuttavia, le colonne generate non archiviate utilizzate in una query possono influire sul suo rendimento a causa dell'overhead della valutazione dell'espressione della colonna generata.

Il rendimento delle operazioni di scrittura (istruzioni DML e mutazioni) è influenzato dall'utilizzo di una colonna generata STORED o di una colonna generata indicizzata. L'overhead è dovuto alla valutazione dell'espressione della colonna generata quando l'operazione di scrittura inserisce o modifica una delle colonne a cui viene fatto riferimento nell'espressione della 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 benchmarking delle applicazioni prima di utilizzare una colonna generata.

Passaggi successivi