cyclist_name

Simple single column partition key. Demonstrates inserting, updating, and deleting data.

// tag::keyspace-cycling-use
USE cycling;
// end::keyspace-cycling-use

// tag::table-cyclist-name-pk-at-start[]
CREATE TABLE cyclist_name (
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);
// end::table-cyclist-name-pk-at-start[]

// LLP: This result output works for pk-at-start, pk-at-end, name-keyspace-included, name-simple
CAPTURE 'table-cyclist-name-describe.result';
// tag::table-cyclist-name-describe[]
DESCRIBE TABLE cycling.cyclist_name;
// end::table-cyclist-name-describe[]
CAPTURE OFF;

// tag::table-cyclist-name-pk-at-end[]
CREATE TABLE cycling.cyclist_name (
  id UUID, 
  lastname text, 
  firstname text, 
  PRIMARY KEY (id)
);
// end::table-cyclist-name-pk-at-end[]

// tag::table-cyclist-name-keyspace-included[]
CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);
// end::table-cyclist-name-keyspace-included[]

// Create a table with a simple partition key
// tag::table-cyclist-name-simple[]
CREATE TABLE IF NOT EXISTS cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);
// end::table-cyclist-name-simple[]

// tag::table-cyclist-name-insert-uuid[]
INSERT INTO cycling.cyclist_name (
  id
) VALUES (
  uuid()
);
// end::table-cyclist-name-insert-uuid[]

// tag::table-cyclist-name-insert-batch[]
BEGIN BATCH

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    6d5f1663-89c0-45fc-8cfd-60a373b01622,'HOSKINS', 'Melissa'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    38ab64b6-26cc-4de9-ab28-c257cf011659,'FERNANDES', 'Marcia'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    9011d3be-d35c-4a8d-83f7-a3c543789ee7,'NIEWIADOMA', 'Katarzyna'
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    95addc4c-459e-4ed7-b4b5-472f19a67995,'ADRIAN', 'Vera'
  );

APPLY BATCH;
// end::table-cyclist-name-insert-batch[]

// Insert data with TTL and timestamp
// tag::table-cyclist-name-insert-ttl-and-timestamp[]
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
)
USING TTL 86400
AND TIMESTAMP 123456789;
// end::table-cyclist-name-insert-ttl-and-timestamp[]

// tag::table-cyclist-name-insert-if-not-exists[]
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
) 
IF NOT EXISTS; 
// end::table-cyclist-name-insert-if-not-exists[]

// tag::table-cyclist-name-create-sasi-index-fn-prefix[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_prefix
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';
// end::table-cyclist-name-create-sasi-index-fn-prefix[]

// tag::table-cyclist-name-drop-sasi-index-fn-prefix[]
DROP INDEX IF EXISTS fn_prefix;
// end::table-cyclist-name-drop-sasi-index-fn-prefix[]

// tag::table-cyclist-name-drop-sasi-index-fn-contains[]
DROP INDEX IF EXISTS fn_contains;
// end::table-cyclist-name-drop-sasi-index-fn-contains[]

// tag::table-cyclist-name-truncate[]
TRUNCATE cycling.cyclist_name;
// end::table-cyclist-name-truncate[]

// Insert 6 rows of cyclists
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

// View output
CAPTURE 'select_all_from_cycling_initial.results';
// tag::table-cyclist-name-select-initial[]
SELECT *
FROM cycling.cyclist_name;
// end::table-cyclist-name-select-initial[]
CAPTURE OFF;

CAPTURE 'select_marianne_from_cyclist_name.results';
// tag::table-cyclist-name-select-Marianne[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne';
// end::table-cyclist-name-select-Marianne
CAPTURE OFF;

CAPTURE 'select_like_M_from_cyclist_name.results';
// tag::table-cyclist-name-select-like-M-start[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE 'M%';
// end::table-cyclist-name-select-like-M-start[]
CAPTURE OFF;

// tag::table-cyclist-name-select-no-match-upper[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MARIANNE';
// end::table-cyclist-name-select-no-match-upper[]

// tag::table-cyclist-name-select-no-match-lower[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
// end::table-cyclist-name-select-no-match-lower[]

// tag::table-cyclist-name-select-no-match-equality[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname = '%M%';
SELECT * FROM cycling.cyclist_name WHERE firstname = 'm%';
// end::table-cyclist-name-select-no-match-equality[]

// tag::table-cyclist-name-create-index-fn-contains[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_contains
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'CONTAINS' };
// end::table-cyclist-name-create-index-fn-contains[]

// These examples generate an error if they are run before the fn_contains index
// is created
// tag::table-cyclist-name-select-like[]
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M%';
// end::table-cyclist-name-select-like[]

CAPTURE 'select_marianne_allow_filtering_from_cyclist_name.results';
// tag::select_marianne_allow_filtering[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne'
ALLOW FILTERING;
// end::select_marianne_allow_filtering[]
CAPTURE OFF;

CAPTURE 'select_like_M_partial_from_cyclist_name.results';
// tag::select_like_M_partial[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%M%';
// end::select_like_M_partial[]
CAPTURE OFF;

CAPTURE 'select_arianne_from_cyclist_name.results';
// tag::select_arianne[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%arianne';
// end::select_arianne[]
CAPTURE OFF;

CAPTURE 'select_arian_from_cyclist_name.results';
// tag::select_arian_name[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%arian%';
// end::select_arian_name[]
CAPTURE OFF;

CAPTURE 'select_mar_from_cyclist_name.results';
// tag::select_greater_Mar[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname > 'Mar'
ALLOW FILTERING;
// end::select_greater_Mar[]
CAPTURE OFF;

// These examples do not return a match when they are run before the
// fn_suffix_allcase index is created
// tag::no_match_partial[]
SELECT * FROM cycling.cyclist_name WHERE firstname = 'MariAnne' ALLOW FILTERING;
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%m%';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE '%M';
SELECT * FROM cycling.cyclist_name WHERE firstname LIKE 'm%';
// end::no_match_partial[]

DROP INDEX IF EXISTS fn_suffix_allcase;

// tag::fn_suffix_allcase[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_suffix_allcase
ON cycling.cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
  'case_sensitive': 'false'
};
// end::fn_suffix_allcase[]

CAPTURE 'select_like_m_lower_from_cyclist_name.results';
// tag::select_like_m_lower[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname LIKE '%m%';
// end::select_like_m_lower[]
CAPTURE OFF;

// tag::alter[]
ALTER TABLE cycling.cyclist_name
ADD age int;

UPDATE cycling.cyclist_name
SET age = 23
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

INSERT INTO cycling.cyclist_name (
  id, age, firstname, lastname
) VALUES (
  8566eb59-07df-43b1-a21b-666a3c08c08a, 18, 'Marianne', 'DAAE'
);
// end::alter[]

CAPTURE 'select_name_and_age_filter_from_cyclist_name.results';
// tag::select_name_and_age_filter[]
SELECT *
FROM cycling.cyclist_name
WHERE firstname = 'Marianne'
  AND age > 20
ALLOW FILTERING;
// end::select_name_and_age_filter[]
CAPTURE OFF;

// Undo the changes in the alter section
DELETE FROM cycling.cyclist_name
WHERE id = 8566eb59-07df-43b1-a21b-666a3c08c08a;
ALTER TABLE cycling.cyclist_name
DROP age;

// Delete data in specified columns from a row, changes cyclist Alex Frame to null in name columns
// tag::deletecolumndata[]
DELETE firstname, lastname
FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
// end::deletecolumndata[]

// View output
CAPTURE 'cyclist_name_delete_firstname_and_lastname.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete an entire row, deletes cyclist Alex Frame altogether
CAPTURE 'cyclist_name_delete_applied_true.results';
// tag::deleteentirerow[]
DELETE FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
IF EXISTS;
// end::deleteentirerow[]
CAPTURE OFF;

// View output
CAPTURE 'cyclist_name_delete_entire_row.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete a row based on a static column condition, attempts to delete cyclist Michael Matthews but lastname does not match 
CAPTURE 'cyclist_name_delete_condition.results';
// tag::deletecondition[]
DELETE FROM cycling.cyclist_name 
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF firstname = 'Michael'
  AND lastname = 'Smith';
// end::deletecondition[]
CAPTURE OFF;

// View output
SELECT *
FROM cycling.cyclist_name;

// Delete a row with a WHERE clause, deletes cyclist Steven Kruikswijk
// tag::deleterowhere[]
DELETE FROM cycling.cyclist_name 
WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;
// end::deleterowhere[]

// View output
CAPTURE 'cyclist_name_delete_row_where.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// Delete rows using an IN clause on a primary key, deletes cyclists Marianne and Paolo
// tag::deletein[]
DELETE FROM cycling.cyclist_name 
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 220844bf-4860-49d6-9a4b-6b5d3a79cbfb
);
// end::deletein[]

// View output
CAPTURE 'cyclist_name_delete_in.results';
SELECT *
FROM cycling.cyclist_name;
CAPTURE OFF;

// tag::delete_lastname[]
DELETE lastname
FROM cycling.cyclist_name
WHERE id = c7fceba0-c141-4207-9494-a29f9809de6f;
// end::delete_lastname[]

// Reinsert data 
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 'VOS','Marianne');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7cd5752-bc0d-4157-a80f-7523add8dbcd, 'VAN DER BREGGEN','Anna');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME','Alex');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (220844bf-4860-49d6-9a4b-6b5d3a79cbfb, 'TIRALONGO','Paolo');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK','Steven');
INSERT INTO cycling.cyclist_name (id, lastname, firstname) VALUES (fb372533-eb95-4bb4-8685-6ef61e994caa, 'MATTHEWS', 'Michael');

// tag::comment[]
ALTER TABLE cycling.cyclist_name
ADD comment text;
// end::comment[]

// tag::update_set_comment[]
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name
SET comment = 'Rides fast, does not get along with others, a real dude'
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
// end::update_set_comment[]

DROP INDEX IF EXISTS cycling.stdanalyzer_idx;

// tag::stdanalyzer_idx[]
CREATE CUSTOM INDEX IF NOT EXISTS stdanalyzer_idx
ON cycling.cyclist_name (comment) USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
  'mode': 'CONTAINS',
  'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
  'analyzed': 'true',
  'tokenization_skip_stop_words': 'and, the, or',
  'tokenization_enable_stemming': 'true',
  'tokenization_normalize_lowercase': 'true',
  'tokenization_locale': 'en'
};
// end::stdanalyzer_idx[]

SOURCE 'cyclist_name-queries.cql';

// Undo UPDATE statements
UPDATE cycling.cyclist_name
SET comment = NULL
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa;

UPDATE cycling.cyclist_name
SET comment = NULL
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

// tag::update[]
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF EXISTS;
// end::update[]

// tag::multcolumns[]
UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;
// end::multcolumns[]

// tag::records[]
UPDATE cycling.cyclist_name
SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  fb372533-eb95-4bb4-8685-6ef61e994caa
);
// end::records[]

// tag::upsert[]
UPDATE cycling.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN' 
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;
// end::upsert[]

// tag::if[]
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF comment = NULL;
// end::if[]

// tag::fail[]
UPDATE cycling.cyclist_name
SET comment = 'Rides hard, gets along with others, a real winner'
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994cac 
IF comment = 'Rides hard, gets along with others, a real winner';
// end::fail[]

// tag::count_lastname[]
SELECT COUNT(lastname)
FROM cycling.cyclist_name;
// end::count_lastname[]

// tag::count_rows[]
SELECT COUNT(*)
FROM cycling.cyclist_name;
// end::count_rows[]

// tag::insert_Roxxane[]
INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  4647f6d3-7bd2-4085-8d6c-1229351b5498, 'KNETEMANN', 'Roxxane'
)
IF NOT EXISTS;
// end::insert_Roxxane[]

// tag::update_Roxxane[]
UPDATE cycling.cyclist_name
SET firstname = 'Roxane'
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498
IF firstname = 'Roxxane';
// end::update_Roxxane[]

DELETE FROM cycling.cyclist_name
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498;

// tag::table-cyclist-name-drop[]
DROP TABLE IF EXISTS cycling.cyclist_name;
// end::table-cyclist-name-drop[]

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