CQL quick reference

Provides a consolidated syntax list of Cassandra Query Language (CQL) commands for quick reference.

Provides a consolidated syntax list of Cassandra Query Language (CQL) commands for quick reference.

See this quick reference guide for other CQL versions: 5.1 | 6.0 | DDAC.

ALTER KEYSPACE

ALTER KEYSPACE keyspace_name 
  WITH REPLICATION = { replication_map }
  [ AND DURABLE_WRITES = ( true | false ) ] ;
Learn more.

Example

Change the cycling keyspace to NetworkTopologyStrategy in a single datacenter and turn off durable writes (not recommended). This example uses the default datacenter name with a replication factor of 1.

ALTER KEYSPACE cycling
WITH REPLICATION = { 
  'class' : 'NetworkTopologyStrategy',
  'SearchAnalytics' : 1 } 
 AND DURABLE_WRITES = false ;

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW [keyspace_name.]view_name 
  WITH table_options [ AND table_options ... ] ;
Learn more.

Examples

This section uses the cyclist_base and cyclist_by_age-mv.

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

Use a property map to specify new properties for compression or 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.

This example changes the keys property to NONE (the default is ALL) and changes the rows_per_partition property to 15.
ALTER MATERIALIZED VIEW cycling.cyclist_by_age 
WITH caching = { 
  'keys' : 'NONE', 
  'rows_per_partition' : '15' };

Viewing current materialized view properties

Use DESCRIBE MATERIALIZED VIEW to see all current properties.
DESCRIBE MATERIALIZED VIEW cycling.cyclist_by_age
A CQL executable script is displayed:
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';

ALTER ROLE

ALTER ROLE role_name 
  [ WITH [ PASSWORD = role_password ] 
  [ [ AND ] LOGIN = ( true | false ) ] 
  [ [ AND ] SUPERUSER = ( true | false ) ] 
  [ [ AND ] OPTIONS = { option_map } ] ] ;
Learn more.

Example

Change the password for coach:
ALTER ROLE sandy WITH PASSWORD='bestTeam';

ALTER SEARCH INDEX CONFIG

ALTER SEARCH INDEX CONFIG ON [keyspace_name.]table_name
  ( ADD element_path [ attribute_list ] WITH $$ json_map $$
  | SET element_identifier = 'value'
  | SET shortcut = value
  | DROP element_identifier
  | DROP shortcut ) ;
Learn more.

Examples

The search index configuration is altered for the wiki.solr keyspace and table, and the specified options.

Enable encryption on search index

Enable encryption on search index:
  1. Change the configuration schema:
    ALTER SEARCH INDEX CONFIG
    ON cycling.comments 
    SET directoryFactory = 'encrypted';
  2. Verify the change is correct in the pending schema:
    DESC PENDING SEARCH INDEX CONFIG ON cycling.comments
  3. Make the configuration active:
    <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <config>
      <luceneMatchVersion>LUCENE_6_0_1</luceneMatchVersion>
      <dseTypeMappingVersion>2</dseTypeMappingVersion>
      <directoryFactory class="solr.EncryptedFSDirectoryFactory" name="DirectoryFactory"/>
      <indexConfig>
        <rt>false</rt>
      </indexConfig>
      <jmx/>
      <updateHandler>
        <autoSoftCommit>
          <maxTime>10000</maxTime>
        </autoSoftCommit>
      </updateHandler>
      <query>
        <filterCache class="solr.SolrFilterCache" highWaterMarkMB="2048" lowWaterMarkMB="1024"/>
        <enableLazyFieldLoading>true</enableLazyFieldLoading>
        <useColdSearcher>true</useColdSearcher>
        <maxWarmingSearchers>16</maxWarmingSearchers>
      </query>
      <requestDispatcher>
        <requestParsers enableRemoteStreaming="true" multipartUploadLimitInKB="2048000"/>
        <httpCaching never304="true"/>
      </requestDispatcher>
      <requestHandler class="solr.SearchHandler" default="true" name="search"/>
      <requestHandler class="com.datastax.bdp.search.solr.handler.component.CqlSearchHandler" name="solr_query"/>
      <requestHandler class="solr.UpdateRequestHandler" name="/update"/>
      <requestHandler class="solr.UpdateRequestHandler" name="/update/csv" startup="lazy"/>
      <requestHandler class="solr.UpdateRequestHandler" name="/update/json" startup="lazy"/>
      <requestHandler class="solr.FieldAnalysisRequestHandler" name="/analysis/field" startup="lazy"/>
      <requestHandler class="solr.DocumentAnalysisRequestHandler" name="/analysis/document" startup="lazy"/>
      <requestHandler class="solr.admin.AdminHandlers" name="/admin/"/>
      <requestHandler class="solr.PingRequestHandler" name="/admin/ping">
        <lst name="invariants">
          <str name="qt">search</str>
          <str name="q">solrpingquery</str>
        </lst>
        <lst name="defaults">
          <str name="echoParams">all</str>
        </lst>
      </requestHandler>
      <requestHandler class="solr.DumpRequestHandler" name="/debug/dump">
        <lst name="defaults">
          <str name="echoParams">explicit</str>
          <str name="echoHandler">true</str>
        </lst>
      </requestHandler>
    </config>
  4. Apply the configuration and rebuild the index:
    RELOAD SEARCH INDEX
    ON cycling.comments;

Auto soft commit max time

To set index configuration with shortcut for automatic soft commit max time to 10000ms:
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 10000;
Make the pending changes active (use DESCRIBE SEARCH INDEX to review changes):
RELOAD SEARCH INDEX
ON cycling.comments;

Request handler

Add a configuration 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 the search index:
RELOAD SEARCH INDEX
ON cycling.comments;
which adds the requestHandler element to the config:
<requestHandler name="/elevate" class="solr.SearchHandler"> 
   <lst name="defaults">
      <str name="echoParams">explicit</int>
   </lst>
   <arr name="last-components">
    <str>elevator</str>
   </arr>
</requestHandler>
To extend TieredMergePolicy to support automatic removal of deletes:
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].bool[@name='mergeSingleSegments'] = true;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='maxMergedSegmentMB'] = 1005;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='forceMergeDeletesPctAllowed'] = 25;
Reload the search index:
RELOAD SEARCH INDEX
ON cycling.comments;

Change the auto-commit time

To change the automatic commit time, ALTER SEARCH INDEX CONFIG. For example:
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 1000;

ALTER SEARCH INDEX SCHEMA

ALTER SEARCH INDEX SCHEMA ON [keyspace_name.]table_name
  ( ADD field column_name
  | ADD element_path [ attribute_list ] WITH $$ json_map $$
  | SET element_identifier = 'value'
  | DROP field field_name
  | DROP element_identifier ) ;
Learn more.

Examples

The search index schema is altered for the cycling.comments keyspace and table, and the specified options.

For extensive information and examples on search indexes, including adding and dropping search index fields, field types, field classes, tuples, UDTs, and map columns, see Managing search index fields.

You must add the search index before you can alter it.

Add a new field using the element path and attribute list

Fields that do not exist in the table can be added to index to combine multiple columns into a single indexed field for searches.
Note: Adding the leading element fields. in ADD fields.field fieldname is optional and provides only cosmetic structure.
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD fields.field[@name='fieldname', @type='StrField', @multiValued = 'false', @indexed='true'];
To apply the schema changes:
RELOAD SEARCH INDEX
ON cycling.comments;

Add a table column to the index

Automatically creates a field definition and adds the field type if required for a field that is not already indexed.
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD FIELD record_id;
To apply the schema changes and rebuild the index:
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Change a field name

DSE maps CQL columns to search index fields by matching the column name to the field name. Use unmapped fields for copy fields. If the field does not already exist it is added.
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@name = 'anotherFieldName';

To apply the schema changes:

To apply the schema changes and rebuild the index:
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Change the field type

Change the field type to another type that is already defined in the schema:
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@type = 'UUIDField';

To apply the schema changes:

To apply the schema changes and rebuild the index:
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Drop a field

To remove a field from the search index, but not from the table:
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
DROP field anotherFieldName;
To apply the schema changes and rebuild the index:
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';
To apply the schema changes and rebuild the index:
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'];
To apply the schema changes and rebuild the index:
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'];
Note: Ensure your dynamic field name has a leading or a trailing asterisk character.
To apply the schema changes and rebuild the index:
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

ALTER TABLE

ALTER TABLE [keyspace_name.]table_name 
  [ ADD ( column_definition | column_definition_list ) [ , ... ] ]
  [ DROP column_name [ , ... ] ]
  [ RENAME column_name TO column_name ]
  [ WITH table_properties [ , ... ] ] ;
Learn more.

Examples

This section uses the cyclist_races table.

Adding a column

To add a column, use the ADD instruction:
ALTER TABLE cycling.cyclist_races 
  ADD manager UUID;
To add a column of a collection type:
ALTER TABLE cycling.cyclist_races 
  ADD completed list<text>;

This operation does not validate the existing data.

Restriction: You cannot use the ADD instruction to add:
  • A column with the same name as an existing column
  • A static column if the table has no clustering columns.

Dropping a column

To remove a column from the table, use the DROP instruction:
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. To force the removal of dropped columns before compaction occurs, use ALTER TABLE to update the metadata, and then run nodetool upgradesstables to put the drop into effect.

Restriction:
  • If you drop a column then re-add it, DataStax Enterprise does not restore the values written before the column was dropped.
  • Do not re-add a dropped column that contained timestamps generated by a client; you can re-add columns with timestamps generated by the write time facility.

Renaming a column

To rename a column in the race_times table:
ALTER TABLE cycling.race_times 
  RENAME race_date TO date;
Restriction: The following restrictions apply to RENAME:
  • You can only rename clustering columns, which are part of the primary key.
  • You cannot rename the partition key because the partition key determines the data storage location on a node. If a different partition name is required, the table must be recreated and the data migrated.
    Note: There are many restrictions when using RENAME because SSTables are immutable. To change the state of the data on disk, everything must be rewritten.
  • You can index a renamed column.
  • You cannot rename a column if an index has been created on it.
  • You cannot rename a static column.

Modifying table properties

To change an existing table's properties, use ALTER TABLE and WITH. You can specify a:
For example, to add a comment to the cyclist_base table using WITH:
ALTER TABLE cycling.cyclist_base
WITH comment = 'basic cyclist information';

Enclose a text property value in single quotation marks.

Modifying compression and compaction

Use a property map to alter the comments table's compression or compaction setting:
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.

CAUTION: If you change the compaction strategy of a table with existing data, the database rewrites all existing SSTables using the new strategy. This can take hours, which can be a major problem for a production system. For strategies to minimize this disruption, see How to change the compaction strategy on a production cluster and Impact of Changing Compaction Strategy.

Changing caching

Set the number of rows per partition to store in the row cache for the comments table to 10 rows:
ALTER TABLE cycling.comments 
   WITH caching = {
    'keys': 'NONE', 
    'rows_per_partition': 10 };

Change the speculative retries

Modify the cyclist_base table to 95th percentile for speculative retry:
ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '95percentile';
Modify the cyclist_base table to use 10 milliseconds for speculative retry:
ALTER TABLE cycling.cyclist_base
WITH speculative_retry = '10ms';

Enabling and disabling background compaction

The following example sets the enabled property to false to disable background compaction:
ALTER TABLE cycling.comments 
WITH COMPACTION = {
   'class': 'SizeTieredCompactionStrategy', 
   'enabled': 'false' };
Warning: Disabling background compaction can be harmful: without it, the database does not regain disk space, and could allow zombies to propagate. Although compaction uses I/O, it is better to leave it enabled in most cases.

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.

Important: If you enable extended compaction logging for any table on any node, it is enabled for all tables on all nodes in the cluster.

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

The compaction logging service logs detailed information about the following types of compaction events:
  • 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

Use DESCRIBE or DESC to view the table definition.
DESC cycling.comments
The table details including the column names are returned.
CREATE TABLE cycling.comments (
    id uuid,
    created_at timestamp,
    comment text,
    commenter text,
    record_id timeuuid,
    solr_query text,
    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', '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';

ALTER TYPE

ALTER TYPE field_name 
  ( ADD field_name cql_datatype
  | RENAME field_name TO new_field_name [ AND field_name TO new_field_name ...] ) ;
Learn more.

Examples

This section uses the fullname type.

Adding a field

To add a new field to a user-defined type, use ALTER TYPE and the ADD keyword. For existing UDTs, the field value is null.
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.

Remove name from all the field names the cycling.fullname UDT.
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
The new field names appear in the description.
CREATE TYPE cycling.fullname (
    first text,
    last text,
    middle text
);

BATCH

BEGIN [ ( UNLOGGED | LOGGED ) ] BATCH 
  [ USING TIMESTAMP [ epoch_microseconds ] ]
  dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] ;
  [ dml_statement [ USING TIMESTAMP [ epoch_microseconds ] ] [ ; ... ] ] ;
  APPLY BATCH ;
Learn more.

Examples

This section uses the cyclist_expenses and popular_count tables.

Applying a client supplied timestamp to all DMLs

Insert meals paid for Vera Adrian using the user-defined date when inserting the records:
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;
Note: Combining two statements for the same partition results in a single table mutation.
View the records vertically in cqlsh:
EXPAND ON
Verify that the timestamps are all the same:
SELECT
  cyclist_name, expense_id,
  amount, WRITETIME(amount),
  description, WRITETIME(description),
  paid, WRITETIME(paid)
FROM
  cycling.cyclist_expenses
WHERE
  cyclist_name = 'Vera ADRIAN';
Both records were entered with the same timestamp.
@ 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.

The following example shows batching of conditional updates. The first statement uses the IF NOT EXISTS conditional clause.
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;
Conditional batches cannot provide custom timestamps. UPDATE and DELETE statements within a conditional batch cannot use IN conditions to filter rows.

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

Batching counter updates

A batch of counters should use the COUNTER option because, unlike other writes in DataStax Enterprise, a counter update is not an idempotent operation.
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.

COMMIT SEARCH INDEX

COMMIT SEARCH INDEX ON [keyspace_name.]table_name ;
Learn more.

Examples

The search index is committed for the comments search index table.
COMMIT SEARCH INDEX
ON cycling.comments;

CREATE AGGREGATE

CREATE [ OR REPLACE ] AGGREGATE [ IF NOT EXISTS ] 
  [keyspace_name.]aggregate_name (cql_type)
  SFUNC udf_name 
  STYPE cql_type
  FINALFUNC udf_name 
  INITCOND init_value
  [ DETERMINISTIC ] ;
Learn more.

Examples

Create an aggregate that calculates average in the cycling keyspace and test on the team_average.
Note: This section explains how to create a function that has the same functionality as the native AVG function.
  1. Create a function with a state parameter as a tuple that counts the rows (by incrementing 1 for each record) in the first position and finds the total by adding the current row value to the existing subtotal the second position, and returns the updated state.
    CREATE OR REPLACE FUNCTION cycling.avgState (
      state tuple<int, bigint>,
      val int
    ) 
      CALLED ON NULL INPUT 
      RETURNS tuple<int, bigint> 
      LANGUAGE java AS 
        $$
          if (val != null) { 
            state.setInt(0, state.getInt(0) + 1); 
            state.setLong(1, state.getLong(1) + val.intValue()); 
          } 
          return state; 
        $$
    ;
    Note: Use a simple test to verify that your function works properly.
    CREATE TABLE cycling.test_avg (
      id int PRIMARY KEY,
      state frozen<tuple<int, bigint>>,
      val int
    );
    
    INSERT INTO cycling.test_avg (
      id, state, val
    ) VALUES (
      1, (6, 9949), 51
    );
    
    INSERT INTO cycling.test_avg (
      id, state, val
    ) VALUES (
      2, (79, 10000), 9999
    );
    
    SELECT state, avgstate(state, val), val
    FROM cycling.test_avg;

    The first value was incremented by one and the second value is the results of the initial state value and val.

     state       | cycling.avgstate(state, val) | val
    -------------+------------------------------+------
       (6, 9949) |                   (7, 10000) |   51
     (79, 10000) |                  (80, 19999) | 9999
  2. Create a function that divides the total value for the selected column by the number of records.
    CREATE OR REPLACE FUNCTION cycling.avgFinal (
      state tuple<int,bigint>
    ) 
      CALLED ON NULL INPUT 
      RETURNS double 
      LANGUAGE java AS 
        $$
          double r = 0; 
          if (state.getInt(0) == 0) return null; 
          r = state.getLong(1); 
          r /= state.getInt(0); 
          return Double.valueOf(r);
        $$ 
    ;
  3. Create the user-defined aggregate to calculate the average value in the column:
    CREATE OR REPLACE AGGREGATE cycling.average (
      int
    ) 
      SFUNC avgState 
      STYPE tuple<int,bigint> 
      FINALFUNC avgFinal 
      INITCOND (0, 0)
    ;
  4. Test the function using a select statement.
    SELECT cycling.average(cyclist_time_sec)
    FROM cycling.team_average 
    WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team' 
      AND race_title = 'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe';

CREATE CUSTOM INDEX

CREATE CUSTOM INDEX [ IF NOT EXISTS ] [ index_name ]
  ON [keyspace_name.]table_name (column_name)
  USING 'org.apache.cassandra.index.sasi.SASIIndex' 
  [ WITH OPTIONS = { option_map } ] ;
Learn more.

Examples

All examples use the cycling.cyclist_name table.

Creating a SASI PREFIX index on a column

Create an SASI index on the column firstname:
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' };
The SASI mode CONTAINS must be specified.

Creating a SASI SPARSE index on a column

Define a table and then create an SASI index on the column age:
CREATE CUSTOM INDEX fn_sparse 
   ON cycling.comments (record_id) 
   USING 'org.apache.cassandra.index.sasi.SASIIndex'
   WITH OPTIONS = { 'mode': 'SPARSE' };
The SASI mode SPARSE must be specified. This mode is used for dense number columns that store timestamps or millisecond sensor readings.

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

Define a table, then create an SASI index on the column age:
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'};
Using the non-tokenizing analyzer is a method to specify case sensitivity or character case normalization without analyzing the specified column.

Creating a SASI analyzing index on a column

Define a table and then create an SASI index on the column comments:
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' }
;

CREATE FUNCTION

CREATE [ OR REPLACE ] FUNCTION [ IF NOT EXISTS ] [keyspace_name.]function_name (argument_list [ , ... ])
  ( CALLED | RETURNS NULL ) ON NULL INPUT RETURNS type 
  [ DETERMINISTIC ]
  [ MONOTONIC [ ON argument_name ] ]
  LANGUAGE ( java | javascript ) AS $$ code_block $$ ;
Learn more.

Examples

Use Java to create FLOG function

Overwrite or create the fLog function that computes the logarithm of an input value. CALLED ON NULL INPUT ensures that the function will always be executed.
CREATE OR REPLACE FUNCTION cycling.fLog (
  input double
) 
  CALLED ON NULL INPUT 
  RETURNS double 
  LANGUAGE java AS 
    $$
      return Double.valueOf(Math.log(input.doubleValue()));
    $$ 
;

Use Javascript to create SQL-like LEFT function

Create a function that returns the first N characters from a text field in Javascript. RETURNS NULL ON NULL INPUT ensures that if the input value is null then the function is not executed.
CREATE OR REPLACE FUNCTION cycling.left (
  column text, num int
) 
  RETURNS NULL ON NULL INPUT 
  RETURNS text 
  LANGUAGE javascript AS 
    $$
      column.substring(0, num)
    $$
;
Use the function in requests:
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

CREATE INDEX

CREATE INDEX [ IF NOT EXISTS ] index_name
  ON [keyspace_name.]table_name 
  ([ ( KEYS | FULL ) ] column_name) 
  (ENTRIES column_name) ;
Learn more.

Examples

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

You can create an index on map collection keys. If an index of the map values of the collection exists, drop that index before creating an index on the map collection keys. Assume a cyclist table contains this map data:
{'nation':'CANADA' }
The map key is located to the left of the colon, and the map value is located to the right of the colon.

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.

To index collection entries, use the ENTRIES keyword and map name in nested parentheses:
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'}
Use the same index to find cyclists from the same country.
SELECT *
FROM cycling.birthday_list
WHERE blist['nation'] = 'NETHERLANDS';
 cyclist_name  | blist
---------------+--------------------------------------------------------------
 Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}

Creating an index on map values

To create an index on map values, use the VALUES keyword and map name in nested parentheses:
CREATE INDEX blist_values_idx
ON cycling.birthday_list ( VALUES(blist) );

To query the table, use a WHERE clause with the map name and the value it contains.

SELECT *
FROM cycling.birthday_list
WHERE blist CONTAINS 'NETHERLANDS';
 cyclist_name  | blist
---------------+--------------------------------------------------------------
 Luc HAGENAARS | {'age': '28', 'bday': '27/07/1987', 'nation': 'NETHERLANDS'}
   Toine POELS | {'age': '52', 'bday': '27/07/1963', 'nation': 'NETHERLANDS'}

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>>
);
To index collection entries, use the FULL keyword and collection name in nested parentheses. For example, index the frozen list rnumbers.
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 KEYSPACE

CREATE KEYSPACE [ IF NOT EXISTS ] keyspace_name 
  WITH REPLICATION = { replication_map }
  [ AND DURABLE_WRITES = ( true | false ) ] ;
Learn more.

Examples

Create a keyspace for a single node evaluation cluster

Create cycling keyspace on a single node evaluation cluster:
CREATE KEYSPACE cycling
WITH REPLICATION = { 
  'class' : 'SimpleStrategy', 
  'replication_factor' : 1 
};

Create a keyspace NetworkTopologyStrategy on an evaluation cluster

This example shows how to create a keyspace with network topology in a single node evaluation cluster.
CREATE KEYSPACE cycling 
WITH REPLICATION = { 
  'class' : 'NetworkTopologyStrategy', 
  'datacenter1' : 1
};
Note: datacenter1 is the default datacenter name. To display the datacenter name, use dsetool status.
dsetool status
Returns the data center name, rack name, host name and IP address.
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 the cycling keyspace in an environment with mutliple data centers

Set the replication factor for the Boston, Seattle, and Tokyo datacenters. The data center name must match the name configured in the snitch.
CREATE KEYSPACE "Cycling"
WITH REPLICATION = {
  'class' : 'NetworkTopologyStrategy', 
  'boston'  : 3 , // Datacenter 1 
  'seattle' : 2 , // Datacenter 2
  'tokyo'   : 2   // Datacenter 3
};
Note: For more about replication strategy options, see Changing keyspace replication strategy

Disabling durable writes

Disable write commit log for the cycling keyspace. Disabling the commit log increases the risk of data loss. Do not disable in SimpleStrategy environments.
CREATE KEYSPACE cycling
WITH REPLICATION = { 
  'class' : 'NetworkTopologyStrategy',
  'datacenter1' : 3 
} 
AND DURABLE_WRITES = false;

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] [keyspace_name.]view_name
  AS SELECT [ (column_list) ]
  FROM [keyspace_name.]table_name
  [ WHERE column_name IS NOT NULL
  [ AND column_name IS NOT NULL ... ] ]
  [ AND relation [ AND ... ] ] 
  PRIMARY KEY ( column_list )
  [ WITH [ table_properties ]
  [ [ AND ] CLUSTERING ORDER BY (cluster_column_name order_option) ] ] ;
Learn more.

Examples

This section shows example scenarios that illustrate the use of materialized views.

Basic examples of materialized views

These tables are used in the first example scenario:
CREATE TABLE IF NOT EXISTS cycling.cyclist_base (
  cid UUID PRIMARY KEY,
  name text,
  age int,
  birthday date,
  country text
);
CREATE TABLE IF NOT EXISTS cycling.cyclist_base_ext (
  cid UUID, 
  name text, 
  age int, 
  birthday date, 
  country text,
  PRIMARY KEY (cid, age, birthday)
);
The following materialized view cyclist_by_age uses the base table cyclist_base. The WHERE clause ensures that only rows whose age and cid columns are non-NULL are added to the materialized view. In the materialized view, age is the partition key, and cid is the clustering column. In the base table, cid is the partition key.
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 caching = { 'keys' : 'ALL', 'rows_per_partition' : '100' }
  AND comment = 'Based on table cyclist';
The results of this query:
SELECT *
FROM cycling.cyclist_by_age;
are:
 age | cid                                  | birthday   | country       | name
-----+--------------------------------------+------------+---------------+---------------------
  28 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 |   Netherlands |   Steven KRUIKSWIJK
  19 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |     Australia |       Kanden GROVES
  19 | 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |    Uzbekistan | Iskandarbek SHODIEV
  18 | 15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 | United States |        Adrien COSTA
  18 | 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 |   Netherlands |         Bram WELTEN
  18 | ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 |   Netherlands |    Pascal EENKHOORN
  22 | e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 |   New Zealand |          Alex FRAME
  27 | c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 |        Brazil |     Cristian EGIDIO
  27 | d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 |       Germany |     Johannes HEIDER
  20 | 862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 |       Denmark |       Joakim BUKDAL
  38 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 |         Italy |     Paolo TIRALONGO
  29 | 96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 |     Australia |     Benjamin DYBALL

(12 rows)
The following materialized view cyclist_by_birthday_and_age uses the base table cyclist_base. The WHERE clause ensures that only rows whose age, birthday, and cid columns are non-NULL are added to the materialized view. In the materialized view, cid is the partition key, birthday is the first clustering column, and age is the second clustering column. In the base table, cid is the partition key.
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age AS
  SELECT age, cid, birthday, country, name
  FROM cycling.cyclist_base
  WHERE age IS NOT NULL
    AND birthday IS NOT NULL
    AND cid IS NOT NULL
  PRIMARY KEY (cid, birthday, age);
The results of this query:
SELECT *
FROM cycling.cyclist_by_birthday_and_age;
are:
 cid                                  | birthday   | age | country       | name
--------------------------------------+------------+-----+---------------+---------------------
 ffdfa2a7-5fc6-49a7-bfdc-3fcdcfdd7156 | 1997-02-08 |  18 |   Netherlands |    Pascal EENKHOORN
 15a116fc-b833-4da6-ab9a-4a7775752836 | 1997-08-19 |  18 | United States |        Adrien COSTA
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | 1993-06-18 |  22 |   New Zealand |          Alex FRAME
 c9c9c484-5e4a-4542-8203-8d047a01b8a8 | 1987-09-04 |  27 |        Brazil |     Cristian EGIDIO
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 |    Uzbekistan | Iskandarbek SHODIEV
 d1aad83b-be60-47a4-bd6e-069b8da0d97b | 1987-09-04 |  27 |       Germany |     Johannes HEIDER
 862cc51f-00a1-4d5a-976b-a359cab7300e | 1994-09-04 |  20 |       Denmark |       Joakim BUKDAL
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |     Australia |       Kanden GROVES
 18f471bf-f631-4bc4-a9a2-d6f6cf5ea503 | 1997-03-29 |  18 |   Netherlands |         Bram WELTEN
 220844bf-4860-49d6-9a4b-6b5d3a79cbfb | 1977-07-08 |  38 |         Italy |     Paolo TIRALONGO
 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | 1987-06-07 |  28 |   Netherlands |   Steven KRUIKSWIJK
 96c4c40d-58c8-4710-b73f-681e9b1f70ae | 1989-04-20 |  29 |     Australia |     Benjamin DYBALL

(12 rows)
The following materialized view cyclist_by_country_and_birthday uses the base table cyclist_base. The WHERE clause ensures that only rows whose birthday and cid columns are non-NULL and country equals Australia are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_country_and_birthday AS
  SELECT age, cid, birthday, country, name
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL
    AND cid IS NOT NULL
    AND country = 'Australia'
  PRIMARY KEY (cid, country, birthday);
The results of this query:
SELECT *
FROM cycling.cyclist_by_country_and_birthday;
are:
 cid                                  | country   | birthday   | age | name
--------------------------------------+-----------+------------+-----+-----------------
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | 1998-12-23 |  19 |   Kanden GROVES
 96c4c40d-58c8-4710-b73f-681e9b1f70ae | Australia | 1989-04-20 |  29 | Benjamin DYBALL

(2 rows)
The following materialized view cyclist_by_birthday_and_age19 uses the base table cyclist_base. The WHERE clause ensures that only rows whose birthday and cid columns are non-NULL and age equals the value 19 are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_birthday_and_age19 AS
  SELECT age, cid, birthday, country, name
  FROM cycling.cyclist_base
  WHERE birthday IS NOT NULL
    AND cid IS NOT NULL
    AND age = 19
  PRIMARY KEY (cid, birthday, age);
The results of this query:
SELECT *
FROM cycling.cyclist_by_birthday_and_age19;
are:
 cid                                  | birthday   | age | country    | name
--------------------------------------+------------+-----+------------+---------------------
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 | Uzbekistan | Iskandarbek SHODIEV
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |  Australia |       Kanden GROVE

(2 rows)
The following materialized view cyclist_by_age_birthday_cid uses the base table cyclist_base_ext. The WHERE clause ensures that only rows whose age, birthday, and cid columns are non-NULL are added to the materialized view.
CREATE MATERIALIZED VIEW cycling.cyclist_by_age_birthday_cid AS
  SELECT age, cid, birthday, country, name
  FROM cycling.cyclist_base_ext
  WHERE age IS NOT NULL
    AND birthday IS NOT NULL
    AND cid IS NOT NULL
  PRIMARY KEY (age, birthday, cid);
The results of this query:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19;
are:
 cid                                  | birthday   | age | country    | name
--------------------------------------+------------+-----+------------+---------------------
 410919ef-bd1b-4efa-8256-b0fd8ab67029 | 1999-01-04 |  19 | Uzbekistan | Iskandarbek SHODIEV
 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | 1998-12-23 |  19 |  Australia |       Kanden GROVE

(2 rows)
The results of this query using a WHERE clause for two values:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE age = 19
  AND birthday = '1998-12-23';
are:
 age | birthday   | cid                                  | country   | name
-----+------------+--------------------------------------+-----------+---------------
  19 | 1998-12-23 | 1c526849-d3a2-42a3-bcf9-7903c80b3d16 | Australia | Kanden GROVES

(1 rows)
Notice that clustering columns must still be included in order. This query violates the rule:
SELECT *
FROM cycling.cyclist_by_age_birthday_cid
WHERE birthday = '1998-12-23';
Result:
cyclist_by_age-mv.cql:195:InvalidRequest: Error from server: code=2200 [Invalid query] message="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.

Using a materialized view to perform queries that are not possible on a base table

The following scenario shows how to use a materialized view to perform queries that are not possible on a base table unless ALLOW FILTERING is used. ALLOW FILTERING is not recommended because of the performance degradation. This table stores the cycling team mechanic information:
CREATE TABLE IF NOT EXISTS cycling.mechanic (
  emp_id int,
  dept_id int,
  name text,
  age int,
  birthdate date,
  PRIMARY KEY (emp_id, dept_id)
);
The table contains these rows:
 emp_id | dept_id | age | birthdate  | name
--------+---------+-----+------------+------------
      5 |       3 |  25 | 1996-10-04 | Lisa SMITH
      1 |       1 |  21 | 1992-06-18 | Fred GREEN
      2 |       1 |  22 | 1993-01-15 | John SMITH
      4 |       2 |  24 | 1995-08-19 | Jack JONES
      3 |       2 |  23 | 1994-02-07 |   Jane DOE

(5 rows)
This materialized view selects the columns from the previous table and contains a different primary key from the table:
CREATE MATERIALIZED VIEW cycling.mechanic_view AS
  SELECT emp_id, dept_id, name, age, birthdate
  FROM cycling.mechanic
  WHERE emp_id IS NOT NULL
    AND dept_id IS NOT NULL
    AND name IS NOT NULL
    AND age IS NOT NULL
    AND birthdate IS NOT NULL
  PRIMARY KEY (age, emp_id, dept_id);
This query retrieves the rows where the age is 21:
SELECT *
FROM cycling.mechanic_view
WHERE age = 21;
The previous query cannot be run on the base table without ALLOW FILTERING. The output from the previous query is as follows:
 age | emp_id | dept_id | birthdate  | name
-----+--------+---------+------------+------------
  21 |      1 |       1 | 1992-06-18 | Fred GREEN

(1 rows)

CREATE ROLE

CREATE ROLE [ IF NOT EXISTS ] role_name
  [ WITH [ SUPERUSER = ( true | false ) ] 
  [ [ AND ] LOGIN = ( true | false ) ] 
  [ [ AND ] PASSWORD = role_password ] 
  [ [ AND ] OPTIONS = { option_map } ] ] ;
Learn more.

Examples

Creating a login account

  1. Create a login role for coach.
    CREATE ROLE coach 
      WITH PASSWORD = 'All4One2day!' 
      AND LOGIN = true;
    Internal authentication requires the role to have a password.
  2. Verify that the account works by logging in:
    LOGIN coach
  3. Enter the password at the prompt.
    Password: 
  4. The cqlsh prompt includes the role name:
    coach@cqlsh>

Creating a role

A best practice when using internal authentication is to create separate roles for permissions and login accounts. Once a role has been created it can be assigned as permission to another role, see GRANT for more details. Roles for externally authenticators users are mapped to the user's group name; LDAP mapping is case sensitive.

Create a role for the cycling keyspace administrator, that is a role that has full permission to only the cycling keyspace.

  1. Create the role:
    CREATE ROLE cycling_admin;
    At this point the role has no permissions. Manage permissions using GRANT and REVOKE.
    Note: A role can only modify permissions of another role and can only modify (GRANT or REVOKE) role permissions that it also has.
  2. Assign the role full access to the cycling keyspace:
    GRANT ALL PERMISSIONS on KEYSPACE cycling to cycling_admin;
  3. Now assign the role to the coach.
    GRANT cycling_admin TO coach;
    This allows you to manage the permissions of all cycling administrators by modifying the cycling_admin role.
  4. View the coach's permissions.
    LIST ALL PERMISSIONS OF coach;

Changing a password

A role can change the password to itself, or another role that it has permission to modify. A superuser can change the password of any role. Use ALTER to change a role's password:
ALTER ROLE sandy WITH PASSWORD='bestTeam';

CREATE SEARCH INDEX

CREATE SEARCH INDEX [ IF NOT EXISTS ] ON [keyspace_name.]table_name
  [ WITH [ COLUMNS column_list { option : value } [ , ... ] ]
  [ [ AND ] PROFILES profile_name [ , ... ] ]
  [ [ AND ] CONFIG { option:value } [ , ... ] ]
  [ [ AND ] OPTIONS { option:value } [ , ... ] ] ] ;
Learn more.

Examples

The search index is created with the wiki.solr keyspace and table, and the specified options.

Create search index if it does not exist
CREATE SEARCH INDEX IF NOT EXISTS
ON wiki.solr
WITH COLUMNS id, body {excluded : false};
Create real time (RT) search index, but don't reindex the data
CREATE SEARCH INDEX
ON wiki.solr
WITH CONFIG { realtime:true }
AND OPTIONS { reindex : false };

Create search index with transparent data encryption (TDE)

CREATE SEARCH INDEX IF NOT EXISTS
ON wiki.solr
WITH COLUMNS c1,c2 {docValues:true}
AND PROFILES spaceSavingAll
AND CONFIG {directoryFactory:'encrypted'};
Create search index with docValues set for all columns with a supported type
CREATE SEARCH INDEX
ON wiki.solr
WITH COLUMNS * { docValues:true };
Create search index to specify the columns to include and exclude from index
CREATE SEARCH INDEX
ON wiki.solr
WITH COLUMNS field1 { indexed:true }, field2 { indexed:false };

Non-indexed columns are included in present in HTTP query results and single pass query results. To exclude, use the excluded option.

Create search index with controls for tuple and UDT fields

CREATE SEARCH INDEX
ON wiki.solr
WITH COLUMNS tuplefield.field1 {docValues:true};
Parent fields are included since the subfield is selected for inclusion.

Create search index to specify the columns to exclude from HTTP query results and single pass queries

CREATE SEARCH INDEX
ON wiki.solr
WITH COLUMNS field1 { excluded:true }, field2 { excluded:false };
Excluded columns are not present in HTTP query results, but non-indexed columns are included.

Create search index with space saving no join option

CREATE SEARCH INDEX
ON wiki.solr
WITH PROFILES spaceSavingNoJoin;

The example avoids indexing the _partitionKey field. See Identifying the partition key.

CREATE TABLE

CREATE TABLE [ IF NOT EXISTS ] [keyspace_name.]table_name
  ( column_definition [ , ... ] | PRIMARY KEY (column_list) )
  [ WITH [ table_options ]
  [ [ AND ] CLUSTERING ORDER BY [ clustering_column_name order ] ] 
  [ [ AND ] ID = 'table_hash_tag' ] ] ;
Learn more.

cassandra.yaml

The location of the cassandra.yaml file depends on the type of installation:
Package installations /etc/dse/cassandra/cassandra.yaml
Tarball installations installation_location/resources/cassandra/conf/cassandra.yaml

Creating a table with a frozen UDT

Create the race_winners table that has a frozen user-defined type (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 the cyclist_name 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 the cyclist_category table and store the data in reverse order:
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 a table that is optimized for query by cyclist rank by year:
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 a change data capture log for the cyclist_id table:
CREATE TABLE cycling.cyclist_id (
  lastname text,
  firstname text,
  age int,
  id UUID,
  PRIMARY KEY ((lastname, firstname), age)
);
CDC logging must be enabled in cassandra.yaml.
CAUTION: Before enabling CDC logging, have a plan for moving and consuming the log information. After the disk space limit is reached, writes to CDC-enabled tables are rejected until more space is freed. See Change-data-capture (CDC) space settings for information about available CDC settings.

Storing data in descending order

The following example shows a table definition that stores the categories with the highest points first.
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

Recreate a table with its original ID to facilitate restoring table data by replaying commit logs:
CREATE TABLE cycling.cyclist_emails (
  userid text PRIMARY KEY,
  id UUID,
  emails set<text>
) WITH ID='1bb7516e-b140-11e8-96f8-529269fb1459';

CREATE TYPE

CREATE TYPE [ IF NOT EXISTS ] [keyspace_name].type_name
  (field_name cql_datatype [ , field_name cql_datatype ... ]) ;
Learn more.

Example

This example creates a user-defined type cycling.basic_info that consists of personal data about an individual cyclist.

CREATE TYPE cycling.basic_info ( 
    birthday timestamp, 
    nationality text, 
    height text,
    weight text
     );

After defining the UDT, you can create a table that has columns with the UDT. CQL collection columns and other columns support the use of user-defined types, as shown in Using CQL examples.

This example creates a user-defined type (UDT) cycling.basic_info_withTTL that consists of personal data about an individual cyclist, which includes the next_race column that will be set with a timestamp and time-to-live (TTL).
CREATE TYPE cycling.basic_info_expire IF NOT EXISTS (
  birthday timestamp,
  nationality text,
  height text,
  weight text,
  next_race text
  ) ;
To insert an entire row of data into a table using a timestamp and TTL, specify the values with an INSERT command:
INSERT INTO cycling.basic_info_TTL_expire (id, lastname, basics)
  VALUES (e7ae5cf3-d358-4d99-b900-85902fda9bb0, 'FRAME',
  { birthday:'1993-06-18',nationality:'New Zealand',weight:'175',height:'72',next_race:'Amgen Tour of California' }) 
  USING TIMESTAMP 100 AND TTL 10000;
To insert a single value with a TTL, use a UPDATE command:
UPDATE cycling.basic_info_TTL_expire 
  USING TTL 100 
  SET basics.next_race='Tour de France' 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
To check the write time and TTL values of a UDT use the WRITETIME and TTL functions in a SELECT command:
SELECT WRITETIME(basics), TTL(basics) FROM cycling.basic_info_TTL_expire 
  WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0;
Note: A string with a single quote in a UDT text field is returned with two quotes in queries and COPY commands. For example, if you store the string Single ' quote in a UDT text field, the string is returned as Single '' Quote.

DELETE

DELETE [ column_name [ term ] [ , ... ] ]
  FROM [keyspace_name.]table_name 
  [ USING TIMESTAMP timestamp_value ]
  WHERE PK_column_conditions 
  [ ( IF EXISTS | IF static_column_conditions ) ] ;
Learn more.

Examples

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;
The table schema and populated table prior to deleting first and last name:
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)
Result:
 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;
Without IF EXISTS, the command proceeds with no standard output. If IF EXISTS returns True (if a row with this primary key does exist), standard output displays a table like the following:
 [applied]
-----------
      True
The cyclist named Alex Frame has been completely removed from the table.
 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';
The results show all the applicable data. The condition was not met because the last names did not match; therefore the operation was not applied.
 [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.

Add IF EXISTS to the command to ensure that an operation is not performed if the specified row does not exist:
DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'JONES' and firstname = 'Bram' 
IF EXISTS;
No such row exists so the conditions returns False and the command fails. In this case, standard output looks like:
 [applied]
-----------
     False
Use IF condition to apply tests to one or more column values in the selected row:
DELETE id FROM cycling.cyclist_id 
WHERE lastname = 'WELTEN' AND firstname = 'Bram' 
IF age = 20;
If all the conditions return True, the standard output is the same as if IF EXISTS returned True. If any of the conditions fails, standard output displays False in the [applied] column and also displays information about the condition that failed:
 [applied] | age
-----------+-----
     False |  18
CAUTION: Conditional deletions incur a non-negligible performance cost and should be used sparingly.

DROP AGGREGATE

DROP AGGREGATE [ IF EXISTS ] [keyspace_name.]aggregate_name [ (argument_name [ , ... ]) ] ;
Learn more.

Examples

Drop the avgState aggregate from the cycling keyspace.

DROP AGGREGATE IF EXISTS cycling.avgState;

DROP FUNCTION

DROP FUNCTION [ IF EXISTS ] [keyspace_name.]function_name [ (argument_name [ , ... ]) ] ;
Learn more.

Examples

Drops the UDF from the cycling keyspace.

DROP FUNCTION IF EXISTS cycling.fLog;

DROP INDEX

DROP INDEX [ IF EXISTS ] [keyspace_name.]index_name ;
Learn more.

Example

Drop the index rank_idx from the cycling.rank_by_year_and_name table.

DROP INDEX IF EXISTS cycling.rank_idx ;

DROP KEYSPACE

DROP KEYSPACE [ IF EXISTS ] keyspace_name ;
Learn more.

Example

Drop the cycling keyspace:
DROP KEYSPACE cycling IF EXISTS;

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW [ IF EXISTS ] [keyspace_name.]view_name ;
Learn more.

Example

Drop the cyclist_by_age materialized view from the cyclist keyspace.

DROP MATERIALIZED VIEW IF EXISTS cycling.cyclist_by_age;

DROP ROLE

DROP ROLE [ IF EXISTS ] role_name ;
Learn more.

Examples

Drop the team manager role.

DROP ROLE IF NOT EXISTS team_manager;

DROP SEARCH INDEX CONFIG

DROP SEARCH INDEX ON [keyspace_name.]table_name
  OPTIONS { option : value } [ , { option : value } ... ] ;
Learn more.

Examples

The search index is dropped for the wiki.solr keyspace and table, and the specified options.

Delete the resources associated with the search index

DROP SEARCH INDEX ON wiki.solr;

Keep the resources associated with the search index

DROP SEARCH INDEX ON wiki.solr WITH OPTIONS { deleteResources:false };

DROP TABLE

DROP TABLE [ IF EXISTS ] [keyspace_name.]table_name ;
Learn more.

Example

Drop the cyclist_name table:
DROP TABLE IF EXISTS cycling.cyclist_name;

DROP TYPE

DROP TYPE [ IF EXISTS ] [keyspace_name.]type_name ;
Learn more.

Examples

Attempting to drop a type that is in use by a table:
DROP TYPE IF EXISTS cycling.basic_info;
Error message with the table names that contain the type:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot drop user type cycling.basic_info as it is still used by table cycling.cyclist_stats"
Drop the table that uses the type:
DROP TABLE IF EXISTS cycling.cyclist_stats;
Drop the type:
DROP TYPE IF EXISTS cycling.basic_info;

GRANT

GRANT permission
  ON object
  TO role_name ;
Learn more.

Examples

In most environments, user authentication is handled by a plug-in that verifies users credentials against an external directory service such as LDAP. For simplicity, the following examples use internal users.

Manage object permissions

Use AUTHORIZE to allow a role to manage access control of specific resources.
  • Allow role to grant any permission type, including AUTHORIZE, on all objects in the cycling keyspace:
    GRANT AUTHORIZE ON KEYSPACE cycling TO cycling_admin;
    Warning: This makes the role a superuser in the cycling keyspace because roles can modify their own permissions as well as roles that they inherit permissions from.
  • Allow the sam role to assign permission to run queries and change data in the cycling keyspace:
    GRANT AUTHORIZE FOR SELECT, MODIFY ON KEYSPACE cycling TO sam;
    Tip: The sam role cannot grant other permissions such as AUTHORIZE, AUTHORIZE FOR ..., ALTER, CREATE, DESCRIBE, and DROP to another role.

Access to data resources

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

Give the role cycling_admin all permissions to the cycling keyspace:
GRANT ALL PERMISSIONS ON KEYSPACE cycling TO cycling_admin;
Give the role coach permission to perform SELECT statements and modify data on all tables in the cycling keyspace:
GRANT SELECT, MODIFY ON KEYSPACE cycling TO coach;
Give the role coach permission to perform ALTER KEYSPACE statements on the cycling keyspace, and also ALTER TABLE, CREATE INDEX, and DROP INDEX statements on all tables in the cycling keyspace:
GRANT ALTER ON KEYSPACE cycling TO coach;
Give the role martin permission to perform SELECT statements on rows that contain 'Sprint' in the cycling.cyclist_category table:
GRANT SELECT ON 'Sprint' ROWS IN cycling.cyclist_category TO martin;
Note: The filtering_data string is case-sensitive.

To view permissions see LIST PERMISSIONS.

INSERT

INSERT [ JSON ] INTO [keyspace_name.]table_name
  [ column_list VALUES column_values ] 
  [ IF NOT EXISTS ] 
  [ USING [ TTL seconds ] [ [ AND ] TIMESTAMP epoch_in_microseconds ] ] ;
Learn more.

Examples

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)

To insert data into a collection, enclose values in curly brackets. Set values must be unique. Insert a list of teams as a set for the cyclist VOS. The set is defined in the table as teams set<text>.
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 data into a map named teams that lists two recent team memberships for the cyclist VOS. The map is defined in the table as teams map<int, text>.
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; 
Without IF NOT EXISTS, the command proceeds with no standard output. If IF NOT EXISTS is included and there is no row with the same primary key, the command is performed and returns true in the [applied] column of the results. For example:
 [applied]
-----------
      True
With IF NOT EXISTS, if the row already exists, the command returns false in the [applied] column and returns the values for the existing row. For example:
 [applied] | id                                   | firstname | lastname
-----------+--------------------------------------+-----------+----------
     False | c4b65263-fe58-4846-83e8-f0e1c13d518f |  Rissella |    RATTO
Note: Using IF NOT EXISTS incurs a performance hit associated with using Paxos internally. For information about Paxos, see Linearizable consistency.

LIST PERMISSIONS

LIST ( ALL PERMISSIONS | permission_list )
  [ ON resource_name ]
  [ OF role_name ] 
  [ NORECURSIVE ] ;
Learn more.

Example

All permissions for all roles and resources

List permissions given to all the roles on all resources:
LIST ALL PERMISSIONS;

Individual role permissions

List all permissions given to sam:
LIST ALL PERMISSIONS OF sam;
Output is:
 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 the cyclist_name table:
LIST ALL PERMISSIONS ON cycling.cyclist_name;
Output is:
 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)

LIST ROLES

LIST ROLES 
  [ OF role_name ] 
  [ NORECURSIVE ] ;
Learn more.

Examples

All roles

Show all the roles that the current role has permission to describe.
LIST ROLES;

(Internal Role Management only) Roles assigned to a role

Show the roles for a particular role. Sufficient privileges are required to show this information.
LIST ROLES OF coach;
Returns only the roles assigned to martin:
 role          | super | login | options
---------------+-------+-------+---------
         coach | False | False |        {}
 cycling_admin | False |  True |        {}

(2 rows)

REBUILD SEARCH INDEX

REBUILD SEARCH INDEX ON [keyspace_name.]table_name
  [ WITH OPTIONS { deleteAll : ( true | false ) } ] ;
Learn more.

Examples

The search index is rebuilt for the wiki.solr keyspace and table.

REBUILD SEARCH INDEX ON wiki.solr WITH OPTIONS { deleteAll:true };

RELOAD SEARCH INDEX

RELOAD SEARCH INDEX ON [keyspace_name.]table_name ;
Learn more.

Examples

The search index schema and configuration is reloaded for the wiki.solr keyspace.

RELOAD SEARCH INDEX ON wiki.solr;

RESTRICT

RESTRICT permission
  ON [keyspace_name.]table_name 
  TO role_name ;
Learn more.

Examples

Prevent the role admin from seeing any data in the cycling keyspace:
RESTRICT MODIFY, SELECT ON KEYSPACE cycling TO role_admin;

RESTRICT ROWS

RESTRICT ROWS 
  ON [keyspace_name.]table_name 
  USING pk_column_name ;
Learn more.

Examples

For the cyclist_expenses table, configure the cyclist_name column for filtering so that permissions can be assigned. In this example, we identify the column so that each cyclist can view only their own expenses:

RESTRICT ROWS ON cyclist_expenses USING cyclist_name;
RLAC requires two commands: a single RESTRICT and one or more GRANT commands. For example, cyclist Vera Adrian can view her expenses:
GRANT SELECT ON 'Vera ADRIAN' ROWS IN cyclist_expenses TO cycling_accounts;
Note: The filtering_data string is case-sensitive.

REVOKE

REVOKE permission 
  ON resource_name
  FROM role_name ;
Learn more.

Example

The role couch can no longer perform queries or modify data in the cycling keyspace.
REVOKE SELECT, MODIFY ON KEYSPACE cycling 
FROM coach;
Restriction:

Because of inheritance, the user can perform SELECT queries on cycling.name if one of these conditions is met:

  • The user is a superuser.
  • The user has SELECT on ALL KEYSPACES permissions.
  • The user has SELECT on the cycling keyspace.
The role coach can no longer perform ALTER commands in the cycling keyspace:
REVOKE ALTER ON KEYSPACE cycling 
FROM coach;

SELECT

SELECT [ JSON ] selectors 
  FROM [keyspace_name.]table_name 
  [ WHERE [ primary_key_conditions ] [ AND ] [ index_conditions ]
  [ GROUP BY column_name [ , ... ] ]
  [ ORDER BY PK_column_name [ , ... ] ( ASC | DESC ) ] 
  [ ( LIMIT N | PER PARTITION LIMIT N ) ]
  [ ALLOW FILTERING ] ;
Learn more.

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

The column specification of the relation must be one of these:
  • One or more members of the partition key of the table.
  • A clustering column, only if the relation is preceded by other relations that specify all columns in the partition key.
  • A column that is indexed using CREATE INDEX.
Restriction: In the WHERE clause, refer to a column using the actual name, not an alias.

Filtering on the partition key

This table has id as the partition key (it is the only column in the primary key, and is therefore also the partition key by default):
CREATE TABLE cycling.cyclist_career_teams (
  id UUID PRIMARY KEY,
  lastname text,
  teams set<text>
);
This query includes the partition key id value in the WHERE clause:
SELECT id, lastname, teams 
FROM cycling.cyclist_career_teams 
WHERE id = 5b6962dd-3f90-4c93-8f61-eabfa4a803e2;
Restriction: A relation that references the partition key can only use an equality operator = or IN. For more details about the IN operator, see the Examples below.

This example table contains a more complex primary key:

CREATE TABLE 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 the primary key columns that precede the race column:
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 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)
It is possible to add ALLOW FILTERING to filter a non-indexed cluster column.
CAUTION: Avoid ALLOW FILTERING because it impacts performance.
The following table definition contains a clustering column named race_start_date and does not have a secondary index.
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
  )
) WITH CLUSTERING ORDER BY (
  race_start_date DESC, race_end_date DESC
);
Example query with ALLOW FILTERING:
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 secondary indexed column must include at least one equality relation to the indexed column. See Indexing a column.

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

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.

For example, examine the primary key in this table:
CREATE TABLE 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.

This table shows an example in which the race column is the last clustering column:
CREATE TABLE 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 with the equality condition for the race column (the last clustering column), an IN clause for the start_month column, and ALLOW FILTERING (avoid in a production environment):
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 DSE clusters using DSE 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';
Output:
 id                                   | lastname | teams
--------------------------------------+----------+----------------------------------------------------------------------------------------------------
 1c9ebc13-1eab-4ad5-be87-dce433216d40 |    BRAND | {'AA Drink - Leontien.nl', 'Leontien.nl', 'Rabobank-Liv Giant', 'Rabobank-Liv Woman Cycling Team'}

(1 rows)
The cyclist_teams table contains a map of int keys and text values named teams. The teams map keys are indexed:
CREATE INDEX team_year_idx
ON cycling.cyclist_teams ( KEYS (teams) );
The index allows a query to filter the map keys:
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

This example adds an index for map entries.
CREATE INDEX blist_idx
ON cycling.birthday_list ( ENTRIES(blist) );
Note: This method only works for maps.
This query finds all cyclists who are 23 years old based on their entry in the blist map in the birthday_list table.
SELECT *
FROM cycling.birthday_list
WHERE blist['age'] = '23';

Filtering a full frozen collection

The example in this section uses a table containing a FROZEN list collection named rnumbers. This statement creates an index, which is required for the query:
CREATE INDEX rnumbers_idx
ON cycling.race_starts ( FULL(rnumbers) );
This query retrieves the row that fully matches the collection's values, specifically a cyclist who has 39 Pro wins, 7 Grand Tour starts, and 14 Classic starts in 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.

This query constructs a filter that selects cycling calendar data whose start date is within a specified range and the race_id is 101. (If race_id were not a component of the primary key, you would need to create an index on race_id to use this query.)
SELECT * 
FROM cycling.calendar 
WHERE race_id = 101
  AND race_start_date >= '2014-05-27' 
  AND race_start_date < '2017-06-16';
Output:
 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

These restrictions apply when using an ORDER BY clause with a compound primary key:
  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.
  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.
See the cyclist_category table, which uses a compound primary key. This query retrieves the cyclist categories, in descending order by points.
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)
The following example shows a table with a more complex compound primary key.
CREATE TABLE 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)
This query has multiple clustering columns in the ORDER BY clause:
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.
  • 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.
The examples in this section use the race_times_summary table:
CREATE TABLE cycling.race_times_summary (
  race_date date,
  race_time time,
  PRIMARY KEY (race_date, race_time)
);
The table contains these rows:
 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)
This query groups the rows by the race_date column values:
SELECT
  race_date, race_time 
FROM
  cycling.race_times_summary
GROUP BY
  race_date;
Each set of rows with the same race_date column value are grouped together into one row in the query output. Three rows are returned because there are three groups of rows with the same race_date column value. The value returned is the first value that is found for the group.
 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)
This query groups the rows by race_date and FLOOR(race_time, 1h), which returns the hour. The number of rows in each group is returned by COUNT(*).
SELECT
  race_date, FLOOR(race_time, 1h), COUNT(*)
FROM
  cycling.race_times_summary
GROUP BY
  race_date, FLOOR(race_time, 1h);
Nine rows are returned because there are nine groups of rows with the same race_date and FLOOR(race_time, 1h) values:
 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 the built-in functions COUNT(), MIN(), MAX(), SUM(), and AVG() that return aggregate values to SELECT statements. You can also create user-defined aggregates (UDAs). 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;
In the output, notice that the columns are null, and one row is returned:
 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.

For example:
SELECT MAX(race_points) 
FROM cycling.cyclist_points
WHERE id = e3b19ec4-774a-4d1c-9e5a-decec1e30aac;
Output:
 system.max(race_points)
-------------------------
                     120

(1 rows)
Note: If you do not include a WHERE clause, a warning message is displayed:
Warnings :
Aggregation query used without partition key
The MIN function returns the minimum value:
 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;
Output:
 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;
Output:
 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.

This query returns the cyclist team average time using a user-defined aggregate (UDA).

SELECT cycling.average(cyclist_time_sec)
FROM cycling.team_average 
WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team' 
  AND race_title = 'Amgen Tour of California Women''s Race presented by SRAM - Stage 1 - Lake Tahoe > Lake Tahoe';
See Creating a user-defined aggregate function (UDA) and CREATE AGGREGATE.

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;
Output:
 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.

This example INSERT sets the TTL of the column values to 200 seconds:
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 = 'dummy' 
WHERE race_id = 200 
  AND race_start_date = '2015-05-27' 
  AND race_end_date = '2015-05-27';
This query retrieves the current TTL of the specified race_name column value:
SELECT TTL(race_name) 
FROM cycling.calendar 
WHERE race_id = 200;
Output:
 ttl(race_name)
----------------
            276

(1 rows)

Retrieving values in JSON format

See Retrieval using JSON

TRUNCATE

TRUNCATE [ TABLE ] [keyspace_name.]table_name ;
Learn more.

Examples

To remove all data from a table without dropping the table:
  1. If necessary, use the cqlsh CONSISTENCY command to set the consistency level to ALL.
  2. Use nodetool status or some other tool to make sure all nodes are up and receiving connections.
  3. Use TRUNCATE or TRUNCATE TABLE, followed by the table name.
    TRUNCATE cycling.country_flag;
  4. Use SELECT to verify the table data has been truncated.
    SELECT * from cycling.country_flag ;
     country | cyclist_name | flag
    ---------+--------------+------
    
    (0 rows)
Note: TRUNCATE sends a JMX command to all nodes to delete SSTables that hold the data from the specified table. If any of the nodes are down or do not respond, the command fails and outputs a message like the following:
Unable to complete request: one or more nodes were unavailable.

UNRESTRICT

UNRESTRICT permission_name
  ON [keyspace_name.]table_name 
  FROM role_name ; 
Learn more.

Examples

Remove the select restriction from the database admin on the cycling keyspace:
UNRESTRICT SELECT ON KEYSPACE cycling FROM db_admin;

UNRESTRICT ROWS

UNRESTRICT ROWS ON [keyspace_name.]table_name ; 
Learn more.

Examples

Remove row-level permissions from the currently selected column:
UNRESTRICT ROWS ON cyclist_name;

UPDATE

UPDATE [keyspace_name.]table_name
  [ USING TTL time_value ]
  [ [ AND ] USING TIMESTAMP timestamp_value ]
  SET assignment [ , assignment ... ] 
  WHERE row_specification
  [ IF EXISTS | IF condition [ AND condition ] ] ;
Learn more.

Examples

Updating a column

Update a column in several rows at once:
UPDATE cycling.cyclist_name
SET firstname = NULL
WHERE id IN (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2, fb372533-eb95-4bb4-8685-6ef61e994caa
);
Update multiple columns in a single row:
UPDATE cycling.cyclist_name
SET
  firstname = 'Marianne',
  lastname = 'VOS'
WHERE id = 88b8fd18-b1ed-4e96-bf79-4280797cba80;

Updating using a timestamp and TTL

To update a set (or any collection) value with a timestamp and TTL, specify the value:
UPDATE cycling.cyclist_sponsors_expire 
USING TIMESTAMP 200
AND TTL 20000 
SET sponsorship += {'Tag Heuer'} 
WHERE cyclist_name = 'PRIETO, Marcela';

Updating a counter column

To update a counter column value in a counter table, specify the increment or decrement to apply to the current value.
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.

USE

USE keyspace_name ;
Learn more.

Example

USE cycling;