このページでは、Spanner テーブルで有効期間(TTL)を使用する方法について説明します。詳細については、TTL についてをご覧ください。
準備
始める前に、以下のベスト プラクティスに従ってください。
バックアップとポイントインタイム リカバリを有効にする
テーブルに TTL を追加する前に、Spanner のバックアップと復元を有効にすることをおすすめします。これによって、誤って TTL ポリシーでデータを削除した場合に、データベースを完全に復元できます。
ポイントインタイム リカバリを有効にしている場合は、削除されたデータが構成されたバージョンの保持期間内にあれば、バックアップから完全に復元することなく、それを表示して復元できます。過去のデータの読み取りについては、ステイル読み取りの実行をご覧ください。
古いデータをクリーンアップする
TTL を初めて使用し、最初の実行で多数の行が削除されることが予想される場合は、まずパーティション化 DML を使用して古いデータを手動でクリーンアップすることを検討してください。これにより、リソースの使用状況を TTL バックグラウンド プロセスにまかせるのではなく、より細かく制御できます。TTL は低い優先度で実行され、増分クリーンアップに最適です。ただし、Spanner の内部作業スケジューラはユーザークエリなどの他の作業を優先するため、ビジー状態のデータベースで最初の行セットを削除するための時間が長くなる可能性があります。
条件を確認する
Google SQL テーブルでは、TTL を有効にする前に行削除ポリシーが影響を受けるデータを確認する場合、同じ条件を使用してテーブルに対してクエリを実行できます。例:
Google SQL
SELECT COUNT(*)
FROM CalculatedRoutes
WHERE TIMESTAMP_ADD(CreatedAt, INTERVAL 30 DAY) < CURRENT_TIMESTAMP();
必要な権限
データベースのスキーマを変更するには、spanner.databases.updateDdl 権限が必要です。詳細については、Spanner のアクセス制御をご覧ください。
行削除ポリシーを作成する
Google SQL
Google SQL を使用して行削除ポリシーを作成するには、新しいテーブルを作成するときに ROW DELETION POLICY
句を定義するか、既存のテーブルにポリシーを追加します。この句には列と間隔の式が含まれます。
テーブルの作成時にポリシーを追加するには:
CREATE TABLE MyTable( Key INT64, CreatedAt TIMESTAMP, ) PRIMARY KEY (Key), ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));
ここで
timestamp_column
は、TIMESTAMP
型の既存の列にする必要があります。commit タイムスタンプがある列と生成された列は有効です。ただし、commit タイムスタンプ列を参照する生成された列は指定できません。num_days
は、行が削除対象としてマークされているtimestamp_column
のタイムスタンプから経過した日数です。この値は、マイナスでない整数にする必要があります。単位はDAY
のみサポートされています。
既存のテーブルにポリシーを追加するには、ALTER TABLE
ステートメントを使用します。1 つのテーブルには、行の削除ポリシーを最大 1 つ含めることができます。ポリシーが存在するテーブルに行削除ポリシーを追加すると、エラーが発生します。より高度な行削除ロジックを指定するには、生成された列の TTL をご覧ください。
ALTER TABLE Albums ADD ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));
PostgreSQL
PostgreSQL を使用して行削除ポリシーを作成するには、新しいテーブルを作成するときに TTL INTERVAL
句を定義するか、既存のテーブルにポリシーを追加します。
テーブルの作成時にポリシーを追加するには:
CREATE TABLE mytable ( key bigint NOT NULL, timestamp_column_name TIMESTAMPTZ, PRIMARY KEY(key) ) TTL INTERVAL interval_spec ON timestamp_column_name;
ここで
timestamp_column_name
は、データ型TIMESTAMPTZ
の列である必要があります。この列は、CREATE TABLE
ステートメントで作成する必要があります。commit タイムスタンプがある列と生成された列は有効です。ただし、commit タイムスタンプ列を参照する生成された列は指定できません。interval_spec
は、行が削除対象としてマークされているtimestamp_column_name
のタイムスタンプから経過した日数です。この値は、日数を表す負でない整数にする必要があります。たとえば、'3 days'
は許されますが、'3 days - 2 minutes'
はエラーを返します。
既存のテーブルにポリシーを追加するには、ALTER TABLE
ステートメントを使用します。1 つのテーブルには、TTL ポリシーを最大 1 つ設定できます。ポリシーがすでに存在するテーブルに TTL ポリシーを追加すると、エラーが発生します。より高度な TTL ロジックを指定するには、生成された列の TTL をご覧ください。
既存のテーブルにポリシーを追加するには:
ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;
ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;
制限事項
以下には行削除ポリシーを作成することはできません。
- 外部キーで参照されるテーブル。
- 外部キーで参照されるテーブルの親。
次の例では、Customers
テーブルに行削除ポリシーを追加できません。これは、Orders
テーブルの外部キーによって行削除ポリシーが参照されていて、お客様を削除すると、この制約に違反する可能性があるためです。
Google SQL
CREATE TABLE Customers (
CustomerID INT64,
CreatedAt TIMESTAMP
) PRIMARY KEY (CustomerID);
CREATE TABLE Orders (
OrderID INT64,
CustomerID INT64,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID)
PostgreSQL
CREATE TABLE customers (
customerid bigint NOT NULL,
CreatedAt timestamptz,
PRIMARY KEY(customerid)
);
CREATE TABLE orders (
orderid bigint NOT NULL,
customerid bigint,
PRIMARY KEY(orderid),
CONSTRAINT fk_customerorder FOREIGN KEY (customerid) REFERENCES customers (customerid)
);
デフォルト値が含まれる列に対する TTL
行削除ポリシーでは、デフォルト値があるタイムスタンプ列を使用できます。通常、デフォルト値は CURRENT_TIMESTAMP
です。列に明示的に値が割り当てられていない場合や、INSERT
または UPDATE
ステートメントによって列がデフォルト値に設定されている場合は、デフォルト値がルール計算で使用されます。
次の例で、テーブル Customers
にある列 CreatedAt
のデフォルト値は、行が作成されたタイムスタンプです。
Google SQL
CREATE TABLE Customers (
CustomerID INT64,
CreatedAt TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (CustomerID);
詳細については、「GoogleSQL データ定義言語」のデフォルト(式)をご覧ください。
PostgreSQL
CREATE TABLE customers (
customerid bigint NOT NULL,
createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(customerid)
);
詳細については、「PostgreSQL データ定義言語」の CREATE TABLE をご覧ください。
生成された列の TTL
行削除ポリシーで、生成された列を使用して、より高度なルールを表現できます。たとえば、複数の列の greatest
タイムスタンプ(Google SQL または PostgreSQL)に行削除ポリシーを定義することや、別の値をタイムスタンプにマッピングすることができます。
Google SQL
次のテーブル Orders
は、販売注文を追跡します。テーブル所有者は、キャンセルされた注文を 30 日後、キャンセルされていない注文を 180 日後に削除する行削除ポリシーを設定したいと考えています。
Spanner TTL では、テーブルあたり 1 つの行削除ポリシーのみが許可されます。2 つの条件を 1 つの列に表現するには、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));
ステートメントは、ExpiredDate
という名前の列を作成し、注文ステータスに応じて 30 日または 180 日を LastModifiedDate
に加算します。次に、INTERVAL 0 day
を指定し、ExpiredDate
列に保存された日付の行を期限切れにするように、行削除ポリシーを定義します。
PostgreSQL
次のテーブル Orders
は、販売注文を追跡します。テーブル オーナーは、30 日間の非アクティブ期間があった行を削除する行削除ポリシーを設定したいと考えています。
Spanner TTL では、テーブルあたり 1 つの行削除ポリシーのみが許可されます。2 つの条件を 1 つの列に表現するには、生成列を作成します。
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;
このステートメントは、2 つの日付(LastModifiedDate
または CreateDate
)の新しい方を求める ExpiredDate
という名前の生成列を作成します。次に、注文後 30 日で行を期限切れにするか、その 30 日以内に注文が変更された場合は削除の期限がさらに 30 日延長される行削除ポリシーを定義します。
TTL とインターリーブされたテーブル
インターリーブされたテーブルは、1 対多の子テーブルにある関連する行を親テーブルの行に関連付けるパフォーマンス最適化です。親テーブルに行削除ポリシーを追加するには、インターリーブされた子テーブルで ON DELETE CASCADE
を指定する必要があります。つまり、子行は親行とともにアトミックに削除されます。これにより、親テーブルに対する削除でも同じトランザクション内の関連する子行が削除されるように、参照整合性が確保されます。Spanner TTL は、ON DELETE NO ACTION
をサポートしません。
トランザクションの最大サイズ
Spanner には、トランザクション サイズの上限があります。インデックス付きの列がある大規模な親子階層での削除のカスケードは、こうした上限を超え、1 つ以上の TTL オペレーションが失敗する可能性があります。オペレーションが失敗した場合、TTL は 1 つの親行まで縮小したバッチで再試行します。ただし、単一の親行でも、大きな子階層がミューテーションの上限を超える場合があります。
失敗したオペレーションは、TTL 指標で報告されます。
1 つの行とそのインターリーブされた子が大きすぎて削除できない場合は、親テーブルのポリシーの他に、子テーブルにも行削除ポリシーを直接接続できます。子テーブルのポリシーは、親行より前に子行が削除されるように構成する必要があります。
次の 2 つのステートメントが適用される場合は、子テーブルに行削除ポリシーを付加することを検討してください。
- 子テーブルにはそれに関連付けられている任意のグローバル インデックスがあります。
- 親の行ごとに多数の(100 を超える)子の行が想定されます。
行削除ポリシーを削除する
テーブルから既存の行削除ポリシーを削除できます。テーブルに行削除ポリシーがない場合は、エラーが返されます。
Google SQL
ALTER TABLE MyTable
DROP ROW DELETION POLICY;
PostgreSQL
ALTER TABLE mytable
DROP TTL;
行削除ポリシーを削除すると、バックグラウンドで実行中の TTL プロセスが中止されます。進行中のプロセスですでに削除された行は、削除されたままになります。
行削除ポリシーによって参照されている列を削除する
Spanner では、行削除ポリシーによって参照されている列を削除できません。列を削除する前に、まず行削除ポリシーを削除する必要があります。
テーブルの行削除ポリシーを表示する
Spanner テーブルの行削除ポリシーを表示できます。
GoogleSQL
SELECT TABLE_NAME, ROW_DELETION_POLICY_EXPRESSION
FROM INFORMATION_SCHEMA.TABLES
WHERE ROW_DELETION_POLICY_EXPRESSION IS NOT NULL;
詳細については、Google SQL 言語データベースの情報スキーマをご覧ください。
PostgreSQL
SELECT table_name, row_deletion_policy_expression
FROM information_schema.tables
WHERE row_deletion_policy_expression is not null;
詳細については、PostgreSQL 言語データベースの情報スキーマをご覧ください。
行削除ポリシーを変更する
既存の行削除ポリシーの列または間隔式は変更できます。次の例では、列を CreatedAt
から ModifiedAt
に切り替え、間隔を 1 DAY
から 7 DAY
に拡張します。テーブルに行削除ポリシーがない場合は、エラーが返されます。
Google SQL
ALTER TABLE MyTable
REPLACE ROW DELETION POLICY (OLDER_THAN(ModifiedAt, INTERVAL 7 DAY));
PostgreSQL
ALTER TABLE mytable
ALTER TTL INTERVAL '7 days' ON timestampcolumn;