cyclist_category

Clustering column simple example with corresponding query.

// Find cyclists that fit a particular category

SOURCE 'keyspace-create-simple.cql';
SOURCE 'keyspace-use.cql';

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

DROP TABLE IF EXISTS cycling.cyclist_category;

// tag::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;

// tag::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');

// tag::rows[]
RESTRICT ROWS ON cycling.cyclist_category USING category;
// end::rows[]

CAPTURE 'select_all_from_cyclist_category_order_by_descending_points.results';
// tag::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

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

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

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

// tag::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';
// tag::select_all_rows[]
SELECT *
FROM cycling.cyclist_category;
// end::select_all_rows[]
CAPTURE OFF;

// tag::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');

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com