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

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;

Alter a table to associate a vertex label or an edge label with it

ALTER TABLE food_cql_conversion.person WITH VERTEX LABEL "person";

ALTER TABLE food_cql_conversion.person_authored_book 
  WITH EDGE LABEL "authored"
  FROM person(person_name, person_id)
  TO book(book_name, book_id);

Renaming a vertex label or an edge label

ALTER TABLE food.person RENAME VERTEX LABEL TO "personX";

ALTER TABLE food."person_authored_book" RENAME EDGE LABEL TO "authoredX";

Removing a vertex label or an edge label

ALTER TABLE food.person WITHOUT VERTEX LABEL "personX";

ALTER TABLE food."person_authored_book" WITHOUT EDGE LABEL "authoredX";

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

Change the 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 (where %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 CompactionStrategy 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

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, balance
  ) VALUES (
    'Vera ADRIAN', 0
  ) IF NOT EXISTS;

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'Vera ADRIAN', 1, 7.95, 'Breakfast', false
  );

APPLY BATCH;
BEGIN BATCH

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

  UPDATE cycling.cyclist_expenses
  SET balance = -32.95
  WHERE cyclist_name = 'Vera ADRIAN'
  IF balance = -7.95;

APPLY BATCH;
BEGIN BATCH

  UPDATE cycling.cyclist_expenses
  SET balance = 0
  WHERE cyclist_name = 'Vera ADRIAN'
  IF balance = -32.95;

  UPDATE cycling.cyclist_expenses
  SET paid = true
  WHERE cyclist_name = 'Vera ADRIAN'
  AND expense_id = 1 IF paid = false;

  UPDATE cycling.cyclist_expenses
  SET paid = true
  WHERE cyclist_name = 'Vera ADRIAN'
  AND expense_id = 3
  IF paid = false;

APPLY BATCH;
BEGIN BATCH

  INSERT INTO cycling.cyclist_expenses (
    cyclist_name, expense_id, amount, description, paid
  ) VALUES (
    'John SMITH', 3, 15.00, 'Lunch', false
  );

  INSERT INTO cycling.cyclist_name (
    id, lastname, firstname
  ) VALUES (
    6ab09bec-e68e-48d9-a5f8-97e6fb4c9b12, 'SMITH', 'John'
  );

APPLY BATCH;
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 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);

CREATE INDEX IF NOT EXISTS 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 IF NOT EXISTS 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 |                                                                                                                                   {2015: 'Rabobank-Liv Woman Cycling Team'}

(2 rows)

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 IF NOT EXISTS 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'}

(2 rows)

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 IF NOT EXISTS cycling.race_starts (
  cyclist_name text PRIMARY KEY,
  rnumbers FROZEN<LIST<int>>
);

CREATE INDEX IF NOT EXISTS 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]

(1 rows)

Creating a table with UUID as the primary key

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

Creating a composite partition key

CREATE TABLE IF NOT EXISTS 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 frozen UDT

CREATE TABLE IF NOT EXISTS 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 a CDC log

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

Delete data in specified columns from a row

CREATE TABLE IF NOT EXISTS 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)

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

 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

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, TRUNCATE, UPDATE
ON KEYSPACE cycling
TO coach;

GRANT ALTER
ON KEYSPACE cycling
TO coach;

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

Specifying time-to-live (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

Specifying time-to-live (TTL) and timestamp in collections.

Insert a cyclist name using both a TTL and timestamp for a sponsorship set collection.

INSERT INTO cycling.cyclist_sponsors_expire (
  cyclist_name, sponsorship
) VALUES (
  'PRIETO, Marcela',
  { 'Castrelli', 'Alfa Romeo' }
) 
USING TIMESTAMP 100
AND TTL 10000;

• 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 roles

LIST ROLES;

(Internal Role Management only) Roles assigned to a role

LIST ROLES OF coach;

 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

(10 rows)

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, the cycling keyspace contains this table:

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

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

(11 rows)

This query returns the top two racers for each race year and race name combination using 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

(8 rows)

Filtering data using WHERE

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

Column specifications

Filtering on the partition key

CREATE TABLE IF NOT EXISTS 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;

This example table contains a more complex primary key:

CREATE TABLE IF NOT EXISTS cycling.events (
  year int,
  start_month int,
  start_day int,
  end_month int,
  end_day int,
  race text,
  discipline text,
  location text,
  uci_code text,
  PRIMARY KEY (
    (year, discipline), start_month, start_day, race
  )
);

SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
  AND start_day = 1;

Output:

 year | discipline  | start_month | start_day | race                                   | end_day | end_month | location | uci_code
------+-------------+-------------+-----------+----------------------------------------+---------+-----------+----------+----------
 2017 | Cyclo-cross |           1 |         1 | DVV verzekeringen trofee - GP Sven Nys |    null |      null |     Baal |      C1 

(1 rows)

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 table in the cycling keyspace (the partition key is the id column, the clustering column is race_points):

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

Example query:

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

Output:

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

(1 rows)

CREATE TABLE IF NOT EXISTS 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
  )
) WITH CLUSTERING ORDER BY (
  race_start_date DESC, race_end_date DESC
);

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 00:00:00.000000+0000 | 2015-06-21 00:00:00.000000+0000 | Tour de Suisse

(1 rows)

Filtering on indexed columns

A WHERE clause in a SELECT on a table with a Storage-Attached Indexing (SAI) secondary indexed column must include at least one equality relation to the indexed column. See CREATE CUSTOM INDEX.

Using the IN operator

Use IN, an equals condition operator, to list multiple values for a column in a WHERE clause.

This example selects the rows where race_id is in a list of values:

SELECT * 
FROM cycling.calendar 
WHERE race_id IN (101, 102, 103); 
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date DESC;
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
ORDER BY race_start_date ASC;
SELECT *
FROM cycling.calendar
WHERE race_id IN (100, 101, 102)
  AND (race_start_date, race_end_date) >= ('2015-05-09', '2015-05-24');

The values in the list are separated by commas.

Using IN to filter on a compound primary key

Use an IN condition on the last column of a compound primary key only when it is preceded by equality conditions for all preceding columns of the primary key.

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

This query contains the appropriate WHERE clause containing equality conditions for the first two columns of the primary key and an IN condition for the last column of the primary key:

SELECT * 
FROM cycling.cyclist_id 
WHERE lastname = 'EENKHOORN'
  AND firstname = 'Pascal'
  AND age IN (17, 18); 

When using IN, you can omit the equality test for clustering columns other than the last clustering column. This may require ALLOW FILTERING and should not be used in a production environment.

CREATE TABLE IF NOT EXISTS cycling.events (
  year int,
  start_month int,
  start_day int,
  end_month int,
  end_day int,
  race text,
  discipline text,
  location text,
  uci_code text,
  PRIMARY KEY (
    (year, discipline), start_month, start_day, race
  )
);

SELECT *
FROM cycling.events
WHERE race = 'Superprestige - Hoogstraten -2017'
  AND start_month IN (1, 2)
ALLOW FILTERING;

CQL supports an empty list of values in the IN clause, which can be useful in driver applications when passing empty arrays as arguments for the IN clause. See Connecting to Astra clusters using Astra drivers.

When not to use IN

Typically, 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 will cause less performance latency because all query actions are performed in a single partition.

See Cassandra Query Patterns: Not using the “in” query for multiple partitions.

Filtering on collections

A query can retrieve a collection in its entirety. You 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 set of text values named teams in the cyclist_career_teams table. This query filters on a value in the teams set.

SELECT *
FROM cycling.cyclist_career_teams
WHERE teams CONTAINS 'Rabobank-Liv Giant';

 id                                   | lastname | teams
--------------------------------------+----------+-----------------------------------------------------------------------------
-----------------------
 1c9ebc13-1eab-4ad5-be87-dce433216d40 |    BRAND | {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Li
v Woman Cycling Team'}

(1 rows)

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

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

See Indexing a collection and cql_reference/cql_commands/cqlCreateIndex.html#cqlCreateIndex__CreatIdxCollKey.

Filtering map entries

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

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

Filtering a full frozen collection

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

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

Range relations

DataStax Astra 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.

SELECT * 
FROM cycling.calendar 
WHERE race_id = 101
  AND race_start_date >= '2014-05-27' 
  AND race_start_date < '2017-06-16';

 race_id | race_start_date                 | race_end_date                   | race_name
---------+---------------------------------+---------------------------------+-----------------------
     101 | 2015-06-07 00:00:00.000000+0000 | 2015-06-14 00:00:00.000000+0000 | Criterium du Dauphine
     101 | 2014-06-06 00:00:00.000000+0000 | 2014-06-13 00:00:00.000000+0000 | Criterium du Dauphine

(2 rows)

To allow selection of a contiguous set of rows, the WHERE clause must apply an equality condition to the race_id component of the primary key.

Using compound primary keys and sorting results

SELECT *
FROM cycling.cyclist_category
WHERE category = 'Time-trial'
ORDER BY points DESC;

Output:

 category   | points | id                                   | lastname
------------+--------+--------------------------------------+------------
 Time-trial |    182 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
 Time-trial |      3 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK

(2 rows)

CREATE TABLE IF NOT EXISTS cycling.events (
  year int,
  start_month int,
  start_day int,
  end_month int,
  end_day int,
  race text,
  discipline text,
  location text,
  uci_code text,
  PRIMARY KEY (
    (year, discipline), start_month, start_day, race
  )
);

This query contains a WHERE clause that provides values for all primary key columns that precede the race column and orders the results by race:

SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
  AND start_day = 1
ORDER BY race;

Output:

 year | discipline  | start_month | start_day | race                                   | end_day | end_month | location | uci_code
------+-------------+-------------+-----------+----------------------------------------+---------+-----------+----------+----------
 2017 | Cyclo-cross |           1 |         1 | DVV verzekeringen trofee - GP Sven Nys |    null |      null |     Baal |      C1 

(1 rows)

SELECT *
FROM cycling.events
WHERE year = 2017
  AND discipline = 'Cyclo-cross'
  AND start_month = 1
ORDER BY start_day, race;

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.

CREATE TABLE IF NOT EXISTS 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

Astra provides the built-in functions COUNT(), MIN(), MAX(), SUM(), and AVG() that return aggregate values to SELECT statements. The following sections show examples.

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

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

This query counts 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 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 query returns the number of rows in the cyclist_name table:

SELECT COUNT(*)
FROM cycling.cyclist_name;

This query counts the number of rows and calculates the maximum value for start_day in the events table:

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

A SELECT using MAX(column_name) returns the maximum value in a column. When the column's data type is numeric (bigint, decimal, double, float, int, or smallint), MAX(column_name) returns the maximum value.

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

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

(1 rows)

Warnings :
Aggregation query used without partition key

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

(1 rows)

If the column referenced by the MAX or MIN functions has an ascii or text data type, the 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, the functions return the most recent or least recent times and dates.

If a column has a null value, MAX and MIN ignore the null 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 returns the largest value from the rows that satisfy the WHERE condition, and MIN returns the smallest value from the rows that satisfy the WHERE condition.

Getting the average or sum of a column of numbers

This example computes the average of all values in a column using AVG:

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

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

(1 rows)

Use SUM to get a total:

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)

If any of the rows returned have a null value for the column referenced in the AVG function, the function includes that row in the row count, but uses zero for the null 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.

This query retrieves 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

The time-to-live (TTL) of a column value in a row is the number of seconds before the value is marked with a tombstone.

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

This example UPDATE sets the TTL of a single race_name column value to 200 seconds:

UPDATE cycling.calendar 
USING TTL 300 
SET race_name = 'Tour de France - Stage 12' 
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 JSON format

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 using a timestamp and TTL

UPDATE cycling.cyclist_sponsors_expire 
USING TIMESTAMP 200
AND TTL 20000 
SET sponsorship += { 'Tag Heuer' } 
WHERE cyclist_name = 'PRIETO, Marcela';

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.