Added: cassandra/site/src/doc/3.10/cql/dml.html URL: http://svn.apache.org/viewvc/cassandra/site/src/doc/3.10/cql/dml.html?rev=1757419&view=auto ============================================================================== --- cassandra/site/src/doc/3.10/cql/dml.html (added) +++ cassandra/site/src/doc/3.10/cql/dml.html Tue Aug 23 19:25:17 2016 @@ -0,0 +1,559 @@ +--- +layout: docpage + +title: "Documentation" + +is_homepage: false +is_sphinx_doc: true + +doc-parent: "The Cassandra Query Language (CQL)" + +doc-title: "Data Manipulation" +doc-header-links: ' + <link rel="top" title="Apache Cassandra Documentation v3.10" href="../index.html"/> + <link rel="up" title="The Cassandra Query Language (CQL)" href="index.html"/> + <link rel="next" title="Secondary Indexes" href="indexes.html"/> + <link rel="prev" title="Data Definition" href="ddl.html"/> +' +doc-search-path: "../search.html" + +extra-footer: ' +<script type="text/javascript"> + var DOCUMENTATION_OPTIONS = { + URL_ROOT: "", + VERSION: "", + COLLAPSE_INDEX: false, + FILE_SUFFIX: ".html", + HAS_SOURCE: false, + SOURCELINK_SUFFIX: "" + }; +</script> +' + +--- +<div class="container-fluid"> + <div class="row"> + <div class="col-md-2"> + <div class="doc-navigation"> + <div class="doc-menu" role="navigation"> + <div class="navbar-header"> + <button type="button" class="pull-left navbar-toggle" data-toggle="collapse" data-target=".sidebar-navbar-collapse"> + <span class="sr-only">Toggle navigation</span> + <span class="icon-bar"></span> + <span class="icon-bar"></span> + <span class="icon-bar"></span> + </button> + </div> + <div class="navbar-collapse collapse sidebar-navbar-collapse"> + <form id="doc-search-form" class="navbar-form" action="../search.html" method="get" role="search"> + <div class="form-group"> + <input type="text" size="30" class="form-control input-sm" name="q" placeholder="Search docs"> + <input type="hidden" name="check_keywords" value="yes" /> + <input type="hidden" name="area" value="default" /> + </div> + </form> + + + + <ul class="current"> +<li class="toctree-l1"><a class="reference internal" href="../getting_started/index.html">Getting Started</a></li> +<li class="toctree-l1"><a class="reference internal" href="../architecture/index.html">Architecture</a></li> +<li class="toctree-l1"><a class="reference internal" href="../data_modeling/index.html">Data Modeling</a></li> +<li class="toctree-l1 current"><a class="reference internal" href="index.html">The Cassandra Query Language (CQL)</a><ul class="current"> +<li class="toctree-l2"><a class="reference internal" href="definitions.html">Definitions</a></li> +<li class="toctree-l2"><a class="reference internal" href="types.html">Data Types</a></li> +<li class="toctree-l2"><a class="reference internal" href="ddl.html">Data Definition</a></li> +<li class="toctree-l2 current"><a class="current reference internal" href="#">Data Manipulation</a><ul> +<li class="toctree-l3"><a class="reference internal" href="#select">SELECT</a></li> +<li class="toctree-l3"><a class="reference internal" href="#insert">INSERT</a></li> +<li class="toctree-l3"><a class="reference internal" href="#update">UPDATE</a></li> +<li class="toctree-l3"><a class="reference internal" href="#delete">DELETE</a></li> +<li class="toctree-l3"><a class="reference internal" href="#batch">BATCH</a></li> +</ul> +</li> +<li class="toctree-l2"><a class="reference internal" href="indexes.html">Secondary Indexes</a></li> +<li class="toctree-l2"><a class="reference internal" href="mvs.html">Materialized Views</a></li> +<li class="toctree-l2"><a class="reference internal" href="security.html">Security</a></li> +<li class="toctree-l2"><a class="reference internal" href="functions.html">Functions</a></li> +<li class="toctree-l2"><a class="reference internal" href="json.html">JSON Support</a></li> +<li class="toctree-l2"><a class="reference internal" href="triggers.html">Triggers</a></li> +<li class="toctree-l2"><a class="reference internal" href="appendices.html">Appendices</a></li> +<li class="toctree-l2"><a class="reference internal" href="changes.html">Changes</a></li> +</ul> +</li> +<li class="toctree-l1"><a class="reference internal" href="../configuration/index.html">Configuring Cassandra</a></li> +<li class="toctree-l1"><a class="reference internal" href="../operating/index.html">Operating Cassandra</a></li> +<li class="toctree-l1"><a class="reference internal" href="../tools/index.html">Cassandra Tools</a></li> +<li class="toctree-l1"><a class="reference internal" href="../troubleshooting/index.html">Troubleshooting</a></li> +<li class="toctree-l1"><a class="reference internal" href="../development/index.html">Cassandra Development</a></li> +<li class="toctree-l1"><a class="reference internal" href="../faq/index.html">Frequently Asked Questions</a></li> +<li class="toctree-l1"><a class="reference internal" href="../bugs.html">Reporting Bugs and Contributing</a></li> +<li class="toctree-l1"><a class="reference internal" href="../contactus.html">Contact us</a></li> +</ul> + + + + </div><!--/.nav-collapse --> + </div> + </div> + </div> + <div class="col-md-8"> + <div class="content doc-content"> + <div class="container"> + + <div class="section" id="data-manipulation"> +<span id="id1"></span><h1>Data Manipulation<a class="headerlink" href="#data-manipulation" title="Permalink to this headline">¶</a></h1> +<p>This section describes the statements supported by CQL to insert, update, delete and query data.</p> +<div class="section" id="select"> +<span id="select-statement"></span><h2>SELECT<a class="headerlink" href="#select" title="Permalink to this headline">¶</a></h2> +<p>Querying data from data is done using a <code class="docutils literal"><span class="pre">SELECT</span></code> statement:</p> +<pre> +<strong id="grammar-token-select_statement">select_statement</strong> ::= SELECT [ JSON | DISTINCT ] ( <a class="reference internal" href="#grammar-token-select_clause"><code class="xref docutils literal"><span class="pre">select_clause</span></code></a> | '*' ) + FROM <a class="reference internal" href="ddl.html#grammar-token-table_name"><code class="xref docutils literal"><span class="pre">table_name</span></code></a> + [ WHERE <a class="reference internal" href="#grammar-token-where_clause"><code class="xref docutils literal"><span class="pre">where_clause</span></code></a> ] + [ GROUP BY <a class="reference internal" href="#grammar-token-group_by_clause"><code class="xref docutils literal"><span class="pre">group_by_clause</span></code></a> ] + [ ORDER BY <a class="reference internal" href="#grammar-token-ordering_clause"><code class="xref docutils literal"><span class="pre">ordering_clause</span></code></a> ] + [ PER PARTITION LIMIT (<a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind_marker"><code class="xref docutils literal"><span class="pre">bind_marker</span></code></a>) ] + [ LIMIT (<a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind_marker"><code class="xref docutils literal"><span class="pre">bind_marker</span></code></a>) ] + [ ALLOW FILTERING ] +<strong id="grammar-token-select_clause">select_clause </strong> ::= <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal"><span class="pre">identifier</span></code></a> ] ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal"><span class="pre">identifier</span></code></a> ] ) +<strong id="grammar-token-selector">selector </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> + | <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> + | CAST '(' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal"><span class="pre">selector</span></code></a> AS <a class="reference internal" href="types.html#grammar-token-cql_type"><code class="xref docutils literal"><span class="pre">cql_type</span></code></a> ')' + | <a class="reference internal" href="functions.html#grammar-token-function_name"><code class="xref docutils literal"><span class="pre">function_name</span></code></a> '(' [ <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal"><span class="pre">selector</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal"><span class="pre">selector</span></code></a> )* ] ')' + | COUNT '(' '*' ')' +<strong id="grammar-token-where_clause">where_clause </strong> ::= <a class="reference internal" href="#grammar-token-relation"><code class="xref docutils literal"><span class="pre">relation</span></code></a> ( AND <a class="reference internal" href="#grammar-token-relation"><code class="xref docutils literal"><span class="pre">relation</span></code></a> )* +<strong id="grammar-token-relation">relation </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> + '(' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal"><span class="pre">operator</span></code></a> <a class="reference internal" href="types.html#grammar-token-tuple_literal"><code class="xref docutils literal"><span class="pre">tuple_literal</span></code></a> + TOKEN '(' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> +<strong id="grammar-token-operator">operator </strong> ::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY +<strong id="grammar-token-group_by_clause">group_by_clause </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> )* +<strong id="grammar-token-ordering_clause">ordering_clause </strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> [ ASC | DESC ] ( ',' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> [ ASC | DESC ] )* +</pre> +<p>For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="k">IN</span> <span class="p">(</span><span class="mf">199</span><span class="p">,</span> <span class="mf">200</span><span class="p">,</span> <span class="mf">207</span><span class="p">);</span> +<span class="k">SELECT</span> <span class="k">JSON</span> <span class="n">name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="mf">199</span><span class="p">;</span> +<span class="k">SELECT</span> <span class="n">name</span> <span class="k">AS</span> <span class="n">user_name</span><span class="p">,</span> <span class="n">occupation</span> <span class="k">AS</span> <span class="n">user_occupation</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span> + +<span class="k">SELECT</span> <span class="nb">time</span><span class="p">,</span> <span class="n">value</span> +<span class="k">FROM</span> <span class="n">events</span> +<span class="k">WHERE</span> <span class="n">event_type</span> <span class="o">=</span> <span class="s1">'myEvent'</span> + <span class="k">AND</span> <span class="nb">time</span> <span class="o">></span> <span class="s1">'2011-02-03'</span> + <span class="k">AND</span> <span class="nb">time</span> <span class="o"><=</span> <span class="s1">'2012-01-01'</span> + +<span class="k">SELECT</span> <span class="k">COUNT</span> <span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">user_count</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">SELECT</span></code> statements reads one or more columns for one or more rows in a table. It returns a result-set of the rows +matching the request, where each row contains the values for the selection corresponding to the query. Additionally, +<a class="reference internal" href="functions.html#cql-functions"><span class="std std-ref">functions</span></a> including <a class="reference internal" href="functions.html#aggregate-functions"><span class="std std-ref">aggregation</span></a> ones can be applied to the result.</p> +<p>A <code class="docutils literal"><span class="pre">SELECT</span></code> statement contains at least a <a class="reference internal" href="#selection-clause"><span class="std std-ref">selection clause</span></a> and the name of the table on which +the selection is on (note that CQL does <strong>not</strong> joins or sub-queries and thus a select statement only apply to a single +table). In most case, a select will also have a <a class="reference internal" href="#where-clause"><span class="std std-ref">where clause</span></a> and it can optionally have additional +clauses to <a class="reference internal" href="#ordering-clause"><span class="std std-ref">order</span></a> or <a class="reference internal" href="#limit-clause"><span class="std std-ref">limit</span></a> the results. Lastly, <a class="reference internal" href="#allow-filtering"><span class="std std-ref">queries that require +filtering</span></a> can be allowed if the <code class="docutils literal"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> flag is provided.</p> +<div class="section" id="selection-clause"> +<span id="id2"></span><h3>Selection clause<a class="headerlink" href="#selection-clause" title="Permalink to this headline">¶</a></h3> +<p>The <a class="reference internal" href="#grammar-token-select_clause"><code class="xref std std-token docutils literal"><span class="pre">select_clause</span></code></a> determines which columns needs to be queried and returned in the result-set, as well as any +transformation to apply to this result before returning. It consists of a comma-separated list of <em>selectors</em> or, +alternatively, of the wildcard character (<code class="docutils literal"><span class="pre">*</span></code>) to select all the columns defined in the table.</p> +<div class="section" id="selectors"> +<h4>Selectors<a class="headerlink" href="#selectors" title="Permalink to this headline">¶</a></h4> +<p>A <a class="reference internal" href="#grammar-token-selector"><code class="xref std std-token docutils literal"><span class="pre">selector</span></code></a> can be one of:</p> +<ul class="simple"> +<li>A column name of the table selected, to retrieve the values for that column.</li> +<li>A term, which is usually used nested inside other selectors like functions (if a term is selected directly, then the +corresponding column of the result-set will simply have the value of this term for every row returned).</li> +<li>A casting, which allows to convert a nested selector to a (compatible) type.</li> +<li>A function call, where the arguments are selector themselves. See the section on <a class="reference internal" href="functions.html#cql-functions"><span class="std std-ref">functions</span></a> for +more details.</li> +<li>The special call <code class="docutils literal"><span class="pre">COUNT(*)</span></code> to the <a class="reference internal" href="functions.html#count-function"><span class="std std-ref">COUNT function</span></a>, which counts all non-null results.</li> +</ul> +</div> +<div class="section" id="aliases"> +<h4>Aliases<a class="headerlink" href="#aliases" title="Permalink to this headline">¶</a></h4> +<p>Every <em>top-level</em> selector can also be aliased (using <cite>AS</cite>). If so, the name of the corresponding column in the result +set will be that of the alias. For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="c1">// Without alias</span> +<span class="k">SELECT</span> <span class="n">intAsBlob</span><span class="p">(</span><span class="mf">4</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span> + +<span class="c1">// intAsBlob(4)</span> +<span class="c1">// --------------</span> +<span class="c1">// 0x00000004</span> + +<span class="c1">// With alias</span> +<span class="k">SELECT</span> <span class="n">intAsBlob</span><span class="p">(</span><span class="mf">4</span><span class="p">)</span> <span class="k">AS</span> <span class="n">four</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span> + +<span class="c1">// four</span> +<span class="c1">// ------------</span> +<span class="c1">// 0x00000004</span> +</pre></div> +</div> +<div class="admonition note"> +<p class="first admonition-title">Note</p> +<p class="last">Currently, aliases aren’t recognized anywhere else in the statement where they are used (not in the <code class="docutils literal"><span class="pre">WHERE</span></code> +clause, not in the <code class="docutils literal"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause, ...). You must use the orignal column name instead.</p> +</div> +</div> +<div class="section" id="writetime-and-ttl-function"> +<h4><code class="docutils literal"><span class="pre">WRITETIME</span></code> and <code class="docutils literal"><span class="pre">TTL</span></code> function<a class="headerlink" href="#writetime-and-ttl-function" title="Permalink to this headline">¶</a></h4> +<p>Selection supports two special functions (that aren’t allowed anywhere else): <code class="docutils literal"><span class="pre">WRITETIME</span></code> and <code class="docutils literal"><span class="pre">TTL</span></code>. Both function +take only one argument and that argument <em>must</em> be a column name (so for instance <code class="docutils literal"><span class="pre">TTL(3)</span></code> is invalid).</p> +<p>Those functions allow to retrieve meta-information that are stored internally for each column, namely:</p> +<ul class="simple"> +<li>the timestamp of the value of the column for <code class="docutils literal"><span class="pre">WRITETIME</span></code>.</li> +<li>the remaining time to live (in seconds) for the value of the column if it set to expire (and <code class="docutils literal"><span class="pre">null</span></code> otherwise).</li> +</ul> +</div> +</div> +<div class="section" id="the-where-clause"> +<span id="where-clause"></span><h3>The <code class="docutils literal"><span class="pre">WHERE</span></code> clause<a class="headerlink" href="#the-where-clause" title="Permalink to this headline">¶</a></h3> +<p>The <code class="docutils literal"><span class="pre">WHERE</span></code> clause specifies which rows must be queried. It is composed of relations on the columns that are part of +the <code class="docutils literal"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> and/or have a <a class="reference external" href="#createIndexStmt">secondary index</a> defined on them.</p> +<p>Not all relations are allowed in a query. For instance, non-equal relations (where <code class="docutils literal"><span class="pre">IN</span></code> is considered as an equal +relation) on a partition key are not supported (but see the use of the <code class="docutils literal"><span class="pre">TOKEN</span></code> 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 <strong>contiguous</strong> (for the ordering) set of rows. For +instance, given:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">posts</span> <span class="p">(</span> + <span class="n">userid</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">blog_title</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">posted_at</span> <span class="nb">timestamp</span><span class="p">,</span> + <span class="n">entry_title</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">content</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">category</span> <span class="nb">int</span><span class="p">,</span> + <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span> +<span class="p">)</span> +</pre></div> +</div> +<p>The following query is allowed:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">entry_title</span><span class="p">,</span> <span class="n">content</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'john doe'</span> + <span class="k">AND</span> <span class="n">blog_title</span><span class="o">=</span><span class="s1">'John''s Blog'</span> + <span class="k">AND</span> <span class="n">posted_at</span> <span class="o">>=</span> <span class="s1">'2012-01-01'</span> <span class="k">AND</span> <span class="n">posted_at</span> <span class="o"><</span> <span class="s1">'2012-01-31'</span> +</pre></div> +</div> +<p>But the following one is not, as it does not select a contiguous set of rows (and we suppose no secondary indexes are +set):</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="c1">// Needs a blog_title to be set to select ranges of posted_at</span> +<span class="k">SELECT</span> <span class="n">entry_title</span><span class="p">,</span> <span class="n">content</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'john doe'</span> + <span class="k">AND</span> <span class="n">posted_at</span> <span class="o">>=</span> <span class="s1">'2012-01-01'</span> <span class="k">AND</span> <span class="n">posted_at</span> <span class="o"><</span> <span class="s1">'2012-01-31'</span> +</pre></div> +</div> +<p>When specifying relations, the <code class="docutils literal"><span class="pre">TOKEN</span></code> function can be used on the <code class="docutils literal"><span class="pre">PARTITION</span> <span class="pre">KEY</span></code> column to query. In that case, +rows will be selected based on the token of their <code class="docutils literal"><span class="pre">PARTITION_KEY</span></code> 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, +<code class="docutils literal"><span class="pre">token(-1)</span> <span class="pre">></span> <span class="pre">token(0)</span></code> in particular). Example:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="k">token</span><span class="p">(</span><span class="n">userid</span><span class="p">)</span> <span class="o">></span> <span class="k">token</span><span class="p">(</span><span class="s1">'tom'</span><span class="p">)</span> <span class="k">AND</span> <span class="k">token</span><span class="p">(</span><span class="n">userid</span><span class="p">)</span> <span class="o"><</span> <span class="k">token</span><span class="p">(</span><span class="s1">'bob'</span><span class="p">)</span> +</pre></div> +</div> +<p>Moreover, the <code class="docutils literal"><span class="pre">IN</span></code> relation is only allowed on the last column of the partition key and on the last column of the full +primary key.</p> +<p>It is also possible to âgroupâ <code class="docutils literal"><span class="pre">CLUSTERING</span> <span class="pre">COLUMNS</span></code> together in a relation using the tuple notation. For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'john doe'</span> + <span class="k">AND</span> <span class="p">(</span><span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span> <span class="o">></span> <span class="p">(</span><span class="s1">'John''s Blog'</span><span class="p">,</span> <span class="s1">'2012-01-01'</span><span class="p">)</span> +</pre></div> +</div> +<p>will request all rows that sorts after the one having âJohn’s Blogâ as <code class="docutils literal"><span class="pre">blog_tile</span></code> and ‘2012-01-01’ for <code class="docutils literal"><span class="pre">posted_at</span></code> +in the clustering order. In particular, rows having a <code class="docutils literal"><span class="pre">post_at</span> <span class="pre"><=</span> <span class="pre">'2012-01-01'</span></code> will be returned as long as their +<code class="docutils literal"><span class="pre">blog_title</span> <span class="pre">></span> <span class="pre">'John''s</span> <span class="pre">Blog'</span></code>, which would not be the case for:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'john doe'</span> + <span class="k">AND</span> <span class="n">blog_title</span> <span class="o">></span> <span class="s1">'John''s Blog'</span> + <span class="k">AND</span> <span class="n">posted_at</span> <span class="o">></span> <span class="s1">'2012-01-01'</span> +</pre></div> +</div> +<p>The tuple notation may also be used for <code class="docutils literal"><span class="pre">IN</span></code> clauses on clustering columns:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">posts</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'john doe'</span> + <span class="k">AND</span> <span class="p">(</span><span class="n">blog_title</span><span class="p">,</span> <span class="n">posted_at</span><span class="p">)</span> <span class="k">IN</span> <span class="p">((</span><span class="s1">'John''s Blog'</span><span class="p">,</span> <span class="s1">'2012-01-01'</span><span class="p">),</span> <span class="p">(</span><span class="s1">'Extreme Chess'</span><span class="p">,</span> <span class="s1">'2014-06-01'</span><span class="p">))</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">CONTAINS</span></code> operator may only be used on collection columns (lists, sets, and maps). In the case of maps, +<code class="docutils literal"><span class="pre">CONTAINS</span></code> applies to the map values. The <code class="docutils literal"><span class="pre">CONTAINS</span> <span class="pre">KEY</span></code> operator may only be used on map columns and applies to the +map keys.</p> +</div> +<div class="section" id="grouping-results"> +<span id="group-by-clause"></span><h3>Grouping results<a class="headerlink" href="#grouping-results" title="Permalink to this headline">¶</a></h3> +<p>The <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option allows to condense into a single row all selected rows that share the same values for a set +of columns.</p> +<p>Using the <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option, it is only possible to group rows at the partition key level or at a clustering column +level. By consequence, the <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> option only accept as arguments primary key column names in the primary key +order. If a primary key column is restricted by an equality restriction it is not required to be present in the +<code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause.</p> +<p>Aggregate functions will produce a separate value for each group. If no <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause is specified, +aggregates functions will produce a single value for all the rows.</p> +<p>If a column is selected without an aggregate function, in a statement with a <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code>, the first value encounter +in each group will be returned.</p> +</div> +<div class="section" id="ordering-results"> +<span id="ordering-clause"></span><h3>Ordering results<a class="headerlink" href="#ordering-results" title="Permalink to this headline">¶</a></h3> +<p>The <code class="docutils literal"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause allows to select the order of the returned results. It takes as argument a list of column names +along with the order for the column (<code class="docutils literal"><span class="pre">ASC</span></code> for ascendant and <code class="docutils literal"><span class="pre">DESC</span></code> for descendant, omitting the order being +equivalent to <code class="docutils literal"><span class="pre">ASC</span></code>). Currently the possible orderings are limited by the <a class="reference internal" href="ddl.html#clustering-order"><span class="std std-ref">clustering order</span></a> +defined on the table:</p> +<ul class="simple"> +<li>if the table has been defined without any specific <code class="docutils literal"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code>, then then allowed orderings are the order +induced by the clustering columns and the reverse of that one.</li> +<li>otherwise, the orderings allowed are the order of the <code class="docutils literal"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> option and the reversed one.</li> +</ul> +</div> +<div class="section" id="limiting-results"> +<span id="limit-clause"></span><h3>Limiting results<a class="headerlink" href="#limiting-results" title="Permalink to this headline">¶</a></h3> +<p>The <code class="docutils literal"><span class="pre">LIMIT</span></code> option to a <code class="docutils literal"><span class="pre">SELECT</span></code> statement limits the number of rows returned by a query, while the <code class="docutils literal"><span class="pre">PER</span> <span class="pre">PARTITION</span> +<span class="pre">LIMIT</span></code> option limits the number of rows returned for a given partition by the query. Note that both type of limit can +used in the same statement.</p> +</div> +<div class="section" id="allowing-filtering"> +<span id="allow-filtering"></span><h3>Allowing filtering<a class="headerlink" href="#allowing-filtering" title="Permalink to this headline">¶</a></h3> +<p>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 <strong>returned</strong> by the query (which can be controlled through <code class="docutils literal"><span class="pre">LIMIT</span></code>).</p> +<p>The <code class="docutils literal"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> option allows to explicitly allow (some) queries that require filtering. Please note that a +query using <code class="docutils literal"><span class="pre">ALLOW</span> <span class="pre">FILTERING</span></code> may thus have unpredictable performance (for the definition above), i.e. even a query +that selects a handful of records <strong>may</strong> exhibit performance that depends on the total amount of data stored in the +cluster.</p> +<p>For instance, considering the following table holding user profiles with their year of birth (with a secondary index on +it) and country of residence:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="k">users</span> <span class="p">(</span> + <span class="n">username</span> <span class="nb">text</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span> + <span class="n">firstname</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">lastname</span> <span class="nb">text</span><span class="p">,</span> + <span class="n">birth_year</span> <span class="nb">int</span><span class="p">,</span> + <span class="n">country</span> <span class="nb">text</span> +<span class="p">)</span> + +<span class="k">CREATE</span> <span class="k">INDEX</span> <span class="k">ON</span> <span class="k">users</span><span class="p">(</span><span class="n">birth_year</span><span class="p">);</span> +</pre></div> +</div> +<p>Then the following queries are valid:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span><span class="p">;</span> +<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span><span class="p">;</span> +</pre></div> +</div> +<p>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 <code class="docutils literal"><span class="pre">LIMIT</span></code>.</p> +<p>However, the following query will be rejected:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span> <span class="k">AND</span> <span class="n">country</span> <span class="o">=</span> <span class="s1">'FR'</span><span class="p">;</span> +</pre></div> +</div> +<p>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 <code class="docutils literal"><span class="pre">ALLOW</span> +<span class="pre">FILTERING</span></code> and so the following query is valid:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">birth_year</span> <span class="o">=</span> <span class="mf">1981</span> <span class="k">AND</span> <span class="n">country</span> <span class="o">=</span> <span class="s1">'FR'</span> <span class="k">ALLOW</span> <span class="k">FILTERING</span><span class="p">;</span> +</pre></div> +</div> +</div> +</div> +<div class="section" id="insert"> +<span id="insert-statement"></span><h2>INSERT<a class="headerlink" href="#insert" title="Permalink to this headline">¶</a></h2> +<p>Inserting data for a row is done using an <code class="docutils literal"><span class="pre">INSERT</span></code> statement:</p> +<pre> +<strong id="grammar-token-insert_statement">insert_statement</strong> ::= INSERT INTO <a class="reference internal" href="ddl.html#grammar-token-table_name"><code class="xref docutils literal"><span class="pre">table_name</span></code></a> ( <a class="reference internal" href="#grammar-token-names_values"><code class="xref docutils literal"><span class="pre">names_values</span></code></a> | <a class="reference internal" href="#grammar-token-json_clause"><code class="xref docutils literal"><span class="pre">json_clause</span></code></a> ) + [ IF NOT EXISTS ] + [ USING <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> )* ] +<strong id="grammar-token-names_values">names_values </strong> ::= <a class="reference internal" href="#grammar-token-names"><code class="xref docutils literal"><span class="pre">names</span></code></a> VALUES <a class="reference internal" href="types.html#grammar-token-tuple_literal"><code class="xref docutils literal"><span class="pre">tuple_literal</span></code></a> +<strong id="grammar-token-json_clause">json_clause </strong> ::= JSON <a class="reference internal" href="definitions.html#grammar-token-string"><code class="xref docutils literal"><span class="pre">string</span></code></a> [ DEFAULT ( NULL | UNSET ) ] +<strong id="grammar-token-names">names </strong> ::= '(' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> )* ')' +</pre> +<p>For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">NerdMovies</span> <span class="p">(</span><span class="n">movie</span><span class="p">,</span> <span class="n">director</span><span class="p">,</span> <span class="n">main_actor</span><span class="p">,</span> <span class="n">year</span><span class="p">)</span> + <span class="k">VALUES</span> <span class="p">(</span><span class="s1">'Serenity'</span><span class="p">,</span> <span class="s1">'Joss Whedon'</span><span class="p">,</span> <span class="s1">'Nathan Fillion'</span><span class="p">,</span> <span class="mf">2005</span><span class="p">)</span> + <span class="k">USING</span> <span class="k">TTL</span> <span class="mf">86400</span><span class="p">;</span> + +<span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">NerdMovies</span> <span class="k">JSON</span> <span class="s1">'{"movie": "Serenity",</span> +<span class="s1"> "director": "Joss Whedon",</span> +<span class="s1"> "year": 2005}'</span><span class="p">;</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">INSERT</span></code> statement writes one or more columns for a given row in a table. Note that since a row is identified by +its <code class="docutils literal"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>, at least the columns composing it must be specified. The list of columns to insert to must be +supplied when using the <code class="docutils literal"><span class="pre">VALUES</span></code> syntax. When using the <code class="docutils literal"><span class="pre">JSON</span></code> syntax, they are optional. See the +section on <a class="reference internal" href="json.html#cql-json"><span class="std std-ref">JSON support</span></a> for more detail.</p> +<p>Note that unlike in SQL, <code class="docutils literal"><span class="pre">INSERT</span></code> 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.</p> +<p>It is however possible to use the <code class="docutils literal"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> condition to only insert if the row does not exist prior to the +insertion. But please note that using <code class="docutils literal"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> will incur a non negligible performance cost (internally, Paxos +will be used) so this should be used sparingly.</p> +<p>All updates for an <code class="docutils literal"><span class="pre">INSERT</span></code> are applied atomically and in isolation.</p> +<p>Please refer to the <a class="reference internal" href="#update-parameters"><span class="std std-ref">UPDATE</span></a> section for informations on the <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref std std-token docutils literal"><span class="pre">update_parameter</span></code></a>.</p> +<p>Also note that <code class="docutils literal"><span class="pre">INSERT</span></code> does not support counters, while <code class="docutils literal"><span class="pre">UPDATE</span></code> does.</p> +</div> +<div class="section" id="update"> +<span id="update-statement"></span><h2>UPDATE<a class="headerlink" href="#update" title="Permalink to this headline">¶</a></h2> +<p>Updating a row is done using an <code class="docutils literal"><span class="pre">UPDATE</span></code> statement:</p> +<pre> +<strong id="grammar-token-update_statement">update_statement</strong> ::= UPDATE <a class="reference internal" href="ddl.html#grammar-token-table_name"><code class="xref docutils literal"><span class="pre">table_name</span></code></a> + [ USING <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> )* ] + SET <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal"><span class="pre">assignment</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal"><span class="pre">assignment</span></code></a> )* + WHERE <a class="reference internal" href="#grammar-token-where_clause"><code class="xref docutils literal"><span class="pre">where_clause</span></code></a> + [ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal"><span class="pre">condition</span></code></a> )*) ] +<strong id="grammar-token-update_parameter">update_parameter</strong> ::= ( TIMESTAMP | TTL ) ( <a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind_marker"><code class="xref docutils literal"><span class="pre">bind_marker</span></code></a> ) +<strong id="grammar-token-assignment">assignment </strong> ::= <a class="reference internal" href="#grammar-token-simple_selection"><code class="xref docutils literal"><span class="pre">simple_selection</span></code></a> '=' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> + | <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> ( '+' | '-' ) <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> + | <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="types.html#grammar-token-list_literal"><code class="xref docutils literal"><span class="pre">list_literal</span></code></a> '+' <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> +<strong id="grammar-token-simple_selection">simple_selection</strong> ::= <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> + | <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> '[' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> ']' + | <a class="reference internal" href="ddl.html#grammar-token-column_name"><code class="xref docutils literal"><span class="pre">column_name</span></code></a> '.' `field_name +<strong id="grammar-token-condition">condition </strong> ::= <a class="reference internal" href="#grammar-token-simple_selection"><code class="xref docutils literal"><span class="pre">simple_selection</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal"><span class="pre">term</span></code></a> +</pre> +<p>For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">UPDATE</span> <span class="n">NerdMovies</span> <span class="k">USING</span> <span class="k">TTL</span> <span class="mf">400</span> + <span class="k">SET</span> <span class="n">director</span> <span class="o">=</span> <span class="s1">'Joss Whedon'</span><span class="p">,</span> + <span class="n">main_actor</span> <span class="o">=</span> <span class="s1">'Nathan Fillion'</span><span class="p">,</span> + <span class="n">year</span> <span class="o">=</span> <span class="mf">2005</span> + <span class="k">WHERE</span> <span class="n">movie</span> <span class="o">=</span> <span class="s1">'Serenity'</span><span class="p">;</span> + +<span class="k">UPDATE</span> <span class="n">UserActions</span> + <span class="k">SET</span> <span class="n">total</span> <span class="o">=</span> <span class="n">total</span> <span class="o">+</span> <span class="mf">2</span> + <span class="k">WHERE</span> <span class="k">user</span> <span class="o">=</span> <span class="m">B70DE1D0-9908-4AE3-BE34-5573E5B09F14</span> + <span class="k">AND</span> <span class="n">action</span> <span class="o">=</span> <span class="s1">'click'</span><span class="p">;</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">UPDATE</span></code> statement writes one or more columns for a given row in a table. The <a class="reference internal" href="#grammar-token-where_clause"><code class="xref std std-token docutils literal"><span class="pre">where_clause</span></code></a> is used to +select the row to update and must include all columns composing the <code class="docutils literal"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>. Non primary key columns are then +set using the <code class="docutils literal"><span class="pre">SET</span></code> keyword.</p> +<p>Note that unlike in SQL, <code class="docutils literal"><span class="pre">UPDATE</span></code> does not check the prior existence of the row by default (except through <code class="docutils literal"><span class="pre">IF</span></code>, see +below): the row is created if none existed before, and updated otherwise. Furthermore, there are no means to know +whether a creation or update occurred.</p> +<p>It is however possible to use the conditions on some columns through <code class="docutils literal"><span class="pre">IF</span></code>, in which case the row will not be updated +unless the conditions are met. But, please note that using <code class="docutils literal"><span class="pre">IF</span></code> conditions will incur a non-negligible performance +cost (internally, Paxos will be used) so this should be used sparingly.</p> +<p>In an <code class="docutils literal"><span class="pre">UPDATE</span></code> statement, all updates within the same partition key are applied atomically and in isolation.</p> +<p>Regarding the <a class="reference internal" href="#grammar-token-assignment"><code class="xref std std-token docutils literal"><span class="pre">assignment</span></code></a>:</p> +<ul class="simple"> +<li><code class="docutils literal"><span class="pre">c</span> <span class="pre">=</span> <span class="pre">c</span> <span class="pre">+</span> <span class="pre">3</span></code> is used to increment/decrement counters. The column name after the ‘=’ sign <strong>must</strong> be the same than +the one before the ‘=’ sign. Note that increment/decrement is only allowed on counters, and are the <em>only</em> update +operations allowed on counters. See the section on <a class="reference internal" href="types.html#counters"><span class="std std-ref">counters</span></a> for details.</li> +<li><code class="docutils literal"><span class="pre">id</span> <span class="pre">=</span> <span class="pre">id</span> <span class="pre">+</span> <span class="pre"><some-collection></span></code> and <code class="docutils literal"><span class="pre">id[value1]</span> <span class="pre">=</span> <span class="pre">value2</span></code> are for collections, see the <a class="reference internal" href="types.html#collections"><span class="std std-ref">relevant section</span></a> for details.</li> +<li><code class="docutils literal"><span class="pre">id.field</span> <span class="pre">=</span> <span class="pre">3</span></code> is for setting the value of a field on a non-frozen user-defined types. see the <a class="reference internal" href="types.html#udts"><span class="std std-ref">relevant section</span></a> for details.</li> +</ul> +<div class="section" id="update-parameters"> +<span id="id3"></span><h3>Update parameters<a class="headerlink" href="#update-parameters" title="Permalink to this headline">¶</a></h3> +<p>The <code class="docutils literal"><span class="pre">UPDATE</span></code>, <code class="docutils literal"><span class="pre">INSERT</span></code> (and <code class="docutils literal"><span class="pre">DELETE</span></code> and <code class="docutils literal"><span class="pre">BATCH</span></code> for the <code class="docutils literal"><span class="pre">TIMESTAMP</span></code>) statements support the following +parameters:</p> +<ul class="simple"> +<li><code class="docutils literal"><span class="pre">TIMESTAMP</span></code>: 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.</li> +<li><code class="docutils literal"><span class="pre">TTL</span></code>: specifies an optional Time To Live (in seconds) for the inserted values. If set, the inserted values are +automatically removed from the database after the specified time. Note that the TTL concerns the inserted values, not +the columns themselves. This means that any subsequent update of the column will also reset the TTL (to whatever TTL +is specified in that update). By default, values never expire. A TTL of 0 is equivalent to no TTL. If the table has a +default_time_to_live, a TTL of 0 will remove the TTL for the inserted or updated values. A TTL of <code class="docutils literal"><span class="pre">null</span></code> is equivalent +to inserting with a TTL of 0.</li> +</ul> +</div> +</div> +<div class="section" id="delete"> +<span id="delete-statement"></span><h2>DELETE<a class="headerlink" href="#delete" title="Permalink to this headline">¶</a></h2> +<p>Deleting rows or parts of rows uses the <code class="docutils literal"><span class="pre">DELETE</span></code> statement:</p> +<pre> +<strong id="grammar-token-delete_statement">delete_statement</strong> ::= DELETE [ <a class="reference internal" href="#grammar-token-simple_selection"><code class="xref docutils literal"><span class="pre">simple_selection</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-simple_selection"><code class="xref docutils literal"><span class="pre">simple_selection</span></code></a> ) ] + FROM <a class="reference internal" href="ddl.html#grammar-token-table_name"><code class="xref docutils literal"><span class="pre">table_name</span></code></a> + [ USING <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> )* ] + WHERE <a class="reference internal" href="#grammar-token-where_clause"><code class="xref docutils literal"><span class="pre">where_clause</span></code></a> + [ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal"><span class="pre">condition</span></code></a> )*) ] +</pre> +<p>For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">DELETE</span> <span class="k">FROM</span> <span class="n">NerdMovies</span> <span class="k">USING</span> <span class="nb">TIMESTAMP</span> <span class="mf">1240003134</span> + <span class="k">WHERE</span> <span class="n">movie</span> <span class="o">=</span> <span class="s1">'Serenity'</span><span class="p">;</span> + +<span class="k">DELETE</span> <span class="n">phone</span> <span class="k">FROM</span> <span class="k">Users</span> + <span class="k">WHERE</span> <span class="n">userid</span> <span class="k">IN</span> <span class="p">(</span><span class="m">C73DE1D3-AF08-40F3-B124-3FF3E5109F22</span><span class="p">,</span> <span class="m">B70DE1D0-9908-4AE3-BE34-5573E5B09F14</span><span class="p">);</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">DELETE</span></code> statement deletes columns and rows. If column names are provided directly after the <code class="docutils literal"><span class="pre">DELETE</span></code> keyword, +only those columns are deleted from the row indicated by the <code class="docutils literal"><span class="pre">WHERE</span></code> clause. Otherwise, whole rows are removed.</p> +<p>The <code class="docutils literal"><span class="pre">WHERE</span></code> clause specifies which rows are to be deleted. Multiple rows may be deleted with one statement by using an +<code class="docutils literal"><span class="pre">IN</span></code> operator. A range of rows may be deleted using an inequality operator (such as <code class="docutils literal"><span class="pre">>=</span></code>).</p> +<p><code class="docutils literal"><span class="pre">DELETE</span></code> supports the <code class="docutils literal"><span class="pre">TIMESTAMP</span></code> option with the same semantics as in <a class="reference internal" href="#update-parameters"><span class="std std-ref">updates</span></a>.</p> +<p>In a <code class="docutils literal"><span class="pre">DELETE</span></code> statement, all deletions within the same partition key are applied atomically and in isolation.</p> +<p>A <code class="docutils literal"><span class="pre">DELETE</span></code> operation can be conditional through the use of an <code class="docutils literal"><span class="pre">IF</span></code> clause, similar to <code class="docutils literal"><span class="pre">UPDATE</span></code> and <code class="docutils literal"><span class="pre">INSERT</span></code> +statements. However, as with <code class="docutils literal"><span class="pre">INSERT</span></code> and <code class="docutils literal"><span class="pre">UPDATE</span></code> statements, this will incur a non-negligible performance cost +(internally, Paxos will be used) and so should be used sparingly.</p> +</div> +<div class="section" id="batch"> +<span id="batch-statement"></span><h2>BATCH<a class="headerlink" href="#batch" title="Permalink to this headline">¶</a></h2> +<p>Multiple <code class="docutils literal"><span class="pre">INSERT</span></code>, <code class="docutils literal"><span class="pre">UPDATE</span></code> and <code class="docutils literal"><span class="pre">DELETE</span></code> can be executed in a single statement by grouping them through a +<code class="docutils literal"><span class="pre">BATCH</span></code> statement:</p> +<pre> +<strong id="grammar-token-batch_statement">batch_statement </strong> ::= BEGIN [ UNLOGGED | COUNTER ] BATCH + [ USING <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update_parameter"><code class="xref docutils literal"><span class="pre">update_parameter</span></code></a> )* ] + <a class="reference internal" href="#grammar-token-modification_statement"><code class="xref docutils literal"><span class="pre">modification_statement</span></code></a> ( ';' <a class="reference internal" href="#grammar-token-modification_statement"><code class="xref docutils literal"><span class="pre">modification_statement</span></code></a> )* + APPLY BATCH +<strong id="grammar-token-modification_statement">modification_statement</strong> ::= <a class="reference internal" href="#grammar-token-insert_statement"><code class="xref docutils literal"><span class="pre">insert_statement</span></code></a> | <a class="reference internal" href="#grammar-token-update_statement"><code class="xref docutils literal"><span class="pre">update_statement</span></code></a> | <a class="reference internal" href="#grammar-token-delete_statement"><code class="xref docutils literal"><span class="pre">delete_statement</span></code></a> +</pre> +<p>For instance:</p> +<div class="highlight-cql"><div class="highlight"><pre><span></span><span class="k">BEGIN</span> <span class="k">BATCH</span> + <span class="k">INSERT</span> <span class="k">INTO</span> <span class="k">users</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="k">password</span><span class="p">,</span> <span class="n">name</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">'user2'</span><span class="p">,</span> <span class="s1">'ch@ngem3b'</span><span class="p">,</span> <span class="s1">'second user'</span><span class="p">);</span> + <span class="k">UPDATE</span> <span class="k">users</span> <span class="k">SET</span> <span class="k">password</span> <span class="o">=</span> <span class="s1">'ps22dhds'</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'user3'</span><span class="p">;</span> + <span class="k">INSERT</span> <span class="k">INTO</span> <span class="k">users</span> <span class="p">(</span><span class="n">userid</span><span class="p">,</span> <span class="k">password</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="s1">'user4'</span><span class="p">,</span> <span class="s1">'ch@ngem3c'</span><span class="p">);</span> + <span class="k">DELETE</span> <span class="n">name</span> <span class="k">FROM</span> <span class="k">users</span> <span class="k">WHERE</span> <span class="n">userid</span> <span class="o">=</span> <span class="s1">'user1'</span><span class="p">;</span> +<span class="k">APPLY</span> <span class="k">BATCH</span><span class="p">;</span> +</pre></div> +</div> +<p>The <code class="docutils literal"><span class="pre">BATCH</span></code> statement group multiple modification statements (insertions/updates and deletions) into a single +statement. It serves several purposes:</p> +<ul class="simple"> +<li>It saves network round-trips between the client and the server (and sometimes between the server coordinator and the +replicas) when batching multiple updates.</li> +<li>All updates in a <code class="docutils literal"><span class="pre">BATCH</span></code> belonging to a given partition key are performed in isolation.</li> +<li>By default, all operations in the batch are performed as <em>logged</em>, to ensure all mutations eventually complete (or +none will). See the notes on <a class="reference internal" href="#unlogged-batches"><span class="std std-ref">UNLOGGED batches</span></a> for more details.</li> +</ul> +<p>Note that:</p> +<ul class="simple"> +<li><code class="docutils literal"><span class="pre">BATCH</span></code> statements may only contain <code class="docutils literal"><span class="pre">UPDATE</span></code>, <code class="docutils literal"><span class="pre">INSERT</span></code> and <code class="docutils literal"><span class="pre">DELETE</span></code> statements (not other batches for instance).</li> +<li>Batches are <em>not</em> a full analogue for SQL transactions.</li> +<li>If a timestamp is not specified for each operation, then all operations will be applied with the same timestamp +(either one generated automatically, or the timestamp provided at the batch level). Due to Cassandra’s conflict +resolution procedure in the case of <a class="reference external" href="http://wiki.apache.org/cassandra/FAQ#clocktie";>timestamp ties</a>, operations may +be applied in an order that is different from the order they are listed in the <code class="docutils literal"><span class="pre">BATCH</span></code> statement. To force a +particular operation ordering, you must specify per-operation timestamps.</li> +</ul> +<div class="section" id="unlogged-batches"> +<span id="id4"></span><h3><code class="docutils literal"><span class="pre">UNLOGGED</span></code> batches<a class="headerlink" href="#unlogged-batches" title="Permalink to this headline">¶</a></h3> +<p>By default, Cassandra uses a batch log to ensure all operations in a batch eventually complete or none will (note +however that operations are only isolated within a single partition).</p> +<p>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 <code class="docutils literal"><span class="pre">UNLOGGED</span></code> option. If the <code class="docutils literal"><span class="pre">UNLOGGED</span></code> option is +used, a failed batch might leave the patch only partly applied.</p> +</div> +<div class="section" id="counter-batches"> +<h3><code class="docutils literal"><span class="pre">COUNTER</span></code> batches<a class="headerlink" href="#counter-batches" title="Permalink to this headline">¶</a></h3> +<p>Use the <code class="docutils literal"><span class="pre">COUNTER</span></code> option for batched counter updates. Unlike other +updates in Cassandra, counter updates are not idempotent.</p> +</div> +</div> +</div> + + + + + <div class="doc-prev-next-links" role="navigation" aria-label="footer navigation"> + + <a href="indexes.html" class="btn btn-default pull-right " role="button" title="Secondary Indexes" accesskey="n">Next <span class="glyphicon glyphicon-circle-arrow-right" aria-hidden="true"></span></a> + + + <a href="ddl.html" class="btn btn-default" role="button" title="Data Definition" accesskey="p"><span class="glyphicon glyphicon-circle-arrow-left" aria-hidden="true"></span> Previous</a> + + </div> + + </div> + </div> + </div> + <div class="col-md-2"> + </div> + </div> +</div> \ No newline at end of file
