You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Sebastian Schmidt <is...@gmail.com> on 2014/04/16 11:43:00 UTC
Deleting column names
Hi,
I'm using a Cassandra table to store some data. I created the table like
this:
CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
PRIMARY KEY (s, p, o, c));
I need the at least the p column to be sorted, so that I can use it in a
WHERE clause. So as far as I understand, the s column is now the row
key, and (p, o, c) is the column name.
I tried to delete single entries with a prepared statement like this:
DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
That didn't work, because p is a primary key part. It failed during
preparation.
I also tried to use variables like this:
DELETE ?, ?, ? FROM table_name WHERE s = ?;
This also failed during preparation, because ? is an unknown identifier.
Since I have multiple different p, o, c combinations per s, deleting the
whole row identified by s is no option. So how can I delete a s, p, o, c
tuple, without deleting other s, p, o, c tuples with the same s? I know
that this worked with Thrift/Hector before.
Regards,
Sebastian
Re: Deleting column names
Posted by Eric Plowe <er...@gmail.com>.
Setting the columns to null is essentially deleting them from my
understanding. A delete operation works on the entire row.
On Monday, April 21, 2014, Andreas Wagner <
andreas.josef.wagner@googlemail.com> wrote:
> Hi cassandra users, hi Sebastian,
>
> I'd be interested in this ... is there any update/solution?
>
> Thanks so much ;)
> Andreas
>
> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>
>> Hi,
>>
>> I'm using a Cassandra table to store some data. I created the table like
>> this:
>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>> PRIMARY KEY (s, p, o, c));
>>
>> I need the at least the p column to be sorted, so that I can use it in a
>> WHERE clause. So as far as I understand, the s column is now the row
>> key, and (p, o, c) is the column name.
>>
>> I tried to delete single entries with a prepared statement like this:
>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>>
>> That didn't work, because p is a primary key part. It failed during
>> preparation.
>>
>> I also tried to use variables like this:
>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>
>> This also failed during preparation, because ? is an unknown identifier.
>>
>>
>> Since I have multiple different p, o, c combinations per s, deleting the
>> whole row identified by s is no option. So how can I delete a s, p, o, c
>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>> that this worked with Thrift/Hector before.
>>
>> Regards,
>> Sebastian
>>
>
>
Re: Deleting column names
Posted by Andreas Wagner <an...@googlemail.com>.
Thanks :) This works ...
Kind regards
Andreas
On 04/22/2014 06:05 PM, Laing, Michael wrote:
> Your understanding is incorrect - the easiest way to see that is to
> try it.
>
>
> On Tue, Apr 22, 2014 at 12:00 PM, Sebastian Schmidt <isibboi@gmail.com
> <ma...@gmail.com>> wrote:
>
> From my understanding, this would delete all entries with the
> given s. Meaning, if I have inserted (sa, p1, o1, c1) and (sa, p2,
> o2, c2), executing this:
>
> DELETE FROM table_name WHERE s = sa AND p = p1 AND o = o1 AND c = c1
>
> would delete sa, p1, o1, c1, p2, o2, c2. Is this correct? Or does
> the above statement only delete p1, o1, c1?
>
>
> 2014-04-22 4:00 GMT+02:00 Steven A Robenalt <srobenal@stanford.edu
> <ma...@stanford.edu>>:
>
> Is there a reason you can't use:
>
> DELETE FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>
>
> On Mon, Apr 21, 2014 at 6:51 PM, Eric Plowe
> <eric.plowe@gmail.com <ma...@gmail.com>> wrote:
>
> Also I don't think you can null out columns that are part
> of the primary key after they've been set.
>
>
> On Monday, April 21, 2014, Andreas Wagner
> <andreas.josef.wagner@googlemail.com
> <ma...@googlemail.com>> wrote:
>
> Hi cassandra users, hi Sebastian,
>
> I'd be interested in this ... is there any
> update/solution?
>
> Thanks so much ;)
> Andreas
>
> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>
> Hi,
>
> I'm using a Cassandra table to store some data. I
> created the table like
> this:
> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p
> BLOB, o BLOB, c BLOB,
> PRIMARY KEY (s, p, o, c));
>
> I need the at least the p column to be sorted, so
> that I can use it in a
> WHERE clause. So as far as I understand, the s
> column is now the row
> key, and (p, o, c) is the column name.
>
> I tried to delete single entries with a prepared
> statement like this:
> DELETE p, o, c FROM table_name WHERE s = ? AND p =
> ? AND o = ? AND c = ?;
>
> That didn't work, because p is a primary key part.
> It failed during
> preparation.
>
> I also tried to use variables like this:
> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>
> This also failed during preparation, because ? is
> an unknown identifier.
>
>
> Since I have multiple different p, o, c
> combinations per s, deleting the
> whole row identified by s is no option. So how can
> I delete a s, p, o, c
> tuple, without deleting other s, p, o, c tuples
> with the same s? I know
> that this worked with Thrift/Hector before.
>
> Regards,
> Sebastian
>
>
>
>
>
> --
> Steve Robenalt
> Software Architect
> HighWire | Stanford University
> 425 Broadway St, Redwood City, CA 94063
>
> srobenal@stanford.edu <ma...@stanford.edu>
> http://highwire.stanford.edu
>
> /
> /
>
>
>
>
>
Re: Deleting column names
Posted by "Laing, Michael" <mi...@nytimes.com>.
Your understanding is incorrect - the easiest way to see that is to try it.
On Tue, Apr 22, 2014 at 12:00 PM, Sebastian Schmidt <is...@gmail.com>wrote:
> From my understanding, this would delete all entries with the given s.
> Meaning, if I have inserted (sa, p1, o1, c1) and (sa, p2, o2, c2),
> executing this:
>
> DELETE FROM table_name WHERE s = sa AND p = p1 AND o = o1 AND c = c1
>
> would delete sa, p1, o1, c1, p2, o2, c2. Is this correct? Or does the
> above statement only delete p1, o1, c1?
>
>
> 2014-04-22 4:00 GMT+02:00 Steven A Robenalt <sr...@stanford.edu>:
>
> Is there a reason you can't use:
>>
>> DELETE FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>>
>>
>> On Mon, Apr 21, 2014 at 6:51 PM, Eric Plowe <er...@gmail.com> wrote:
>>
>>> Also I don't think you can null out columns that are part of the primary
>>> key after they've been set.
>>>
>>>
>>> On Monday, April 21, 2014, Andreas Wagner <
>>> andreas.josef.wagner@googlemail.com> wrote:
>>>
>>>> Hi cassandra users, hi Sebastian,
>>>>
>>>> I'd be interested in this ... is there any update/solution?
>>>>
>>>> Thanks so much ;)
>>>> Andreas
>>>>
>>>> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I'm using a Cassandra table to store some data. I created the table
>>>>> like
>>>>> this:
>>>>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>>>>> PRIMARY KEY (s, p, o, c));
>>>>>
>>>>> I need the at least the p column to be sorted, so that I can use it in
>>>>> a
>>>>> WHERE clause. So as far as I understand, the s column is now the row
>>>>> key, and (p, o, c) is the column name.
>>>>>
>>>>> I tried to delete single entries with a prepared statement like this:
>>>>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c =
>>>>> ?;
>>>>>
>>>>> That didn't work, because p is a primary key part. It failed during
>>>>> preparation.
>>>>>
>>>>> I also tried to use variables like this:
>>>>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>>>>
>>>>> This also failed during preparation, because ? is an unknown
>>>>> identifier.
>>>>>
>>>>>
>>>>> Since I have multiple different p, o, c combinations per s, deleting
>>>>> the
>>>>> whole row identified by s is no option. So how can I delete a s, p, o,
>>>>> c
>>>>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>>>>> that this worked with Thrift/Hector before.
>>>>>
>>>>> Regards,
>>>>> Sebastian
>>>>>
>>>>
>>>>
>>
>>
>> --
>> Steve Robenalt
>> Software Architect
>> HighWire | Stanford University
>> 425 Broadway St, Redwood City, CA 94063
>>
>> srobenal@stanford.edu
>> http://highwire.stanford.edu
>>
>>
>>
>>
>>
>>
>
Re: Deleting column names
Posted by Sebastian Schmidt <is...@gmail.com>.
>From my understanding, this would delete all entries with the given s.
Meaning, if I have inserted (sa, p1, o1, c1) and (sa, p2, o2, c2),
executing this:
DELETE FROM table_name WHERE s = sa AND p = p1 AND o = o1 AND c = c1
would delete sa, p1, o1, c1, p2, o2, c2. Is this correct? Or does the above
statement only delete p1, o1, c1?
2014-04-22 4:00 GMT+02:00 Steven A Robenalt <sr...@stanford.edu>:
> Is there a reason you can't use:
>
> DELETE FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>
>
> On Mon, Apr 21, 2014 at 6:51 PM, Eric Plowe <er...@gmail.com> wrote:
>
>> Also I don't think you can null out columns that are part of the primary
>> key after they've been set.
>>
>>
>> On Monday, April 21, 2014, Andreas Wagner <
>> andreas.josef.wagner@googlemail.com> wrote:
>>
>>> Hi cassandra users, hi Sebastian,
>>>
>>> I'd be interested in this ... is there any update/solution?
>>>
>>> Thanks so much ;)
>>> Andreas
>>>
>>> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm using a Cassandra table to store some data. I created the table like
>>>> this:
>>>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>>>> PRIMARY KEY (s, p, o, c));
>>>>
>>>> I need the at least the p column to be sorted, so that I can use it in a
>>>> WHERE clause. So as far as I understand, the s column is now the row
>>>> key, and (p, o, c) is the column name.
>>>>
>>>> I tried to delete single entries with a prepared statement like this:
>>>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c =
>>>> ?;
>>>>
>>>> That didn't work, because p is a primary key part. It failed during
>>>> preparation.
>>>>
>>>> I also tried to use variables like this:
>>>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>>>
>>>> This also failed during preparation, because ? is an unknown identifier.
>>>>
>>>>
>>>> Since I have multiple different p, o, c combinations per s, deleting the
>>>> whole row identified by s is no option. So how can I delete a s, p, o, c
>>>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>>>> that this worked with Thrift/Hector before.
>>>>
>>>> Regards,
>>>> Sebastian
>>>>
>>>
>>>
>
>
> --
> Steve Robenalt
> Software Architect
> HighWire | Stanford University
> 425 Broadway St, Redwood City, CA 94063
>
> srobenal@stanford.edu
> http://highwire.stanford.edu
>
>
>
>
>
>
Re: Deleting column names
Posted by "Laing, Michael" <mi...@nytimes.com>.
Referring to the original post, I think the confusion is what is a "row" in
this context:
So as far as I understand, the s column is now the *row *key
...
Since I have multiple different p, o, c combinations per s, deleting the whole
> *row* identified by s is no option
The s column is in fact the *partition_key*, not the row key, which is the
composite of all 4 columns (the partiton_key plus the clustering columns).
Deleting the row, as Steven correctly showed, will not delete the
partition, but only the row - the tuple of the 4 columns.
Terminology has changed with cql and we all have to get used to it.
ml
On Mon, Apr 21, 2014 at 10:00 PM, Steven A Robenalt
<sr...@stanford.edu>wrote:
> Is there a reason you can't use:
>
> DELETE FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>
>
> On Mon, Apr 21, 2014 at 6:51 PM, Eric Plowe <er...@gmail.com> wrote:
>
>> Also I don't think you can null out columns that are part of the primary
>> key after they've been set.
>>
>>
>> On Monday, April 21, 2014, Andreas Wagner <
>> andreas.josef.wagner@googlemail.com> wrote:
>>
>>> Hi cassandra users, hi Sebastian,
>>>
>>> I'd be interested in this ... is there any update/solution?
>>>
>>> Thanks so much ;)
>>> Andreas
>>>
>>> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>>>
>>>> Hi,
>>>>
>>>> I'm using a Cassandra table to store some data. I created the table like
>>>> this:
>>>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>>>> PRIMARY KEY (s, p, o, c));
>>>>
>>>> I need the at least the p column to be sorted, so that I can use it in a
>>>> WHERE clause. So as far as I understand, the s column is now the row
>>>> key, and (p, o, c) is the column name.
>>>>
>>>> I tried to delete single entries with a prepared statement like this:
>>>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c =
>>>> ?;
>>>>
>>>> That didn't work, because p is a primary key part. It failed during
>>>> preparation.
>>>>
>>>> I also tried to use variables like this:
>>>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>>>
>>>> This also failed during preparation, because ? is an unknown identifier.
>>>>
>>>>
>>>> Since I have multiple different p, o, c combinations per s, deleting the
>>>> whole row identified by s is no option. So how can I delete a s, p, o, c
>>>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>>>> that this worked with Thrift/Hector before.
>>>>
>>>> Regards,
>>>> Sebastian
>>>>
>>>
>>>
>
>
> --
> Steve Robenalt
> Software Architect
> HighWire | Stanford University
> 425 Broadway St, Redwood City, CA 94063
>
> srobenal@stanford.edu
> http://highwire.stanford.edu
>
>
>
>
>
>
Re: Deleting column names
Posted by Steven A Robenalt <sr...@stanford.edu>.
Is there a reason you can't use:
DELETE FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
On Mon, Apr 21, 2014 at 6:51 PM, Eric Plowe <er...@gmail.com> wrote:
> Also I don't think you can null out columns that are part of the primary
> key after they've been set.
>
>
> On Monday, April 21, 2014, Andreas Wagner <
> andreas.josef.wagner@googlemail.com> wrote:
>
>> Hi cassandra users, hi Sebastian,
>>
>> I'd be interested in this ... is there any update/solution?
>>
>> Thanks so much ;)
>> Andreas
>>
>> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>>
>>> Hi,
>>>
>>> I'm using a Cassandra table to store some data. I created the table like
>>> this:
>>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>>> PRIMARY KEY (s, p, o, c));
>>>
>>> I need the at least the p column to be sorted, so that I can use it in a
>>> WHERE clause. So as far as I understand, the s column is now the row
>>> key, and (p, o, c) is the column name.
>>>
>>> I tried to delete single entries with a prepared statement like this:
>>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>>>
>>> That didn't work, because p is a primary key part. It failed during
>>> preparation.
>>>
>>> I also tried to use variables like this:
>>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>>
>>> This also failed during preparation, because ? is an unknown identifier.
>>>
>>>
>>> Since I have multiple different p, o, c combinations per s, deleting the
>>> whole row identified by s is no option. So how can I delete a s, p, o, c
>>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>>> that this worked with Thrift/Hector before.
>>>
>>> Regards,
>>> Sebastian
>>>
>>
>>
--
Steve Robenalt
Software Architect
HighWire | Stanford University
425 Broadway St, Redwood City, CA 94063
srobenal@stanford.edu
http://highwire.stanford.edu
Re: Deleting column names
Posted by Eric Plowe <er...@gmail.com>.
Also I don't think you can null out columns that are part of the primary
key after they've been set.
On Monday, April 21, 2014, Andreas Wagner <
andreas.josef.wagner@googlemail.com> wrote:
> Hi cassandra users, hi Sebastian,
>
> I'd be interested in this ... is there any update/solution?
>
> Thanks so much ;)
> Andreas
>
> On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
>
>> Hi,
>>
>> I'm using a Cassandra table to store some data. I created the table like
>> this:
>> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
>> PRIMARY KEY (s, p, o, c));
>>
>> I need the at least the p column to be sorted, so that I can use it in a
>> WHERE clause. So as far as I understand, the s column is now the row
>> key, and (p, o, c) is the column name.
>>
>> I tried to delete single entries with a prepared statement like this:
>> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>>
>> That didn't work, because p is a primary key part. It failed during
>> preparation.
>>
>> I also tried to use variables like this:
>> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>>
>> This also failed during preparation, because ? is an unknown identifier.
>>
>>
>> Since I have multiple different p, o, c combinations per s, deleting the
>> whole row identified by s is no option. So how can I delete a s, p, o, c
>> tuple, without deleting other s, p, o, c tuples with the same s? I know
>> that this worked with Thrift/Hector before.
>>
>> Regards,
>> Sebastian
>>
>
>
Re: Deleting column names
Posted by Andreas Wagner <an...@googlemail.com>.
Hi cassandra users, hi Sebastian,
I'd be interested in this ... is there any update/solution?
Thanks so much ;)
Andreas
On 04/16/2014 11:43 AM, Sebastian Schmidt wrote:
> Hi,
>
> I'm using a Cassandra table to store some data. I created the table like
> this:
> CREATE TABLE IF NOT EXISTS table_name (s BLOB, p BLOB, o BLOB, c BLOB,
> PRIMARY KEY (s, p, o, c));
>
> I need the at least the p column to be sorted, so that I can use it in a
> WHERE clause. So as far as I understand, the s column is now the row
> key, and (p, o, c) is the column name.
>
> I tried to delete single entries with a prepared statement like this:
> DELETE p, o, c FROM table_name WHERE s = ? AND p = ? AND o = ? AND c = ?;
>
> That didn't work, because p is a primary key part. It failed during
> preparation.
>
> I also tried to use variables like this:
> DELETE ?, ?, ? FROM table_name WHERE s = ?;
>
> This also failed during preparation, because ? is an unknown identifier.
>
>
> Since I have multiple different p, o, c combinations per s, deleting the
> whole row identified by s is no option. So how can I delete a s, p, o, c
> tuple, without deleting other s, p, o, c tuples with the same s? I know
> that this worked with Thrift/Hector before.
>
> Regards,
> Sebastian