Advance Data Modeling

Advance_Data_Modeling.cql

DROP KEYSPACE mykeyspace ;
CREATE KEYSPACE mykeyspace WITH replication = {'class': 'NetworkTopologyStrategy', 'replication_factor' : 3} AND durable_writes = true;

USE mykeyspace;

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

INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:00+0000', 100);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:01+0000', 101);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:02+0000', 110);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:03+0000', 102);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:04+0000', 101);

INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (80d39c78-9dc0-11eb-a8b3-0242ac130003, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:00+0000', 120);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (80d39c78-9dc0-11eb-a8b3-0242ac130003, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:01+0000', 121);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (80d39c78-9dc0-11eb-a8b3-0242ac130003, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:02+0000', 120);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (80d39c78-9dc0-11eb-a8b3-0242ac130003, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:03+0000', 111);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (80d39c78-9dc0-11eb-a8b3-0242ac130003, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:04+0000', 125);

INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (92cf4f94-9dc0-11eb-a8b3-0242ac130003, b4a63c18-9dc0-11eb-a8b3-0242ac130003, '2021-05-01 01:00+0000', 70);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (92cf4f94-9dc0-11eb-a8b3-0242ac130003, b4a63c18-9dc0-11eb-a8b3-0242ac130003, '2021-05-01 01:01+0000', 80);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (92cf4f94-9dc0-11eb-a8b3-0242ac130003, b4a63c18-9dc0-11eb-a8b3-0242ac130003, '2021-05-01 01:02+0000', 77);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (92cf4f94-9dc0-11eb-a8b3-0242ac130003, b4a63c18-9dc0-11eb-a8b3-0242ac130003, '2021-05-01 01:03+0000', 79);
INSERT INTO heartrate_v10(pet_chip_id,owner,time,heart_rate) VALUES (92cf4f94-9dc0-11eb-a8b3-0242ac130003, b4a63c18-9dc0-11eb-a8b3-0242ac130003, '2021-05-01 01:04+0000', 70);

SELECT * FROM heartrate_v10 WHERE pet_chip_id = a2a60505-3e17-4ad4-8e1a-f11139caa1cc;
SELECT * FROM heartrate_v10 WHERE pet_chip_id = a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time >= '2021-05-01 01:01:00+0000' AND time <= '2021-05-01 01:03:00+0000';
SELECT * FROM heartrate_v10 WHERE owner = 642adfee-6ad9-4ca5-aa32-a72e506b8ad8;




SELECT * FROM heartrate_v10 LIMIT 3;

 pet_chip_id                          | time                            | heart_rate | owner
--------------------------------------+---------------------------------+------------+--------------------------------------
 80d39c78-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:00:00.000000+0000 |        120 | 642adfee-6ad9-4ca5-aa32-a72e506b8ad8
 80d39c78-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:01:00.000000+0000 |        121 | 642adfee-6ad9-4ca5-aa32-a72e506b8ad8
 80d39c78-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:02:00.000000+0000 |        120 | 642adfee-6ad9-4ca5-aa32-a72e506b8ad8

### MV

CREATE MATERIALIZED VIEW heartrate_by_owner AS
 SELECT * FROM heartrate_v10
 WHERE owner IS NOT NULL AND pet_chip_id IS NOT NULL AND time IS NOT NULL
 PRIMARY KEY(owner, pet_chip_id, time);


SELECT * FROM heartrate_by_owner WHERE owner = 642adfee-6ad9-4ca5-aa32-a72e506b8ad8;

 owner                                | pet_chip_id                          | time                            | heart_rate
--------------------------------------+--------------------------------------+---------------------------------+------------
 b4a63c18-9dc0-11eb-a8b3-0242ac130003 | 92cf4f94-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:00:00.000000+0000 |         70
 b4a63c18-9dc0-11eb-a8b3-0242ac130003 | 92cf4f94-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:01:00.000000+0000 |         80
 b4a63c18-9dc0-11eb-a8b3-0242ac130003 | 92cf4f94-9dc0-11eb-a8b3-0242ac130003 | 2021-05-01 01:02:00.000000+0000 |         77


CREATE MATERIALIZED VIEW heartrate_by_owner_subset AS
 SELECT * FROM heartrate_v10
 WHERE owner IS NOT NULL AND pet_chip_id IS NOT NULL AND time IS NOT NULL AND time > '2021-05-01 01:02+0000'
 PRIMARY KEY(owner, pet_chip_id, time);

### SI

CREATE INDEX pet_by_owner ON heartrate_v10 (owner);
SELECT * FROM heartrate_v10 WHERE owner = 642adfee-6ad9-4ca5-aa32-a72e506b8ad8;

### Counters

CREATE TABLE poop_num (pet_chip_id UUID PRIMARY KEY, poops counter);
CREATE TABLE poop_num_v2 (pet_chip_id UUID PRIMARY KEY, poops counter, play counter);


UPDATE poop_num SET poops = poops + 1 WHERE pet_chip_id = 92cf4f94-9dc0-11eb-a8b3-0242ac130003;
SELECT * FROM poop_num;

### Collections

CREATE TABLE heartrate_v11 (
   pet_chip_id uuid,
   owner uuid,
   time timestamp,
   heart_rate int,
   legs_on_the_ground set<text>,
   barks list<text>,
   pos map<text, int>,
   PRIMARY KEY (pet_chip_id, time)
);


INSERT INTO heartrate_v11(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:00+0000', 100);
INSERT INTO heartrate_v11(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:01+0000', 101);
INSERT INTO heartrate_v11(pet_chip_id,owner,time,heart_rate) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:02+0000', 110);

INSERT INTO heartrate_v11(pet_chip_id,owner,time,heart_rate, legs_on_the_ground) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:20+0000', 111, {'front-left'});

UPDATE heartrate_v11 SET legs_on_the_ground = legs_on_the_ground + {'front-right'} WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';

UPDATE heartrate_v11 SET barks = ['woof'] + barks WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';
UPDATE heartrate_v11 SET barks = ['wif', 'waf'] + barks WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';
UPDATE heartrate_v11 SET barks = barks + ['meow'] WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';
select barks from heartrate_v11 WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';


UPDATE heartrate_v11 SET pos = {'latitude':17, 'longitude':18} WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';
UPDATE heartrate_v11 SET pos = pos + {'altitude':100} WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';

### UDT

CREATE TYPE geo (
      altitude int,
      latitude int,
      longitude int);


CREATE TABLE heartrate_v12 (
   pet_chip_id uuid,
   owner uuid,
   time timestamp,
   heart_rate int,
   pos geo,
   PRIMARY KEY (pet_chip_id, time)
);

INSERT INTO heartrate_v12(pet_chip_id,owner,time,heart_rate, pos) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cc, 642adfee-6ad9-4ca5-aa32-a72e506b8ad8, '2021-05-01 01:00+0000', 100, {altitude: 30, latitude: 31, longitude: 22});
INSERT INTO heartrate_v12(pet_chip_id,time, pos) VALUES (a2a60505-3e17-4ad4-8e1a-f11139caa1cd, '2021-05-01 01:00+0000', {altitude: 30, latitude: 31, longitude: 100});

UPDATE heartrate_v12 SET pos =  {altitude: 30, latitude: 31, longitude: 22} WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';
UPDATE heartrate_v12 SET pos =  {altitude: 30, latitude: 31, longitude: 22} WHERE pet_chip_id=a2a60505-3e17-4ad4-8e1a-f11139caa1cc AND time = '2021-05-01 01:00+0000';

### TTL

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

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);

### TTL on a row
INSERT INTO heartrate_ttl(pet_chip_id, time, heart_rate) VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:04:00', 131) USING TTL 20;

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

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