CQL for Cassandra 2.x

Expiring data

Data in a column, other than a counter column, can have an optional expiration date called TTL (time to live). The client request specifies a TTL value, defined in seconds, for the data. TTL data is marked with a tombstone after the requested amount of time has expired. A tombstone exists for gc_grace_seconds. After data is marked with a tombstone, the data is automatically removed during the normal compaction and repair processes.

Use CQL to set the TTL for data.

If you want to change the TTL of expiring data, you have to re-insert the data with a new TTL. In Cassandra, the insertion of data is actually an insertion or update operation, depending on whether or not a previous version of the data exists.

TTL data has a precision of one second, as calculated on the server. Therefore, a very small TTL probably does not make much sense. Moreover, the clocks on the servers should be synchronized; otherwise reduced precision could be observed because the expiration time is computed on the primary host that receives the initial insertion but is then interpreted by other hosts on the cluster.

Expiring data has an additional overhead of 8 bytes in memory and on disk (to record the TTL and expiration time) compared to standard data.

Expiring data example

Both the INSERT and UPDATE commands support setting a time for data in a column to expire. The expiration time (TTL) is set using CQL.

Procedure

  1. Use the INSERT command to set a password column in the users table to expire in 86400 seconds, or one day.
    cqlsh:demodb> INSERT INTO users
                    (user_name, password)
                    VALUES ('cbrown', 'ch@ngem4a') USING TTL 86400;
  2. Extend the expiration period to five days by using the UPDATE command/
    cqlsh:demodb> UPDATE users USING TTL 432000 SET password = 'ch@ngem4a'
                    WHERE user_name = 'cbrown';

Determining time-to-live for a column

This procedure creates a table, inserts data into two columns, and calls the TTL function to retrieve the date/time of the writes to the columns.

Procedure

  1. Create a users table named clicks in the excelsior keyspace.
    CREATE TABLE excelsior.clicks (
      userid uuid,
      url text,
      date timestamp,  //unrelated to WRITETIME discussed in the next section
      name text,
      PRIMARY KEY (userid, url)
    );
  2. Insert data into the table, including a date in yyyy-mm-dd format, and set that data to expire in a day (86400 seconds). Use the USING TTL clause to set the expiration period.
    INSERT INTO excelsior.clicks (
      userid, url, date, name)
      VALUES (
        3715e600-2eb0-11e2-81c1-0800200c9a66,
        'http://apache.org',
        '2013-10-09', 'Mary')
        USING TTL 86400;
  3. Wait for a while and then issue a SELECT statement to determine how much longer the data entered in step 2 has to live.
    SELECT TTL (name) from excelsior.clicks
      WHERE url = 'http://apache.org' ALLOW FILTERING;
    Output is, for example, 85908 seconds:
     ttl(name)
    -----------
     85908
                        
    (1 rows)
Show/hide