Configuring Time to Live (TTL)

This section contains recipes for configuration of table's TTL with YQL.

Enabling TTL for an existing table

In the example below, the items of the mytable table will be deleted an hour after the time set in the created_at column:

ALTER TABLE `mytable` SET (TTL = Interval("PT1H") ON created_at);

The example below shows how to use the modified_at column with a numeric type (Uint32) as a TTL column. The column value is interpreted as the number of seconds since the Unix epoch:

ALTER TABLE `mytable` SET (TTL = Interval("PT1H") ON modified_at AS SECONDS);

Enabling data eviction to S3-compatible external storage

In the following example, rows of the table mytable will be moved to the bucket described in the external data source /Root/s3_cold_data one hour after the time recorded in the column created_at and will be deleted after 24 hours:

ALTER TABLE `mytable` SET (
  TTL =
      Interval("PT1H") TO EXTERNAL DATA SOURCE `/Root/s3_cold_data`,
      Interval("PT24H") DELETE
  ON modified_at AS SECONDS
);

To enable data eviction, an external data source object that describes a connection to the external storage is needed.
In the example below, an external data source /Root/s3_cold_data is created. It describes a connection to bucket test_cold_data located in Yandex Object Storage with authorization by static access keys provided via secrets access_key and secret_key.

CREATE OBJECT access_key (TYPE SECRET) WITH (value="...");
CREATE OBJECT secret_key (TYPE SECRET) WITH (value="...");

CREATE EXTERNAL DATA SOURCE `/Root/s3_cold_data` WITH (
    SOURCE_TYPE="ObjectStorage",
    AUTH_METHOD="AWS",
    LOCATION="http://storage.yandexcloud.net/test_cold_data",
    AWS_ACCESS_KEY_ID_SECRET_NAME="access_key",
    AWS_SECRET_ACCESS_KEY_SECRET_NAME="secret_key",
    AWS_REGION="ru-central1"
)

Follow examples below to enable data eviction using an external data source.

In the following example, rows of the table mytable will be moved to the bucket described in the external data source /Root/s3_cold_data one hour after the time recorded in the column created_at and will be deleted after 24 hours:

ALTER TABLE `mytable` SET (
    TTL =
        Interval("PT1H") TO EXTERNAL DATA SOURCE `/Root/s3_cold_data`,
        Interval("PT24H") DELETE
    ON modified_at AS SECONDS
);

In the following example, rows of the table mytable will be moved to buckets /Root/s3_cold and /Root/s3_frozen one hour and 30 days respectively after the time recorded in the column created_at:

ALTER TABLE `mytable` SET (
    TTL =
        Interval("PT1H") TO EXTERNAL DATA SOURCE `/Root/s3_cold`,
        Interval("PT30D") TO EXTERNAL DATA SOURCE `/Root/s3_frozen`
    ON modified_at AS SECONDS
);


## Enabling TTL for a newly created table {#enable-for-new-table}

For a newly created table, you can pass TTL settings along with the table description:

```yql
CREATE TABLE `mytable` (
  id Uint64,
  expire_at Timestamp,
  PRIMARY KEY (id)
) WITH (
  TTL = Interval("PT0S") ON expire_at
);

Disabling TTL

ALTER TABLE `mytable` RESET (TTL);