http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector Software, Support, and Training for Apache Cassandra en-US Fri, 24 May 2013 19:24:18 +0000 http://bbpress.org/?v=1.0.3 q http://www.datastax.com/support-forums/search.php http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1028 Fri, 06 Jan 2012 21:47:56 +0000 zznate 1028@http://www.datastax.com/support-forums/ <p>The original motivation on our side was some experimentation with persisting object graphs:<br /> <a href="https://github.com/riptano/hector-jpa" rel="nofollow">https://github.com/riptano/hector-jpa</a></p> <p>As you may have figured out, though it's been there for a few months, most folks have not really put composites through their paces. Specifically, the grave misunderstanding (and admitedly poor documentation of) DynamicComposites:<br /> <a href="https://issues.apache.org/jira/browse/CASSANDRA-3625" rel="nofollow">https://issues.apache.org/jira/browse/CASSANDRA-3625</a> </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1027 Fri, 06 Jan 2012 21:42:38 +0000 Anonymous 1027@http://www.datastax.com/support-forums/ <p>Yeah, it was this jira that led me to trying this out with composite types. Todd Nine's mention and example (quoted below) of a column scan made me think I might be able to scan within the first and second components. Obviously this is not yet possible as we figured out.</p> <blockquote><p> status &gt; 100 &amp;&amp; status &lt; 300 &amp;&amp; unitId = 10</p> <p>I would need to construct a column scan of the following to get correct result sets.</p> <p>start =&gt; 100+1+10+0</p> <p>end =&gt; 300+0+10+1 </p></blockquote> <p>So given Todd's example, "status &gt; 100 &amp;&amp; status &lt; 300 &amp;&amp; unitId = 10" works but "status &gt; 100 &amp;&amp; status &lt; 300 &amp;&amp; unitId &lt; 10" doesn't.</p> <p>Thanks. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1026 Fri, 06 Jan 2012 20:40:34 +0000 zznate 1026@http://www.datastax.com/support-forums/ <p>Just remembered a long ago thread regarding how composites do comparisons independently on different components (see <a href="https://issues.apache.org/jira/browse/CASSANDRA-2231" rel="nofollow">https://issues.apache.org/jira/browse/CASSANDRA-2231</a> if you are interested). You will get the expected results, all columns where the first component is "zero" with the following:</p> <p> Composite startRange = new Composite();<br /> startRange.addComponent(0,new Long(0), AbstractComposite.ComponentEquality.EQUAL);</p> <p> Composite endRange = new Composite();<br /> endRange.addComponent(0, new Long(0), AbstractComposite.ComponentEquality.GREATER_THAN_EQUAL);</p> <p> sliceQuery.setRange(startRange, endRange, false, 100); </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1025 Fri, 06 Jan 2012 20:19:42 +0000 Anonymous 1025@http://www.datastax.com/support-forums/ <p>Separately, I'm experimenting with using super columns which seems like a better way to go for my particular use case. Because composite column queries restrict component equality to one component (the first component), I cannot limit the query results like I can in the super column solution. So it seems for my use case, CompositeTypes are not an efficient substitute for SuperColumns. Here's why I think that is...</p> <p>Given data (T1:T2)=&gt; (2:1),(2:0),(1:0),(0:1),(0:0) - DESCENDING ordered<br /> Arguments t1=2 and t2=0 where T1&lt;=t1 and T2&lt;=t2</p> <p>Composite<br /> ----------<br /> slice_query(t1); /* returns (2:1),(2:0),(1:0),(0:1),(0:0) to client */<br /> client_filter(results_of_slice_query, t2); </p> <p>Super - Allows limit to be applied<br /> -----------------------------------<br /> super_slice_query(t1 limit 1); /* returns {(2:1),(2:0)} */<br /> client_filter(results_of_slice_query, t2); </p> <p>The Super column solution is not as efficient if the client_filter fails to find a T2 given t2 because it would require an iterative query. Maybe not so bad given we will have 1000s of different values for T1 and with a composite column solution, it would be inefficient to send all those to the client.</p> <p>There may be other solutions... </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1022 Fri, 06 Jan 2012 19:49:41 +0000 Anonymous 1022@http://www.datastax.com/support-forums/ <p>@zznate same problem with 1.0.6. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1018 Fri, 06 Jan 2012 18:29:00 +0000 zznate 1018@http://www.datastax.com/support-forums/ <p>@heskech can you try again with 1.0.6? There was a (vaguely related) issue that was addressed (but this is a stretch). </p> <p>You are correct that it should have filtered on equality for the first component - I'll take your code and try to duplicate what you are seeing. Thanks for your patience (and the completeness of your examples!). </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1016 Fri, 06 Jan 2012 17:07:42 +0000 Anonymous 1016@http://www.datastax.com/support-forums/ <p>I'm seeing bizarre results after implementing the suggestion to do the comparison on only the first component and then filter the second component on the client. The comparison on the first component gives incorrect results so I didn't proceed with the client LTE filter.</p> <p>Here's my test case:</p> <p><code><br /> TestCompositeSliceQuery t = new TestCompositeSliceQuery(keyspace, "Key2");<br /> t.queryOnlyFirstComponent(0);<br /> t.queryOnlyFirstComponent(1);<br /> </code><br /> `</p> <p>and the incorrect results:<br /> Querying Component1 as &lt;= 0. Result: # columns in slice: 0, Columns:<br /> Querying Component1 as &lt;= 1. Result: # columns in slice: 2, Columns:<br /> Component1: 0, Component2: 0<br /> Component1: 0, Component2: 1</p> <p>and the expected results:<br /> Querying Component1 as &lt;= 0. Result: # columns in slice: 2, Columns:<br /> Component1: 0, Component2: 0<br /> Component1: 0, Component2: 1<br /> Querying Component1 as &lt;= 1. Result: # columns in slice: 3, Columns:<br /> Component1: 1, Component2: 1<br /> Component1: 0, Component2: 0<br /> Component1: 0, Component2: 1</p> <p>It seems as though the LESS_THAN_EQUAL is being interpreted as LESS_THAN because if I increment component1Value, I get the correct results. Is this the case?</p> <p><code><br /> package tests;</p> <p>import java.util.List;</p> <p>import me.prettyprint.cassandra.serializers.CompositeSerializer;<br /> import me.prettyprint.cassandra.serializers.LongSerializer;<br /> import me.prettyprint.cassandra.serializers.StringSerializer;<br /> import me.prettyprint.hector.api.Keyspace;<br /> import me.prettyprint.hector.api.beans.AbstractComposite;<br /> import me.prettyprint.hector.api.beans.ColumnSlice;<br /> import me.prettyprint.hector.api.beans.Composite;<br /> import me.prettyprint.hector.api.beans.HColumn;</p> <p>import me.prettyprint.hector.api.factory.HFactory;<br /> import me.prettyprint.hector.api.query.QueryResult;<br /> import me.prettyprint.hector.api.query.SliceQuery;</p> <p>public class TestCompositeSliceQuery {</p> <p> private Keyspace keyspace;<br /> private String cf;</p> <p> public TestCompositeSliceQuery(Keyspace keyspace, String cf) {<br /> this.keyspace = keyspace;<br /> this.cf = cf;<br /> } </p> <p> public void queryOnlyFirstComponent(long component1Value) {<br /> SliceQuery&lt;String, Composite, String&gt; sq =<br /> HFactory.createSliceQuery(keyspace, StringSerializer.get(), new CompositeSerializer(), StringSerializer.get());</p> <p> sq.setColumnFamily(cf);<br /> sq.setKey("x");</p> <p> Composite endRange = new Composite();<br /> endRange.addComponent(component1Value, LongSerializer.get(), "LongType", AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);</p> <p> sq.setRange(null, endRange, false, 100);</p> <p> QueryResult&lt;ColumnSlice&lt;Composite,String&gt;&gt; r = sq.execute();<br /> ColumnSlice&lt;Composite, String&gt; slice = r.get();</p> <p> List&lt;HColumn&lt;Composite, String&gt;&gt; l = slice.getColumns();</p> <p> System.out.println("Querying Component1 as &lt;= " + component1Value + ". Result: # columns in slice: " + l.size() + ", Columns:");<br /> for (int i=0;i&lt;l.size();i++) {<br /> System.out.println(" Component1: " + l.get(i).getName().get(0, LongSerializer.get()) +<br /> ", Component2: " + l.get(i).getName().get(1, LongSerializer.get()));<br /> }<br /> }</p> <p>}</p> <p></code></p> <p>The Data:<br /> [default@TEST] get Key2[x];<br /> =&gt; (column=0:0, value=1, timestamp=1325798988709000)<br /> =&gt; (column=0:1, value=2, timestamp=1325799008549000)<br /> =&gt; (column=1:1, value=3, timestamp=1325799031192000)<br /> Returned 3 results.</p> <p>Schema:<br /> create column family Key2<br /> with column_type = 'Standard'<br /> and comparator = 'CompositeType(org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.LongType)' </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1015 Fri, 06 Jan 2012 15:19:46 +0000 Anonymous 1015@http://www.datastax.com/support-forums/ <p>It might be generally beneficial if one could do the filtering on the server side, as this is a rather common sort of bitemporal query. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1010 Fri, 06 Jan 2012 00:16:13 +0000 Anonymous 1010@http://www.datastax.com/support-forums/ <p>Thanks, I'll try adding only one component to the range, but first the results of changing limit and flipping reverse. In summary, doesn't look like ComponentEquality (on the outer) works at all with two components, though I'm still not clear why. The results: </p> <p><code><br /> create column family Key<br /> with column_type = 'Standard'<br /> and comparator = 'CompositeType(org.apache.cassandra.db.marshal.LongType,org.apache.cassandra.db.marshal.LongType)'<br /> and default_validation_class = 'UTF8Type'<br /> and key_validation_class = 'UTF8Type';</p> <p> public static String query(long component1Value, long component2Value) {<br /> SliceQuery&lt;String, Composite, String&gt; sq =<br /> HFactory.createSliceQuery(keyspace, StringSerializer.get(), new CompositeSerializer(), StringSerializer.get());</p> <p> sq.setColumnFamily(keyCF);<br /> sq.setKey("x");</p> <p> Composite startRange = new Composite();<br /> startRange.addComponent(component1Value, LongSerializer.get(), "LongType");<br /> startRange.addComponent(component2Value, LongSerializer.get(), "LongType", AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);</p> <p> sq.setRange(null, startRange, false, 100);</p> <p> QueryResult&lt;ColumnSlice&lt;Composite,String&gt;&gt; result = sq.execute();<br /> ColumnSlice&lt;Composite, String&gt; slice = result.get();</p> <p> return slice.getColumns().get(0).getValue();<br /> }<br /> </code></p> <p>Data:<br /> [default@TEST] get Key[x];<br /> =&gt; (column=0:0, value=1, timestamp=1325801757823000)<br /> =&gt; (column=0:1, value=2, timestamp=1325801771058000)<br /> =&gt; (column=1:1, value=3, timestamp=1325801793378000)</p> <p>Results:<br /> query(1, 0) = 1;<br /> query(1, 1) = 1; FAILED CONDITION (1&lt;=0)<br /> query(1, 0) = 1; </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1009 Thu, 05 Jan 2012 23:33:30 +0000 zznate 1009@http://www.datastax.com/support-forums/ <p>flipping the reversed=true flag on the column family definition means the comparators are working backwards. LESS_THAN_EQUAL would then have to get GREATER_THAN_EQUAL to get the results you want. </p> <p>So to sum, given the following SQL:<br /> SELECT value FROM Key WHERE component1 &lt;= $1 AND component2 &lt;= $1 LIMIT 1;</p> <p>You want to :<br /> - take out "reveresed=true" that is only going to confuse you (you can always use 'order' flag on the slicePredicate by changing the 'false' to 'true' when you call setRange)<br /> - then you only add one component to the range:<br /> startRange.addComponent(component1Value, LongSerializer.get(), "LongType", AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);</p> <p>- filter the results of the above query on the client side applying the LTE clause on the second component there</p> <p>Out of curiosity though, change the limit in the startRange from 1 back to the default 100 and see if you get the second column. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1006 Thu, 05 Jan 2012 23:05:57 +0000 Anonymous 1006@http://www.datastax.com/support-forums/ <p>Yes, that's intended. My theory was (and knowing little about this would work with the way things are implemented in Cassandra) the reverse ordering will give a descending ordered iterator by component1, component2. The outer component equality filter would then be applied on iteration, "throwing out" any columns that fail the LESS_THAN_EQUAL condition and stopping when the condition succeeds. I've assumed a lot about the way this works.</p> <p>Below is a SQL version of what I'd like to achieve:</p> <p>CREATE TABLE Key {component1 BIGINT, component2 BIGINT, value VARCHAR);<br /> CREATE INDEX IDX on Key (component1 DESC, component2 DESC);<br /> SELECT value FROM Key WHERE component1 &lt;= $1 AND component2 &lt;= $1 LIMIT 1; </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1003 Thu, 05 Jan 2012 22:05:24 +0000 zznate 1003@http://www.datastax.com/support-forums/ <p>You defined the comparators in reversed order it looks like:<br /> and comparator = 'CompositeType(LongType(reversed=true), LongType(reversed=true))' </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-1002 Thu, 05 Jan 2012 21:59:40 +0000 Anonymous 1002@http://www.datastax.com/support-forums/ <p>When I add the non-equality component only on the outer component, I don't get an exception but I also don't get the answer I was expecting:</p> <p>query(1,0) returns value =2 which not what I wanted. My understanding (or lack of) is that if the LESS_THAN_EQUAL condition were applied the way I thought it should, the query should have found (0, 0), not (0,1). (0,1) should fail the component2.LESS_THAN_EQUAL 0 condition. </p> <p>How is this intended to work? I'm probably misunderstanding the intended functionality.</p> <p>[default@TEST] get Key[x];<br /> =&gt; (column=1:1, value=3, timestamp=1325793190047000) (1&lt;=0) FALSE<br /> =&gt; (column=0:1, value=2, timestamp=1325793174138000) (1&lt;=0) FALSE<br /> =&gt; (column=0:0, value=1, timestamp=1325793158844000) (0&lt;=0) TRUE</p> <p>Code changed to:<br /> <pre><code>&lt;br /&gt; startRange.addComponent(component1Value, LongSerializer.get(), &quot;LongType&quot;);&lt;/p&gt; &lt;p&gt;startRange.addComponent(component2Value, LongSerializer.get(), &quot;LongType&quot;, AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);&lt;br /&gt;</code></pre> <p>Thanks. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-995 Thu, 05 Jan 2012 19:30:34 +0000 zznate 995@http://www.datastax.com/support-forums/ <p>You can only add a non-equality component operation on the outer most component. </p> <p>To solve your use case with comparissons of both components, you would have to get all the columns resolved by the first criteria, component1&lt;=arg1, then filter the rest on the client. </p> http://www.datastax.com/support-forums/topic/are-a-compositetype-column-queries-with-less_than_equal-component-equality-supported-in-hector#post-994 Thu, 05 Jan 2012 19:14:47 +0000 Anonymous 994@http://www.datastax.com/support-forums/ <p>I have a composite column with two components (component1, component2), and I'd like to query where component1&lt;=arg1 AND component2&lt;=arg2. I assumed this could be achieved with the code below but I get an exception from Hector: "InvalidRequestException(why:Too many bytes for comparator)"</p> <p>I'm new to Cassandra (and thus Hector) and I'm unsure if this type of Composite query is supported or if I'm even going about this the right way. I found tutorial code (<a href="https://github.com/zznate/cassandra-tutorial/blob/master/src/main/java/com/datastax/tutorial/StaticCompositeIndex.java" rel="nofollow">https://github.com/zznate/cassandra-tutorial/blob/master/src/main/java/com/datastax/tutorial/StaticCompositeIndex.java</a>) that has commented code setting ComponentEquality to GREATER_THAN_EQUAL, but when I uncomment and run it, I see the same exception. </p> <p><code></p> <p>public String query(long component1Value, long Component2Value) {<br /> SliceQuery&lt;String, Composite, String&gt; sq = HFactory.createSliceQuery(keyspace, StringSerializer.get(), new CompositeSerializer(), StringSerializer.get());</p> <p> sq.setColumnFamily(keyCF);<br /> sq.setKey("x");</p> <p> Composite startRange = new Composite();</p> <p> startRange.addComponent(component1Value, LongSerializer.get(), "LongType", AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);</p> <p> startRange.addComponent(component2Value, LongSerializer.get(), "LongType", AbstractComposite.ComponentEquality.LESS_THAN_EQUAL);</p> <p> sq.setRange(startRange, null, false, 1);</p> <p> QueryResult&lt;ColumnSlice&lt;Composite,String&gt;&gt; result = sq.execute();<br /> ColumnSlice&lt;Composite, String&gt; slice = result.get();</p> <p> return slice.getColumns().get(0).getValue();<br /> }</p> <p>// The column family definition</p> <p>create column family Key<br /> with column_type = 'Standard'<br /> and comparator = 'CompositeType(LongType(reversed=true), LongType(reversed=true))'<br /> and default_validation_class = 'UTF8Type'<br /> and key_validation_class = 'UTF8Type'<br /> and rows_cached = 0<br /> and row_cache_save_period = 0<br /> and row_cache_keys_to_save = 2147483647<br /> and keys_cached = 1.0<br /> and key_cache_save_period = 14400<br /> and read_repair_chance = 0.1<br /> and gc_grace = 864000<br /> and min_compaction_threshold = 4<br /> and max_compaction_threshold = 32<br /> and replicate_on_write = true<br /> and row_cache_provider = 'ConcurrentLinkedHashCacheProvider'<br /> and compaction_strategy = 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'</p> <p>set Key[x]['0:0']=1;<br /> set Key[x]['0:1']=2;<br /> set Key[x]['1:1']=3;<br /> </code></p> <p>With the above Java code, the desired result should be query(1, 0) returns the value "1" or the value corresponding to Key[x]['0:0'] because obviously that is the only column that satisfies component1 &lt;= 1 AND component2 &lt;= 0.</p> <p>Environment is: Cassandra 1.0.5, Hector 1.0.2 </p>