CQL for Cassandra 2.x

Table properties

CQL supports Cassandra table properties, such as comments and compaction options, listed in the following table.

In CQL commands, such as CREATE TABLE, you format properties in either the name-value pair or collection map format. The name-value pair property syntax is:

name = value AND name = value

The collection map format, used by compaction and compression properties, is:

{ name : value, name : value, name : value ... }

Enclose properties that are strings in single quotation marks.

See CREATE TABLE for examples.

CQL properties
CQL property Description Default
bloom_filter_fp_chance Desired false-positive probability for SSTable Bloom filters. more . . . 0.01 for SizeTieredCompactionStrategy, 0.1 for LeveledCompactionStrategy
caching Optimizes the use of cache memory without manual tuning. .. . . more Cassandra 2.1:

ALL for keys

NONE for rows_per_partition

Cassandra 2.0.x: keys_only

comment A human readable comment describing the table. . . . more N/A
compaction Sets the compaction strategy for the table. . . . more SizeTieredCompactionStrategy
compression The compression algorithm to use. Valid values are LZ4Compressor), SnappyCompressor, and DeflateCompressor. . . . . more LZ4Compressor
dclocal_read_repair_chance Specifies the probability of read repairs being invoked over all replicas in the current data center. 0.1 (Cassandra 2.1, Cassandra 2.0.9 and later) 0.0 (Cassandra 2.0.8 and earlier)
default_time_to_live The default expiration time in seconds for a table. Used in MapReduce/Hive scenarios when you have no control of TTL. 0
gc_grace_seconds Specifies the time to wait before garbage collecting tombstones (deletion markers). The default value allows a great deal of time for consistency to be achieved prior to deletion. In many deployments this interval can be reduced, and in a single-node cluster it can be safely set to zero. 864000 [10 days]
min_index_interval, max_index_interval (Cassandra 2.1.x) or index_interval (Cassandra 2.0.x) To control the sampling of entries from the partition index, configure the sample frequency of the partition summary by changing these properties. . . . more min_index_interval 128 and max_index_interval 2048, or index_interval 128
memtable_flush_period_in_ms Forces flushing of the memtable after the specified time in milliseconds elapses. 0
populate_io_cache_on_flush (Cassandra 2.0.x only) Adds newly flushed or compacted sstables to the operating system page cache, potentially evicting other cached data to make room. Enable when all data in the table is expected to fit in memory. You can also configure the global compaction_preheat_key_cache option in the cassandra.yaml file. false
read_repair_chance Specifies the basis for invoking read repairs on reads in clusters configured for a non-quorum consistency. The value must be between 0 and 1. 0.0 (Cassandra 2.1, Cassandra 2.0.9 and later) 0.1 (Cassandra 2.0.8 and earlier)
replicate_on_write (Cassandra 2.0.x only) Removed in Cassandra 2.1. Applies only to counter tables. When set to true, replicates writes to all affected replicas regardless of the consistency level specified by the client for a write request. For counter tables, this should always be set to true. true
speculative_retry Overrides normal read timeout when read_repair_chance is not 1.0, sending another request to read. more . . . 99percentile Cassandra 2.0.2 and later

Bloom filter

The Bloom filter property is the desired false-positive probability for SSTable Bloom filters. When data is requested, the Bloom filter checks if the row exists before doing disk I/O. Bloom filter property value ranges from 0 to 1.0. The effects of the minimum and maximum values are:

0 Enables the unmodified, effectively the largest possible, Bloom filter

1.0 Disables the Bloom Filter

The recommended setting is 0.1. A higher value yields diminishing returns.

caching

Caching optimizes the use of cache memory without manual tuning. You set table properties to configure caching when you create or alter the table. Cassandra weights the cached data by size and access frequency. After configuring the caching table property, configure the global caching properties in the cassandra.yaml file. For information about global caching properties, see Cassandra 2.1 documentation or Cassandra 2.0 documentation.

Cassandra 2.1

Configure the cache by creating a property map of values for the caching property. Options are:

  • keys: ALL or NONE
  • rows_per_partition: number of CQL rows, NONE or ALL
For example:
CREATE TABLE DogTypes (
  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
) WITH caching = { 'keys' : 'NONE', 'rows_per_partition' : '120' };

Cassandra 2.0

Configure the cache using one of these caching property options:

  • all
  • keys_only
  • rows_only
  • none
You can specify a key or row cache, or specify both key and row caches using the options. For example:
// Cassandra 2.0.x only

CREATE TABLE DogTypes (
  block_id uuid,
  species text,
  alias text,
  population varint,
  PRIMARY KEY (block_id)
) WITH caching = 'keys_only';
Important: In Cassandra 2.0.x, use row caching with caution.

comments

Comments can be used to document CQL statements in your application code. Single line comments can begin with a double dash (--) or a double slash (//) and extend to the end of the line. Multi-line comments can be enclosed in /* and */ characters.

compaction

The compaction property defines the compaction strategy class to use. The default supported classes are:
  • SizeTieredCompactionStrategy: The default compaction strategy. This strategy triggers a minor compaction when there are a number of similar sized SSTables on disk as configured by the table subproperty, min_threshold. A minor compaction does not involve all the tables in a keyspace.
  • LeveledCompactionStrategy: The leveled compaction strategy creates SSTables of a fixed, relatively small size (5 MB by default) that are grouped into levels. Within each level, SSTables are guaranteed to be non-overlapping. Each level (L0, L1, L2 and so on) is 10 times as large as the previous. Disk I/O is more uniform and predictable on higher than on lower levels as SSTables are continuously being compacted into progressively larger levels. At each level, row keys are merged into non-overlapping SSTables. This can improve performance for reads, because Cassandra can determine which SSTables in each level to check for the existence of row key data. This compaction strategy is modeled after Google's leveldb implementation. For more information, see When to Use Leveled Compaction, Leveled Compaction in Apache Cassandra, and compaction subproperties.

Hybrid (leveled and size-tiered) compaction improvements to the leveled compaction strategy reduce the performance overhead on read operations when compaction cannot keep pace with write-heavy workload. When using the LeveledCompactionStrategy, if Cassandra cannot keep pace with the workload, the compaction strategy switches to SizeTieredCompaction until Cassandra catches up. For this reason, it is a best practice to configure the max_threshold subproperty for a table to use when the switch occurs.

You can specify a custom strategy. Use the full class name as a string constant.

compression

To configure compression, choose the LZ4Compressor, SnappyCompressor, or DeflateCompressor property to use in creating or altering a table. Use an empty string ('') to disable compression, as shown in the example of how to use subproperties. Choosing the right compressor depends on your requirements for space savings over read performance. LZ4 is fastest to decompress, followed by Snappy, then by Deflate. Compression effectiveness is inversely correlated with decompression speed. The extra compression from Deflate or Snappy is not enough to make up for the decreased performance for general-purpose workloads, but for archival data they may be worth considering. Developers can also implement custom compression classes using the org.apache.cassandra.io.compress.ICompressor interface. Specify the full class name enclosed in single quotation marks. Also use the compression subproperties.

min_index_interval and max_index_interval

The index_interval (Cassandra 2.0.x) property or the min_index_interval and max_index_interval (Cassandra 2.1) properties control the sampling of entries from the primary row index, configure sample frequency of the partition summary by changing the index interval. After changing the index interval, SSTables are written to disk with new information. The interval corresponds to the number of index entries that are skipped between taking each sample. By default Cassandra samples one row key out of every 128. The larger the interval, the smaller and less effective the sampling. The larger the sampling, the more effective the index, but with increased memory usage. In Cassandra 2.0.x, generally, the best trade off between memory usage and performance is a value between 128 and 512 in combination with a large table key cache. However, if you have small rows (many to an OS page), you may want to increase the sample size, which often lowers memory usage without an impact on performance. For large rows, decreasing the sample size may improve read performance.

In Cassandra 2.1, the name index_interval is replaced by min_index_interval and max_index_interval. The max_index_interval is 2048 by default. The default would be reached only when SSTables are infrequently-read and the index summary memory pool is full. When upgrading from earlier releases, Cassandra uses the old index_interval value for the min_index_interval.

speculative retry

To override normal read timeout when read_repair_chance is not 1.0, sending another request to read, choose one of these values and use the property to create or alter the table:
  • ALWAYS: Retry reads of all replicas.
  • Xpercentile: Retry reads based on the effect on throughput and latency.
  • Yms: Retry reads after specified milliseconds.
  • NONE: Do not retry reads.
Using the speculative retry property, you can configure rapid read protection in Cassandra 2.0.2 and later. Use this property to retry a request after some milliseconds have passed or after a percentile of the typical read latency has been reached, which is tracked per table. For example:
  ALTER TABLE users WITH speculative_retry = '10ms';
Or:
  ALTER TABLE users WITH speculative_retry = '99percentile';
Show/hide