DataStax Support Forums » Topic: Writing loads of columns using CQL vs Hector

DataStax Support Forums » Topic: Writing loads of columns using CQL vs Hector http://www.datastax.com/support-forums/topic/writing-loads-of-columns-using-cql-vs-hector Software, Support, and Training for Apache Cassandra en-US Thu, 20 Jun 2013 09:37:41 +0000 http://bbpress.org/?v=1.0.3 <![CDATA[Search]]> q http://www.datastax.com/support-forums/search.php Marius Waldal on "Writing loads of columns using CQL vs Hector" http://www.datastax.com/support-forums/topic/writing-loads-of-columns-using-cql-vs-hector#post-1161 Thu, 16 Feb 2012 13:15:46 +0000 Marius Waldal 1161@http://www.datastax.com/support-forums/ Thank you so much for anwering, Nate. To be more specific about what I'm trying to do: The use case is a location CF with predefined columns for county, municipality etc, and an areaid. There are about 5.300 rows in this cf. In addition to using this as a cf for lookup, it is also used to verify if areaids from a log import job are valid when importing log lines. To do this, we have so far read all ids by getting all rows and returning the id column (done once, putting the IDs in a List). However, I thought it would be more efficient (and more cassandra-ish) to include 1 additional row in the location cf with all the IDS, so that when importing we would instead just read the valid IDs from columns in this row. And this is where the CQL dilemma shows up, writing 5300+ columns in that gnarly CQL statement :-) But we also thought about another option: concatenating all the IDs into a loong delimited String and then writing that string to a single column in the new lookup row, and then splitting it again when doing the import job. This approach has a hacky feel to it, but whatever is more efficient is good. It should also ensure less overhead than using thousands of columns, as the location cf is also rebuilt periodically, and this may involve areaids being removed. An update query against the row with thousands of columns will not remove the IDs that are obsolete, so we would have to first delete the row and then write it with the new columns. Having all IDs concatenated in one column means the update only needs to update this single column. However, this could perhaps also result in the query being too large for a single thrift msg? That was a lot of rambling. What do you think? 1 column? Or thousands of columns with row deletion and new insert, in batches of i.e. 500 columns? The columns will be String columns with a maximum of 5 digits each: 20012,20063 etc Marius zznate on "Writing loads of columns using CQL vs Hector" http://www.datastax.com/support-forums/topic/writing-loads-of-columns-using-cql-vs-hector#post-1150 Wed, 15 Feb 2012 20:47:05 +0000 zznate 1150@http://www.datastax.com/support-forums/ Yeah, that insert would end up being some gnarly CQL. Using Hector's Mutator class would be simple and most likely more efficient. If this is an insert your workload will be doing frequently, take some time to play with the batch size. The bigger this is, the more efficient, but making this too big will cause transport errors and burn memory. To avoid continuous array resizing overhead, Thrift will keep expanding buffer sizes of the underlying connection until the max is reached (16MB by default). Start with about 500 columns and go up or down as needed. Marius Waldal on "Writing loads of columns using CQL vs Hector" http://www.datastax.com/support-forums/topic/writing-loads-of-columns-using-cql-vs-hector#post-1143 Wed, 15 Feb 2012 09:43:57 +0000 Marius Waldal 1143@http://www.datastax.com/support-forums/ We are trying to use CQL for all Cassandra operations, but in cases that are typical cassandra-ish and not relational-ish (like having a single row with thousands of columns), this seems like bad practice. As a relational db would never have a high number of columns, inserting multiple columns listing first the column names and then column values or updating columns using name=value pairs is practical. But creating a CQL statement with thousands of name=value pairs does not ring well in my ears. Of course, one could do batch inserts instead, but this still seems like trying to force relational db-pants on nosql-legs... Is it in any way better to use e.g. Hector mutators with addInsertion for each column? Will it be more efficient? Marius