Mailing-List: contact user-help@cassandra.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cassandra.apache.org
Received-SPF: pass (nike.apache.org: domain of matthew.hillsborough@gmail.com
 designates 209.85.192.177 as permitted sender)
MIME-Version: 1.0
From: Matthew Hillsborough <matthew.hillsborough@gmail.com>
Date: Mon, 27 May 2013 10:28:50 -0400
Message-ID: 
 <CAGFecmQhBvhG91YkNMhvz8YMbLXH14Jtf+1c06=nDdVVk8b0Mg@mail.gmail.com>
Subject: Using CQL to insert a column to a row dynamically
To: user@cassandra.apache.org
Content-Type: multipart/alternative; boundary=047d7bacc834d4974f04ddb3f8a7

--047d7bacc834d4974f04ddb3f8a7
Content-Type: text/plain; charset=ISO-8859-1

Hi all,

I posted a similar thread on stackoverflow - hope it's not repetitive for
anyone here. Looking for better insight from the community on whether
Cassandra is the right tool for me or not.

I am trying to understand some fundamentals in Cassandra, I was under the
impression that one of the advantages a developer can take in designing a
data model is by dynamically adding columns to a row identified by a key.
That means I can model my data so that if it makes sense, a key can be
something such as a user_id from a relational database, and I can for
example, create arbitrary amounts of columns that relate to that user.

What I'm not understanding is why there is so much emphasis to predefined
columns in CQL examples, particularly in the CREATE TABLE/COLUMNFAMILY
examples:

CREATE TABLE emp (

  empID int,

  deptID int,

  first_name varchar,

  last_name varchar,

  PRIMARY KEY (empID, deptID)

);

Wouldn't this type of model make more sense to just stuff into a relational
database? What if I don't know my column name until runtime and need to
dynamically create it? Do I have to use ALTER TABLE to add a new column to
the row using CQL? The particular app use-case I have in mind I would just
need a key identifier and arbitrary column names where the column name
might include a timestamp+variable_identifier. The whole point is that so I
can see have extremely wide rows at the wonderful performance that
Cassandra has to offer. As of right now, from everything I'm reading in
regards to DataStax recommending CQL over Thrift (I think what I'm
describing is possible with Thrift, but correct me if I'm wrong). That
means I'd have to go AGAINST the recommendation to a protocol that's pretty
much going to eventually not be supported.

Is Cassandra the right tool for that? Are the predefined columns in
documentation nothing more than an example? How does one add a dynamic
column name with an existing column family/table? If I'm stuck with static
columns, how is this any different than using a relational database such as
postgres or mysql? What I found really powerful about Cassandra is being
able to do something like the following in cassandra-cli which uses Thrift:


SET mycf[id]['arbitrary_column'] = 'foo';

However, doing that in CQL isn't possible. Completely limits the way I was
going to model my data for an application and would have no distinct
advantage over a relational database.


Please tell me I'm an idiot and/or am wrong and how I can make this work.
It seems Thrift is the only solution, but I hate going against the
recommended protocol.


Thanks.

--047d7bacc834d4974f04ddb3f8a7
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi all,<div><br></div><div style>I posted a similar thread=
 on stackoverflow - hope it&#39;s not repetitive for anyone here. Looking f=
or better insight from the community on whether Cassandra is the right tool=
 for me or not.</div>

<div style>


<p class=3D"">I am trying to understand some fundamentals in Cassandra, I w=
as under the impression that one of the advantages a developer can take in =
designing a data model is by dynamically adding columns to a row identified=
 by a key. That means I can model my data so that if it makes sense, a key =
can be something such as a user_id from a relational database, and I can fo=
r example, create arbitrary amounts of columns that relate to that user.</p=
>


<p class=3D"">What I&#39;m not understanding is why there is so much emphas=
is to predefined columns in CQL examples, particularly in the CREATE TABLE/=
COLUMNFAMILY examples:</p>
<p class=3D"">CREATE<span class=3D""> </span>TABLE<span class=3D""> emp (</=
span></p>
<p class=3D"">=A0 empID int,</p>
<p class=3D"">=A0 deptID int,</p>
<p class=3D"">=A0 first_name varchar,</p>
<p class=3D"">=A0 last_name varchar,</p>
<p class=3D"">=A0 <span class=3D"">PRIMARY</span> <span class=3D"">KEY</spa=
n> (empID, deptID)</p>
<p class=3D"">);</p>
<p class=3D"">Wouldn&#39;t this type of model make more sense to just stuff=
 into a relational database? What if I don&#39;t know my column name until =
runtime and need to dynamically create it? Do I have to use ALTER TABLE to =
add a new column to the row using CQL? The particular app use-case I have i=
n mind I would just need a key identifier and arbitrary column names where =
the column name might include a timestamp+variable_identifier. The whole po=
int is that so I can see have extremely wide rows at the wonderful performa=
nce that Cassandra has to offer. As of right now, from everything I&#39;m r=
eading in regards to DataStax recommending CQL over Thrift (I think what I&=
#39;m describing is possible with Thrift, but correct me if I&#39;m wrong).=
 That means I&#39;d have to go AGAINST the recommendation to a protocol tha=
t&#39;s pretty much going to eventually not be supported.</p>


<p class=3D"">Is Cassandra the right tool for that? Are the predefined colu=
mns in documentation nothing more than an example? How does one add a dynam=
ic column name with an existing column family/table? If I&#39;m stuck with =
static columns, how is this any different than using a relational database =
such as postgres or mysql? What I found really powerful about Cassandra is =
being able to do something like the following in cassandra-cli which uses T=
hrift:</p>

<p class=3D""><br></p><p class=3D"" style>SET mycf[id][&#39;arbitrary_colum=
n&#39;] =3D &#39;foo&#39;;</p><p class=3D"" style>However, doing that in CQ=
L isn&#39;t possible. Completely limits the way I was going to model my dat=
a for an application and would have no distinct advantage over a relational=
 database.</p>

<p class=3D"" style><br></p><p class=3D"" style>Please tell me I&#39;m an i=
diot and/or am wrong and how I can make this work. It seems Thrift is the o=
nly solution, but I hate going against the recommended protocol.</p><p clas=
s=3D"" style>

<br></p><p class=3D"" style>Thanks.</p></div></div>

--047d7bacc834d4974f04ddb3f8a7--