I now think the original idea from CASSANDRA-2025 of "SELECT columnA:x, columnA:y FROM foo WHERE key = 'bar'" is the wrong way to go. Instead, moving the compoundness-specifier to the "column parent" is a better fit:

SELECT x, y, FROM foo:bar WHERE parent='columnA'

(Note that "parent" would be a configurable alias, a la key_alias today.) This generalizes to deeper nesting, if we wish to support that:

select a, b FROM foo:bar:columnA where subparent='x'

This is both a better match for existing supercolumn semantics (so translation to StorageProxy requests is straightforward) as well as a better fit for APIs designed for SQL like JDBC.

seeing a key value (bar) in a FROM clause seems pretty unnatural for "SQL-like". And a column value in a WHERE clause? I see you are trying to make it cleaner and simpler to declare but it really stretches the semantics of the FROM and the WHERE clauses.

Remember that the point of this is to support materialized-view-in-a-row use cases. So essentially, we're turning a wide row into a skinny table – foo:bar is then the materialized view represented by one row of the original CF.

Doesn't this limit things to one row key?

If I have a CF of wide rows it would be nice to get back a transposed view of all rows..

you could use a SQL hint syntax like:

select /*+TRANSPOSED*/ key, column, subcolumn, value from foo;

Doesn't this limit things to one row key?

Right. This is fine, because

• row is the unit of what we can do efficiently, multiget causes a lot of foot-shooting
• query-within-row is exactly what you should be using mview rows for anyway, mixing data from different mviews? that has a code smell to it

you could use a SQL hint syntax

that doesn't seem nearly as natural to me, and there is no good way distinguish between non-transposed where expressions (for keys) and transposed (for columns/subcolumns?)

I see what you mean... some more questions/ideas.

1. are you suggesting we only allow access to supercolumns in a materialized view?
2. if not, how would non-materialized view access a supercolumn?
3. should we use a UNION command to fetch many rows?
4. why do you need to use the FROM cf:key syntax? why not require parent keyword to also have a key predicate?
5. would you allow "parent >= 'a' and parent <= 'c'"

1. materialized view is a way of thinking about it, I'm not saying we require declaring special mview metadata (I would like to, but I think people will want "manual" control)
3. I like that idea
4. not sure what you mean. SQL is about "give me this projection, from this relation, where this predicate is satisfied." putting it in the FROM (relation) part requires the least special casing for the rest (projection + predicate look normal). Since the relation part is the logical part for QueryProcessor to determine "am I doing a transposed query?" it simplifies implementation too.
5. yes, you'd get the full range of expressivity, limited only by what the engine can handle (e.g., in my first example, the current supercolumn engine doesn't index subcolumns so "AND x > 100" would not be efficient). but you can see how this gives us room to add more power w/o needing more syntax or special casing in the parser.

How about a nested SELECT? This is a familiar paradigm and allows you the staged isolation and no new syntax?

How about a nested SELECT

That's an interesting idea.

I work best with examples. So let's take my proposal

SELECT x, y FROM foo:bar WHERE parent='columnA'

and try to come up with a nested-select equivalent. IMO the most natural would be

SELECT x, y
FROM (SELECT * from foo where id='bar')
WHERE parent='columnA'

My problem with this is that the semantics are completely different than what I would expect from a relational system, and there is no cue telling me to expect that difference other than if I happen to know "nested-selects magically trigger transpose." As someone with a fairly long relational background this would drive me crazy.

More practically, I'd like to provide a single syntax for compound columns in both CQL and HQL (Hive's SQL) on Cassandra. HQL does support "real" nested selects so trying to figure out when a nested select should transpose vs when it should not seems like a non-starter to me.

This is not so straight forward in the Hive case.

In order to provide the foo:bar approach you need to play tricks in the HiveMetaStore since it will throw out that kind of table (it only knows about foo). Brisk does have a HiveMetaStore impl that we could do this in but it's separate from our hive driver. Meaning the two would need to assume they both exist inorder to work at all... Users of the Hive driver shouldn't be forced to only use it in brisk.

We could require Hive users create a table for each row but this is why I asked initially about supporting many rows. In the MapReduce case this is the norm... So I don't think the integration can be quite as seamless as you would like.

My suggestion is to start with the hive support and see what subset we can support in CQL rather than the other way.

My suggestion is to start with the hive support and see what subset we can support in CQL

So, how does Hive currently handle our example here?

Well the hive driver does something akin to the transpose I mentioned above.

Something that may work more natively with hive is
http://wiki.apache.org/hadoop/Hive/LanguageManual/LateralView

How about a bit of a hybrid of the Nested select and composite names:

SELECT ColumnA:x, ColumnA:y
FROM (SELECT * from foo where id='bar');

That implies the dataset to get the composite values for is the result of the nested select.

the hive driver does something akin to the transpose I mentioned above

Let's be specific here, because it's not at all clear to me how this works, especially the WHERE part.

Users of the Hive driver shouldn't be forced to only use it in brisk

Wouldn't it be fairly easy to create a MetaStore that wrapped any other MetaStore and taught it about the :row syntax?

Something that may work more natively with hive is LATERAL VIEW

LATERAL VIEW looks like it's designed to handle lists-in-a-column not subcolumns-in-a-row. I'm not optimistic that it would be easy to re-purpose here.

(What does HBase do for wide row support with Hive? Does it allow access to version information?)

Details of the current CF mapping in hive for static vs dynamic columns is here:

http://www.datastax.com/docs/0.8/brisk/about_hive#mapping-hive-external-tables-to-cassandra-column-families

HBase uses the Hive MAP<k,v> type to do this.

Search in http://wiki.apache.org/hadoop/Hive/HBaseIntegration for
"Hive MAP to HBase Column Family"

Wouldn't it be fairly easy to create a MetaStore that wrapped any other MetaStore and taught it about the :row syntax?

Sure but this would still require a custom hive build so users of say EMR wouldn't be able to use it... We might be able to make a stand alone hive metastore service to support the syntax but it feels like a bad solution...

I don't see what is so bad about mapping each column and subcolumn so its own row like the Cassandra Hive driver does today. This is the most natural fit for SQL.

Example:
key1 : { col1 : {subcol1:val1, subcol2: val2}}

maps to:

key, column, subcol, value
key1, col1, subcol1, val1
key1, col1, subcol2, val2

Details of the current CF mapping in hive for static vs dynamic columns is here:

No, that really doesn't give me details at all. All that tells me is that I can use magic, hard-coded column names to support wide rows with no subcolumns. (I assume subcolumns are supported too but this does not tell me now.)

I don't see what is so bad about mapping each column and subcolumn so its own row like the Cassandra Hive driver does today.

Because that ignores that the columns actually have structure of their own. Let's extend your example with another column:

key1: { col1:

, col2:

}

We want to map the key1 row as a non-sparse table:

key     subcol1    subcol2
col1    val1       val2
col2    v3         v4

The "N columns with M subcolumns become N*M mapped rows" makes getting real resultsets of columns-from-a-single "subrow" impossible, which has real impact on how you work with the data: how would you query for "all subrows[columns] where subcol1=v3" with the N*M model? You can't, because you've destroyed the connection between subcolumns in the same parent column.

(This is the same reason people always regret saying "I know, I'll bypass the problems of having to do ALTER TABLE in mysql by creating a table that has two columns, key and value.")

Note that the :bar notation allows dealing with dynamic, sparse subcolumns as well: but only within a single parent at a time, unless you do something like the UNION you propose. Which comes back to my original argument that this is the right level of granularity for the API to encourage, or you've screwed up your data model.

No, that really doesn't give me details at all. All that tells me is that I can use magic, hard-coded column names to support wide rows with no subcolumns. (I assume subcolumns are supported too but this does not tell me now.)

You didn't read it all then you can specify the following column mappings in cassandra.columns.mapping:

:key = row key
:column = column/supercolumn name
:subcolumn = subcolumn
:value = value

(This is the same reason people always regret saying "I know, I'll bypass the problems of having to do ALTER TABLE in mysql by creating a table that has two columns, key and value.")

I agree but this is why subselects work in the case of hive.

Which comes back to my original argument that this is the right level of granularity for the API to encourage, or you've screwed up your data model.

This is why I said it's hard todo in the case of Hive since you are going to be doing a map reduce on most/all the data anyway. Or are you arguing that we shouldn't allow access to all rows in hive?

you can specify the following column mappings in cassandra.columns.mapping

Okay, I get it. The reason there is no explanation of WHERE and resultsets is because these are not supported, for the reason deduced above in the N*M explanation.

So you are limited to the number of levels of nesting that you have magic names for?

you are going to be doing a map reduce on most/all the data anyway

The right way to represent a "select * from foo" with no compound modification is to present it with compound keys (in the SQL sense not in the sense of "multiple keys crammed into a single value"):

rowkey columnkey     subcol1    subcol2
key1         col1    val1       val2
key1         col2    v3         v4

So you are limited to the number of levels of nesting that you have magic names for?

correct. max of 4 when you transpose a wide row.

Regarding compound keys. that's nice since you retain types...

Would the rowkey and columnkey be automatically included in the result?
Is that a proposal for CQL?

Would the rowkey and columnkey be automatically included in the result

Yes, appropriately aliased, when doing a wildcard SELECT. Otherwise only columns (or aliases) explicitly requested should be included. In other words, you would get the above result either with "SELECT *" or "SELECT rowkey, columnkey, subcol1, subcol2" but "SELECT subcol1, subcol2" would only give the requested columns (and "SELECT rowkey, subcol1, subcol2" would also be honored).

In short, we minimize "magic" and don't assume we know better than the query author ("you didn't request the row key but I'm going to stick it in your resultset anyway").

Is that a proposal for CQL?

I'd view it as not nearly as important as the single-row case but I'm not against it.

This issue could stand to be summarized (I still wish we used a mailing list for monsters like this).

Summary: general agreement that "SELECT parent:column" notation is a bad fit. Some alternatives are presented. Most would require a Cassandra-aware Hive metastore to get the same syntax for Hive and Cassandra; Hive is primarily concerned with "select *" but Cassandra needs an effective way of representing "give me all the subcolumns for parent foo" (which is where the current HQL syntax is inadequate).

Coming back to this. Maybe the best idea so far is Jake's hint:

SELECT /*+TRANSPOSED*/ subcolumn, value FROM foo WHERE key=X and column=Y;

I have to say though that I still think this is ugly as sin. :-|

But I can't come up with anything better that doesn't require the Hive metastore.

Is the Hive driver flexible enough that we can add syntax?

SELECT TRANSPOSED subcolumn, value FROM foo WHERE key=X and column=Y;

I don't (yet) know how to add hint types to hive but once a transposed hint operator was added we should be able to hook it into the hive driver.

Isn't changing query semantics kind of the opposite of what hints are supposed to be for?

What about this? https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-BuiltinTableGeneratingFunctions%28UDTF%29

Could we write a UDTF such that we could write

SELECT transposed(subcolumn, value) FROM foo WHERE key=X AND column=Y

I'm guessing that would require some modifications to the UTDF code, it looks like it's single-column only now. But it shouldn't be much of a stretch to add support for more (he says sight unseen), and it's in the "spirit" of the existing UDTF goals.

I think the LATERAL VIEW stuff is kind of orthogonal to the UTDF per se. Basically a way to allow using normal columns a select with UTDF because otherwise their compiler is too dumb to handle mixing the two.

I think the best we can do with no major changes to HiveQL is:

  SELECT transposed(column) FROM foo WHERE key=X

This will return:
rowkey, subcol1, subcol2 ...

You could then also do supercol slices like transposed(column, start, end)

Looking at hive you can make functions return many columns:

http://massapi.com/source/hive-0.5.0-dev/src/contrib/src/java/org/apache/hadoop/hive/contrib/udtf/example/GenericUDTFExplode2.java.html

SELECT transposed(column) FROM foo WHERE key=X

Can we just write transposed() then instead of implying that there's actually a single column we're doing this to?

You could then also do supercol slices like transposed(column, start, end)

This isn't much of an improvement over the old slice syntax, from a CQL perspective. Here's why. If we have "real" WHERE clauses we get all the benefits of regularity that implies: we can get "traditional" slices

SELECT transposed() FROM foo WHERE key=X AND column >= X AND column <= Y

Or "paging" style (start-exclusive), possibly the single most-requested slicing feature:

SELECT transposed() FROM foo WHERE key=X AND column > X AND column <= Y

No special syntax needed for count(), other than I'm not sure we want to go full-on subquery here. The HQL options don't look great. Maybe we just add a transposed_count() function too):

SELECT count(*) FROM
(SELECT transposed() FROM foo WHERE key=X AND column > X AND column <= Y)

More natural support for just once slice "endpoint":

SELECT transposed() FROM foo WHERE key=X AND column >= X

Multi-row slicing:

SELECT transposed() FROM foo WHERE key IN (X, Y) AND column >= X AND column <= Y

Multi-slice from single row (CASSANDRA-2710):

SELECT transposed() FROM foo WHERE key=X AND ((column >= X AND column <= Y) OR (column >= A AND column <= B))

Composite column support (i.e., first-part-of-column-name is exactly X):

SELECT transposed() FROM foo WHERE key=X AND column=X

Future proof against hypothetical future CQL support for functions:

SELECT transposed() FROM foo WHERE key=X AND sqrt(column) < X

... and of course combinations of the above. Adding functions for even just the examples here would be ugly, let alone further mix-and-matching.

Understood, thx.

The core issue is we can't work around using special keywords like column and key in the hive predicates without altering the hive metastore.

What I think we can do to get us closer is create some UDFs to replace the Keywords so you could do:

  SELECT transposed() FROM foo WHERE key()=X AND column() > X and column() < Y;

These UDFs would hook into the cassandra hive driver and return the correct row or column depending on the cursor.

I think we need to do a POC to verify if this is truly possible but I believe it just might work.
For CQL we could obviously not require the () and use actual keywords.

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:"

SELECT transposed(*) FROM timeline WHERE key=X LIMIT 50

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?

SELECT transposed(username, timestamp) FROM timeline WHERE key=X LIMIT 50

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,

SELECT transposed(tweet_id, -- supercolumn
                  username, timestamp) -- subcolumns
FROM timeline 
WHERE user_id='jbellis' -- key
LIMIT 50

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:

SELECT transposed(retweet_id) -- second-level column name / subcolumn
FROM retweets 
WHERE tweet_id=1b09d795-f038-4cf9-a996-5678fb092c3a -- first-level column name / supercolumn
  AND user_id='jbellis' -- key
LIMIT 50

In your last example how does the query parser know that tweet_id refers to the first level column / supercolumn? In twissandra the uuid is the supercolumn name.

That would come from the schema, similar to how we have key_alias.

(Implementation TBD, especially for "dynamic composite columns." For those we might just have to reserve component1, component2, etc. names)

Rather than trying to support all this flexibility on the fly in Hive, why don't we let users create transposed projections in the DML?

I think this is reasonable vs CQL language because CQL is the source of truth for the DML where-as Hive refers to the meta info explicitly.

Example for timeseries supercol wide rows:

CREATE EXTERNAL TABLE timeline(user_id string, tweet_id long, username string, timestamp long)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      WITH SERDEPROPERTIES ( "cassandra.columns.mapping" = ":key,:supercol,:subcol:uname,:subcol:ts")    

:key, :supercol, :subcol are reserved keywords

Example for composite/supercolumn level:

CREATE EXTERNAL TABLE retweets(user_id string, tweet_id long, retweet_id long)  
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      WITH SERDEPROPERTIES ( "cassandra.columns.mapping" = ":key,retweet:component1,retweet:component2)

This means you might have to create many projections on the source data in Hive to support your analysis but this feels more OLAP than the CQL transpose on the fly approach.

Also, we could still support the transpose() function syntax using a passthrough UTF so the same syntax works across CQL and HiveQL

Rather than trying to support all this flexibility on the fly in Hive, why don't we let users create transposed projections in the DML?

I like that idea, as the next best thing to finding a syntax that works well for both Hive and CQL.

So for CQL, I think we're close with the "transposed" idea. As explained above, this allows using "'real' WHERE clauses [so] we get all the benefits of regularity that implies."

I did note one problem with the transposed(x, y, z) syntax, though: you lose the ability to compose with other functions in the SELECT clause. Currently that just means count(), but it's conceivable that we'd add scalar functions like sqrt(), or even aggregation like sum().

So I propose adding ".transposed" to the table name instead:

SELECT tweet_id, username, timestamp
FROM timeline.transposed 
WHERE user_id='jbellis'
LIMIT 50

That sounds good, except I think somthing.somthing is the database.table syntax so might need to pick a different delimiter? ":" ?

table:transposed works for me. (and actually is the delimiter we already use for "compound" stuff in the ALTER api, for what it's worth.)

Illustration time. (See: screenshot-1.jpg)

We have a "wide row" of stock histories. Just "normal" columns, no composites or supercolumns.

We could use transposition syntax to get these as "normal" (not sparse) resultset:

SELECT date, price
FROM stockhist
WHERE symbol='GOOG'
  AND date >= '2011-01-02'
LIMIT 50

Which would yield

date        price
2011-01-02  $75.23
2011-01-03  $82.11

As mentioned above, we need to figure out how to allow adding the date/price aliases to refer to column name/value, and extend that to the more general composite column case.

How about we do syntax like this:

for Standard columns

SELECT :column as date, :value as price FROM stockhist WHERE <key_alias>='GOOG' AND date >= '2011-01-02' LIMIT 50

for Super columns

SELECT :parent as company, :column as date, :value as price FROM stockhist WHERE company='GOOG' AND date >= '2011-01-02' AND <key_alias> = '...' LIMIT 50

for composite columns

SELECT :component as company, :component as date, :value as price FROM stockhist WHERE company='GOOG' AND date >= '2011-01-02' AND <key_alias> = '...' LIMIT 50

we can extend that syntax like this to support multiple columns:

for composite columns:

SELECT :column as (date, company), :value as price FROM stockhist WHERE <key_alias>='GOOG' AND date >= '2011-01-02' LIMIT 50

for super columns:

SELECT :parent as company, :column as (date, <attr>), :value as (price, <attr_value>) FROM stockhist WHERE company='GOOG' AND date >= '2011-01-02' AND <key_alias> = '...' LIMIT 50

Also instead of :column we could use just :name, it might be even cleaner.

That's a good start. I have two suggestions:

• we probably don't want the colons in the default aliases
• it would be good to make composite columns less of a special case. Instead of "column" for a normal column and "parent/column" for supercolumns and "component1..componentN" for composite, use component1 for normal, component1/2 for super, component1/N for composite.

So we'd end up with

SELECT component1 AS date, value AS price
FROM stockhist
WHERE symbol='GOOG'
  AND date >= '2011-01-02'
LIMIT 50

(I think we could put this in column_metadata, too: column_metadata=[

]. Thinking out loud here... let's save that for later if we do it at all.)

Composite columns are tricky because there's more than one way to "encode" data. Consider screenshot-2, illustrating the twitter timeline example from earlier. This shows 3 ways of encoding the same data; first as supercolumns, then as two different composite column encodings. The supercolumn and first composite encoding would look like this:

SELECT component1 AS tweet_id, username, value AS body
FROM timeline
WHERE user_id = '95a789a'

The second composite encoding though would be

SELECT component1 AS tweet_id, component2 AS username, value AS body
FROM timeline
WHERE user_id = '95a789a'

(Note that the first composite encoding is sparse, like the supercolumn version. The second is dense. This means that it's more fragile when it comes to extending it – if I wanted to add a location tweet field, I'd need to delete (e.g.) the 2e1c3308,cscotta column and replace it with 2e1c3308,cscotta,<location>. So, generally not recommended... but the composite column spec implies we should support it.)

Which brings us to: how to deal with insert/update in composite columns.

we probably don't want the colons in the default aliases

I think we better have them so users won't get confused

it would be good to make composite columns less of a special case. Instead of "column" for a normal column and "parent/column" for supercolumns and "component1..componentN" for composite, use component1 for normal, component1/2 for super, component1/N for composite.

Maybe it is better if we will support (..,..,..) notation for composite columns, replace :column with :name and leave :parent for supercolumns? It will allow us to drop special casing. As I mentioned in my previous comment:

SELECT :name as (date, company), :value as price FROM stockhist WHERE <key_alias>='GOOG' AND date >= '2011-01-02' LIMIT 50

and

SELECT :parent as company, :name as (date, <attr>), :value as price FROM stockhist WHERE company='GOOG' AND date >= '2011-01-02' AND <key_alias> = '...' LIMIT 50

I'm not very familiar with composite columns by from what I can see we are forced to work with what we have in the statement without re-creation because even in your example when we will try to add a new <location> field it will be very hard to guess how column name was looking previously...

I think we better have them so users won't get confused

We already have the transposition as a visual "hint" as to what is going on. I don't think it's difficult to say that "you need to use componentX when using transposition," and :colon tokens are usually used to represent bind variables in SQL drivers.

Maybe it is better if we will support (..,..,..) notation for composite columns

What does this buy us?

even in your example when we will try to add a new <location> field it will be very hard to guess how column name was looking previously

Sorry, I don't follow.

Note: Eric Evans on IRC suggested that we simplify this by only supporting "normal" and composite columns, leaving out supercolumns until a future release where supercolumns are reimplemented with composites under the hood. That seems reasonable to me, and should simplify implementation.

We already have the transposition as a visual "hint" as to what is going on. I don't think it's difficult to say that "you need to use componentX when using transposition," and :colon tokens are usually used to represent bind variables in SQL drivers.

I didn't use ":transposed" keyword any where but I'm file using it instead of :colon tokens. I don't like "componentX" notation because we can simply preserve ordering of the arguments to make the same thing which is much clearer syntax on my opinion

Maybe it is better if we will support (..,..,..) notation for composite columns

Can be used as a replacement for "componentX"

Sorry, I don't follow.

You wrote:

If I wanted to add a location tweet field, I'd need to delete (e.g.) the 2e1c3308,cscotta column and replace it with 2e1c3308,cscotta,<location>. So, generally not recommended... but the composite column spec implies we should support it.) Which brings us to: how to deal with insert/update in composite columns.

I mean when we want to extend "2e1c3308,cscotta" with <location> we can simply update old column's name to "2e1c3308,cscotta,<location>", for querying we can use "1/0/-1" as <'end-of-component' byte> from CompositeType doc when we want to search by specific components of the column name.

e.g. (if I understand CompositeType annotation correctly)

SELECT name AS (tweet_id, username), value AS body
FROM timeline
WHERE tweet_id = '95a789a' AND user_id = 'cscotta'

start = "<7>95a789a.getBytes()<0><7>cscotta.getBytes()<0>"
end = "<7>95a789a.getBytes()<0><7>cscotta.getBytes()<1>"

Which should give as all columns where column name starts with 95a789a,cscotta

SELECT name AS (tweet_id, username, location), value AS body
FROM timeline
WHERE tweet_id = '95a789a' AND user_id = 'cscotta' AND location = 'USA'

start = "<7>95a789a.getBytes()<0><7>cscotta.getBytes()<0><3>USA.getBytes()<0>"
end = "<7>95a789a.getBytes()<0><7>cscotta.getBytes()<0><3>USA.getBytes()<1>"

Which should give as all columns where column name starts with 95a789a,cscotta,USA

What happened to the table:transposed syntax?

Can be used as a replacement for "componentX"

I think the goal should be to make it look more like SQL when that is reasonable.

when we want to extend "2e1c3308,cscotta" with <location> we can simply update old column's name to "2e1c3308,cscotta,<location>"

That would imply a "rename column" operation. I don't want to go there.

I think the goal should be to make it look more like SQL when that is reasonable.

I hope that the main goal here is to give end-user clean syntax instead of trying to be as SQL as possible in all the cases.

That would imply a "rename column" operation. I don't want to go there.

I guess I described it wrong - we can insert new column with name "2e1c3308,cscotta,<location>" and still use "1/0/-1" as <'end-of-component' byte> for querying. Is there any reason why we should delete "2e1c3308,cscotta" I'm missing?

I hope that the main goal here is to give end-user clean syntax instead of trying to be as SQL as possible in all the cases.

Please give an example then, preferably using either the tweet or stockhist examples, of how this syntax improves things.

Is there any reason why we should delete "2e1c3308,cscotta" I'm missing?

Yes, because the goal is to go from a resultset like this

tweet_id  username  body
2e1c3308  cscotta   Brother...
2debb66c  Werner    Why ...
2f941d9a  hlship    Igor ...

To this:

tweet_id  username  body        location
2e1c3308  cscotta   Brother...  CA
2debb66c  Werner    Why ...
2f941d9a  hlship    Igor ...

Not this:

tweet_id  username  body        location
2e1c3308  cscotta   Brother...  CA
2e1c3308  cscotta   Brother...
2debb66c  Werner    Why ...
2f941d9a  hlship    Igor ...

Please give an example then, preferably using either the tweet or stockhist examples, of how this syntax improves things.

Using "ComponentX"

SELECT component1 AS tweet_id, component2 AS username, component3 location, value AS body
FROM timeline:transposed
WHERE user_id = '95a789a'

vs. (..,..,..) notation from my previous comments

SELECT name AS (tweet_id, username, location), value AS body
FROM timeline:transposed
WHERE tweet_id = '95a789a'

Yes, because the goal is to go from a resultset like this

Oh, I see now, we will need to delete old column any time we would need to add or remove name component...

Ah, I see. Sure, that's reasonable. I like it. But how do you apply that to the first composite model, with the "sparse" fields?

For sparse field a propose following syntax

to list exact aliases you want to include into result

SELECT name as (tweet_id, username|body) ....

if you want to return any component names ? can be used

SELECT name as (tweet_id, ?) ....

That is going to return username, body and location

Star would be better... And ? would conflict with PreparedStatement type data binding.

I don't have anything against using star .

Remember that the ideal for CQL is to have "SELECT x, y, z" and get back exactly columns x, y, and z. The farther we get from that the more conceptual debt we incur.

SELECT name AS (tweet_id, username, location), value AS body

This is probably acceptable because many people are familiar with destructuring assignment and the intuition from that is accurate here: you have a single [cassandra] column, that gets destructured into multiple [resultset] columns.

SELECT name as (tweet_id, username|body) ....

But this doesn't match intuition for either SQL, or destructuring assignment. Because what you're doing is actually turning multiple cassandra columns, into a single row. I think the component syntax does a better job of describing this – you use "componentX" in the composite tree until you get to the "parent" of the named fields, and then you can use those names directly.

I think we should go with the component syntax for now (since it can handle both sparse and dense) and consider adding the destructuring syntax for dense encodings later.

Remember that the ideal for CQL is to have "SELECT x, y, z" and get back exactly columns x, y, and z.

composite/super columns won't originally play nice with SQL syntax because it wasn't designed to query hierarchical data.

Few problems I have with componentX syntax:

• if we have 10 subcolumns do I need to list them all using component syntax (which would be totally unreadable)?
• it lacks scoping therefore on the big queries it will be hard to read
  e.g.

  SELECT component1 AS tweet_id, component2 AS username, body, location, age, value AS body
  

• will potentially be hard to put into grammar because it can have ambiguous rules again because lack of scoping
• why should we force users to actually give each component a number?

And I don't get why do you think that (..,..,..) is a "rocket science" syntax:

If we presume that user should be familiar with composite type columns before start using the syntax then he will know what does each section (separated by ",") mean:

SELECT name AS (tweet_id, username, location), value AS body

means that we have three sections as column name which we are aliasing to tweet_id, username, location

SELECT name AS (tweet_id, username | body | location | age), value AS body

means that we have two components in the name: first one - tweet_id, and second component that has multiple meanings but we only want to get username, body, location

SELECT name AS (tweet_id, *), value AS body

means that we still have two components in the column name but we don't care what holds component #2 and we expect result set to return all of the possible values.

The keyword "AS" may be a bit confusing to SQL users. It is a optional keyword that signifies that that the following word is to be taken has an alias for the first word. In CQL context it would more appropriately be used for giving a text label to a numeric (or whatever) column name. I readily admit I do not really understand how this feature works. Even if it is used, it would seem you would declare it in the reverse order? Like:

SELECT column_name AS alias FROM CF;

Could "WITH" be a substitute for "AS" in this compound context?

I think that "as" is more appropriate than "with" in compound column context because we use name "as" collection of components (aliases).

My point is that the familiar usage would be that the alias is the "manufactured" name that does not actually exist in the row and so it should be after the AS as opposed to before. Note too that in SQL the "AS" is optional, the alias is recognized positionally as following the column identifier if present.

I understand your point but semantically "with" is not appropriate here.

I do agree with Eric earlier on, I think this issue could stand being summarized, I'm not too sure I understand what is proposed here so far. So I apologize in advance if it turns out the propositions made above do answer everything that is below.

However, it seems that we're focusing on some representation based on materialized views here. Did we focus on that because we consider the basic use cases for composite type, those where we don't use them for materialized view at all, are easy to deal with ?

Why not consider composite column name for what they are, one column name that is composed of multiple sub-elements ? What I mean here is, I'm not that sure I'm convinced that

the original idea from CASSANDRA-2025 of "SELECT columnA:x, columnA:y FROM foo WHERE key = 'bar'" is the wrong way to go

I'm even less convinced when I see the number of comments on this ticket.

Again, there seems that the focus was exclusively on materialized views, but I strongly think that composite column names are useful for more than materialized view (I've used composite column names countless time, never for materialized view).

But let's take an example of what I mean. Suppose that what you store in your column family are events. Those events arrive with a timestamp whose resolution is maybe the minute (or more precisely, you only care about query them at that precision). Those events have a category (that may have a sorting that make sense), and maybe a subcategory. They also have a unique identifier eventId. Moreover there is a lot of events every minutes and the category/subcategory are not necessarily predefined. The query you want to do are typically:

• Give me all the events for time t, category c and sub-category sc.
• Give me all the events for time t and category c.
• Give me all the events for time t and category c1 to c2 (where c1 < c2 for the category sorting)
• Give me everything for the last 4 hours
  Probably most of those would requires paging because there is shit tons of events but still, I want to do those fast.

I haven't found a better data model for that kind of example than using a composite column name where the name is (timestamp, category, sub-category, eventId).

I haven't found in all the discussion above anything that would allow me to do this better than what is in the initial proposition of CASSANDRA-2025.

Now I completely agree that having a good notation to work with materialized view would be great, but IMO if we try to find a syntax that is too far from how composite columns work, I fear we'll end up limiting the usefulness of composite types in CQL to one narrow use case.

I'll note too that I haven't seen any proposal of how insertion with compound types should look like.

if we consider that timestamp is key and event_id, category and subcategory is composite name then:

Give me all the events for time t, category c and sub-category sc

SELECT name AS (event_id, category, subcategory), value AS event FROM events WHERE key = <timestamp> AND category = <name> AND subcategory = <name>;

Give me all the events for time t and category c

SELECT name AS (event_id, category, *), value AS event FROM events WHERE key = <timestamp> AND category = <name>;

Give me all the events for time t and category c1 to c2 (where c1 < c2 for the category sorting)

SELECT name AS (event_id, category, *), value AS event FROM events WHERE key = <timestamp> AND category > c1 AND category < c2;

Give me everything for the last 4 hours

SELECT name AS (event_id, category, *), value AS event FROM events WHERE key > <timestamp> AND key < <timestamp>;

Well, the timestamp was not meant to be the key in my example and the event_id needs to be the last component for this to make sense (since it is not specified in the query) but ok..

Now, I don't understand how:

SELECT name AS (category, subcategory, *), value AS event FROM events WHERE key = <timestamp> AND category = <category> AND subcategory = <subcat>;

is fundamentally different from

SELECT <category>:<subcat>:<event_id>, value FROM events WHERE key = <timestamp>;

which is roughly the proposition from CASSANDRA-2025. And I mean fundamentally different, not just from a syntax point of view (I have nothing against using parenthesis). If if it just a syntax difference, then fine.

Or how

SELECT name AS (category, *), value AS event FROM events WHERE key = <timestamp> AND category > c1 AND category < c2;

is fundamentally different from

SELECT c1:*..c2:*, value FROM events WHERE key = <timestamp>;

Maybe giving an example of what is supposed to be the returned would start to show the differences, but so far it seems only a difference of syntax. And the discussions above suggests that there is more than that underneath.

The core difference is that (..,..,..) notation will return given aliases (category, subcategory) as column names in the results.

The core difference is that (..,..,..) notation will return given aliases (category, subcategory) as column names in the results

But how will it do that ?

The result of

SELECT c1:*..c2:*, value FROM events WHERE key = <timestamp>;

would be something like

Key         | c1:subc1     | c1:subc2     | c1:subc3     | c2:subc1     |
<timestamp> | event_value1 | event_value2 | event_value3 | event_value4 |

How do the result look like with 'given aliases (category, subcategory) as column names in the results' ?

composite/super columns won't originally play nice with SQL syntax because it wasn't designed to query hierarchical data

That's exactly what "transposition" solves – taking a horizontal slice and "turning it sideways" into a resultset with the same set of columns.

We are NOT trying to solve a more generic hierarchical data problem – all the ("leaf") data we select has to be at the same "level" in the hierarchy.

if we have 10 subcolumns do I need to list them all using component syntax

You will if you are using the "dense" format. And let's be clear: this is NOT the recommended way to do things, because it is fragile, as described above. We want to support it [dense], but making it beautiful is not our goal. Sparse encoding will be the recommended practice.

it lacks scoping therefore on the big queries it will be hard to read, e.g. "SELECT component1 AS tweet_id, component2 AS username, body, location, age, value"

I don't understand, that seems perfectly readable to me.

SELECT name AS (tweet_id, username | body | location | age), value AS body

This syntax is not viable for the reasons given in my previous comment. I'm happy to entertain other alternatives to the component syntax but there's no need to spend further time on this one.

@Sylvain

it is going to return

Key         | category          | subc1        | subc2        | subc3         |
<timestamp> | c1                | event_value1 |              | event_value3  |
<timestamp> | c2                | event_value4 | event_value2 |               |

@Jonathan

I don't understand, that seems perfectly readable to me. This syntax is not viable for the reasons given in my previous comment. I'm happy to entertain other alternatives to the component syntax but there's no need to spend further time on this one.

I don't seem that readable to me. We can resolve this question by writing email to the cassandra mailing list and let people decide.

Reaching back into the past to reply to Rick:

The keyword "AS" may be a bit confusing to SQL users. It is a optional keyword that signifies that that the following word is to be taken has an alias for the first word.

That's exactly how it's being used in [my proposals for] CQL: giving an alias to a column ("componentX") that isn't very meaningful in a resultset.

I haven't found a better data model for that kind of example than using a composite column name where the name is (timestamp, category, sub-category, eventId).

This is exactly the "dense" composite column case we've been discussing.

I make three claims:

• A more Cassandra-ish way to model this would be to encode this as a series of columns: (<timestamp>, 'category', <category>), (<timestamp>, 'subcategory', <subcategory>), (<timestamp>, 'event', <eventId>). This is better in the general case for the same reason that a sparse top-level set of columns is better: I can easily add more data to events (e.g., "source") without rewriting existing events.
• The component syntax is much, much better at handling sparse queries, and adequate for handling dense ones; the destructuring syntax is terrible at sparse queries while good at dense ones, and the : syntax doesn't handle sparse queries at all, or at least nobody's put forth an explanation of how that would work.
• the .. syntax is a bad fit for CQL and should be deprecated in favor of transposed queries.

Here is how we can do your examples with the component syntax and transposition. I'm using "sparse" encoding here, but as described above "the component proposal" handles dense as well, just with more "AS" entries.

• Give me all the events for time t, category c and sub-category sc.

  SELECT component1 as timestamp, category, subcategory, event
  FROM events.transposed
  WHERE timestamp = ? AND category = ? AND subcategory = ?
  

• Give me all the events for time t and category c1 to c2 (where c1 < c2 for the category sorting)

  SELECT component1 as timestamp, category, subcategory, event
  FROM events.transposed
  WHERE timestamp = ? 
    AND c1 < category AND category <= c2
  

• Give me everything for the last 4 hours
  SELECT component1 as timestamp, category, subcategory, event
  FROM events.transposed
  WHERE timestamp > ?

(If you're just joining us, the reason .. syntax is bad is that it's a one-off that doesn't allow the full expresivity you get with WHERE clauses – I gave some examples in https://issues.apache.org/jira/browse/CASSANDRA-2474?focusedCommentId=13087705&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-13087705)

We can resolve this question by writing email to the cassandra mailing list and let people decide

More bikeshedding by people who know less about the subject isn't going to help here.

A more Cassandra-ish way to model this would be to encode this as a series of columns: (<timestamp>, 'category', <category>), (<timestamp>, 'subcategory', <subcategory>), (<timestamp>, 'event', <eventId>). This is better in the general case for the same reason that a sparse top-level set of columns is better: I can easily add more data to events (e.g., "source") without rewriting existing events.

But my point is: I disagree with that claim.

Maybe sometime your proposal is better, but not always. What if you know that you won't add more data to events. Or more precisely, you know that what identify an event won't change. What if you decided to model it with a (timestamp, category, sub-category, eventId) composite not as a way to feed data into the column key, but because this correspond to how you want to query the data (which I would say is a very cassandra-ish way to model).

Let's take an example. The data for the (timestamp, category, sub-category, eventId) composite (for some key) could look like (on disk):

  ts1:catA:subcatA:id1 -> <value>
  ts1:catA:subcatA:id2 -> <value>
  ts1:catA:subcatA:id3 -> <value>
  ts1:catA:subcatA:id4 -> <value>
  ts1:catA:subcatB:id5 -> <value>
  ts1:catA:subcatB:id6 -> <value>
  ts1:catB:subcatA:id7 -> <value>
  ts1:catB:subcatA:id8 -> <value>
  ....

And say that value is some opaque bytes representing some event data.

Now, I'm not even sure how you model the same thing with your proposal, but I'm pretty sure it will involve indirections (or duplication), I doubt it will be more user friendly and you will need more than one query (I would have said 3 queries at first but after trying to see how it would look like I'm not even sure I see where you would put the value in your proposal) to do query like:

• give me all the events (eventid and value) for (ts1, catA, subcatA)
• give me all the events (eventid and value) for (ts1, catA)
• give me all the events (eventid and value) for ts1
  because the events would not be ordered correctly.

The kind of modeling you propose would make sense if the <value> for an event above was not opaque but composed of a number of property. They yes, I may would want to model things as:

  ts1:catA:subcatA:id1:prop1 -> <value_prop1>
  ts1:catA:subcatA:id1:prop2 -> <value_prop2>
  ts1:catA:subcatA:id1:prop3 -> <value_prop3>
  ts1:catA:subcatA:id2:prop1 -> <value_prop1>
  ts1:catA:subcatA:id2:prop2 -> <value_prop2>
  ts1:catA:subcatA:id2:prop3 -> <value_prop3>
  ts1:catA:subcatA:id3:prop1 -> <value_prop1>
  ts1:catA:subcatA:id3:prop2 -> <value_prop2>
  ts1:catA:subcatA:id3:prop3 -> <value_prop3>
  ...

because that doesn't screw up with the sorting I'm trying to impose (and that correspond to my queries). And btw, prop1 could 'category' (though that would be redundant in that case). But there is two different thing:

1. the first part of the key (ts1:catA:subcatA:id1) is the key to my object. It is what makes the ordering corresponding to my queries.
2. the last component (prop1, ...) is just the way to express the different properties of my object (and just a way to emulate super columns after all).

So I guess what I'm arguing here is just to not forget the case where you use CompositeType because your column key do is intrinsically composed of multiple parts. Because it is very useful.

I guess what I'm arguing here is just to not forget the case where you use CompositeType because your column key do is intrinsically composed of multiple parts

I think something got lost along the way here, because I've been very clear from the beginning that we do need to support the "dense" case, and that I prefer "the component syntax" in part because it does accommodate that. We can bikeshed all day about whether "sparse" or "dense" is the more common, but bottom line is we need to support both.

I think something got lost along the way here

Seems like the confusion is around the component1 syntax?

This is meant to be used with composite columns. Example:

ts1        :catA      :subcatA   :id1       :prop1
component1 :component2:component3:component4:component5

how about this?

-- sparse select example
SELECT TRANSPOSE NAME TO (tweet_id; username, body; location) FROM tweets WHERE key = <key> AND username = 'xedin';

-- dense select example
SELECT TRANSPOSE NAME TO (tweet_id; username; location) FROM tweets WHERE key = <key> AND username = 'xedin';

-- insert
INSERT INTO tweets (KEY, COMPOUND NAME ('2e1c3308', username)) VALUES (<key>, 'cscotta');
INSERT INTO tweets (KEY, COMPOUND NAME ('2e1c3308', body)) VALUES (<key>, 'Brother...');
INSERT INTO tweets (KEY, COMPOUND NAME ('2e1c3308', 'cscotta')) VALUES (<key>, 'Brother...');

-- update
UPDATE tweets SET COMPOUND NAME ('2e1c3308', 'cscotta') = 'My motocycle...' WHERE KEY = <key>;

We can't add syntax like TRANSPOSE or TO to Hive without forking it.

because I've been very clear from the beginning that we do need to support the "dense" case

Alright, cool.

We can bikeshed all day about whether "sparse" or "dense" is the more common, but bottom line is we need to support both

Agreed. I just felt from the conversation that "dense" was getting really much less love that sparse based on the argument that "doing dense is wrong". Since I disagree with that argument, I just wanted to make sure we don't pick a syntax that discourage "dense" too much (again, which isn't saying we should discourage "sparse" either).

One thing I hadn't followed at first was that we planned to deprecate the '..' notation in favor for transposed view even for non-composite columns. But now that I've connected the dots, that makes sense.

To make it Hive friendly

-- sparse select example
SELECT tweet_id; username, body; location FROM tweets:transposed WHERE key = <key> AND username = 'xedin';

-- dense select example
SELECT tweet_id; username; location FROM tweets:transposed WHERE key = <key> AND username = 'xedin';

-- insert
INSERT INTO tweets (KEY, COMPOUND_NAME('2e1c3308', username))  VALUES (<key>, 'cscotta');
INSERT INTO tweets (KEY, COMPOUND_NAME('2e1c3308', body))      VALUES (<key>, 'Brother...');
INSERT INTO tweets (KEY, COMPOUND_NAME('2e1c3308', 'cscotta')) VALUES (<key>, 'Brother...');

-- update
UPDATE tweets SET value = 'My motocycle...' WHERE KEY = <key> AND COMPOUND_NAME('2e1c3308', 'cscotta');

I think the compound_name() function is a reasonable solution for the insert side, +1.

On the SELECT side, it's a valiant effort, but these examples just doesn't look like [C|S]QL anymore. Remember that a simple SQL SELECT grammar is

SELECT * | expression [ [ AS ] output_name ] [, ...]

I think adding non-SQL syntax to that is a bad thing to do if we can avoid it. (I don't think it matters particularly that ";" is also typically used as a statement delimiter in SQL shells, including cqlsh – the choice of delimiter isn't the main problem.)

UPDATE tweets SET COMPOUND NAME ('2e1c3308', 'cscotta') = 'My motocycle...' WHERE KEY = <key>;

We can create a function like COMPOUND_NAME which will create a composite column under the hood, that will work for hive too.

The syntax would then look like:

UPDATE tweets:transposed SET value = 'my motorcycle' WHERE KEY= <key> AND column = COMPOUND_NAME('2e1c3308', 'cscotta');

+1

We also should support defining CompositeType columns w/o using the internal AbstractType names.

Robin Schumacher notes that Oracle supports a related concept of "nested tables": http://www.orafaq.com/wiki/NESTED_TABLE. The important syntax is:

• TYPE x IS OBJECT (foo, bar, ...): declares a "row" object type
• TYPE y is TABLE (baz, zep, ...): declares a "table" object type
• Above can be combined: INSERT into parent_table ('subtable') VALUES (y(x(a, b), x(c, d), ...)
• Nested table creation is done using the TABLE type and NESTED TABLE keywords: CREATE TABLE parent_table(i int, subtable y) NESTED TABLE y STORE AS y_tab (unclear to me what STORE AS does, if anything; none of the examples I saw have involved that other than as a necessary part of the declaration)
• SELECT also requires the TABLE keyword: select p.i, s.* FROM parent_table p, TABLE(p.y) s

See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8135488196597 and http://www.databasejournal.com/features/oracle/article.php/3788331/So-what-is-an-Oracle-Nested-Table.htm

Thinking out loud:

• We need to support adding "new" subcolumns in the "sparse" case but it doesn't make sense to have wildly different types of data in the same CF (an antipattern). So it's probably okay to have use some kind of predeclaration like CREATE TRANSPOSED TABLE to simplify querying.
• If we do use that approach do we want to continue to require FROM table:TRANSPOSED syntax in the SELECT?
• SELECT x, y FROM foo:key syntax really gives the right "you're selecting a resultset from this row" flavor. But, it doesn't allow composing with the normal WHERE predicates, especially key > foo and key IN (...), i.e. range scans and multigets. I can live without multigets, although it would be a loss of functionality since the Thrift API supports slice multiget, but we can't live without range scans (for Hadoop, since I want to move CFIF to CQL)
• We need to support combinations of "sparse" and "dense." But at most the last one can be sparse/dynamic, since it's the name:value pair that gives it that characteristic.
• Insertion is simpler for us in some ways than Oracle's nested tables; we're still inserting "rows" even in the transposed case. Where to put the row key (and column "path", in the sparse case) is the main question.
• I kind of like the UNQL document-like syntax for updates (UPDATE table SET x.y.z = foo WHERE key = bar) but that doesn't preserve the transposition conceptual integrity.

Still thinking through this.

How about this?

CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    posted_by int,
    body text
) TRANSPOSED AS (posted_at), SPARSE(posted_by, body);

SELECT, INSERT, and UPDATE syntax requires no changes.

The SPARSE keyword means this is a "dynamic" composite column. Thus, the actual composite columns stored here might be

and

. That is, the column name literals 'posted_by' and 'body' are part of the composite column name. It will take one composite column per SPARSE column to store a transposed row.

ALTER TABLE timeline ADD location int; would add location to the sparse subcolumns.

CREATE TABLE events (
    series text primary key,
    ts1 int,
    cat text,
    subcat text,
    id uuid
) TRANSPOSED AS (ts1, cat, subcat, id);

There are no sparse components; an example column stored might be (2355234412, 'trucks', 'Ford', 2165cd4c-4db8-4a8f-a2b2-e8fa157f7697). Each transposed row is represented by one column. ALTER TABLE events would not be allowed.

I think that this is the best option as it doesn't require to modify most of the statements and easily understandable! +1 with small modification - lets change "," to "WITH" before SPARSE so we have statements like this "TRANSPOSED AS (posted_at) WITH SPARSE (posted_by, body)". Also we should probably reject statements were at least one of the defined "not primary key" columns is not listed in TRANSPOSED.

I like the WITH change. This also makes the parentheses unnecessary, although we might still want to keep them for the visual cues they offer.

Agreed.

In the non-sparse case you would always ignore the column value? I think we need to expose that somehow. (first non-transposed, non-key, non-sparse column?)

Overall I like this because it forces a user to think at schema creation time and not access time. This approach makes sense for CQL only access, but for users who are coming from thrift they will be asking "how do i access data from my current data model?"

On the negative side, this approach feels a bit too restrictive since you MUST use the same kind of schema across all rows within a CF. What if a user doesn't know what the sparse columns will be ahead of time?

Also, I know that's best practice but want to make the point, what if a user wants to access data in composite form and "raw" mode, should we support multiple "views" on the CF?

but for users who are coming from thrift they will be asking "how do i access data from my current data model?"

We can support special command to set metadata about CF transposition, something like "TRANSPOSE <cf> AS (col, ...) WITH SPARSE (<id>, ...)" or "ALTER TABLE <cf> SET TRANSPOSED AS (col, ...) WITH SPARSE (<id>, ...)"

On the negative side, this approach feels a bit too restrictive since you MUST use the same kind of schema across all rows within a CF. What if a user doesn't know what the sparse columns will be ahead of time?

User will be able to add sparse columns using "ALTER TABLE" command.

Also, I know that's best practice but want to make the point, what if a user wants to access data in composite form and "raw" mode, should we support multiple "views" on the CF?

Can you elaborate "raw" mode?

User will be able to add sparse columns using "ALTER TABLE" command.

Requiring a ALTER when you may not know what columns you have is too restrictive. Example, a ETL from a 3rd party manufacturer that provides a custom set of attributes per product: some standard (Unit Price, Model, Color, etc) some specific (DPI, Shipping Size, Contrast Ratio). We don't want to go back to having to know exactly what your data will look like before you can write/read it. That's one of the important tenants of nosql I'd like to keep

Can you elaborate "raw" mode?

I mean non-transposed mode.

Requiring a ALTER when you may not know what columns you have is too restrictive. Example, a ETL from a 3rd party manufacturer that provides a custom set of attributes per product: some standard (Unit Price, Model, Color, etc) some specific (DPI, Shipping Size, Contrast Ratio). We don't want to go back to having to know exactly what your data will look like before you can write/read it. That's one of the important tenants of nosql I'd like to keep

User still will be able to add sparse columns on insert without alter as with current insert (it will just use default value validator).

I mean non-transposed mode.

I think we can do that.

In the non-sparse case you always would always ignore the column value?

No, it would be associated with the last column definition.

for users who are coming from thrift they will be asking "how do i access data from my current data model?"

Requiring a ALTER when you may not know what columns you have is too restrictive. Example, a ETL from a 3rd party manufacturer that provides a custom set of attributes per product: some standard (Unit Price, Model, Color, etc) some specific (DPI, Shipping Size, Contrast Ratio).

But that's exactly when you do know what columns you have. (We're totally fine with having most sparse columns be null.) What we can't do is query "undefined" columns. This is inherent to any transposition approach, even the earlier ones above. The only way we can support that is by destructuring each column into a resultset of (key, columnname, columnvalue).

I can't think of a single example where wide rows with actual different structure (as opposed to just sparse columns as in your example here) is the Right Way to do things. If this is actually necessary though then I think we should add separate syntax for the destructuring approach.

what if a user wants to access data in composite form and "raw" [non-transposed] mode, should we support multiple "views" on the CF

No. Nested-but-not-transposed data aka "documents" is another separate case.

But that's exactly when you do know what columns you have.

In my example, when a new column is added to the file and inserted by the loader, it's hidden from view till someone explicitly adds it as a sparse column. That makes us no longer schemaless.

Nested-but-not-transposed data aka "documents" is another separate case.

This is the case I'm thinking of then. Would this be handled in CQL or a "document" api?

In my example, when a new column is added to the file and inserted by the loader, it's hidden from view till someone explicitly adds it as a sparse column. That makes us no longer schemaless.

Right. But "schemaless" is a non-feature; "painless schema" is what people care about. (1000 columns? No problem! ALTER without rewriting your data? No problem!)

This is the case I'm thinking of then. Would this be handled in CQL or a "document" api?

I think it should be CQL, although we can support additional APIs on top of that. CASSANDRA-3647

ok +1

On the Hive side we can support the same semantics, however achieving the same syntax will be hard. On the other hand since this is now DDL and no longer DML I think it's not a big deal.

For the sparse example:

CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    posted_by int,
    body text
) TRANSPOSED AS (posted_at), SPARSE(posted_by, body);

Not sure I'm very fond of that. The fact that the type of 'posted_by' and 'body' are actually not the type of the component itself but the type of the value while they do correspond to an actual component means that:

1. you have not way to give the type of that last component; and if it's not text, the notation won't look so nice.
2. the notation only work if the 'sparse' component is the last one, which may be the case for "transposition of super columns", but feels arbitrarily limited otherwise.

I think that the fact that 'posted_by' and 'body' are actually string literals is not very intuitive and only mildly consistent with the rest of the syntax.

I'd also note that as far as I can tell, we wouldn't be able to handle the dynamic composite type with this in a meaningful way. But as you said above, this can be handled by a destructuring syntax. Which I think we definitively need.

The crucial part of this latest proposal is that it really highlights that transposition really is just an implementation detail from the relational perspective. So, to flesh that out:

INSERT INTO timeline (user_id, posted_at, posted_by, body)
VALUES ('tjefferson', '1818', 'jadams', 'Revolution was effected before the war commenced');

INSERT INTO timeline (user_id, posted_at, posted_by, body)
VALUES ('tjefferson', '1763', 'jadams', 'Democracy will soon degenerate into an anarchy');

INSERT INTO timeline (user_id, posted_at, posted_by, body)
VALUES ('tjefferson', '1790', 'gwashington', 'To be prepared for war is one of the most effectual means of preserving peace');

INSERT INTO timeline (user_id, posted_at, posted_by, body)
VALUES ('bfranklin', '1781', 'tjefferson', 'Every government degenerates when trusted to the rulers of the people alone');

... corresponding to the data in

which in "raw" form looks like

Does that make sense? We're using TRANSPOSED AS similarly to how databases have used storage hints like CLUSTERED. It doesn't affect the relational model of the data, but it gives you different performance characteristics. (The analogy is particularly apt in that both CLUSTERED and TRANSPOSED AS affect ordering of results. EDIT: oops, goofed in ordering of the tjefferson row in my diagram.)

So, you could do queries like

SELECT * FROM timeline WHERE user_id = 'tjefferson' AND posted_at > 1770;

Which would give the resultset shown in

you have not way to give the type of that last component; and if it's not text, the notation won't look so nice

Okay, so we can make the following minor changes to make the syntax more flexible:

• Drop the AS and the first list from TRANSPOSED AS; all columns are transposed, so we can just list the sparse ones (which may occur anywhere in the list):
• Allow an optional WITH (or AND, if there is already a WITH SPARSE) clause of COLUMN NAMES that includes the name type.

So my first example would become

CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    posted_by int,
    body text
) TRANSPOSED WITH SPARSE(posted_by, body);

A more complex one including some non-utf8 types might be

CREATE TABLE events (
    series text primary key,
    ts1 int,
    cat text,
    subcat text,
    "1337" uuid,
    "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" bigint
) TRANSPOSED WITH COLUMN NAMES ("1337" int, "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" uuid);

(I'm waving my hands a bit here and using "" to denote quotes-to-help-parsing rather than quotes-to-indicate-string. This is the convention postgresql uses.)

the notation only work if the 'sparse' component is the last one, which may be the case for "transposition of super columns", but feels arbitrarily limited otherwise

True, but none of the other proposals even come close to being as friendly as this one for typical cases, so I think we're in the right space to "make common things easy and unusual things possible." Tree-like sparse models may also fall into the "belongs in the CASSANDRA-3647 document/destructuring api ticket. If not, maybe we need the three-tuple model as well (but I'd rather let that slide until/unless we have a real need for it).

How about we change it to:

CREATE TRANSPOSED TABLE events (
    series text primary key,
    ts1 int SPARSE,
    cat text,
    subcat text,
    "1337" uuid DENSE(int),
    "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" bigint SPARSE(uuid)
);

Where SPARSE could be with or without an argument identifying column comparator. DENSE could only with with argument, columns without keyword are DENSE by default?... This allows to specify comparator and order of sparse/dense columns.

I like it better with the TRANSPOSED information in a separate block where it messes w/ standard CREATE grammar less.

Here is another option for this:

TRANSPOSED AS (<column> ?MODIFIER(?<comparator>), ....) 

where MODIFIER = SPARSE | DENSE and <comparator> = utf8, int, uuid, timeuuid, ...; if no MODIFIER set then MODIFIER = DENSE(default_type)

CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    posted_by int,
    body text,
    "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" bigint
) TRANSPOSED AS (posted_at, posted_by SPARSE, 92d21d0a-d6cb-437c-9d3f-b67aa733a19f" DENSE(uuid), body SPARSE);

True, but none of the other proposals even come close to being as friendly as this one for typical cases

Playing devils advocate I would say that 'sucking much less' doesn't necessarily make it 'the right solution'.

Now, don't get me wrong, I like the TRANSPOSED idea for composite. But I think you made 2 proposals:

1. a reasonably generic way to access CF with composite comparator in a CQL-ish way: the TRANSPOSED part.
2. an attempt at some special handling for the case of composites where the last component takes only a know number of values: the SPARSE thing.

I do like the first part. Though I'd like to mention some remarks on the following comment:

We're using TRANSPOSED AS similarly to how databases have used storage hints like CLUSTERED. It doesn't affect the relational model of the data, but it gives you different performance characteristics

While I understand what you mean, I don't think it's completely true. Because in the transposed case, the order of definition matters, which has a consequence on what you can do, both in terms of writes and reads.
Consider the two definitions:

CREATE TABLE test1 (
    key text primary key,
    prop1 int,
    prop2 int,
    prop3 int
)

and

CREATE TABLE test2 (
    key int primary key,
    prop1 int,
    prop2 int,
    prop3 int
) TRANSPOSED AS (prop1, prop2)

Those two definitions don't only differ from a performance standpoint. Typically, you can do

UPDATE test1 SET prop2 = 42 WHERE key = 'someKey';

but you cannot do the same query on test2. Btw, for test2, you don't necessarily have to specify prop2 for every row, but you need at least prop1 and prop3 each time. My point being that you do have to understand a bit what is going on underneath to understand the limitation we will have to put on this.

You also have the similar thing for gets: you can do

SELECT prop2 FROM test1 WHERE key = 'someKey';

but this make no sense with test2 (or rather there is no way we can do this efficiently, i.e, without reading the row fully).

That being said, I'll reiter that I'm reasonably convinced by this transposition notion, even though I'll probably prefer to write it as

CREATE TRANSPOSED TABLE test2 (
    key int primary key,
    prop1 int,
    prop2 int,
    prop3 int
)

as was suggested in some comments above.

On the SPARSE thing, I am much less convinced that this is the right solution. I think that having at the same 'level' variables that are just names to identify values in the resultset (posted_at) and literals (posted_by) is confusing (and ugly). (As a side note, I don't "understand" the choice of the SPARSE word).

Overall, I'm afraid we'll end up doing some bad choice by trying to do too much at once. The first problem we have is that CQL, that we'd like to push as the de-facto way to access Cassandra, doesn't allow access to composite columns at all. It seems to me that the transposed alone fixes that (again, except for the dynamic composite type). The SPARSE don't add any new possibility, it just adds a presumably better syntax for a specific case. I would be in favor of moving this to a second step (which would be less urgent and would allow refocusing the discussion on that very specific optimisation).

Lastly, and for the record, I would actually be in favor of having the first step on this being the addition of a very simple 'raw' notation to access composites. Something that could look like the example in the attached file 'raw_composite.txt' (put separatly because this comment is way too long already). The advantages being that: it's super simple to do, it'll be natural for users coming from thrift and it'll have not specific limitation (in particular it'll handle dynamic composites). Then, a second step would be to add more limited but more user-friendly notation to deal with specific cases (like the transposed and the sparse thing).

I would say that 'sucking much less' doesn't necessarily make it 'the right solution'.

This is more than sucking less. This is adapting exactly to the relational philosophy that SQL is about sets of records and predicates that deal with them, and how things are stored is an implementation detail. I don't see how we can do better than this or something like it.

Typically, you can do UPDATE test1 SET prop2 = 42 WHERE key = 'someKey' but you cannot do the same [statement] on test2.

That's a good point, although I think the limitations are fairly easy to explain, along with the effect on ordering.

The SPARSE don't add any new possibility, it just adds a presumably better syntax for a specific case.

Technically that is true, but that is akin to arguing that because PHP is Turing complete it's as good as Java to write databases in.

Consider my timeline example. If we defined the table without SPARSE, as

CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    column string,
    value blob
) TRANSPOSED

The query SELECT * FROM timeline WHERE user_id = 'tjefferson' AND posted_at > 1770 would give us

I don't see how this is usable in any reasonable sense of the word. Am I missing something?

(Edit: it's actually worse than that, since you can't even use 'posted_at' in the query. I've actually written SQL with "dynamic columns" like this and I cannot overemphasize how badly it sucks.)

(And no, "just model it with dense composites" is not an option for the mview use case, where adding new columns requires rebuilding the entire CF. That's a big win for us, and I'm not willing to give it up.)

I would actually be in favor of having the first step on this being the addition of a very simple 'raw' notation to access composites.

I'm strongly against this or any "row slicing" notation, it's a terrible fit for anything that wants to deal with resultsets of rows sharing a common set of columns (i.e., CQL and its drivers). Unless this actually returns rows instead of columns which is not at all implied by the syntax. Strongly against that too.

This is adapting exactly to the relational philosophy that SQL is about sets of records and predicates that deal with them

I suppose this sums up my objections and why I can't get myself to be fully convinced by those propositions. I had though at first that the goal of CQL was to use a query language because this was making it much more easy to make it evolve without breaking clients (and why not a syntax close to SQL as long as it doesn't constrain us in any way – though that was fishy from the start). But I didn't though the goal was to 'adapt to the relational philosophy'. I don't like that idea (for a number of reasons but it's not the place for those), I think we are impoverishing Cassandra going this road, and truth is, it's not the first time I've had a bad feeling about this.

But it's very possible that fitting to the relational philosophy is good for adoption, and that it's the best solution for CQL at this point (I should probably have cared for CQL and object to it sooner anyway), so ranting being made, I'll shut up and try to first embrace the relational philosophy to hopefully make more constructive criticisms

One possibility that could avoid SPARSE/DENSE syntax would be:

--DENSE transposed format
--column names are used directly in comparator

CREATE TABLE msg (
    user text primary key,
    sender text,
    thread text,
    tid     int,
    body    text
)
WITH comparator = composite(sender,thread,tid);

--SPARSE transposed format
--composite comparator with no specified columns will be a dynamic comparator
--and includes the column name as part of the dynamic column name
CREATE TABLE msg (
    user text primary key,
    sender text,
    thread text,
    tid     int,
    body   text
)
WITH comparator = composite;

--Finally in the case of both SPARSE/DENSE
CREATE TABLE timeline (
    userid int primary key,
    posted_at uuid,
    column string,
    value blob
)
WITH comparator = composite(posted_at,*);

Incidentally, a side benefit of having metadata that CF X is transposed is that we can start applying wide-row optimizations for it, e.g. skipping a column-level bloom filter.

I didn't though the goal was to 'adapt to the relational philosophy'

All data is relational, the only question is implementation details.

Seriously though, "send a query, get back rows" is a good design for a language + driver, and you simply can't represent slices of wide, composite rows sanely that way without something like transposition to expose the structure "hidden" underneath.

WITH comparator = composite(sender,thread,tid);

Wouldn't you need body in the composite list too?

--Finally in the case of both SPARSE/DENSE

So this is basically what I gave, right? It sounds like you're bikeshedding the TRANSPOSED / WITH COMPARATOR syntax but not really solving the problem I outlined. Maybe I'm missing something.

Wouldn't you need body in the composite list too?

That would be the value of the composite column

you simply can't represent slices of wide, composite rows sanely that way without something like transposition to expose the structure "hidden" underneath.

Put another way: we've always said the "right" way to model is to have one "atom" of data per CF – that is, data that is queried together should go in the same CF. But, we haven't enforced this in the API. From my perspective, that's a bug, not a feature. If we do have a single type of data (or type of record) per CF, then we can make relational-style resultsets out of it; the only question is, how do we expose that in the query language. And the conclusion I'm coming to is that we shouldn't have to change the language; CQL-modeled-on-SQL is already good at describing sets of a given type of data. So the right thing to do is to provide hints in the DDL that tell Cassandra how to represent it at definition time, rather than at query time.

"Document" data doesn't change this, it's just an expansion of what we consider a "record" or "atom". Should we be able to have wide rows of documents? Yes. (E.g., by having a column type "json.") But I don't think we need to boil that ocean here.

TLDR: I am totally fine with having some constructs technically representable using composite columns, that we don't actually allow building from the API. Total schema-freedom is not a design goal.

"just model it with dense composites" is not an option for the mview use case, where adding new columns requires rebuilding the entire CF. That's a big win for us, and I'm not willing to give it up.

Eric raised an interesting point on IRC – is there a technical reason why the "dense" CompositeType implementation can't just treat missing components as null, allowing adding new columns in a straightforward "extension" of the existing CT tuples?

(Dropping columns is trickier but doable: we'd need to keep the CT definition the same, but add an "ignore these parts of the tuple" set to track columns that are no longer relevant.)

If that's reasonable then I'm fine with dropping the whole "sparse" idea.

Aright, sorry if it is just me being stupid, but would you mind defining precisely what "dense" and "sparse" means. I think I know something about CompositeType but somehow those words make no sense to me. I had more or less gathered that "sparse" was related to the use case where you have a kwown/finite set of values for the last component, but I have doubts now as 'the "dense" CompositeType implementation' doesn't mean anything in that context.

I'm also not sure what 'treat missing components as null, allowing adding new columnss in a straightforward "extension" of the existing CT tuples' exactly refers too. What I can tell is that if you declare a comparator like CompositeType(IntType, UUIDType, UTF8Type), you can have columns that have only some prefix of the component (typically '42:92d21d0a-d6cb-437c-9d3f-b67aa733a19f' is a valid column name for that comparator), and as a consequence it would be possible to allow 'extend' the preceding comparator to say CompositeType(IntType, UUIDType, UTF8Type, IntType) (but we cannot right now because we 'cannot change comparator, period').

I'm using "sparse" to mean "we store the column name explicitly," which is similar to how do it for "simple" columns within a row, and as opposed to being purely convention that we apply to the column name tuple, which I call "dense."

we cannot right now because we 'cannot change comparator, period'

Sounds like an easy enough change to make, then. Opened CASSANDRA-3657 for that.

Assuming we go with TRANSPOSED approach, this is what the HIVE DDL would look like:

CREATE EXTERNAL TABLE timeline(user_id string, tweet_id long, username string, timestamp long)
      STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
      WITH SERDEPROPERTIES ("transposed" = "true", "cassandra.column.mappings" = ":key")

I wanted to bring this up because it hasn't been mentioned yet, and it's currently a topic of discussion on the hector-users list: for query results of composite columns are you going to deserialize the column name or leave it as an opaque blob? For the current implementation of composite columns in Hector the type information for dynamic composites is encoded in the name, but that information is lacking for the static variety. My understanding is that the type information is only stored at the CFDef level as the type alias, and could possibly be cached to aide in deserialization but that seems like a bit of a hack.

The more I think about it the less happy I am with omitting support for sparse columns. Remember that dense composites may only be inserted and deleted, not updated, since they are just a tuple of values with "column names" determined by schema and/or convention.

I think we can support sparse columns well in a way that improves the conceptual integrity for the dense composites as well:

-- "column" and "value" are sparse; a transposed row will be stored as
-- two columns of (posted_at, 'column': string) and (posted_at, 'value': blob),
-- with C* row key of user_id
CREATE TABLE timeline (
    user_id int,
    posted_at uuid,
    column string,
    value blob,    
    PRIMARY KEY(user_id, posted_at)
) TRANSPOSED;

-- entire transposed row is stored as a single dense composite column
-- (ts1, cat, subcat, 1337, 92d21d0a-...: []) with a C* row key of series.  
-- Note that the composite column's value is unused in this case.
CREATE TABLE events (
    series text,
    ts1 int,
    cat text,
    subcat text,
    "1337" uuid,
    "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" bigint,
    PRIMARY KEY(series, ts1, cat, subcat, "1337", "92d21d0a-d6cb-437c-9d3f-b67aa733a19f")
) TRANSPOSED WITH COLUMN NAMES ("1337" int, "92d21d0a-d6cb-437c-9d3f-b67aa733a19f" uuid);

Thus, columns included in the (transposed) primary key will be "dense," and not updateable, which conforms to our existing practice that keys are not updateable. Remaining columns will be updateable since they will each map to a separate physical column.

(Jake suggests on the wiki to use "WITH COLUMN TYPES" instead of "WITH COLUMN NAMES," which is reasonable but I'm worried about confusion with the type of the value.)

I think we can support sparse columns well in a way that improves the conceptual integrity for the dense composites as well

I think this is clean, the only worry with DENSE never using a column value is it will make it hard for current users of composites to adopt this, since they may well use a value.

As I said above, there's definitely some things you can represent with a CompositeType that we won't expose in the API. IMO it's an intractable problem otherwise. (And anyone currently using CT isn't using CQL... whatever they're doing now will continue to work, no matter what we add to CQL.)

I've updated the wiki page at http://wiki.apache.org/cassandra/Cassandra2474 with a more in-depth Background section that hopefully clears up where I'm going with this sparse/dense business.

Edit: had some discussion here about making row key explicit in TRANSPOSED WITH ROW KEY clause, instead of implicitly being the first element of PRIMARY KEY, but consensus seems to be this is a bad idea.

Created CASSANDRA-3685 for the WITH COLUMN NAMES feature, which is really distinct from compound columns (we have the same problem in standard CFs defined with CQL today).

I think we're closing in on something workable.

Eric points out that TRANSPOSED is unnecessary since it's redundant with respect to the composite PRIMARY KEY definition.

Should we support column values in non-sparse rows by adding a VALUE(column_name) section?

CREATE TABLE timeline (
    user_id int,
    posted_at uuid,
    body string,
    posted_by string,    
    PRIMARY KEY(user_id, posted_at, posted_by),
    VALUE(body)
);

(Open to better suggestions for that keyword.)

Ok, I think I'm really warming up to what we're getting at. I would change the syntax of the VALUE() thing however. Instead of:

CREATE TABLE timeline (
   userid int,
   posted_at uuid,
   body string,
   PRIMARY KEY(user_id, posted_at),
   VALUE(body)
)

I would prefer:

CREATE COMPACT TABLE timeline (
   userid int,
   posted_at uuid,
   body string,
   PRIMARY KEY(user_id, posted_at),
)

The reasons being that it really influences the implementation layout of the CF in C*. Namely, the non-compact CF defined by

CREATE TABLE timeline (
   userid int,
   posted_at uuid,
   body string,
   PRIMARY KEY(user_id, posted_at),
)

would look in C* like:

<userid> : {
    <posted_at>:'body' -> <value>
}

while the COMPACT variant would be:

<userid> : {
    <posted_at> -> <value>
}

which is using the fact that there is only 1 field not part of the key to "optimize" the layout. And I believe making the COMPACT part of the CREATE emphasis better that it's a property of the definition itself (that cannot be changed) rather than of that specific 'body' field. It also make the rule for COMPACT table rather simple: "a compact table should have only one field not part of the primary key"; you don't have to deal with errors like someone defining two VALUE() for instance.

That being said, I'd like to try to resume where we're at (including the COMPACT change above) and add a few random ideas along the way. Please correct me if I've got something wrong.

I think we have 4 different cases, 2 for 'standard' CF without composites:

• static CFs (the only case CQL handle really well today)
• dynamic CFs (wide rows, time series if you prefer) and 2 for CF with composite column names:
• 'dense' composite (typically time series but where the key is naturally multi-parts)
• 'sparse' composite (aka super columns)

Let me try to take an example for which, with how it would translate internally and example queries.

Standard "static" CF

"For each user, holds his infos"

CREATE TABLE users (
    userid uuid PRIMARY KEY,
    firstname text,
    lastname text,
    age int
)

In C*:

<userid> : {
    'firstname' -> <value>
    'lastname' -> <value>
    'age' -> <value>
}

Query:

SELECT firstname, lastname FROM users WHERE userid = '...';

Standard "dynamic" CF

"For each user, keep each url he clicked on with the date of last click"

CREATE COMPACT TABLE clicks (
    userid uuid,
    url text,
    timestamp date
    PRIMARY KEY (userid, url)
)

In C*:

<userid> : {
    <url> -> <timestamp>
}

Query:

SELECT url, timestamp FROM clicks WHERE userid = '...';
SELECT timestamp FROM clicks WHERE userid = '...' and url = 'http://...';

'dense' composite

"For each user, keep ip and port from where he connected with the date of last
connection"

CREATE COMPACT TABLE connections (
    userid uuid,
    ip binary,
    port int,
    timestamp date
    PRIMARY KEY (userid, ip, port)
)

In C*:

<userid> : {
    <ip>:<port> -> <timestamp>
}

Query:

SELECT ip, port, timestamp FROM connections WHERE userid = '...';

'sparse' composite

"User timeline"

CREATE TABLE timeline (
    userid uuid,
    posted_at date,
    body text,
    posted_by text,
    PRIMARY KEY (user_id, posted_at),
);

In C*:

<userid> : {
    <posted_at>:'body' -> <value>
    <posted_at>:'posted_by' -> <value>
}

Query:

SELECT body, posted_by FROM timeline WHERE userid = '...' and posted_at = '2 janvier 2010'

Note: I think we really should also be able to do queries like:

SELECT posted_ad, body, posted_by FROM timeline WHERE userid = '...' and posted_at > '2 janvier 2010'

but that's more akin to the modification of the syntax for slices.

Random other ideas

1. We could allow something like:

   CONSTRAINT key PRIMARY KEY (userid, ip, port)
   

   which would then allow to write

   SELECT timestamp FROM users WHERE key = ('...', 192.168.0.1, 80);
   

   (I believe this is the 'standard' notation to name a 'composite' key in SQL)

2. Above we're ony handling the use of composites for column names, but they can be useful for value (and row keys) and it could be nice to have an easy notation for that (clearly a following ticket however). What about:

   CREATE COMPACT TABLE timeline (
       userid_part1 text,
       userid_part2 int,
       posted_at date,
       posted_by uuid,
       body text
       header text
       GROUP (userid_part1, userid_part2) AS userid,
       PRIMARY KEY (userid, posted_at, posted_by)
       GROUP (header, body)
   )
   

   In C*:

   <userid_part1>:<userid_part2> : {
       <posted_at>:<posted_by> -> <header>:<body>
   }
   

   Query:

   SELECT posted_at, posted_by, body, header FROM timeline WHERE userid = ('john', 32)
   

I would prefer CREATE COMPACT TABLE ... it's a property of the definition itself (that cannot be changed) rather than of that specific 'body' field. It also make the rule for COMPACT table rather simple: "a compact table should have only one field not part of the primary key"; you don't have to deal with errors like someone defining two VALUE() for instance.

Those are good points. Personally I'd still rather have a "WITH COMPACT STORAGE" syntax after CREATE TABLE ( ... ), since that's a more general way to tackle nonstandard options.

I think we have 4 different cases

You nailed it, that's exactly what I want to solve here.

Random other ideas

As you say, let's leave these as future work for now, this is a big enough discussion as it is.

Personally I'd still rather have a "WITH COMPACT STORAGE" syntax after CREATE TABLE ( ... ), since that's a more general way to tackle nonstandard options.

I'm good with that.

So if we're closing in on consensus on the syntax, the next question is, how do we represent this in the schema? Do we even bother trying to make it fit the Thrift schema api? I'd take CQL-only at this point if that helps us get it into 1.1.

So if we're closing in on consensus on the syntax, the next question is, how do we represent this in the schema? Do we even bother trying to make it fit the Thrift schema api?

The only think I see that would need to be added is some column name aliases. To be more precise, in

CREATE TABLE clicks (
    userid uuid,
    url text,
    timestamp date
    PRIMARY KEY (userid, url)
) WITH COMPACT STORAGE

the only thing that is missing for the current schema is that 'column name alias' is "url". And to support composites we basically need to make that alias a list. So overall it sounds just like one field for CfDef. Is there something else I'm missing?

Though to be honest my preference would go to not adding it to CfDef as Thrift doesn't care about aliase (I would have preferred key_alias was not added to CfDef for the same reason) but to add to CQL the option to do:

ALTER TABLE foo
ADD PRIMARY KEY (userid, url)

for those upgrading from thrift. Not that I feel really strongly about it though.

I'd take CQL-only at this point if that helps us get it into 1.1.

I'm happily volunteering to work on this and to start now, unless someone else feels strongly about doing this one (and unless someone object to the consensus on the syntax). But at the risk of sounding like a buzzkill, I'd mention that this will be tight and I don't think we should rush this (both code and review). That being said, if there is no hiccup with the implementation we may be good.

I'm fine with CQL only

the only thing that is missing for the current schema is that 'column name alias' is "url". And to support composites we basically need to make that alias a list. So overall it sounds just like one field for CfDef.

Agreed, that's what I came up with before I fell asleep last night too.

my preference would go to not adding it to CfDef as Thrift doesn't care about aliases

I think we need to though, since that's how everyone learns the schema until CASSANDRA-2477 is done. (Although CASSANDRA-1391 changes the internal storage enough that it may make 2477 easy. Guess reviewing that is at the top of my list today.)

I think we need to though, since that's how everyone learns the schema until CASSANDRA-2477 is done.

Right, let's add it then unless CASSANDRA-2477 gets resolved in the meantime. No biggy anyway.

But at the risk of sounding like a buzzkill, I'd mention that this will be tight and I don't think we should rush this (both code and review). That being said, if there is no hiccup with the implementation we may be good.

I also think this is tight, particularly since it's introducing new syntax, and a syntax that required one of the most (if not the most) epic discussions. Ever.

I've been thinking about this for a while, but what do people think about implementing an experimental mode? Something that involves setting a boolean in cassandra.conf, or passing a -Dcassandra.experimental=true property.

Code like this (or prepared statements for that matter) could test that experimental mode is enabled, or raise a new Thrift exception if it isn't.

Granted that means we'll see less testing than we would otherwise, but I think we stand to see more/better testing than we would from a beta or RC. More importantly, it sets an expectation that it's new, less tested, and that breaking changes might be coming (all of which are true IMO).

+1 on experimental mode.

-0 here, I don't think it really provides anything other than a CYA for us. ("We TOLD you it was experimental, you even had to turn on the option!") Otherwise it's common sense that new-this-release features are going to be less stable, so it doesn't do much besides leave people just that much frustrated when they go to try feature X and have to edit-and-bounce their server first.

-0 here, I don't think it really provides anything other than a CYA for us. ("We TOLD you it was experimental, you even had to turn on the option!") Otherwise it's common sense that new-this-release features are going to be less stable

I don't think it's about CYA, I see it as setting a clear expectation.

IMO (and recent experience bares this out), we can't in any confidence introduce a new CQL feature like this without learning something after the fact, and wishing we could go back and do it differently (or flat having no choice). By that point, the Cat is out of the bag. Either we live with it (and provide repeated explanations), or we fix it. Both of which are sources of frustrations for users.

Some would say (have said) that we should do better QA to avoid this happening, but there is only so much you can do without wider testing, and unfortunately beta releases just don't cut it.

An experimental mode let's us Smoke Test a new feature like this while being honest with our users that that's what we're doing. If we need to make breaking changes, it gives us the freedom to do it (in this case without a CQL major bump), because we've set that expectation. Users who value stability were spared those landmines.

...it doesn't do much besides leave people just that much frustrated when they go to try feature X and have to edit-and-bounce their server first.

I understand you probably meant s/edit-and-bounce/anything/, but we could also make this settable though JMX and add a nodetool command for it (making a skooch easier).

Speaking of changing stuff, this makes WITH COMPARATOR and WITH VALIDATOR obsolete.

Speaking of changing stuff, this makes WITH COMPARATOR and WITH VALIDATOR obsolete.

Agreed on the validator, but for the comparator I think it boils down to the question "do we want to allow 'static' cfs with non-utf8 names?". That is, this ticket makes it unecessary for the dynamic cfs, but not for static ones. So do we want to allow. I'll admit I have a hard time coming with a good use of static cf with non-utf8 name though.

Speaking of changing stuff, this makes WITH COMPARATOR and WITH VALIDATOR obsolete.

Are you saying this new CREATE COLUMNFAMILY syntax is a wholesale replacement for the previous one?

I suppose I think of it more as "adding PRIMARY KEY (...)" and "COMPACT STORAGE" syntax to the existing one, but either way is a reasonable way to describe it.

I guess what I'm asking is: Are you saying that WITH COMPARATOR is obsolete because there will be a Better Way, or because that syntax will no longer work?

I'm saying that this gives us a Better Way, so we should deprecate WITH COMPARATOR and WITH DEFAULT_VALIDATOR and remove them in 1.2 (but not in 1.1).

for the comparator I think it boils down to the question "do we want to allow 'static' cfs with non-utf8 names?"

I'm comfortable with saying, "No." (If we have a full major release worth of being deprecated, that should give people plenty of time to say "Hey, I actually have a use for this.")

Because I just though of that, I'll note that one could do a small criticism about how intuitive our notation in the case of USE COMPACT STORAGE (and even without it in the composite case), namely that in

CREATE TABLE connections (
    userid uuid,
    ip binary,
    port int,
    timestamp date
    PRIMARY KEY (userid, ip, port)
) USING COMPACT STORAGE;

the output will be sorted for ip and port (which very likely do matter), but not for userid (for RandomPartitioner at least) and the notation doesn't give much hint of that. Typically, a notation like:

CREATE TABLE connections (
    userid uuid PRIMARY KEY,
    ip binary,
    port int,
    timestamp date
    SECONDARY KEY (ip, port)
) USING COMPACT STORAGE;

would maybe make it easier to explain that a secondary key involve a sorting but not a primary key (typically saying that "if you care about the sorting of records, you'll need to use a SECONDARY KEY" would be a fairly clear instruction). Of course, we could just say that in a primary key, columns after the first one involve a sorting, but I guess the syntax convey the idea a tad less.

I don't think anything we do here is going to be 100% intuitive with respect to the sorting behavior (I can't think of a way to make a SQL database sort by columns X and Y, but only within the same column Z, which is itself ordered randomly), so I'd rather avoid inventing non-SQL syntax. And the PRIMARY KEY syntax is still a good fit for the "you can't update the PK directly" behavior.

I'll precise that I was not talking about being intuitive to people coming from SQL. That's one of those situation where I think that trying to absolutely stick to SQL syntax syntax should be an absolute goal, not if that means making Cassandra specificity less clear, because at the end of the day, people will be using CQL, i.e. Cassandra, not SQL. So yes, no SQL database will have a way to correctly describe our case, but in a way I think that is more a argument for having a syntax that doesn't exist in SQL, because that makes it clear to the user that there is a specificity that his worth understanding.

And to avoid having the debate on the wrong thing, I'll note that SECONDARY KEY is likely a bad name, because secondary key does mean something in the SQL world (maybe not as a syntax, but it has entries in google at least ). The point would be to take something that state explicitly that we differ from SQL, maybe something like SORTING KEY.

I'll also note that it's not only the sorting itself, as for instance the fact that (talking my examples above) you can say 'userid IN (...)' but not 'ip IN (...)' is more intuitive with the SORTING KEY syntax. Same for slices.

PRIMARY KEY syntax is still a good fit for the "you can't update the PK directly" behavior

True, but that would still be true except that we would add "and you can't update the SORTING KEY (if any) directly either", which doesn't seem too bad to me.

The thing is, PRIMARY KEY (X, Y) means that you can have exactly one row with the given values of X and Y. Which fits perfectly our composite PRIMARY KEY use case. As an implementation detail it ALSO has effects on sorting, which I am okay with. PRIMARY KEY (X) SECONDARY KEY (Y) would be flat out wrong in implying that you can have exactly one row for key X, which I am not okay with. You'd have to call them something like ENTITY GROUP (X) SECONDARY KEY (Y) and then explain how that effectively means PRIMARY KEY (X, Y) plus sorting on Y. I don't really think it's an improvement overall.

I guess my argument is that while PK does identify uniquely a record, which fits the SQL notion for that part, we will have a number of difference with how a PK works in SQL. Namely the difference is that the order of the argument in the PRIMARY KEY definition matter, and quite a lot actually.

To be concrete, if you define:

CREATE TABLE table (
    X text;
    Y text;
    Z text;
    V int;
    PRIMARY KEY (X, Y, Z);
) WITH COMPACT STORAGE;

then this is not at all the same than having PRIMARY KEY (X, Z, Y) or even PRIMARY KEY (Z, Y, X), and in particular:

• you will be able to do SELECT * WHERE X = 'foo' AND Y = 'bar' but not SELECT * WHERE X = 'foo' AND X = 'bar'
• you will be able to do INSERT INTO table (X, Y, V) values ('foo', 'bar', 3), but not INSERT INTO table (X, Z, V) values ('foo', 'bar', 3).

There is also the sorting, which is not imo an implementaion detail because it is absolutely fundamental for the wide row case.

I guess there is two points:

1. If a concept we introduce is different in reasonably subtle ways to a SQL concept, we probably better avoid reusing the same name. I feel it'll be less confusing to have people ask upfront what is a given (unknown) concept, and to explain it saying that it is close to some other SQL concept but with given differences, rather that having everyone think they know how it work based on name and the superficial similarities and get beaten by the differences later on. That's a argument for renaming PK to something else.
2. The sorting is a rather important concept in the case of wide rows. And we don't sort on the row key. So it feels that splitting the PK into two concept would make the syntax more informative. On the one side we would lose a bit on the intuition of what uniquely identify a record, but we'll win on the sorting intuition. And the latter seems a more important concept to me (in C* that is), one that have more consequences on what you can/cannot do.

Now if I'm the only one to think that maybe the PK notation may end up being more confusing than helpful and does not convey important notion specific to C*, then I'll shut up.

Now if I'm the only one to think that maybe the PK notation may end up being more confusing than helpful and does not convey important notion specific to C*, then I'll shut up.

FWIW, I agree with you in principle. I've been involved in several such discussions in the past and have always felt that strict adherence to SQL syntax without adherence to SQL semantics was a double-edged sword. It's great that it leverages what people already know, until it doesn't work they way they know it should.

But those discussions are in the past (where were you then? ), and convention has since become to adopt SQL syntax where possible, even if semantics differ.

What differs here is the degree by which this has the potential to surprise people, and I do think this sets a precedence in that regard.

The patch is coming along rather well I think, but I still have a few details to fix/clean up.

The patch does involve a number of language changes (which I strongly believe are for the best), and the introduction of a native way to deal with wide rows have a bunch of consequences. So let me list a number of those changes and see on which you disagree, so we discuss that sooner than later:

• The '..' notation for slices is removed. It just doesn't work with the new way to handle wide rows (and composites).
• I've made static definitions (i.e, those definitions that don't use COMPACT STORAGE basically) really static. That is, inserting a column that is not defined is not accepted, and there is no way to do something that translate as a column slice on a static CF. The reason is twofold: I think this is good for the user to have the validation that it doesn't make a basic typo when inserting a column (thus inserting the wrong column). But also, I believe that it makes the language much more coherent.
• Column definitions for static (and sparse) CF are all UTF8 strings (as discussed above).
• I'm going to make all column definition case insensitive, unless they are enclosed in single or double quotes (I've heard it's how PostgreSQL does it more or less). If you have a better idea ...
• My current patch don't really allow creating a secondary index on a non static CF, because it's not clear how that would work from the syntax and I'm sure I see good use for that. Does that bother someone ?
• Currently, prepared statement allows you to write SELECT ? FROM users WHERE ? > 3. I.e, you can have marker in place of a column definition (I call 'column definition' a name defined in the CREATE TABLE basically). I think it could make sense to disallow that. The rational being that this patch make select more complicated, it has more work to do to validate the query is correct and to generate the query itself. If we were to disallow markers in place of column definition, a fair amount of that preprocessing could be done during the preparation phase in theory, which would be faster and allow to return a number of errors more quickly. Besides, column definition are small string, so allowing marker for them doesn't seem to be any real win.

Now, I do have a problem I'm not sure how to deal with: I'm not sure how to deal with the limit for slices. The problem is that a slice on a wide row will now return a number of CqlRow, not one with many columns. So it sound like we should use the LIMIT. However, when you do say SELECT * FROM CF; (of if you query multiple keys with IN), then it's unclear what you do with the LIMIT. I unfortunately don't have a good solution for that yet.

I'm also not sure have the REVERSED just after the SELECT is still the right choice after this patch.

I'll also note that this patch (with the things above) make it so that in the CqlMetadata (in the result), the name_types are not useful anymore (since this is always UTF8), and neither is the default comparator and default validation types, as we can always set the type of each columns in the value_types map. I suppose the best way to proceed is to mark them deprecated for now and remove them in the next version.

In any case, I'm attaching the dtest file I'm using to test this patch (cql_test.py) because I think it's a great way to see what this patch is about.

Lastly, because of the scale of the changes this patch does, I don't think it is a good candidate to hide the change behind a runtime flag. It would be messy and I think would make it less clear that both

• previous behavior is unchanged without the flag turned on
• new behavior works as expected when the flag is on

I'll also note that this patch (with the things above) make it so that in the CqlMetadata (in the result), the name_types are not useful anymore (since this is always UTF8), and neither is the default comparator and default validation types, as we can always set the type of each columns in the value_types map. I suppose the best way to proceed is to mark them deprecated for now and remove them in the next version.

This seems possibly too far. Clients ought to be able to know what a CF's default comparator and validation types are, even if they're not necessary to deserialize column values.

Also, cqlsh has an ASSUME facility similar to the cli one. You can tell cqlsh to deserialize columns without an explicit column type as though they were something different. Would this still be possible without being able to tell whether column values had an explicit column_metadata type, or whether they were using the default?

Clients ought to be able to know what a CF's default comparator and validation types are, even if they're not necessary to deserialize column values.

I think one of the goal is to not necessarily stick to C* internal notion, as supported by the fact that specifying the comparator and default_validator during creates will be removed. Basically, why should we expose internal detail that are useless (which I'm claiming they are after this patch) for the client?

Would this still be possible without being able to tell whether column values had an explicit column_metadata type, or whether they were using the default?

I don't see any problem to having an ASSUME facility with what I'm proposing. Maybe it will be more clear with the patch, but I really thing that after the patch those info adds really no value to the resultSet.

My current patch don't really allow creating a secondary index on a non static CF, because it's not clear how that would work from the syntax and I'm sure I see good use for that. Does that bother someone ?

CASSANDRA-3680 will add support for this at a later time

The '..' notation for slices is removed

Personally I'd rather support both for one release to make the transition easier, but with neither super nor composite support I doubt many people are using the current .., so if doing both adds a lot of complexity I'm okay with this.

I've made static definitions (i.e, those definitions that don't use COMPACT STORAGE basically) really static.

I'm a little uneasy about this, but the only use cases I've been able to come up with make more sense as a COMPACT STORAGE wide row. So it's probably a good change.

Currently, prepared statement allows you to write SELECT ? FROM users WHERE ? > 3. I.e, you can have marker in place of a column definition (I call 'column definition' a name defined in the CREATE TABLE basically). I think it could make sense to disallow that.

+1

However, when you do say SELECT * FROM CF; (of if you query multiple keys with IN), then it's unclear what you do with the LIMIT

I don't think it's unclear at all, but maybe that's because of my SQL background. Granted, it doesn't make a great deal of sense to use IN + LIMIT, but if someone does, the LIMIT should take precedence.

I'm also not sure have the REVERSED just after the SELECT is still the right choice after this patch.

What if we allowed "ORDER BY DESC" instead? (This is not quite valid SQL, which requires "ORDER BY <expr> [ASC|DESC]", but it's close.)

(BTW, why test this with dtest instead of just single node mode?)

I don't see any problem to having an ASSUME facility with what I'm proposing. Maybe it will be more clear with the patch, but I really thing that after the patch those info adds really no value to the resultSet.

Example:

CREATE COLUMNFAMILY columnfam (keyname uuid PRIMARY KEY, username utf8) WITH default_validation = blob;
ASSUME columnfam VALUES ARE float;

In this situation, cqlsh should deserialize 'keyname' values as uuid, and 'username' values as utf8, but any other column values should be deserialized as float. cqlsh currently makes the distinction by seeing whether the type for a value is given in the CqlMetadata.value_types field. It sounds like your proposal involves sending a type for every column in the result; am I misinterpreting that?

I guess cqlsh could query the columnfamily options itself whenever it needed to determine the default_validation, but that seems like it kind of makes CqlMetadata useless.

Personally I'd rather support both for one release to make the transition easier, but with neither super nor composite support I doubt many people are using the current .., so if doing both adds a lot of complexity I'm okay with this.

The patch changes enough the implementation of select that keeping support for '..' would amount to add back some special code to handle it. But I guess removing it right away may mean a rather painful upgrade for anyone using CQL in production right now, so maybe it's worth it. Once the patch is ready, I'll see what adding back the '..' for easing transition entails exactly.

I've made static definitions (i.e, those definitions that don't use COMPACT STORAGE basically) really static.

To try to justify this a little bit more (so it doesn't seem too random a choice), I see mainly two big advantages to doing that:

1. added validation/security for the programmer: If you define:

   CREATE TABLE Users (
       ID int PRIMARY KEY,
       NAME text,
       EMAIL text)
   

   I think it's great that the DB warns you that

   INSERT INTO users (ID, NAME, EMA1L) VALUES (2, "Jacques", "j@cques.com")
   

   or

   SELECT * FROM users WHERE EMA1L = "j@cques.com"
   

   are likely mistakes on your side. It's also what someone coming from SQL would expect

2. It adds some (imo reassuring) regularity to the language, in that in

   SELECT xxx, yyy FROM cf WHERE zzz > 3;
   

   we know that xxx, yyy and zzz are always names defined in the "schema" (schema meaning here the CREATE TABLE definition). If we allow something random, it will only be meaningful for static (and sparse) CF and we will have to deal with the conflict with other column definition (parts of the PRIMARY KEY typically). Typically, in my example above, it means we would allow random column names to be insert except for the column name ID.

And I don't see any downside since you can cheaply update the schema or use wide rows if appropriate. Yes, internally our engine would allow for insert non-predefined column for 'static' CF, but is that useful is the right question. Or, as a great man once said: "schemaless" is a non-feature; "painless schema" is what people care about.

Granted, it doesn't make a great deal of sense to use IN + LIMIT, but if someone does, the LIMIT should take precedence

What I meant is I'm not sure how to implement it. Suppose you have the following wide row definition (good ol' time series):

CREATE TABLE Events (
    event_type text,
    time date,
    event_details binay,
    PRIMARY KEY (event_type, time)
) USING COMPACT STORAGE

and say for two event_type e1 and e2, you have 1000 events each. Now if you do (with limit as a way to do paging):

SELECT * FROM Events WHERE event_type IN (e1, e2) LIMIT 500;

How does that translate internally? If we do a multiGetSlice with a slice having a limit of 500, we'll read 500 columns from e2 uselessly. And we have a similar problem if we do more simply:

SELECT * FROM Events LIMIT 1000

because we currently have no way to do a range query that stops when we have n columns across all rows. In a way it's a simpler problem that in the 'IN' case because we could add internal support for this, but it's additional work and not really in the scope of this ticket.

In other words, I'm not sure how to implement LIMIT currently with the new definitions introduced by this patch while keeping it's SQL semantic.

What if we allowed "ORDER BY DESC" instead?

I'd be fine with that (though wouldn't "ORDER DESC" sound less weird?).

BTW, why test this with dtest instead of just single node mode?

No reason outside of it being simpler for me (the tests only use a single node) and my ignorance of an "official" CQL test suite (but I kind of think the dtest framework would be a good official test framework for anything not a unit test).

In this situation, cqlsh should deserialize 'keyname' values as uuid, and 'username' values as utf8, but any other column values should be deserialized as floa

I guess my point is that your example wouldn't be valid anymore after this patch (it may still be accepted for one version for compatibility sake but that's all). And the ASSUME would look like:

ASSUME column_name IS type IN cf;

Of course, for a good part, this amount to my argument that static CF should be really static. If we end up disagreeing with that (which would be a mistake but that's another point ), then yes, both the 'WITH default_validation' syntax for CREATE and the default_validator in the CqlMetatada would have to stay (which is actually not a bad argument in favor of my proposal of static is static IMHO). But let's say that at least CqlMetadata.name_types and CqlMetadata.comparator will lose their usefulness in any case.

What if we allowed "ORDER BY DESC" instead?

Actually, now that I think of it, ORDER DESC will have a similar problem to LIMIT for range slice. That is, we can reverse the column slices, but we internally do not support a reverse for range queries.

SELECT * FROM Events WHERE event_type IN (e1, e2) LIMIT 500;

I'm okay with this being inefficient because (as above) it's kind of a nonsense query.

SELECT * FROM Events LIMIT 1000

As you say, this one is relatively straightforward to add an efficient implementation of. In fact it's probably not much more work than hacking around it in QueryProcessor (range query for keys first, then page through the rows one at a time to avoid OOMing), but I'm fine ignoring optimization here as well – this is probably the most common query interactively, but not something you'd find in an actual application.

now that I think of it, ORDER DESC will have a similar problem to LIMIT for range slice

You're right. I guess we could just disallow it unless there is also a WHERE clause on the row key...

I'm okay with this being inefficient because (as above) it's kind of a nonsense query.

I can live with that too.

In fact it's probably not much more work than hacking around it in QueryProcessor

That's true. But since it is, and since all range query end up having a limit, whether it is the default or not, it's probably better to handle it correctly since day one. I've created CASSANDRA-3742 to do that. I'm not too far to have a patch that can be shown, so as soon as I've attached it, I'll deal with CASSANDRA-3742, unless someone that have a little free time takes it before.

I guess we could just disallow it unless there is also a WHERE clause on the row key

Right, that's probably good enough on a first shot. I suppose it wouldn't be crazy to add a reversed option to range slices later but I'm good leaving this one to later (it's unclear how efficient it will be (to do all those backward seek) and it's likely a bit more work that the global count thingy).

Attaching initial patch. It's not complete but close I believe and presentable enough that whoever wants to review can start looking at it.

First, the things that are not working/not ready:

• LIMIT: as said previously, I'll have to do CASSANDRA-3742 before.
• The '..' notation is broken. The syntax still supports it but it's ignored right now. I'll probably add back the support (keeping the resultset has previously for backward compatibility).

It would be worth running a number of existing CQL tests against this to see if there is other broken backward compatibility problem but I haven't yet done it.

On the other side, the tests I previously attached (cql_test.py) are all passing with this patch.

There is also a few simple improvements that could be done that I've not done yet by lack of time:

• There is no query that translate to a query-by-name on 'compact' CF (dynamic or composite dense ones). Allowing it would mean allowing IN relation for the last part of multi-component PRIMARY KEY.
• As I was hinting in a previous comment, select, update and delete now have a post-parsing validation/preprocessing phase that is not totally trivial. He would be a little more efficient (and not hard) to make that preprocessing parts of the prepared statements preparation.

The '..' notation for slices is removed

-1. We at Rackspace use this extensively. Also, pre-emptive -1 on supporting it for one more version and then removing it.

I've made static definitions (i.e, those definitions that don't use COMPACT STORAGE basically) really static.

-1. The two reasons you gave are subjective. IMO (also subjective), they're not strong enough for introducing backwards-incompatible changes.

I'm going to make all column definition case insensitive

Ugh: "because PGSQL does it." How about we leave them the way they are BECAUSE USERS EXPECT IT TO BE THAT WAY?

I think CQL developers have forgotten that people are actually using CQL and are using it because it is a more compelling interface than the old thrift. If this API changes as often as the thrift API did (and it is beginning to look that way), what have we gained?

Setting aside any discussion of the specific changes being proposed for a moment, I would like to point out that I count at least 3 breaking changes to the language (a third new major language version in as many releases), that we're proposing making these changes late in the 11th hour of the release cycle, and without seeking input from the wider community (I can't imagine too many people are able to follow this issue any more).

First, let's be clear about our priorities here. While backwards compatibility is an important consideration, it is not a guarantee we have made for CQL, nor should we at this stage in its development.

As Eric points out, we have made breaking changes deliberately before, because updating the language to add missing functionality made clear where we'd gotten something wrong. Expecting anything else from a partially finished language is totally unrealistic.

Keep in mind as well that maintaining deprecated features indefinitely is a resource sink that we can ill afford. We have a relatively small set of active developers, and time spent working around code supporting obsolete features, perhaps fixing regressions caused by keeping it alive, is time we can ill afford. Unlike Thrift, where methods live in relative isolation in CassandraServer, everything in the CQL parser and QueryProcessor is intertwined. So there is a higher price to pay than you may realize.

What should you do if backwards compatibility is your highest priority? You should absolutely be using the Thrift API, where we have guaranteed 100% backwards compatibility. There are legitimate reasons to be a CQL early adopter (primarily ease of driver implementation, which I assume is what drove your decision to go that route for Node.js) but backwards compatibility is not one of them.

Will we get to a point eventually where we can say "100% backwards compatibility in CQL from here on out?" Absolutely. But we're just not there yet. Fortunately, there aren't many production CQL installations yet, so, with all due respect for your situation, we can fix mistakes and add features without too much pain yet, compared to the pain from leaving them festering (and not adding features like this one).

But the longer we go before making necessary changes and finishing things, the more painful that will get. So, to Eric's point, better to make these changes now – which I've been promising in public since at least Cassandra SF, so I don't think it's valid to cry end-of-release foul – than to make them for the 1.2 transition, when presumably more people will have deployed on CQL.

This is a long way of saying that as a general principle, dropping blanket -1s on CQL changes to force compatibility-by-stagnation is irresponsible at this point.

Now, to the specifics of your objections:

pre-emptive -1 on supporting [.. notation] for one more version and then removing it

We could probably stretch this out to two releases, but as I've explained, we shouldn't be committing to "forever". We need to be able to fix misfeatures without paying the tax of keeping deprecated interfaces around indefinitely.

The two reasons you gave are subjective

Let me elaborate. We're not actually removing functionality here. The new-style definition of

CREATE TABLE foo ( 
    key <type1> PRIMARY KEY, 
    column <type2>, 
    value <type3> 
) WITH COMPACT STORAGE 

gives you the same freedom of using arbitrary column names as an old-style declaration with a comparator of type2. The difference is, this definition allows CQL queries to know how to turn the column name into a resultset value, allowing it to be used in more flexible WHERE clauses than .., as well as allowing use in paging across multiple rows (necessary for wide-row map/reduce support).

So, we're not taking away the flexibility to use column names at run time for non-composite columns; just requiring that if you want to use a CF in a dynamic, wide-row way, you tell us about it so we can support you appropriately. (Knowing when a CF is "wide row oriented" also opens up new optimization possibilities, from simple ones like skipping the row-level bloom filter to fancier ones like CASSANDRA-3581.)

How about we leave them the way they are BECAUSE USERS EXPECT IT TO BE THAT WAY?

Hmm. I'm not sure if you misread what he said and thought he's changing it to case-sensitive, or if you think case-sensitive is how it behaves now. Or maybe something else entirely.

Currently, column names are case insensitive, (see QueryProcessor.getColumnNames), and so are row keys (WhereClause.extractKeysFromColumns). But, right now you're allowed to define a CF with columns "a" and "A" but no way to disambiguate at SELECT time which one you actually want. So, the important part of what Sylvain is talking about here is "... unless they are enclosed in single or double quotes," i.e., to allow you to write SELECT "A" to be explicit about which one you want. I don't see this breaking anything.

(I don't think you're saying that users expect things to be case-sensitive, because CQL has never worked that way, but just in case, I'll also point out that both SQL and the CLI treat column names case insensitive, so I'd be very surprised if many users agree that sensitive is "expected.")

unless they are enclosed in single or double quotes

I do have my own minor gripe about this, actually, which is that single quotes means "string literal" to SQL. We should stick to double quotes when we need identifier disambiguation.

I do have my own minor gripe about this, actually, which is that single quotes means "string literal" to SQL. We should stick to double quotes when we need identifier disambiguation.

Except in SQL, integers, floats, uuids, etc., aren't valid column names. CQL already supports using (quoted) string literals as column names, which seems like it would be useful for disambiguating text column names that might otherwise parse as integers or whatever. It seems more natural to keep the string-literal notation used everywhere else in the language intact for column names.

Maybe this is no longer important with the new semantics, though. Haven't thought it through yet.

First, let's be clear about our priorities here. While backwards compatibility is an important consideration, it is not a guarantee we have made for CQL, nor should we at this stage in its development.

It's not about guarantees, it's about expectations.

Interface stability was the motiviating factor behind CQL. Other arguments became obvious later, yes, but that was what set the whole thing in motion. It was born out of requirements that my employer at the time had, and it was the reason they paid me to do the initial work.

Since then I've been very vocal about interface stability in every forum. It's a message that has resonated extremely well, and something that seemed to have consensus within the project; If I was speaking out of turn, no one stepped in to tell me.

TL;DR The absence of an iron-clad guarantee doesn't mean that breakage isn't unwanted, or won't come as a surprise. Breaking the language now (with plans already laid to break it again in 1.2) exceeds my expectations, and I know it exceeds others.

As Eric points out, we have made breaking changes deliberately before, because updating the language to add missing functionality made clear where we'd gotten something wrong. Expecting anything else from a partially finished language is totally unrealistic.

Keep in mind as well that maintaining deprecated features indefinitely is a resource sink that we can ill afford. We have a relatively small set of active developers, and time spent working around code supporting obsolete features, perhaps fixing regressions caused by keeping it alive, is time we can ill afford. Unlike Thrift, where methods live in relative isolation in CassandraServer, everything in the CQL parser and QueryProcessor is intertwined. So there is a higher price to pay than you may realize.

Of course there is a balance to strike, and a cost to pay for maintaining backward compatibility. But, we've used this argument for years and IMO people are starting to expecting more from us now.

But the longer we go before making necessary changes and finishing things, the more painful that will get. So, to Eric's point, better to make these changes now – which I've been promising in public since at least Cassandra SF, so I don't think it's valid to cry end-of-release foul – than to make them for the 1.2 transition, when presumably more people will have deployed on CQL.

I'm crying foul because we're proposing to break the language very late in the game. How often people are expecting CQL to break notwithstanding, anyone who has been trying to plan their projects around what they expect to land in 1.1 are in for a surprise. If no one is making such plans, then perhaps it's because they've given up trying. Either way it's not good.

Doing this so late in the game, also increases the likelihood that something will be missed and that things will need to change again later.

And I'm crying foul because this ticket was contentious and required a lot of discussion to reach consensus (which I am very thankful of when looking back at what might have been implemented otherwise), and yet it seems we're all too ready to pull the trigger on this potentially controversial "errata" without taking it to the wider community.

I know this is publicly available, but you'd be hard pressed to do a better job of obfuscating a discussion than having it in the comments of this issue.

Let me elaborate. We're not actually removing functionality here. The new-style definition of

CREATE TABLE foo (
   key <type1> PRIMARY KEY,
   column <type2>,
   value <type3>
) WITH COMPACT STORAGE

gives you the same freedom of using arbitrary column names as an old-style declaration with a comparator of type2. The difference is, this definition allows CQL queries to know how to turn the column name into a resultset value, allowing it to be used in more flexible WHERE clauses than .., as well as allowing use in paging across multiple rows (necessary for wide-row map/reduce support).

So, we're not taking away the flexibility to use column names at run time for non-composite columns; just requiring that if you want to use a CF in a dynamic, wide-row way, you tell us about it so we can support you appropriately. (Knowing when a CF is "wide row oriented" also opens up new optimization possibilities, from simple ones like skipping the row-level bloom filter to fancier ones like CASSANDRA-3581.)

What is the effect on users with an existing schema?

Hmm. I'm not sure if you misread what he said and thought he's changing it to case-sensitive, or if you think case-sensitive is how it behaves now. Or maybe something else entirely.

Currently, column names are case insensitive, (see QueryProcessor.getColumnNames), and so are row keys (WhereClause.extractKeysFromColumns). But, right now you're allowed to define a CF with columns "a" and "A" but no way to disambiguate at SELECT time which one you actually want. So, the important part of what Sylvain is talking about here is "... unless they are enclosed in single or double quotes," i.e., to allow you to write SELECT "A" to be explicit about which one you want. I don't see this breaking anything.

I don't understand this, they look to be case-sensitive to me. Are we talking about the same thing?

Of course there is a balance to strike, and a cost to pay for maintaining backward compatibility. But, we've used this argument for years and IMO people are starting to expecting more from us now.

The problem with the '..' notation is that it works very differently from the new proposed way to handle wide rows. In particular, maintaining it deprecated forever means maintaining the 'SELECT FIRST', the 'SELECT REVERSED' notations too, as well as the otherwise useless CREATE notations 'WITH comparator = <comparator>' and 'WITH default_validator = <comparator>'. It also means keeping forever the otherwise useless name_types and default_validator fields of the CqlResult. And all of this will require a fair amount of specific code since those work rather differently from their preferred replacement. I do think it makes the balance leaning clearly over 'not maintaining deprecated features forever' for this one.

How often people are expecting CQL to break notwithstanding, anyone who has been trying to plan their projects around what they expect to land in 1.1 are in for a surprise.

While I did suggested removing '..' from 1.1 in a previous comment, I apologize, let me take that back. I was more describing the current state of the patch in my previous comment and laying out a number of point for discussion. But as said in a follow up comment, I'm good just deprecating '..' and keeping it a little bit longer to make transition easier (and thus only creating a real language break for 1.2). But keeping both '..' and the proposition of this ticket forever is not a very viable option imho.

What is the effect on users with an existing schema?

For their static CF, if they have a few columns that are not declared in the schema, they may have to do a bunch of ALTER CF to add them. For dynamic CFs, they'd also need to do a ALTER CF to basically declare a schema like the one descibe a the beginning of that comment (current patch don't allow that yet, but it's on the todo list and not a hard one to add).

I don't understand this, they look to be case-sensitive to me. Are we talking about the same thing?

If I'm not mistaken, currently the PRIMARY KEY (i.e the key_alias) is case-sensitive and the other columns are case-insensitive. It is this difference I'm proposing to solve basically. And this patch introduces more aliases (column_aliases and value_alias to take the patch naming), so keeping the difference would be more confusing I believe. So the proposal is to treat every name declared in a CREATE TABLE the same way, whether they are part of the PRIMARY KEY or not, and whether the CF uses COMPACT STORAGE or not. Note that we could use case-sensitivity by default for everything. I'd be good with that if we think it's better. But it seems to me that re-using the SQL idea to have case-insensitivity by default with a mean to force case-sensitivity has a user friendliness to it.

we're proposing making these changes late in the 11th hour of the release cycle, and without seeking input from the wider community

I don't understand. You emailed the community for input, we created a wiki explaining the options. We've run out of paint for the bikeshed. Now that the work is being done we need to go back and re-explain the issue?

Have we stated either way to the community that the current form of CQL is going to be backwards compatible? I understand that its the end goal but we are not there yet. If it was considered done why are we discussing handling wide rows in the first place?

I think the best option here is to keep the old cql around in whatever form it currently is and start fresh with this transposed approach. If/When the current users get around to changing syntax then we can drop it.

</badcop>

I think the best option here is to keep the old cql around in whatever form it currently is and start fresh with this transposed approach

I actually think this may be the best idea to move forward. The proposals this ticket makes go farther than the initial proposition of supporting composites. It also adds a reasonable support for wide rows, and while doing that, it allows for a number of other changes that makes the language more regular. I am convinced that those changes are for the better, and so far I have see no other argument against except for compatibility break. But it's actually more complicated (and to a point contradictory) to add this while keeping backward compatibility.

So following Jake's idea, I propose to create a new cql3 package in the code to implement the ideas of this ticket without the burden of keeping backward compatibility, nor the fear to break previous versions. We would then make it clear that this new version is beta for 1.1 and it would be opt-in at this point. Which would give us time to get feedback from the community, but at least we'd get feedback on an actual implementation. And of course, it would leave us the choice to adjust on a number of details. This probably mean we'll want to add a new version parameter to the thrift call or something but that's probably a good thing to have anyway.

I sympathize with Rackspace's (the employer Eric referred to, for those following along) desire to build their new rewrite-everything-on-Node.js stack on CQL, because it is much easier than building on Thrift. That goal, we've delivered on.

But wishing for an alpha-release API to be 100% stable is a level of fantasy that requires either much more optimism or a much larger ego to think that you got it THAT right on your first try without ever building anything substantial on it first, than I'm capable of generating. And we really are talking alpha here; beta implies feature completness, or close to it.

The users I've talked see clearly that although the ultimate goal does include stability, an incomplete API is simply not ready to deliver on that. Which is why you see the overwhelming majority of development still done on "classic" clients like Hector and pycassa. (And which is why I'm pushing hard to make CQL3/1.1 feature complete. I do want CQL to succeed, but I'm realistic about where it stands today.)

Jake and Sylvain's proposal sounds like the only way we're going to be able to deliver the remaining features we need while still maintaining a compatibility escape hatch for applications built on early CQL version. But let's be clear: past 1.1, anyone who wants the old cql package maintained is going to need to roll up his sleeves and pitch in, because the rest of us have plenty of things to spend our time on that are more valuable to the community as a whole.

Apologies for quoting from difference sources.

(Jonathan) This is a long way of saying that as a general principle, dropping blanket -1s on CQL changes to force compatibility-by-stagnation is irresponsible at this point.

(Jake) We've run out of paint for the bikeshed. Now that the work is being done we need to go back and re-explain the issue?

Sorry. I traded my cassandra dev hat for a user hat a year ago cannot devote the same kind of energy towards tracking the project. Tracking tickets like this is difficult. I wasn't suggesting abandoning wide-row support or letting CQL stagnate though. I was objecting to a set of changes that I see as being disruptive towards at least a subset of CQL users.

One of the strongest cases for CQL is user experience. The previously proposed (prior to today) changes do not make CQL a better user experience.

(Sylvain) So following Jake's idea, I propose to create a new cql3 package in the code to implement the ideas of this ticket without the burden of keeping backward compatibility, nor the fear to break previous versions.

(Sylvain) This probably mean we'll want to add a new version parameter to the thrift call or something but that's probably a good thing to have anyway.

If I understand this correctly, it sounds like a "compatability mode" where the driver (or whatever) stipulates the mode and can go about it's business using that level of syntax. As a driver developer and a CQL user, I think this is a good idea.

(Jonathan) But wishing for an alpha-release API to be 100% stable is a level of fantasy...

Rubbish. CQL has been 1.0 since 0.8. That's not alpha. This may boil down to a perception of what 1.0. means, but I think it is safe to say that it doesn't imply alpha. 1.0, to me at least, has always implied 'ready for production' and 'please use this now'.

(Jonathan) But let's be clear: past 1.1, anyone who wants the old cql package maintained is going to need to roll up his sleeves and pitch in...

I totally get that things need to change and am behind what Jake and Sylvain propose. My hope was that more consideration be given to user impact before deciding to break compatability. I think it will be important that users perceive CQL to be stable enough to build applications with. Three backwards incompatible versions in lockstep with three releases of Cassandra doesn't signal that to me.

CQL has been 1.0 since 0.8. That's not alpha.

The 1.0 designation was Eric's. I didn't push back because I remembered when he said this:

the only reason to fuss about a 1.0, is that it is loaded with special meaning. To impart some vague notion of readiness on people who should be paying less attention to a number, and doing more due diligence.

I also take this part of the CQL docs to mean that a X.Y version number implies no stability or maturity, only levels of compatibility:

Versioning of the CQL language adheres to the "Semantic Versioning":http://semver.org guidelines.  Versions take the form X.Y.Z where X, Y, and Z are integer values representing major, minor, and patch level respectively.

In any case, a rose by any other name would smell as sweet, and calling an incomplete, untested API 1.0 doesn't make it non-alpha.

As a user, this is really hard to follow, however this definitely affects me.

I really bought into CQL to make it so API changes were less brutal.
I also bought in because this was a better user experience, I've used cassandra for a long time and spinning dev's up on the thrift API was difficult.

Lastly, I imagine this will actually affect the upgrade path for companies using CQL, just like the reason we never upgraded from 0.6 -> * on a product was the changeset and amount of moving parts were too great (It wasn't just that reason, there are other forces at work, don't get me wrong).

These type of things hurt the user community, and need to be treated with care, especially as this product has really grown into something important to A LOT of people (like myself).

Just my 2 cents

I don't understand. You emailed the community for input, we created a wiki explaining the options. We've run out of paint for the bikeshed. Now that the work is being done we need to go back and re-explain the issue?

It was discussed, consensus reached, and then brand new, additional, and disruptive changes were tacked on, so yes. Is that really so unreasonable?

But wishing for an alpha-release API to be 100% stable is a level of fantasy that requires either much more optimism or a much larger ego to think that you got it THAT right on your first try without ever building anything substantial on it first, than I'm capable of generating. And we really are talking alpha here; beta implies feature completness, or close to it.

Getting back to expectations, where was the expectation set that CQL was alpha-release? I know I'm not the only one who missed that memo. Granted, it's not feature-complete, but what is? Cassandra isn't feature-complete either (or we wouldn't still be adding features).

The users I've talked see clearly that although the ultimate goal does include stability, an incomplete API is simply not ready to deliver on that. Which is why you see the overwhelming majority of development still done on "classic" clients like Hector and pycassa. (And which is why I'm pushing hard to make CQL3/1.1 feature complete. I do want CQL to succeed, but I'm realistic about where it stands today.)

Are you saying that after CQL3/1.1, that people can expect some level of stability? I would of course love to see a "Yes", but pretending that the need to add or make changes won't continue, and the arguments about backward compatibility being Hard won't continue to be relevant, require much more optimism than I'm capable of generating.

And to be clear, when I talk about setting expectations for stability, I don't mean "No Changes, Ever". Like I said, I understand there is a balance to strike. But, other than assuming breakage and being pleasantly surprised if you dodge a bullet, what can our users expect today?

Jake and Sylvain's proposal sounds like the only way we're going to be able to deliver the remaining features we need while still maintaining a compatibility escape hatch for applications built on early CQL version. But let's be clear: past 1.1, anyone who wants the old cql package maintained is going to need to roll up his sleeves and pitch in, because the rest of us have plenty of things to spend our time on that are more valuable to the community as a whole.

I think this an excellent idea, especially if we're very clear that CQL3 is volatile. When the time comes that we're willing to commit to something, we should be clear about that too. Bonus points if we can make a 2->3 transition reasonable, or at least reasonably documented.

And, even if the number of CQL <= 2.0 users is small enough to be considered acceptable collateral damage, an orderly transition like this will go a long way toward showing everyone that we're trying not to burn them.

The 1.0 designation was Eric's. I didn't push back because...

Actually, the version I was going with at the time was 0.99. My reasoning was that I felt that until the features that had been implemented got some real usage, it was naive to think that they were right. I described it at the time as a way of communicating that it was meant to represent 1.0, but that the door was cracked open, just in case.

You (Jonathan) argued that it should be 1.0, because 0.99 would send the message that it wasn't ready, and would result in less adoption.

This issue is realllllllllllly long. So I've created CASSANDRA-3761 for CQL 3.0, which basically will be the changes of the patch and whatever other changes I'm claiming are implied. It feels vaguely reasonable to switch to a more general ticket anyway now that we've decided on a number of things (the main syntax, the fact that we'll keep support for 2.0 for now etc...).

I'll update CASSANDRA-3761 with a patch and more details about that patch soon, real soon.

Closing this as duplicate then.