Composite key in Cassandra with Pig and where_clause for part of the key in the where clause

Question

I basically have the same problem as the following Composite key in Cassandra with Pig. The only difference is I try to query for a part of the composite key within the where_clause of pig.

The data structure is similar to the earlier mentioned issue, I'll copy some code/context to minimize the reading of that issue.

We have a CQL table that looks something like this:

CREATE table data (
    occurday  text,
    seqnumber int,
    occurtimems bigint,
    unique bigint,
    fields map<text, text>,
    primary key ((occurday, seqnumber), occurtimems, unique)
)

Instead of querying for both the seqnumber and the occurday (as was the issue in previously mentioned issue) I try to query one of the keys.

If I execute this query as part of a LOAD from within Pig, however, things don't work.

-- Need to URL encode the query
data = LOAD 'cql://ks/data?where_clause=occurday%3D%272013-10-01%27' USING CqlStorage();

gives

java.lang.RuntimeException
    at org.apache.cassandra.hadoop.cql3.CqlPagingRecordReader$RowIterator.executeQuery(CqlPagingRecordReader.java:665)
    at org.apache.cassandra.hadoop.cql3.CqlPagingRecordReader$RowIterator.<init>(CqlPagingRecordReader.java:301)
    at org.apache.cassandra.hadoop.cql3.CqlPagingRecordReader.initialize(CqlPagingRecordReader.java:167)
    at org.apache.pig.backend.hadoop.executionengine.mapReduceLayer.PigRecordReader.initialize(PigRecordReader.java:181)
    at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.initialize(MapTask.java:522)
    at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:763)
    at org.apache.hadoop.mapred.MapTask.run(MapTask.java:370)
    at org.apache.hadoop.mapred.LocalJobRunner$Job.run(LocalJobRunner.java:212)
Caused by: InvalidRequestException(why:occurday cannot be restricted by more than one relation if it includes an Equal)
    at org.apache.cassandra.thrift.Cassandra$prepare_cql3_query_result$prepare_cql3_query_resultStandardScheme.read(Cassandra.java:51017)
    at org.apache.cassandra.thrift.Cassandra$prepare_cql3_query_result$prepare_cql3_query_resultStandardScheme.read(Cassandra.java:50994)
    at org.apache.cassandra.thrift.Cassandra$prepare_cql3_query_result.read(Cassandra.java:50933)
    at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:78)
    at org.apache.cassandra.thrift.Cassandra$Client.recv_prepare_cql3_query(Cassandra.java:1756)
    at org.apache.cassandra.thrift.Cassandra$Client.prepare_cql3_query(Cassandra.java:1742)
    at org.apache.cassandra.hadoop.cql3.CqlPagingRecordReader$RowIterator.prepareQuery(CqlPagingRecordReader.java:605)
    at org.apache.cassandra.hadoop.cql3.CqlPagingRecordReader$RowIterator.executeQuery(CqlPagingRecordReader.java:635)
    ... 7 more

Basically my question is, what am I doing wrong or what don't I understand?

As I understand from CqlPagingRecorderReader Used when Partition Key Is Explicitly Stated I should be able to query with just part of the partition key?

Also while reading Add CqlRecordReader to take advantage of native CQL pagination I get the impression this should be possible, but I am swimming around with (in my opinion) no clear direction on how to accomplish this.

Any help is very very welcome at this point.

Regards,

Lennart Weijl

PS.

I am running on Cassandra 2.0.9 with Pig 0.13.0

nate nate · Accepted Answer · 2014-07-30T18:32:36

According to CASSANDRA-6311, I believe you need to apply the 6331-v2-2.0-branch.txt patch, recompile pig, and then update your LOAD statement to:

data = LOAD 'cql://ks/data?where_clause=occurday%3D%272013-10-01%27' USING CqlInputFormat();

The key change being USING CqlInputFormat() which triggers the use of the new CqlRecordReader that was released in Cassandra 2.0.7.

Edit: Note that the exception is thrown from CqlPagingRecordReader which means you're still using the old record reader.

Composite key in Cassandra with Pig and where_clause for part of the key in the where clause

1 Answers