Cassandra
  1. Cassandra
  2. CASSANDRA-2474

CQL support for compound columns and wide rows

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Duplicate
    • Fix Version/s: None
    • Component/s: API, Core
    • Labels:

      Description

      For the most part, this boils down to supporting the specification of compound column names (the CQL syntax is colon-delimted terms), and then teaching the decoders (drivers) to create structures from the results.

      1. 0002-thrift-generated-code.patch
        58 kB
        Sylvain Lebresne
      2. 0001-Add-support-for-wide-and-composite-CFs.patch
        184 kB
        Sylvain Lebresne
      3. cql_tests.py
        7 kB
        Sylvain Lebresne
      4. 2474-transposed-select-no-sparse.PNG
        9 kB
        Jonathan Ellis
      5. raw_composite.txt
        0.6 kB
        Sylvain Lebresne
      6. 2474-transposed-select.PNG
        5 kB
        Jonathan Ellis
      7. 2474-transposed-1.PNG
        8 kB
        Jonathan Ellis
      8. 2474-transposed-raw.PNG
        8 kB
        Jonathan Ellis
      9. ASF.LICENSE.NOT.GRANTED--screenshot-2.jpg
        123 kB
        Jonathan Ellis
      10. ASF.LICENSE.NOT.GRANTED--screenshot-1.jpg
        26 kB
        Jonathan Ellis

        Issue Links

          Activity

          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Rick Shaw added a comment -

          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.

          Show
          Rick Shaw added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment - - edited

          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;
          
          Show
          T Jake Luciani added a comment - - edited 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;
          Hide
          Jonathan Ellis added a comment -

          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?)

          Show
          Jonathan Ellis added a comment - 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?)
          Hide
          T Jake Luciani added a comment -

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

          Show
          T Jake Luciani added a comment - 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'"
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Rick Shaw added a comment -

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

          Show
          Rick Shaw added a comment - How about a nested SELECT? This is a familiar paradigm and allows you the staged isolation and no new syntax?
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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?

          Show
          Jonathan Ellis added a comment - 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?
          Hide
          T Jake Luciani added a comment -

          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

          Show
          T Jake Luciani added a comment - 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
          Hide
          Rick Shaw added a comment -

          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.

          Show
          Rick Shaw added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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?)

          Show
          Jonathan Ellis added a comment - 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?)
          Hide
          T Jake Luciani added a comment -

          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

          Show
          T Jake Luciani added a comment - 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
          Hide
          Jonathan Ellis added a comment -

          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:

          {subcol1:val1, subcol2: val2}

          , col2:

          {subcol1: v3, subcol2: v4}

          }

          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.

          Show
          Jonathan Ellis added a comment - 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: {subcol1:val1, subcol2: val2} , col2: {subcol1: v3, subcol2: v4} } 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.
          Hide
          T Jake Luciani added a comment -

          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?

          Show
          T Jake Luciani added a comment - 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?
          Hide
          Jonathan Ellis added a comment - - edited

          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
          
          Show
          Jonathan Ellis added a comment - - edited 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
          Hide
          T Jake Luciani added a comment -

          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?

          Show
          T Jake Luciani added a comment - 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?
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Eric Evans added a comment -

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

          Show
          Eric Evans added a comment - This issue could stand to be summarized (I still wish we used a mailing list for monsters like this).
          Hide
          Jonathan Ellis added a comment -

          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).

          Show
          Jonathan Ellis added a comment - 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).
          Hide
          Jonathan Ellis added a comment -

          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;
          
          Show
          Jonathan Ellis added a comment - 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;
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - Isn't changing query semantics kind of the opposite of what hints are supposed to be for?
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment - - edited

          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

          Show
          T Jake Luciani added a comment - - edited 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
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Jonathan Ellis added a comment - - edited

          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
          
          Show
          Jonathan Ellis added a comment - - edited 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
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Jonathan Ellis added a comment - - edited

          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)

          Show
          Jonathan Ellis added a comment - - edited 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)
          Hide
          T Jake Luciani added a comment - - edited

          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

          Show
          T Jake Luciani added a comment - - edited 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
          Hide
          Jonathan Ellis added a comment - - edited

          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
          
          Show
          Jonathan Ellis added a comment - - edited 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
          Hide
          T Jake Luciani added a comment -

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

          Show
          T Jake Luciani added a comment - That sounds good, except I think somthing.somthing is the database.table syntax so might need to pick a different delimiter? ":" ?
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          Jonathan Ellis added a comment - - edited

          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.

          Show
          Jonathan Ellis added a comment - - edited 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.
          Hide
          Pavel Yaskevich added a comment -

          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
          
          Show
          Pavel Yaskevich added a comment - 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
          Hide
          Pavel Yaskevich added a comment - - edited

          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.

          Show
          Pavel Yaskevich added a comment - - edited 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.
          Hide
          Jonathan Ellis added a comment -

          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=[

          {component1_alias: 'date', value_alias: 'price', ...}

          ]. 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.

          Show
          Jonathan Ellis added a comment - 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=[ {component1_alias: 'date', value_alias: 'price', ...} ]. 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.
          Hide
          Pavel Yaskevich added a comment -

          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...

          Show
          Pavel Yaskevich added a comment - 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...
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Pavel Yaskevich added a comment - - edited

          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

          Show
          Pavel Yaskevich added a comment - - edited 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
          Hide
          T Jake Luciani added a comment -

          What happened to the table:transposed syntax?

          Show
          T Jake Luciani added a comment - What happened to the table:transposed syntax?
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

          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?

          Show
          Pavel Yaskevich added a comment - 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?
          Hide
          Jonathan Ellis added a comment -

          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 ...
          
          Show
          Jonathan Ellis added a comment - 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 ...
          Hide
          Pavel Yaskevich added a comment -

          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...

          Show
          Pavel Yaskevich added a comment - 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...
          Hide
          Jonathan Ellis added a comment -

          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?

          Show
          Jonathan Ellis added a comment - 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?
          Hide
          Pavel Yaskevich added a comment -

          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

          Show
          Pavel Yaskevich added a comment - 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
          Hide
          Rick Shaw added a comment -

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

          Show
          Rick Shaw added a comment - Star would be better... And ? would conflict with PreparedStatement type data binding.
          Hide
          Pavel Yaskevich added a comment -

          I don't have anything against using star .

          Show
          Pavel Yaskevich added a comment - I don't have anything against using star .
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Pavel Yaskevich added a comment - - edited

          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.

          Show
          Pavel Yaskevich added a comment - - edited 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.
          Hide
          Rick Shaw added a comment -

          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?

          Show
          Rick Shaw added a comment - 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?
          Hide
          Pavel Yaskevich added a comment -

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

          Show
          Pavel Yaskevich added a comment - I think that "as" is more appropriate than "with" in compound column context because we use name "as" collection of components (aliases).
          Hide
          Rick Shaw added a comment -

          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.

          Show
          Rick Shaw added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

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

          Show
          Pavel Yaskevich added a comment - I understand your point but semantically "with" is not appropriate here.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

          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>;
          
          Show
          Pavel Yaskevich added a comment - 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>;
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

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

          Show
          Pavel Yaskevich added a comment - The core difference is that (..,..,..) notation will return given aliases (category, subcategory) as column names in the results.
          Hide
          Sylvain Lebresne added a comment -

          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' ?

          Show
          Sylvain Lebresne added a comment - 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' ?
          Hide
          Jonathan Ellis added a comment - - edited

          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.

          Show
          Jonathan Ellis added a comment - - edited 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.
          Hide
          Pavel Yaskevich added a comment -

          @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.

          Show
          Pavel Yaskevich added a comment - @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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Sylvain Lebresne added a comment - - edited

          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.

          Show
          Sylvain Lebresne added a comment - - edited 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: 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. 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment - - edited

          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
          
          Show
          T Jake Luciani added a comment - - edited 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
          Hide
          Pavel Yaskevich added a comment -

          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>;
          
          Show
          Pavel Yaskevich added a comment - 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>;
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - We can't add syntax like TRANSPOSE or TO to Hive without forking it.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Pavel Yaskevich added a comment - - edited

          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');
          
          Show
          Pavel Yaskevich added a comment - - edited 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');
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          T Jake Luciani added a comment -

          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');
          
          Show
          T Jake Luciani added a comment - 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');
          Hide
          Jonathan Ellis added a comment -

          +1

          Show
          Jonathan Ellis added a comment - +1
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - We also should support defining CompositeType columns w/o using the internal AbstractType names.
          Hide
          Jonathan Ellis added a comment -

          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

          Show
          Jonathan Ellis added a comment - 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
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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

          {(08ec87a0-2cc3-4982-a0e7-a434884451f8, 'posted_by'): 524342}

          and

          {(08ec87a0-2cc3-4982-a0e7-a434884451f8, 'body'): 'CQL FTW'}

          . 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.

          Show
          Jonathan Ellis added a comment - 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 {(08ec87a0-2cc3-4982-a0e7-a434884451f8, 'posted_by'): 524342} and {(08ec87a0-2cc3-4982-a0e7-a434884451f8, 'body'): 'CQL FTW'} . 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.
          Hide
          Pavel Yaskevich added a comment -

          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.

          Show
          Pavel Yaskevich added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

          Agreed.

          Show
          Pavel Yaskevich added a comment - Agreed.
          Hide
          T Jake Luciani added a comment - - edited

          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?

          Show
          T Jake Luciani added a comment - - edited 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?
          Hide
          Pavel Yaskevich added a comment -

          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?

          Show
          Pavel Yaskevich added a comment - 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?
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Pavel Yaskevich added a comment -

          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.

          Show
          Pavel Yaskevich added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          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?

          Show
          T Jake Luciani added a comment - 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?
          Hide
          Jonathan Ellis added a comment -

          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

          Show
          Jonathan Ellis added a comment - 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
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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: 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. 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.
          Hide
          Jonathan Ellis added a comment - - edited

          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.)

          Show
          Jonathan Ellis added a comment - - edited 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.)
          Hide
          Jonathan Ellis added a comment - - edited

          So, you could do queries like

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

          Which would give the resultset shown in

          Show
          Jonathan Ellis added a comment - - edited So, you could do queries like SELECT * FROM timeline WHERE user_id = 'tjefferson' AND posted_at > 1770; Which would give the resultset shown in
          Hide
          Jonathan Ellis added a comment -

          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).

          Show
          Jonathan Ellis added a comment - 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).
          Hide
          Pavel Yaskevich added a comment -

          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.

          Show
          Pavel Yaskevich added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - I like it better with the TRANSPOSED information in a separate block where it messes w/ standard CREATE grammar less.
          Hide
          Pavel Yaskevich added a comment - - edited

          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);
          
          Show
          Pavel Yaskevich added a comment - - edited 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);
          Hide
          Sylvain Lebresne added a comment -

          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).

          Show
          Sylvain Lebresne added a comment - 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: a reasonably generic way to access CF with composite comparator in a CQL-ish way: the TRANSPOSED part. 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).
          Hide
          Jonathan Ellis added a comment - - edited

          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.

          Show
          Jonathan Ellis added a comment - - edited 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.
          Hide
          Sylvain Lebresne added a comment -

          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

          Show
          Sylvain Lebresne added a comment - 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
          Hide
          T Jake Luciani added a comment -

          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,*);
          
          Show
          T Jake Luciani added a comment - 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,*);
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment -

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

          That would be the value of the composite column

          Show
          T Jake Luciani added a comment - Wouldn't you need body in the composite list too? That would be the value of the composite column
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - "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.
          Hide
          Sylvain Lebresne added a comment -

          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').

          Show
          Sylvain Lebresne added a comment - 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').
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          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")
          
          Show
          T Jake Luciani added a comment - 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")
          Hide
          Matt Stump added a comment -

          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.

          Show
          Matt Stump added a comment - 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.
          Hide
          Jonathan Ellis added a comment - - edited

          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.

          Show
          Jonathan Ellis added a comment - - edited 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.
          Hide
          Jonathan Ellis added a comment -

          (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.)

          Show
          Jonathan Ellis added a comment - (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 .)
          Hide
          T Jake Luciani added a comment -

          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.

          Show
          T Jake Luciani added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          Jonathan Ellis added a comment - - edited

          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.

          Show
          Jonathan Ellis added a comment - - edited 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.
          Hide
          Jonathan Ellis added a comment -

          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).

          Show
          Jonathan Ellis added a comment - 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).
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          Sylvain Lebresne added a comment -

          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)
            
          Show
          Sylvain Lebresne added a comment - 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 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) 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)
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          I'm fine with CQL only

          Show
          T Jake Luciani added a comment - I'm fine with CQL only
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Eric Evans added a comment -

          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).

          Show
          Eric Evans added a comment - 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).
          Hide
          Pavel Yaskevich added a comment -

          +1 on experimental mode.

          Show
          Pavel Yaskevich added a comment - +1 on experimental mode.
          Hide
          Jonathan Ellis added a comment -

          -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.

          Show
          Jonathan Ellis added a comment - -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.
          Hide
          Eric Evans added a comment -

          -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).

          Show
          Eric Evans added a comment - -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).
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - Speaking of changing stuff, this makes WITH COMPARATOR and WITH VALIDATOR obsolete.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Eric Evans added a comment -

          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?

          Show
          Eric Evans added a comment - 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?
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Eric Evans added a comment -

          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?

          Show
          Eric Evans added a comment - 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?
          Hide
          Jonathan Ellis added a comment -

          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).

          Show
          Jonathan Ellis added a comment - 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).
          Hide
          Jonathan Ellis added a comment -

          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.")

          Show
          Jonathan Ellis added a comment - 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.")
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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: 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. 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.
          Hide
          Eric Evans added a comment -

          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.

          Show
          Eric Evans added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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
          Show
          Sylvain Lebresne added a comment - 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
          Hide
          paul cannon added a comment -

          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?

          Show
          paul cannon added a comment - 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?
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          T Jake Luciani added a comment - - edited

          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

          Show
          T Jake Luciani added a comment - - edited 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
          Hide
          Jonathan Ellis added a comment -

          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.)

          Show
          Jonathan Ellis added a comment - 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.)
          Hide
          Jonathan Ellis added a comment -

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

          Show
          Jonathan Ellis added a comment - (BTW, why test this with dtest instead of just single node mode?)
          Hide
          paul cannon added a comment -

          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.

          Show
          paul cannon added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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).

          Show
          Sylvain Lebresne added a comment - 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: 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 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).
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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...

          Show
          Jonathan Ellis added a comment - 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...
          Hide
          Sylvain Lebresne added a comment -

          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).

          Show
          Sylvain Lebresne added a comment - 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).
          Hide
          Sylvain Lebresne added a comment -

          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.
          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Gary Dusbabek added a comment -

          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?

          Show
          Gary Dusbabek added a comment - 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?
          Hide
          Eric Evans added a comment -

          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).

          Show
          Eric Evans added a comment - 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).
          Hide
          Jonathan Ellis added a comment -

          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.")

          Show
          Jonathan Ellis added a comment - 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.")
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          paul cannon added a comment -

          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.

          Show
          paul cannon added a comment - 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.
          Hide
          Eric Evans added a comment -

          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?

          Show
          Eric Evans added a comment - 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?
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          T Jake Luciani added a comment -

          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>

          Show
          T Jake Luciani added a comment - 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>
          Hide
          Sylvain Lebresne added a comment -

          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.

          Show
          Sylvain Lebresne added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Gary Dusbabek added a comment -

          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.

          Show
          Gary Dusbabek added a comment - 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.
          Hide
          Jonathan Ellis added a comment -

          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.

          Show
          Jonathan Ellis added a comment - 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.
          Hide
          Dan Di Spaltro added a comment -

          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

          Show
          Dan Di Spaltro added a comment - 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
          Hide
          Eric Evans added a comment -

          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?

          Show
          Eric Evans added a comment - 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?
          Hide
          Eric Evans added a comment -

          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.

          Show
          Eric Evans added a comment - 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.
          Hide
          Eric Evans added a comment -

          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.

          Show
          Eric Evans added a comment - 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.
          Hide
          Sylvain Lebresne added a comment - - edited

          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.

          Show
          Sylvain Lebresne added a comment - - edited 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.
          Hide
          Jonathan Ellis added a comment -

          Closing this as duplicate then.

          Show
          Jonathan Ellis added a comment - Closing this as duplicate then.

            People

            • Assignee:
              Unassigned
              Reporter:
              Eric Evans
            • Votes:
              10 Vote for this issue
              Watchers:
              21 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development