Added: cassandra/site/src/doc/3.10/_sources/cql/changes.txt URL: http://svn.apache.org/viewvc/cassandra/site/src/doc/3.10/_sources/cql/changes.txt?rev=1757419&view=auto ============================================================================== --- cassandra/site/src/doc/3.10/_sources/cql/changes.txt (added) +++ cassandra/site/src/doc/3.10/_sources/cql/changes.txt Tue Aug 23 19:25:17 2016 @@ -0,0 +1,197 @@ +.. Licensed to the Apache Software Foundation (ASF) under one +.. or more contributor license agreements. See the NOTICE file +.. distributed with this work for additional information +.. regarding copyright ownership. The ASF licenses this file +.. to you under the Apache License, Version 2.0 (the +.. "License"); you may not use this file except in compliance +.. with the License. You may obtain a copy of the License at +.. +.. http://www.apache.org/licenses/LICENSE-2.0 +.. +.. Unless required by applicable law or agreed to in writing, software +.. distributed under the License is distributed on an "AS IS" BASIS, +.. WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +.. See the License for the specific language governing permissions and +.. limitations under the License. + +.. highlight:: cql + +Changes +------- + +The following describes the changes in each version of CQL. + +3.4.3 +^^^^^ + +- Support for ``GROUP BY`` (:jira:`10707`). +- Adds a ``DEFAULT UNSET`` option for ``INSERT JSON`` to ignore omitted columns (:jira:`11424`). +- Allows ``null`` as a legal value for TTL on insert and update. It will be treated as equivalent to +inserting a 0 (:jira:`12216`). + +3.4.2 +^^^^^ + +- If a table has a non zero ``default_time_to_live``, then explicitly specifying a TTL of 0 in an ``INSERT`` or + ``UPDATE`` statement will result in the new writes not having any expiration (that is, an explicit TTL of 0 cancels + the ``default_time_to_live``). This wasn't the case before and the ``default_time_to_live`` was applied even though a + TTL had been explicitly set. +- ``ALTER TABLE`` ``ADD`` and ``DROP`` now allow multiple columns to be added/removed. +- New ``PER PARTITION LIMIT`` option for ``SELECT`` statements (see `CASSANDRA-7017 + <https://issues.apache.org/jira/browse/CASSANDRA-7017)>`__. +- :ref:`User-defined functions <cql-functions>` can now instantiate ``UDTValue`` and ``TupleValue`` instances via the + new ``UDFContext`` interface (see `CASSANDRA-10818 <https://issues.apache.org/jira/browse/CASSANDRA-10818)>`__. +- :ref:`User-defined types <udts>` may now be stored in a non-frozen form, allowing individual fields to be updated and + deleted in ``UPDATE`` statements and ``DELETE`` statements, respectively. (`CASSANDRA-7423 + <https://issues.apache.org/jira/browse/CASSANDRA-7423)>`__). + +3.4.1 +^^^^^ + +- Adds ``CAST`` functions. + +3.4.0 +^^^^^ + +- Support for :ref:`materialized views <materialized-views>`. +- ``DELETE`` support for inequality expressions and ``IN`` restrictions on any primary key columns. +- ``UPDATE`` support for ``IN`` restrictions on any primary key columns. + +3.3.1 +^^^^^ + +- The syntax ``TRUNCATE TABLE X`` is now accepted as an alias for ``TRUNCATE X``. + +3.3.0 +^^^^^ + +- :ref:`User-defined functions and aggregates <cql-functions>` are now supported. +- Allows double-dollar enclosed strings literals as an alternative to single-quote enclosed strings. +- Introduces Roles to supersede user based authentication and access control +- New ``date``, ``time``, ``tinyint`` and ``smallint`` :ref:`data types <data-types>` have been added. +- :ref:`JSON support <cql-json>` has been added +- Adds new time conversion functions and deprecate ``dateOf`` and ``unixTimestampOf``. + +3.2.0 +^^^^^ + +- :ref:`User-defined types <udts>` supported. +- ``CREATE INDEX`` now supports indexing collection columns, including indexing the keys of map collections through the + ``keys()`` function +- Indexes on collections may be queried using the new ``CONTAINS`` and ``CONTAINS KEY`` operators +- :ref:`Tuple types <tuples>` were added to hold fixed-length sets of typed positional fields. +- ``DROP INDEX`` now supports optionally specifying a keyspace. + +3.1.7 +^^^^^ + +- ``SELECT`` statements now support selecting multiple rows in a single partition using an ``IN`` clause on combinations + of clustering columns. +- ``IF NOT EXISTS`` and ``IF EXISTS`` syntax is now supported by ``CREATE USER`` and ``DROP USER`` statements, + respectively. + +3.1.6 +^^^^^ + +- A new ``uuid()`` method has been added. +- Support for ``DELETE ... IF EXISTS`` syntax. + +3.1.5 +^^^^^ + +- It is now possible to group clustering columns in a relation, see :ref:`WHERE <where-clause>` clauses. +- Added support for :ref:`static columns <static-columns>`. + +3.1.4 +^^^^^ + +- ``CREATE INDEX`` now allows specifying options when creating CUSTOM indexes. + +3.1.3 +^^^^^ + +- Millisecond precision formats have been added to the :ref:`timestamp <timestamps>` parser. + +3.1.2 +^^^^^ + +- ``NaN`` and ``Infinity`` has been added as valid float constants. They are now reserved keywords. In the unlikely case + you we using them as a column identifier (or keyspace/table one), you will now need to double quote them. + +3.1.1 +^^^^^ + +- ``SELECT`` statement now allows listing the partition keys (using the ``DISTINCT`` modifier). See `CASSANDRA-4536 + <https://issues.apache.org/jira/browse/CASSANDRA-4536>`__. +- The syntax ``c IN ?`` is now supported in ``WHERE`` clauses. In that case, the value expected for the bind variable + will be a list of whatever type ``c`` is. +- It is now possible to use named bind variables (using ``:name`` instead of ``?``). + +3.1.0 +^^^^^ + +- ``ALTER TABLE`` ``DROP`` option added. +- ``SELECT`` statement now supports aliases in select clause. Aliases in WHERE and ORDER BY clauses are not supported. +- ``CREATE`` statements for ``KEYSPACE``, ``TABLE`` and ``INDEX`` now supports an ``IF NOT EXISTS`` condition. + Similarly, ``DROP`` statements support a ``IF EXISTS`` condition. +- ``INSERT`` statements optionally supports a ``IF NOT EXISTS`` condition and ``UPDATE`` supports ``IF`` conditions. + +3.0.5 +^^^^^ + +- ``SELECT``, ``UPDATE``, and ``DELETE`` statements now allow empty ``IN`` relations (see `CASSANDRA-5626 + <https://issues.apache.org/jira/browse/CASSANDRA-5626)>`__. + +3.0.4 +^^^^^ + +- Updated the syntax for custom :ref:`secondary indexes <secondary-indexes>`. +- Non-equal condition on the partition key are now never supported, even for ordering partitioner as this was not + correct (the order was **not** the one of the type of the partition key). Instead, the ``token`` method should always + be used for range queries on the partition key (see :ref:`WHERE clauses <where-clause>`). + +3.0.3 +^^^^^ + +- Support for custom :ref:`secondary indexes <secondary-indexes>` has been added. + +3.0.2 +^^^^^ + +- Type validation for the :ref:`constants <constants>` has been fixed. For instance, the implementation used to allow + ``'2'`` as a valid value for an ``int`` column (interpreting it has the equivalent of ``2``), or ``42`` as a valid + ``blob`` value (in which case ``42`` was interpreted as an hexadecimal representation of the blob). This is no longer + the case, type validation of constants is now more strict. See the :ref:`data types <data-types>` section for details + on which constant is allowed for which type. +- The type validation fixed of the previous point has lead to the introduction of blobs constants to allow the input of + blobs. Do note that while the input of blobs as strings constant is still supported by this version (to allow smoother + transition to blob constant), it is now deprecated and will be removed by a future version. If you were using strings + as blobs, you should thus update your client code ASAP to switch blob constants. +- A number of functions to convert native types to blobs have also been introduced. Furthermore the token function is + now also allowed in select clauses. See the :ref:`section on functions <cql-functions>` for details. + +3.0.1 +^^^^^ + +- Date strings (and timestamps) are no longer accepted as valid ``timeuuid`` values. Doing so was a bug in the sense + that date string are not valid ``timeuuid``, and it was thus resulting in `confusing behaviors + <https://issues.apache.org/jira/browse/CASSANDRA-4936>`__. However, the following new methods have been added to help + working with ``timeuuid``: ``now``, ``minTimeuuid``, ``maxTimeuuid`` , + ``dateOf`` and ``unixTimestampOf``. +- Float constants now support the exponent notation. In other words, ``4.2E10`` is now a valid floating point value. + +Versioning +^^^^^^^^^^ + +Versioning of the CQL language adheres to the `Semantic Versioning <http://semver.org>`__ guidelines. Versions take the +form X.Y.Z where X, Y, and Z are integer values representing major, minor, and patch level respectively. There is no +correlation between Cassandra release versions and the CQL language version. + +========= ============================================================================================================= + version description +========= ============================================================================================================= + Major The major version *must* be bumped when backward incompatible changes are introduced. This should rarely + occur. + Minor Minor version increments occur when new, but backward compatible, functionality is introduced. + Patch The patch version is incremented when bugs are fixed. +========= =============================================================================================================
Added: cassandra/site/src/doc/3.10/_sources/cql/ddl.txt URL: http://svn.apache.org/viewvc/cassandra/site/src/doc/3.10/_sources/cql/ddl.txt?rev=1757419&view=auto ============================================================================== --- cassandra/site/src/doc/3.10/_sources/cql/ddl.txt (added) +++ cassandra/site/src/doc/3.10/_sources/cql/ddl.txt Tue Aug 23 19:25:17 2016 @@ -0,0 +1,677 @@ +.. Licensed to the Apache Software Foundation (ASF) under one +.. or more contributor license agreements. See the NOTICE file +.. distributed with this work for additional information +.. regarding copyright ownership. The ASF licenses this file +.. to you under the Apache License, Version 2.0 (the +.. "License"); you may not use this file except in compliance +.. with the License. You may obtain a copy of the License at +.. +.. http://www.apache.org/licenses/LICENSE-2.0 +.. +.. Unless required by applicable law or agreed to in writing, software +.. distributed under the License is distributed on an "AS IS" BASIS, +.. WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +.. See the License for the specific language governing permissions and +.. limitations under the License. + +.. highlight:: cql + +.. _data-definition: + +Data Definition +--------------- + +CQL stores data in *tables*, whose schema defines the layout of said data in the table, and those tables are grouped in +*keyspaces*. A keyspace defines a number of options that applies to all the tables it contains, most prominently of +which is the :ref:`replication strategy <replication-strategy>` used by the keyspace. It is generally encouraged to use +one keyspace by *application*, and thus many cluster may define only one keyspace. + +This section describes the statements used to create, modify, and remove those keyspace and tables. + +Common definitions +^^^^^^^^^^^^^^^^^^ + +The names of the keyspaces and tables are defined by the following grammar: + +.. productionlist:: + keyspace_name: `name` + table_name: [ `keyspace_name` '.' ] `name` + name: `unquoted_name` | `quoted_name` + unquoted_name: re('[a-zA-Z_0-9]{1, 48}') + quoted_name: '"' `unquoted_name` '"' + +Both keyspace and table name should be comprised of only alphanumeric characters, cannot be empty and are limited in +size to 48 characters (that limit exists mostly to avoid filenames (which may include the keyspace and table name) to go +over the limits of certain file systems). By default, keyspace and table names are case insensitive (``myTable`` is +equivalent to ``mytable``) but case sensitivity can be forced by using double-quotes (``"myTable"`` is different from +``mytable``). + +Further, a table is always part of a keyspace and a table name can be provided fully-qualified by the keyspace it is +part of. If is is not fully-qualified, the table is assumed to be in the *current* keyspace (see :ref:`USE statement +<use-statement>`). + +Further, the valid names for columns is simply defined as: + +.. productionlist:: + column_name: `identifier` + +We also define the notion of statement options for use in the following section: + +.. productionlist:: + options: `option` ( AND `option` )* + option: `identifier` '=' ( `identifier` | `constant` | `map_literal` ) + +.. _create-keyspace-statement: + +CREATE KEYSPACE +^^^^^^^^^^^^^^^ + +A keyspace is created using a ``CREATE KEYSPACE`` statement: + +.. productionlist:: + create_keyspace_statement: CREATE KEYSPACE [ IF NOT EXISTS ] `keyspace_name` WITH `options` + +For instance:: + + CREATE KEYSPACE Excelsior + WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3}; + + CREATE KEYSPACE Excalibur + WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3} + AND durable_writes = false; + + +The supported ``options`` are: + +=================== ========== =========== ========= =================================================================== +name kind mandatory default description +=================== ========== =========== ========= =================================================================== +``replication`` *map* yes The replication strategy and options to use for the keyspace (see + details below). +``durable_writes`` *simple* no true Whether to use the commit log for updates on this keyspace + (disable this option at your own risk!). +=================== ========== =========== ========= =================================================================== + +The ``replication`` property is mandatory and must at least contains the ``'class'`` sub-option which defines the +:ref:`replication strategy <replication-strategy>` class to use. The rest of the sub-options depends on what replication +strategy is used. By default, Cassandra support the following ``'class'``: + +- ``'SimpleStrategy'``: A simple strategy that defines a replication factor for the whole cluster. The only sub-options + supported is ``'replication_factor'`` to define that replication factor and is mandatory. +- ``'NetworkTopologyStrategy'``: A replication strategy that allows to set the replication factor independently for + each data-center. The rest of the sub-options are key-value pairs where a key is a data-center name and its value is + the associated replication factor. + +Attempting to create a keyspace that already exists will return an error unless the ``IF NOT EXISTS`` option is used. If +it is used, the statement will be a no-op if the keyspace already exists. + +.. _use-statement: + +USE +^^^ + +The ``USE`` statement allows to change the *current* keyspace (for the *connection* on which it is executed). A number +of objects in CQL are bound to a keyspace (tables, user-defined types, functions, ...) and the current keyspace is the +default keyspace used when those objects are referred without a fully-qualified name (that is, without being prefixed a +keyspace name). A ``USE`` statement simply takes the keyspace to use as current as argument: + +.. productionlist:: + use_statement: USE `keyspace_name` + +.. _alter-keyspace-statement: + +ALTER KEYSPACE +^^^^^^^^^^^^^^ + +An ``ALTER KEYSPACE`` statement allows to modify the options of a keyspace: + +.. productionlist:: + alter_keyspace_statement: ALTER KEYSPACE `keyspace_name` WITH `options` + +For instance:: + + ALTER KEYSPACE Excelsior + WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 4}; + +The supported options are the same than for :ref:`creating a keyspace <create-keyspace-statement>`. + +.. _drop-keyspace-statement: + +DROP KEYSPACE +^^^^^^^^^^^^^ + +Dropping a keyspace can be done using the ``DROP KEYSPACE`` statement: + +.. productionlist:: + drop_keyspace_statement: DROP KEYSPACE [ IF EXISTS ] `keyspace_name` + +For instance:: + + DROP KEYSPACE Excelsior; + +Dropping a keyspace results in the immediate, irreversible removal of that keyspace, including all the tables, UTD and +functions in it, and all the data contained in those tables. + +If the keyspace does not exists, the statement will return an error, unless ``IF EXISTS`` is used in which case the +operation is a no-op. + +.. _create-table-statement: + +CREATE TABLE +^^^^^^^^^^^^ + +Creating a new table uses the ``CREATE TABLE`` statement: + +.. productionlist:: + create_table_statement: CREATE TABLE [ IF NOT EXISTS ] `table_name` + : '(' + : `column_definition` + : ( ',' `column_definition` )* + : [ ',' PRIMARY KEY '(' `primary_key` ')' ] + : ')' [ WITH `table_options` ] + column_definition: `column_name` `cql_type` [ STATIC ] [ PRIMARY KEY] + primary_key: `partition_key` [ ',' `clustering_columns` ] + partition_key: `column_name` + : | '(' `column_name` ( ',' `column_name` )* ')' + clustering_columns: `column_name` ( ',' `column_name` )* + table_options: COMPACT STORAGE [ AND `table_options` ] + : | CLUSTERING ORDER BY '(' `clustering_order` ')' [ AND `table_options` ] + : | `options` + clustering_order: `column_name` (ASC | DESC) ( ',' `column_name` (ASC | DESC) )* + +For instance:: + + CREATE TABLE monkeySpecies ( + species text PRIMARY KEY, + common_name text, + population varint, + average_size int + ) WITH comment='Important biological records' + AND read_repair_chance = 1.0; + + CREATE TABLE timeline ( + userid uuid, + posted_month int, + posted_time uuid, + body text, + posted_by text, + PRIMARY KEY (userid, posted_month, posted_time) + ) WITH compaction = { 'class' : 'LeveledCompactionStrategy' }; + + CREATE TABLE loads ( + machine inet, + cpu int, + mtime timeuuid, + load float, + PRIMARY KEY ((machine, cpu), mtime) + ) WITH CLUSTERING ORDER BY (mtime DESC); + +A CQL table has a name and is composed of a set of *rows*. Creating a table amounts to defining which :ref:`columns +<column-definition>` the rows will be composed, which of those columns compose the :ref:`primary key <primary-key>`, as +well as optional :ref:`options <create-table-options>` for the table. + +Attempting to create an already existing table will return an error unless the ``IF NOT EXISTS`` directive is used. If +it is used, the statement will be a no-op if the table already exists. + + +.. _column-definition: + +Column definitions +~~~~~~~~~~~~~~~~~~ + +Every rows in a CQL table has a set of predefined columns defined at the time of the table creation (or added later +using an :ref:`alter statement<alter-table-statement>`). + +A :token:`column_definition` is primarily comprised of the name of the column defined and it's :ref:`type <data-types>`, +which restrict which values are accepted for that column. Additionally, a column definition can have the following +modifiers: + +``STATIC`` + it declares the column as being a :ref:`static column <static-columns>`. + +``PRIMARY KEY`` + it declares the column as being the sole component of the :ref:`primary key <primary-key>` of the table. + +.. _static-columns: + +Static columns +`````````````` +Some columns can be declared as ``STATIC`` in a table definition. A column that is static will be âsharedâ by all the +rows belonging to the same partition (having the same :ref:`partition key <partition-key>`). For instance:: + + CREATE TABLE t ( + pk int, + t int, + v text, + s text static, + PRIMARY KEY (pk, t) + ); + + INSERT INTO t (pk, t, v, s) VALUES (0, 0, 'val0', 'static0'); + INSERT INTO t (pk, t, v, s) VALUES (0, 1, 'val1', 'static1'); + + SELECT * FROM t; + pk | t | v | s + ----+---+--------+----------- + 0 | 0 | 'val0' | 'static1' + 0 | 1 | 'val1' | 'static1' + +As can be seen, the ``s`` value is the same (``static1``) for both of the row in the partition (the partition key in +that example being ``pk``, both rows are in that same partition): the 2nd insertion has overridden the value for ``s``. + +The use of static columns as the following restrictions: + +- tables with the ``COMPACT STORAGE`` option (see below) cannot use them. +- a table without clustering columns cannot have static columns (in a table without clustering columns, every partition + has only one row, and so every column is inherently static). +- only non ``PRIMARY KEY`` columns can be static. + +.. _primary-key: + +The Primary key +~~~~~~~~~~~~~~~ + +Within a table, a row is uniquely identified by its ``PRIMARY KEY``, and hence all table **must** define a PRIMARY KEY +(and only one). A ``PRIMARY KEY`` definition is composed of one or more of the columns defined in the table. +Syntactically, the primary key is defined the keywords ``PRIMARY KEY`` followed by comma-separated list of the column +names composing it within parenthesis, but if the primary key has only one column, one can alternatively follow that +column definition by the ``PRIMARY KEY`` keywords. The order of the columns in the primary key definition matter. + +A CQL primary key is composed of 2 parts: + +- the :ref:`partition key <partition-key>` part. It is the first component of the primary key definition. It can be a + single column or, using additional parenthesis, can be multiple columns. A table always have at least a partition key, + the smallest possible table definition is:: + + CREATE TABLE t (k text PRIMARY KEY); + +- the :ref:`clustering columns <clustering-columns>`. Those are the columns after the first component of the primary key + definition, and the order of those columns define the *clustering order*. + +Some example of primary key definition are: + +- ``PRIMARY KEY (a)``: ``a`` is the partition key and there is no clustering columns. +- ``PRIMARY KEY (a, b, c)`` : ``a`` is the partition key and ``b`` and ``c`` are the clustering columns. +- ``PRIMARY KEY ((a, b), c)`` : ``a`` and ``b`` compose the partition key (this is often called a *composite* partition + key) and ``c`` is the clustering column. + + +.. _partition-key: + +The partition key +````````````````` + +Within a table, CQL defines the notion of a *partition*. A partition is simply the set of rows that share the same value +for their partition key. Note that if the partition key is composed of multiple columns, then rows belong to the same +partition only they have the same values for all those partition key column. So for instance, given the following table +definition and content:: + + CREATE TABLE t ( + a int, + b int, + c int, + d int, + PRIMARY KEY ((a, b), c, d) + ); + + SELECT * FROM t; + a | b | c | d + ---+---+---+--- + 0 | 0 | 0 | 0 // row 1 + 0 | 0 | 1 | 1 // row 2 + 0 | 1 | 2 | 2 // row 3 + 0 | 1 | 3 | 3 // row 4 + 1 | 1 | 4 | 4 // row 5 + +``row 1`` and ``row 2`` are in the same partition, ``row 3`` and ``row 4`` are also in the same partition (but a +different one) and ``row 5`` is in yet another partition. + +Note that a table always has a partition key, and that if the table has no :ref:`clustering columns +<clustering-columns>`, then every partition of that table is only comprised of a single row (since the primary key +uniquely identifies rows and the primary key is equal to the partition key if there is no clustering columns). + +The most important property of partition is that all the rows belonging to the same partition are guarantee to be stored +on the same set of replica nodes. In other words, the partition key of a table defines which of the rows will be +localized together in the Cluster, and it is thus important to choose your partition key wisely so that rows that needs +to be fetch together are in the same partition (so that querying those rows together require contacting a minimum of +nodes). + +Please note however that there is a flip-side to this guarantee: as all rows sharing a partition key are guaranteed to +be stored on the same set of replica node, a partition key that groups too much data can create a hotspot. + +Another useful property of a partition is that when writing data, all the updates belonging to a single partition are +done *atomically* and in *isolation*, which is not the case across partitions. + +The proper choice of the partition key and clustering columns for a table is probably one of the most important aspect +of data modeling in Cassandra, and it largely impact which queries can be performed, and how efficiently they are. + + +.. _clustering-columns: + +The clustering columns +`````````````````````` + +The clustering columns of a table defines the clustering order for the partition of that table. For a given +:ref:`partition <partition-key>`, all the rows are physically ordered inside Cassandra by that clustering order. For +instance, given:: + + CREATE TABLE t ( + a int, + b int, + c int, + PRIMARY KEY (a, c, d) + ); + + SELECT * FROM t; + a | b | c + ---+---+--- + 0 | 0 | 4 // row 1 + 0 | 1 | 9 // row 2 + 0 | 2 | 2 // row 3 + 0 | 3 | 3 // row 4 + +then the rows (which all belong to the same partition) are all stored internally in the order of the values of their +``b`` column (the order they are displayed above). So where the partition key of the table allows to group rows on the +same replica set, the clustering columns controls how those rows are stored on the replica. That sorting allows the +retrieval of a range of rows within a partition (for instance, in the example above, ``SELECT * FROM t WHERE a = 0 AND b +> 1 and b <= 3``) very efficient. + + +.. _create-table-options: + +Table options +~~~~~~~~~~~~~ + +A CQL table has a number of options that can be set at creation (and, for most of them, :ref:`altered +<alter-table-statement>` later). These options are specified after the ``WITH`` keyword. + +Amongst those options, two important ones cannot be changed after creation and influence which queries can be done +against the table: the ``COMPACT STORAGE`` option and the ``CLUSTERING ORDER`` option. Those, as well as the other +options of a table are described in the following sections. + +.. _compact-tables: + +Compact tables +`````````````` + +.. warning:: Since Cassandra 3.0, compact tables have the exact same layout internally than non compact ones (for the + same schema obviously), and declaring a table compact **only** creates artificial limitations on the table definition + and usage that are necessary to ensure backward compatibility with the deprecated Thrift API. And as ``COMPACT + STORAGE`` cannot, as of Cassandra |version|, be removed, it is strongly discouraged to create new table with the + ``COMPACT STORAGE`` option. + +A *compact* table is one defined with the ``COMPACT STORAGE`` option. This option is mainly targeted towards backward +compatibility for definitions created before CQL version 3 (see `www.datastax.com/dev/blog/thrift-to-cql3 +<http://www.datastax.com/dev/blog/thrift-to-cql3>`__ for more details) and shouldn't be used for new tables. Declaring a +table with this option creates limitations for the table which are largely arbitrary but necessary for backward +compatibility with the (deprecated) Thrift API. Amongst those limitation: + +- a compact table cannot use collections nor static columns. +- if a compact table has at least one clustering column, then it must have *exactly* one column outside of the primary + key ones. This imply you cannot add or remove columns after creation in particular. +- a compact table is limited in the indexes it can create, and no materialized view can be created on it. + +.. _clustering-order: + +Reversing the clustering order +`````````````````````````````` + +The clustering order of a table is defined by the :ref:`clustering columns <clustering-columns>` of that table. By +default, that ordering is based on natural order of those clustering order, but the ``CLUSTERING ORDER`` allows to +change that clustering order to use the *reverse* natural order for some (potentially all) of the columns. + +The ``CLUSTERING ORDER`` option takes the comma-separated list of the clustering column, each with a ``ASC`` (for +*ascendant*, e.g. the natural order) or ``DESC`` (for *descendant*, e.g. the reverse natural order). Note in particular +that the default (if the ``CLUSTERING ORDER`` option is not used) is strictly equivalent to using the option with all +clustering columns using the ``ASC`` modifier. + +Note that this option is basically a hint for the storage engine to change the order in which it stores the row but it +has 3 visible consequences: + +# it limits which ``ORDER BY`` clause are allowed for :ref:`selects <select-statement>` on that table. You can only + order results by the clustering order or the reverse clustering order. Meaning that if a table has 2 clustering column + ``a`` and ``b`` and you defined ``WITH CLUSTERING ORDER (a DESC, b ASC)``, then in queries you will be allowed to use + ``ORDER BY (a DESC, b ASC)`` and (reverse clustering order) ``ORDER BY (a ASC, b DESC)`` but **not** ``ORDER BY (a + ASC, b ASC)`` (nor ``ORDER BY (a DESC, b DESC)``). +# it also change the default order of results when queried (if no ``ORDER BY`` is provided). Results are always returned + in clustering order (within a partition). +# it has a small performance impact on some queries as queries in reverse clustering order are slower than the one in + forward clustering order. In practice, this means that if you plan on querying mostly in the reverse natural order of + your columns (which is common with time series for instance where you often want data from the newest to the oldest), + it is an optimization to declare a descending clustering order. + +.. _create-table-general-options: + +Other table options +``````````````````` + +.. todo:: review (misses cdc if nothing else) and link to proper categories when appropriate (compaction for instance) + +A table supports the following options: + ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| option | kind | default | description | ++================================+==========+=============+===========================================================+ +| ``comment`` | *simple* | none | A free-form, human-readable comment. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``read_repair_chance`` | *simple* | 0.1 | The probability with which to query extra nodes (e.g. | +| | | | more nodes than required by the consistency level) for | +| | | | the purpose of read repairs. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``dclocal_read_repair_chance`` | *simple* | 0 | The probability with which to query extra nodes (e.g. | +| | | | more nodes than required by the consistency level) | +| | | | belonging to the same data center than the read | +| | | | coordinator for the purpose of read repairs. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``gc_grace_seconds`` | *simple* | 864000 | Time to wait before garbage collecting tombstones | +| | | | (deletion markers). | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``bloom_filter_fp_chance`` | *simple* | 0.00075 | The target probability of false positive of the sstable | +| | | | bloom filters. Said bloom filters will be sized to provide| +| | | | the provided probability (thus lowering this value impact | +| | | | the size of bloom filters in-memory and on-disk) | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``default_time_to_live`` | *simple* | 0 | The default expiration time (âTTLâ) in seconds for a | +| | | | table. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``compaction`` | *map* | *see below* | :ref:`Compaction options <cql-compaction-options>`. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``compression`` | *map* | *see below* | :ref:`Compression options <cql-compression-options>`. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ +| ``caching`` | *map* | *see below* | :ref:`Caching options <cql-caching-options>`. | ++--------------------------------+----------+-------------+-----------------------------------------------------------+ + +.. _cql-compaction-options: + +Compaction options +################## + +The ``compaction`` options must at least define the ``'class'`` sub-option, that defines the compaction strategy class +to use. The default supported class are ``'SizeTieredCompactionStrategy'`` (:ref:`STCS <STCS>`), +``'LeveledCompactionStrategy'`` (:ref:`LCS <LCS>`) and ``'TimeWindowCompactionStrategy'`` (:ref:`TWCS <TWCS>`) (the +``'DateTieredCompactionStrategy'`` is also supported but is deprecated and ``'TimeWindowCompactionStrategy'`` should be +preferred instead). Custom strategy can be provided by specifying the full class name as a :ref:`string constant +<constants>`. + +All default strategies support a number of :ref:`common options <compaction-options>`, as well as options specific to +the strategy chosen (see the section corresponding to your strategy for details: :ref:`STCS <stcs-options>`, :ref:`LCS +<lcs-options>` and :ref:`TWCS <TWCS>`). + +.. _cql-compression-options: + +Compression options +################### + +The ``compression`` options define if and how the sstables of the table are compressed. The following sub-options are +available: + +========================= =============== ============================================================================= + Option Default Description +========================= =============== ============================================================================= + ``class`` LZ4Compressor The compression algorithm to use. Default compressor are: LZ4Compressor, + SnappyCompressor and DeflateCompressor. Use ``'enabled' : false`` to disable + compression. Custom compressor can be provided by specifying the full class + name as a âstring constantâ:#constants. + ``enabled`` true Enable/disable sstable compression. + ``chunk_length_in_kb`` 64KB On disk SSTables are compressed by block (to allow random reads). This + defines the size (in KB) of said block. Bigger values may improve the + compression rate, but increases the minimum size of data to be read from disk + for a read + ``crc_check_chance`` 1.0 When compression is enabled, each compressed block includes a checksum of + that block for the purpose of detecting disk bitrot 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 every other read | +========================= =============== ============================================================================= + +.. _cql-caching-options: + +Caching options +############### + +The ``caching`` options allows to configure both the *key cache* and the *row cache* for the table. The following +sub-options are available: + +======================== ========= ==================================================================================== + Option Default Description +======================== ========= ==================================================================================== + ``keys`` ALL Whether to cache keys (âkey cacheâ) for this table. Valid values are: ``ALL`` and + ``NONE``. + ``rows_per_partition`` NONE The amount of rows to cache per partition (ârow cacheâ). If an integer ``n`` is + specified, the first ``n`` queried rows of a partition will be cached. Other + possible options are ``ALL``, to cache all rows of a queried partition, or ``NONE`` + to disable row caching. +======================== ========= ==================================================================================== + +Other considerations: +##################### + +- Adding new columns (see ``ALTER TABLE`` below) is a constant time operation. There is thus no need to try to + anticipate future usage when creating a table. + +.. _alter-table-statement: + +ALTER TABLE +^^^^^^^^^^^ + +Altering an existing table uses the ``ALTER TABLE`` statement: + +.. productionlist:: + alter_table_statement: ALTER TABLE `table_name` `alter_table_instruction` + alter_table_instruction: ALTER `column_name` TYPE `cql_type` + : | ADD `column_name` `cql_type` ( ',' `column_name` `cql_type` )* + : | DROP `column_name` ( `column_name` )* + : | WITH `options` + +For instance:: + + ALTER TABLE addamsFamily ALTER lastKnownLocation TYPE uuid; + + ALTER TABLE addamsFamily ADD gravesite varchar; + + ALTER TABLE addamsFamily + WITH comment = 'A most excellent and useful table' + AND read_repair_chance = 0.2; + +The ``ALTER TABLE`` statement can: + +- Change the type of one of the column in the table (through the ``ALTER`` instruction). Note that the type of a column + cannot be changed arbitrarily. The change of type should be such that any value of the previous type should be a valid + value of the new type. Further, for :ref:`clustering columns <clustering-columns>` and columns on which a secondary + index is defined, the new type must sort values in the same way the previous type does. See the :ref:`type + compatibility table <alter-table-type-compatibility>` below for detail on which type changes are accepted. +- Add new column(s) to the table (through the ``ADD`` instruction). Note that the primary key of a table cannot be + changed and thus newly added column will, by extension, never be part of the primary key. Also note that :ref:`compact + tables <compact-tables>` have restrictions regarding column addition. Note that this is constant (in the amount of + data the cluster contains) time operation. +- Remove column(s) from the table. This drops both the column and all its content, but note that while the column + becomes immediately unavailable, its content is only removed lazily during compaction. Please also see the warnings + below. Due to lazy removal, the altering itself is a constant (in the amount of data removed or contained in the + cluster) time operation. +- Change some of the table options (through the ``WITH`` instruction). The :ref:`supported options + <create-table-options>` are the same that when creating a table (outside of ``COMPACT STORAGE`` and ``CLUSTERING + ORDER`` that cannot be changed after creation). Note that setting any ``compaction`` sub-options has the effect of + erasing all previous ``compaction`` options, so you need to re-specify all the sub-options if you want to keep them. + The same note applies to the set of ``compression`` sub-options. + +.. warning:: Dropping a column assumes that the timestamps used for the value of this column are "real" timestamp in + microseconds. Using "real" timestamps in microseconds is the default is and is **strongly** recommended but as + Cassandra allows the client to provide any timestamp on any table it is theoretically possible to use another + convention. Please be aware that if you do so, dropping a column will not work correctly. + +.. warning:: Once a column is dropped, it is allowed to re-add a column with the same name than the dropped one + **unless** the type of the dropped column was a (non-frozen) column (due to an internal technical limitation). + +.. _alter-table-type-compatibility: + +CQL type compatibility: +~~~~~~~~~~~~~~~~~~~~~~~ + +CQL data types may be converted only as the following table. + ++-------------------------------------------------------+--------------------+ +| Existing type | Can be altered to: | ++=======================================================+====================+ +| timestamp | bigint | ++-------------------------------------------------------+--------------------+ +| ascii, bigint, boolean, date, decimal, double, float, | blob | +| inet, int, smallint, text, time, timestamp, timeuuid, | | +| tinyint, uuid, varchar, varint | | ++-------------------------------------------------------+--------------------+ +| int | date | ++-------------------------------------------------------+--------------------+ +| ascii, varchar | text | ++-------------------------------------------------------+--------------------+ +| bigint | time | ++-------------------------------------------------------+--------------------+ +| bigint | timestamp | ++-------------------------------------------------------+--------------------+ +| timeuuid | uuid | ++-------------------------------------------------------+--------------------+ +| ascii, text | varchar | ++-------------------------------------------------------+--------------------+ +| bigint, int, timestamp | varint | ++-------------------------------------------------------+--------------------+ + +Clustering columns have stricter requirements, only the following conversions are allowed: + ++------------------------+----------------------+ +| Existing type | Can be altered to | ++========================+======================+ +| ascii, text, varchar | blob | ++------------------------+----------------------+ +| ascii, varchar | text | ++------------------------+----------------------+ +| ascii, text | varchar | ++------------------------+----------------------+ + +.. _drop-table-statement: + +DROP TABLE +^^^^^^^^^^ + +Dropping a table uses the ``DROP TABLE`` statement: + +.. productionlist:: + drop_table_statement: DROP TABLE [ IF EXISTS ] `table_name` + +Dropping a table results in the immediate, irreversible removal of the table, including all data it contains. + +If the table does not exist, the statement will return an error, unless ``IF EXISTS`` is used in which case the +operation is a no-op. + +.. _truncate-statement: + +TRUNCATE +^^^^^^^^ + +A table can be truncated using the ``TRUNCATE`` statement: + +.. productionlist:: + truncate_statement: TRUNCATE [ TABLE ] `table_name` + +Note that ``TRUNCATE TABLE foo`` is allowed for consistency with other DDL statements but tables are the only object +that can be truncated currently and so the ``TABLE`` keyword can be omitted. + +Truncating a table permanently removes all existing data from the table, but without removing the table itself. Added: cassandra/site/src/doc/3.10/_sources/cql/definitions.txt URL: http://svn.apache.org/viewvc/cassandra/site/src/doc/3.10/_sources/cql/definitions.txt?rev=1757419&view=auto ============================================================================== --- cassandra/site/src/doc/3.10/_sources/cql/definitions.txt (added) +++ cassandra/site/src/doc/3.10/_sources/cql/definitions.txt Tue Aug 23 19:25:17 2016 @@ -0,0 +1,232 @@ +.. Licensed to the Apache Software Foundation (ASF) under one +.. or more contributor license agreements. See the NOTICE file +.. distributed with this work for additional information +.. regarding copyright ownership. The ASF licenses this file +.. to you under the Apache License, Version 2.0 (the +.. "License"); you may not use this file except in compliance +.. with the License. You may obtain a copy of the License at +.. +.. http://www.apache.org/licenses/LICENSE-2.0 +.. +.. Unless required by applicable law or agreed to in writing, software +.. distributed under the License is distributed on an "AS IS" BASIS, +.. WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +.. See the License for the specific language governing permissions and +.. limitations under the License. + +.. _UUID: https://en.wikipedia.org/wiki/Universally_unique_identifier + +.. highlight:: cql + +Definitions +----------- + +.. _conventions: + +Conventions +^^^^^^^^^^^ + +To aid in specifying the CQL syntax, we will use the following conventions in this document: + +- Language rules will be given in an informal `BNF variant + <http://en.wikipedia.org/wiki/Backus%E2%80%93Naur_Form#Variants>`_ notation. In particular, we'll use square brakets + (``[ item ]``) for optional items, ``*`` and ``+`` for repeated items (where ``+`` imply at least one). +- The grammar will also use the following convention for convenience: non-terminal term will be lowercase (and link to + their definition) while terminal keywords will be provided "all caps". Note however that keywords are + :ref:`identifiers` and are thus case insensitive in practice. We will also define some early construction using + regexp, which we'll indicate with ``re(<some regular expression>)``. +- The grammar is provided for documentation purposes and leave some minor details out. For instance, the comma on the + last column definition in a ``CREATE TABLE`` statement is optional but supported if present even though the grammar in + this document suggests otherwise. Also, not everything accepted by the grammar is necessarily valid CQL. +- References to keywords or pieces of CQL code in running text will be shown in a ``fixed-width font``. + + +.. _identifiers: + +Identifiers and keywords +^^^^^^^^^^^^^^^^^^^^^^^^ + +The CQL language uses *identifiers* (or *names*) to identify tables, columns and other objects. An identifier is a token +matching the regular expression ``[a-zA-Z][a-zA-Z0-9_]*``. + +A number of such identifiers, like ``SELECT`` or ``WITH``, are *keywords*. They have a fixed meaning for the language +and most are reserved. The list of those keywords can be found in :ref:`appendix-A`. + +Identifiers and (unquoted) keywords are case insensitive. Thus ``SELECT`` is the same than ``select`` or ``sElEcT``, and +``myId`` is the same than ``myid`` or ``MYID``. A convention often used (in particular by the samples of this +documentation) is to use upper case for keywords and lower case for other identifiers. + +There is a second kind of identifiers called *quoted identifiers* defined by enclosing an arbitrary sequence of +characters (non empty) in double-quotes(``"``). Quoted identifiers are never keywords. Thus ``"select"`` is not a +reserved keyword and can be used to refer to a column (note that using this is particularly advised), while ``select`` +would raise a parsing error. Also, contrarily to unquoted identifiers and keywords, quoted identifiers are case +sensitive (``"My Quoted Id"`` is *different* from ``"my quoted id"``). A fully lowercase quoted identifier that matches +``[a-zA-Z][a-zA-Z0-9_]*`` is however *equivalent* to the unquoted identifier obtained by removing the double-quote (so +``"myid"`` is equivalent to ``myid`` and to ``myId`` but different from ``"myId"``). Inside a quoted identifier, the +double-quote character can be repeated to escape it, so ``"foo "" bar"`` is a valid identifier. + +.. note:: *quoted identifiers* allows to declare columns with arbitrary names, and those can sometime clash with + specific names used by the server. For instance, when using conditional update, the server will respond with a + result-set containing a special result named ``"[applied]"``. If youâve declared a column with such a name, this + could potentially confuse some tools and should be avoided. In general, unquoted identifiers should be preferred but + if you use quoted identifiers, it is strongly advised to avoid any name enclosed by squared brackets (like + ``"[applied]"``) and any name that looks like a function call (like ``"f(x)"``). + +More formally, we have: + +.. productionlist:: + identifier: `unquoted_identifier` | `quoted_identifier` + unquoted_identifier: re('[a-zA-Z][a-zA-Z0-9_]*') + quoted_identifier: '"' (any character where " can appear if doubled)+ '"' + +.. _constants: + +Constants +^^^^^^^^^ + +CQL defines the following kind of *constants*: + +.. productionlist:: + constant: `string` | `integer` | `float` | `boolean` | `uuid` | `blob` | NULL + string: '\'' (any character where ' can appear if doubled)+ '\'' + : '$$' (any character other than '$$') '$$' + integer: re('-?[0-9]+') + float: re('-?[0-9]+(\.[0-9]*)?([eE][+-]?[0-9+])?') | NAN | INFINITY + boolean: TRUE | FALSE + uuid: `hex`{8}-`hex`{4}-`hex`{4}-`hex`{4}-`hex`{12} + hex: re("[0-9a-fA-F]") + blob: '0' ('x' | 'X') `hex`+ + +In other words: + +- A string constant is an arbitrary sequence of characters enclosed by single-quote(``'``). A single-quote + can be included by repeating it, e.g. ``'It''s raining today'``. Those are not to be confused with quoted + :ref:`identifiers` that use double-quotes. Alternatively, a string can be defined by enclosing the arbitrary sequence + of characters by two dollar characters, in which case single-quote can be use without escaping (``$$It's raining + today$$``). That latter form is often used when defining :ref:`user-defined functions <udfs>` to avoid having to + escape single-quote characters in function body (as they are more likely to occur than ``$$``). +- Integer, float and boolean constant are defined as expected. Note however than float allows the special ``NaN`` and + ``Infinity`` constants. +- CQL supports UUID_ constants. +- Blobs content are provided in hexadecimal and prefixed by ``0x``. +- The special ``NULL`` constant denotes the absence of value. + +For how these constants are typed, see the :ref:`data-types` section. + +Terms +^^^^^ + +CQL has the notion of a *term*, which denotes the kind of values that CQL support. Terms are defined by: + +.. productionlist:: + term: `constant` | `literal` | `function_call` | `type_hint` | `bind_marker` + literal: `collection_literal` | `udt_literal` | `tuple_literal` + function_call: `identifier` '(' [ `term` (',' `term`)* ] ')' + type_hint: '(' `cql_type` `)` term + bind_marker: '?' | ':' `identifier` + +A term is thus one of: + +- A :ref:`constant <constants>`. +- A literal for either :ref:`a collection <collections>`, :ref:`a user-defined type <udts>` or :ref:`a tuple <tuples>` + (see the linked sections for details). +- A function call: see :ref:`the section on functions <cql-functions>` for details on which :ref:`native function + <native-functions>` exists and how to define your own :ref:`user-defined ones <udfs>`. +- A *type hint*: see the :ref:`related section <type-hints>` for details. +- A bind marker, which denotes a variable to be bound at execution time. See the section on :ref:`prepared-statements` + for details. A bind marker can be either anonymous (``?``) or named (``:some_name``). The latter form provides a more + convenient way to refer to the variable for binding it and should generally be preferred. + + +Comments +^^^^^^^^ + +A comment in CQL is a line beginning by either double dashes (``--``) or double slash (``//``). + +Multi-line comments are also supported through enclosure within ``/*`` and ``*/`` (but nesting is not supported). + +:: + + -- This is a comment + // This is a comment too + /* This is + a multi-line comment */ + +Statements +^^^^^^^^^^ + +CQL consists of statements that can be divided in the following categories: + +- :ref:`data-definition` statements, to define and change how the data is stored (keyspaces and tables). +- :ref:`data-manipulation` statements, for selecting, inserting and deleting data. +- :ref:`secondary-indexes` statements. +- :ref:`materialized-views` statements. +- :ref:`cql-roles` statements. +- :ref:`cql-permissions` statements. +- :ref:`User-Defined Functions <udfs>` statements. +- :ref:`udts` statements. +- :ref:`cql-triggers` statements. + +All the statements are listed below and are described in the rest of this documentation (see links above): + +.. productionlist:: + cql_statement: `statement` [ ';' ] + statement: `ddl_statement` + : | `dml_statement` + : | `secondary_index_statement` + : | `materialized_view_statement` + : | `role_or_permission_statement` + : | `udf_statement` + : | `udt_statement` + : | `trigger_statement` + ddl_statement: `use_statement` + : | `create_keyspace_statement` + : | `alter_keyspace_statement` + : | `drop_keyspace_statement` + : | `create_table_statement` + : | `alter_table_statement` + : | `drop_table_statement` + : | `truncate_statement` + dml_statement: `select_statement` + : | `insert_statement` + : | `update_statement` + : | `delete_statement` + : | `batch_statement` + secondary_index_statement: `create_index_statement` + : | `drop_index_statement` + materialized_view_statement: `create_materialized_view_statement` + : | `drop_materialized_view_statement` + role_or_permission_statement: `create_role_statement` + : | `alter_role_statement` + : | `drop_role_statement` + : | `grant_role_statement` + : | `revoke_role_statement` + : | `list_roles_statement` + : | `grant_permission_statement` + : | `revoke_permission_statement` + : | `list_permissions_statement` + : | `create_user_statement` + : | `alter_user_statement` + : | `drop_user_statement` + : | `list_users_statement` + udf_statement: `create_function_statement` + : | `drop_function_statement` + : | `create_aggregate_statement` + : | `drop_aggregate_statement` + udt_statement: `create_type_statement` + : | `alter_type_statement` + : | `drop_type_statement` + trigger_statement: `create_trigger_statement` + : | `drop_trigger_statement` + +.. _prepared-statements: + +Prepared Statements +^^^^^^^^^^^^^^^^^^^ + +CQL supports *prepared statements*. Prepared statements are an optimization that allows to parse a query only once but +execute it multiple times with different concrete values. + +Any statement that uses at least one bind marker (see :token:`bind_marker`) will need to be *prepared*. After which the statement +can be *executed* by provided concrete values for each of its marker. The exact details of how a statement is prepared +and then executed depends on the CQL driver used and you should refer to your driver documentation. Added: cassandra/site/src/doc/3.10/_sources/cql/dml.txt URL: http://svn.apache.org/viewvc/cassandra/site/src/doc/3.10/_sources/cql/dml.txt?rev=1757419&view=auto ============================================================================== --- cassandra/site/src/doc/3.10/_sources/cql/dml.txt (added) +++ cassandra/site/src/doc/3.10/_sources/cql/dml.txt Tue Aug 23 19:25:17 2016 @@ -0,0 +1,521 @@ +.. Licensed to the Apache Software Foundation (ASF) under one +.. or more contributor license agreements. See the NOTICE file +.. distributed with this work for additional information +.. regarding copyright ownership. The ASF licenses this file +.. to you under the Apache License, Version 2.0 (the +.. "License"); you may not use this file except in compliance +.. with the License. You may obtain a copy of the License at +.. +.. http://www.apache.org/licenses/LICENSE-2.0 +.. +.. Unless required by applicable law or agreed to in writing, software +.. distributed under the License is distributed on an "AS IS" BASIS, +.. WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +.. See the License for the specific language governing permissions and +.. limitations under the License. + +.. highlight:: cql + +.. _data-manipulation: + +Data Manipulation +----------------- + +This section describes the statements supported by CQL to insert, update, delete and query data. + +.. _select-statement: + +SELECT +^^^^^^ + +Querying data from data is done using a ``SELECT`` statement: + +.. productionlist:: + select_statement: SELECT [ JSON | DISTINCT ] ( `select_clause` | '*' ) + : FROM `table_name` + : [ WHERE `where_clause` ] + : [ GROUP BY `group_by_clause` ] + : [ ORDER BY `ordering_clause` ] + : [ PER PARTITION LIMIT (`integer` | `bind_marker`) ] + : [ LIMIT (`integer` | `bind_marker`) ] + : [ ALLOW FILTERING ] + select_clause: `selector` [ AS `identifier` ] ( ',' `selector` [ AS `identifier` ] ) + selector: `column_name` + : | `term` + : | CAST '(' `selector` AS `cql_type` ')' + : | `function_name` '(' [ `selector` ( ',' `selector` )* ] ')' + : | COUNT '(' '*' ')' + where_clause: `relation` ( AND `relation` )* + relation: `column_name` `operator` `term` + : '(' `column_name` ( ',' `column_name` )* ')' `operator` `tuple_literal` + : TOKEN '(' `column_name` ( ',' `column_name` )* ')' `operator` `term` + operator: '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY + group_by_clause: `column_name` ( ',' `column_name` )* + ordering_clause: `column_name` [ ASC | DESC ] ( ',' `column_name` [ ASC | DESC ] )* + +For instance:: + + SELECT name, occupation FROM users WHERE userid IN (199, 200, 207); + SELECT JSON name, occupation FROM users WHERE userid = 199; + SELECT name AS user_name, occupation AS user_occupation FROM users; + + SELECT time, value + FROM events + WHERE event_type = 'myEvent' + AND time > '2011-02-03' + AND time <= '2012-01-01' + + SELECT COUNT (*) AS user_count FROM users; + +The ``SELECT`` statements reads one or more columns for one or more rows in a table. It returns a result-set of the rows +matching the request, where each row contains the values for the selection corresponding to the query. Additionally, +:ref:`functions <cql-functions>` including :ref:`aggregation <aggregate-functions>` ones can be applied to the result. + +A ``SELECT`` statement contains at least a :ref:`selection clause <selection-clause>` and the name of the table on which +the selection is on (note that CQL does **not** joins or sub-queries and thus a select statement only apply to a single +table). In most case, a select will also have a :ref:`where clause <where-clause>` and it can optionally have additional +clauses to :ref:`order <ordering-clause>` or :ref:`limit <limit-clause>` the results. Lastly, :ref:`queries that require +filtering <allow-filtering>` can be allowed if the ``ALLOW FILTERING`` flag is provided. + +.. _selection-clause: + +Selection clause +~~~~~~~~~~~~~~~~ + +The :token:`select_clause` determines which columns needs to be queried and returned in the result-set, as well as any +transformation to apply to this result before returning. It consists of a comma-separated list of *selectors* or, +alternatively, of the wildcard character (``*``) to select all the columns defined in the table. + +Selectors +````````` + +A :token:`selector` can be one of: + +- A column name of the table selected, to retrieve the values for that column. +- A term, which is usually used nested inside other selectors like functions (if a term is selected directly, then the + corresponding column of the result-set will simply have the value of this term for every row returned). +- A casting, which allows to convert a nested selector to a (compatible) type. +- A function call, where the arguments are selector themselves. See the section on :ref:`functions <cql-functions>` for + more details. +- The special call ``COUNT(*)`` to the :ref:`COUNT function <count-function>`, which counts all non-null results. + +Aliases +``````` + +Every *top-level* selector can also be aliased (using `AS`). If so, the name of the corresponding column in the result +set will be that of the alias. For instance:: + + // Without alias + SELECT intAsBlob(4) FROM t; + + // intAsBlob(4) + // -------------- + // 0x00000004 + + // With alias + SELECT intAsBlob(4) AS four FROM t; + + // four + // ------------ + // 0x00000004 + +.. note:: Currently, aliases aren't recognized anywhere else in the statement where they are used (not in the ``WHERE`` + clause, not in the ``ORDER BY`` clause, ...). You must use the orignal column name instead. + + +``WRITETIME`` and ``TTL`` function +``````````````````````````````````` + +Selection supports two special functions (that aren't allowed anywhere else): ``WRITETIME`` and ``TTL``. Both function +take only one argument and that argument *must* be a column name (so for instance ``TTL(3)`` is invalid). + +Those functions allow to retrieve meta-information that are stored internally for each column, namely: + +- the timestamp of the value of the column for ``WRITETIME``. +- the remaining time to live (in seconds) for the value of the column if it set to expire (and ``null`` otherwise). + +.. _where-clause: + +The ``WHERE`` clause +~~~~~~~~~~~~~~~~~~~~ + +The ``WHERE`` clause specifies which rows must be queried. It is composed of relations on the columns that are part of +the ``PRIMARY KEY`` and/or have a `secondary index <#createIndexStmt>`__ defined on them. + +Not all relations are allowed in a query. For instance, non-equal relations (where ``IN`` is considered as an equal +relation) on a partition key are not supported (but see the use of the ``TOKEN`` method below to do non-equal queries on +the partition key). Moreover, for a given partition key, the clustering columns induce an ordering of rows and relations +on them is restricted to the relations that allow to select a **contiguous** (for the ordering) set of rows. For +instance, given:: + + CREATE TABLE posts ( + userid text, + blog_title text, + posted_at timestamp, + entry_title text, + content text, + category int, + PRIMARY KEY (userid, blog_title, posted_at) + ) + +The following query is allowed:: + + SELECT entry_title, content FROM posts + WHERE userid = 'john doe' + AND blog_title='John''s Blog' + AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31' + +But the following one is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are +set):: + + // Needs a blog_title to be set to select ranges of posted_at + SELECT entry_title, content FROM posts + WHERE userid = 'john doe' + AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31' + +When specifying relations, the ``TOKEN`` function can be used on the ``PARTITION KEY`` column to query. In that case, +rows will be selected based on the token of their ``PARTITION_KEY`` rather than on the value. Note that the token of a +key depends on the partitioner in use, and that in particular the RandomPartitioner won't yield a meaningful order. Also +note that ordering partitioners always order token values by bytes (so even if the partition key is of type int, +``token(-1) > token(0)`` in particular). Example:: + + SELECT * FROM posts + WHERE token(userid) > token('tom') AND token(userid) < token('bob') + +Moreover, the ``IN`` relation is only allowed on the last column of the partition key and on the last column of the full +primary key. + +It is also possible to âgroupâ ``CLUSTERING COLUMNS`` together in a relation using the tuple notation. For instance:: + + SELECT * FROM posts + WHERE userid = 'john doe' + AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01') + +will request all rows that sorts after the one having âJohn's Blogâ as ``blog_tile`` and '2012-01-01' for ``posted_at`` +in the clustering order. In particular, rows having a ``post_at <= '2012-01-01'`` will be returned as long as their +``blog_title > 'John''s Blog'``, which would not be the case for:: + + SELECT * FROM posts + WHERE userid = 'john doe' + AND blog_title > 'John''s Blog' + AND posted_at > '2012-01-01' + +The tuple notation may also be used for ``IN`` clauses on clustering columns:: + + SELECT * FROM posts + WHERE userid = 'john doe' + AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01'), ('Extreme Chess', '2014-06-01')) + +The ``CONTAINS`` operator may only be used on collection columns (lists, sets, and maps). In the case of maps, +``CONTAINS`` applies to the map values. The ``CONTAINS KEY`` operator may only be used on map columns and applies to the +map keys. + +.. _group-by-clause: + +Grouping results +~~~~~~~~~~~~~~~~ + +The ``GROUP BY`` option allows to condense into a single row all selected rows that share the same values for a set +of columns. + +Using the ``GROUP BY`` option, it is only possible to group rows at the partition key level or at a clustering column +level. By consequence, the ``GROUP BY`` option only accept as arguments primary key column names in the primary key +order. If a primary key column is restricted by an equality restriction it is not required to be present in the +``GROUP BY`` clause. + +Aggregate functions will produce a separate value for each group. If no ``GROUP BY`` clause is specified, +aggregates functions will produce a single value for all the rows. + +If a column is selected without an aggregate function, in a statement with a ``GROUP BY``, the first value encounter +in each group will be returned. + +.. _ordering-clause: + +Ordering results +~~~~~~~~~~~~~~~~ + +The ``ORDER BY`` clause allows to select the order of the returned results. It takes as argument a list of column names +along with the order for the column (``ASC`` for ascendant and ``DESC`` for descendant, omitting the order being +equivalent to ``ASC``). Currently the possible orderings are limited by the :ref:`clustering order <clustering-order>` +defined on the table: + +- if the table has been defined without any specific ``CLUSTERING ORDER``, then then allowed orderings are the order + induced by the clustering columns and the reverse of that one. +- otherwise, the orderings allowed are the order of the ``CLUSTERING ORDER`` option and the reversed one. + +.. _limit-clause: + +Limiting results +~~~~~~~~~~~~~~~~ + +The ``LIMIT`` option to a ``SELECT`` statement limits the number of rows returned by a query, while the ``PER PARTITION +LIMIT`` option limits the number of rows returned for a given partition by the query. Note that both type of limit can +used in the same statement. + +.. _allow-filtering: + +Allowing filtering +~~~~~~~~~~~~~~~~~~ + +By default, CQL only allows select queries that don't involve âfilteringâ server side, i.e. queries where we know that +all (live) record read will be returned (maybe partly) in the result set. The reasoning is that those ânon filteringâ +queries have predictable performance in the sense that they will execute in a time that is proportional to the amount of +data **returned** by the query (which can be controlled through ``LIMIT``). + +The ``ALLOW FILTERING`` option allows to explicitly allow (some) queries that require filtering. Please note that a +query using ``ALLOW FILTERING`` may thus have unpredictable performance (for the definition above), i.e. even a query +that selects a handful of records **may** exhibit performance that depends on the total amount of data stored in the +cluster. + +For instance, considering the following table holding user profiles with their year of birth (with a secondary index on +it) and country of residence:: + + CREATE TABLE users ( + username text PRIMARY KEY, + firstname text, + lastname text, + birth_year int, + country text + ) + + CREATE INDEX ON users(birth_year); + +Then the following queries are valid:: + + SELECT * FROM users; + SELECT * FROM users WHERE birth_year = 1981; + +because in both case, Cassandra guarantees that these queries performance will be proportional to the amount of data +returned. In particular, if no users are born in 1981, then the second query performance will not depend of the number +of user profile stored in the database (not directly at least: due to secondary index implementation consideration, this +query may still depend on the number of node in the cluster, which indirectly depends on the amount of data stored. +Nevertheless, the number of nodes will always be multiple number of magnitude lower than the number of user profile +stored). Of course, both query may return very large result set in practice, but the amount of data returned can always +be controlled by adding a ``LIMIT``. + +However, the following query will be rejected:: + + SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR'; + +because Cassandra cannot guarantee that it won't have to scan large amount of data even if the result to those query is +small. Typically, it will scan all the index entries for users born in 1981 even if only a handful are actually from +France. However, if you âknow what you are doingâ, you can force the execution of this query by using ``ALLOW +FILTERING`` and so the following query is valid:: + + SELECT * FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING; + +.. _insert-statement: + +INSERT +^^^^^^ + +Inserting data for a row is done using an ``INSERT`` statement: + +.. productionlist:: + insert_statement: INSERT INTO `table_name` ( `names_values` | `json_clause` ) + : [ IF NOT EXISTS ] + : [ USING `update_parameter` ( AND `update_parameter` )* ] + names_values: `names` VALUES `tuple_literal` + json_clause: JSON `string` [ DEFAULT ( NULL | UNSET ) ] + names: '(' `column_name` ( ',' `column_name` )* ')' + +For instance:: + + INSERT INTO NerdMovies (movie, director, main_actor, year) + VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005) + USING TTL 86400; + + INSERT INTO NerdMovies JSON '{"movie": "Serenity", + "director": "Joss Whedon", + "year": 2005}'; + +The ``INSERT`` statement writes one or more columns for a given row in a table. Note that since a row is identified by +its ``PRIMARY KEY``, at least the columns composing it must be specified. The list of columns to insert to must be +supplied when using the ``VALUES`` syntax. When using the ``JSON`` syntax, they are optional. See the +section on :ref:`JSON support <cql-json>` for more detail. + +Note that unlike in SQL, ``INSERT`` does not check the prior existence of the row by default: the row is created if none +existed before, and updated otherwise. Furthermore, there is no mean to know which of creation or update happened. + +It is however possible to use the ``IF NOT EXISTS`` condition to only insert if the row does not exist prior to the +insertion. But please note that using ``IF NOT EXISTS`` will incur a non negligible performance cost (internally, Paxos +will be used) so this should be used sparingly. + +All updates for an ``INSERT`` are applied atomically and in isolation. + +Please refer to the :ref:`UPDATE <update-parameters>` section for informations on the :token:`update_parameter`. + +Also note that ``INSERT`` does not support counters, while ``UPDATE`` does. + +.. _update-statement: + +UPDATE +^^^^^^ + +Updating a row is done using an ``UPDATE`` statement: + +.. productionlist:: + update_statement: UPDATE `table_name` + : [ USING `update_parameter` ( AND `update_parameter` )* ] + : SET `assignment` ( ',' `assignment` )* + : WHERE `where_clause` + : [ IF ( EXISTS | `condition` ( AND `condition` )*) ] + update_parameter: ( TIMESTAMP | TTL ) ( `integer` | `bind_marker` ) + assignment: `simple_selection` '=' `term` + :| `column_name` '=' `column_name` ( '+' | '-' ) `term` + :| `column_name` '=' `list_literal` '+' `column_name` + simple_selection: `column_name` + :| `column_name` '[' `term` ']' + :| `column_name` '.' `field_name + condition: `simple_selection` `operator` `term` + +For instance:: + + UPDATE NerdMovies USING TTL 400 + SET director = 'Joss Whedon', + main_actor = 'Nathan Fillion', + year = 2005 + WHERE movie = 'Serenity'; + + UPDATE UserActions + SET total = total + 2 + WHERE user = B70DE1D0-9908-4AE3-BE34-5573E5B09F14 + AND action = 'click'; + +The ``UPDATE`` statement writes one or more columns for a given row in a table. The :token:`where_clause` is used to +select the row to update and must include all columns composing the ``PRIMARY KEY``. Non primary key columns are then +set using the ``SET`` keyword. + +Note that unlike in SQL, ``UPDATE`` does not check the prior existence of the row by default (except through ``IF``, see +below): the row is created if none existed before, and updated otherwise. Furthermore, there are no means to know +whether a creation or update occurred. + +It is however possible to use the conditions on some columns through ``IF``, in which case the row will not be updated +unless the conditions are met. But, please note that using ``IF`` conditions will incur a non-negligible performance +cost (internally, Paxos will be used) so this should be used sparingly. + +In an ``UPDATE`` statement, all updates within the same partition key are applied atomically and in isolation. + +Regarding the :token:`assignment`: + +- ``c = c + 3`` is used to increment/decrement counters. The column name after the '=' sign **must** be the same than + the one before the '=' sign. Note that increment/decrement is only allowed on counters, and are the *only* update + operations allowed on counters. See the section on :ref:`counters <counters>` for details. +- ``id = id + <some-collection>`` and ``id[value1] = value2`` are for collections, see the :ref:`relevant section + <collections>` for details. +- ``id.field = 3`` is for setting the value of a field on a non-frozen user-defined types. see the :ref:`relevant section + <udts>` for details. + +.. _update-parameters: + +Update parameters +~~~~~~~~~~~~~~~~~ + +The ``UPDATE``, ``INSERT`` (and ``DELETE`` and ``BATCH`` for the ``TIMESTAMP``) statements support the following +parameters: + +- ``TIMESTAMP``: sets the timestamp for the operation. If not specified, the coordinator will use the current time (in + microseconds) at the start of statement execution as the timestamp. This is usually a suitable default. +- ``TTL``: specifies an optional Time To Live (in seconds) for the inserted values. If set, the inserted values are + automatically removed from the database after the specified time. Note that the TTL concerns the inserted values, not + the columns themselves. This means that any subsequent update of the column will also reset the TTL (to whatever TTL + is specified in that update). By default, values never expire. A TTL of 0 is equivalent to no TTL. If the table has a + default_time_to_live, a TTL of 0 will remove the TTL for the inserted or updated values. A TTL of ``null`` is equivalent + to inserting with a TTL of 0. + +.. _delete_statement: + +DELETE +^^^^^^ + +Deleting rows or parts of rows uses the ``DELETE`` statement: + +.. productionlist:: + delete_statement: DELETE [ `simple_selection` ( ',' `simple_selection` ) ] + : FROM `table_name` + : [ USING `update_parameter` ( AND `update_parameter` )* ] + : WHERE `where_clause` + : [ IF ( EXISTS | `condition` ( AND `condition` )*) ] + +For instance:: + + DELETE FROM NerdMovies USING TIMESTAMP 1240003134 + WHERE movie = 'Serenity'; + + DELETE phone FROM Users + WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14); + +The ``DELETE`` statement deletes columns and rows. If column names are provided directly after the ``DELETE`` keyword, +only those columns are deleted from the row indicated by the ``WHERE`` clause. Otherwise, whole rows are removed. + +The ``WHERE`` clause specifies which rows are to be deleted. Multiple rows may be deleted with one statement by using an +``IN`` operator. A range of rows may be deleted using an inequality operator (such as ``>=``). + +``DELETE`` supports the ``TIMESTAMP`` option with the same semantics as in :ref:`updates <update-parameters>`. + +In a ``DELETE`` statement, all deletions within the same partition key are applied atomically and in isolation. + +A ``DELETE`` operation can be conditional through the use of an ``IF`` clause, similar to ``UPDATE`` and ``INSERT`` +statements. However, as with ``INSERT`` and ``UPDATE`` statements, this will incur a non-negligible performance cost +(internally, Paxos will be used) and so should be used sparingly. + +.. _batch_statement: + +BATCH +^^^^^ + +Multiple ``INSERT``, ``UPDATE`` and ``DELETE`` can be executed in a single statement by grouping them through a +``BATCH`` statement: + +.. productionlist:: + batch_statement: BEGIN [ UNLOGGED | COUNTER ] BATCH + : [ USING `update_parameter` ( AND `update_parameter` )* ] + : `modification_statement` ( ';' `modification_statement` )* + : APPLY BATCH + modification_statement: `insert_statement` | `update_statement` | `delete_statement` + +For instance:: + + BEGIN BATCH + INSERT INTO users (userid, password, name) VALUES ('user2', 'ch@ngem3b', 'second user'); + UPDATE users SET password = 'ps22dhds' WHERE userid = 'user3'; + INSERT INTO users (userid, password) VALUES ('user4', 'ch@ngem3c'); + DELETE name FROM users WHERE userid = 'user1'; + APPLY BATCH; + +The ``BATCH`` statement group multiple modification statements (insertions/updates and deletions) into a single +statement. It serves several purposes: + +- It saves network round-trips between the client and the server (and sometimes between the server coordinator and the + replicas) when batching multiple updates. +- All updates in a ``BATCH`` belonging to a given partition key are performed in isolation. +- By default, all operations in the batch are performed as *logged*, to ensure all mutations eventually complete (or + none will). See the notes on :ref:`UNLOGGED batches <unlogged-batches>` for more details. + +Note that: + +- ``BATCH`` statements may only contain ``UPDATE``, ``INSERT`` and ``DELETE`` statements (not other batches for instance). +- Batches are *not* a full analogue for SQL transactions. +- If a timestamp is not specified for each operation, then all operations will be applied with the same timestamp + (either one generated automatically, or the timestamp provided at the batch level). Due to Cassandra's conflict + resolution procedure in the case of `timestamp ties <http://wiki.apache.org/cassandra/FAQ#clocktie>`__, operations may + be applied in an order that is different from the order they are listed in the ``BATCH`` statement. To force a + particular operation ordering, you must specify per-operation timestamps. + +.. _unlogged-batches: + +``UNLOGGED`` batches +~~~~~~~~~~~~~~~~~~~~ + +By default, Cassandra uses a batch log to ensure all operations in a batch eventually complete or none will (note +however that operations are only isolated within a single partition). + +There is a performance penalty for batch atomicity when a batch spans multiple partitions. If you do not want to incur +this penalty, you can tell Cassandra to skip the batchlog with the ``UNLOGGED`` option. If the ``UNLOGGED`` option is +used, a failed batch might leave the patch only partly applied. + +``COUNTER`` batches +~~~~~~~~~~~~~~~~~~~ + +Use the ``COUNTER`` option for batched counter updates. Unlike other +updates in Cassandra, counter updates are not idempotent.
