Questa pagina illustra come utilizzare la durata (TTL) nelle tabelle Spanner. Per saperne di più, consulta Informazioni sul TTL.
Prima di iniziare
Prima di iniziare, segui queste best practice.
Abilita backup e recupero point-in-time
Prima di aggiungere il TTL alla tabella, ti consigliamo di abilitare Backup e ripristino di Spanner. In questo modo puoi ripristinare completamente un database nel caso in cui tu elimini accidentalmente i tuoi dati con il criterio TTL.
Se hai abilitato il recupero point-in-time, puoi visualizzare e ripristinare i dati eliminati, senza un ripristino completo dal backup, se rientrano nel periodo di conservazione della versione configurato. Per informazioni sulla lettura di dati in passato, consulta Eseguire una lettura inattiva.
Pulisci i vecchi dati
Se è la prima volta che utilizzi TTL e prevedi che la prima esecuzione elimini molte righe, valuta innanzitutto la possibilità di eseguire manualmente la pulizia dei vecchi dati utilizzando il DML partizionato. In questo modo, hai un maggiore controllo sull'utilizzo delle risorse, invece di lasciare che sia il processo in background TTL. Il TTL viene eseguito a una priorità bassa, ideale per una pulizia incrementale. Tuttavia, questo potrebbe allungare il tempo necessario per eliminare il set iniziale di righe in un database occupato, perché lo scheduler di lavoro interno di Spanner darà priorità ad altre attività, come le query degli utenti.
Verifica le condizioni
Per le tabelle GoogleSQL, se vuoi verificare i dati interessati dal criterio di eliminazione delle righe prima di abilitare il TTL, puoi eseguire query sulla tabella utilizzando le stesse condizioni. Ad esempio:
GoogleSQL
SELECT COUNT(*)
FROM CalculatedRoutes
WHERE TIMESTAMP_ADD(CreatedAt, INTERVAL 30 DAY) < CURRENT_TIMESTAMP();
Autorizzazioni obbligatorie
Per modificare lo schema del database, devi disporre dell'autorizzazione spanner.databases.updateDdl. Per maggiori dettagli, vedi Controllo dell'accesso per Spanner.
Crea un criterio di eliminazione delle righe
GoogleSQL
Per creare un criterio di eliminazione delle righe utilizzando GoogleSQL, puoi definire una clausola ROW DELETION POLICY
quando crei una nuova tabella o aggiungere un criterio a una tabella esistente. Questa clausola contiene un'espressione di una colonna e un intervallo.
Per aggiungere un criterio al momento della creazione della tabella:
CREATE TABLE MyTable( Key INT64, CreatedAt TIMESTAMP, ) PRIMARY KEY (Key), ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));
Dove:
timestamp_column
deve essere una colonna esistente di tipoTIMESTAMP
. Le colonne con timestamp di commit sono valide, così come le colonne generate. Tuttavia, non puoi specificare una colonna generata che faccia riferimento a una colonna di timestamp di commit.num_days
indica il numero di giorni dopo il timestamp intimestamp_column
in cui la riga viene contrassegnata per l'eliminazione. Il valore deve essere un numero intero non negativo eDAY
è l'unica unità supportata.
Per aggiungere un criterio a una tabella esistente, utilizza l'istruzione ALTER TABLE
. Una tabella può avere al massimo un criterio di eliminazione di riga. L'aggiunta di un criterio di eliminazione delle righe a una tabella con un criterio esistente non riesce e restituisce un errore. Consulta TTL sulle colonne generate per specificare una logica di eliminazione delle righe più sofisticata.
ALTER TABLE Albums ADD ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));
PostgreSQL
Per creare un criterio di eliminazione delle righe utilizzando PostgreSQL, puoi definire una clausola TTL INTERVAL
quando crei una nuova tabella o aggiungere un criterio a una tabella esistente.
Per aggiungere un criterio al momento della creazione della tabella:
CREATE TABLE mytable ( key bigint NOT NULL, timestamp_column_name TIMESTAMPTZ, PRIMARY KEY(key) ) TTL INTERVAL interval_spec ON timestamp_column_name;
Dove:
timestamp_column_name
deve essere una colonna con tipo di datiTIMESTAMPTZ
. Devi creare questa colonna nell'istruzioneCREATE TABLE
. Le colonne con timestamp di commit sono valide, così come le colonne generate. Tuttavia, non puoi specificare una colonna generata che faccia riferimento a una colonna di timestamp di commit.interval_spec
indica il numero di giorni dopo il timestamp intimestamp_column_name
in cui la riga viene contrassegnata per l'eliminazione. Il valore deve essere un numero intero non negativo e deve corrispondere a un numero intero di giorni. Ad esempio,'3 days'
è consentito, ma'3 days - 2 minutes'
restituisce un errore.
Per aggiungere un criterio a una tabella esistente, utilizza l'istruzione ALTER TABLE
. Una tabella può avere al massimo un criterio TTL. L'aggiunta di un criterio TTL a una tabella
con un criterio esistente non riesce e restituisce un errore. Consulta TTL sulle colonne generate per specificare una logica TTL più sofisticata.
Per aggiungere un criterio a una tabella esistente:
ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;
ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;
Limitazioni
I criteri di eliminazione delle righe hanno le seguenti restrizioni.
TTL nelle tabelle a cui fa riferimento una chiave esterna
Non puoi creare un criterio di eliminazione delle righe:
- In una tabella a cui fa riferimento una chiave esterna che non include il vincolo ON ELIMINA CASCADE.
- Nel padre di una tabella a cui fa riferimento una chiave esterna che non include l'azione referenziale ON DELETE CASCADE.
Nell'esempio seguente, non puoi aggiungere un criterio di eliminazione delle righe alla tabella Customers
perché vi fa riferimento una chiave esterna nella tabella Orders
, che non ha il vincolo ON DELETE CASCADE.
L'eliminazione dei clienti potrebbe violare questo vincolo di chiave esterna. Inoltre, non puoi aggiungere un criterio di eliminazione delle righe alla tabella Districts
. L'eliminazione di una riga da Districts
potrebbe causare l'applicazione a cascata nella tabella Customers
secondaria, il che potrebbe violare il vincolo della chiave esterna nella tabella Orders
.
GoogleSQL
CREATE TABLE Districts (
DistrictID INT64
) PRIMARY KEY (DistrictID);
CREATE TABLE Customers (
DistrictID INT64,
CustomerID INT64,
CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE;
CREATE TABLE Orders (
OrderID INT64,
DistrictID INT64,
CustomerID INT64,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE districts (
districtid bigint NOT NULL,
PRIMARY KEY(districtid)
);
CREATE TABLE customers (
districtid bigint NOT NULL,
customerid bigint NOT NULL,
createdat timestamptz,
PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE;
CREATE TABLE orders (
orderid bigint NOT NULL,
districtid bigint,
customerid bigint,
PRIMARY KEY(orderid),
CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid)
);
Puoi creare un criterio di eliminazione delle righe in una tabella a cui fa riferimento un vincolo di chiave esterna che utilizza ON DELETE CASCADE
. Nell'esempio seguente, puoi creare nella tabella Customers
un criterio di eliminazione delle righe a cui fa riferimento il vincolo di chiave esterna CustomerOrder
definito nella tabella Orders
. Quando
il TTL elimina le righe in Customers
, l'eliminazione viene applicata alle righe corrispondenti
presenti nella tabella Orders
.
GoogleSQL
CREATE TABLE Districts (
DistrictID INT64,
CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID),
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));
CREATE TABLE Customers (
DistrictID INT64,
CustomerID INT64,
CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE,
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));
CREATE TABLE Orders (
OrderID INT64,
DistrictID INT64,
CustomerID INT64,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID) ON DELETE CASCADE
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE districts (
districtid bigint NOT NULL,
createdat timestamptz,
PRIMARY KEY(districtid)
) TTL INTERVAL '1 day' ON createdat;
CREATE TABLE customers (
districtid bigint NOT NULL,
customerid bigint NOT NULL,
createdat timestamptz,
PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE
TTL INTERVAL '1 day' ON createdat;
CREATE TABLE orders (
orderid bigint NOT NULL,
districtid bigint,
customerid bigint,
PRIMARY KEY(orderid),
CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid) ON DELETE CASCADE
);
Analogamente, puoi creare un criterio di eliminazione delle righe in un elemento padre di una tabella a cui fa riferimento un vincolo di chiave esterna ON DELETE CASCADE
.
TTL su colonne con valori predefiniti
Un criterio di eliminazione delle righe può utilizzare una colonna timestamp con un valore predefinito. Un tipico valore predefinito è CURRENT_TIMESTAMP
. Se nessun valore viene assegnato esplicitamente alla colonna o se la colonna è impostata sul valore predefinito da un'istruzione INSERT
o UPDATE
, nel calcolo della regola viene utilizzato il valore predefinito.
Nell'esempio seguente, il valore predefinito per la colonna CreatedAt
nella tabella Customers
è il timestamp in cui viene creata la riga.
GoogleSQL
CREATE TABLE Customers (
CustomerID INT64,
CreatedAt TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (CustomerID);
Per maggiori informazioni, consulta PREDEFINITO (espressione) in "Linguaggio di definizione dei dati GoogleSQL".
PostgreSQL
CREATE TABLE customers (
customerid bigint NOT NULL,
createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(customerid)
);
Per maggiori informazioni, vedi CREA TABELLA in "Linguaggio di definizione dei dati PostgreSQL".
TTL nelle colonne generate
I criteri di eliminazione delle righe possono utilizzare le colonne generate per esprimere regole più sofisticate. Ad esempio, puoi definire un criterio di eliminazione delle righe sul timestamp greatest
(GoogleSQL o PostgreSQL) di più colonne o mappare un altro valore a un timestamp.
GoogleSQL
La seguente tabella denominata Orders
monitora gli ordini di vendita.
Il proprietario della tabella vuole configurare un criterio di eliminazione delle righe che elimini gli ordini annullati dopo 30 giorni e gli ordini non annullati dopo 180 giorni.
Il TTL di Spanner consente un solo criterio di eliminazione delle righe per tabella. Per esprimere i due criteri in una singola colonna, puoi utilizzare una colonna generata con un'istruzione IF
:
CREATE TABLE Orders (
OrderId INT64 NOT NULL,
OrderStatus STRING(30) NOT NULL,
LastModifiedDate TIMESTAMP NOT NULL,
ExpiredDate TIMESTAMP AS (IF(OrderStatus = 'Cancelled',
TIMESTAMP_ADD(LastModifiedDate, INTERVAL 30 DAY),
TIMESTAMP_ADD(LastModifiedDate, INTERVAL 180 DAY))) STORED,
) PRIMARY KEY(OrderId),
ROW DELETION POLICY (OLDER_THAN(ExpiredDate, INTERVAL 0 DAY));
L'istruzione crea una colonna denominata ExpiredDate
che aggiunge 30 o 180 giorni a LastModifiedDate
a seconda dello stato dell'ordine. Quindi,
definisce il criterio di eliminazione delle righe in modo che le righe scadano nel giorno archiviato nella
colonna ExpiredDate
specificando INTERVAL 0 day
.
PostgreSQL
La seguente tabella denominata Orders
monitora gli ordini di vendita.
Il proprietario della tabella vuole configurare un criterio di eliminazione delle righe che elimini le righe dopo 30 giorni di inattività.
Il TTL di Spanner consente un solo criterio di eliminazione delle righe per tabella. Per esprimere i due criteri in una singola colonna, puoi creare una colonna generata:
CREATE TABLE orders (
orderid bigint NOT NULL,
orderstatus varchar(30) NOT NULL,
createdate timestamptz NOT NULL,
lastmodifieddate timestamptz,
expireddate timestamptz GENERATED ALWAYS AS (GREATEST(createdate, lastmodifieddate)) STORED,
PRIMARY KEY(orderid)
) TTL INTERVAL '30 days' ON expireddate;
L'istruzione crea una colonna generata denominata ExpiredDate
che valuta
la più recente delle due date (LastModifiedDate
o CreateDate
).
Quindi, definisce il criterio di eliminazione delle righe in modo che le righe scadano 30 giorni dopo
la creazione dell'ordine oppure, se l'ordine è stato modificato entro questi 30 giorni,
estenderà l'eliminazione di altri 30 giorni.
Tabelle TTL e con interleaving
Le tabelle con interleaving
sono un'ottimizzazione delle prestazioni che associa le righe correlate in una tabella one-to-many
secondaria a una riga in una tabella padre. Per aggiungere un criterio di eliminazione delle righe a una
tabella padre, tutte le tabelle figlio con interleaving devono specificare ON DELETE CASCADE
,
il che significa che le righe figlio verranno eliminate atomicamente con la riga padre. Ciò
garantisce l'integrità referenziale, in modo che le eliminazioni sulla tabella padre eliminino anche
le righe figlio correlate nella stessa transazione. Il TTL di Spanner non supporta ON DELETE NO ACTION
.
Dimensione massima della transazione
Spanner ha un limite per le dimensioni delle transazioni. Le eliminazioni a cascata su gerarchie padre-figlio di grandi dimensioni con colonne indicizzate potrebbero superare questi limiti e causare l'esito negativo di una o più operazioni TTL. Per le operazioni non riuscite, il TTL proverà nuovamente con batch più piccoli, fino a una singola riga padre. Tuttavia, le gerarchie figlio grandi, anche per una singola riga padre, possono comunque superare il limite di mutazione.
Le operazioni non riuscite sono riportate nelle metriche TTL.
Se una singola riga e i relativi elementi secondari con interleaving sono troppo grandi per essere eliminati, puoi collegare un criterio di eliminazione delle righe direttamente nelle tabelle figlio, oltre a quello nella tabella padre. Il criterio nelle tabelle figlio deve essere configurato in modo che le righe figlio vengano eliminate prima delle righe padre.
Valuta la possibilità di collegare un criterio di eliminazione delle righe alle tabelle figlio quando si applicano le due seguenti istruzioni:
- Alla tabella figlio sono associati indici globali; e
- Prevedi un numero elevato di (>100) righe secondarie per riga principale.
Elimina un criterio di eliminazione delle righe
Puoi rilasciare un criterio di eliminazione delle righe esistente da una tabella. Questo restituisce un errore se nella tabella non esiste un criterio di eliminazione delle righe.
GoogleSQL
ALTER TABLE MyTable
DROP ROW DELETION POLICY;
PostgreSQL
ALTER TABLE mytable
DROP TTL;
L'eliminazione di un criterio di eliminazione delle righe interrompe immediatamente tutti i processi TTL in esecuzione in background. Tutte le righe già eliminate dai processi in corso rimangono eliminate.
Elimina una colonna a cui fa riferimento un criterio di eliminazione delle righe
Spanner non consente di eliminare una colonna a cui fa riferimento un criterio di eliminazione delle righe. Devi eliminare il criterio di eliminazione delle righe prima di eliminare la colonna.
Visualizzare il criterio di eliminazione delle righe di una tabella
Puoi visualizzare i criteri di eliminazione delle righe delle tabelle Spanner.
GoogleSQL
SELECT TABLE_NAME, ROW_DELETION_POLICY_EXPRESSION
FROM INFORMATION_SCHEMA.TABLES
WHERE ROW_DELETION_POLICY_EXPRESSION IS NOT NULL;
Per maggiori informazioni, consulta Schema di informazioni per i database di dialetti GoogleSQL.
PostgreSQL
SELECT table_name, row_deletion_policy_expression
FROM information_schema.tables
WHERE row_deletion_policy_expression is not null;
Per maggiori informazioni, consulta Schema di informazioni per i database di dialetti PostgreSQL.
Modificare un criterio di eliminazione delle righe
Puoi modificare la colonna o l'espressione di intervallo di un criterio di eliminazione delle righe esistente. L'esempio seguente cambia la colonna da CreatedAt
a
ModifiedAt
ed estende l'intervallo da 1 DAY
a 7 DAY
. Questo restituisce un errore se nella tabella non esiste un criterio di eliminazione delle righe.
GoogleSQL
ALTER TABLE MyTable
REPLACE ROW DELETION POLICY (OLDER_THAN(ModifiedAt, INTERVAL 7 DAY));
PostgreSQL
ALTER TABLE mytable
ALTER TTL INTERVAL '7 days' ON timestampcolumn;