You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Wei Zhu <wz...@yahoo.com> on 2012/10/31 18:14:09 UTC
Create CF with composite column through CQL 3
I try to use CQL3 to create CF with composite columns,
CREATE TABLE Friends (
... user_id bigint,
... friend_id bigint,
... status int,
... source int,
... created timestamp,
... lastupdated timestamp,
... PRIMARY KEY (user_id, friend_id, status, source)
... );
When I check it with cli, the composite type is a bit odd, why it's defined as Long, Int32, Int32, UTF8, is it supposed to be Long, Long, Int32, Int32? Did
I do something wrong?
describe friends;
ColumnFamily: friends
Key Validation Class: org.apache.cassandra.db.marshal.LongType
Default column value validator: org.apache.cassandra.db.marshal.UTF8Type
Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(
org.apache.cassandra.db.marshal.LongType,
org.apache.cassandra.db.marshal.Int32Type,
org.apache.cassandra.db.marshal.Int32Type,
org.apache.cassandra.db.marshal.UTF8Type)
GC grace seconds: 864000
Compaction min/max thresholds: 4/32
Read repair chance: 0.1
DC Local Read repair chance: 0.0
Replicate on write: true
Caching: KEYS_ONLY
Bloom Filter FP chance: default
Built indexes: []
Compaction Strategy: org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy
Compression Options:
sstable_compression: org.apache.cassandra.io.compress.SnappyCompressor
Thanks.
-Wei
Re: Create CF with composite column through CQL 3
Posted by Timmy Turner <ti...@gmail.com>.
CQL3 handles columns/rows/keys differently than Cassandra itself
underneath. If you look closely you'll see that your real primary key
is in fact only a Long:
> Key Validation Class: org.apache.cassandra.db.marshal.LongType
If you want to know more about it, check these 2 articles:
http://www.datastax.com/dev/blog/cql3-for-cassandra-experts
http://www.datastax.com/dev/blog/thrift-to-cql3
(Also note that there are differences in CQL3 between 1.1 an 1.2.)
This caused a bit of confusion for myself too a few days ago. It
should really be documented somewhere in the wiki (as a CQL upgrade
guide or something), not just be mentioned in the Datastax blog.
2012/10/31 Wei Zhu <wz...@yahoo.com>:
> I try to use CQL3 to create CF with composite columns,
>
> CREATE TABLE Friends (
> ... user_id bigint,
> ... friend_id bigint,
> ... status int,
> ... source int,
> ... created timestamp,
> ... lastupdated timestamp,
> ... PRIMARY KEY (user_id, friend_id, status, source)
> ... );
>
>
> When I check it with cli, the composite type is a bit odd, why it's defined
> as Long, Int32, Int32, UTF8, is it supposed to be Long, Long, Int32, Int32?
> Did
> I do something wrong?
>
> describe friends;
> ColumnFamily: friends
> Key Validation Class: org.apache.cassandra.db.marshal.LongType
> Default column value validator:
> org.apache.cassandra.db.marshal.UTF8Type
> Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(
> org.apache.cassandra.db.marshal.LongType,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.UTF8Type)
> GC grace seconds: 864000
> Compaction min/max thresholds: 4/32
> Read repair chance: 0.1
> DC Local Read repair chance: 0.0
> Replicate on write: true
> Caching: KEYS_ONLY
> Bloom Filter FP chance: default
> Built indexes: []
> Compaction Strategy:
> org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy
> Compression Options:
> sstable_compression:
> org.apache.cassandra.io.compress.SnappyCompressor
>
> Thanks.
> -Wei
Re: Create CF with composite column through CQL 3
Posted by Wei Zhu <wz...@yahoo.com>.
Thanks Tristan and Sylvain, it all makes sense now.
One follow up question regarding the composite column. It seems that for the where clause I can only restrict the query on the first composite column (friend_id, in my case). I understand it's determined by the underlining row storage structure.
Is any plan to improve that to be able to search on the other composite columns if I don't care about the performance.
cqlsh:demo> select * from friends where source = 7;
Bad Request: PRIMARY KEY part source cannot be restricted (preceding part status is either not restricted or by a non-EQ relation)
Thanks.
-Wei
________________________________
From: Tristan Seligmann <mi...@mithrandi.net>
To: user@cassandra.apache.org; Wei Zhu <wz...@yahoo.com>
Sent: Wednesday, October 31, 2012 10:47 AM
Subject: Re: Create CF with composite column through CQL 3
On Wed, Oct 31, 2012 at 7:14 PM, Wei Zhu <wz...@yahoo.com> wrote:
> I try to use CQL3 to create CF with composite columns,
>
> CREATE TABLE Friends (
> ... user_id bigint,
> ... friend_id bigint,
> ... status int,
> ... source int,
> ... created timestamp,
> ... lastupdated timestamp,
> ... PRIMARY KEY (user_id, friend_id, status, source)
> ... );
>
>
> When I check it with cli, the composite type is a bit odd, why it's defined
> as Long, Int32, Int32, UTF8, is it supposed to be Long, Long, Int32, Int32?
The first component of the PRIMARY KEY (user_id) is the row key:
> Key Validation Class: org.apache.cassandra.db.marshal.LongType
The rest of the components (friend_id, status, source) are part of the
column name:
> Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(
> org.apache.cassandra.db.marshal.LongType,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.Int32Type,
and the final component of of the column name is the "CQL-level" column name:
> org.apache.cassandra.db.marshal.UTF8Type)
In this case, it will be "created" or "lastupdated" as those are the
only columns not part of the PRIMARY KEY.
--
mithrandi, i Ainil en-Balandor, a faer Ambar
Re: Create CF with composite column through CQL 3
Posted by Tristan Seligmann <mi...@mithrandi.net>.
On Wed, Oct 31, 2012 at 7:14 PM, Wei Zhu <wz...@yahoo.com> wrote:
> I try to use CQL3 to create CF with composite columns,
>
> CREATE TABLE Friends (
> ... user_id bigint,
> ... friend_id bigint,
> ... status int,
> ... source int,
> ... created timestamp,
> ... lastupdated timestamp,
> ... PRIMARY KEY (user_id, friend_id, status, source)
> ... );
>
>
> When I check it with cli, the composite type is a bit odd, why it's defined
> as Long, Int32, Int32, UTF8, is it supposed to be Long, Long, Int32, Int32?
The first component of the PRIMARY KEY (user_id) is the row key:
> Key Validation Class: org.apache.cassandra.db.marshal.LongType
The rest of the components (friend_id, status, source) are part of the
column name:
> Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(
> org.apache.cassandra.db.marshal.LongType,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.Int32Type,
and the final component of of the column name is the "CQL-level" column name:
> org.apache.cassandra.db.marshal.UTF8Type)
In this case, it will be "created" or "lastupdated" as those are the
only columns not part of the PRIMARY KEY.
--
mithrandi, i Ainil en-Balandor, a faer Ambar
Re: Create CF with composite column through CQL 3
Posted by Sylvain Lebresne <sy...@datastax.com>.
You didn't do anything wrong, it's how CQL3 works. I suggest having a
look at http://www.datastax.com/dev/blog/thrift-to-cql3. Especially
the section called "Non compact tables" might hopefully explains more.
--
Sylvain
On Wed, Oct 31, 2012 at 6:14 PM, Wei Zhu <wz...@yahoo.com> wrote:
> I try to use CQL3 to create CF with composite columns,
>
> CREATE TABLE Friends (
> ... user_id bigint,
> ... friend_id bigint,
> ... status int,
> ... source int,
> ... created timestamp,
> ... lastupdated timestamp,
> ... PRIMARY KEY (user_id, friend_id, status, source)
> ... );
>
>
> When I check it with cli, the composite type is a bit odd, why it's defined
> as Long, Int32, Int32, UTF8, is it supposed to be Long, Long, Int32, Int32?
> Did
> I do something wrong?
>
> describe friends;
> ColumnFamily: friends
> Key Validation Class: org.apache.cassandra.db.marshal.LongType
> Default column value validator:
> org.apache.cassandra.db.marshal.UTF8Type
> Columns sorted by: org.apache.cassandra.db.marshal.CompositeType(
> org.apache.cassandra.db.marshal.LongType,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.Int32Type,
> org.apache.cassandra.db.marshal.UTF8Type)
> GC grace seconds: 864000
> Compaction min/max thresholds: 4/32
> Read repair chance: 0.1
> DC Local Read repair chance: 0.0
> Replicate on write: true
> Caching: KEYS_ONLY
> Bloom Filter FP chance: default
> Built indexes: []
> Compaction Strategy:
> org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy
> Compression Options:
> sstable_compression:
> org.apache.cassandra.io.compress.SnappyCompressor
>
> Thanks.
> -Wei