Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 1661 lines (1147 sloc) 92.411 kb
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1 <link rel="StyleSheet" href="CQL.css" type="text/css" media="screen">
2
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
3 h1. Cassandra Query Language (CQL) v3.3.0
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
4
5
6 <span id="tableOfContents">
7
8 {toc:maxLevel=3}
9
10 </span>
11
12 h2. CQL Syntax
13
14 h3. Preamble
15
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
16 This document describes the Cassandra Query Language (CQL) version 3. CQL v3 is not backward compatible with CQL v2 and differs from it in numerous ways. Note that this document describes the last version of the languages. However, the "changes":#changes section provides the diff between the different versions of CQL v3.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
17
18 CQL v3 offers a model very close to SQL in the sense that data is put in _tables_ containing _rows_ of _columns_. For that reason, when used in this document, these terms (tables, rows and columns) have the same definition than they have in SQL. But please note that as such, they do *not* refer to the concept of rows and columns found in the internal implementation of Cassandra and in the thrift and CQL v2 API.
19
20
21 h3. Conventions
22
23 To aid in specifying the CQL syntax, we will use the following conventions in this document:
24
25 * Language rules will be given in a "BNF":http://en.wikipedia.org/wiki/Backus%E2%80%93Naur_Form -like notation:
26
27 bc(syntax). <start> ::= TERMINAL <non-terminal1> <non-terminal1>
28
29 * Nonterminal symbols will have @<angle brackets>@.
30 * As additional shortcut notations to BNF, we'll use traditional regular expression's symbols (@?@, @+@ and @*@) to signify that a given symbol is optional and/or can be repeated. We'll also allow parentheses to group symbols and the @[<characters>]@ notation to represent any one of @<characters>@.
31 * The grammar is provided for documentation purposes and leave some minor details out. For instance, the last column definition in a @CREATE TABLE@ statement is optional but supported if present even though the provided grammar in this document suggest it is not supported.
32 * Sample code will be provided in a code block:
33
34 bc(sample). SELECT sample_usage FROM cql;
35
36 * References to keywords or pieces of CQL code in running text will be shown in a @fixed-width font@.
37
38 h3(#identifiers). Identifiers and keywords
39
40 p. 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_]@@*@.
41
42 p. 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 "Appendix A":#appendixA.
43
44 p. 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@ for instance. 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.
45
46 p. There is a second kind of identifiers called _quoted identifiers_ defined by enclosing an arbitrary sequence of characters in double-quotes(@"@). Quoted identifiers are never keywords. Thus @"select"@ is not a reserved keyword and can be used to refer to a column, while @select@ would raise a parse 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 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.
47
48 h3(#constants). Constants
49
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
50 CQL defines the following kind of _constants_: strings, integers, floats, booleans, uuids and blobs:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
51 * A string constant is an arbitrary sequence of characters characters enclosed by single-quote(@'@). One can include a single-quote in a string by repeating it, e.g. @'It''s raining today'@. Those are not to be confused with quoted identifiers that use double-quotes.
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
52 * An integer constant is defined by @'-'?[0-9]+@.
08f2e97 Sylvain Lebresne Support NaN and Infinity as float constants
pcmanus authored
53 * A float constant is defined by @'-'?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?@. On top of that, @NaN@ and @Infinity@ are also float constants.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
54 * A boolean constant is either @true@ or @false@ up to case-insensitivity (i.e. @True@ is a valid boolean constant).
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
55 * A "UUID":http://en.wikipedia.org/wiki/Universally_unique_identifier constant is defined by @hex{8}-hex{4}-hex{4}-hex{4}-hex{12}@ where @hex@ is an hexadecimal character, e.g. @[0-9a-fA-F]@ and @{4}@ is the number of such characters.
56 * A blob constant is an hexadecimal number defined by @0[xX](hex)+@ where @hex@ is an hexadecimal character, e.g. @[0-9a-fA-F]@.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
57
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
58 For how these constants are typed, see the "data types section":#types.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
59
60 h3. Comments
61
62 A comment in CQL is a line beginning by either double dashes (@--@) or double slash (@//@).
63
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
64 Multi-line comments are also supported through enclosure within @/*@ and @*/@ (but nesting is not supported).
66bd605 Sylvain Lebresne Document multiline comments
pcmanus authored
65
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
66 bc(sample).
67 -- This is a comment
68 // This is a comment too
66bd605 Sylvain Lebresne Document multiline comments
pcmanus authored
69 /* This is
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
70 a multi-line comment */
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
71
72 h3(#statements). Statements
73
74 CQL consists of statements. As in SQL, these statements can be divided in 3 categories:
75 * Data definition statements, that allow to set and change the way data is stored.
76 * Data manipulation statements, that allow to change data
77 * Queries, to look up data
78
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
79 All statements end with a semicolon (@;@) but that semicolon can be omitted when dealing with a single statement. The supported statements are described in the following sections. When describing the grammar of said statements, we will reuse the non-terminal symbols defined below:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
80
81 bc(syntax)..
82 <identifier> ::= any quoted or unquoted identifier, excluding reserved keywords
83 <tablename> ::= (<identifier> '.')? <identifier>
84
85 <string> ::= a string constant
86 <integer> ::= an integer constant
87 <float> ::= a float constant
88 <number> ::= <integer> | <float>
89 <uuid> ::= a uuid constant
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
90 <boolean> ::= a boolean constant
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
91 <hex> ::= a blob constant
92
93 <constant> ::= <string>
94 | <number>
95 | <uuid>
96 | <boolean>
97 | <hex>
98 <variable> ::= '?'
37e9bce Sylvain Lebresne Support named bind variables in CQL
pcmanus authored
99 | ':' <identifier>
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
100 <term> ::= <constant>
101 | <collection-literal>
102 | <variable>
103 | <function> '(' (<term> (',' <term>)*)? ')'
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
104
105 <collection-literal> ::= <map-literal>
106 | <set-literal>
107 | <list-literal>
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
108 <map-literal> ::= '{' ( <term> ':' <term> ( ',' <term> ':' <term> )* )? '}'
109 <set-literal> ::= '{' ( <term> ( ',' <term> )* )? '}'
110 <list-literal> ::= '[' ( <term> ( ',' <term> )* )? ']'
111
112 <function> ::= <ident>
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
113
114 <properties> ::= <property> (AND <property>)*
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
115 <property> ::= <identifier> '=' ( <identifier> | <constant> | <map-literal> )
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
116 p.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
117 Please note that not every possible productions of the grammar above will be valid in practice. Most notably, @<variable>@ and nested @<collection-literal>@ are currently not allowed inside @<collection-literal>@.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
118
37e9bce Sylvain Lebresne Support named bind variables in CQL
pcmanus authored
119 p. A @<variable>@ can be either anonymous (a question mark (@?@)) or named (an identifier preceded by @:@). Both declare a bind variables for "prepared statements":#preparedStatement. The only difference between an anymous and a named variable is that a named one will be easier to refer to (how exactly depends on the client driver used).
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
120
121 p. The @<properties>@ production is use by statement that create and alter keyspaces and tables. Each @<property>@ is either a _simple_ one, in which case it just has a value, or a _map_ one, in which case it's value is a map grouping sub-options. The following will refer to one or the other as the _kind_ (_simple_ or _map_) of the property.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
122
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
123 p. A @<tablename>@ will be used to identify a table. This is an identifier representing the table name that can be preceded by a keyspace name. The keyspace name, if provided, allow to identify a table in another keyspace than the currently active one (the currently active keyspace is set through the <a href="#useStmt"><tt>USE</tt></a> statement).
124
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
125 p. For supported @<function>@, see the section on "functions":#functions.
126
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
127 p. Strings can be either enclosed with single quotes or two dollar characters. The second syntax has been introduced to allow strings that contain single quotes. Typical candidates for such strings are source code fragments for user-defined functions.
128
129 __Sample:__
130
131 bc(sample)..
132 'some string value'
133
134 $$double-dollar string can contain single ' quotes$$
135 p.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
136
137 h3(#preparedStatement). Prepared Statement
138
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
139 CQL supports _prepared statements_. Prepared statement is an optimization that allows to parse a query only once but execute it multiple times with different concrete values.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
140
37e9bce Sylvain Lebresne Support named bind variables in CQL
pcmanus authored
141 In a statement, each time a column value is expected (in the data manipulation and query statements), a @<variable>@ (see above) can be used instead. A statement with bind variables must then be _prepared_. Once it has been prepared, it can executed by providing concrete values for the bind variables. The exact procedure to prepare a statement and execute a prepared statement depends on the CQL driver used and is beyond the scope of this document.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
142
143
144 h2(#dataDefinition). Data Definition
145
146 h3(#createKeyspaceStmt). CREATE KEYSPACE
147
148 __Syntax:__
149
150 bc(syntax)..
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
151 <create-keyspace-stmt> ::= CREATE KEYSPACE (IF NOT EXISTS)? <identifier> WITH <properties>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
152 p.
153 __Sample:__
154
155 bc(sample)..
156 CREATE KEYSPACE Excelsior
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
157 WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 3};
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
158
159 CREATE KEYSPACE Excalibur
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
160 WITH replication = {'class': 'NetworkTopologyStrategy', 'DC1' : 1, 'DC2' : 3}
161 AND durable_writes = false;
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
162 p.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
163 The @CREATE KEYSPACE@ statement creates a new top-level _keyspace_. A keyspace is a namespace that defines a replication strategy and some options for a set of tables. Valid keyspaces names are identifiers composed exclusively of alphanumerical characters and whose length is lesser or equal to 32. Note that as identifiers, keyspace names are case insensitive: use a quoted identifier for case sensitive keyspace names.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
164
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
165 The supported @<properties>@ for @CREATE KEYSPACE@ are:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
166
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
167 |_. name |_. kind |_. mandatory |_. default |_. description|
168 |@replication@ | _map_ | yes | | The replication strategy and options to use for the keyspace. |
169 |@durable_writes@ | _simple_ | no | true | Whether to use the commit log for updates on this keyspace (disable this option at your own risk!). |
170
171 The @replication@ @<property>@ is mandatory. It must at least contains the @'class'@ sub-option which defines the replication strategy class to use. The rest of the sub-options depends on that replication strategy class. By default, Cassandra support the following @'class'@:
172 * @'SimpleStrategy'@: A simple strategy that defines a simple replication factor for the whole cluster. The only sub-options supported is @'replication_factor'@ to define that replication factor and is mandatory.
173 * @'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 each time the key is the name of a datacenter and the value the replication factor for that data-center.
174 * @'OldNetworkTopologyStrategy'@: A legacy replication strategy. You should avoid this strategy for new keyspaces and prefer @'NetworkTopologyStrategy'@.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
175
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
176 Attempting to create an already existing keyspace 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.
177
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
178 h3(#useStmt). USE
179
180 __Syntax:__
181
182 bc(syntax). <use-stmt> ::= USE <identifier>
183
184 __Sample:__
185
186 bc(sample). USE myApp;
187
188 The @USE@ statement takes an existing keyspace name as argument and set it as the per-connection current working keyspace. All subsequent keyspace-specific actions will be performed in the context of the selected keyspace, unless "otherwise specified":#statements, until another USE statement is issued or the connection terminates.
189
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
190 h3(#alterKeyspaceStmt). ALTER KEYSPACE
191
192 __Syntax:__
193
194 bc(syntax)..
195 <create-keyspace-stmt> ::= ALTER KEYSPACE <identifier> WITH <properties>
196 p.
197 __Sample:__
198
199 bc(sample)..
200 ALTER KEYSPACE Excelsior
201 WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 4};
202
203 p.
204 The @ALTER KEYSPACE@ statement alter the properties of an existing keyspace. The supported @<properties>@ are the same that for the "@CREATE TABLE@":#createKeyspaceStmt statement.
205
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
206
207 h3(#dropKeyspaceStmt). DROP KEYSPACE
208
209 __Syntax:__
210
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
211 bc(syntax). <drop-keyspace-stmt> ::= DROP KEYSPACE ( IF EXISTS )? <identifier>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
212
213 __Sample:__
214
215 bc(sample). DROP KEYSPACE myApp;
216
217 A @DROP KEYSPACE@ statement results in the immediate, irreversible removal of an existing keyspace, including all column families in it, and all data contained in those column families.
218
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
219 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.
220
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
221
222 h3(#createTableStmt). CREATE TABLE
223
224 __Syntax:__
225
226 bc(syntax)..
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
227 <create-table-stmt> ::= CREATE ( TABLE | COLUMNFAMILY ) ( IF NOT EXISTS )? <tablename>
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
228 '(' <column-definition> ( ',' <column-definition> )* ')'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
229 ( WITH <option> ( AND <option>)* )?
230
b09d876 Sylvain Lebresne Add static columns in CQL3
pcmanus authored
231 <column-definition> ::= <identifier> <type> ( STATIC )? ( PRIMARY KEY )?
929b26e Sylvain Lebresne Add composite partition keys support to CQL3 doc
pcmanus authored
232 | PRIMARY KEY '(' <partition-key> ( ',' <identifier> )* ')'
233
234 <partition-key> ::= <identifier>
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
235 | '(' <identifier> (',' <identifier> )* ')'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
236
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
237 <option> ::= <property>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
238 | COMPACT STORAGE
239 | CLUSTERING ORDER
240 p.
241 __Sample:__
242
243 bc(sample)..
244 CREATE TABLE monkeySpecies (
245 species text PRIMARY KEY,
246 common_name text,
247 population varint,
248 average_size int
249 ) WITH comment='Important biological records'
250 AND read_repair_chance = 1.0;
251
252 CREATE TABLE timeline (
253 userid uuid,
254 posted_month int,
255 posted_time uuid,
256 body text,
257 posted_by text,
258 PRIMARY KEY (userid, posted_month, posted_time)
6cdbb2d Jonathan Ellis fix WITH compaction_strategy -> WITH compaction
jbellis authored
259 ) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
260 p.
261 The @CREATE TABLE@ statement creates a new table. Each such table is a set of _rows_ (usually representing related entities) for which it defines a number of properties. A table is defined by a "name":#createTableName, it defines the <a href="#createTableColumn"><it>columns</it></a> composing rows of the table and have a number of "options":#createTableOptions. Note that the @CREATE COLUMNFAMILY@ syntax is supported as an alias for @CREATE TABLE@ (for historical reasons).
262
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
263 Attempting to create an already existing table 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 table already exists.
264
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
265 h4(#createTableName). @<tablename>@
266
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
267 Valid table names are the same as valid "keyspace names":#createKeyspaceStmt (up to 32 characters long alphanumerical identifiers). If the table name is provided alone, the table is created within the current keyspace (see <a href="#useStmt"><tt>USE</tt></a>), but if it is prefixed by an existing keyspace name (see "@<tablename>@":#statements grammar), it is created in the specified keyspace (but does *not* change the current keyspace).
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
268
269
270 h4(#createTableColumn). @<column-definition>@
271
272 A @CREATE TABLE@ statement defines the columns that rows of the table can have. A _column_ is defined by its name (an identifier) and its type (see the "data types":#types section for more details on allowed types and their properties).
273
274 Within a table, a row is uniquely identified by its @PRIMARY KEY@ (or more simply the key), and hence all table definitions *must* define a PRIMARY KEY (and only one). A @PRIMARY KEY@ is composed of one or more of the columns defined in the table. If the @PRIMARY KEY@ is only one column, this can be specified directly after the column definition. Otherwise, it must be specified by following @PRIMARY KEY@ by the comma-separated list of column names composing the key within parenthesis. Note that:
275
276 bc(sample).
277 CREATE TABLE t (
278 k int PRIMARY KEY,
279 other text
280 )
281
282 is equivalent to
283
284 bc(sample).
285 CREATE TABLE t (
286 k int,
287 other text,
288 PRIMARY KEY (k)
289 )
290
4cb045a Sylvain Lebresne Improve CQL3 documentation section on COMPACT STORAGE (CASSANDRA-6642)
pcmanus authored
291 h4(#createTablepartitionClustering). Partition key and clustering columns
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
292
929b26e Sylvain Lebresne Add composite partition keys support to CQL3 doc
pcmanus authored
293 In CQL, the order in which columns are defined for the @PRIMARY KEY@ matters. The first column of the key is called the __partition key__. It has the property that all the rows sharing the same partition key (even across table in fact) are stored on the same physical node. Also, insertion/update/deletion on rows sharing the same partition key for a given table are performed __atomically__ and in __isolation__. Note that it is possible to have a composite partition key, i.e. a partition key formed of multiple columns, using an extra set of parentheses to define which columns forms the partition key.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
294
1f68442 Sylvain Lebresne Rename clustering keys to clustering columns in doc
pcmanus authored
295 The remaining columns of the @PRIMARY KEY@ definition, if any, are called __clustering columns. On a given physical node, rows for a given partition key are stored in the order induced by the clustering columns, making the retrieval of rows in that clustering order particularly efficient (see <a href="#selectStmt"><tt>SELECT</tt></a>).
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
296
b09d876 Sylvain Lebresne Add static columns in CQL3
pcmanus authored
297 h4(#createTableStatic). @STATIC@ columns
298
299 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 partition key). For instance, in:
300
301 bc(sample).
302 CREATE TABLE test (
303 pk int,
304 t int,
305 v text,
306 s text static,
307 PRIMARY KEY (pk, t)
308 );
309 INSERT INTO test(pk, t, v, s) VALUES (0, 0, 'val0', 'static0');
310 INSERT INTO test(pk, t, v, s) VALUES (0, 1, 'val1', 'static1');
311 SELECT * FROM test WHERE pk=0 AND t=0;
312
313 the last query will return @'static1'@ as value for @s@, since @s@ is static and thus the 2nd insertion modified this "shared" value. Note however that static columns are only static within a given partition, and if in the example above both rows where from different partitions (i.e. if they had different value for @pk@), then the 2nd insertion would not have modified the value of @s@ for the first row.
314
315 A few restrictions applies to when static columns are allowed:
316 * tables with the @COMPACT STORAGE@ option (see below) cannot have them
317 * 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).
318 * only non @PRIMARY KEY@ columns can be static
319
320
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
321 h4(#createTableOptions). @<option>@
322
323 The @CREATE TABLE@ statement supports a number of options that controls the configuration of a new table. These options can be specified after the @WITH@ keyword.
324
b09d876 Sylvain Lebresne Add static columns in CQL3
pcmanus authored
325 The first of these option is @COMPACT STORAGE@. This option is mainly targeted towards backward compatibility for definitions created before CQL3 (see "www.datastax.com/dev/blog/thrift-to-cql3":http://www.datastax.com/dev/blog/thrift-to-cql3 for more details). The option also provides a slightly more compact layout of data on disk but at the price of diminished flexibility and extensibility for the table. Most notably, @COMPACT STORAGE@ tables cannot have collections nor static columns and a @COMPACT STORAGE@ table with at least one clustering column supports exactly one (as in not 0 nor more than 1) column not part of the @PRIMARY KEY@ definition (which imply in particular that you cannot add nor remove columns after creation). For those reasons, @COMPACT STORAGE@ is not recommended outside of the backward compatibility reason evoked above.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
326
4cb045a Sylvain Lebresne Improve CQL3 documentation section on COMPACT STORAGE (CASSANDRA-6642)
pcmanus authored
327 Another option is @CLUSTERING ORDER@. It allows to define the ordering of rows on disk. It takes the list of the clustering column names with, for each of them, the on-disk order (Ascending or descending). Note that this option affects "what @ORDER BY@ are allowed during @SELECT@":#selectOrderBy.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
328
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
329 Table creation supports the following other @<property>@:
330
331 |_. option |_. kind |_. default |_. description|
332 |@comment@ | _simple_ | none | A free-form, human-readable comment.|
333 |@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.|
334 |@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.|
335 |@gc_grace_seconds@ | _simple_ | 864000 | Time to wait before garbage collecting tombstones (deletion markers).|
336 |@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)|
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
337 |@compaction@ | _map_ | _see below_ | The compaction options to use, see below.|
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
338 |@compression@ | _map_ | _see below_ | Compression options, see below. |
339 |@caching@ | _simple_ | keys_only | Whether to cache keys ("key cache") and/or rows ("row cache") for this table. Valid values are: @all@, @keys_only@, @rows_only@ and @none@. |
1467b9f Jon Haddad Add default_time_to_live details to CQL 3 documentation
rustyrazorblade authored
340 |@default_time_to_live@ | _simple_ | 0 | The default expiration time ("TTL") in seconds for a table.|
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
341
342
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
343 h4(#compactionOptions). @compaction@ options
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
344
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
345 The @compaction@ property must at least define the @'class'@ sub-option, that defines the compaction strategy class to use. The default supported class are @'SizeTieredCompactionStrategy'@ and @'LeveledCompactionStrategy'@. Custom strategy can be provided by specifying the full class name as a "string constant":#constants. The rest of the sub-options depends on the chosen class. The sub-options supported by the default classes are:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
346
367c741 Marcus Eriksson Add option to do more aggressive tombstone compaction.
krummas authored
347 |_. option |_. supported compaction strategy |_. default |_. description |
348 | @enabled@ | _all_ | true | A boolean denoting whether compaction should be enabled or not.|
349 | @tombstone_threshold@ | _all_ | 0.2 | A ratio such that if a sstable has more than this ratio of gcable tombstones over all contained columns, the sstable will be compacted (with no other sstables) for the purpose of purging those tombstones. |
350 | @tombstone_compaction_interval@ | _all_ | 1 day | The minimum time to wait after an sstable creation time before considering it for "tombstone compaction", where "tombstone compaction" is the compaction triggered if the sstable has more gcable tombstones than @tombstone_threshold@. |
351 | @unchecked_tombstone_compaction@ | _all_ | false | Setting this to true enables more aggressive tombstone compactions - single sstable tombstone compactions will run without checking how likely it is that they will be successful. |
352 | @min_sstable_size@ | SizeTieredCompactionStrategy | 50MB | The size tiered strategy groups SSTables to compact in buckets. A bucket groups SSTables that differs from less than 50% in size. However, for small sizes, this would result in a bucketing that is too fine grained. @min_sstable_size@ defines a size threshold (in bytes) below which all SSTables belong to one unique bucket|
353 | @min_threshold@ | SizeTieredCompactionStrategy | 4 | Minimum number of SSTables needed to start a minor compaction.|
354 | @max_threshold@ | SizeTieredCompactionStrategy | 32 | Maximum number of SSTables processed by one minor compaction.|
355 | @bucket_low@ | SizeTieredCompactionStrategy | 0.5 | Size tiered consider sstables to be within the same bucket if their size is within [average_size * @bucket_low@, average_size * @bucket_high@ ] (i.e the default groups sstable whose sizes diverges by at most 50%)|
356 | @bucket_high@ | SizeTieredCompactionStrategy | 1.5 | Size tiered consider sstables to be within the same bucket if their size is within [average_size * @bucket_low@, average_size * @bucket_high@ ] (i.e the default groups sstable whose sizes diverges by at most 50%).|
357 | @sstable_size_in_mb@ | LeveledCompactionStrategy | 5MB | The target size (in MB) for sstables in the leveled strategy. Note that while sstable sizes should stay less or equal to @sstable_size_in_mb@, it is possible to exceptionally have a larger sstable as during compaction, data for a given partition key are never split into 2 sstables|
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
358
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
359
360 For the @compression@ property, the following default sub-options are available:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
361
362 |_. option |_. default |_. description |
bed60fb Mikhail Stepura Ninja-commit for "Add LZ4Compressor in CQL document" CASSANDRA-6331
Mishail authored
363 | @sstable_compression@ | LZ4Compressor | The compression algorithm to use. Default compressor are: LZ4Compressor, SnappyCompressor and DeflateCompressor. Use an empty string (@''@) to disable compression. Custom compressor can be provided by specifying the full class name as a "string constant":#constants.|
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
364 | @chunk_length_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 |
365 | @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|
366
367
368 h4. Other considerations:
369
370 * When "inserting":#insertStmt/"updating":#updateStmt a given row, not all columns needs to be defined (except for those part of the key), and missing columns occupy no space on disk. Furthermore, adding new columns (see <a href=#alterStmt><tt>ALTER TABLE</tt></a>) is a constant time operation. There is thus no need to try to anticipate future usage (or to cry when you haven't) when creating a table.
371
372
373 h3(#alterTableStmt). ALTER TABLE
374
375 __Syntax:__
376
377 bc(syntax)..
378 <alter-table-stmt> ::= ALTER (TABLE | COLUMNFAMILY) <tablename> <instruction>
379
380 <instruction> ::= ALTER <identifier> TYPE <type>
381 | ADD <identifier> <type>
883c34b Aleksey Yeschenko Reenable ALTER TABLE DROP with new semantics
iamaleksey authored
382 | DROP <identifier>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
383 | WITH <option> ( AND <option> )*
384 p.
385 __Sample:__
386
387 bc(sample)..
388 ALTER TABLE addamsFamily
389 ALTER lastKnownLocation TYPE uuid;
390
391 ALTER TABLE addamsFamily
392 ADD gravesite varchar;
393
394 ALTER TABLE addamsFamily
395 WITH comment = 'A most excellent and useful column family'
396 AND read_repair_chance = 0.2;
397 p.
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
398 The @ALTER@ statement is used to manipulate table definitions. It allows for adding new columns, dropping existing ones, changing the type of existing columns, or updating the table options. As with table creation, @ALTER COLUMNFAMILY@ is allowed as an alias for @ALTER TABLE@.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
399
400 The @<tablename>@ is the table name optionally preceded by the keyspace name. The @<instruction>@ defines the alteration to perform:
1f68442 Sylvain Lebresne Rename clustering keys to clustering columns in doc
pcmanus authored
401 * @ALTER@: Update the type of a given defined column. Note that the type of the "clustering columns":#createTablepartitionClustering cannot be modified as it induces the on-disk ordering of rows. Columns on which a "secondary index":#createIndexStmt is defined have the same restriction. Other columns are free from those restrictions (no validation of existing data is performed), but it is usually a bad idea to change the type to a non-compatible one, unless no data have been inserted for that column yet, as this could confuse CQL drivers/tools.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
402 * @ADD@: Adds a new column to the table. The @<identifier>@ for the new column must not conflict with an existing column. Moreover, columns cannot be added to tables defined with the @COMPACT STORAGE@ option.
883c34b Aleksey Yeschenko Reenable ALTER TABLE DROP with new semantics
iamaleksey authored
403 * @DROP@: Removes a column from the table. Dropped columns will immediately become unavailable in the queries and will not be included in compacted sstables in the future. If a column is readded, queries won't return values written before the column was last dropped. It is assumed that timestamps represent actual time, so if this is not your case, you should NOT readd previously dropped columns. Columns can't be dropped from tables defined with the @COMPACT STORAGE@ option.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
404 * @WITH@: Allows to update the options of the table. The "supported @<option>@":#createTableOptions (and syntax) are the same as for the @CREATE TABLE@ statement except that @COMPACT STORAGE@ is not supported. 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
405
406 h3(#dropTableStmt). DROP TABLE
407
408 __Syntax:__
409
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
410 bc(syntax). <drop-table-stmt> ::= DROP TABLE ( IF EXISTS )? <tablename>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
411
412 __Sample:__
413
414 bc(sample). DROP TABLE worldSeriesAttendees;
415
416 The @DROP TABLE@ statement results in the immediate, irreversible removal of a table, including all data contained in it. As for table creation, @DROP COLUMNFAMILY@ is allowed as an alias for @DROP TABLE@.
417
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
418 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.
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
419
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
420 h3(#truncateStmt). TRUNCATE
421
422 __Syntax:__
423
424 bc(syntax). <truncate-stmt> ::= TRUNCATE <tablename>
425
426 __Sample:__
427
428 bc(sample). TRUNCATE superImportantData;
429
430 The @TRUNCATE@ statement permanently removes all data from a table.
431
432
433 h3(#createIndexStmt). CREATE INDEX
434
435 __Syntax:__
436
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
437 bc(syntax)..
4cdfa85 Tyler Hobbs Ninja: fix index name spec for CREATE INDEX in CQL docs
thobbs authored
438 <create-index-stmt> ::= CREATE ( CUSTOM )? INDEX ( IF NOT EXISTS )? ( <indexname> )?
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
439 ON <tablename> '(' <index-identifier> ')'
440 ( USING <string> ( WITH OPTIONS = <map-literal> )? )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
441
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
442 <index-identifier> ::= <identifier>
443 | keys( <identifier> )
444 p.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
445 __Sample:__
446
447 bc(sample).
448 CREATE INDEX userIndex ON NerdMovies (user);
449 CREATE INDEX ON Mutants (abilityId);
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
450 CREATE INDEX ON users (keys(favs));
2397bc8 Aleksey Yeschenko Updated CREATE CUSTOM INDEX syntax
iamaleksey authored
451 CREATE CUSTOM INDEX ON users (email) USING 'path.to.the.IndexClass';
5fa6055 Aleksey Yeschenko Allow specifying custom secondary index options in CQL3
iamaleksey authored
452 CREATE CUSTOM INDEX ON users (email) USING 'path.to.the.IndexClass' WITH OPTIONS = {'storage': '/mnt/ssd/indexes/'};
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
453
db70bbe Sylvain Lebresne Add CAS to the CQL doc
pcmanus authored
454 The @CREATE INDEX@ statement is used to create a new (automatic) secondary index for a given (existing) column in a given table. A name for the index itself can be specified before the @ON@ keyword, if desired. If data already exists for the column, it will be indexed asynchronously. After the index is created, new data for the column is indexed automatically at insertion time.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
455
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
456 Attempting to create an already existing index 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 index already exists.
457
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
458 h4(#keysIndex). Indexes on Map Keys
459
460 When creating an index on a "map column":#map, you may index either the keys or the values. If the column identifier is placed within the @keys()@ function, the index will be on the map keys, allowing you to use @CONTAINS KEY@ in @WHERE@ clauses. Otherwise, the index will be on the map values.
461
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
462 h3(#dropIndexStmt). DROP INDEX
463
464 __Syntax:__
465
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
466 bc(syntax). <drop-index-stmt> ::= DROP INDEX ( IF EXISTS )? ( <keyspace> '.' )? <identifier>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
467
468 __Sample:__
469
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
470 bc(sample)..
471 DROP INDEX userIndex;
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
472
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
473 DROP INDEX userkeyspace.address_index;
474 p.
475 The @DROP INDEX@ statement is used to drop an existing secondary index. The argument of the statement is the index name, which may optionally specify the keyspace of the index.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
476
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
477 If the index does not exists, the statement will return an error, unless @IF EXISTS@ is used in which case the operation is a no-op.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
478
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
479 h3(#createTypeStmt). CREATE TYPE
480
481 __Syntax:__
482
483 bc(syntax)..
484 <create-type-stmt> ::= CREATE TYPE ( IF NOT EXISTS )? <typename>
485 '(' <field-definition> ( ',' <field-definition> )* ')'
486
487 <typename> ::= ( <keyspace-name> '.' )? <identifier>
488
489 <field-definition> ::= <identifier> <type>
490
491 p.
492 __Sample:__
493
494 bc(sample)..
495 CREATE TYPE address (
496 street_name text,
497 street_number int,
498 city text,
499 state text,
500 zip int
501 )
502
503 CREATE TYPE work_and_home_addresses (
504 home_address address,
505 work_address address
506 )
507 p.
508 The @CREATE TYPE@ statement creates a new user-defined type. Each type is a set of named, typed fields. Field types may be any valid type, including collections and other existing user-defined types.
509
510 Attempting to create an already existing type will result in an error unless the @IF NOT EXISTS@ option is used. If it is used, the statement will be a no-op if the type already exists.
511
512 h4(#createTypeName). @<typename>@
513
514 Valid type names are identifiers. The names of existing CQL types and "reserved type names":#appendixB may not be used.
515
516 If the type name is provided alone, the type is created with the current keyspace (see <a href="#useStmt"><tt>USE</tt></a>). If it is prefixed by an existing keyspace name, the type is created within the specified keyspace instead of the current keyspace.
517
518 h3(#alterTypeStmt). ALTER TYPE
519
520 __Syntax:__
521
522 bc(syntax)..
523 <alter-type-stmt> ::= ALTER TYPE <typename> <instruction>
524
525 <instruction> ::= ALTER <field-name> TYPE <type>
526 | ADD <field-name> <type>
527 | RENAME <field-name> TO <field-name> ( AND <field-name> TO <field-name> )*
528 p.
529 __Sample:__
530
531 bc(sample)..
532 ALTER TYPE address ALTER zip TYPE varint
533
534 ALTER TYPE address ADD country text
535
536 ALTER TYPE address RENAME zip TO zipcode AND street_name TO street
537 p.
538 The @ALTER TYPE@ statement is used to manipulate type definitions. It allows for adding new fields, renaming existing fields, or changing the type of existing fields.
539
540 When altering the type of a column, the new type must be compatible with the previous type.
541
542 h3(#dropTypeStmt). DROP TYPE
543
544 __Syntax:__
545
546 bc(syntax)..
547 <drop-type-stmt> ::= DROP TYPE ( IF EXISTS )? <typename>
548 p.
549 The @DROP TYPE@ statement results in the immediate, irreversible removal of a type. Attempting to drop a type that is still in use by another type or a table will result in an error.
550
551 If the type does not exist, an error will be returned unless @IF EXISTS@ is used, in which case the operation is a no-op.
552
1456ec8 Mikhail Stepura (cqlsh) tab completion for triggers
Mishail authored
553 h3(#createTriggerStmt). CREATE TRIGGER
554
555 __Syntax:__
556
557 bc(syntax)..
558 <create-trigger-stmt> ::= CREATE TRIGGER ( IF NOT EXISTS )? ( <triggername> )?
559 ON <tablename>
560 USING <string>
561
562 p.
563 __Sample:__
564
565 bc(sample).
566 CREATE TRIGGER myTrigger ON myTable USING 'org.apache.cassandra.triggers.InvertedIndex';
567
568 The actual logic that makes up the trigger can be written in any Java (JVM) language and exists outside the database. You place the trigger code in a @lib/triggers@ subdirectory of the Cassandra installation directory, it loads during cluster startup, and exists on every node that participates in a cluster. The trigger defined on a table fires before a requested DML statement occurs, which ensures the atomicity of the transaction.
569
570 h3(#dropTriggerStmt). DROP TRIGGER
571
572 __Syntax:__
573
574 bc(syntax)..
575 <drop-trigger-stmt> ::= DROP TRIGGER ( IF EXISTS )? ( <triggername> )?
576 ON <tablename>
577 p.
578 __Sample:__
579
580 bc(sample).
581 DROP TRIGGER myTrigger ON myTable;
582
583 @DROP TRIGGER@ statement removes the registration of a trigger created using @CREATE TRIGGER@.
584
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
585 h3(#createFunctionStmt). CREATE FUNCTION
586
587 __Syntax:__
588
589 bc(syntax)..
590 <create-function-stmt> ::= CREATE ( OR REPLACE )?
591 ( ( NON )? DETERMINISTIC )?
592 FUNCTION ( IF NOT EXISTS )?
593 ( ( <keyspace> '.' )? <function-name> )?
594 '(' <arg-name> <arg-type> ( ',' <arg-name> <arg-type> )* ')'
595 RETURNS <type>
596 LANGUAGE <language>
597 AS <body>
598 p.
599 __Sample:__
600
601 bc(sample).
602 CREATE OR REPLACE FUNCTION somefunction
603 ( somearg int, anotherarg text, complexarg frozen<someUDT>, listarg list<bigint> )
604 RETURNS text
605 LANGUAGE java
606 AS $$
607 // some Java code
608 $$;
609 CREATE FUNCTION akeyspace.fname IF NOT EXISTS
610 ( someArg int )
611 RETURNS text
612 LANGUAGE java
613 AS $$
614 // some Java code
615 $$;
616
617 @CREATE FUNCTION@ creates or replaces a user-defined function.
618
619 Functions are either @DETERMINISTIC@ or @NON DETERMINISTIC@. A deterministic function always returns the same value for the same input values. A non-deterministic function may not. Examples of deterministic functions are math functions like _add_ or _sin_. Examples of non-deterministic functions are: _now_ or _random_. Functions are assumed to be deterministic by default.
620
621 h4(#functionSignature). Function Signature
622
623 Signatures are used to distinguish individual functions. The signature consists of:
624
625 # The fully qualified function name - i.e _keyspace_ plus _function-name_
626 # The concatenated list of all argument types
627
628 Note that keyspace names, function names and argument types are subject to the default naming conventions and case-sensitivity rules.
629
630 @CREATE FUNCTION@ with the optional @OR REPLACE@ keywords either creates a function or replaces an existing one with the same signature. A @CREATE FUNCTION@ without @OR REPLACE@ fails if a function with the same signature already exists.
631
632 If the optional @IF NOT EXISTS@ keywords are used, the function will only be created if another function with the same signature does not exist.
633
634 @OR REPLACE@ and @IF NOT EXIST@ cannot be used together.
635
636 Functions belong to a keyspace. If no keyspace is specified in @<function-name>@, the current keyspace is used (i.e. the keyspace specified using the "@USE@":#useStmt statement). It is not possible to create a user-defined function in one of the system keyspaces.
637
638 See the section on "user-defined functions":#udfs for more information.
639
640 h3(#dropFunctionStmt). DROP FUNCTION
641
642 __Syntax:__
643
644 bc(syntax)..
645 <drop-function-stmt> ::= DROP FUNCTION ( IF EXISTS )?
646 ( ( <keyspace> '.' )? <function-name> )?
647 ( '(' <arg-type> ( ',' <arg-type> )* ')' )?
648
649 p.
650 __Sample:__
651
652 bc(sample).
653 DROP FUNCTION myfunction;
654 DROP FUNCTION mykeyspace.afunction;
655 DROP FUNCTION afunction ( int );
656 DROP FUNCTION afunction ( text );
657
658 @DROP FUNCTION@ statement removes a function created using @CREATE FUNCTION@.
659 You must specify the argument types ("signature":#functionSignature) of the function to drop if there are multiple functions with the same name but a different signature (overloaded functions).
660
661 @DROP FUNCTION@ with the optional @IF EXISTS@ keywords drops a function if it exists.
662
663 h3(#createAggregateStmt). CREATE AGGREGATE
664
665 __Syntax:__
666
667 bc(syntax)..
668 <create-aggregate-stmt> ::= CREATE ( OR REPLACE )?
669 AGGREGATE ( IF NOT EXISTS )?
670 ( ( <keyspace> '.' )? <aggregate-name> )?
671 '(' <arg-type> ( ',' <arg-type> )* ')'
672 SFUNC ( <keyspace> '.' )? <state-functionname>
673 STYPE <state-type>
674 ( FINALFUNC ( <keyspace> '.' )? <final-functionname> )?
675 ( INITCOND <init-cond> )?
676 p.
677 __Sample:__
678
679 bc(sample).
680 CREATE AGGREGATE myaggregate ( val text )
681 SFUNC myaggregate_state
682 STYPE text
683 FINALFUNC myaggregate_final
684 INITCOND 'foo';
685
686 See the section on "user-defined aggregates":#udas for a complete example.
687
688 @CREATE AGGREGATE@ creates or replaces a user-defined aggregate.
689
690 @CREATE AGGREGATE@ with the optional @OR REPLACE@ keywords either creates an aggregate or replaces an existing one with the same signature. A @CREATE AGGREGATE@ without @OR REPLACE@ fails if an aggregate with the same signature already exists.
691
692 @CREATE AGGREGATE@ with the optional @IF NOT EXISTS@ keywords either creates an aggregate if it does not already exist.
693
694 @OR REPLACE@ and @IF NOT EXIST@ cannot be used together.
695
696 Aggregates belong to a keyspace. If no keyspace is specified in @<aggregate-name>@, the current keyspace is used (i.e. the keyspace specified using the "@USE@":#useStmt statement). It is not possible to create a user-defined aggregate in one of the system keyspaces.
697
698 Signatures for user-defined aggregates follow the "same rules":#functionSignature as for user-defined functions.
699
700 @STYPE@ defines the type of the state value and must be specified.
701
702 The optional @INITCOND@ defines the initial state value for the aggregate. It defaults to @null@.
703
704 @SFUNC@ references an existing function to be used as the state modifying function. The type of first argument of the state function must match @STYPE@. The remaining argument types of the state function must match the argument types of the aggregate function.
705
706 The optional @FINALFUNC@ is called just before the aggregate result is returned. It must take only one argument with type @STYPE@. The return type of the @FINALFUNC@ may be a different type.
707
708 If no @FINALFUNC@ is defined, the overall return type of the aggregate function is @STYPE@. If a @FINALFUNC@ is defined, it is the return type of that function.
709
710 See the section on "user-defined aggregates":#udas for more information.
711
712 h3(#dropAggregateStmt). DROP AGGREGATE
713
714 __Syntax:__
715
716 bc(syntax)..
717 <drop-function-stmt> ::= DROP AGGREGATE ( IF EXISTS )?
718 ( ( <keyspace> '.' )? <functionname> )?
719 ( '(' <arg-type> ( ',' <arg-type> )* ')' )?
720 p.
721
722 __Sample:__
723
724 bc(sample).
725 DROP AGGREGATE myAggregate;
726 DROP AGGREGATE myKeyspace.anAggregate;
727 DROP AGGREGATE someAggregate ( int );
728 DROP AGGREGATE someAggregate ( text );
729
730 The @DROP AGGREGATE@ statement removes an aggregate created using @CREATE AGGREGATE@. You must specify the argument types of the aggregate to drop if there are multiple aggregates with the same name but a different signature (overloaded aggregates).
731
732 @DROP AGGREGATE@ with the optional @IF EXISTS@ keywords drops an aggregate if it exists, and does nothing if a function with the signature does not exist.
733
734 Signatures for user-defined aggregates follow the "same rules":#functionSignature as for user-defined functions.
735
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
736 h2(#dataManipulation). Data Manipulation
737
738 h3(#insertStmt). INSERT
739
740 __Syntax:__
741
742 bc(syntax)..
743 <insertStatement> ::= INSERT INTO <tablename>
744 '(' <identifier> ( ',' <identifier> )* ')'
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
745 VALUES '(' <term-or-literal> ( ',' <term-or-literal> )* ')'
db70bbe Sylvain Lebresne Add CAS to the CQL doc
pcmanus authored
746 ( IF NOT EXISTS )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
747 ( USING <option> ( AND <option> )* )?
748
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
749 <term-or-literal> ::= <term>
750 | <collection-literal>
751
752 <option> ::= TIMESTAMP <integer>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
753 | TTL <integer>
754 p.
755 __Sample:__
756
757 bc(sample).
758 INSERT INTO NerdMovies (movie, director, main_actor, year)
759 VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
760 USING TTL 86400;
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
761
16efdf4 Sylvain Lebresne Remove/update invalid sentences in CQL doc
pcmanus authored
762 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
763
db70bbe Sylvain Lebresne Add CAS to the CQL doc
pcmanus authored
764 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.
765
766 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
767
768 All updates for an @INSERT@ are applied atomically and in isolation.
769
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
770 Please refer to the "@UPDATE@":#updateOptions section for information on the @<option>@ available and to the "collections":#collections section for use of @<collection-literal>@. Also note that @INSERT@ does not support counters, while @UPDATE@ does.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
771
772 h3(#updateStmt). UPDATE
773
774 __Syntax:__
775
776 bc(syntax)..
777 <update-stmt> ::= UPDATE <tablename>
778 ( USING <option> ( AND <option> )* )?
779 SET <assignment> ( ',' <assignment> )*
780 WHERE <where-clause>
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
781 ( IF <condition> ( AND condition )* )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
782
783 <assignment> ::= <identifier> '=' <term>
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
784 | <identifier> '=' <identifier> ('+' | '-') (<int-term> | <set-literal> | <list-literal>)
785 | <identifier> '=' <identifier> '+' <map-literal>
786 | <identifier> '[' <term> ']' '=' <term>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
787
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
788 <condition> ::= <identifier> '=' <term>
789 | <identifier> '[' <term> ']' '=' <term>
790
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
791 <where-clause> ::= <relation> ( AND <relation> )*
792
793 <relation> ::= <identifier> '=' <term>
794 | <identifier> IN '(' ( <term> ( ',' <term> )* )? ')'
e93578b Sylvain Lebresne Support variadic parameters for IN clauses
pcmanus authored
795 | <identifier> IN '?'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
796
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
797 <option> ::= TIMESTAMP <integer>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
798 | TTL <integer>
799 p.
800 __Sample:__
801
802 bc(sample)..
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
803 UPDATE NerdMovies USING TTL 400
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
804 SET director = 'Joss Whedon',
805 main_actor = 'Nathan Fillion',
806 year = 2005
807 WHERE movie = 'Serenity';
808
809 UPDATE UserActions SET total = total + 2 WHERE user = B70DE1D0-9908-4AE3-BE34-5573E5B09F14 AND action = 'click';
810 p.
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
811 The @UPDATE@ statement writes one or more columns for a given row in a table. The @<where-clause>@ is used to select the row to update and must include all columns composing the @PRIMARY KEY@ (the @IN@ relation is only supported for the last column of the partition key). Other columns values are specified through @<assignment>@ after the @SET@ keyword.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
812
db70bbe Sylvain Lebresne Add CAS to the CQL doc
pcmanus authored
813 Note that unlike in SQL, @UPDATE@ 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.
814
815 It is however possible to use the conditions on some columns through @IF@, in which case the row will not be updated unless such condition 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
816
817 In an @UPDATE@ statement, all updates within the same partition key are applied atomically and in isolation.
818
819 The @c = c + 3@ form of @<assignment>@ is used to increment/decrement counters. The identifier after the '=' sign *must* be the same than the one before the '=' sign (Only increment/decrement is supported on counters, not the assignment of a specific value).
820
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
821 The @id = id + <collection-literal>@ and @id[value1] = value2@ forms of @<assignment>@ are for collections. Please refer to the "relevant section":#collections for more details.
822
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
823 h4(#updateOptions). @<options>@
824
825 The @UPDATE@ and @INSERT@ statements allows to specify the following options for the insertion:
08deff7 Tyler Hobbs Clarify default timestamp in CQL docs
thobbs authored
826 * @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.
034ac7d Sylvain Lebresne Document that TTL <= 0 means no TTL
pcmanus authored
827 * @TTL@: allows to specify 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 column 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 or a negative one is equivalent to no TTL.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
828
829
830 h3(#deleteStmt). DELETE
831
832 __Syntax:__
833
834 bc(syntax)..
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
835 <delete-stmt> ::= DELETE ( <selection> ( ',' <selection> )* )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
836 FROM <tablename>
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
837 ( USING TIMESTAMP <integer>)?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
838 WHERE <where-clause>
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
839 ( IF ( EXISTS | ( <condition> ( AND <condition> )*) ) )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
840
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
841 <selection> ::= <identifier> ( '[' <term> ']' )?
842
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
843 <where-clause> ::= <relation> ( AND <relation> )*
844
845 <relation> ::= <identifier> '=' <term>
846 | <identifier> IN '(' ( <term> ( ',' <term> )* )? ')'
e93578b Sylvain Lebresne Support variadic parameters for IN clauses
pcmanus authored
847 | <identifier> IN '?'
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
848
849 <condition> ::= <identifier> '=' <term>
850 | <identifier> '[' <term> ']' '=' <term>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
851 p.
852 __Sample:__
853
854 bc(sample)..
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
855 DELETE FROM NerdMovies USING TIMESTAMP 1240003134 WHERE movie = 'Serenity';
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
856
857 DELETE phone FROM Users WHERE userid IN (C73DE1D3-AF08-40F3-B124-3FF3E5109F22, B70DE1D0-9908-4AE3-BE34-5573E5B09F14);
858 p.
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
859 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>@ (the @id[value]@ syntax in @<selection>@ is for collection, please refer to the "collection section":#collections for more details). Otherwise whole rows are removed. The @<where-clause>@ allows to specify the key for the row(s) to delete (the @IN@ relation is only supported for the last column of the partition key).
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
860
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
861 @DELETE@ supports the @TIMESTAMP@ options with the same semantic that in the "@UPDATE@":#updateStmt statement.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
862
863 In a @DELETE@ statement, all deletions within the same partition key are applied atomically and in isolation.
864
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
865 A @DELETE@ operation application can be conditioned using @IF@ like for @UPDATE@ and @INSERT@. But please not that as for the later, this will incur a non negligible performance cost (internally, Paxos will be used) and so should be used sparingly.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
866
867
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
868 h3(#batchStmt). BATCH
869
870 __Syntax:__
871
872 bc(syntax)..
7e87001 Tyler Hobbs Add UNLOGGED, COUNTER options to BATCH docs
thobbs authored
873 <batch-stmt> ::= BEGIN ( UNLOGGED | COUNTER ) BATCH
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
874 ( USING <option> ( AND <option> )* )?
875 <modification-stmt> ( ';' <modification-stmt> )*
876 APPLY BATCH
877
878 <modification-stmt> ::= <insert-stmt>
879 | <update-stmt>
880 | <delete-stmt>
881
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
882 <option> ::= TIMESTAMP <integer>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
883 p.
884 __Sample:__
885
886 bc(sample).
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
887 BEGIN BATCH
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
888 INSERT INTO users (userid, password, name) VALUES ('user2', 'ch@ngem3b', 'second user');
889 UPDATE users SET password = 'ps22dhds' WHERE userid = 'user3';
890 INSERT INTO users (userid, password) VALUES ('user4', 'ch@ngem3c');
891 DELETE name FROM users WHERE userid = 'user1';
892 APPLY BATCH;
893
7e87001 Tyler Hobbs Add UNLOGGED, COUNTER options to BATCH docs
thobbs authored
894 The @BATCH@ statement group multiple modification statements (insertions/updates and deletions) into a single statement. It serves several purposes:
895 # It saves network round-trips between the client and the server (and sometimes between the server coordinator and the replicas) when batching multiple updates.
896 # All updates in a @BATCH@ belonging to a given partition key are performed in isolation.
897 # By default, all operations in the batch are performed atomically. See the notes on "@UNLOGGED@":#unloggedBatch for more details.
427fdd4 Tyler Hobbs Document lack of order guarantees for BATCH statements
thobbs authored
898
899 Note that:
900 * @BATCH@ statements may only contain @UPDATE@, @INSERT@ and @DELETE@ statements.
901 * Batches are _not_ a full analogue for SQL transactions.
902 * If a timestamp is not specified for each operation, then all operations will be applied with the same timestamp. 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
903
7e87001 Tyler Hobbs Add UNLOGGED, COUNTER options to BATCH docs
thobbs authored
904 h4(#unloggedBatch). @UNLOGGED@
905
906 By default, Cassandra uses a batch log to ensure all operations in a batch are applied atomically. (Note that the operations are still only isolated within a single partition.)
907
908 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, operations are only atomic within a single partition.
909
910 h4(#counterBatch). @COUNTER@
911
912 Use the @COUNTER@ option for batched counter updates. Unlike other updates in Cassandra, counter updates are not idempotent.
913
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
914 h4(#batchOptions). @<option>@
915
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
916 @BATCH@ supports both the @TIMESTAMP@ option, with similar semantic to the one described in the "@UPDATE@":#updateOptions statement (the timestamp applies to all the statement inside the batch). However, if used, @TIMESTAMP@ *must not* be used in the statements within the batch.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
917
918
919 h2(#queries). Queries
920
921
922 h3(#selectStmt). SELECT
923
924 __Syntax:__
925
926 bc(syntax)..
927 <select-stmt> ::= SELECT <select-clause>
928 FROM <tablename>
929 ( WHERE <where-clause> )?
930 ( ORDER BY <order-by> )?
931 ( LIMIT <integer> )?
4df6136 Sylvain Lebresne Add ALLOW FILTERING to CQL3 documentation
pcmanus authored
932 ( ALLOW FILTERING )?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
933
b362aeb Aleksey Yeschenko Add ability for CQL3 to list partition keys
iamaleksey authored
934 <select-clause> ::= DISTINCT? <selection-list>
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
935 | COUNT '(' ( '*' | '1' ) ')' (AS <identifier>)?
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
936
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
937 <selection-list> ::= <selector> (AS <identifier>)? ( ',' <selector> (AS <identifier>)? )*
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
938 | '*'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
939
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
940 <selector> ::= <identifier>
941 | WRITETIME '(' <identifier> ')'
942 | TTL '(' <identifier> ')'
943 | <function> '(' (<selector> (',' <selector>)*)? ')'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
944
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
945 <where-clause> ::= <relation> ( AND <relation> )*
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
946
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
947 <relation> ::= <identifier> <op> <term>
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
948 | '(' <identifier> (',' <identifier>)* ')' <op> <term-tuple>
dc457c5 Aleksey Yeschenko Allow empty IN relations in SELECT/UPDATE/DELETE statements
iamaleksey authored
949 | <identifier> IN '(' ( <term> ( ',' <term>)* )? ')'
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
950 | '(' <identifier> (',' <identifier>)* ')' IN '(' ( <term-tuple> ( ',' <term-tuple>)* )? ')'
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
951 | TOKEN '(' <identifier> ( ',' <identifer>)* ')' <op> <term>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
952
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
953 <op> ::= '=' | '<' | '>' | '<=' | '>=' | CONTAINS | CONTAINS KEY
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
954 <order-by> ::= <ordering> ( ',' <odering> )*
955 <ordering> ::= <identifer> ( ASC | DESC )?
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
956 <term-tuple> ::= '(' <term> (',' <term>)* ')'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
957 p.
958 __Sample:__
959
960 bc(sample)..
961 SELECT name, occupation FROM users WHERE userid IN (199, 200, 207);
962
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
963 SELECT name AS user_name, occupation AS user_occupation FROM users;
964
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
965 SELECT time, value
966 FROM events
967 WHERE event_type = 'myEvent'
2a83793 Aleksey Yeschenko Correct timestamp formatting in CQL3 doc
iamaleksey authored
968 AND time > '2011-02-03'
969 AND time <= '2012-01-01'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
970
971 SELECT COUNT(*) FROM users;
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
972
973 SELECT COUNT(*) AS user_count FROM users;
974
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
975 p.
976 The @SELECT@ statements reads one or more columns for one or more rows in a table. It returns a result-set of rows, where each row contains the collection of columns corresponding to the query.
977
978 h4(#selectSelection). @<select-clause>@
979
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
980 The @<select-clause>@ determines which columns needs to be queried and returned in the result-set. It consists of either the comma-separated list of <selector> or the wildcard character (@*@) to select all the columns defined for the table.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
981
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
982 A @<selector>@ is either a column name to retrieve or a @<function>@ of one or more @<term>@s. The function allowed are the same as for @<term>@ and are described in the "function section":#functions. In addition to these generic functions, the @WRITETIME@ (resp. @TTL@) function allows to select the timestamp of when the column was inserted (resp. the time to live (in seconds) for the column (or null if the column has no expiration set)).
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
983
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
984 Any @<selector>@ can be aliased using @AS@ keyword (see examples). Please note that @<where-clause>@ and @<order-by>@ clause should refer to the columns by their original names and not by their aliases.
985
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
986 The @COUNT@ keyword can be used with parenthesis enclosing @*@. If so, the query will return a single result: the number of rows matching the query. Note that @COUNT(1)@ is supported as an alias.
987
988 h4(#selectWhere). @<where-clause>@
989
990 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.
991
1f68442 Sylvain Lebresne Rename clustering keys to clustering columns in doc
pcmanus authored
992 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
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
993
994 bc(sample).
995 CREATE TABLE posts (
996 userid text,
997 blog_title text,
998 posted_at timestamp,
999 entry_title text,
1000 content text,
1001 category int,
1002 PRIMARY KEY (userid, blog_title, posted_at)
1003 )
1004
1005 The following query is allowed:
1006
1007 bc(sample).
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
1008 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'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1009
1010 But the following one is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are set):
1011
1012 bc(sample).
1013 // Needs a blog_title to be set to select ranges of posted_at
2a83793 Aleksey Yeschenko Correct timestamp formatting in CQL3 doc
iamaleksey authored
1014 SELECT entry_title, content FROM posts WHERE userid='john doe' AND posted_at >= '2012-01-01' AND posted_at < '2012-01-31'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1015
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1016 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:
4a002a5 Sylvain Lebresne Minor edit to CQL3 doc
pcmanus authored
1017
1018 bc(sample).
1019 SELECT * FROM posts WHERE token(userid) > token('tom') AND token(userid) < token('bob')
1020
55fed33 Sylvain Lebresne CQL3 documentation fixes (#5878)
pcmanus authored
1021 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.
1022
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
1023 It is also possible to "group" @CLUSTERING COLUMNS@ together in a relation using the tuple notation. For instance:
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
1024
1025 bc(sample).
1026 SELECT * FROM posts WHERE userid='john doe' AND (blog_title, posted_at) > ('John''s Blog', '2012-01-01')
1027
1028 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 wouldn't be the case for:
1029
1030 bc(sample).
1031 SELECT * FROM posts WHERE userid='john doe' AND blog_title > 'John''s Blog' AND posted_at > '2012-01-01'
1032
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
1033 The tuple notation may also be used for @IN@ clauses on @CLUSTERING COLUMNS@:
1034
1035 bc(sample).
1036 SELECT * FROM posts WHERE userid='john doe' AND (blog_title, posted_at) IN (('John''s Blog', '2012-01-01), ('Extreme Chess', '2014-06-01'))
1037
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
1038 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.
1039
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1040 h4(#selectOrderBy). @<order-by>@
1041
1042 The @ORDER BY@ option 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 (which depends on the table "@CLUSTERING ORDER@":#createTableOptions):
4cb045a Sylvain Lebresne Improve CQL3 documentation section on COMPACT STORAGE (CASSANDRA-6642)
pcmanus authored
1043 * 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.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1044 * otherwise, the orderings allowed are the order of the @CLUSTERING ORDER@ option and the reversed one.
1045
4df6136 Sylvain Lebresne Add ALLOW FILTERING to CQL3 documentation
pcmanus authored
1046 h4(#selectLimit). @LIMIT@
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1047
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1048 The @LIMIT@ option to a @SELECT@ statement limits the number of rows returned by a query.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1049
4df6136 Sylvain Lebresne Add ALLOW FILTERING to CQL3 documentation
pcmanus authored
1050 h4(#selectAllowFiltering). @ALLOW FILTERING@
1051
1052 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@).
1053
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1054 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.
4df6136 Sylvain Lebresne Add ALLOW FILTERING to CQL3 documentation
pcmanus authored
1055
1056 For instance, considering the following table holding user profiles with their year of birth (with a secondary index on it) and country of residence:
1057
1058 bc(sample)..
1059 CREATE TABLE users (
1060 username text PRIMARY KEY,
1061 firstname text,
1062 lastname text,
1063 birth_year int,
1064 country text
1065 )
1066
1067 CREATE INDEX ON users(birth_year);
1068 p.
1069
1070 Then the following queries are valid:
1071
1072 bc(sample).
1073 SELECT * FROM users;
1074 SELECT firstname, lastname FROM users WHERE birth_year = 1981;
1075
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1076 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@.
4df6136 Sylvain Lebresne Add ALLOW FILTERING to CQL3 documentation
pcmanus authored
1077
1078 However, the following query will be rejected:
1079
1080 bc(sample).
1081 SELECT firstname, lastname FROM users WHERE birth_year = 1981 AND country = 'FR';
1082
1083 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:
1084
1085 bc(sample).
1086 SELECT firstname, lastname FROM users WHERE birth_year = 1981 AND country = 'FR' ALLOW FILTERING;
1087
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1088
1089 h2(#types). Data Types
1090
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
1091 CQL supports a rich set of data types for columns defined in a table, including collection types. On top of those native and collection types, users can also provide custom types (through a JAVA class extending @AbstractType@ loadable by Cassandra). The syntax of types is thus:
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1092
1093 bc(syntax)..
929b26e Sylvain Lebresne Add composite partition keys support to CQL3 doc
pcmanus authored
1094 <type> ::= <native-type>
1095 | <collection-type>
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
1096 | <tuple-type>
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1097 | <string> // Used for custom types. The fully-qualified name of a JAVA class
1098
929b26e Sylvain Lebresne Add composite partition keys support to CQL3 doc
pcmanus authored
1099 <native-type> ::= ascii
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1100 | bigint
1101 | blob
1102 | boolean
1103 | counter
1104 | decimal
1105 | double
1106 | float
c6ddbea Sylvain Lebresne Add inet support in binary protocol
pcmanus authored
1107 | inet
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1108 | int
1109 | text
1110 | timestamp
1111 | timeuuid
1112 | uuid
1113 | varchar
1114 | varint
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1115
929b26e Sylvain Lebresne Add composite partition keys support to CQL3 doc
pcmanus authored
1116 <collection-type> ::= list '<' <native-type> '>'
1117 | set '<' <native-type> '>'
1118 | map '<' <native-type> ',' <native-type> '>'
6b988fd Tyler Hobbs Ninja: fix grammar for tuple type in CQL docs
thobbs authored
1119 <tuple-type> ::= tuple '<' <type> (',' <type>)* '>'
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1120 p. Note that the native types are keywords and as such are case-insensitive. They are however not reserved ones.
1121
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
1122 p. The following table gives additional informations on the native data types, and on which kind of "constants":#constants each type supports:
1123
1124 |_. type |_. constants supported|_. description|
1125 |@ascii@ | strings |ASCII character string|
1126 |@bigint@ | integers |64-bit signed long|
1127 |@blob@ | blobs |Arbitrary bytes (no validation)|
1128 |@boolean@ | booleans |true or false|
1129 |@counter@ | integers |Counter column (64-bit signed value). See "Counters":#counters for details|
1130 |@decimal@ | integers, floats |Variable-precision decimal|
1131 |@double@ | integers |64-bit IEEE-754 floating point|
1132 |@float@ | integers, floats |32-bit IEEE-754 floating point|
1133 |@inet@ | strings |An IP address. It can be either 4 bytes long (IPv4) or 16 bytes long (IPv6). There is no @inet@ constant, IP address should be inputed as strings|
1134 |@int@ | integers |32-bit signed int|
1135 |@text@ | strings |UTF8 encoded string|
1136 |@timestamp@| integers, strings |A timestamp. Strings constant are allow to input timestamps as dates, see "Working with dates":#usingdates below for more information.|
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1137 |@timeuuid@ | uuids |Type 1 UUID. This is generally used as a "conflict-free" timestamp. Also see the "functions on Timeuuid":#timeuuidFun|
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
1138 |@uuid@ | uuids |Type 1 or type 4 UUID|
1139 |@varchar@ | strings |UTF8 encoded string|
1140 |@varint@ | integers |Arbitrary-precision integer|
1141
1142 For more information on how to use the collection types, see the "Working with collections":#collections section below.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1143
1144 h3(#usingdates). Working with dates
1145
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1146 Values of the @timestamp@ type are encoded as 64-bit signed integers representing a number of milliseconds since the standard base time known as "the epoch": January 1 1970 at 00:00:00 GMT.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1147
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1148 Timestamp can be input in CQL as simple long integers, giving the number of milliseconds since the epoch, as defined above.
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1149
1150 They can also be input as string literals in any of the following ISO 8601 formats, each representing the time and date Mar 2, 2011, at 04:05:00 AM, GMT.:
1151
1152 * @2011-02-03 04:05+0000@
1153 * @2011-02-03 04:05:00+0000@
21bb531 Aleksey Yeschenko Update docs, bump CQL3 version, correct CHANGES.txt for CASSANDRA-6395
iamaleksey authored
1154 * @2011-02-03 04:05:00.000+0000@
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1155 * @2011-02-03T04:05+0000@
1156 * @2011-02-03T04:05:00+0000@
21bb531 Aleksey Yeschenko Update docs, bump CQL3 version, correct CHANGES.txt for CASSANDRA-6395
iamaleksey authored
1157 * @2011-02-03T04:05:00.000+0000@
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1158
1159 The @+0000@ above is an RFC 822 4-digit time zone specification; @+0000@ refers to GMT. US Pacific Standard Time is @-0800@. The time zone may be omitted if desired-- the date will be interpreted as being in the time zone under which the coordinating Cassandra node is configured.
1160
1161 * @2011-02-03 04:05@
1162 * @2011-02-03 04:05:00@
21bb531 Aleksey Yeschenko Update docs, bump CQL3 version, correct CHANGES.txt for CASSANDRA-6395
iamaleksey authored
1163 * @2011-02-03 04:05:00.000@
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1164 * @2011-02-03T04:05@
1165 * @2011-02-03T04:05:00@
21bb531 Aleksey Yeschenko Update docs, bump CQL3 version, correct CHANGES.txt for CASSANDRA-6395
iamaleksey authored
1166 * @2011-02-03T04:05:00.000@
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1167
1168 There are clear difficulties inherent in relying on the time zone configuration being as expected, though, so it is recommended that the time zone always be specified for timestamps when feasible.
1169
1170 The time of day may also be omitted, if the date is the only piece that matters:
1171
1172 * @2011-02-03@
1173 * @2011-02-03+0000@
1174
1175 In that case, the time of day will default to 00:00:00, in the specified or default time zone.
1176
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1177
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1178 h3(#counters). Counters
1179
1180 The @counter@ type is used to define _counter columns_. A counter column is a column whose value is a 64-bit signed integer and on which 2 operations are supported: incrementation and decrementation (see "@UPDATE@":#updateStmt for syntax). Note the value of a counter cannot be set. A counter doesn't exist until first incremented/decremented, and the first incrementation/decrementation is made as if the previous value was 0. Deletion of counter columns is supported but have some limitations (see the "Cassandra Wiki":http://wiki.apache.org/cassandra/Counters for more information).
1181
1182 The use of the counter type is limited in the following way:
1183 * It cannot be used for column that is part of the @PRIMARY KEY@ of a table.
1184 * A table that contains a counter can only contain counters. In other words, either all the columns of a table outside the @PRIMARY KEY@ have the counter type, or none of them have it.
1185
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1186 h3(#collections). Working with collections
1187
c63d068 Sylvain Lebresne Add warning on collection limitations in CQL doc
pcmanus authored
1188 h4. Noteworthy characteristics
1189
1190 Collections are meant for storing/denormalizing relatively small amount of data. They work well for things like "the phone numbers of a given user", "labels applied to an email", etc. But when items are expected to grow unbounded ("all the messages sent by a given user", "events registered by a sensor", ...), then collections are not appropriate anymore and a specific table (with clustering columns) should be used. Concretely, collections have the following limitations:
1191 * Collections are always read in their entirety (and reading one is not paged internally).
1192 * Collections cannot have more than 65535 elements. More precisely, while it may be possible to insert more than 65535 elements, it is not possible to read more than the 65535 first elements (see "CASSANDRA-5428":https://issues.apache.org/jira/browse/CASSANDRA-5428 for details).
1193 * While insertion operations on sets and maps never incur a read-before-write internally, some operations on lists do (see the section on lists below for details). It is thus advised to prefer sets over lists when possible.
1194
1195 Please note that while some of those limitations may or may not be loosen in the future, the general rule that collections are for denormalizing small amount of data is meant to stay.
1196
1197
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1198 h4(#map). Maps
1199
1200 A @map@ is a "typed":#types set of key-value pairs, where keys are unique. Furthermore, note that the map are internally sorted by their keys and will thus always be returned in that order. To create a column of type @map@, use the @map@ keyword suffixed with comma-separated key and value types, enclosed in angle brackets. For example:
1201
1202 bc(sample).
1203 CREATE TABLE users (
1204 id text PRIMARY KEY,
1205 given text,
1206 surname text,
1207 favs map<text, text> // A map of text keys, and text values
1208 )
1209
1210 Writing @map@ data is accomplished with a JSON-inspired syntax. To write a record using @INSERT@, specify the entire map as a JSON-style associative array. _Note: This form will always replace the entire map._
1211
1212 bc(sample).
1213 // Inserting (or Updating)
1214 INSERT INTO users (id, given, surname, favs)
1215 VALUES ('jsmith', 'John', 'Smith', { 'fruit' : 'apple', 'band' : 'Beatles' })
1216
a6c3481 Sylvain Lebresne update CQL3 doc for collections & ttls
pcmanus authored
1217 Adding or updating key-values of a (potentially) existing map can be accomplished either by subscripting the map column in an @UPDATE@ statement or by adding a new map literal:
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1218
1219 bc(sample).
1220 // Updating (or inserting)
1221 UPDATE users SET favs['author'] = 'Ed Poe' WHERE id = 'jsmith'
a6c3481 Sylvain Lebresne update CQL3 doc for collections & ttls
pcmanus authored
1222 UPDATE users SET favs = favs + { 'movie' : 'Cassablanca' } WHERE id = 'jsmith'
1223
1224 Note that TTLs are allowed for both @INSERT@ and @UPDATE@, but in both case the TTL set only apply to the newly inserted/updated _values_. In other words,
1225
1226 bc(sample).
1227 // Updating (or inserting)
1228 UPDATE users USING TTL 10 SET favs['color'] = 'green' WHERE id = 'jsmith'
1229
1230 will only apply the TTL to the @{ 'color' : 'green' }@ record, the rest of the map remaining unaffected.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1231
1232 Deleting a map record is done with:
1233
1234 bc(sample).
fce1735 Aleksey Yeschenko Ninja-fix CQL3 doc
iamaleksey authored
1235 DELETE favs['author'] FROM users WHERE id = 'jsmith'
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1236
1237 h4(#set). Sets
1238
1239 A @set@ is a "typed":#types collection of unique values. Sets are ordered by their values. To create a column of type @set@, use the @set@ keyword suffixed with the value type enclosed in angle brackets. For example:
1240
1241 bc(sample).
1242 CREATE TABLE images (
1243 name text PRIMARY KEY,
1244 owner text,
1245 date timestamp,
1246 tags set<text>
1247 );
1248
1249 Writing a @set@ is accomplished by comma separating the set values, and enclosing them in curly braces. _Note: An @INSERT@ will always replace the entire set._
1250
1251 bc(sample).
1252 INSERT INTO images (name, owner, date, tags)
1253 VALUES ('cat.jpg', 'jsmith', 'now', { 'kitten', 'cat', 'pet' });
1254
1255 Adding and removing values of a set can be accomplished with an @UPDATE@ by adding/removing new set values to an existing @set@ column.
1256
1257 bc(sample).
1258 UPDATE images SET tags = tags + { 'cute', 'cuddly' } WHERE name = 'cat.jpg';
1259 UPDATE images SET tags = tags - { 'lame' } WHERE name = 'cat.jpg';
1260
a6c3481 Sylvain Lebresne update CQL3 doc for collections & ttls
pcmanus authored
1261 As with "maps":#map, TTLs if used only apply to the newly inserted/updated _values_.
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1262
1263 h4(#list). Lists
1264
1265 A @list@ is a "typed":#types collection of non-unique values where elements are ordered by there position in the list. To create a column of type @list@, use the @list@ keyword suffixed with the value type enclosed in angle brackets. For example:
1266
1267 bc(sample).
1268 CREATE TABLE plays (
1269 id text PRIMARY KEY,
1270 game text,
1271 players int,
1272 scores list<int>
1273 )
1274
1275 Do note that as explained below, lists have some limitations and performance considerations to take into account, and it is advised to prefer "sets":#set over lists when this is possible.
1276
1277 Writing @list@ data is accomplished with a JSON-style syntax. To write a record using @INSERT@, specify the entire list as a JSON array. _Note: An @INSERT@ will always replace the entire list._
1278
1279 bc(sample).
1280 INSERT INTO plays (id, game, players, scores)
1281 VALUES ('123-afde', 'quake', 3, [17, 4, 2]);
1282
1283 Adding (appending or prepending) values to a list can be accomplished by adding a new JSON-style array to an existing @list@ column.
1284
1285 bc(sample).
1286 UPDATE plays SET players = 5, scores = scores + [ 14, 21 ] WHERE id = '123-afde';
1287 UPDATE plays SET players = 5, scores = [ 12 ] + scores WHERE id = '123-afde';
1288
64bc335 Sylvain Lebresne Document that list append/prepend are not idempotent and should be used ...
pcmanus authored
1289 It should be noted that append and prepend are not idempotent operations. This means that if during an append or a prepend the operation timeout, it is not always safe to retry the operation (as this could result in the record appended or prepended twice).
1290
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1291 Lists also provides the following operation: setting an element by its position in the list, removing an element by its position in the list and remove all the occurrence of a given value in the list. _However, and contrarily to all the other collection operations, these three operations induce an internal read before the update, and will thus typically have slower performance characteristics_. Those operations have the following syntax:
1292
1293 bc(sample).
1294 UPDATE plays SET scores[1] = 7 WHERE id = '123-afde'; // sets the 2nd element of scores to 7 (raises an error is scores has less than 2 elements)
1295 DELETE scores[1] FROM plays WHERE id = '123-afde'; // deletes the 2nd element of scores (raises an error is scores has less than 2 elements)
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1296 UPDATE plays SET scores = scores - [ 12, 21 ] WHERE id = '123-afde'; // removes all occurrences of 12 and 21 from scores
3b425b5 Sylvain Lebresne Update CQL3 documentation
pcmanus authored
1297
a6c3481 Sylvain Lebresne update CQL3 doc for collections & ttls
pcmanus authored
1298 As with "maps":#map, TTLs if used only apply to the newly inserted/updated _values_.
1299
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1300
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1301 h2(#functions). Functions
1302
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1303 CQL3 distinguishes between built-in functions (so called 'native functions') and "user-defined functions":#udfs. CQL3 includes several native functions, described below:
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1304
1305 h3(#tokenFun). Token
1306
1307 The @token@ function allows to compute the token for a given partition key. The exact signature of the token function depends on the table concerned and of the partitioner used by the cluster.
1308
1309 The type of the arguments of the @token@ depend on the type of the partition key columns. The return type depend on the partitioner in use:
1310 * For Murmur3Partitioner, the return type is @bigint@.
1311 * For RandomPartitioner, the return type is @varint@.
1312 * For ByteOrderedPartitioner, the return type is @blob@.
1313
1314 For instance, in a cluster using the default Murmur3Partitioner, if a table is defined by
1315
1316 bc(sample).
1317 CREATE TABLE users (
1318 userid text PRIMARY KEY,
1319 username text,
1320 ...
1321 )
1322
4cb045a Sylvain Lebresne Improve CQL3 documentation section on COMPACT STORAGE (CASSANDRA-6642)
pcmanus authored
1323 then the @token@ function will take a single argument of type @text@ (in that case, the partition key is @userid@ (there is no clustering columns so the partition key is the same than the primary key)), and the return type will be @bigint@.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1324
32c15c2 Carl Yeksigian Add uuid() function
carlyeks authored
1325 h3(#uuidFun). Uuid
1326
1327 The @uuid@ function takes no parameters and generates a random type 4 uuid suitable for use in INSERT or SET statements.
1328
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1329 h3(#timeuuidFun). Timeuuid functions
1330
1331 h4. @now@
1332
1333 The @now@ function takes no arguments and generates a new unique timeuuid (at the time where the statement using it is executed). Note that this method is useful for insertion but is largely non-sensical in @WHERE@ clauses. For instance, a query of the form
1334
1335 bc(sample).
1336 SELECT * FROM myTable WHERE t = now()
1337
1338 will never return any result by design, since the value returned by @now()@ is guaranteed to be unique.
1339
1340 h4. @minTimeuuid@ and @maxTimeuuid@
1341
1342 The @minTimeuuid@ (resp. @maxTimeuuid@) function takes a @timestamp@ value @t@ (which can be "either a timestamp or a date string":#usingdates) and return a _fake_ @timeuuid@ corresponding to the _smallest_ (resp. _biggest_) possible @timeuuid@ having for timestamp @t@. So for instance:
1343
1344 bc(sample).
1345 SELECT * FROM myTable WHERE t > maxTimeuuid('2013-01-01 00:05+0000') AND t < minTimeuuid('2013-02-02 10:00+0000')
1346
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1347 will select all rows where the @timeuuid@ column @t@ is strictly older than '2013-01-01 00:05+0000' but strictly younger than '2013-02-02 10:00+0000'. Please note that @t >= maxTimeuuid('2013-01-01 00:05+0000')@ would still _not_ select a @timeuuid@ generated exactly at '2013-01-01 00:05+0000' and is essentially equivalent to @t > maxTimeuuid('2013-01-01 00:05+0000')@.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1348
1349 _Warning_: We called the values generated by @minTimeuuid@ and @maxTimeuuid@ _fake_ UUID because they do no respect the Time-Based UUID generation process specified by the "RFC 4122":http://www.ietf.org/rfc/rfc4122.txt. In particular, the value returned by these 2 methods will not be unique. This means you should only use those methods for querying (as in the example above). Inserting the result of those methods is almost certainly _a bad idea_.
1350
1351 h4. @dateOf@ and @unixTimestampOf@
1352
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1353 The @dateOf@ and @unixTimestampOf@ functions take a @timeuuid@ argument and extract the embedded timestamp. However, while the @dateof@ function return it with the @timestamp@ type (that most client, including cqlsh, interpret as a date), the @unixTimestampOf@ function returns it as a @bigint@ raw value.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1354
1355 h3(#blobFun). Blob conversion functions
1356
1357 A number of functions are provided to "convert" the native types into binary data (@blob@). For every @<native-type>@ @type@ supported by CQL3 (a notable exceptions is @blob@, for obvious reasons), the function @typeAsBlob@ takes a argument of type @type@ and return it as a @blob@. Conversely, the function @blobAsType@ takes a 64-bit @blob@ argument and convert it to a @bigint@ value. And so for instance, @bigintAsBlob(3)@ is @0x0000000000000003@ and @blobAsBigint(0x0000000000000003)@ is @3@.
1358
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1359 h2(#udfs). User-Defined Functions
1360
1361 User-defined functions allow execution of user-provided code in Cassandra. By default, Cassandra supports defining functions in _Java_ and _JavaScript_. Support for other JSR 223 compliant scripting languages (such as Python, Ruby, and Scala) can be added by adding a JAR to the classpath.
1362
1363 UDFs are part of the Cassandra schema. As such, they are automatically propagated to all nodes in the cluster.
1364
1365 UDFs can be _overloaded_ - i.e. multiple UDFs with different argument types but the same function name. Example:
1366
1367 bc(sample).
1368 CREATE FUNCTION sample ( arg int ) ...;
1369 CREATE FUNCTION sample ( arg text ) ...;
1370
1371 User-defined functions are susceptible to all of the normal problems with the chosen programming language. Accordingly, implementations should be safe against null pointer exceptions, illegal arguments, or any other potential source of exceptions. An exception during function execution will result in the entire statement failing.
1372
1373 It is valid to use _complex_ types like collections, tuple types and user-defined types as argument and return types. Tuple types and user-defined types are handled by the conversion functions of the DataStax Java Driver. Please see the documentation of the Java Driver for details on handling tuple types and user-defined types.
1374
1375 Arguments for functions can be literals or terms. Prepared statement placeholders can be used, too.
1376
1377 Note that you can use the double-quoted string syntax to enclose the UDF source code. For example:
1378
1379 bc(sample)..
1380 CREATE FUNCTION some_function ( arg int )
1381 RETURNS int
1382 LANGUAGE java
1383 AS $$ return arg; $$;
1384
1385 SELECT some_function(column) FROM atable ...;
1386 UPDATE atable SET col = some_function(?) ...;
1387 p.
1388
1389 bc(sample).
1390 CREATE TYPE custom_type (txt text, i int);
1391 CREATE FUNCTION fct_using_udt ( udtarg frozen<customType> )
1392 RETURNS text
1393 LANGUAGE java
1394 AS $$ return udtarg.getString("txt"); $$;
1395
1396 User-defined functions can be used in "@SELECT@":#selectStmt, "@INSERT@":#insertStmt and "@UPDATE@":#updateStmt statements.
1397
1398 See "@CREATE FUNCTION@":#createFunctionStmt and "@DROP FUNCTION@":#dropFunctionStmt.
1399
1400 h2(#udas). User-Defined Aggregates
1401
1402 User-defined aggregates allow creation of custom aggregate functions using "UDFs":#udfs. Common examples of aggregate functions are _count_, _min_, and _max_.
1403
1404 Each aggregate requires an _initial state_ (@INITCOND@, which defaults to @null@) of type @STYPE@. The first argument of the state function must have type @STYPE@. The remaining arguments of the state function must match the types of the user-defined aggregate arguments. The state function is called once for each row, and the value returned by the state function becomes the new state. After all rows are processed, the optional @FINALFUNC@ is executed with last state value as its argument.
1405
1406 @STYPE@ is mandatory in order to be able to distinguish possibly overloaded versions of the state and/or final function (since the overload can appear after creation of the aggregate).
1407
1408 User-defined aggregates can be used in "@SELECT@":#selectStmt statement.
1409
1410 A complete working example for user-defined aggregates (assuming that a keyspace has been selected using the "@USE@":#useStmt statement):
1411
1412 bc(sample)..
1413 CREATE FUNCTION averageState ( state tuple<int,bigint>, val int )
1414 RETURNS tuple<int,bigint>
1415 LANGUAGE java
1416 AS '
1417 if (val != null) {
1418 state.setInt(0, state.getInt(0)+1);
1419 state.setLong(1, state.getLong(1)+val.intValue());
1420 }
1421 return state;
1422 ';
1423
1424 CREATE FUNCTION averageFinal ( state tuple<int,bigint> )
1425 RETURNS double
1426 LANGUAGE java
1427 AS '
1428 double r = 0;
1429 if (state.getInt(0) == 0) return null;
1430 r = state.getLong(1);
1431 r /= state.getInt(0);
1432 return Double.valueOf(r);
1433 ';
1434
1435 CREATE AGGREGATE average ( int )
1436 SFUNC averageState
1437 STYPE tuple<int,bigint>
1438 FINALFUNC averageFinal
1439 INITCOND (0, 0);
1440
1441 CREATE TYPE atable (
1442 pk int PRIMARY KEY,
1443 val int);
1444 INSERT INTO atable (pk, val) VALUES (1,1);
1445 INSERT INTO atable (pk, val) VALUES (2,2);
1446 INSERT INTO atable (pk, val) VALUES (3,3);
1447 INSERT INTO atable (pk, val) VALUES (4,4);
1448 SELECT average(val) FROM atable;
1449 p.
1450
1451 See "@CREATE AGGREGATE@":#createAggregateStmt and "@DROP AGGREGATE@":#dropAggregateStmt.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1452
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1453 h2(#appendixA). Appendix A: CQL Keywords
1454
1455 CQL distinguishes between _reserved_ and _non-reserved_ keywords. Reserved keywords cannot be used as identifier, they are truly reserved for the language (but one can enclose a reserved keyword by double-quotes to use it as an identifier). Non-reserved keywords however only have a specific meaning in certain context but can used as identifer otherwise. The only _raison d'être_ of these non-reserved keywords is convenience: some keyword are non-reserved when it was always easy for the parser to decide whether they were used as keywords or not.
1456
1457 |_. Keyword |_. Reserved? |
1458 | @ADD@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1459 | @AGGREGATE@ | no |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1460 | @ALL@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1461 | @ALTER@ | yes |
1462 | @AND@ | yes |
1463 | @ANY@ | yes |
1464 | @APPLY@ | yes |
08df0b8 Aleksey Yeschenko Add alias support to SELECT statement
iamaleksey authored
1465 | @AS@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1466 | @ASC@ | yes |
1467 | @ASCII@ | no |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1468 | @AUTHORIZE@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1469 | @BATCH@ | yes |
1470 | @BEGIN@ | yes |
1471 | @BIGINT@ | no |
1472 | @BLOB@ | no |
1473 | @BOOLEAN@ | no |
1474 | @BY@ | yes |
1475 | @CLUSTERING@ | no |
72ca5e6 Sylvain Lebresne Fix typo in CQL doc
pcmanus authored
1476 | @COLUMNFAMILY@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1477 | @COMPACT@ | no |
72ca5e6 Sylvain Lebresne Fix typo in CQL doc
pcmanus authored
1478 | @CONSISTENCY@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1479 | @COUNT@ | no |
1480 | @COUNTER@ | no |
1481 | @CREATE@ | yes |
1482 | @DECIMAL@ | no |
1483 | @DELETE@ | yes |
1484 | @DESC@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1485 | @DETERMINISTIC@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1486 | @DOUBLE@ | no |
1487 | @DROP@ | yes |
1488 | @EACH_QUORUM@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1489 | @FUNCTION@ | no |
1490 | @FINALFUNC@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1491 | @FLOAT@ | no |
1492 | @FROM@ | yes |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1493 | @GRANT@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1494 | @IN@ | yes |
1495 | @INDEX@ | yes |
64c0d1e Aleksey Yeschenko Add custom secondary index support to CQL3
iamaleksey authored
1496 | @CUSTOM@ | no |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1497 | @INITCOND@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1498 | @INSERT@ | yes |
1499 | @INT@ | no |
1500 | @INTO@ | yes |
1501 | @KEY@ | no |
1502 | @KEYSPACE@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1503 | @LANGUAGE@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1504 | @LEVEL@ | no |
1505 | @LIMIT@ | yes |
c9528f9 Jason Brown apply local_one changes to 2.0
jasobrown authored
1506 | @LOCAL_ONE@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1507 | @LOCAL_QUORUM@ | yes |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1508 | @MODIFY@ | yes |
1509 | @NORECURSIVE@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1510 | @NON@ | no |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1511 | @NOSUPERUSER@ | no |
1512 | @OF@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1513 | @ON@ | yes |
1514 | @ONE@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1515 | @OR@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1516 | @ORDER@ | yes |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1517 | @PASSWORD@ | no |
1518 | @PERMISSION@ | no |
1519 | @PERMISSIONS@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1520 | @PRIMARY@ | yes |
1521 | @QUORUM@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1522 | @REPLACE@ | yes |
1523 | @RETURNS@ | no |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1524 | @REVOKE@ | yes |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1525 | @SCHEMA@ | yes |
1526 | @SELECT@ | yes |
1527 | @SET@ | yes |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1528 | @SFUNC@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1529 | @STORAGE@ | no |
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1530 | @STYPE@ | no |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1531 | @SUPERUSER@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1532 | @TABLE@ | yes |
1533 | @TEXT@ | no |
1534 | @TIMESTAMP@ | no |
1535 | @TIMEUUID@ | no |
1536 | @THREE@ | yes |
1537 | @TOKEN@ | yes |
1538 | @TRUNCATE@ | yes |
1539 | @TTL@ | no |
1540 | @TWO@ | yes |
1541 | @TYPE@ | no |
1542 | @UPDATE@ | yes |
1543 | @USE@ | yes |
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1544 | @USER@ | no |
1545 | @USERS@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1546 | @USING@ | yes |
1547 | @UUID@ | no |
1548 | @VALUES@ | no |
1549 | @VARCHAR@ | no |
1550 | @VARINT@ | no |
1551 | @WHERE@ | yes |
1552 | @WITH@ | yes |
1553 | @WRITETIME@ | no |
b362aeb Aleksey Yeschenko Add ability for CQL3 to list partition keys
iamaleksey authored
1554 | @DISTINCT@ | no |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1555
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
1556 h2(#appendixB). Appendix B: CQL Reserved Types
1557
1558 The following type names are not currently used by CQL, but are reserved for potential future use. User-defined types may not use reserved type names as their name.
1559
1560 |_. type |
1561 | @byte@ |
1562 | @smallint@ |
1563 | @complex@ |
1564 | @enum@ |
1565 | @date@ |
1566 | @interval@ |
1567 | @macaddr@ |
1568 | @bitstring@ |
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1569
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1570 h2(#changes). Changes
1571
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
1572 The following describes the changes in each version of CQL.
1573
93ebff3 Robert Stupp Update CQL docs for UDFs and aggregates
snazy authored
1574 h3. 3.3.0
1575
1576 * User-defined functions are now supported through "@CREATE FUNCTION@":#createFunctionStmt and "@DROP FUNCTION@":#dropFunctionStmt,
1577 * User-defined aggregates are now supported through "@CREATE AGGREGATE@":#createAggregateStmt and "@DROP AGGREGATE@":#dropAggregateStmt.
1578 * Allows double-dollar enclosed strings literals as an alternative to single-quote enclosed strings.
1579
e60b4e6 Tyler Hobbs Bump CQL version to 3.2.0 and update CQL docs
thobbs authored
1580 h3. 3.2.0
1581
1582 * User-defined types are now supported through "@CREATE TYPE@":#createTypeStmt, "@ALTER TYPE@":#alterTypeStmt, and "@DROP TYPE@":#dropTypeStmt
1583 * "@CREATE INDEX@":#createIndexStmt now supports indexing collection columns, including indexing the keys of map collections through the @keys()@ function
1584 * Indexes on collections may be queried using the new @CONTAINS@ and @CONTAINS KEY@ operators
1585 * Tuple types were added to hold fixed-length sets of typed positional fields (see the section on "types":#types)
1586 * "@DROP INDEX@":#dropIndexStmt now supports optionally specifying a keyspace
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1587
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
1588 h3. 3.1.7
1589
1590 * @SELECT@ statements now support selecting multiple rows in a single partition using an @IN@ clause on combinations of clustering columns. See "SELECT WHERE":#selectWhere clauses.
7216639 Aleksey Yeschenko Add conditional CREATE/DROP USER support
iamaleksey authored
1591 * @IF NOT EXISTS@ and @IF EXISTS@ syntax is now supported by @CREATE USER@ and @DROP USER@ statmenets, respectively.
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
1592
48d7e40 Sylvain Lebresne Fix CQL version number for CASSANDRA-7055
pcmanus authored
1593 h3. 3.1.6
1594
1595 * A new "@uuid@ method":#uuidFun has been added.
1596 * Support for @DELETE ... IF EXISTS@ syntax.
1597
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
1598 h3. 3.1.5
1599
926f639 Tyler Hobbs Bump CQL version to 3.1.7, update CQL docs
thobbs authored
1600 * It is now possible to group clustering columns in a relatiion, see "SELECT WHERE":#selectWhere clauses.
b09d876 Sylvain Lebresne Add static columns in CQL3
pcmanus authored
1601 * Added support for @STATIC@ columns, see "static in CREATE TABLE":#createTableStatic.
652ec6a Sylvain Lebresne CQL3: improve support for paginating over composites
pcmanus authored
1602
5fa6055 Aleksey Yeschenko Allow specifying custom secondary index options in CQL3
iamaleksey authored
1603 h3. 3.1.4
1604
1605 * @CREATE INDEX@ now allows specifying options when creating CUSTOM indexes (see "CREATE INDEX reference":#createIndexStmt).
1606
21bb531 Aleksey Yeschenko Update docs, bump CQL3 version, correct CHANGES.txt for CASSANDRA-6395
iamaleksey authored
1607 h3. 3.1.3
1608
1609 * Millisecond precision formats have been added to the timestamp parser (see "working with dates":#usingdates).
1610
08f2e97 Sylvain Lebresne Support NaN and Infinity as float constants
pcmanus authored
1611 h3. 3.1.2
1612
1613 * @NaN@ and @Infinity@ has been added as valid float contants. They are now reserved keywords. In the unlikely case you we using them as a column identifier (or keyspace/table one), you will noew need to double quote them (see "quote identifiers":#identifiers).
1614
b362aeb Aleksey Yeschenko Add ability for CQL3 to list partition keys
iamaleksey authored
1615 h3. 3.1.1
1616
1617 * @SELECT@ statement now allows listing the partition keys (using the @DISTINCT@ modifier). See "CASSANDRA-4536":https://issues.apache.org/jira/browse/CASSANDRA-4536.
e93578b Sylvain Lebresne Support variadic parameters for IN clauses
pcmanus authored
1618 * 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.
37e9bce Sylvain Lebresne Support named bind variables in CQL
pcmanus authored
1619 * It is now possible to use named bind variables (using @:name@ instead of @?@).
b362aeb Aleksey Yeschenko Add ability for CQL3 to list partition keys
iamaleksey authored
1620
883c34b Aleksey Yeschenko Reenable ALTER TABLE DROP with new semantics
iamaleksey authored
1621 h3. 3.1.0
1622
1623 * "ALTER TABLE":#alterTableStmt @DROP@ option has been reenabled for CQL3 tables and has new semantics now: the space formerly used by dropped columns will now be eventually reclaimed (post-compaction). You should not readd previously dropped columns unless you use timestamps with microsecond precision (see "CASSANDRA-3919":https://issues.apache.org/jira/browse/CASSANDRA-3919 for more details).
b73f9d4 Sylvain Lebresne Conditional create/drop ks/table/index statements
pcmanus authored
1624 * @SELECT@ statement now supports aliases in select clause. Aliases in WHERE and ORDER BY clauses are not supported. See the "section on select"#selectStmt for details.
1625 * @CREATE@ statements for @KEYSPACE@, @TABLE@ and @INDEX@ now supports an @IF NOT EXISTS@ condition. Similarly, @DROP@ statements support a @IF EXISTS@ condition.
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1626 * @INSERT@ statements optionally supports a @IF NOT EXISTS@ condition and @UPDATE@ supports @IF@ conditions.
883c34b Aleksey Yeschenko Reenable ALTER TABLE DROP with new semantics
iamaleksey authored
1627
dc457c5 Aleksey Yeschenko Allow empty IN relations in SELECT/UPDATE/DELETE statements
iamaleksey authored
1628 h3. 3.0.5
1629
91cd653 Aleksey Yeschenko Tiny CQL3 doc tweak
iamaleksey authored
1630 * @SELECT@, @UPDATE@, and @DELETE@ statements now allow empty @IN@ relations (see "CASSANDRA-5626":https://issues.apache.org/jira/browse/CASSANDRA-5626).
dc457c5 Aleksey Yeschenko Allow empty IN relations in SELECT/UPDATE/DELETE statements
iamaleksey authored
1631
2397bc8 Aleksey Yeschenko Updated CREATE CUSTOM INDEX syntax
iamaleksey authored
1632 h3. 3.0.4
1633
1634 * Updated the syntax for custom "secondary indexes":#createIndexStmt.
41f418a Sylvain Lebresne Never allow partition range queries in CQL3 without token()
pcmanus authored
1635 * 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 "WHERE clauses":#selectWhere).
2397bc8 Aleksey Yeschenko Updated CREATE CUSTOM INDEX syntax
iamaleksey authored
1636
64c0d1e Aleksey Yeschenko Add custom secondary index support to CQL3
iamaleksey authored
1637 h3. 3.0.3
1638
1639 * Support for custom "secondary indexes":#createIndexStmt has been added.
1640
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
1641 h3. 3.0.2
1642
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1643 * Type validation for the "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 "data types":#types section for details on which constant is allowed for which type.
f2eaf9a Aleksey Yeschenko Fix CQL3 doc typos
iamaleksey authored
1644 * The type validation fixed of the previous point has lead to the introduction of "blobs constants":#constants to allow inputing blobs. Do note that while inputing blobs as strings constant is still supported by this version (to allow smoother transition to blob constant), it is now deprecated (in particular the "data types":#types section does not list strings constants as valid blobs) 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.
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1645 * 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 "section on functions":#functions for details.
a67f779 Sylvain Lebresne Improve CQL3 type validation
pcmanus authored
1646
8be7e5c Sylvain Lebresne Fix interpreting dates as valid timeuuid and introduce new methods to wo...
pcmanus authored
1647 h3. 3.0.1
1648
31e669a Sylvain Lebresne CQL3 refactor to allow conversion function
pcmanus authored
1649 * "Date strings":#usingdates (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@. See the "section dedicated to these methods":#usingtimeuuid for more detail.
1650 * "Float constants"#constants now support the exponent notation. In other words, @4.2E10@ is now a valid floating point value.
0804de9 Sylvain Lebresne Minor CQL3 doc fixes
pcmanus authored
1651
1652
e4ce6b3 Sylvain Lebresne Add CQL3 documentation
pcmanus authored
1653 h2. Versioning
1654
1655 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.
1656
1657 |_. version|_. description|
1658 |Major |The major version _must_ be bumped when backward incompatible changes are introduced. This should rarely occur.|
1659 |Minor |Minor version increments occur when new, but backward compatible, functionality is introduced.|
1660 |Patch |The patch version is incremented when bugs are fixed.|
Something went wrong with that request. Please try again.