cyclist_category

Clustering column simple example with corresponding query.

// Find cyclists that fit a particular category

SOURCE '0_create_keyspace.cql';

// CREATE TABLE CLUSTERING ORDER, PRIMARY KEY: PARTITION KEY + 1 CLUSTERING COLUMN, SIMPLE WHERE QUERY

DROP TABLE IF EXISTS cycling.cyclist_category;

// START-use_and_cpartitionkey
USE cycling;
CREATE TABLE IF NOT EXISTS cyclist_category ( 
  category text, 
  points int, 
  id UUID, 
  lastname text,     
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
// END-use_and_cpartitionkey

DROP TABLE IF EXISTS cycling.cyclist_category;

// START-cpartitionkey
CREATE TABLE IF NOT EXISTS cycling.cyclist_category (
  category text,
  points int,
  id UUID,
  lastname text,
  PRIMARY KEY (category, points)
)
WITH CLUSTERING ORDER BY (points DESC);
// END-cpartitionkey

INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('GC',1269,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('One-day-races',367,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Time-trial',182,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Sprint',0,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('GC',1324,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('One-day-races',198,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Sprint',39,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Time-trial',3,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');

// START-rows
RESTRICT ROWS ON cycling.cyclist_category USING category;
// END-rows

CAPTURE 'select_all_from_cyclist_category_order_by_descending_points.results';
// START-select_compound_primary_key
SELECT *
FROM cycling.cyclist_category
WHERE category = 'Time-trial'
ORDER BY points DESC;
// END-select_compound_primary_key
CAPTURE OFF;

// Add JSON rows

// START-insert_SUTHERLAND
INSERT INTO cycling.cyclist_category JSON '{
  "category" : "GC", 
  "points" : 780, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda",
  "lastname" : "SUTHERLAND"
}';
// END-insert_SUTHERLAND

// START-clear_lastname
INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint", 
  "points" : 780, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}';
// END-clear_lastname

// START-unset
INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint", 
  "points" : 780, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}'
DEFAULT UNSET;
// END-unset

// START-set_points
INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint", 
  "points" : 700, 
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}';
// END-set_points

CAPTURE 'select_all_from_cyclist_category.results';
// START-select_all_rows
SELECT *
FROM cycling.cyclist_category;
// END-select_all_rows
CAPTURE OFF;

// START-copy
COPY cycling.cyclist_category
FROM 'cyclist_category.csv'
WITH DELIMITER = '|'
  AND HEADER = TRUE;
// END-copy

// Truncate table
TRUNCATE TABLE cycling.cyclist_category;

// Re-insert data
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('GC',1269,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('One-day-races',367,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Time-trial',182,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Sprint',0,220844bf-4860-49d6-9a4b-6b5d3a79cbfb,'TIRALONGO');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('GC',1324,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('One-day-races',198,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Sprint',39,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');
INSERT INTO cycling.cyclist_category (category, points, id, lastname) VALUES ('Time-trial',3,6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47,'KRUIJSWIJK');