CREATE TABLE
Creates a new table.
Creates a new table in the selected keyspace. Use IF NOT
EXISTS
to suppress the error message if the table already exists; no table is
created. A static column can store the same data in
multiple clustered rows of a partition, and then retrieve that data with a single
SELECT
statement.
Tables support a single counter column.
Synopsis
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' ] ] ;
Syntax conventions | Description |
---|---|
UPPERCASE | Literal keyword. |
Lowercase | Not literal. |
Italics |
Variable value. Replace with a user-defined value. |
[] |
Optional. Square brackets ( [] ) surround
optional command arguments. Do not type the square brackets. |
( ) |
Group. Parentheses ( ( ) ) identify a group to
choose from. Do not type the parentheses. |
| |
Or. A vertical bar ( | ) separates alternative
elements. Type any one of the elements. Do not type the vertical
bar. |
... |
Repeatable. An ellipsis ( ... ) indicates that
you can repeat the syntax element as often as required. |
'Literal string' |
Single quotation ( ' ) marks must surround
literal strings in CQL statements. Use single quotation marks to
preserve upper case. |
{ key : value
} |
Map collection. Braces ( { } ) enclose map
collections or key value pairs. A colon separates the key and the
value. |
<datatype1,datatype2> |
Set, list, map, or tuple. Angle brackets ( <
> ) enclose data types in a set, list, map, or tuple.
Separate the data types with a comma. |
cql_statement; |
End CQL statement. A semicolon ( ; ) terminates
all CQL statements. |
[--] |
Separate the command line options from the command arguments with
two hyphens ( -- ). This syntax is useful when
arguments might be mistaken for command line options. |
' <schema> ... </schema>
' |
Search CQL only: Single quotation marks ( ' )
surround an entire XML schema declaration. |
@xml_entity='xml_entity_type' |
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
column_definition
Sets the column name, defines the data type, and optionally sets a column to static or primary key.
column_name
cql_type_definition [STATIC | PRIMARY KEY] [, ...]
- When primary key is at the end of a column definition, that column is the only primary key for the table.
- A table must have at least one
PRIMARY KEY
. - A static column cannot be a primary key.
- Primary keys can include frozen collections.
- column_name
- Use a unique name for each column in a table. To preserve case or use special characters, enclose the name in double-quotes.
- cql_type_definition
- Defines the type of data allowed in the column. See CQL data type or a user-defined type.
- STATIC
- Optional, the column has a single value.
- PRIMARY KEY
-
When the
PRIMARY KEY
is one column, append PRIMARY KEY to the end of the column definition. This is only schema information required to create a table. When there is one primary key, it is the partition key; the data is divided and stored by the unique values in this column.column_name cql_type_definition PRIMARY KEY
.Alternatively, you can declare the primary key consisting of only one column in the same way as you declare a compound primary key.
PRIMARY KEY (column_list)
Uniquely identifies rows, determines storage partitions, and orders data (clustering columns) within a partition.
NULL
value cannot be
inserted into a PRIMARY KEY
column. This restriction applies to
both partition keys and clustering columns.- column_list
-
Defines a partition and clustering columns, which affects how the data in stored.
- Compound primary key: the first column is the partition key, and
the additional columns are clustering keys. Syntax:
PRIMARY KEY (partition_column_name, clustering_column_name [, ...])
- Composite partition key: Multiple columns in the partition
key. Enclose the partition key columns in parentheses. Syntax:
PRIMARY KEY ((partition_column_name[, ...]),clustering_column_name [, ...])
- Compound primary key: the first column is the partition key, and
the additional columns are clustering keys. Syntax:
table_options
CQL table properties and descriptions of the syntax.
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 |
Tunes data handling, including I/O operations, compression, and compaction. Table property options use the following syntax:
- Single values:
option_name = 'value'
- Multiple values:
option_name = { 'subproperty' : 'value' [, ...] } [AND ...]
Simple JSON format, key-value pairs in a comma-separated list enclosed by curly brackets.
WITH
clause to define table property options. Separate multiple values
with
AND
.CREATE TABLE [keyspace_name.]table_name WITH option_name = 'value' AND option_name = {option_map};
- bloom_filter_fp_chance = N
-
False-positive probability for SSTable bloom filter. When a client requests data, the bloom filter checks if the row exists before executing disk I/O. Values range from 0 to 1.0, where:
0
is the minimum value use to enable the largest possible bloom filter (uses the most memory) and1.0
is the maximum value disabling the bloom filter.Tip: Recommended setting:0.1
. A higher value yields diminishing returns.Default:
bloom_filter_fp_chance = '0.01'
- caching = { 'keys' : 'value', 'rows_per_partition' : 'value'}
-
Optimizes the use of cache memory without manual tuning. Weighs the cached data by size and access frequency. Coordinate this setting with the global caching properties in the cassandra.yaml file. Valid values:
ALL
– all primary keys or rowsNONE
– no primary keys or rowsN
: (rows per partition only) – specify a whole number
Default:
{ 'keys': 'ALL', 'rows_per_partition': 'NONE' }
- cdc
-
Creates a Change Data Capture (CDC) log on the table.
Valid values:TRUE
- create CDC logFALSE
- do not create CDC log
- comments = 'some text that describes the table'
-
Provide documentation on the table.Tip: Enter a description of the types of queries the table was designed to satisfy.
- dclocal_read_repair_chance
- Probability that a successful read operation triggers a read repair, limited
to the same datacenter as the coordinator node.Warning: This option is deprecated and should be set to
0.0
. - default_time_to_live
- TTL (Time To Live) in seconds, where zero is disabled. The maximum configurable value
is
630720000
(20 years). Beginning in 2018, the expiration timestamp can exceed the maximum value supported by the storage engine; see the warning below. If the value is greater than zero, TTL is enabled for the entire table and an expiration timestamp is added to each column. A new TTL timestamp is calculated each time the data is updated and the row is removed after all the data expires.Default value:
0
(disabled).Warning: The database storage engine can only encode TTL timestamps throughJanuary 19 2038 03:14:07 UTC
due to the Year 2038 problem. The TTL date overflow policy determines whether requests with expiration timestamps later than the maximum date are rejected or inserted. See -Dcassandra.expiration_date_overflow_policy. - gc_grace_seconds
- Seconds after data is marked with a tombstone (deletion marker) before it is eligible
for garbage-collection. Default value: 864000 (10 days). The default value allows time
for the database to maximize consistency prior to deletion.In a single-node cluster, this property can safely be set to zero. You can also reduce this value for tables whose data is not explicitly deleted — for example, tables containing only data with TTL set, or tables with default_time_to_live set. However, if you lower the gc_grace_seconds value, consider its interaction with these operations:
- hint replays: When a node goes down and then comes back up, other nodes replay the write operations (called hints) that are queued for that node while it was unresponsive. The database does not replay hints older than gc_grace_seconds after creation. The max_hint_window_in_ms setting in the cassandra.yaml file sets the time limit (3 hours by default) for collecting hints for the unresponsive node.
- batch replays : Like hint queues, batch operations store database mutations that are replayed in sequence. As with hints, the database does not replay a batched mutation older than gc_grace_seconds after creation. If your application uses batch operations, consider the possibility that decreasing gc_grace_seconds increases the chance that a batched write operation may restore deleted data. The batchlog_replay_throttle_in_kb property in the cassandra.yaml file give some control of the batch replay process. The most important factors, however, are the size and scope of the batches you use.
- memtable_flush_period_in_ms
- Milliseconds before
memtables
associated with the table are flushed.When memtable_flush_period_in_ms=0, the memtable will flush when:- the flush threshold is met
- on shutdown
- on nodetool flush
- when commitlogs get full
Default:
0
- min_index_interval
- Minimum gap between index entries in the index summary. A lower min_index_interval means the index summary contains more entries from the index, which allows the database to search fewer index entries to execute a read. A larger index summary may also use more memory. The value for min_index_interval is the densest possible sampling of the index.
- max_index_interval
- If the total memory usage of all index summaries reaches this value, DataStax Enterprise decreases the index summaries for the coldest SSTables to the maximum set by max_index_interval. The max_index_interval is the sparsest possible sampling in relation to memory pressure.
- nodesync
- Manages the table repair settings using the following syntax:
{ 'enabled' : value, 'deadline_target_sec' : seconds }
enabled
: Set totrue
orfalse
to enable/disable NodeSync on the table.Default: true.
deadline_target_sec
: Specify the maximum number of seconds to validate all segments of the table. Set to less than the gc_grace_seconds to avoid resurrecting deleted data. DataStax recommends using the nodetool nodesyncservice ratesimulator to calculate the appropriate setting.Default: Highest value of gc_grace_seconds or 4 days.
- read_repair_chance
- The probability that a successful read operation triggers a read
repair.Warning: This option is deprecated and should be set to
0.0
. - speculative_retry
-
Specify the value as a number followed by a type,
ms
(milliseconds) orpercentile
. For example,speculative_retry = '3ms'
.Configures rapid read protection. Normal read requests are sent to just enough replica nodes to satisfy the consistency level. In rapid read protection, extra read requests are sent to other replicas, even after the consistency level has been met. The speculative retry property specifies the trigger for these extra read requests.- ALWAYS: The coordinator node sends extra read requests to all other replicas after every read of that table.
- Xpercentile: Track each table's typical read latency (in
milliseconds). Coordinator node retrieves the typical latency time of the table
being read and calculates X percent of that figure. The coordinator sends
redundant read requests if the number of milliseconds it waits without responses
exceeds that calculated figure.
For example, if the speculative_retry property for Table_A is set to
80percentile
, and that table's typical latency is 60 milliseconds, the coordinator node handling a read of Table_A would send a normal read request first, and send out redundant read requests if it received no responses within 48ms, which is 80% of 60ms. - Nms: The coordinator node sends extra read requests to all
other replicas if the coordinator node has not received any responses within
N
milliseconds. - NONE: The coordinator node does not send extra read requests after any read of that table.
compression = { compression_map }
Sets table compression.
compression_map
by specifying the compression algorithm
class
followed by the subproperties in simple JSON format. org.apache.cassandra.io.compress.ICompressor
interface.compression = {
['class' : 'compression_algorithm_name',
'chunk_length_in_kb' : 'value',
'crc_check_chance' : 'value',]
| 'sstable_compression' : '']
}
- class
-
Sets the compressor name. DataStax Enterprise provides the following built-in classes:
LZ4Compressor
SnappyCompressor
DeflateCompressor
Important: Use only compression implementations bundled with DSE.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.
Default:
LZ4Compressor
. - chunk_length_in_kb
-
Size (in KB) of the block. On disk, SSTables are compressed by block to allow random reads. Values larger than the default value might improve the compression rate, but increases the minimum size of data to be read from disk when a read occurs. The default value is a good middle ground for compressing tables. Adjust compression size to account for read/write access patterns (how much data is typically requested at once) and the average size of rows in the table.
Default:
64
. - crc_check_chance
-
When compression is enabled, each compressed block includes a checksum of that block for the purpose of detecting disk bit rot and avoiding the propagation of corruption to other replica. This option defines the probability with which those checksums are checked during read. By default they are always checked. Set to 0 to disable checksum checking and to 0.5, for instance, to check them on every other read.
Default:
1.0
. - sstable_compression
- Disables compression. Specify a null value.
compaction = {compaction_map}
Construct a map of the compaction option and its subproperties.
Defines the strategy for cleaning up data after writes.
compaction = { 'class' : 'compaction_strategy_name', 'property_name' : value [, ...] }where the compaction_strategy_name is SizeTieredCompactionStrategy, TimeWindowCompactionStrategy, or LeveledCompactionStrategy.
Common properties
compaction = { 'class' : 'compaction_strategy_name', 'enabled' : (true | false), 'log_all' : (true | false), 'only_purge_repaired_tombstone' : (true | false), 'tombstone_threshold' : ratio, 'tombstone_compaction_interval' : sec, 'unchecked_tombstone_compaction' : (true | false), 'min_threshold' : num_sstables, 'max_threshold' : num_sstables }
- enabled
- Enable background compaction.
true
runs minor compactions.false
disables minor compactions.
Tip: Usenodetool enableautocompaction
to start running compactions.Default:
true
- log_all
- Activates advanced logging for the entire
cluster.
Default:
false
- only_purge_repaired_tombstone
- Enabling this property prevents data from resurrecting when repair is not run within
the
gc_grace_seconds
. When its been a long time between repairs, the database keeps all tombstones.true
- Only allow tombstone purges on repaired SSTables.false
- Purge tombstones on SSTables during compaction even if the table has not been repaired.
Default:
false
- tombstone_threshold
- The ratio of garbage-collectable tombstones to all contained columns. If the ratio
exceeds this limit, compactions starts only on that table to purge the
tombstones.
Default:
0.2
- tombstone_compaction_interval
- Number of seconds before compaction can run on an SSTable after it is created. An
SSTable is eligible for compaction when it exceeds the
tombstone_threshold
. Because it might not be possible to drop tombstones when doing a single SSTable compaction, and since the compaction is triggered base on an estimated tombstone ratio, this setting makes the minimum interval between two single SSTable compactions tunable to prevent an SSTable from being constantly re-compacted.Default:
86400
(1 day) - unchecked_tombstone_compaction
- Setting to
true
allows tombstone compaction to run without pre-checking which tables are eligible for the operation. Even without this pre-check, DSE checks an SSTable to make sure it is safe to drop tombstones.Default:
false
- min_threshold
- The minimum number of SSTables to trigger a minor compaction. Restriction: Not used in
LeveledCompactionStrategy
.Default:
4
- max_threshold
- The maximum number of SSTables before a minor compaction is triggered. Restriction: Not used in
LeveledCompactionStrategy
.Default:
32
SizeTieredCompactionStrategy
The compaction class SizeTieredCompactionStrategy
(STCS) triggers a minor compaction when table meets the
min_threshold
. Minor compactions do not involve all the tables in a
keyspace. See SizeTieredCompactionStrategy (STCS).
compaction = { 'class' : 'SizeTieredCompactionStrategy', 'bucket_high' : factor, 'bucket_low' : factor, 'min_sstable_size' : int }
- bucket_high
- Size-tiered compaction merges sets of SSTables that are
approximately the same size. The database compares each SSTable size to the average
of all SSTable sizes for this table on the node. It merges SSTables whose size in KB
are within [average-size * bucket_low] and [average-size *
bucket_high].
Default:
1.5
- bucket_low
- Size-tiered compaction merges sets of SSTables that are
approximately the same size. The database compares each SSTable size to the average
of all SSTable sizes for this table on the node. It merges SSTables whose size in KB
are within [average-size * bucket_low] and [average-size *
bucket_high].
Default:
0.5
- min_sstable_size
- STCS groups SSTables into buckets. The bucketing process groups SSTables that differ
in size by less than 50%. This bucketing process is too fine-grained for small
SSTables. If your SSTables are small, use this option to define a size threshold in MB
below which all SSTables belong to one unique
bucket.
Default:
50
(MB)
cold_reads_to_omit
property for SizeTieredCompactionStrategy (STCS) is no longer supported.TimeWindowCompactionStrategy
The compaction class TimeWindowCompactionStrategy
(TWCS) compacts SSTables using a series of time windows or
buckets. TWCS creates a new time window within each successive time period.
During the active time window, TWCS compacts all SSTables flushed from memory into larger
SSTables using STCS. At the end of the time period, all of these SSTables are compacted
into a single SSTable. Then the next time window starts and the process repeats. See TimeWindowCompactionStrategy (TWCS).
compaction = { 'class' : 'TimeWindowCompactionStrategy, 'compaction_window_unit' : days, 'compaction_window_size' : int, 'split_during_flush' : (true | false) }
- compaction_window_unit
- Time unit used to define the bucket size. The value is based on the Java
TimeUnit
. For the list of valid values, see the Java APITimeUnit
page located at https://docs.oracle.com/javase/8/docs/api/java/util/concurrent/TimeUnit.html.Default:
days
- compaction_window_size
- Units per bucket.
- split_during_flush
- Prevents mixing older data from repairs and hints with newer data from the current
time window. During a flush operation, determines whether data partitions are split
based on the configured time window.
false
- the data partitions are not split based on the configured time window.true
- ensure that data repaired by NodeSync is placed in the correct TWCS window. Enablesplit_during_flush
when using NodeSync with TWCS or when running node repairs.
LeveledCompactionStrategy
The compaction class LeveledCompactionStrategy
(LCS) creates SSTables of a fixed, relatively small size (160 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 in the next level. See LeveledCompactionStrategy (LCS).
compaction = { 'class' : 'LeveledCompactionStrategy, 'sstable_size_in_mb' : int }
- sstable_size_in_mb
- The target size for SSTables that use the LeveledCompactionStrategy. Although
SSTable sizes should be less or equal to sstable_size_in_mb, it
is possible that compaction could produce a larger SSTable during compaction. This
occurs when data for a given partition key is exceptionally large. The DSE database
does not split the data into two
SSTables.
Default:
160
DateTieredCompactionStrategy (deprecated)
Stores data written within a certain period of time in the same SSTable.
- base_time_seconds
- The size of the first time window.
Default:
3600
- max_sstable_age_days (deprecated)
- DSE does not compact SSTables if its most recent data is older than this property.
Fractional days can be set.
Default:
1000
- max_window_size_seconds
- The maximum window size in seconds.
Default:
86400
- timestamp_resolution
- Units, MICROSECONDS or MILLISECONDS, to match
the timestamp of inserted
data.
Default:
MICROSECONDS
Table keywords
- CLUSTERING ORDER BY ( column_name ASC | DESC)
-
Order rows storage to make use of the on-disk sorting of columns. Specifying order can make query results more efficient. Options are:
ASC
: ascending (default order)DESC
: descending, reverse order - ID
-
If a table is accidentally dropped with DROP TABLE, use this option to recreate the table and run a commit log replay to retrieve the data.
Examples
CREATE TABLE CQL examples.
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
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
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
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 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
cyclist_id
table:CREATE TABLE cycling.cyclist_id ( lastname text, firstname text, age int, id UUID, PRIMARY KEY ((lastname, firstname), age) );
Storing data in descending order
CREATE TABLE cycling.cyclist_category ( category text, points int, id UUID, lastname text, PRIMARY KEY (category, points)) WITH CLUSTERING ORDER BY (points DESC);
Restoring from the table ID for commit log replay
CREATE TABLE cycling.cyclist_emails ( userid text PRIMARY KEY, id UUID, emails set<text> ) WITH ID='1bb7516e-b140-11e8-96f8-529269fb1459';
id
column of system_schema.tables
. For
example:SELECT id FROM system_schema.tables WHERE keyspace_name = 'cycling' AND table_name = 'cyclist_emails';
To perform a point-in-time restoration of the table, see Restoring a backup to a specific point-in-time.