2

We have cassandra column family. each row have multiple columns. columns have name, but value is empty. if we have 5-10 row keys, how we can find column names that appear in all of these keys. e.g.

row1: php, programming, accounting
row2: php, bookkeeping, accounting
row3: php, accounting

must return:

result: php, accounting

note we can not easily load whole row into the memory, because it may contain 1M+ columns solution not need to be fast.

Nick
  • 9,962
  • 4
  • 42
  • 80

3 Answers3

1

In order to do intersection of several rows, we will need to intersect two of them first, then to intersect the result with third and so on.

Looks like in cassandra we can query the data by column names and this is relatively fast operation.

So we first get Column Slice of 10k rows. Making list of column names (in PHP Cassa - put them in array). Then select those from second row.

Code may be looking like this:

$x = $cf->get($first_key, <some column slice>);

$column_names = array();
foreach(array_keys($x) as $k)
   $column_names[] = $k;

$result = $cf->get($second_key, $column_slice = null, $column_names);

// write result somewhere, and proceed with next slice
Nick
  • 9,962
  • 4
  • 42
  • 80
  • 1
    This is true but in one good rule of thumb in nosql is design you data based on your read & write pattern. Better try creating a manual index using super columns which will have your tag (say php accounting) as first level column and row keys which has those tags as second level. But this might not give a soln for your current question though :P I'm jus saying – Tamil Aug 03 '12 at 07:12
  • your idea with super columns is brilliant. only drawback i see in my case is, after inserting, I will need to do second pass, so I can count each super column. – Nick Aug 03 '12 at 22:08
  • However, instead of super columns I can use counters on single row - each counter for each tag. Then I increase them, and finally I do one more pass, in order to find what counters have number of intersected rows. – Nick Aug 03 '12 at 22:16
0

You columns names are sorted and you can create an iterator for each row (this iterator load portion of date at once, for example 10k of columns). Now put each iterator into a priority queue (by the next column name). If you take for queue the k times the iterator with the same column names, this is common names between all rows, in the other case we move to the next element and return iterators to queue.

Alexander Kuznetsov
  • 3,062
  • 2
  • 25
  • 29
0

You could use a Hadoop map/reduce job as follows:

  • Map output key = column name

  • Map output value = row key

  • Reducer counts row keys for each column and outputs column name & count to a CF with the following schema:

    key : [column name] { Count : [count] }

  • You can then query counts from this CF in reverse order. The first record will be the max, so you can keep iterating until a value is < max. This will be your intersection.

rs_atl
  • 8,935
  • 1
  • 23
  • 28
  • Interesting approach, but I do not know Hadoop yet. Thanks anyway. – Nick Aug 02 '12 at 20:51
  • @Nick Other than boilerplate, this is probably 20 lines of code in a M/R job, and if you're planning to use Cassandra then Hadoop is more than worth your effort. You could also do this quite easily in Pig or Hive, obviating the need to write the M/R by hand. – rs_atl Aug 02 '12 at 21:47