Data modelling with Apache Cassandra

CREATE KEYSPACE movies
WITH replication = {
    'class' : 'SimpleStrategy',
    'replication_factor' : 2
};

DROP KEYSPACE movies;

USE movies

ALTER KEYSPACE movies
WITH REPLICATION =  {
     'class' : 'SimpleStrategy',
     'replication_factor' : 3
};

nodetool repair --full keyspace_name

CREATE TABLE my_table (
   col1 int,
   col2 text,
   col3 int,
   col4 int,
   col5 int STATIC,
   col6 timestamp,
   PRIMARY KEY ( (col1,col2), col3,col4))
   [WITH CLUSTERING ORDER BY (col3 DESC, col4 ASC)];

CREATE TABLE my_table (
   col1 int,
   col2 text,
   col3 int,
   col4 int,
   col5 int STATIC,
   col6 timestamp,
   PRIMARY KEY ( (col1,col2), col3,col4))
   [WITH CLUSTERING ORDER BY (col3 DESC, col4 ASC)];

CREATE TABLE my_table (
   col1 int,
   col2 text,
   col3 int,
   col4 int,
   col5 int STATIC,
   col6 timestamp,
   PRIMARY KEY ( (col1,col2), col3,col4))
   [WITH CLUSTERING ORDER BY (col3 DESC, col4 ASC)];

CREATE TABLE my_table (
   col1 int,
   col2 text,
   col3 int,
   col4 int,
   col5 int STATIC,
   col6 timestamp,
   PRIMARY KEY ( (col1,col2),col3,col4))
   [WITH CLUSTERING ORDER BY (col3 DESC, col4 ASC)];

CREATE TABLE my_table (
   col1 int,
   col2 text,
   col3 int,
   col4 int,
   col5 int STATIC,
   col6 timestamp,
   PRIMARY KEY ( (col1,col2),col3,col4))
   [WITH CLUSTERING ORDER BY (col3 DESC, col4 ASC)];

CREATE TABLE temperature (
    ville text,
    temperature int,
    PRIMARY KEY (ville)
);
INSERT INTO temperature (ville, temperature ) VALUES ( 'Paris', 30);
INSERT INTO temperature (ville, temperature ) VALUES ( 'Paris', 29);

SELECT * FROM temperature;
 ville  | temperature
--------+-------------
  Paris |          29

CREATE TABLE temperature (
    ville text,
    temperature int,
    PRIMARY KEY (ville)
);

INSERT INTO temperature (ville, temperature ) VALUES ( 'Rennes', 30);

SELECT * FROM temperature;

 ville  | temperature
--------+-------------
  Paris |          29
 Rennes |          30

> tools/bin/sstabledump mc-1-big-Data.db
{
  [
    { "partition" : {
      "key" : [ "Paris" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "row",
        "position" : 30,
        "liveness_info" : { "tstamp" : "2017-11-14T15:34:40.012400Z" },
        "cells" : [
          { "name" : "temperature", "value" : 29 }
        ]
      }
    ]
  },
  {
    "partition" : {
      "key" : [ "Rennes" ],
      "position" : 31
    },
    "rows" : [
      {
        "type" : "row",
        "position" : 62,
        "liveness_info" : { "tstamp" : "2017-11-14T15:34:40.039478Z" },
        "cells" : [
          { "name" : "temperature", "value" : 30 }
        ]
      }
    ]
  }

CREATE TABLE temperature_date (
    ville text,
    record_date text,
    temperature int,
    humidity int,
    PRIMARY KEY (ville, record_date)
) WITH CLUSTERING ORDER BY (record_date DESC) ;

INSERT INTO temperature_date (ville, record_date, temperature, ) VALUES ( 'Paris', '2017/11/14', 30);
INSERT INTO temperature_date (ville, record_date, temperature ) VALUES ( 'Paris', '2017/11/14', 29);
INSERT INTO temperature_date (ville, record_date, temperature ) VALUES ( 'Rennes', '2016/11/10', 40);
INSERT INTO temperature_date (ville, record_date, temperature ) VALUES ( 'Paris', '2017/11/15', 29);

cqlsh:temperature> SELECT * FROM temperature_date;

 ville  | record_date | temperature
--------+-------------+-------------
  Paris |  2017/11/15 |          29
  Paris |  2017/11/14 |          29
 Rennes |  2016/11/10 |          40



 cqlsh:temperature> SELECT * FROM temperature_date WHERE ville = 'Paris' LIMIT 2;

  ville | record_date | temperature
 -------+-------------+-------------
  Paris |  2017/11/15 |          29
  Paris |  2017/11/14 |          29

 (2 rows)

CREATE TABLE teammember_by_team (
  teamname text,
  manager text static,
  location text static,
  membername text,
  nationality text,
  position text,
  PRIMARY KEY ((teamname), membername)
);

CREATE TABLE teammember_by_team (
  teamname text,
  manager text static,
  location text static,
  membername text,
  nationality text,
  position text,
  PRIMARY KEY ((teamname), membername)
);

INSERT INTO teammember_by_team (teamname, manager, location)
VALUES ('Red Bull', 'Christian Horner', '<unknown>');

teamname  | membername | location | manager          | nationality | position
----------+------------+----------+------------------+-------------+----------
 Red Bull |       null | <unkown> | Christian Horner |        null |     null

> tools/bin/sstabledump mc-1-big-Data.db
[
  {
    "partition" : {
      "key" : [ "Red Bull" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "static_block",
        "position" : 64,
        "cells" : [
          { "name" : "location", "value" : "<unknown>", "tstamp" : "2018-11-26T16:57:37.374405Z" },
          { "name" : "manager", "value" : "Christian Horner", "tstamp" : "2018-11-26T16:57:37.374405Z" }
        ]
      }
    ]
  }
]

CREATE TABLE teammember_by_team (
  teamname text,
  manager text static,
  location text static,
  membername text,
  nationality text,
  position text,
  PRIMARY KEY ((teamname), membername)
);
INSERT INTO teammember_by_team (teamname, membername, nationality, position)
                      VALUES ('Red Bull', 'Ricciardo', 'Australian', 'driver');


select * from teammember_by_team;
 teamname | membername | location  | manager          | nationality | position
----------+------------+-----------+------------------+-------------+----------
 Red Bull |  Ricciardo | <unknown> | Christian Horner |  Australian |   driver

> tools/bin/sstabledump mc-2-big-Data.db
[
  {
    "partition" : {
      "key" : [ "Red Bull" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "row",
        "position" : 57,
        "clustering" : [ "Ricciardo" ],
        "liveness_info" : { "tstamp" : "2018-11-26T17:06:36.807957Z" },
        "cells" : [
          { "name" : "nationality", "value" : "Australian" },
          { "name" : "position", "value" : "driver" }
        ]
      }
    ]
  }

>tools/bin/sstabledump mc-3-big-Data.db
[
  {
    "partition" : {
      "key" : [ "Red Bull" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "static_block",
        "position" : 96,
        "cells" : [
          { "name" : "location", "value" : "<unknown>", "tstamp" : "2018-11-26T16:57:37.374405Z" },
          { "name" : "manager", "value" : "Christian Horner", "tstamp" : "2018-11-26T16:57:37.374405Z" }
        ]
      },
      {
        "type" : "row",
        "position" : 96,
        "clustering" : [ "Ricciardo" ],
        "liveness_info" : { "tstamp" : "2018-11-26T17:06:36.807957Z" },
        "cells" : [
          { "name" : "nationality", "value" : "Australian" },
          { "name" : "position", "value" : "driver" }
        ]
      }
    ]
  }

CREATE TABLE teammember_by_team (
  teamname text,
  manager text static,
  location text static,
  membername text, nationality text, position text,
  PRIMARY KEY ((teamname), membername)
);
INSERT INTO teammember_by_team (teamname, membername, nationality, position) VALUES ('Red Bull', 'Ricciardo', 'Australian', 'driver');
INSERT INTO teammember_by_team (teamname, membername, nationality, position) VALUES ('Red Bull', 'Kvyat', 'Russian', 'driver');

                              teamname | membername | location | manager          | nationality | position
                             ----------+------------+----------+------------------+-------------+----------
                              Red Bull |      Kvyat |<location>| Christian Horner |     Russian |   driver
                              Red Bull |  Ricciardo |<location>| Christian Horner |  Australian |   driver

INSERT INTO teammember_by_team (teamname, membername, nationality, location) VALUES ('Red Bull', 'Grosjean', 'French', 'FR');

         teamname | membername | location | manager          | nationality | position
        ----------+------------+----------+------------------+-------------+----------
         Red Bull |   Grosjean |       FR | Christian Horner |      French |     null
         Red Bull |      Kvyat |       FR | Christian Horner |     Russian |   driver
         Red Bull |  Ricciardo |       FR | Christian Horner |  Australian |   driver

  {
    "partition" : {
      "key" : [ "Red Bull" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "static_block",
        "position" : 87,
        "cells" : [
          { "name" : "location", "value" : "FR", "tstamp" : "2017-11-15T01:53:29.914293Z" },
          { "name" : "manager", "value" : "Christian Horner", "tstamp" : "2017-11-14T20:04:07.780008Z" }
        ]
      },
      {
        "type" : "row",
        "position" : 87,
        "clustering" : [ "Grosjean" ],
        "liveness_info" : { "tstamp" : "2017-11-15T01:53:29.914293Z" },
        "cells" : [
          { "name" : "nationality", "value" : "French" }
        ]
      },
      {
        "type" : "row",
        "position" : 87,
        "clustering" : [ "Kvyat" ],
        "liveness_info" : { "tstamp" : "2017-11-15T01:51:53.491421Z" },
        "cells" : [
          { "name" : "nationality", "value" : "Russian" },
          { "name" : "position", "value" : "driver", "tstamp" : "2017-11-14T21:35:44.148500Z" }
        ]
      },
      {
        "type" : "row",
        "position" : 124,
        "clustering" : [ "Ricciardo" ],
        "liveness_info" : { "tstamp" : "2017-11-14T21:35:40.564459Z" },
        "cells" : [
          { "name" : "nationality", "value" : "Australian" },
          { "name" : "position", "value" : "driver" }
        ]
      }
    ]
  }

Node1: INSERT INTO teammember_by_team (teamname, manager, location)
VALUES ('Red Bull', 'Christian Horner', '<unknown>') USING TIMESTAMP;

Node1: INSERT INTO teammember_by_team (teamname, manager, location)
VALUES ('Red Bull', 'Christian Horner', '<unknown>');

Node1: INSERT INTO teammember_by_team (teamname, manager, location)
VALUES ('Red Bull', 'Christian Horner', '<unknown>');


Node2: INSERT INTO teammember_by_team (teamname, manager, location)
VALUES ('Red Bull', 'Patrick Stewart', 'Milton-Keys');

Node3: DELETE location FROM teammember_by_team WHERE teamname='Red Bull'

[
  {
    "partition" : {
      "key" : [ "Red Bull" ],
      "position" : 0
    },
    "rows" : [
      {
        "type" : "static_block",
        "position" : 91,
        "cells" : [
          { "name" : "location", "deletion_info" : { "local_delete_time" : "2018-11-26T17:21:43Z" },
            "tstamp" : "2018-11-26T17:21:43.283089Z"
          },
          { "name" : "manager", "value" : "Christian Horner", "tstamp" : "2018-11-26T16:57:37.374405Z" }
        ]
      },
      {
        "type" : "row",
......................................

INSERT INTO [keyspace_name.] table_name (column_list)
VALUES (column_values)
[IF NOT EXISTS]
[USING TTL seconds | TIMESTAMP epoch_in_microseconds]

CREATE TABLE ratings_by_user (
    user_id text,
    movie_id text,
    name text,
    rating int,
    ts int,
    PRIMARY KEY (user_id, movie_id)
)

INSERT INTO ratings_by_user (user_id , movie_id , name , rating , ts )
VALUES ( 'uuid1','uuid2','Starwars',4,3); -- OK

INSERT INTO ratings_by_user (user_id, movie_id) VALUES ('2323..','2442..'); -- OK !!!


INSERT INTO ratings_by_user (user_id) VALUES ('2323..'); -- KO !!!

INSERT INTO cyclist_career_teams (id,lastname,teams)
    VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS',
  { 'Rabobank-Liv Woman Cycling Team','Rabobank-Liv Giant','Rabobank Women Team','Nederland bloeit' } ); (1)

-- ADD ELEMENT
UPDATE cyclist_career_teams
    SET teams = teams + {'Team DSB - Ballast Nedam'} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2; (2)

-- REMOVE ELEMENT
UPDATE cyclist_career_teams
    SET teams = teams - {'WOMBATS - Womens Mountain Bike & Tea Society'} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2; (3)

-- DELETE
UPDATE cyclist.cyclist_career_teams SET teams = {} WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2; (4)

DELETE teams FROM cycling.cyclist_career_teams WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2; (5)

INSERT INTO upcoming_calendar (year, month, events)
VALUES (2015, 06, ['Criterium du Dauphine','Tour de Suisse']);

-- ADD ELEMENT
UPDATE upcoming_calendar
SET events = ['The Parx Casino Philly Cycling Classic'] + events WHERE year = 2015 AND month = 06;


-- ADD ELEMENT + SHIFT
 UPDATE upcoming_calendar SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2015 AND month = 06;

COPY table1(col1, col2,...) FROM 'users.csv';

COPY table1(col1, col2,...) FROM 'users.csv'
WITH HEADER=true;

BEGIN [UNLOGGED | LOGGED] BATCH
[USING TIMESTAMP [epoch_microseconds]]
   dml_statement [USING TIMESTAMP [epoch_microseconds]];
   [dml_statement; ...]
APPLY BATCH;

CREATE TABLE popular_count (
  id UUID PRIMARY KEY,
  popularity counter);

UPDATE cycling.popular_count
 SET popularity = popularity + 1
 WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

 CREATE TYPE cycling.basic_info (
  birthday timestamp,
  nationality text,
  weight text,
  height text
);

CREATE TABLE cycling.cyclist_stats (
            id uuid PRIMARY KEY, lastname text, basics FROZEN<basic_info>);

CREATE TABLE users (
   user_id int,
   user_name text,
   user_age int,
   PRIMARY KEY (user_id));

SELECT * FROM users WHERE user_age = 22;

CREATE TABLE teammember_by_team (
  teamname text,
  manager text static,
  location text static,
  membername text, nationality text, position text,
  PRIMARY KEY ((teamname), membername)
);
SELECT * FROM teammember_by_team WHERE teamname='Red Bull'; --OK
SELECT * FROM teammember_by_team WHERE teamname='Red Bull' AND membername='Kyvat'; --OK

SELECT * FROM teammember_by_team WHERE membername='Kyvat'; --KO
SELECT * FROM teammember_by_team WHERE position='driver'; --KO
SELECT * FROM teammember_by_team WHERE manager='Christian Horner'; --KO

CREATE TABLE cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);

SELECT * FROM cycling.rank_by_year_and_name WHERE race_year=2015 AND race_name='TJI'; -- OK (both race_name and race_year)

SELECT * FROM cycling.rank_by_year_and_name WHERE race_year=2015; -- KO

CREATE INDEX ryear ON cycling.rank_by_year_and_name (race_year);
SELECT * FROM cycling.rank_by_year_and_name WHERE race_year=2015;

CREATE TABLE cc_transactions (
    userid text,
    year int,
    month int,
    day int,
    id int,
    amount int,
    card text,
    status text,
    PRIMARY KEY ((userid, year), month, day, id)
);

CREATE MATERIALIZED VIEW transactions_by_day AS
    SELECT year, month, day, userid, id, amount, card, status
    FROM cc_transactions
    WHERE userid IS NOT NULL AND year IS NOT NULL AND month IS NOT NULL AND day IS NOT NULL AND id IS NOT NULL AND card IS NOT NULL
    PRIMARY KEY ((year, month, day), userid, id);

// Q4:
CREATE TABLE attachments_by_email (id TIMEUUID, filename TEXT, type TEXT, value TEXT, PRIMARY KEY ((id,filename)));

/* SELECT type, value FROM attachments_by_email WHERE id=? and filename=?; */