CQL quick reference

Modifying table properties

Add a comment and set the bloom filter properties:

ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH comment = 'A most excellent and useful view'
AND bloom_filter_fp_chance = 0.02;

For an overview of properties that apply to materialized views, see table_options.

Modifying compression and compaction

ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH compression = { 
  'sstable_compression' : 'DeflateCompressor', 
  'chunk_length_kb' : 64 }
AND compaction = {
  'class': 'SizeTieredCompactionStrategy', 
  'max_threshold': 64};

Changing caching

You can create and change caching properties using a property map.

ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH caching = { 
  'keys' : 'NONE', 
  'rows_per_partition' : '15' };

Viewing current materialized view properties

DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age

CREATE MATERIALIZED VIEW cycling.cyclist_by_age AS
    SELECT age, cid, birthday, country, name
    FROM cycling.cyclist_base
    WHERE age IS NOT NULL AND cid IS NOT NULL
    PRIMARY KEY (age, cid)
    WITH CLUSTERING ORDER BY (cid ASC)
    AND bloom_filter_fp_chance = 0.02
    AND caching = {'keys': 'NONE', 'rows_per_partition': '15'}
    AND comment = 'A most excellent and useful view'
    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '64', 'min_threshold': '4'}
    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.DeflateCompressor'}
    AND crc_check_chance = 1.0
    AND default_time_to_live = 0
    AND gc_grace_seconds = 864000
    AND max_index_interval = 2048
    AND memtable_flush_period_in_ms = 0
    AND min_index_interval = 128
    AND speculative_retry = '99PERCENTILE';

Enable encryption on search index

Auto soft commit max time

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 10000;

RELOAD SEARCH INDEX
ON cycling.comments;

Request handler

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
ADD requestHandler[@name='/elevate',@class='solr.SearchHandler', @startup='lazy'] 
WITH  $$ {"defaults":[{"echoParams":"explicit"}],"last-components":["elevator"]} $$;

RELOAD SEARCH INDEX
ON cycling.comments;

<requestHandler name="/elevate" class="solr.SearchHandler"> 
   <lst name="defaults">
      <str name="echoParams">explicit</int>
   </lst>
   <arr name="last-components">
    <str>elevator</str>
   </arr>
</requestHandler>

Add a new field using the element path and attribute list

ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD fields.field[@name='fieldname', @type='StrField', @multiValued = 'false', @indexed='true'];

RELOAD SEARCH INDEX
ON cycling.comments;

Add a table column to the index

ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD FIELD record_id;

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Change a field name

ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@name = 'anotherFieldName';

To apply the schema changes:

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Change the field type

ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@type = 'UUIDField';

To apply the schema changes:

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Drop a field

ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
DROP field anotherFieldName;

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Set a field type and a text field

The first command sets the TextField type, which is required for the second command that sets the text field:

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET types.fieldType[@name='TextField']@class='org.apache.solr.schema.TextField';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET fields.field[@name='comment']@type='TextField';

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Drop a field and a field type

The first command drops the field from the search index and the second command drops the field type:

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP field comment;

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP types.fieldType[@name='TextField'];

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Add a field type and a dynamic field

The first command adds the TextField type, which must exist before the dynamic field is added by the second command:

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD types.fieldType[@class='org.apache.solr.schema.TextField', @name='TextField']
WITH '{"analyzer":{"class":"org.apache.lucene.analysis.standard.StandardAnalyzer"}}';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD dynamicField[@name='*fieldname', @type='TextField'];

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Adding a column

ALTER TABLE cycling.cyclist_races 
  ADD manager UUID;

ALTER TABLE cycling.cyclist_races 
  ADD completed list<text>;

This operation does not validate the existing data.

Dropping a column

ALTER TABLE cycling.cyclist_races 
  DROP manager;

DROP removes the column from the table definition. The column becomes unavailable for queries immediately after it is dropped. The database drops the column data during the next compaction.

Renaming a column

ALTER TABLE cycling.race_times 
  RENAME race_date TO date;

Modifying table properties

ALTER TABLE cycling.cyclist_base
WITH comment = 'basic cyclist information';

Enclose a text property value in single quotation marks.

Modifying compression and compaction

ALTER TABLE cycling.comments 
  WITH compression = { 
    'sstable_compression' : 'DeflateCompressor', 
    'chunk_length_kb' : 64 };

Enclose the name of each key in single quotes. If the value is a string, enclose the string in quotes as well.

Changing caching

ALTER TABLE cycling.comments 
  WITH caching = {
    'keys': 'NONE', 
    'rows_per_partition': 10 };

Changing speculative retries

ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '95percentile';

ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '10ms';

Enabling and disabling background compaction

ALTER TABLE cycling.comments 
  WITH COMPACTION = {
    'class': 'SizeTieredCompactionStrategy', 
    'enabled': 'false' };

Reading extended compaction logs

Set the log_all subproperty to true to collect in-depth information about compaction activity on a node in a dedicated log file.

When extended compaction is enabled, the database creates a file named compaction-%d.log (%d is a sequential number) in home/logs.

• type:enable

  Lists SSTables that have been flushed previously.

  {"type":"enable","keyspace":"test","table":"t","time":1470071098866,"strategies":
    [    {"strategyId":"0","type":"LeveledCompactionStrategy","tables":[],"repaired":true,"folders":
        ["/home/carl/oss/cassandra/bin/../data/data"]},
      {"strategyId":"1","type":"LeveledCompactionStrategy","tables":[],"repaired":false,"folders":
        ["/home/carl/oss/cassandra/bin/../data/data"]
      }
   ]
  }

• type: flush

  Logs a flush event from a memtable to an SSTable on disk, including the compaction strategy for each table.

  {"type":"flush","keyspace":"test","table":"t","time":1470083335639,"tables":
    [    {"strategyId":"1","table":
        {"generation":1,"version":"mb","size":106846362,"details":
          {"level":0,"min_token":"-9221834874718566760","max_token":"9221396997139245178"}
        }
      }
   ]
  }

• type: compaction

  Logs a compaction event.

  {"type":"compaction","keyspace":"test","table":"t","time":1470083660267,
   "start":"1470083660188","end":"1470083660267","input":
    [    {"strategyId":"1","table":
        {"generation":1372,"version":"mb","size":1064979,"details":
          {"level":1,"min_token":"7199305267944662291",
           "max_token":"7323434447996777057"}
        }
      }
   ],"output":
    [    {"strategyId":"1","table":
        {"generation":1404,"version":"mb","size":1064306,"details":
          {"level":2,"min_token":"7199305267944662291",
           "max_token":"7323434447996777057"}
        }
      }
   ]
  }

• type: pending

  Lists the number of pending tasks for a compaction strategy.

  {"type":"pending","keyspace":"test","table":"t","time":1470083447967,
   "strategyId":"1","pending":200}

Reviewing the table definition

DESC cycling.comments

CREATE TABLE cycling.comments (
  id uuid,
  created_at timestamp,
  comment text,
  commenter text,
  record_id timeuuid,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC)
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'NONE', 'rows_per_partition': '10'}
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy',
  'enabled': 'true', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 
  'class': 'org.apache.cassandra.io.compress.DeflateCompressor'}
AND crc_check_chance = 1.0
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND speculative_retry = '99PERCENTILE';

Adding a field

ALTER TYPE cycling.fullname 
   ADD middlename text;

Changing a field name

To change the name of a field in a user-defined type, use the RENAME old_name TO new_name syntax. Rename multiple fields by separating the directives with AND.

ALTER TYPE cycling.fullname 
  RENAME middlename TO middle 
  AND lastname TO last 
  AND firstname TO first;

Verify the changes using describe:

DESC TYPE cycling.fullname

CREATE TYPE cycling.fullname (
    first text,
    last text,
    middle text
);

Applying a client supplied timestamp to all DMLs

BEGIN BATCH USING TIMESTAMP 1481124356754405

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 2, 13.44, 'Lunch', true
  );
  
  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 3, 25.00, 'Dinner', true
  );

APPLY BATCH;

EXPAND ON

SELECT
  cyclist_name, expense_id,
  amount, WRITETIME(amount),
  description, WRITETIME(description),
  paid, WRITETIME(paid)
FROM
  cycling.cyclist_expenses
WHERE
  cyclist_name = 'Vera ADRIAN';

@ Row 1
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 2
 amount                 | 13.44
 writetime(amount)      | 1481124356754405
 description            | Lunch
 writetime(description) | 1481124356754405
 paid                   | True
 writetime(paid)        | 1481124356754405

@ Row 2
------------------------+------------------
 cyclist_name           | Vera ADRIAN
 expense_id             | 3
 amount                 | 25
 writetime(amount)      | 1481124356754405
 description            | Dinner
 writetime(description) | 1481124356754405
 paid                   | True
 writetime(paid)        | 1481124356754405

(2 rows)

If any DML statement in the batch uses compare-and-set (CAS) logic, an error is returned. For example, the following batch with the CAS IF NOT EXISTS option returns an error:

BEGIN BATCH USING TIMESTAMP 1481124356754405

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 2, 13.44, 'Lunch', true
  );

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 3, 25.00, 'Dinner', false
  ) IF NOT EXISTS;

APPLY BATCH;

InvalidRequest: Error from server: code=2200 [Invalid query]
message="Cannot provide custom timestamp for conditional BATCH"

Batching conditional updates

Batch conditional updates introduced as lightweight transactions. However, a batch containing conditional updates can operate only within a single partition, because the underlying Paxos implementation only works at partition-level granularity. If one statement in a batch is a conditional update, the conditional logic must return true, or the entire batch fails. If the batch contains two or more conditional updates, all the conditions must return true, or the entire batch fails.

BEGIN BATCH

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id
  ) VALUES (
    'Joe WALLS', 1
  ) IF NOT EXISTS;

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Joe WALLS', 1, 8, 'burrito', false
  );

APPLY BATCH;

A continuation of this example shows how to use a static column with conditional updates in batch.

Batching counter updates

BEGIN COUNTER BATCH

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

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

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

APPLY BATCH;

Counter batches cannot include non-counter columns in the DML statements, just as a non-counter batch cannot include counter columns. Counter batch statements cannot provide custom timestamps.

Creating a SASI PREFIX index on a column

CREATE CUSTOM INDEX fn_prefix 
   ON cycling.comments (commenter) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex';

The SASI mode PREFIX is the default, and does not need to be specified.

Creating a SASI CONTAINS index on a column

Create an SASI index on the column firstname:

CREATE CUSTOM INDEX fn_contains 
   ON cycling.comments (comment) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex'
   WITH OPTIONS = { 'mode': 'CONTAINS' };

Creating a SASI SPARSE index on a column

CREATE CUSTOM INDEX fn_sparse 
   ON cycling.comments (record_id) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex'
   WITH OPTIONS = { 'mode': 'SPARSE' };

Creating a SASI PREFIX index on a column using the non-tokenizing analyzer

CREATE CUSTOM INDEX  fn_notcasesensitive 
ON cycling.comments (comment) 
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 
   'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
   'case_sensitive': 'false'};

Creating a SASI analyzing index on a column

CREATE CUSTOM INDEX stdanalyzer_idx 
   ON cycling.comments (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' }
;

Use Java to create FLOG function

Use Javascript to create SQL-like LEFT function

CREATE OR REPLACE FUNCTION cycling.left (
  column text, num int
) 
  RETURNS NULL ON NULL INPUT 
  RETURNS text 
  LANGUAGE javascript AS 
    $$
      column.substring(0, num)
    $$
;

SELECT left(firstname, 1), lastname
FROM cycling.cyclist_name;

 cycling.left(firstname, 1) | lastname
----------------------------+-----------------
                          A |           FRAME
                       null |        MATTHEWS
                       null |             VOS
                          P |       TIRALONGO
                          M |             VOS
                          S |      KRUIKSWIJK
                          A | VAN DER BREGGEN

Creating an index on a clustering column

Define a table having a composite partition key, and then create an index on a clustering column.

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

CREATE INDEX rank_idx ON
cycling.rank_by_year_and_name (rank);

Creating an index on a set or list collection

Create an index on a set or list collection column as you would any other column. Enclose the name of the collection column in parentheses at the end of the CREATE INDEX statement. For example, add a collection of teams to the cyclist_career_teams table to index the data in the teams set.

CREATE TABLE cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

CREATE INDEX teams_idx
ON cycling.cyclist_career_teams (teams) ;

Creating an index on map keys

{'nation':'CANADA' }

To index map keys, use the KEYS keyword and map name in nested parentheses:

CREATE INDEX team_year_idx
ON cycling.cyclist_teams ( KEYS (teams) );

To query the table, you can use CONTAINS KEY in WHERE clauses.

SELECT *
FROM cycling.cyclist_teams
WHERE teams CONTAINS KEY 2015;

The example returns cyclist teams that have an entry for the year 2015.

 id                                   | firstname | lastname   | teams
--------------------------------------+-----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 cb07baad-eac8-4f65-b28a-bddc06a0de23 | Elizabeth | ARMITSTEAD | {2011: 'Team Garmin - Cervelo', 2012: 'AA Drink - Leontien.nl', 2013: 'Boels:Dolmans Cycling Team', 2014: 'Boels:Dolmans Cycling Team', 2015: 'Boels:Dolmans Cycling Team'}
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |        VOS |                                                                                          {2014: 'Rabobank-Liv Woman Cycling Team', 2015: 'Rabobank-Liv Woman Cycling Team'}

Creating an index on map entries

You can create an index on map entries. An ENTRIES index can be created only on a map column of a table that doesn't have an existing index.

CREATE INDEX blist_idx 
   ON cycling.birthday_list (ENTRIES(blist));

To query the map entries in the table, use a WHERE clause with the map name and a value.

SELECT * FROM cycling.birthday_list WHERE blist['age'] = '23';

The example finds cyclists who are the same age.

 cyclist_name     | blist
------------------+----------------------------------------------------------
   Claudio HEINEN | {'age': '23', 'bday': '27/07/1992', 'nation': 'GERMANY'}
 Laurence BOURQUE |  {'age': '23', 'bday': '27/07/1992', 'nation': 'CANADA'}

Creating an index on map values

Creating an index on the full content of a frozen collection

You can create an index on a full FROZEN collection. A FULL index can be created on a set, list, or map column of a table that doesn't have an existing index.

Create an index on the full content of a FROZEN list. The table in this example stores the number of Pro wins, Grand Tour races, and Classic races that a cyclist has competed in.

CREATE TABLE cycling.race_starts (cyclist_name text PRIMARY KEY, rnumbers FROZEN<LIST<int>>);

CREATE INDEX rnumbers_idx ON cycling.race_starts (FULL(rnumbers));

To query the table, use a WHERE clause with the collection name and values:

SELECT * FROM cycling.race_starts WHERE rnumbers = [39,7,14];

 cyclist_name   | rnumbers
----------------+-------------
 John DEGENKOLB | [39, 7, 14]

Create a keyspace for a single node evaluation cluster

CREATE KEYSPACE cycling
  WITH REPLICATION = { 
   'class' : 'SimpleStrategy', 
   'replication_factor' : 1 
  };

Create a keyspace NetworkTopologyStrategy on an evaluation cluster

CREATE KEYSPACE cycling 
  WITH REPLICATION = { 
   'class' : 'NetworkTopologyStrategy', 
   'datacenter1' : 1 } 
;

dsetool status

DC: Cassandra       Workload: Cassandra       Graph: no
======================================================
Status=Up/Down
|/ State=Normal/Leaving/Joining/Moving
--   Address          Load             Owns                 Token                                        Rack         Health [0,1]
                                                            0
UN   10.0.0.15   67.44 MiB        ?                    -9223372036854775808                         rack1        0.90
UN   10.0.0.110   766.4 KiB        ?                    0                                            rack2        0.90

Note: you must specify a keyspace to get ownership information.

CREATE KEYSPACE "Cycling"
  WITH REPLICATION = {
   'class' : 'NetworkTopologyStrategy', 
   'boston'  : 3 , // Datacenter 1 
   'seattle' : 2 , // Datacenter 2
   'tokyo'   : 2   // Datacenter 3
  };

Disabling durable writes

CREATE KEYSPACE cycling
  WITH REPLICATION = { 
   'class' : 'NetworkTopologyStrategy',
   'datacenter1' : 3 
  } 
AND DURABLE_WRITES = false ;

Creating a table with a frozen UDT

CREATE TABLE cycling.race_winners (
  cyclist_name FROZEN<fullname>, 
  race_name text,
  race_position int,
  PRIMARY KEY (race_name, race_position));

See Creating a user-defined type for information on creating UDTs. UDTs can be created unfrozen if only non-collection fields are used in the user-defined type creation. If the table is created with an unfrozen UDT, then individual field values can be updated and deleted.

Creating a table with UUID as the primary key

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

Creating a compound primary key

CREATE TABLE cycling.cyclist_category (
   category text,
   points int,
   id UUID,
   lastname text,
   PRIMARY KEY (category, points))
WITH CLUSTERING ORDER BY (points DESC);

Creating a composite partition key

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

Creating a table with a CDC log

CREATE TABLE cycling.cyclist_id (
   lastname text,
   firstname text,
   age int,
   id UUID,
   PRIMARY KEY ((lastname, firstname), age) );

Storing data in descending order

CREATE TABLE cycling.cyclist_category (
   category text,
   points int,
   id UUID,
   lastname text,
   PRIMARY KEY (category, points))
WITH CLUSTERING ORDER BY (points DESC);

Restoring from the table ID for commit log replay

CREATE TABLE cycling.cyclist_emails (
  userid text PRIMARY KEY,
  id UUID,
  emails set<text>
) WITH ID='1bb7516e-b140-11e8-96f8-529269fb1459';

Delete data in specified columns from a row

Delete the data in specific columns by listing them after the DELETE command, separated by commas. Change the data in first and last name columns to null for the cyclist specified by id.

DELETE firstname, lastname
FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;

CREATE TABLE cycling.cyclist_name (
  id UUID PRIMARY KEY,
  lastname text,
  firstname text
);

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      Alex |           FRAME
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(6 rows)

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 |      null |            null
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(6 rows)

Delete an entire row

Entering no column names after DELETE removes the entire matching row. Remove a cyclist entry from the cyclist_name table and return an error if no rows match by specifying IF EXISTS.

DELETE FROM cycling.cyclist_name 
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
IF EXISTS;

 [applied]
-----------
      True

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 |    Steven |      KRUIKSWIJK
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(5 rows)

Delete a row based on a static column condition

The IF condition limits the WHERE clause, allowing selection based on values in non-PRIMARY KEY columns, such as first and last name. This example removes the cyclist record if the first and last names do not match.

DELETE FROM cycling.cyclist_name 
WHERE id = fb372533-eb95-4bb4-8685-6ef61e994caa 
IF firstname = 'Michael'
  AND lastname = 'Smith';

 [applied] | firstname | lastname
-----------+-----------+----------
     False |   Michael | MATTHEWS

Conditionally deleting data from a column

Use the IF or IF EXISTS clauses to conditionally delete data from columns. Deleting column data conditionally is similar to making an UPDATE conditionally.

DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'JONES' and firstname = 'Bram' 
IF EXISTS;

 [applied]
-----------
     False

DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' 
IF age = 20;

 [applied] | age
-----------+-----
     False |  18

Deleting one or more rows

The WHERE clause specifies which row or rows to delete from a specified table.

DELETE FROM cycling.cyclist_name 
WHERE id = 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47;

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 5b6962dd-3f90-4c93-8f61-eabfa4a803e2 |  Marianne |             VOS
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |     Paolo |       TIRALONGO
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(4 rows)

To delete more than one row, use the keyword IN and supply a list of comma-separated values in parentheses:

DELETE FROM cycling.cyclist_name 
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  220844bf-4860-49d6-9a4b-6b5d3a79cbfb
);

 id                                   | firstname | lastname
--------------------------------------+-----------+-----------------
 fb372533-eb95-4bb4-8685-6ef61e994caa |   Michael |        MATTHEWS
 e7cd5752-bc0d-4157-a80f-7523add8dbcd |      Anna | VAN DER BREGGEN

(2 rows)

Deleting old data using a timestamp

The TIMESTAMP is an integer representing microseconds. Use TIMESTAMP to identify data to delete. The query deletes any rows from a partition older than the timestamp.

DELETE firstname, lastname
  FROM cycling.cyclist_name
  USING TIMESTAMP 1318452291034
  WHERE lastname = 'VOS';

Deleting from a collection set, list, or map

DELETE sponsorship FROM cycling.race_sponsors 
WHERE race_name = 'Giro d''Italia';

To delete an element from a list, specify the column_name followed by the list index position in square brackets:

DELETE sponsorship[2] FROM cycling.race_sponsors WHERE race_year=2018 AND race_name='Tour de France' ;

DELETE teams[2014]
FROM cycling.cyclist_teams
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

Manage object permissions

Access to data resources

Use the data resource permissions to manage access to keyspaces, tables, rows, and types.

GRANT ALL PERMISSIONS ON KEYSPACE cycling TO cycling_admin;

GRANT SELECT, MODIFY ON KEYSPACE cycling TO coach;

GRANT ALTER ON KEYSPACE cycling TO coach;

GRANT SELECT ON 'Sprint' ROWS IN cycling.cyclist_category TO martin;

Specifying TTL and TIMESTAMP

Insert a cyclist name using both a 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;

• Time-to-live (TTL) in seconds
• Timestamp in microseconds since epoch

Inserting values into a collection (set and map)

INSERT INTO cycling.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'
  }
);

INSERT INTO cycling.cyclist_teams (
  id, firstname, lastname, teams
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
  'Marianne',
  'VOS', 
  {
    2015 : 'Rabobank-Liv Woman Cycling Team', 
    2014 : 'Rabobank-Liv Woman Cycling Team'
  }
);

The size of one item in a collection is limited to 64K.

To insert data into a collection column of a user-defined type, enclose components of the type in parentheses within the curly brackets, as shown in Using a user-defined type.

Inserting a row only if it does not already exist

Add IF NOT EXISTS to the command to ensure that the operation is not performed if a row with the same primary key already exists:

INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
) 
IF NOT EXISTS; 

 [applied]
-----------
      True

 [applied] | id                                   | firstname | lastname
-----------+--------------------------------------+-----------+----------
     False | c4b65263-fe58-4846-83e8-f0e1c13d518f |  Rissella |    RATTO

All permissions for all roles and resources

LIST ALL PERMISSIONS;

Individual role permissions

LIST ALL PERMISSIONS OF sam;

 role | username | resource           | permission | granted | restricted | grantable
------+----------+--------------------+------------+---------+------------+-----------
  sam |      sam | <keyspace cycling> |     SELECT |   False |      False |      True
  sam |      sam | <keyspace cycling> |     MODIFY |   False |      False |      True

(2 rows)

All permissions on a resource

LIST ALL PERMISSIONS ON cycling.cyclist_name;

 role            | username        | resource           | permission | granted | restricted | grantable
-----------------+-----------------+--------------------+------------+---------+------------+-----------
           coach |           coach | <keyspace cycling> |      ALTER |    True |      False |     False
           coach |           coach | <keyspace cycling> |     SELECT |    True |      False |     False
           coach |           coach | <keyspace cycling> |     MODIFY |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     CREATE |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |      ALTER |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |       DROP |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     SELECT |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |     MODIFY |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |  AUTHORIZE |    True |      False |     False
   cycling_admin |   cycling_admin | <keyspace cycling> |   DESCRIBE |    True |      False |     False
 cycling_analyst | cycling_analyst | <keyspace cycling> |     SELECT |    True |      False |     False
        dantest1 |        dantest1 | <keyspace cycling> |  AUTHORIZE |    True |      False |     False
        db_admin |        db_admin | <keyspace cycling> |     SELECT |   False |       True |     False
        db_admin |        db_admin | <keyspace cycling> |     MODIFY |   False |       True |     False
          martin |          martin |    <all keyspaces> |     CREATE |   False |       True |     False
          martin |          martin | <keyspace cycling> |     CREATE |    True |      False |     False
      role_admin |      role_admin | <keyspace cycling> |     SELECT |   False |       True |     False
      role_admin |      role_admin | <keyspace cycling> |     MODIFY |   False |       True |     False
             sam |             sam | <keyspace cycling> |     SELECT |   False |      False |      True
             sam |             sam | <keyspace cycling> |     MODIFY |   False |      False |      True
    team_manager |    team_manager | <keyspace cycling> |     MODIFY |    True |      False |     False

(21 rows)

All roles

LIST ROLES;

(Internal Role Management only) Roles assigned to a role

LIST ROLES OF coach;

 role          | super | login | options
---------------+-------+-------+---------
         coach | False | False |        {}
 cycling_admin | False |  True |        {}

(2 rows)

Using a column alias

When your selection list includes functions or other complex expressions, use aliases to make the output more readable. This example applies aliases to the dateOf(created_at) and blobAsText(content) functions:

SELECT
  event_id, 
  dateOf(created_at) AS creation_date,
  blobAsText(content) AS content 
FROM timeline;

The output labels these columns with more understandable names:

 event_id                | creation_date            | content
-------------------------+--------------------------+----------------
 550e8400-e29b-41d4-a716 | 2013-07-26 10:44:33+0200 | Some stuff

(1 rows)

The number of rows returned by the query is shown at the bottom of the output.

Specifying the source table using FROM

The following example SELECT statement returns the number of rows in the IndexInfo table in the system keyspace:

SELECT COUNT(*) 
FROM system.IndexInfo;

Controlling the number of rows returned using LIMIT

The LIMIT option sets the maximum number of rows that the query returns:

SELECT lastname 
FROM cycling.cyclist_name 
LIMIT 50000;

Even if the query matches 105,291 rows, the database only returns the first 50,000.

The cqlsh shell has a default row limit of 10,000. The DSE server and native protocol do not limit the number of returned rows, but they apply a timeout to prevent malformed queries from causing system instability.

Selecting partitions

partition_column = value

partition_column IN ( value1, value2 [ , ... ] )

partition_column1 = value1 
AND partition_column2 = value2 [ AND ... ] )

Controlling the number of rows returned using PER PARTITION LIMIT

The PER PARTITION LIMIT option sets the maximum number of rows that the query returns from each partition. For example, create a table that sorts data into more than one partition.

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

After inserting data, the table contains these rows:

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    1 |    Phillippe GILBERT
      2014 |                        4th Tour of Beijing |    2 |        Daniel MARTIN
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    1 |        Ilnur ZAKARIN
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    2 |      Carlos BETANCUR
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 |          Adam PHELAN
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

To get the top two racers in every race year and race name, use the SELECT statement with PER PARTITION LIMIT 2.

SELECT * 
FROM cycling.rank_by_year_and_name 
PER PARTITION LIMIT 2;

Output:

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    1 |    Phillippe GILBERT
      2014 |                        4th Tour of Beijing |    2 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    1 |        Ilnur ZAKARIN
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    2 |      Carlos BETANCUR
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 |          Adam PHELAN

Filtering data using WHERE

The WHERE clause introduces one or more relations that filter the rows returned by SELECT.

The column specifications

Filtering on the partition key

CREATE TABLE cycling.cyclist_career_teams (
  id UUID PRIMARY KEY, lastname text, teams set<text>
);

SELECT id, lastname, teams 
FROM cycling.cyclist_career_teams 
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

Filtering on a clustering column

Use a relation on a clustering column only if it is preceded by relations that reference all the elements of the partition key.

Example:

CREATE TABLE cycling.cyclist_points (
  id UUID, 
  race_points int, 
  firstname text, 
  lastname text, 
  race_title text, 
  PRIMARY KEY (id, race_points)
);

SELECT SUM(race_points) 
FROM cycling.cyclist_points 
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac
  AND race_points > 7;

 system.sum(race_points)
-------------------------
                     195

(1 rows)

Example:

CREATE TABLE cycling.calendar (
  race_id int, 
  race_name text, 
  race_start_date timestamp, 
  race_end_date timestamp, 
  PRIMARY KEY (race_id, race_start_date, race_end_date)
);

SELECT * 
FROM cycling.calendar 
WHERE race_start_date = '2015-06-13' 
ALLOW FILTERING;

Output:

 race_id | race_start_date                 | race_end_date                   | race_name
---------+---------------------------------+---------------------------------+----------------
     102 | 2015-06-13 07:00:00.000000+0000 | 2015-06-13 07:00:00.000000+0000 | Tour de Suisse
     103 | 2015-06-13 07:00:00.000000+0000 | 2015-06-17 07:00:00.000000+0000 | Tour de France

It is possible to combine the partition key and a clustering column in a single relation. For details, see Comparing clustering columns.

Filtering on indexed columns

A WHERE clause in a SELECT on an indexed table must include at least one equality relation to the indexed column. For details, see Indexing a column.

Using the IN operator

Use IN, an equals condition operator, to list multiple possible values for a column. This example selects two columns, first_name and last_name, from three rows having employee ids (primary key) 105, 107, or 104:

SELECT first_name, last_name 
FROM emp 
WHERE empID IN (105, 107, 104);

The list can consist of a range of column values separated by commas.

Using IN to filter on a compound or composite primary key

CREATE TABLE parts (
  part_type text, 
  part_name text, 
  part_num int, 
  part_year text, 
  serial_num text, 
  PRIMARY KEY ((part_type, part_name), part_num, part_year)
);

SELECT * 
FROM parts 
WHERE part_type='alloy'
  AND part_name='hubcap' 
  AND part_num = 1249
  AND part_year IN ('2010', '2015');

SELECT * 
FROM parts 
WHERE part_num = 123456
  AND part_year IN ('2010', '2015') 
ALLOW FILTERING;

CQL supports an empty list of values in the IN clause, useful in Java Driver applications when passing empty arrays as arguments for the IN clause.

When not to use IN

Under most conditions, using IN in relations on the partition key is not recommended. To process a list of values, the SELECT may have to query many nodes, which degrades performance. For example, consider a single local datacenter cluster with 30 nodes, a replication factor of 3, and a consistency level of LOCAL_QUORUM. A query on a single partition key query goes out to two nodes. But if the SELECT uses the IN condition, the operation can involve more nodes — up to 20, depending on where the keys fall in the token range.

Using IN for clustering columns is safer. See Cassandra Query Patterns: Not using the “in” query for multiple partitions for additional logic about using IN.

Filtering on collections

Your query can retrieve a collection in its entirety. It can also index the collection column, and then use the CONTAINS condition in the WHERE clause to filter the data for a particular value in the collection, or use CONTAINS KEY to filter by key. This example features a collection of tags in the playlists table. The query can index the tags, then filter on 'blues' in the tag set.

SELECT album, tags 
FROM playlists 
WHERE tags CONTAINS 'blues';

SELECT * 
FROM playlists 
WHERE venue CONTAINS 'The Fillmore';

SELECT * 
FROM playlists 
WHERE venue CONTAINS KEY '2014-09-22 22:00:00-0700';

Filtering a map's entries

CREATE INDEX blist_idx
ON cycling.birthday_list (ENTRIES(blist));

SELECT * 
FROM cycling.birthday_list 
WHERE blist['age'] = '23';

Filtering a full frozen collection

CREATE INDEX rnumbers_idx
ON cycling.race_starts (FULL(rnumbers));

SELECT * 
FROM cycling.race_starts 
WHERE rnumbers = [39, 7, 14];

Range relations

DataStax Enterprise supports greater-than and less-than comparisons, but for a given partition key, the conditions on the clustering column are restricted to the filters that allow selection of a contiguous set of rows.

CREATE TABLE ruling_stewards (
  steward_name text,
  king text,
  reign_start int,
  event text,
  PRIMARY KEY (steward_name, king, reign_start)
); 

SELECT *
FROM ruling_stewards
WHERE king = 'Brego'
  AND reign_start >= 2450
  AND reign_start < 2500 
ALLOW FILTERING;

 steward_name | king  | reign_start | event
--------------+-------+-------------+--------------------
      Boromir | Brego |        2477 |   Attacks continue
       Cirion | Brego |        2489 | Defeat of Balchoth
       
(2 rows)

To allow selection of a contiguous set of rows, the WHERE clause must apply an equality condition to the king component of the primary key. The ALLOW FILTERING clause is also required. ALLOW FILTERING provides the capability to query the clustering columns using any condition.

Bad Request: Cannot execute this query as it might involve data
filtering and thus may have unpredictable performance. If you want
to execute this query despite the performance unpredictability,
use ALLOW FILTERING.

SELECT * 
FROM ruling_stewards
WHERE king = 'none'
  AND reign_start >= 1500
  AND reign_start < 3000 
LIMIT 10 
ALLOW FILTERING;

Using compound primary keys and sorting results

1. Only include clustering columns in the ORDER BY clause.
2. In the WHERE clause, provide all the partition key values and clustering column values that precede the column(s) in the ORDER BY clause. In 6.0 and later, the columns specified in the ORDER BY clause must be an ordered subset of the columns of the clustering key; however, columns restricted by the equals operator (=) or a single-valued IN restriction can be skipped.
3. When sorting multiple columns, the columns must be listed in the same order in the ORDER BY clause as they are listed in the PRIMARY KEY clause of the table definition.
4. Sort ordering is limited. For example, if your table definition uses CLUSTERING ORDER BY (start_month ASC, start_day ASC), then you can use ORDER BY start_day, race in your query (ASC is the default). You can also reverse the sort ordering if you apply it to all of the columns; for example, ORDER BY start_day DESC, race DESC.
5. Refer to a column using the actual name, not an alias.

For example, set up the playlists table (which uses a compound primary key), and use this query to get information about a particular playlist, ordered by song_order. You do not need to include the ORDER BY column in the select expression.

SELECT *
FROM playlists 
WHERE id = 62c36092-82a1-3a00-93d1-46196ee77204
ORDER BY song_order DESC 
LIMIT 50;

Output:

Or, create an index on playlist artists, and use this query to get titles of Fu Manchu songs on the playlist:

CREATE INDEX ON playlists(artist);

SELECT album, title 
FROM playlists 
WHERE artist = 'Fu Manchu';

Output:

Grouping results

The GROUP BY clause condenses the selected rows that share the same values for a set of columns into a group. A GROUP BY clause can contain:

• Partition key columns and clustering columns.
• A deterministic monotonic function, including a user-defined function (UDF), on the last clustering column specified in the GROUP BY clause. The FLOOR() function is monotonic when the duration and start time parameters are constants.
• A deterministic aggregate.

CREATE TABLE cycling.race_times_summary (
  race_date date,
  race_time time,
  PRIMARY KEY (race_date, race_time)
);

 race_date  | race_time
------------+--------------------
 2019-03-21 | 10:01:18.000000000
 2019-03-21 | 10:15:20.000000000
 2019-03-21 | 11:15:38.000000000
 2019-03-21 | 12:15:40.000000000
 2018-07-26 | 10:01:18.000000000
 2018-07-26 | 10:15:20.000000000
 2018-07-26 | 11:15:38.000000000
 2018-07-26 | 12:15:40.000000000
 2017-04-14 | 10:01:18.000000000
 2017-04-14 | 10:15:20.000000000
 2017-04-14 | 11:15:38.000000000
 2017-04-14 | 12:15:40.000000000

(12 rows)

SELECT
  race_date, race_time 
FROM
  cycling.race_times_summary
GROUP BY
  race_date;

 race_date  | race_time
------------+--------------------
 2019-03-21 | 10:01:18.000000000
 2018-07-26 | 10:01:18.000000000
 2017-04-14 | 10:01:18.000000000

(3 rows)

SELECT
  race_date, FLOOR(race_time, 1h), COUNT(*)
FROM
  cycling.race_times_summary
GROUP BY
  race_date, FLOOR(race_time, 1h);

 race_date  | system.floor(race_time, 1h) | count
------------+-----------------------------+-------
 2019-03-21 |          10:00:00.000000000 |     2
 2019-03-21 |          11:00:00.000000000 |     1
 2019-03-21 |          12:00:00.000000000 |     1
 2018-07-26 |          10:00:00.000000000 |     2
 2018-07-26 |          11:00:00.000000000 |     1
 2018-07-26 |          12:00:00.000000000 |     1
 2017-04-14 |          10:00:00.000000000 |     2
 2017-04-14 |          11:00:00.000000000 |     1
 2017-04-14 |          12:00:00.000000000 |     1

(9 rows)

Computing aggregates

DSE provides standard built-in functions that return aggregate values to SELECT statements.

Using COUNT() to get the non-null value count for a column

A SELECT expression using COUNT(column_name) returns the number of non-null values in a column. COUNT ignores null values.

For example, count the number of last names in the cyclist_name table:

SELECT COUNT(lastname) 
FROM cycling.cyclist_name;

Getting the number of matching rows and aggregate values with COUNT()

A SELECT expression using COUNT(*) returns the number of rows that matched the query. Use COUNT(1) to get the same result. COUNT(*) or COUNT(1) can be used in conjunction with other aggregate functions or columns.

This example counts the number of rows in the cyclist name table:

SELECT COUNT(*)
FROM cycling.cyclist_name;

This example calculates the maximum value for start day in the cycling events table and counts the number of rows returned:

SELECT start_month, MAX(start_day), COUNT(*)
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross';

This example provides a year that is not stored in the events table:

SELECT start_month, MAX(start_day)
FROM cycling.events
WHERE year = 2022
ALLOW FILTERING;

 start_month | system.max(start_day)
-------------+-----------------------
        null |                  null

(1 rows)

Getting maximum and minimum values in a column

SELECT MAX(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.max(race_points)
-------------------------
                     120

Warnings :
Aggregation query used without partition key

SELECT MIN(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.min(race_points)
-------------------------
                       6

If the column referenced by MAX or MIN has an ascii or text data type, these functions return the last or first item in an alphabetic sort of the column values. If the specified column has data type date or timestamp, these functions return the most recent or least recent times and dates. If a column has a null value, MAX and MIN ignores that value; if the column for an entire set of rows contains null, MAX and MIN return null.

If the query includes a WHERE clause (recommended), MAX or MIN returns the largest or smallest value from the rows that satisfy the WHERE condition.

Getting the average or sum of a column of numbers

DSE computes the average of all values in a column when AVG is used in the SELECT statement:

SELECT AVG(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.avg(race_points)
-------------------------
                      67

Using SUM to get a total:

SELECT SUM(race_points)
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 system.sum(race_points)
-------------------------
                     201

(1 rows)

If any of the rows returned have a null value for the column referenced for a SUM or AVG aggregation function, the function includes that row in the row count, but uses a zero value to calculate the average. The SUM and AVG functions do not work with text, uuid, or date fields.

Retrieving the date/time a write occurred

The WRITETIME function applied to a column returns the date/time in microseconds at which the column was written to the database.

For example, to retrieve the date/time that writes occurred to the firstname column of a cyclist:

SELECT WRITETIME (firstname)
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;

 writetime(firstname)
----------------------
     1538688876521481
     1538688876523973
     1538688876525239

The WRITETIME output of the last write 1538688876525239 in microseconds converts to Thursday, October 4, 2018 4:34:36.525 PM GMT-05:00 DST.

Retrieving the time-to-live of a column

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  200, 'placeholder', '2015-05-27', '2015-05-27'
) 
USING TTL 100;

UPDATE cycling.calendar 
USING TTL 300 
SET race_name = 'dummy' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';

SELECT TTL(race_name) 
FROM cycling.calendar 
WHERE race_id = 200;

 ttl(race_name)
----------------
            276

(1 rows) 

Retrieving values in the JSON format

For details, see Retrieval using JSON

Updating a column

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

UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;

Updating a counter column

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

To use a lightweight transaction on a counter column to ensure accuracy, put one or more counter updates in the batch statement. For details, see Performing conditional updates in a batch.

Creating a partition using UPDATE

UPDATE cycling.cyclist_name
SET
  firstname = 'Anna',
  lastname = 'VAN DER BREGGEN' 
WHERE id = e7cd5752-bc0d-4157-a80f-7523add8dbcd;

Updating a list

UPDATE cycling.upcoming_calendar 
SET events = ['Criterium du Dauphine','Tour de Suisse']
WHERE year=2015 AND month=06;

UPDATE cycling.upcoming_calendar 
SET events = ['Tour de France'] + events 
WHERE year=2015 AND month=06;

UPDATE cycling.upcoming_calendar 
SET events = events + ['Tour de France'] 
WHERE year=2017 AND month=05;

UPDATE cycling.upcoming_calendar 
SET events[2] = 'Tour de France' 
WHERE year=2015 AND month=06;

UPDATE cycling.upcoming_calendar 
SET events = events - ['Tour de France'] 
WHERE year=2015 AND month=06;

To update data in a collection column of a user-defined type, enclose components of the type in parentheses within the curly brackets, as shown in "Using a user-defined type."

Updating a set

UPDATE cycling.cyclist_career_teams
SET teams = teams + {'Team DSB - Ballast Nedam'} 
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

UPDATE cycling.cyclist_career_teams
SET teams = teams - {'DSB Bank Nederland bloeit'} 
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;

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

Updating a map

UPDATE cycling.upcoming_calendar 
SET description = description + {'Criterium du Dauphine' : 'Easy race'} 
WHERE year = 2015 AND month = 06 ;

UPDATE cycling.upcoming_calendar 
SET events[2] = 'Vuelta Ciclista a Venezuela' 
WHERE year = 2015 AND month = 06;

UPDATE cycling.upcoming_calendar 
USING TTL 10000000
SET events[2] = 'Vuelta Ciclista a Venezuela' 
WHERE year = 2015 AND month = 06;

UPDATE cycling.upcoming_calendar 
SET description = description + {'Criterium du Dauphine' : 'Easy race', 'Tour du Suisse' : 'Hard uphill race'}
WHERE year = 2015 AND month = 6;

Remove elements from a map in the same way using - instead of +.

About updating sets and maps caution

CQL supports alternate methods for updating sets and maps. These alternatives may seem to accomplish the same tasks, but the database handles them differently in important ways.

UPDATE cycling.upcoming_calendar 
SET description = 
{'Criterium du Dauphine' : 'Easy race', 
 'Tour du Suisse' : 'Hard uphill race'} 
WHERE year = 2015 AND month = 6;

The easiest way to add a new entry to the map is to use the + operator as described above.

You may, however, try to add the new entry with a command that overwrites the first two and adds the new one.

These two statements seem to do the same thing. But behind the scenes, the database processes the second statement by deleting the entire collection and replacing it with a new collection containing three entries. This creates tombstones for the deleted entries, even though these entries are identical to the entries in the new map collection. If your code updates all map collections this way, it generates many tombstones, which may slow the system down.

The examples above use map collections, but the same caution applies to updating sets.

Updating a UDT with non-collection fields

UPDATE cycling.cyclist_stats 
SET basics.birthday = '2000-12-12' 
WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

Conditionally updating columns

You can conditionally update columns using IF or IF EXISTS.

UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' AND age = 18
IF EXISTS;

• If the row exists (returns true), the following is output:

   [applied]
  -----------
        True

• If no row exists (returns false), the command fails and the following is output:

   [applied]
  -----------
       False

Use IF condition to apply tests to one or more other (non-primary key) column values in the matching row.

UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' AND age = 18
IF id = 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503;

UPDATE cycling.cyclist_id 
SET id = UUID() 
WHERE lastname = 'Smith' AND firstname = 'Joe' AND age = 22
IF id = NULL;

Performing conditional updates in a BATCH

The UPDATE command creates a new row if no matching row is found. New rows are not immediately available for lightweight transactions applied in the same BATCH.

For example:

CREATE TABLE cycling.mytable (a int, b int, s int static, d text, PRIMARY KEY (a, b));

BEGIN BATCH
     INSERT INTO cycling.mytable (a, b, d) values (7, 7, 'a');
     UPDATE cycling.mytable SET s = 7 WHERE a = 7 IF s = NULL;
APPLY BATCH;

In the first batch above, the insert command creates a partition with primary key values (7,7) but does not set a value for the s column. Even though the s column was not defined for this row, the IF s = NULL conditional succeeds, so the batch succeeds. (In previous versions, the conditional would have failed, and that failure would have caused the entire batch to fail.)