Databases
StackOverflow2013
Postsdbo
POHow much control over static and dynamic column mixing do I have in Cassandra CQL 3 table definitions?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow much control over static and dynamic column mixing do I have in Cassandra CQL 3 table definitions?
    text
    copied!<p>I am trying to better understand what level I can actually mix static and dynamic columns in CQL3. I creating a table in CQL 3 to store user subscription data, but I am also testing out methods to store a tenant_id (with a secondary index) in the table as well so that I can quickly identify what users belong to what tenants (a tenant being a higher-level entity to which the user belongs). </p> <p>My create table statement is as follows, followed by some dummy data inserts: </p> <pre><code>cqlsh:demodb&gt; CREATE TABLE subscription_by_user ( user_id text, tenant_id uuid, subscription_id int, type text, distribution int, PRIMARY KEY (user_id, subscription_id) ); cqlsh:demodb&gt; CREATE INDEX subscription_ids ON subscription_templates_by_user (tenant_id); cqlsh:demodb&gt; INSERT INTO subscription_by_user (user_id, tenant_id, subscription_id, type, distribution) VALUES ('user1', f81d4fae-7dec-11d0-a765-00a0c91e6bf6, 2, 'MESSAGE', 4); cqlsh:demodb&gt; INSERT INTO subscription_by_user (user_id, tenant_id, subscription_id, type, distribution) VALUES ('user2', f81d4fae-7dec-11d0-a765-00a0c91e6bf6, 3, 'TOPIC', 5); cqlsh:demodb&gt; INSERT INTO subscription_by_user (user_id, tenant_id, subscription_id, type, distribution) VALUES ('user1', f81d4fae-7dec-11d0-a765-00a0c91e6bf6, 3, 'USER', 4); </code></pre> <p>This is all well and good and I get back what I expect to using the cql 3 API:</p> <pre><code>cqlsh:demodb&gt; SELECT * FROM subscription_by_user ; user_id | subscription_id | distribution | tenant_id | type ---------+-----------------+--------------+--------------------------------------+------------ user2 | 3 | 5 | f81d4fae-7dec-11d0-a765-00a0c91e6bf6 | TOPIC user1 | 2 | 4 | f81d4fae-7dec-11d0-a765-00a0c91e6bf6 | BOARD user1 | 3 | 4 | f81d4fae-7dec-11d0-a765-00a0c91e6bf6 | USER </code></pre> <p>However, the underlying storage of the row results in Cassandra duplicating tenant_id for every new subscription:</p> <pre><code>[default@demodb] list subscription_templates_by_user; Using default limit of 100 Using default column limit of 100 ------------------- RowKey: user2 =&gt; (column=3:, value=, timestamp=1366150799244000) =&gt; (column=3:distribution, value=00000005, timestamp=1366150799244000) =&gt; (column=3:tenant_id, value=f81d4fae7dec11d0a76500a0c91e6bf6, timestamp=1366150799244000) =&gt; (column=3:type, value=746573742d7479706532, timestamp=1366150799244000) ------------------- RowKey: user1 =&gt; (column=2:, value=, timestamp=1366150764854000) =&gt; (column=2:distribution, value=00000004, timestamp=1366150764854000) =&gt; (column=2:tenant_id, value=f81d4fae7dec11d0a76500a0c91e6bf6, timestamp=1366150764854000) =&gt; (column=2:type, value=746573742d74797065, timestamp=1366150764854000) =&gt; (column=3:, value=, timestamp=1366151741325000) =&gt; (column=3:distribution, value=00000004, timestamp=1366151741325000) =&gt; (column=3:tenant_id, value=f81d4fae7dec11d0a76500a0c91e6bf6, timestamp=1366151741325000) =&gt; (column=3:type, value=746573742d74797065, timestamp=1366151741325000) </code></pre> <p>My question is: is there a way for me (using CQL 3) to structure the table in a way where subscription_id is only listed once per row? If not, is it possible to do this using the older cassandra-cli API? It seems like this would be a viable use case and is really a question of mixing static data with dynamic data (i.e., the subscription bits), but I could very well be wrong. I know that maps and the other CQL 3 collection types are one way of doing this, but the fact that I must retrieve the entire collection at once makes me a bit nervous and I would rather not serialize some complex object into the map value slot if I can help it.</p> <p>Thanks!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload