Code used for this video: https://youtu.be/SXkbu7mFHeA

scylla-ttl.cql

CREATE KEYSPACE ttl_example WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor': 1};

USE ttl_example;


-----------------------------------------------------------
-- BASIC USAGE
-----------------------------------------------------------

-- Set deafult TTL with CREATE TABLE

CREATE TABLE heartrate_ttl (
 pet_chip_id  uuid,
 time timestamp,
 heart_rate int,
 PRIMARY KEY (pet_chip_id, time)
) WITH default_time_to_live = 60;

-- Insert sample data

BEGIN BATCH
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate) 
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:00', 103); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:03:00', 130); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:04:00', 131);
APPLY BATCH;


-- Read current TTL

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate)
FROM heartrate_ttl;



-- Insert sample data

BEGIN BATCH
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate) 
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:00', 103); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:03:00', 130); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:04:00', 131);
APPLY BATCH;


-- Set TTL with ALTER TABLE (modify default TTL)

ALTER TABLE heartrate_ttl WITH default_time_to_live = 600;

-- TTL on existing rows did NOT change

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate)
FROM heartrate_ttl;

-- new rows will have the new TTL

INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
	VALUES (236e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:05:00', 103); 

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate)
FROM heartrate_ttl;






-----------------------------------------------------------
-- setting TTL during UPDATE
-----------------------------------------------------------


CREATE TABLE heartrate_ttl2 (
 pet_chip_id  uuid,
 time timestamp,
 heart_rate int,
 PRIMARY KEY (pet_chip_id, time)
);

-- Insert sample data

BEGIN BATCH
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate) 
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:00', 103); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:03:00', 130); 
	INSERT INTO heartrate_ttl (pet_chip_id, time, heart_rate)
		VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:04:00', 131);
APPLY BATCH;



-- Set TTL with UPDATE

UPDATE heartrate_ttl2 USING TTL 2000 
SET heart_rate = 131 
WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND time = '2019-03-04 07:04:00';


-- See current TTL value

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate) from heartrate_ttl2;




-- another UPDATE, without TTL

UPDATE heartrate_ttl2
SET heart_rate = 110
WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23 AND time = '2019-03-04 07:04:00';


-- See current TTL value

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate) from heartrate_ttl2;

-----------------------------------------------------------
-- INSERT
-----------------------------------------------------------

-- Set TTL with INSERT

INSERT INTO heartrate_ttl2 (pet_chip_id, time, heart_rate)
VALUES (452e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:05:00', 131) USING TTL 3000;

SELECT pet_chip_id, time, heart_rate, TTL(heart_rate)
FROM heartrate_ttl2
WHERE pet_chip_id = 452e4567-e89b-12d3-a456-426655440b23




-----------------------------------------------------------
-- TTL PER CELL / COLUMN
-----------------------------------------------------------

DROP TABLE ttl_per_cell;

CREATE TABLE ttl_per_cell (
 pet_chip_id  uuid,
 time timestamp,
 heart_rate int,
 steps int,
 PRIMARY KEY (pet_chip_id, time)
);

INSERT INTO ttl_per_cell (pet_chip_id, time, heart_rate)
VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 100) USING TTL 500; 

SELECT pet_chip_id, ttl(heart_rate), ttl(steps) FROM ttl_per_cell;

INSERT INTO ttl_per_cell (pet_chip_id, time, steps)
VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00', 500) USING TTL 800; 

SELECT pet_chip_id, ttl(heart_rate), ttl(steps) FROM ttl_per_cell;

SELECT pet_chip_id, heart_rate, steps FROM ttl_per_cell;


-----------------------------------------------------------
-- Collections + TTL
-----------------------------------------------------------

-- frozen

DROP TABLE collection_ttl;

CREATE TABLE collection_ttl (
 pet_chip_id  uuid,
 nicknames frozen<list<text>>,
 PRIMARY KEY (pet_chip_id)
) WITH default_time_to_live = 10;

INSERT INTO collection_ttl (pet_chip_id, nicknames)
VALUES (uuid(), ['Buddy', 'Champ', 'Chief']);


SELECT pet_chip_id, ttl(nicknames) FROM collection_ttl;


INSERT INTO collection_ttl (pet_chip_id, nicknames)
VALUES (uuid(), ['Buddy', 'Champ', 'Chief']) USING TTL 100;

SELECT pet_chip_id, ttl(nicknames) FROM collection_ttl;


-- non-frozen collections

DROP TABLE collection_ttl2;

CREATE TABLE collection_ttl2 (
 pet_chip_id  uuid,
 nicknames list<text>,
 PRIMARY KEY (pet_chip_id)
) WITH default_time_to_live = 10;


INSERT INTO collection_ttl2 (pet_chip_id, nicknames)
VALUES (11111111-1111-1111-1111-111111111111, ['Buddy', 'Champ', 'Chief']);

UPDATE collection_ttl2 USING TTL 100
SET nicknames = nicknames + ['Doggo']
WHERE pet_chip_id = 11111111-1111-1111-1111-111111111111;


SELECT pet_chip_id, nicknames FROM collection_ttl2;