You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Tony Anecito <ad...@yahoo.com> on 2013/07/17 05:44:59 UTC
Intresting issue with getting Order By to work...
Hi All,
Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went to insert data that worked fine on the cli created table I now get an exception:
Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
Thanks,
-Tony
Re: Intresting issue with getting Order By to work...
Posted by Tony Anecito <ad...@yahoo.com>.
Many thanks Vladimir I am starting to see what you are talking about.
Yeah all I want to do is a simple Order By via SQL but having the column family setup using CLI to support that is a bit of a challenge for me at the moment since the two are at different levels but I prefer CLI for column family setup and just need an example of one at CLI level setup that translates to working Order By at CQL level.
Many Thanks!
-Tony
________________________________
From: Vladimir Prudnikov <v....@gmail.com>
To: user@cassandra.apache.org
Sent: Thursday, July 18, 2013 3:54 AM
Subject: Re: Intresting issue with getting Order By to work...
I'm not an expert, still learning C* but can tell something about your questions.
1) You have to understand that CQL row is not the same as row that C* uses to store data and which is accessible through the Trift interface. Primary key in terms of CQL is not the same as Row key.
2) You have to be clear what you want to order: raw columns, rows or CQL rows. If you want to get ordered slices of raw rows you have to use Order Preserving Partitioner (which is not recommended, depends on you schema); If you want to order columns, it can be done easily; If you want to order CQL rows you have to have a composite primary key with at least 2 columns and you can order only by the second column in primary key.
3) As far as I know column families created from CLI or Thrift will be accessible from CQL, but not opposite.
I hope experts will correct me if I'm wrong.
On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
>>
>>> Hi All,
>>>
>>> Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
>>> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went
to insert data that worked fine on the cli created table I now get an exception:
>>> Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
>>>
>>> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
>>>
>>> I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
>>> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
>>>
>>> Thanks,
>>> -Tony
>>>
>>>
>>
>>
>>
>
--
Vladimir Prudnikov
Intresting issue with getting Order By to work...
Posted by Tony Anecito <ad...@yahoo.com>.
Hi All,
I am trying to have a table that I can have a key for one CQL to get a row based on a key and another CQL to where I have a list of keys using the "IN" keyword and have an ORDER BY clause. The data I am getting is from the Column value not the Column Name. The issue I am having is trying to setup the column family to achieve this. I have this cli command to create the column family but when I go do an insert I get a odd exception about the Primary key. Below is the cli and the error when doing the insert of column values after the column family was created.
The cli command used to create the column family:
create column family video_item_details
with key_validation_class = 'CompositeType(IntegerType,UTF8Type)'
and comparator = 'UTF8Type'
and column_metadata = [
{column_name : key, validation_class: IntegerType, index_type: KEYS}
{column_name : name, validation_class : 'UTF8Type', index_type: KEYS}
{column_name : description, validation_class :'UTF8Type'}
{column_name : url, validation_class : 'UTF8Type'}
{column_name : play_time, validation_class : 'UTF8Type'}
{column_name : type, validation_class : 'UTF8Type'}
]
with caching='ALL';
the error during insert is:
Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Missing mandatory PRIMARY KEY part key2
The insert statement that was being attempted to be executed was:
INSERT INTO video.video_item_details(key, name, description, url, play_time, type) values(1,'Gettysburg','Gettysburg Movie Trailer','http://localhost/test.avi','2:53','Streaming')
>From the links sent to me and the examples googling I have found only cli comands using CompositeType for comparitor and not validator and also for only one Primary Key and not a Compound Primary key using CompositeType. Thus I am stuck on something I feel should be a standard thing to do with a column family but no working examples.
Thanks for any advice on how to get Cassandra to do this type of query.
Best Regards,
-Tony
Re: Intresting issue with getting Order By to work...
Posted by Vladimir Prudnikov <v....@gmail.com>.
I'm not an expert, still learning C* but can tell something about your
questions.
1) You have to understand that CQL row is not the same as row that C* uses
to store data and which is accessible through the Trift interface. Primary
key in terms of CQL is not the same as Row key.
2) You have to be clear what you want to order: raw columns, rows or CQL
rows. If you want to get ordered slices of raw rows you have to use Order
Preserving Partitioner (which is not recommended, depends on you schema);
If you want to order columns, it can be done easily; If you want to order
CQL rows you have to have a composite primary key with at least 2 columns
and you can order only by the second column in primary key.
3) As far as I know column families created from CLI or Thrift will be
accessible from CQL, but not opposite.
I hope experts will correct me if I'm wrong.
On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
>
> > Hi All,
> >
> > Well I got most everything working I wanted using Cassandra then
> discovered I needed to use an Order By. I am using Cassandra 1.2.5.
> > The use of Order By requires Primary Key which appears to be only
> supported by by using CQL and not Cassandra-cli. So I dropped my table
> created uisng CLI and used CQL and was able to create a "Table". But when I
> went to insert data that worked fine on the cli created table I now get an
> exception:
> > Error while inserting
> com.datastax.driver.core.exceptions.InvalidQueryException: Unknown
> identifier type.
> >
> > I read that thrift clients will not work with CQL created tables due to
> extra things created by the CQL. If so how can I create Primary Keys and be
> supported by thrift based clients??
> >
> > I will post to Datastax the same question but trying to understand how
> to resolve cli vs CQL issue like this. Seems like Cassandra-cli should
> support creation of compound primary keys or CQL should create tables
> readable by thrift based clients. Is there some meta column info people
> should add?
> > Also CQL tables are not visible via cli.so I can not see details on what
> was created by CQL and the cqlsh script has errors according to the latest
> Python windows program I tried.
> >
> > Thanks,
> > -Tony
> >
> >
>
>
>
>
--
Vladimir Prudnikov
Re: Intresting issue with getting Order By to work...
Posted by Tony Anecito <ad...@yahoo.com>.
Hi Rob,
Thanks for the feedback. I had heard about this in regards to CQL created table not being visible to CLI but have not seen any examples of setting up CQL "table" to be visible by CLI.
Best Regards,
-Tony
________________________________
From: Robert Coli <rc...@eventbrite.com>
To: "user@cassandra.apache.org" <us...@cassandra.apache.org>; Tony Anecito <ad...@yahoo.com>
Sent: Thursday, July 18, 2013 10:16 AM
Subject: Re: Intresting issue with getting Order By to work...
On Thu, Jul 18, 2013 at 8:12 AM, Tony Anecito <ad...@yahoo.com> wrote:
As I work more with CQL and CLI as some other posting I have seen regarding usage I am thinking that CLI for keyspace and Column Family setup and maintenance is best
> while CQL for queries/inserts ect is best. Mainly I am thinking this because of better control over the schema using CLI.
The question is not really CQL vs CLI, it's COMPACT STORAGE vs. (NON-COMPACT) CQL storage. Picking one or the other strongly informs whether you want to also use Thrift or CQL (respectively) as an interface.
=Rob
Re: Intresting issue with getting Order By to work...
Posted by Robert Coli <rc...@eventbrite.com>.
On Thu, Jul 18, 2013 at 8:12 AM, Tony Anecito <ad...@yahoo.com> wrote:
> As I work more with CQL and CLI as some other posting I have seen
> regarding usage I am thinking that CLI for keyspace and Column Family setup
> and maintenance is best
> while CQL for queries/inserts ect is best. Mainly I am thinking this
> because of better control over the schema using CLI.
>
The question is not really CQL vs CLI, it's COMPACT STORAGE vs.
(NON-COMPACT) CQL storage. Picking one or the other strongly informs
whether you want to also use Thrift or CQL (respectively) as an interface.
=Rob
Re: Intresting issue with getting Order By to work...
Posted by Tony Anecito <ad...@yahoo.com>.
Many Thanks Aaron!!
As I work more with CQL and CLI as some other posting I have seen regarding usage I am thinking that CLI for keyspace and Column Family setup and maintenance is best
while CQL for queries/inserts ect is best. Mainly I am thinking this because of better control over the schema using CLI.
I will look over your links carefully.
-Tony
________________________________
From: aaron morton <aa...@thelastpickle.com>
To: Cassandra User <us...@cassandra.apache.org>
Sent: Thursday, July 18, 2013 2:21 AM
Subject: Re: Intresting issue with getting Order By to work...
Here are some posts about CQL and Thrift
http://thelastpickle.com/2013/01/11/primary-keys-in-cql/
http://www.datastax.com/dev/blog/cql3-for-cassandra-experts
http://www.datastax.com/dev/blog/thrift-to-cql3
Hope that helps.
-----------------
Aaron Morton
Cassandra Consultant
New Zealand
@aaronmorton
http://www.thelastpickle.com/
On 17/07/2013, at 11:38 PM, Tony Anecito <ad...@yahoo.com> wrote:
Thanks for the answers.
>
>The reason why I ask is it is stated the composite keys are not the same as Primary Key. I found no examples for thrift where it specifcally said the composite key is a primary key required by order by. All the examples where the words primary key were used were with CQL examples and I am seeing postings where people had issues with Order By but no answers like what you said.
>
>If there was better documentation for Cassandra with working examples and explnations about the differences between CQL and CLI I would not need to ask questions on the users groups. I have also spotted major issues and tried to help understand them for all users.
>
>-Tony
>
>
>
>________________________________
>From: aaron morton <aa...@thelastpickle.com>
>To: Cassandra User <us...@cassandra.apache.org>
>Sent: Wednesday, July 17, 2013 4:06 AM
>Subject: Re: Intresting issue with getting Order By to work...
>
>
>> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli.
>Order By in CQL is the also supported on the thrift interface.
>
>When using thrift the order you get the columns back is the order the Comparator puts them in. If you want them reversed the thrift API supports that.
>
>> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
>No.
>Do not access CQL tables with the thrift API.
>
>> Seems like Cassandra-cli should support creation of compound primary keys or
>It does.
>See help on the CompositeType
>
>> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
>They
are visible for read access.
>
>> I will post to Datastax the same question
>Please ask questions to one group at a time so people do not waste their time providing answers you already have.
>
>Cheers
>
>
>-----------------
>Aaron Morton
>Cassandra Consultant
>New Zealand
>
>@aaronmorton
>http://www.thelastpickle.com/
>
>On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
>
>> Hi All,
>>
>> Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
>> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went
to insert data that worked fine on the cli created table I now get an exception:
>> Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
>>
>> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
>>
>> I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
>> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
>>
>> Thanks,
>> -Tony
>>
>>
>
>
>
Re: Intresting issue with getting Order By to work...
Posted by aaron morton <aa...@thelastpickle.com>.
Here are some posts about CQL and Thrift
http://thelastpickle.com/2013/01/11/primary-keys-in-cql/
http://www.datastax.com/dev/blog/cql3-for-cassandra-experts
http://www.datastax.com/dev/blog/thrift-to-cql3
Hope that helps.
-----------------
Aaron Morton
Cassandra Consultant
New Zealand
@aaronmorton
http://www.thelastpickle.com
On 17/07/2013, at 11:38 PM, Tony Anecito <ad...@yahoo.com> wrote:
> Thanks for the answers.
>
> The reason why I ask is it is stated the composite keys are not the same as Primary Key. I found no examples for thrift where it specifcally said the composite key is a primary key required by order by. All the examples where the words primary key were used were with CQL examples and I am seeing postings where people had issues with Order By but no answers like what you said.
>
> If there was better documentation for Cassandra with working examples and explnations about the differences between CQL and CLI I would not need to ask questions on the users groups. I have also spotted major issues and tried to help understand them for all users.
>
> -Tony
>
> From: aaron morton <aa...@thelastpickle.com>
> To: Cassandra User <us...@cassandra.apache.org>
> Sent: Wednesday, July 17, 2013 4:06 AM
> Subject: Re: Intresting issue with getting Order By to work...
>
> > The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli.
> Order By in CQL is the also supported on the thrift interface.
>
> When using thrift the order you get the columns back is the order the Comparator puts them in. If you want them reversed the thrift API supports that.
>
> > I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
> No.
> Do not access CQL tables with the thrift API.
>
> > Seems like Cassandra-cli should support creation of compound primary keys or
> It does.
> See help on the CompositeType
>
> > Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
> They are visible for read access.
>
> > I will post to Datastax the same question
> Please ask questions to one group at a time so people do not waste their time providing answers you already have.
>
> Cheers
>
>
> -----------------
> Aaron Morton
> Cassandra Consultant
> New Zealand
>
> @aaronmorton
> http://www.thelastpickle.com/
>
> On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
>
> > Hi All,
> >
> > Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
> > The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went to insert data that worked fine on the cli created table I now get an exception:
> > Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
> >
> > I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
> >
> > I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
> > Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
> >
> > Thanks,
> > -Tony
> >
> >
>
>
Re: Intresting issue with getting Order By to work...
Posted by Tony Anecito <ad...@yahoo.com>.
Thanks for the answers.
The reason why I ask is it is stated the composite keys are not the same as Primary Key. I found no examples for thrift where it specifcally said the composite key is a primary key required by order by. All the examples where the words primary key were used were with CQL examples and I am seeing postings where people had issues with Order By but no answers like what you said.
If there was better documentation for Cassandra with working examples and explnations about the differences between CQL and CLI I would not need to ask questions on the users groups. I have also spotted major issues and tried to help understand them for all users.
-Tony
From: aaron morton <aa...@thelastpickle.com>
To: Cassandra User <us...@cassandra.apache.org>
Sent: Wednesday, July 17, 2013 4:06 AM
Subject: Re: Intresting issue with getting Order By to work...
> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli.
Order By in CQL is the also supported on the thrift interface.
When using thrift the order you get the columns back is the order the Comparator puts them in. If you want them reversed the thrift API supports that.
> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
No.
Do not access CQL tables with the thrift API.
> Seems like Cassandra-cli should support creation of compound primary keys or
It does.
See help on the CompositeType
> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
They are visible for read access.
> I will post to Datastax the same question
Please ask questions to one group at a time so people do not waste their time providing answers you already have.
Cheers
-----------------
Aaron Morton
Cassandra Consultant
New Zealand
@aaronmorton
http://www.thelastpickle.com/
On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
> Hi All,
>
> Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went to insert data that worked fine on the cli created table I now get an exception:
> Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
>
> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
>
> I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
>
> Thanks,
> -Tony
>
>
Re: Intresting issue with getting Order By to work...
Posted by aaron morton <aa...@thelastpickle.com>.
> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli.
Order By in CQL is the also supported on the thrift interface.
When using thrift the order you get the columns back is the order the Comparator puts them in. If you want them reversed the thrift API supports that.
> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
No.
Do not access CQL tables with the thrift API.
> Seems like Cassandra-cli should support creation of compound primary keys or
It does.
See help on the CompositeType
> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
They are visible for read access.
> I will post to Datastax the same question
Please ask questions to one group at a time so people do not waste their time providing answers you already have.
Cheers
-----------------
Aaron Morton
Cassandra Consultant
New Zealand
@aaronmorton
http://www.thelastpickle.com
On 17/07/2013, at 3:44 PM, Tony Anecito <ad...@yahoo.com> wrote:
> Hi All,
>
> Well I got most everything working I wanted using Cassandra then discovered I needed to use an Order By. I am using Cassandra 1.2.5.
> The use of Order By requires Primary Key which appears to be only supported by by using CQL and not Cassandra-cli. So I dropped my table created uisng CLI and used CQL and was able to create a "Table". But when I went to insert data that worked fine on the cli created table I now get an exception:
> Error while inserting com.datastax.driver.core.exceptions.InvalidQueryException: Unknown identifier type.
>
> I read that thrift clients will not work with CQL created tables due to extra things created by the CQL. If so how can I create Primary Keys and be supported by thrift based clients??
>
> I will post to Datastax the same question but trying to understand how to resolve cli vs CQL issue like this. Seems like Cassandra-cli should support creation of compound primary keys or CQL should create tables readable by thrift based clients. Is there some meta column info people should add?
> Also CQL tables are not visible via cli.so I can not see details on what was created by CQL and the cqlsh script has errors according to the latest Python windows program I tried.
>
> Thanks,
> -Tony
>
>