[
https://issues.apache.org/jira/browse/CASSANDRA-2474?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13087765#comment-13087765
]
Jonathan Ellis edited comment on CASSANDRA-2474 at 8/19/11 3:36 PM:
--------------------------------------------------------------------
I need to back up. Neither transposed() nor transposed(column) gives us the
power we need, unless we layer subqueries on top. Let me use a more complete
example to illustrate why.
Suppose we have a Twissandra data model where the timeline storage is done
"properly" in supercolumns. Then we might want to say, "give me the most
recent 50 tweets in user X's timeline:"
{code}
SELECT transposed(*) FROM timeline WHERE key=X LIMIT 50
{code}
So far so good. (Note again how this obviates the need for the existing,
cumbersome "column limit" syntax for ".." slices.)
But what if we just want, say, the username and timestamp from each entry?
{code}
SELECT transposed(username, timestamp) FROM timeline WHERE key=X LIMIT 50
{code}
That's where I was going with the multiple parameters to transposed().
Now, for the WHERE clause components... I guess for metastore-less Hive that's
ok. Or even, as you originally suggested, just leave it out and say "you can
only do full-CF scans without a C* metastore." I'm okay with that.
But for CQL we need to be able to give meaningful names to things, e.g., a row
full of supercolumns representing tweets could be "tweet_id" instead of
"supercolumn." So it would be nice to support this somehow in "full metastore
Hive."
This is important because we want to generalize beyond the current "one level
of nesting" you get with supercolumns, to arbitrary with composite columns. So,
{code}
SELECT transposed(tweet_id, -- supercolumn
username, timestamp) -- subcolumns
FROM timeline
WHERE user_id='jbellis' -- key
LIMIT 50
{code}
Another example. Suppose I have a "retweets" CF, where I have a composite
columns of (tweet_id, retweet_id). (You can think of this as simply
supercolumn/subcolumn, the difference being that composites won't deserialize
the entire parent container to fetch a single subcolumn or subcolumn slice.)
And I want the most recent retweets, for a given tweet:
{code}
SELECT transposed(retweet_id) -- second-level column name / subcolumn
FROM timeline
WHERE tweet_id=1b09d795-f038-4cf9-a996-5678fb092c3a -- first-level column name
/ supercolumn
AND user_id='jbellis' -- key
LIMIT 50
{code}
was (Author: jbellis):
I need to back up. Neither transposed() nor transposed(column) gives us
the power we need, unless we layer subqueries on top. Let me use a more
complete example to illustrate why.
Suppose we have a Twissandra data model where the timeline storage is done
"properly" in supercolumns. Then we might want to say, "give me the most
recent 50 tweets in user X's timeline:"
{code}
SELECT transposed(*) FROM foo WHERE key=X LIMIT 50
{code}
So far so good. (Note again how this obviates the need for the existing,
cumbersome "column limit" syntax for ".." slices.)
But what if we just want, say, the username and timestamp from each entry?
{code}
SELECT transposed(username, timestamp) FROM foo WHERE key=X LIMIT 50
{code}
That's where I was going with the multiple parameters to transposed().
Now, for the WHERE clause components... I guess for metastore-less Hive that's
ok. Or even, as you originally suggested, just leave it out and say "you can
only do full-CF scans without a C* metastore." I'm okay with that.
But for CQL we need to be able to give meaningful names to things, e.g., a row
full of supercolumns representing tweets could be "tweet_id" instead of
"supercolumn." So it would be nice to support this somehow in "full metastore
Hive."
This is important because we want to generalize beyond the current "one level
of nesting" you get with supercolumns, to arbitrary with composite columns. So,
{code}
SELECT transposed(tweet_id, -- supercolumn
username, timestamp) -- subcolumns
FROM timeline
WHERE user_id='jbellis' -- key
LIMIT 50
{code}
Another example. Suppose I have a "retweets" CF, where I have a composite
columns of (tweet_id, retweet_id). (You can think of this as simply
supercolumn/subcolumn, the difference being that composites won't deserialize
the entire parent container to fetch a single subcolumn or subcolumn slice.)
And I want the most recent retweets, for a given tweet:
{code}
SELECT transposed(retweet_id) -- second-level column name / subcolumn
FROM timeline
WHERE tweet_id=1b09d795-f038-4cf9-a996-5678fb092c3a -- first-level column name
/ supercolumn
AND user_id='jbellis' -- key
LIMIT 50
{code}
> CQL support for compound columns
> --------------------------------
>
> Key: CASSANDRA-2474
> URL: https://issues.apache.org/jira/browse/CASSANDRA-2474
> Project: Cassandra
> Issue Type: Sub-task
> Components: API, Core
> Reporter: Eric Evans
> Labels: cql
> Fix For: 1.0
>
>
> For the most part, this boils down to supporting the specification of
> compound column names (the CQL syntax is colon-delimted terms), and then
> teaching the decoders (drivers) to create structures from the results.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
