| --- |
| layout: docpage |
| |
| title: "Documentation" |
| |
| is_homepage: false |
| is_sphinx_doc: true |
| |
| doc-parent: "The Cassandra Query Language (CQL)" |
| |
| doc-title: "Data Definition" |
| 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="Data Manipulation" href="dml.html"/> |
| <link rel="prev" title="Data Types" href="types.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 current"><a class="current reference internal" href="#">Data Definition</a><ul> |
| <li class="toctree-l3"><a class="reference internal" href="#common-definitions">Common definitions</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#create-keyspace">CREATE KEYSPACE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#use">USE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#alter-keyspace">ALTER KEYSPACE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#drop-keyspace">DROP KEYSPACE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#create-table">CREATE TABLE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#alter-table">ALTER TABLE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#drop-table">DROP TABLE</a></li> |
| <li class="toctree-l3"><a class="reference internal" href="#truncate">TRUNCATE</a></li> |
| </ul> |
| </li> |
| <li class="toctree-l2"><a class="reference internal" href="dml.html">Data Manipulation</a></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-definition"> |
| <span id="id1"></span><h1>Data Definition<a class="headerlink" href="#data-definition" title="Permalink to this headline">¶</a></h1> |
| <p>CQL stores data in <em>tables</em>, whose schema defines the layout of said data in the table, and those tables are grouped in |
| <em>keyspaces</em>. A keyspace defines a number of options that applies to all the tables it contains, most prominently of |
| which is the <a class="reference internal" href="#replication-strategy"><span class="std std-ref">replication strategy</span></a> used by the keyspace. It is generally encouraged to use |
| one keyspace by <em>application</em>, and thus many cluster may define only one keyspace.</p> |
| <p>This section describes the statements used to create, modify, and remove those keyspace and tables.</p> |
| <div class="section" id="common-definitions"> |
| <h2>Common definitions<a class="headerlink" href="#common-definitions" title="Permalink to this headline">¶</a></h2> |
| <p>The names of the keyspaces and tables are defined by the following grammar:</p> |
| <pre> |
| <strong id="grammar-token-keyspace-name">keyspace_name</strong> ::= <a class="reference internal" href="#grammar-token-name"><code class="xref docutils literal notranslate"><span class="pre">name</span></code></a> |
| <strong id="grammar-token-table-name">table_name </strong> ::= [ <a class="reference internal" href="#grammar-token-keyspace-name"><code class="xref docutils literal notranslate"><span class="pre">keyspace_name</span></code></a> '.' ] <a class="reference internal" href="#grammar-token-name"><code class="xref docutils literal notranslate"><span class="pre">name</span></code></a> |
| <strong id="grammar-token-name">name </strong> ::= <a class="reference internal" href="#grammar-token-unquoted-name"><code class="xref docutils literal notranslate"><span class="pre">unquoted_name</span></code></a> | <a class="reference internal" href="#grammar-token-quoted-name"><code class="xref docutils literal notranslate"><span class="pre">quoted_name</span></code></a> |
| <strong id="grammar-token-unquoted-name">unquoted_name</strong> ::= re('[a-zA-Z_0-9]{1, 48}') |
| <strong id="grammar-token-quoted-name">quoted_name </strong> ::= '"' <a class="reference internal" href="#grammar-token-unquoted-name"><code class="xref docutils literal notranslate"><span class="pre">unquoted_name</span></code></a> '"' |
| </pre> |
| <p>Both keyspace and table name should be comprised of only alphanumeric characters, cannot be empty and are limited in |
| size to 48 characters (that limit exists mostly to avoid filenames (which may include the keyspace and table name) to go |
| over the limits of certain file systems). By default, keyspace and table names are case insensitive (<code class="docutils literal notranslate"><span class="pre">myTable</span></code> is |
| equivalent to <code class="docutils literal notranslate"><span class="pre">mytable</span></code>) but case sensitivity can be forced by using double-quotes (<code class="docutils literal notranslate"><span class="pre">"myTable"</span></code> is different from |
| <code class="docutils literal notranslate"><span class="pre">mytable</span></code>).</p> |
| <p>Further, a table is always part of a keyspace and a table name can be provided fully-qualified by the keyspace it is |
| part of. If is is not fully-qualified, the table is assumed to be in the <em>current</em> keyspace (see <a class="reference internal" href="#use-statement"><span class="std std-ref">USE statement</span></a>).</p> |
| <p>Further, the valid names for columns is simply defined as:</p> |
| <pre> |
| <strong id="grammar-token-column-name">column_name</strong> ::= <a class="reference internal" href="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><span class="pre">identifier</span></code></a> |
| </pre> |
| <p>We also define the notion of statement options for use in the following section:</p> |
| <pre> |
| <strong id="grammar-token-options">options</strong> ::= <a class="reference internal" href="#grammar-token-option"><code class="xref docutils literal notranslate"><span class="pre">option</span></code></a> ( AND <a class="reference internal" href="#grammar-token-option"><code class="xref docutils literal notranslate"><span class="pre">option</span></code></a> )* |
| <strong id="grammar-token-option">option </strong> ::= <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="definitions.html#grammar-token-identifier"><code class="xref docutils literal notranslate"><span class="pre">identifier</span></code></a> | <a class="reference internal" href="definitions.html#grammar-token-constant"><code class="xref docutils literal notranslate"><span class="pre">constant</span></code></a> | <a class="reference internal" href="types.html#grammar-token-map-literal"><code class="xref docutils literal notranslate"><span class="pre">map_literal</span></code></a> ) |
| </pre> |
| </div> |
| <div class="section" id="create-keyspace"> |
| <span id="create-keyspace-statement"></span><h2>CREATE KEYSPACE<a class="headerlink" href="#create-keyspace" title="Permalink to this headline">¶</a></h2> |
| <p>A keyspace is created using a <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">KEYSPACE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-create-keyspace-statement">create_keyspace_statement</strong> ::= CREATE KEYSPACE [ IF NOT EXISTS ] <a class="reference internal" href="#grammar-token-keyspace-name"><code class="xref docutils literal notranslate"><span class="pre">keyspace_name</span></code></a> WITH <a class="reference internal" href="#grammar-token-options"><code class="xref docutils literal notranslate"><span class="pre">options</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excelsior</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'SimpleStrategy'</span><span class="p">,</span> <span class="s1">'replication_factor'</span> <span class="p">:</span> <span class="mf">3</span><span class="p">};</span> |
| |
| <span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'DC1'</span> <span class="p">:</span> <span class="mf">1</span><span class="p">,</span> <span class="s1">'DC2'</span> <span class="p">:</span> <span class="mf">3</span><span class="p">}</span> |
| <span class="k">AND</span> <span class="n">durable_writes</span> <span class="o">=</span> <span class="n">false</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>Attempting to create a keyspace that already exists will return an error unless the <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> option is used. If |
| it is used, the statement will be a no-op if the keyspace already exists.</p> |
| <p>The supported <code class="docutils literal notranslate"><span class="pre">options</span></code> are:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="16%" /> |
| <col width="9%" /> |
| <col width="9%" /> |
| <col width="8%" /> |
| <col width="58%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">name</th> |
| <th class="head">kind</th> |
| <th class="head">mandatory</th> |
| <th class="head">default</th> |
| <th class="head">description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">replication</span></code></td> |
| <td><em>map</em></td> |
| <td>yes</td> |
| <td> </td> |
| <td>The replication strategy and options to use for the keyspace (see |
| details below).</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">durable_writes</span></code></td> |
| <td><em>simple</em></td> |
| <td>no</td> |
| <td>true</td> |
| <td>Whether to use the commit log for updates on this keyspace |
| (disable this option at your own risk!).</td> |
| </tr> |
| </tbody> |
| </table> |
| <p>The <code class="docutils literal notranslate"><span class="pre">replication</span></code> property is mandatory and must at least contains the <code class="docutils literal notranslate"><span class="pre">'class'</span></code> sub-option which defines the |
| <a class="reference internal" href="#replication-strategy"><span class="std std-ref">replication strategy</span></a> class to use. The rest of the sub-options depends on what replication |
| strategy is used. By default, Cassandra support the following <code class="docutils literal notranslate"><span class="pre">'class'</span></code>:</p> |
| <div class="section" id="simplestrategy"> |
| <span id="replication-strategy"></span><h3><code class="docutils literal notranslate"><span class="pre">SimpleStrategy</span></code><a class="headerlink" href="#simplestrategy" title="Permalink to this headline">¶</a></h3> |
| <p>A simple strategy that defines a replication factor for data to be spread |
| across the entire cluster. This is generally not a wise choice for production |
| because it does not respect datacenter layouts and can lead to wildly varying |
| query latency. For a production ready strategy, see |
| <code class="docutils literal notranslate"><span class="pre">NetworkTopologyStrategy</span></code>. <code class="docutils literal notranslate"><span class="pre">SimpleStrategy</span></code> supports a single mandatory argument:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="30%" /> |
| <col width="7%" /> |
| <col width="8%" /> |
| <col width="54%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">sub-option</th> |
| <th class="head">type</th> |
| <th class="head">since</th> |
| <th class="head">description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">'replication_factor'</span></code></td> |
| <td>int</td> |
| <td>all</td> |
| <td>The number of replicas to store per range</td> |
| </tr> |
| </tbody> |
| </table> |
| </div> |
| <div class="section" id="networktopologystrategy"> |
| <h3><code class="docutils literal notranslate"><span class="pre">NetworkTopologyStrategy</span></code><a class="headerlink" href="#networktopologystrategy" title="Permalink to this headline">¶</a></h3> |
| <p>A production ready replication strategy that allows to set the replication |
| factor independently for each data-center. The rest of the sub-options are |
| key-value pairs where a key is a data-center name and its value is the |
| associated replication factor. Options:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="39%" /> |
| <col width="6%" /> |
| <col width="6%" /> |
| <col width="48%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">sub-option</th> |
| <th class="head">type</th> |
| <th class="head">since</th> |
| <th class="head">description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">'<datacenter>'</span></code></td> |
| <td>int</td> |
| <td>all</td> |
| <td>The number of replicas to store per range in |
| the provided datacenter.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">'replication_factor'</span></code></td> |
| <td>int</td> |
| <td>4.0</td> |
| <td>The number of replicas to use as a default |
| per datacenter if not specifically provided. |
| Note that this always defers to existing |
| definitions or explicit datacenter settings. |
| For example, to have three replicas per |
| datacenter, supply this with a value of 3.</td> |
| </tr> |
| </tbody> |
| </table> |
| <p>Note that when <code class="docutils literal notranslate"><span class="pre">ALTER</span></code> ing keyspaces and supplying <code class="docutils literal notranslate"><span class="pre">replication_factor</span></code>, |
| auto-expansion will only <em>add</em> new datacenters for safety, it will not alter |
| existing datacenters or remove any even if they are no longer in the cluster. |
| If you want to remove datacenters while still supplying <code class="docutils literal notranslate"><span class="pre">replication_factor</span></code>, |
| explicitly zero out the datacenter you want to have zero replicas.</p> |
| <p>An example of auto-expanding datacenters with two datacenters: <code class="docutils literal notranslate"><span class="pre">DC1</span></code> and <code class="docutils literal notranslate"><span class="pre">DC2</span></code>:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'replication_factor'</span> <span class="p">:</span> <span class="mf">3</span><span class="p">}</span> |
| |
| <span class="k">DESCRIBE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'DC1'</span><span class="p">:</span> <span class="s1">'3'</span><span class="p">,</span> <span class="s1">'DC2'</span><span class="p">:</span> <span class="s1">'3'</span><span class="p">}</span> <span class="k">AND</span> <span class="n">durable_writes</span> <span class="o">=</span> <span class="n">true</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>An example of auto-expanding and overriding a datacenter:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'replication_factor'</span> <span class="p">:</span> <span class="mf">3</span><span class="p">,</span> <span class="s1">'DC2'</span><span class="p">:</span> <span class="mf">2</span><span class="p">}</span> |
| |
| <span class="k">DESCRIBE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'DC1'</span><span class="p">:</span> <span class="s1">'3'</span><span class="p">,</span> <span class="s1">'DC2'</span><span class="p">:</span> <span class="s1">'2'</span><span class="p">}</span> <span class="k">AND</span> <span class="n">durable_writes</span> <span class="o">=</span> <span class="n">true</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>An example that excludes a datacenter while using <code class="docutils literal notranslate"><span class="pre">replication_factor</span></code>:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'replication_factor'</span> <span class="p">:</span> <span class="mf">3</span><span class="p">,</span> <span class="s1">'DC2'</span><span class="p">:</span> <span class="mf">0</span><span class="p">}</span> <span class="p">;</span> |
| |
| <span class="k">DESCRIBE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> |
| <span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">excalibur</span> <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'DC1'</span><span class="p">:</span> <span class="s1">'3'</span><span class="p">}</span> <span class="k">AND</span> <span class="n">durable_writes</span> <span class="o">=</span> <span class="n">true</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>If transient replication has been enabled, transient replicas can be configured for both |
| <code class="docutils literal notranslate"><span class="pre">SimpleStrategy</span></code> and <code class="docutils literal notranslate"><span class="pre">NetworkTopologyStrategy</span></code> by defining replication factors in the format <code class="docutils literal notranslate"><span class="pre">'<total_replicas>/<transient_replicas>'</span></code></p> |
| <p>For instance, this keyspace will have 3 replicas in DC1, 1 of which is transient, and 5 replicas in DC2, 2 of which are transient:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">CREATE</span> <span class="k">KEYSPACE</span> <span class="n">some_keysopace</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'NetworkTopologyStrategy'</span><span class="p">,</span> <span class="s1">'DC1'</span> <span class="p">:</span> <span class="s1">'3/1'', '</span><span class="n">DC2</span><span class="s1">' : '</span><span class="mf">5</span><span class="o">/</span><span class="mf">2</span><span class="s1">'};</span> |
| </pre></div> |
| </div> |
| </div> |
| </div> |
| <div class="section" id="use"> |
| <span id="use-statement"></span><h2>USE<a class="headerlink" href="#use" title="Permalink to this headline">¶</a></h2> |
| <p>The <code class="docutils literal notranslate"><span class="pre">USE</span></code> statement allows to change the <em>current</em> keyspace (for the <em>connection</em> on which it is executed). A number |
| of objects in CQL are bound to a keyspace (tables, user-defined types, functions, …) and the current keyspace is the |
| default keyspace used when those objects are referred without a fully-qualified name (that is, without being prefixed a |
| keyspace name). A <code class="docutils literal notranslate"><span class="pre">USE</span></code> statement simply takes the keyspace to use as current as argument:</p> |
| <pre> |
| <strong id="grammar-token-use-statement">use_statement</strong> ::= USE <a class="reference internal" href="#grammar-token-keyspace-name"><code class="xref docutils literal notranslate"><span class="pre">keyspace_name</span></code></a> |
| </pre> |
| </div> |
| <div class="section" id="alter-keyspace"> |
| <span id="alter-keyspace-statement"></span><h2>ALTER KEYSPACE<a class="headerlink" href="#alter-keyspace" title="Permalink to this headline">¶</a></h2> |
| <p>An <code class="docutils literal notranslate"><span class="pre">ALTER</span> <span class="pre">KEYSPACE</span></code> statement allows to modify the options of a keyspace:</p> |
| <pre> |
| <strong id="grammar-token-alter-keyspace-statement">alter_keyspace_statement</strong> ::= ALTER KEYSPACE <a class="reference internal" href="#grammar-token-keyspace-name"><code class="xref docutils literal notranslate"><span class="pre">keyspace_name</span></code></a> WITH <a class="reference internal" href="#grammar-token-options"><code class="xref docutils literal notranslate"><span class="pre">options</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">ALTER</span> <span class="k">KEYSPACE</span> <span class="n">Excelsior</span> |
| <span class="k">WITH</span> <span class="n">replication</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'SimpleStrategy'</span><span class="p">,</span> <span class="s1">'replication_factor'</span> <span class="p">:</span> <span class="mf">4</span><span class="p">};</span> |
| </pre></div> |
| </div> |
| <p>The supported options are the same than for <a class="reference internal" href="#create-keyspace-statement"><span class="std std-ref">creating a keyspace</span></a>.</p> |
| </div> |
| <div class="section" id="drop-keyspace"> |
| <span id="drop-keyspace-statement"></span><h2>DROP KEYSPACE<a class="headerlink" href="#drop-keyspace" title="Permalink to this headline">¶</a></h2> |
| <p>Dropping a keyspace can be done using the <code class="docutils literal notranslate"><span class="pre">DROP</span> <span class="pre">KEYSPACE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-drop-keyspace-statement">drop_keyspace_statement</strong> ::= DROP KEYSPACE [ IF EXISTS ] <a class="reference internal" href="#grammar-token-keyspace-name"><code class="xref docutils literal notranslate"><span class="pre">keyspace_name</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">DROP</span> <span class="k">KEYSPACE</span> <span class="n">Excelsior</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>Dropping a keyspace results in the immediate, irreversible removal of that keyspace, including all the tables, UTD and |
| functions in it, and all the data contained in those tables.</p> |
| <p>If the keyspace does not exists, the statement will return an error, unless <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">EXISTS</span></code> is used in which case the |
| operation is a no-op.</p> |
| </div> |
| <div class="section" id="create-table"> |
| <span id="create-table-statement"></span><h2>CREATE TABLE<a class="headerlink" href="#create-table" title="Permalink to this headline">¶</a></h2> |
| <p>Creating a new table uses the <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-create-table-statement">create_table_statement</strong> ::= CREATE TABLE [ IF NOT EXISTS ] <a class="reference internal" href="#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-column-definition"><code class="xref docutils literal notranslate"><span class="pre">column_definition</span></code></a> |
| ( ',' <a class="reference internal" href="#grammar-token-column-definition"><code class="xref docutils literal notranslate"><span class="pre">column_definition</span></code></a> )* |
| [ ',' PRIMARY KEY '(' <a class="reference internal" href="#grammar-token-primary-key"><code class="xref docutils literal notranslate"><span class="pre">primary_key</span></code></a> ')' ] |
| ')' [ WITH <a class="reference internal" href="#grammar-token-table-options"><code class="xref docutils literal notranslate"><span class="pre">table_options</span></code></a> ] |
| <strong id="grammar-token-column-definition">column_definition </strong> ::= <a class="reference internal" href="#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-cql-type"><code class="xref docutils literal notranslate"><span class="pre">cql_type</span></code></a> [ STATIC ] [ PRIMARY KEY] |
| <strong id="grammar-token-primary-key">primary_key </strong> ::= <a class="reference internal" href="#grammar-token-partition-key"><code class="xref docutils literal notranslate"><span class="pre">partition_key</span></code></a> [ ',' <a class="reference internal" href="#grammar-token-clustering-columns"><code class="xref docutils literal notranslate"><span class="pre">clustering_columns</span></code></a> ] |
| <strong id="grammar-token-partition-key">partition_key </strong> ::= <a class="reference internal" href="#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-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> ( ',' <a class="reference internal" href="#grammar-token-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* ')' |
| <strong id="grammar-token-clustering-columns">clustering_columns </strong> ::= <a class="reference internal" href="#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-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* |
| <strong id="grammar-token-table-options">table_options </strong> ::= COMPACT STORAGE [ AND <a class="reference internal" href="#grammar-token-table-options"><code class="xref docutils literal notranslate"><span class="pre">table_options</span></code></a> ] |
| | CLUSTERING ORDER BY '(' <a class="reference internal" href="#grammar-token-clustering-order"><code class="xref docutils literal notranslate"><span class="pre">clustering_order</span></code></a> ')' [ AND <a class="reference internal" href="#grammar-token-table-options"><code class="xref docutils literal notranslate"><span class="pre">table_options</span></code></a> ] |
| | <a class="reference internal" href="#grammar-token-options"><code class="xref docutils literal notranslate"><span class="pre">options</span></code></a> |
| <strong id="grammar-token-clustering-order">clustering_order </strong> ::= <a class="reference internal" href="#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="#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">CREATE</span> <span class="k">TABLE</span> <span class="n">monkeySpecies</span> <span class="p">(</span> |
| <span class="n">species</span> <span class="nb">text</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">,</span> |
| <span class="n">common_name</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="n">population</span> <span class="nb">varint</span><span class="p">,</span> |
| <span class="n">average_size</span> <span class="nb">int</span> |
| <span class="p">)</span> <span class="k">WITH</span> <span class="n">comment</span><span class="o">=</span><span class="s1">'Important biological records'</span><span class="p">;</span> |
| |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">timeline</span> <span class="p">(</span> |
| <span class="n">userid</span> <span class="nb">uuid</span><span class="p">,</span> |
| <span class="n">posted_month</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">posted_time</span> <span class="nb">uuid</span><span class="p">,</span> |
| <span class="n">body</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="n">posted_by</span> <span class="nb">text</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">posted_month</span><span class="p">,</span> <span class="n">posted_time</span><span class="p">)</span> |
| <span class="p">)</span> <span class="k">WITH</span> <span class="n">compaction</span> <span class="o">=</span> <span class="p">{</span> <span class="s1">'class'</span> <span class="p">:</span> <span class="s1">'LeveledCompactionStrategy'</span> <span class="p">};</span> |
| |
| <span class="k">CREATE</span> <span class="k">TABLE</span> <span class="n">loads</span> <span class="p">(</span> |
| <span class="n">machine</span> <span class="nb">inet</span><span class="p">,</span> |
| <span class="n">cpu</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">mtime</span> <span class="nb">timeuuid</span><span class="p">,</span> |
| <span class="n">load</span> <span class="nb">float</span><span class="p">,</span> |
| <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">((</span><span class="n">machine</span><span class="p">,</span> <span class="n">cpu</span><span class="p">),</span> <span class="n">mtime</span><span class="p">)</span> |
| <span class="p">)</span> <span class="k">WITH</span> <span class="k">CLUSTERING</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="p">(</span><span class="n">mtime</span> <span class="k">DESC</span><span class="p">);</span> |
| </pre></div> |
| </div> |
| <p>A CQL table has a name and is composed of a set of <em>rows</em>. Creating a table amounts to defining which <a class="reference internal" href="#column-definition"><span class="std std-ref">columns</span></a> the rows will be composed, which of those columns compose the <a class="reference internal" href="#primary-key"><span class="std std-ref">primary key</span></a>, as |
| well as optional <a class="reference internal" href="#create-table-options"><span class="std std-ref">options</span></a> for the table.</p> |
| <p>Attempting to create an already existing table will return an error unless the <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">NOT</span> <span class="pre">EXISTS</span></code> directive is used. If |
| it is used, the statement will be a no-op if the table already exists.</p> |
| <p>Every rows in a CQL table has a set of predefined columns defined at the time of the table creation (or added later |
| using an <a class="reference internal" href="#alter-table-statement"><span class="std std-ref">alter statement</span></a>).</p> |
| <p>A <a class="reference internal" href="#grammar-token-column-definition"><code class="xref std std-token docutils literal notranslate"><span class="pre">column_definition</span></code></a> is primarily comprised of the name of the column defined and it’s <a class="reference internal" href="types.html#data-types"><span class="std std-ref">type</span></a>, |
| which restrict which values are accepted for that column. Additionally, a column definition can have the following |
| modifiers:</p> |
| <dl class="docutils"> |
| <dt><code class="docutils literal notranslate"><span class="pre">STATIC</span></code></dt> |
| <dd>it declares the column as being a <a class="reference internal" href="#static-columns"><span class="std std-ref">static column</span></a>.</dd> |
| <dt><code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code></dt> |
| <dd>it declares the column as being the sole component of the <a class="reference internal" href="#primary-key"><span class="std std-ref">primary key</span></a> of the table.</dd> |
| </dl> |
| <p>Some columns can be declared as <code class="docutils literal notranslate"><span class="pre">STATIC</span></code> in a table definition. A column that is static will be “shared” by all the |
| rows belonging to the same partition (having the same <a class="reference internal" href="#partition-key"><span class="std std-ref">partition key</span></a>). For instance:</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">t</span> <span class="p">(</span> |
| <span class="n">pk</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">t</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">v</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="n">s</span> <span class="nb">text</span> <span class="k">static</span><span class="p">,</span> |
| <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="n">pk</span><span class="p">,</span> <span class="n">t</span><span class="p">)</span> |
| <span class="p">);</span> |
| |
| <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">pk</span><span class="p">,</span> <span class="n">t</span><span class="p">,</span> <span class="n">v</span><span class="p">,</span> <span class="n">s</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mf">0</span><span class="p">,</span> <span class="mf">0</span><span class="p">,</span> <span class="s1">'val0'</span><span class="p">,</span> <span class="s1">'static0'</span><span class="p">);</span> |
| <span class="k">INSERT</span> <span class="k">INTO</span> <span class="n">t</span> <span class="p">(</span><span class="n">pk</span><span class="p">,</span> <span class="n">t</span><span class="p">,</span> <span class="n">v</span><span class="p">,</span> <span class="n">s</span><span class="p">)</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mf">0</span><span class="p">,</span> <span class="mf">1</span><span class="p">,</span> <span class="s1">'val1'</span><span class="p">,</span> <span class="s1">'static1'</span><span class="p">);</span> |
| |
| <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span> |
| <span class="n">pk</span> <span class="o">|</span> <span class="n">t</span> <span class="o">|</span> <span class="n">v</span> <span class="o">|</span> <span class="n">s</span> |
| <span class="c1">----+---+--------+-----------</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="o">|</span> <span class="s1">'val0'</span> <span class="o">|</span> <span class="s1">'static1'</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="s1">'val1'</span> <span class="o">|</span> <span class="s1">'static1'</span> |
| </pre></div> |
| </div> |
| <p>As can be seen, the <code class="docutils literal notranslate"><span class="pre">s</span></code> value is the same (<code class="docutils literal notranslate"><span class="pre">static1</span></code>) for both of the row in the partition (the partition key in |
| that example being <code class="docutils literal notranslate"><span class="pre">pk</span></code>, both rows are in that same partition): the 2nd insertion has overridden the value for <code class="docutils literal notranslate"><span class="pre">s</span></code>.</p> |
| <p>The use of static columns as the following restrictions:</p> |
| <ul class="simple"> |
| <li>tables with the <code class="docutils literal notranslate"><span class="pre">COMPACT</span> <span class="pre">STORAGE</span></code> option (see below) cannot use them.</li> |
| <li>a table without clustering columns cannot have static columns (in a table without clustering columns, every partition |
| has only one row, and so every column is inherently static).</li> |
| <li>only non <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> columns can be static.</li> |
| </ul> |
| <p>Within a table, a row is uniquely identified by its <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code>, and hence all table <strong>must</strong> define a PRIMARY KEY |
| (and only one). A <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> definition is composed of one or more of the columns defined in the table. |
| Syntactically, the primary key is defined the keywords <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> followed by comma-separated list of the column |
| names composing it within parenthesis, but if the primary key has only one column, one can alternatively follow that |
| column definition by the <code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span></code> keywords. The order of the columns in the primary key definition matter.</p> |
| <p>A CQL primary key is composed of 2 parts:</p> |
| <ul> |
| <li><p class="first">the <a class="reference internal" href="#partition-key"><span class="std std-ref">partition key</span></a> part. It is the first component of the primary key definition. It can be a |
| single column or, using additional parenthesis, can be multiple columns. A table always have at least a partition key, |
| the smallest possible table definition is:</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">t</span> <span class="p">(</span><span class="n">k</span> <span class="nb">text</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">);</span> |
| </pre></div> |
| </div> |
| </li> |
| <li><p class="first">the <a class="reference internal" href="#clustering-columns"><span class="std std-ref">clustering columns</span></a>. Those are the columns after the first component of the primary key |
| definition, and the order of those columns define the <em>clustering order</em>.</p> |
| </li> |
| </ul> |
| <p>Some example of primary key definition are:</p> |
| <ul class="simple"> |
| <li><code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">(a)</span></code>: <code class="docutils literal notranslate"><span class="pre">a</span></code> is the partition key and there is no clustering columns.</li> |
| <li><code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">(a,</span> <span class="pre">b,</span> <span class="pre">c)</span></code> : <code class="docutils literal notranslate"><span class="pre">a</span></code> is the partition key and <code class="docutils literal notranslate"><span class="pre">b</span></code> and <code class="docutils literal notranslate"><span class="pre">c</span></code> are the clustering columns.</li> |
| <li><code class="docutils literal notranslate"><span class="pre">PRIMARY</span> <span class="pre">KEY</span> <span class="pre">((a,</span> <span class="pre">b),</span> <span class="pre">c)</span></code> : <code class="docutils literal notranslate"><span class="pre">a</span></code> and <code class="docutils literal notranslate"><span class="pre">b</span></code> compose the partition key (this is often called a <em>composite</em> partition |
| key) and <code class="docutils literal notranslate"><span class="pre">c</span></code> is the clustering column.</li> |
| </ul> |
| <p>Within a table, CQL defines the notion of a <em>partition</em>. A partition is simply the set of rows that share the same value |
| for their partition key. Note that if the partition key is composed of multiple columns, then rows belong to the same |
| partition only they have the same values for all those partition key column. So for instance, given the following table |
| definition and content:</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">t</span> <span class="p">(</span> |
| <span class="n">a</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">b</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">c</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">d</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">a</span><span class="p">,</span> <span class="n">b</span><span class="p">),</span> <span class="n">c</span><span class="p">,</span> <span class="n">d</span><span class="p">)</span> |
| <span class="p">);</span> |
| |
| <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span> |
| <span class="n">a</span> <span class="o">|</span> <span class="n">b</span> <span class="o">|</span> <span class="n">c</span> <span class="o">|</span> <span class="n">d</span> |
| <span class="c1">---+---+---+---</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="c1">// row 1</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="mf">1</span> <span class="c1">// row 2</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="mf">2</span> <span class="o">|</span> <span class="mf">2</span> <span class="c1">// row 3</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="mf">3</span> <span class="o">|</span> <span class="mf">3</span> <span class="c1">// row 4</span> |
| <span class="mf">1</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="mf">4</span> <span class="o">|</span> <span class="mf">4</span> <span class="c1">// row 5</span> |
| </pre></div> |
| </div> |
| <p><code class="docutils literal notranslate"><span class="pre">row</span> <span class="pre">1</span></code> and <code class="docutils literal notranslate"><span class="pre">row</span> <span class="pre">2</span></code> are in the same partition, <code class="docutils literal notranslate"><span class="pre">row</span> <span class="pre">3</span></code> and <code class="docutils literal notranslate"><span class="pre">row</span> <span class="pre">4</span></code> are also in the same partition (but a |
| different one) and <code class="docutils literal notranslate"><span class="pre">row</span> <span class="pre">5</span></code> is in yet another partition.</p> |
| <p>Note that a table always has a partition key, and that if the table has no <a class="reference internal" href="#clustering-columns"><span class="std std-ref">clustering columns</span></a>, then every partition of that table is only comprised of a single row (since the primary key |
| uniquely identifies rows and the primary key is equal to the partition key if there is no clustering columns).</p> |
| <p>The most important property of partition is that all the rows belonging to the same partition are guarantee to be stored |
| on the same set of replica nodes. In other words, the partition key of a table defines which of the rows will be |
| localized together in the Cluster, and it is thus important to choose your partition key wisely so that rows that needs |
| to be fetch together are in the same partition (so that querying those rows together require contacting a minimum of |
| nodes).</p> |
| <p>Please note however that there is a flip-side to this guarantee: as all rows sharing a partition key are guaranteed to |
| be stored on the same set of replica node, a partition key that groups too much data can create a hotspot.</p> |
| <p>Another useful property of a partition is that when writing data, all the updates belonging to a single partition are |
| done <em>atomically</em> and in <em>isolation</em>, which is not the case across partitions.</p> |
| <p>The proper choice of the partition key and clustering columns for a table is probably one of the most important aspect |
| of data modeling in Cassandra, and it largely impact which queries can be performed, and how efficiently they are.</p> |
| <p>The clustering columns of a table defines the clustering order for the partition of that table. For a given |
| <a class="reference internal" href="#partition-key"><span class="std std-ref">partition</span></a>, all the rows are physically ordered inside Cassandra by that clustering order. 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">t</span> <span class="p">(</span> |
| <span class="n">a</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">b</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="n">c</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">a</span><span class="p">,</span> <span class="n">b</span><span class="p">,</span> <span class="n">c</span><span class="p">)</span> |
| <span class="p">);</span> |
| |
| <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">;</span> |
| <span class="n">a</span> <span class="o">|</span> <span class="n">b</span> <span class="o">|</span> <span class="n">c</span> |
| <span class="c1">---+---+---</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">0</span> <span class="o">|</span> <span class="mf">4</span> <span class="c1">// row 1</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">1</span> <span class="o">|</span> <span class="mf">9</span> <span class="c1">// row 2</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">2</span> <span class="o">|</span> <span class="mf">2</span> <span class="c1">// row 3</span> |
| <span class="mf">0</span> <span class="o">|</span> <span class="mf">3</span> <span class="o">|</span> <span class="mf">3</span> <span class="c1">// row 4</span> |
| </pre></div> |
| </div> |
| <p>then the rows (which all belong to the same partition) are all stored internally in the order of the values of their |
| <code class="docutils literal notranslate"><span class="pre">b</span></code> column (the order they are displayed above). So where the partition key of the table allows to group rows on the |
| same replica set, the clustering columns controls how those rows are stored on the replica. That sorting allows the |
| retrieval of a range of rows within a partition (for instance, in the example above, <code class="docutils literal notranslate"><span class="pre">SELECT</span> <span class="pre">*</span> <span class="pre">FROM</span> <span class="pre">t</span> <span class="pre">WHERE</span> <span class="pre">a</span> <span class="pre">=</span> <span class="pre">0</span> <span class="pre">AND</span> <span class="pre">b</span> |
| <span class="pre">></span> <span class="pre">1</span> <span class="pre">and</span> <span class="pre">b</span> <span class="pre"><=</span> <span class="pre">3</span></code>) to be very efficient.</p> |
| <p>A CQL table has a number of options that can be set at creation (and, for most of them, <a class="reference internal" href="#alter-table-statement"><span class="std std-ref">altered</span></a> later). These options are specified after the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> keyword.</p> |
| <p>Amongst those options, two important ones cannot be changed after creation and influence which queries can be done |
| against the table: the <code class="docutils literal notranslate"><span class="pre">COMPACT</span> <span class="pre">STORAGE</span></code> option and the <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> option. Those, as well as the other |
| options of a table are described in the following sections.</p> |
| <div class="admonition warning"> |
| <p class="first admonition-title">Warning</p> |
| <p class="last">Since Cassandra 3.0, compact tables have the exact same layout internally than non compact ones (for the |
| same schema obviously), and declaring a table compact <strong>only</strong> creates artificial limitations on the table definition |
| and usage. It only exists for historical reason and is preserved for backward compatibility And as <code class="docutils literal notranslate"><span class="pre">COMPACT</span> |
| <span class="pre">STORAGE</span></code> cannot, as of Cassandra 4.0-alpha4, be removed, it is strongly discouraged to create new table with the |
| <code class="docutils literal notranslate"><span class="pre">COMPACT</span> <span class="pre">STORAGE</span></code> option.</p> |
| </div> |
| <p>A <em>compact</em> table is one defined with the <code class="docutils literal notranslate"><span class="pre">COMPACT</span> <span class="pre">STORAGE</span></code> option. This option is only maintained for backward |
| compatibility for definitions created before CQL version 3 and shouldn’t be used for new tables. Declaring a |
| table with this option creates limitations for the table which are largely arbitrary (and exists for historical |
| reasons). Amongst those limitation:</p> |
| <ul class="simple"> |
| <li>a compact table cannot use collections nor static columns.</li> |
| <li>if a compact table has at least one clustering column, then it must have <em>exactly</em> one column outside of the primary |
| key ones. This imply you cannot add or remove columns after creation in particular.</li> |
| <li>a compact table is limited in the indexes it can create, and no materialized view can be created on it.</li> |
| </ul> |
| <p>The clustering order of a table is defined by the <a class="reference internal" href="#clustering-columns"><span class="std std-ref">clustering columns</span></a> of that table. By |
| default, that ordering is based on natural order of those clustering order, but the <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> allows to |
| change that clustering order to use the <em>reverse</em> natural order for some (potentially all) of the columns.</p> |
| <p>The <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> option takes the comma-separated list of the clustering column, each with a <code class="docutils literal notranslate"><span class="pre">ASC</span></code> (for |
| <em>ascendant</em>, e.g. the natural order) or <code class="docutils literal notranslate"><span class="pre">DESC</span></code> (for <em>descendant</em>, e.g. the reverse natural order). Note in particular |
| that the default (if the <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> <span class="pre">ORDER</span></code> option is not used) is strictly equivalent to using the option with all |
| clustering columns using the <code class="docutils literal notranslate"><span class="pre">ASC</span></code> modifier.</p> |
| <p>Note that this option is basically a hint for the storage engine to change the order in which it stores the row but it |
| has 3 visible consequences:</p> |
| <dl class="docutils"> |
| <dt># it limits which <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause are allowed for <a class="reference internal" href="dml.html#select-statement"><span class="std std-ref">selects</span></a> on that table. You can only</dt> |
| <dd>order results by the clustering order or the reverse clustering order. Meaning that if a table has 2 clustering column |
| <code class="docutils literal notranslate"><span class="pre">a</span></code> and <code class="docutils literal notranslate"><span class="pre">b</span></code> and you defined <code class="docutils literal notranslate"><span class="pre">WITH</span> <span class="pre">CLUSTERING</span> <span class="pre">ORDER</span> <span class="pre">(a</span> <span class="pre">DESC,</span> <span class="pre">b</span> <span class="pre">ASC)</span></code>, then in queries you will be allowed to use |
| <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span> <span class="pre">(a</span> <span class="pre">DESC,</span> <span class="pre">b</span> <span class="pre">ASC)</span></code> and (reverse clustering order) <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span> <span class="pre">(a</span> <span class="pre">ASC,</span> <span class="pre">b</span> <span class="pre">DESC)</span></code> but <strong>not</strong> <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span> <span class="pre">(a</span> |
| <span class="pre">ASC,</span> <span class="pre">b</span> <span class="pre">ASC)</span></code> (nor <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span> <span class="pre">(a</span> <span class="pre">DESC,</span> <span class="pre">b</span> <span class="pre">DESC)</span></code>).</dd> |
| <dt># it also change the default order of results when queried (if no <code class="docutils literal notranslate"><span class="pre">ORDER</span> <span class="pre">BY</span></code> is provided). Results are always returned</dt> |
| <dd>in clustering order (within a partition).</dd> |
| <dt># it has a small performance impact on some queries as queries in reverse clustering order are slower than the one in</dt> |
| <dd>forward clustering order. In practice, this means that if you plan on querying mostly in the reverse natural order of |
| your columns (which is common with time series for instance where you often want data from the newest to the oldest), |
| it is an optimization to declare a descending clustering order.</dd> |
| </dl> |
| <div class="admonition-todo admonition" id="index-0"> |
| <p class="first admonition-title">Todo</p> |
| <p class="last">review (misses cdc if nothing else) and link to proper categories when appropriate (compaction for instance)</p> |
| </div> |
| <p>A table supports the following options:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="28%" /> |
| <col width="9%" /> |
| <col width="11%" /> |
| <col width="52%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">option</th> |
| <th class="head">kind</th> |
| <th class="head">default</th> |
| <th class="head">description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">comment</span></code> |
| <code class="docutils literal notranslate"><span class="pre">speculative_retry</span></code></td> |
| <td><em>simple</em> |
| <em>simple</em></td> |
| <td>none |
| 99PERCENTILE</td> |
| <td>A free-form, human-readable comment. |
| <a class="reference internal" href="#speculative-retry-options"><span class="std std-ref">Speculative retry options</span></a>.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">cdc</span></code></td> |
| <td><em>boolean</em></td> |
| <td>false</td> |
| <td>Create a Change Data Capture (CDC) log on the table.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">additional_write_policy</span></code></td> |
| <td><em>simple</em></td> |
| <td>99PERCENTILE</td> |
| <td><a class="reference internal" href="#speculative-retry-options"><span class="std std-ref">Speculative retry options</span></a>.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">gc_grace_seconds</span></code></td> |
| <td><em>simple</em></td> |
| <td>864000</td> |
| <td>Time to wait before garbage collecting tombstones |
| (deletion markers).</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">bloom_filter_fp_chance</span></code></td> |
| <td><em>simple</em></td> |
| <td>0.00075</td> |
| <td>The target probability of false positive of the sstable |
| bloom filters. Said bloom filters will be sized to provide |
| the provided probability (thus lowering this value impact |
| the size of bloom filters in-memory and on-disk)</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">default_time_to_live</span></code></td> |
| <td><em>simple</em></td> |
| <td>0</td> |
| <td>The default expiration time (“TTL”) in seconds for a |
| table.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">compaction</span></code></td> |
| <td><em>map</em></td> |
| <td><em>see below</em></td> |
| <td><a class="reference internal" href="#cql-compaction-options"><span class="std std-ref">Compaction options</span></a>.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">compression</span></code></td> |
| <td><em>map</em></td> |
| <td><em>see below</em></td> |
| <td><a class="reference internal" href="#cql-compression-options"><span class="std std-ref">Compression options</span></a>.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">caching</span></code></td> |
| <td><em>map</em></td> |
| <td><em>see below</em></td> |
| <td><a class="reference internal" href="#cql-caching-options"><span class="std std-ref">Caching options</span></a>.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">memtable_flush_period_in_ms</span></code></td> |
| <td><em>simple</em></td> |
| <td>0</td> |
| <td>Time (in ms) before Cassandra flushes memtables to disk.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">read_repair</span></code></td> |
| <td><em>simple</em></td> |
| <td>BLOCKING</td> |
| <td>Sets read repair behavior (see below)</td> |
| </tr> |
| </tbody> |
| </table> |
| <p>By default, Cassandra read coordinators only query as many replicas as necessary to satisfy |
| consistency levels: one for consistency level <code class="docutils literal notranslate"><span class="pre">ONE</span></code>, a quorum for <code class="docutils literal notranslate"><span class="pre">QUORUM</span></code>, and so on. |
| <code class="docutils literal notranslate"><span class="pre">speculative_retry</span></code> determines when coordinators may query additional replicas, which is useful |
| when replicas are slow or unresponsive. Speculative retries are used to reduce the latency. The speculative_retry option may be |
| used to configure rapid read protection with which a coordinator sends more requests than needed to satisfy the Consistency level.</p> |
| <p>Pre-4.0 speculative Retry Policy takes a single string as a parameter, this can be <code class="docutils literal notranslate"><span class="pre">NONE</span></code>, <code class="docutils literal notranslate"><span class="pre">ALWAYS</span></code>, <code class="docutils literal notranslate"><span class="pre">99PERCENTILE</span></code> (PERCENTILE), <code class="docutils literal notranslate"><span class="pre">50MS</span></code> (CUSTOM).</p> |
| <p>Examples of setting speculative retry are:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="k">users</span> <span class="k">WITH</span> <span class="n">speculative_retry</span> <span class="o">=</span> <span class="s1">'10ms'</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>Or,</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="k">users</span> <span class="k">WITH</span> <span class="n">speculative_retry</span> <span class="o">=</span> <span class="s1">'99PERCENTILE'</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>The problem with these settings is when a single host goes into an unavailable state this drags up the percentiles. This means if we |
| are set to use <code class="docutils literal notranslate"><span class="pre">p99</span></code> alone, we might not speculate when we intended to to because the value at the specified percentile has gone so high. |
| As a fix 4.0 adds support for hybrid <code class="docutils literal notranslate"><span class="pre">MIN()</span></code>, <code class="docutils literal notranslate"><span class="pre">MAX()</span></code> speculative retry policies (<a class="reference external" href="https://issues.apache.org/jira/browse/CASSANDRA-14293">CASSANDRA-14293</a>). This means if the normal <code class="docutils literal notranslate"><span class="pre">p99</span></code> for the |
| table is <50ms, we will still speculate at this value and not drag the tail latencies up… but if the <code class="docutils literal notranslate"><span class="pre">p99th</span></code> goes above what we know we |
| should never exceed we use that instead.</p> |
| <p>In 4.0 the values (case-insensitive) discussed in the following table are supported:</p> |
| <p>As of version 4.0 speculative retry allows more friendly params (<a class="reference external" href="https://issues.apache.org/jira/browse/CASSANDRA-13876">CASSANDRA-13876</a>). The <code class="docutils literal notranslate"><span class="pre">speculative_retry</span></code> is more flexible with case. As an example a |
| value does not have to be <code class="docutils literal notranslate"><span class="pre">NONE</span></code>, and the following are supported alternatives.</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">alter</span> <span class="k">table</span> <span class="k">users</span> <span class="k">WITH</span> <span class="n">speculative_retry</span> <span class="o">=</span> <span class="s1">'none'</span><span class="p">;</span> |
| <span class="k">alter</span> <span class="k">table</span> <span class="k">users</span> <span class="k">WITH</span> <span class="n">speculative_retry</span> <span class="o">=</span> <span class="s1">'None'</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>The text component is case insensitive and for <code class="docutils literal notranslate"><span class="pre">nPERCENTILE</span></code> version 4.0 allows <code class="docutils literal notranslate"><span class="pre">nP</span></code>, for instance <code class="docutils literal notranslate"><span class="pre">99p</span></code>. |
| In a hybrid value for speculative retry, one of the two values must be a fixed millisecond value and the other a percentile value.</p> |
| <p>Some examples:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="n">min</span><span class="p">(</span><span class="mf">99</span><span class="n">percentile</span><span class="p">,</span><span class="mf">50</span><span class="n">ms</span><span class="p">)</span> |
| <span class="n">max</span><span class="p">(</span><span class="mf">99</span><span class="n">p</span><span class="p">,</span><span class="mf">50</span><span class="n">MS</span><span class="p">)</span> |
| <span class="n">MAX</span><span class="p">(</span><span class="mf">99</span><span class="n">P</span><span class="p">,</span><span class="mf">50</span><span class="n">ms</span><span class="p">)</span> |
| <span class="n">MIN</span><span class="p">(</span><span class="mf">99.9</span><span class="n">PERCENTILE</span><span class="p">,</span><span class="mf">50</span><span class="n">ms</span><span class="p">)</span> |
| <span class="n">max</span><span class="p">(</span><span class="mf">90</span><span class="n">percentile</span><span class="p">,</span><span class="mf">100</span><span class="n">MS</span><span class="p">)</span> |
| <span class="n">MAX</span><span class="p">(</span><span class="mf">100.0</span><span class="n">PERCENTILE</span><span class="p">,</span><span class="mf">60</span><span class="n">ms</span><span class="p">)</span> |
| </pre></div> |
| </div> |
| <p>Two values of the same kind cannot be specified such as <code class="docutils literal notranslate"><span class="pre">min(90percentile,99percentile)</span></code> as it wouldn’t be a hybrid value. |
| This setting does not affect reads with consistency level <code class="docutils literal notranslate"><span class="pre">ALL</span></code> because they already query all replicas.</p> |
| <p>Note that frequently reading from additional replicas can hurt cluster performance. |
| When in doubt, keep the default <code class="docutils literal notranslate"><span class="pre">99PERCENTILE</span></code>.</p> |
| <p><code class="docutils literal notranslate"><span class="pre">additional_write_policy</span></code> specifies the threshold at which a cheap quorum write will be upgraded to include transient replicas.</p> |
| <p>The <code class="docutils literal notranslate"><span class="pre">compaction</span></code> options must at least define the <code class="docutils literal notranslate"><span class="pre">'class'</span></code> sub-option, that defines the compaction strategy class |
| to use. The supported class are <code class="docutils literal notranslate"><span class="pre">'SizeTieredCompactionStrategy'</span></code> (<a class="reference internal" href="../operating/compaction/stcs.html#stcs"><span class="std std-ref">STCS</span></a>), |
| <code class="docutils literal notranslate"><span class="pre">'LeveledCompactionStrategy'</span></code> (<a class="reference internal" href="../operating/compaction/lcs.html#lcs"><span class="std std-ref">LCS</span></a>) and <code class="docutils literal notranslate"><span class="pre">'TimeWindowCompactionStrategy'</span></code> (<a class="reference internal" href="../operating/compaction/twcs.html#twcs"><span class="std std-ref">TWCS</span></a>) (the |
| <code class="docutils literal notranslate"><span class="pre">'DateTieredCompactionStrategy'</span></code> is also supported but is deprecated and <code class="docutils literal notranslate"><span class="pre">'TimeWindowCompactionStrategy'</span></code> should be |
| preferred instead). The default is <code class="docutils literal notranslate"><span class="pre">'SizeTieredCompactionStrategy'</span></code>. Custom strategy can be provided by specifying the full class name as a <a class="reference internal" href="definitions.html#constants"><span class="std std-ref">string constant</span></a>.</p> |
| <p>All default strategies support a number of <a class="reference internal" href="../operating/compaction/index.html#compaction-options"><span class="std std-ref">common options</span></a>, as well as options specific to |
| the strategy chosen (see the section corresponding to your strategy for details: <a class="reference internal" href="../operating/compaction/stcs.html#stcs-options"><span class="std std-ref">STCS</span></a>, <a class="reference internal" href="../operating/compaction/lcs.html#lcs-options"><span class="std std-ref">LCS</span></a> and <a class="reference internal" href="../operating/compaction/twcs.html#twcs"><span class="std std-ref">TWCS</span></a>).</p> |
| <p>The <code class="docutils literal notranslate"><span class="pre">compression</span></code> options define if and how the sstables of the table are compressed. Compression is configured on a per-table |
| basis as an optional argument to <code class="docutils literal notranslate"><span class="pre">CREATE</span> <span class="pre">TABLE</span></code> or <code class="docutils literal notranslate"><span class="pre">ALTER</span> <span class="pre">TABLE</span></code>. The following sub-options are |
| available:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="19%" /> |
| <col width="11%" /> |
| <col width="70%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">Option</th> |
| <th class="head">Default</th> |
| <th class="head">Description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">class</span></code></td> |
| <td>LZ4Compressor</td> |
| <td>The compression algorithm to use. Default compressor are: LZ4Compressor, |
| SnappyCompressor, DeflateCompressor and ZstdCompressor. Use <code class="docutils literal notranslate"><span class="pre">'enabled'</span> <span class="pre">:</span> <span class="pre">false</span></code> to disable |
| compression. Custom compressor can be provided by specifying the full class |
| name as a “string constant”:#constants.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">enabled</span></code></td> |
| <td>true</td> |
| <td>Enable/disable sstable compression. If the <code class="docutils literal notranslate"><span class="pre">enabled</span></code> option is set to <code class="docutils literal notranslate"><span class="pre">false</span></code> no other |
| options must be specified.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">chunk_length_in_kb</span></code></td> |
| <td>64</td> |
| <td><p class="first">On disk SSTables are compressed by block (to allow random reads). This |
| defines the size (in KB) of said block. Bigger values may improve the |
| compression rate, but increases the minimum size of data to be read from disk |
| for a read. The default value is an optimal value for compressing tables. Chunk length must |
| be a power of 2 because so is assumed so when computing the chunk number from an uncompressed |
| file offset. Block size may be adjusted based on read/write access patterns such as:</p> |
| <blockquote class="last"> |
| <div><ul class="simple"> |
| <li>How much data is typically requested at once</li> |
| <li>Average size of rows in the table</li> |
| </ul> |
| </div></blockquote> |
| </td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">crc_check_chance</span></code></td> |
| <td>1.0</td> |
| <td>Determines how likely Cassandra is to verify the checksum on each compression chunk during |
| reads.</td> |
| </tr> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">compression_level</span></code></td> |
| <td>3</td> |
| <td>Compression level. It is only applicable for <code class="docutils literal notranslate"><span class="pre">ZstdCompressor</span></code> and accepts values between |
| <code class="docutils literal notranslate"><span class="pre">-131072</span></code> and <code class="docutils literal notranslate"><span class="pre">22</span></code>.</td> |
| </tr> |
| </tbody> |
| </table> |
| <p>For instance, to create a table with LZ4Compressor and a chunk_lenth_in_kb of 4KB:</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">simple</span> <span class="p">(</span> |
| <span class="n">id</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="k">key</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="n">value</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="k">key</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span> |
| <span class="p">)</span> <span class="k">with</span> <span class="n">compression</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'class'</span><span class="p">:</span> <span class="s1">'LZ4Compressor'</span><span class="p">,</span> <span class="s1">'chunk_length_in_kb'</span><span class="p">:</span> <span class="mf">4</span><span class="p">};</span> |
| </pre></div> |
| </div> |
| <p>Caching optimizes the use of cache memory of a table. The cached data is weighed by size and access frequency. The <code class="docutils literal notranslate"><span class="pre">caching</span></code> |
| options allows to configure both the <em>key cache</em> and the <em>row cache</em> for the table. The following |
| sub-options are available:</p> |
| <table border="1" class="docutils"> |
| <colgroup> |
| <col width="21%" /> |
| <col width="8%" /> |
| <col width="72%" /> |
| </colgroup> |
| <thead valign="bottom"> |
| <tr class="row-odd"><th class="head">Option</th> |
| <th class="head">Default</th> |
| <th class="head">Description</th> |
| </tr> |
| </thead> |
| <tbody valign="top"> |
| <tr class="row-even"><td><code class="docutils literal notranslate"><span class="pre">keys</span></code></td> |
| <td>ALL</td> |
| <td>Whether to cache keys (“key cache”) for this table. Valid values are: <code class="docutils literal notranslate"><span class="pre">ALL</span></code> and |
| <code class="docutils literal notranslate"><span class="pre">NONE</span></code>.</td> |
| </tr> |
| <tr class="row-odd"><td><code class="docutils literal notranslate"><span class="pre">rows_per_partition</span></code></td> |
| <td>NONE</td> |
| <td>The amount of rows to cache per partition (“row cache”). If an integer <code class="docutils literal notranslate"><span class="pre">n</span></code> is |
| specified, the first <code class="docutils literal notranslate"><span class="pre">n</span></code> queried rows of a partition will be cached. Other |
| possible options are <code class="docutils literal notranslate"><span class="pre">ALL</span></code>, to cache all rows of a queried partition, or <code class="docutils literal notranslate"><span class="pre">NONE</span></code> |
| to disable row caching.</td> |
| </tr> |
| </tbody> |
| </table> |
| <p>For instance, to create a table with both a key cache and 10 rows per partition:</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">simple</span> <span class="p">(</span> |
| <span class="n">id</span> <span class="nb">int</span><span class="p">,</span> |
| <span class="k">key</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="n">value</span> <span class="nb">text</span><span class="p">,</span> |
| <span class="k">PRIMARY</span> <span class="k">KEY</span> <span class="p">(</span><span class="k">key</span><span class="p">,</span> <span class="n">value</span><span class="p">)</span> |
| <span class="p">)</span> <span class="k">WITH</span> <span class="n">caching</span> <span class="o">=</span> <span class="p">{</span><span class="s1">'keys'</span><span class="p">:</span> <span class="s1">'ALL'</span><span class="p">,</span> <span class="s1">'rows_per_partition'</span><span class="p">:</span> <span class="mf">10</span><span class="p">};</span> |
| </pre></div> |
| </div> |
| <p>The <code class="docutils literal notranslate"><span class="pre">read_repair</span></code> options configures the read repair behavior to allow tuning for various performance and |
| consistency behaviors. Two consistency properties are affected by read repair behavior.</p> |
| <ul class="simple"> |
| <li>Monotonic Quorum Reads: Provided by <code class="docutils literal notranslate"><span class="pre">BLOCKING</span></code>. Monotonic quorum reads prevents reads from appearing to go back |
| in time in some circumstances. When monotonic quorum reads are not provided and a write fails to reach a quorum of |
| replicas, it may be visible in one read, and then disappear in a subsequent read.</li> |
| <li>Write Atomicity: Provided by <code class="docutils literal notranslate"><span class="pre">NONE</span></code>. Write atomicity prevents reads from returning partially applied writes. |
| Cassandra attempts to provide partition level write atomicity, but since only the data covered by a SELECT statement |
| is repaired by a read repair, read repair can break write atomicity when data is read at a more granular level than it |
| is written. For example read repair can break write atomicity if you write multiple rows to a clustered partition in a |
| batch, but then select a single row by specifying the clustering column in a SELECT statement.</li> |
| </ul> |
| <p>The available read repair settings are:</p> |
| <p>The default setting. When <code class="docutils literal notranslate"><span class="pre">read_repair</span></code> is set to <code class="docutils literal notranslate"><span class="pre">BLOCKING</span></code>, and a read repair is triggered, the read will block |
| on writes sent to other replicas until the CL is reached by the writes. Provides monotonic quorum reads, but not partition |
| level write atomicity</p> |
| <p>When <code class="docutils literal notranslate"><span class="pre">read_repair</span></code> is set to <code class="docutils literal notranslate"><span class="pre">NONE</span></code>, the coordinator will reconcile any differences between replicas, but will not |
| attempt to repair them. Provides partition level write atomicity, but not monotonic quorum reads.</p> |
| <ul class="simple"> |
| <li>Adding new columns (see <code class="docutils literal notranslate"><span class="pre">ALTER</span> <span class="pre">TABLE</span></code> below) is a constant time operation. There is thus no need to try to |
| anticipate future usage when creating a table.</li> |
| </ul> |
| </div> |
| <div class="section" id="alter-table"> |
| <span id="alter-table-statement"></span><h2>ALTER TABLE<a class="headerlink" href="#alter-table" title="Permalink to this headline">¶</a></h2> |
| <p>Altering an existing table uses the <code class="docutils literal notranslate"><span class="pre">ALTER</span> <span class="pre">TABLE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-alter-table-statement">alter_table_statement </strong> ::= ALTER TABLE <a class="reference internal" href="#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-alter-table-instruction"><code class="xref docutils literal notranslate"><span class="pre">alter_table_instruction</span></code></a> |
| <strong id="grammar-token-alter-table-instruction">alter_table_instruction</strong> ::= ADD <a class="reference internal" href="#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-cql-type"><code class="xref docutils literal notranslate"><span class="pre">cql_type</span></code></a> ( ',' <a class="reference internal" href="#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-cql-type"><code class="xref docutils literal notranslate"><span class="pre">cql_type</span></code></a> )* |
| | DROP <a class="reference internal" href="#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-column-name"><code class="xref docutils literal notranslate"><span class="pre">column_name</span></code></a> )* |
| | WITH <a class="reference internal" href="#grammar-token-options"><code class="xref docutils literal notranslate"><span class="pre">options</span></code></a> |
| </pre> |
| <p>For instance:</p> |
| <div class="highlight-cql notranslate"><div class="highlight"><pre><span></span><span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">addamsFamily</span> <span class="k">ADD</span> <span class="n">gravesite</span> <span class="nb">varchar</span><span class="p">;</span> |
| |
| <span class="k">ALTER</span> <span class="k">TABLE</span> <span class="n">addamsFamily</span> |
| <span class="k">WITH</span> <span class="n">comment</span> <span class="o">=</span> <span class="s1">'A most excellent and useful table'</span><span class="p">;</span> |
| </pre></div> |
| </div> |
| <p>The <code class="docutils literal notranslate"><span class="pre">ALTER</span> <span class="pre">TABLE</span></code> statement can:</p> |
| <ul class="simple"> |
| <li>Add new column(s) to the table (through the <code class="docutils literal notranslate"><span class="pre">ADD</span></code> instruction). Note that the primary key of a table cannot be |
| changed and thus newly added column will, by extension, never be part of the primary key. Also note that <a class="reference internal" href="#compact-tables"><span class="std std-ref">compact |
| tables</span></a> have restrictions regarding column addition. Note that this is constant (in the amount of |
| data the cluster contains) time operation.</li> |
| <li>Remove column(s) from the table. This drops both the column and all its content, but note that while the column |
| becomes immediately unavailable, its content is only removed lazily during compaction. Please also see the warnings |
| below. Due to lazy removal, the altering itself is a constant (in the amount of data removed or contained in the |
| cluster) time operation.</li> |
| <li>Change some of the table options (through the <code class="docutils literal notranslate"><span class="pre">WITH</span></code> instruction). The <a class="reference internal" href="#create-table-options"><span class="std std-ref">supported options</span></a> are the same that when creating a table (outside of <code class="docutils literal notranslate"><span class="pre">COMPACT</span> <span class="pre">STORAGE</span></code> and <code class="docutils literal notranslate"><span class="pre">CLUSTERING</span> |
| <span class="pre">ORDER</span></code> that cannot be changed after creation). Note that setting any <code class="docutils literal notranslate"><span class="pre">compaction</span></code> sub-options has the effect of |
| erasing all previous <code class="docutils literal notranslate"><span class="pre">compaction</span></code> options, so you need to re-specify all the sub-options if you want to keep them. |
| The same note applies to the set of <code class="docutils literal notranslate"><span class="pre">compression</span></code> sub-options.</li> |
| </ul> |
| <div class="admonition warning"> |
| <p class="first admonition-title">Warning</p> |
| <p class="last">Dropping a column assumes that the timestamps used for the value of this column are “real” timestamp in |
| microseconds. Using “real” timestamps in microseconds is the default is and is <strong>strongly</strong> recommended but as |
| Cassandra allows the client to provide any timestamp on any table it is theoretically possible to use another |
| convention. Please be aware that if you do so, dropping a column will not work correctly.</p> |
| </div> |
| <div class="admonition warning"> |
| <p class="first admonition-title">Warning</p> |
| <p class="last">Once a column is dropped, it is allowed to re-add a column with the same name than the dropped one |
| <strong>unless</strong> the type of the dropped column was a (non-frozen) column (due to an internal technical limitation).</p> |
| </div> |
| </div> |
| <div class="section" id="drop-table"> |
| <span id="drop-table-statement"></span><h2>DROP TABLE<a class="headerlink" href="#drop-table" title="Permalink to this headline">¶</a></h2> |
| <p>Dropping a table uses the <code class="docutils literal notranslate"><span class="pre">DROP</span> <span class="pre">TABLE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-drop-table-statement">drop_table_statement</strong> ::= DROP TABLE [ IF EXISTS ] <a class="reference internal" href="#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a> |
| </pre> |
| <p>Dropping a table results in the immediate, irreversible removal of the table, including all data it contains.</p> |
| <p>If the table does not exist, the statement will return an error, unless <code class="docutils literal notranslate"><span class="pre">IF</span> <span class="pre">EXISTS</span></code> is used in which case the |
| operation is a no-op.</p> |
| </div> |
| <div class="section" id="truncate"> |
| <span id="truncate-statement"></span><h2>TRUNCATE<a class="headerlink" href="#truncate" title="Permalink to this headline">¶</a></h2> |
| <p>A table can be truncated using the <code class="docutils literal notranslate"><span class="pre">TRUNCATE</span></code> statement:</p> |
| <pre> |
| <strong id="grammar-token-truncate-statement">truncate_statement</strong> ::= TRUNCATE [ TABLE ] <a class="reference internal" href="#grammar-token-table-name"><code class="xref docutils literal notranslate"><span class="pre">table_name</span></code></a> |
| </pre> |
| <p>Note that <code class="docutils literal notranslate"><span class="pre">TRUNCATE</span> <span class="pre">TABLE</span> <span class="pre">foo</span></code> is allowed for consistency with other DDL statements but tables are the only object |
| that can be truncated currently and so the <code class="docutils literal notranslate"><span class="pre">TABLE</span></code> keyword can be omitted.</p> |
| <p>Truncating a table permanently removes all existing data from the table, but without removing the table itself.</p> |
| </div> |
| </div> |
| |
| |
| |
| |
| <div class="doc-prev-next-links" role="navigation" aria-label="footer navigation"> |
| |
| <a href="dml.html" class="btn btn-default pull-right " role="button" title="Data Manipulation" accesskey="n">Next <span class="glyphicon glyphicon-circle-arrow-right" aria-hidden="true"></span></a> |
| |
| |
| <a href="types.html" class="btn btn-default" role="button" title="Data Types" accesskey="p"><span class="glyphicon glyphicon-circle-arrow-left" aria-hidden="true"></span> Previous</a> |
| |
| </div> |
| |
| </div> |
| </div> |
| </div> |
| </div> |
| </div> |