Crea e gestisci le colonne generate

Una colonna generata è una colonna che viene sempre calcolata da altre colonne in un riga di comando. 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 viene descritto 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 memorizzare le informazioni sugli utenti. Abbiamo colonne per FirstName e LastName e definiamo una generata per FullName, che è la concatenazione di FirstName e LastName. Il codice SQL tra parentesi è chiamato espressione di generazione.

Una colonna generata può essere contrassegnata come STORED per risparmiare il costo di valutazione della 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 archiviato insieme alle 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 (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)
);

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 semplificare le query. In PostgreSQL, puoi creare un modello di database generata 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 restrizioni.

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

    • L'espressione non può contenere sottoquery.

    • Le espressioni con funzioni non deterministiche come PENDING_COMMIT_TIMESTAMP(), CURRENT_DATE() e CURRENT_TIMESTAMP() non possono essere trasformate in una colonna generata da STORED o in una colonna generata che è indicizzata.

    • Impossibile modificare l'espressione di un valore STORED o la colonna generata indicizzata.

  • L'attributo STORED che segue l'espressione memorizza il risultato dell'istruzione insieme ad altre colonne della tabella. Successiva a qualsiasi colonna a cui viene fatto riferimento fa sì che Spanner rivalutare e archiviare 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 di colonna allow_commit_timestamp non è consentita nelle colonne generate o qualsiasi colonna che ha generato un riferimento a colonne.

  • Non puoi modificare i dati per STORED o per le colonne generate che sono indicizzate tipo di colonna 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 quanto segue 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 un DEFAULT.

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

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

È possibile eseguire query sulla colonna generata come qualsiasi altra colonna, come mostrato nella dall'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. Di conseguenza, 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;

Crea un indice su una colonna generata

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

Per facilitare le ricerche nella colonna FullName generata, possiamo creare una 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 a la 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 eseguita una per eseguire il backfill dei valori della colonna. Durante il backfill, non è possibile leggere o eseguire query su colonne generate. Lo stato di backfill è riportato nella tabella INFORMATION_SCHEMA.

Crea un indice parziale utilizzando una colonna generata

E se volessimo eseguire query solo su utenti maggiori di 18 anni? Una scansione completa della tabella non sarebbe efficiente, quindi usiamo un indice parziale.

  1. Utilizza la seguente istruzione per aggiungere un'altra colonna generata che restituisca un valore l'età dell'utente se ha più di 18 anni, altrimenti restituisce 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. 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 ed efficiente rispetto a un normale indice perché esclude chiunque sia 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 Id e Age di tutti gli utenti maggiori di 18 anni, esegui lo 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 che maggiore di 21, utilizza lo stesso indice e lo stesso filtro nella colonna generata in questo 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;

    Una colonna generata indicizzata può salvare il costo della valutazione di una al momento della query ed evita di archiviare i valori due volte (nella tabella di base e indice) rispetto a una colonna generata da 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 della 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;

Aggiornamento dell'espressione di una colonna generata STORED o di una colonna indicizzata non archiviata non è consentito.

Crea una chiave primaria in una colonna generata

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

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

Di solito, per accedere in modo efficiente a una riga specifica, devi specificare tutte le chiavi colonne. Nell'esempio precedente, ciò significa fornire sia ShardId che UserId. Tuttavia, a volte Spanner può dedurre il valore del parametro di chiave primaria generata se dipende da una sola altra colonna e se della colonna da cui dipende è completamente determinato. Ciò è vero se La colonna a cui viene fatto riferimento nella colonna di chiave primaria generata soddisfa una delle seguenti condizioni: condizioni:

  • È uguale a un valore costante o a un parametro associato nella clausola WHERE, oppure
  • Ottiene il suo valore impostato da un operatore IN nella clausola WHERE
  • Il valore viene ottenuto da una condizione di join equi

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 la query ottimizzazione:

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 seguente mostra come creare la tabella Students e utilizzare un'espressione che recupera il campo id della colonna JSON StudentInfo e lo utilizza come chiave principale:

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

Visualizza le proprietà di una colonna generata

Il valore INFORMATION_SCHEMA di Spanner contiene informazioni sull'oggetto delle colonne del database. Di seguito sono riportati alcuni esempi di domande può rispondere quando esegui una query sullo schema di informazioni.

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, ti consigliamo di passa 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 archiviata può essere con una query SQL. Tuttavia, se viene indicizzato, puoi utilizzare il metodo API Lettura per accedere al valore dall'indice.

Prestazioni

Una colonna STORED generata non influisce sulle prestazioni di una lettura o di una query operativa. Tuttavia, le colonne generate non memorizzate utilizzate in una query possono influire sulle relative rendimento a causa dell'overhead associato alla valutazione dell'espressione di colonna generata.

Le prestazioni delle operazioni di scrittura (istruzioni e mutazioni DML) sono interessate quando utilizzi una colonna generata STORED o una colonna generata che è indicizzati. Il sovraccarico è dovuto alla valutazione dell'espressione della colonna generata quando l'operazione di scrittura inserisce o modifica una delle colonne a cui fa riferimento l'espressione della colonna generata. Poiché l'overhead varia a seconda della scrittura carico di lavoro per le caratteristiche dell'applicazione, della progettazione dello schema e dei set di dati, ti consigliamo di confrontare le tue applicazioni prima di utilizzare una colonna generata.

Passaggi successivi