| --- |
| 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 v4.0-alpha4" 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: ".txt" |
| }; |
| </script> |
| ' |
| |
| --- |
| <div class="container-fluid"> |
| <div class="row"> |
| <div class="col-md-3"> |
| <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="../new/index.html">New Features in Apache Cassandra 4.0</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../architecture/index.html">Architecture</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="operators.html">Arithmetic Operators</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="../data_modeling/index.html">Data Modeling</a></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">Contributing to Cassandra</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="../plugins/index.html">Third-Party Plugins</a></li> |
| <li class="toctree-l1"><a class="reference internal" href="../bugs.html">Reporting Bugs</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="content-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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">table_name</span></code></a> |
| [ WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><span class="pre">bind_marker</span></code></a>) ] |
| [ LIMIT (<a class="reference internal" href="definitions.html#grammar-token-integer"><code class="xref docutils literal notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><span class="pre">identifier</span></code></a> ] ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> [ AS <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> |
| | <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> |
| | CAST '(' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> AS <a class="reference internal" href="types.html#grammar-token-cql-type"><code class="xref docutils literal notranslate"><span class="pre">cql_type</span></code></a> ')' |
| | <a class="reference internal" href="functions.html#grammar-token-function-name"><code class="xref docutils literal notranslate"><span class="pre">function_name</span></code></a> '(' [ <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><span class="pre">selector</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-selector"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">relation</span></code></a> ( AND <a class="reference internal" href="#grammar-token-relation"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> |
| '(' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="types.html#grammar-token-tuple-literal"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><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 notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> [ ASC | DESC ] )* |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">WHERE</span></code> |
| clause, not in the <code class="docutils literal notranslate"><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 notranslate"><span class="pre">WRITETIME</span></code> and <code class="docutils literal notranslate"><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 notranslate"><span class="pre">WRITETIME</span></code> and <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">TOKEN</span></code> function can be used on the <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">blog_tile</span></code> and ‘2012-01-01’ for <code class="docutils literal notranslate"><span class="pre">posted_at</span></code> |
| in the clustering order. In particular, rows having a <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">IN</span></code> clauses on clustering columns:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><span class="pre">CONTAINS</span></code> applies to the map values. The <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">ASC</span></code> for ascendant and <code class="docutils literal notranslate"><span class="pre">DESC</span></code> for descendant, omitting the order being |
| equivalent to <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">LIMIT</span></code> option to a <code class="docutils literal notranslate"><span class="pre">SELECT</span></code> statement limits the number of rows returned by a query, while the <code class="docutils literal notranslate"><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 notranslate"><span class="pre">LIMIT</span></code>).</p> |
| <p>The <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">LIMIT</span></code>.</p> |
| <p>However, the following query will be rejected:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><span class="pre">ALLOW</span> |
| <span class="pre">FILTERING</span></code> and so the following query is valid:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><span class="pre">table_name</span></code></a> ( <a class="reference internal" href="#grammar-token-names-values"><code class="xref docutils literal notranslate"><span class="pre">names_values</span></code></a> | <a class="reference internal" href="#grammar-token-json-clause"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">names</span></code></a> VALUES <a class="reference internal" href="types.html#grammar-token-tuple-literal"><code class="xref docutils literal notranslate"><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 notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">VALUES</span></code> syntax. When using the <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">update_parameter</span></code></a>.</p> |
| <p>Also note that <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> does not support counters, while <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><span class="pre">table_name</span></code></a> |
| [ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ] |
| SET <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal notranslate"><span class="pre">assignment</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-assignment"><code class="xref docutils literal notranslate"><span class="pre">assignment</span></code></a> )* |
| WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><span class="pre">where_clause</span></code></a> |
| [ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">integer</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-bind-marker"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">simple_selection</span></code></a> '=' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> |
| | <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( '+' | '-' ) <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> |
| | <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '=' <a class="reference internal" href="types.html#grammar-token-list-literal"><code class="xref docutils literal notranslate"><span class="pre">list_literal</span></code></a> '+' <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">column_name</span></code></a> |
| | <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> '[' <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> ']' |
| | <a class="reference internal" href="ddl.html#grammar-token-column-name"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">simple_selection</span></code></a> <a class="reference internal" href="#grammar-token-operator"><code class="xref docutils literal notranslate"><span class="pre">operator</span></code></a> <a class="reference internal" href="definitions.html#grammar-token-term"><code class="xref docutils literal notranslate"><span class="pre">term</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>. Non primary key columns are then |
| set using the <code class="docutils literal notranslate"><span class="pre">SET</span></code> keyword.</p> |
| <p>Note that unlike in SQL, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> does not check the prior existence of the row by default (except through <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">assignment</span></code></a>:</p> |
| <ul class="simple"> |
| <li><code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">UPDATE</span></code>, <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> (and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> and <code class="docutils literal notranslate"><span class="pre">BATCH</span></code> for the <code class="docutils literal notranslate"><span class="pre">TIMESTAMP</span></code>) statements support the following |
| parameters:</p> |
| <ul class="simple"> |
| <li><code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">simple_selection</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-simple-selection"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">table_name</span></code></a> |
| [ USING <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ] |
| WHERE <a class="reference internal" href="#grammar-token-where-clause"><code class="xref docutils literal notranslate"><span class="pre">where_clause</span></code></a> |
| [ IF ( EXISTS | <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> ( AND <a class="reference internal" href="#grammar-token-condition"><code class="xref docutils literal notranslate"><span class="pre">condition</span></code></a> )*) ] |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><span class="pre">DELETE</span></code> statement deletes columns and rows. If column names are provided directly after the <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> keyword, |
| only those columns are deleted from the row indicated by the <code class="docutils literal notranslate"><span class="pre">WHERE</span></code> clause. Otherwise, whole rows are removed.</p> |
| <p>The <code class="docutils literal notranslate"><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 notranslate"><span class="pre">IN</span></code> operator. A range of rows may be deleted using an inequality operator (such as <code class="docutils literal notranslate"><span class="pre">>=</span></code>).</p> |
| <p><code class="docutils literal notranslate"><span class="pre">DELETE</span></code> supports the <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><span class="pre">DELETE</span></code> operation can be conditional through the use of an <code class="docutils literal notranslate"><span class="pre">IF</span></code> clause, similar to <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> and <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> |
| statements. However, as with <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> and <code class="docutils literal notranslate"><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 notranslate"><span class="pre">INSERT</span></code>, <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code> and <code class="docutils literal notranslate"><span class="pre">DELETE</span></code> can be executed in a single statement by grouping them through a |
| <code class="docutils literal notranslate"><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 notranslate"><span class="pre">update_parameter</span></code></a> ( AND <a class="reference internal" href="#grammar-token-update-parameter"><code class="xref docutils literal notranslate"><span class="pre">update_parameter</span></code></a> )* ] |
| <a class="reference internal" href="#grammar-token-modification-statement"><code class="xref docutils literal notranslate"><span class="pre">modification_statement</span></code></a> ( ';' <a class="reference internal" href="#grammar-token-modification-statement"><code class="xref docutils literal notranslate"><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 notranslate"><span class="pre">insert_statement</span></code></a> | <a class="reference internal" href="#grammar-token-update-statement"><code class="xref docutils literal notranslate"><span class="pre">update_statement</span></code></a> | <a class="reference internal" href="#grammar-token-delete-statement"><code class="xref docutils literal notranslate"><span class="pre">delete_statement</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><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 notranslate"><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 notranslate"><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 notranslate"><span class="pre">BATCH</span></code> statements may only contain <code class="docutils literal notranslate"><span class="pre">UPDATE</span></code>, <code class="docutils literal notranslate"><span class="pre">INSERT</span></code> and <code class="docutils literal notranslate"><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 notranslate"><span class="pre">BATCH</span></code> statement. To force a |
| particular operation ordering, you must specify per-operation timestamps.</li> |
| <li>A LOGGED batch to a single partition will be converted to an UNLOGGED batch as an optimization.</li> |
| </ul> |
| <div class="section" id="unlogged-batches"> |
| <span id="id4"></span><h3><code class="docutils literal notranslate"><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 notranslate"><span class="pre">UNLOGGED</span></code> option. If the <code class="docutils literal notranslate"><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 notranslate"><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 notranslate"><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> |
| </div> |