[ https://issues.apache.org/jira/browse/CASSANDRA-7062?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Sylvain Lebresne resolved CASSANDRA-7062. ----------------------------------------- Resolution: Won't Fix > Extension of static columns for compound cluster keys > ----------------------------------------------------- > > Key: CASSANDRA-7062 > URL: https://issues.apache.org/jira/browse/CASSANDRA-7062 > Project: Cassandra > Issue Type: New Feature > Reporter: Constance Eustace > > CASSANDRA-6561 implemented static columns for a given partition key. > What this is proposing for a compound cluster key is a static column that is > static at intermediate parts of a compound cluster key. This example shows a > table modelling a moderately complex EAV pattern : > {code} > CREATE TABLE t ( > entityID text, > propertyName text, > valueIndex text, > entityName text static (entityID), > propertyType text static (entityID, propertyName), > propertyRelations List<text> static (entityID, propertyName), > data text, > PRIMARY KEY (entityID, (propertyName,valueIndex)) > ) > {code} > So in this example has the following static columns: > - the entityName column behaves exactly as CASSANDRA-6561 details, so all > cluster rows have the same value > - the propertyType and propertyRelations columns are static with respect to > the remaining parts of the cluster key (that is, across all valueIndex values > for a given propertyName), so an update to those values for an entityID and a > propertyName will be shared/constant by all the value rows... > Is this a relatively simple extension of the same mechanism in -6561, or is > this a "whoa, you have no idea what you are proposing"? > Sample data: > Mary and Jane aren't married... > {code} > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex, > data) VALUES ('0001','MARY MATALIN','married','SingleValue','0','false'); > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex, > data) VALUES ('0002','JANE JOHNSON','married','SingleValue','0','false'); > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex) > VALUES ('0001','MARY MATALIN','kids','NOVALUE',''); > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex) > VALUES ('0002','JANE JOHNSON','kids','NOVALUE',''); > {code} > {code} > SELECT * FROM t: > 0001 MARY MATALIN married SingleValue 0 false > 0001 MARY MATALIN kids NOVALUE null > 0002 JANE JOHNSON married SingleValue 0 false > 0002 JANE JOHNSON kids NOVALUE null > {code} > Then mary and jane get married (so the entityName column that is static on > the partition key is updated just like CASSANDRA-6561 ) > {code} > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex, > data) VALUES ('0001','MARY SMITH','married','SingleValue','0','TRUE'); > INSERT INTO t (entityID, entityName, propertyName, propertyType, valueIndex, > data) VALUES ('0002','JANE JONES','married','SingleValue','0','TRUE'); > {code} > {code} > SELECT * FROM t: > 0001 MARY SMITH married SingleValue 0 TRUE > 0001 MARY SMITH kids NOVALUE null > 0002 JANE JONES married SingleValue 0 TRUE > 0002 JANE JONES kids NOVALUE null > {code} > Then mary and jane have a kid, so we add another value to the kids attribute: > {code} > INSERT INTO t (entityID, propertyName, propertyType, valueIndex,data) VALUES > ('0001','kids','SingleValue','0','JIM-BOB'); > INSERT INTO t (entityID, propertyName, propertyType, valueIndex,data) VALUES > ('0002','kids','SingleValue','0','JENNY'); > {code} > {code} > SELECT * FROM t: > 0001 MARY SMITH married SingleValue 0 TRUE > 0001 MARY SMITH kids SingleValue null > 0001 MARY SMITH kids SingleValue 0 JIM-BOB > 0002 JANE JONES married SingleValue 0 TRUE > 0002 JANE JONES kids SingleValue null > 0002 JANE JONES kids SingleValue 0 JENNY > {code} > Then Mary has ANOTHER kid, which demonstrates the partially static column > relative to the cluster key, as ALL value rows for the property 'kids' get > updated to the new value: > {code} > INSERT INTO t (entityID, propertyName, propertyType, valueIndex,data) VALUES > ('0001','kids','MultiValue','1','HARRY'); > {code} > {code} > SELECT * FROM t: > 0001 MARY SMITH married SingleValue 0 TRUE > 0001 MARY SMITH kids MultiValue null > 0001 MARY SMITH kids MultiValue 0 JIM-BOB > 0001 MARY SMITH kids MultiValue 1 HARRY > 0002 JANE JONES married SingleValue 0 TRUE > 0002 JANE JONES kids SingleValue null > 0002 JANE JONES kids SingleValue 0 JENNY > {code} > ... ok, hopefully that example isn't TOO complicated. Yes, there's a stupid > hack bug in there with the null/empty row for the kids attribute, but please > bear with me on that > Generally speaking, this will aid in flattening / denormalization of > relational constructs into cassandra-friendly schemas. In the above example > we are flattening a relational schema of three tables: entity, property, and > value tables into a single sparse flattened denormalized compound table. -- This message was sent by Atlassian JIRA (v6.2#6252)