UPDATE with Cassandra CQL

Question

I'm new to Cassandra and I'm trying to update a table using this CQL query:

UPDATE analytics SET counter = ? WHERE hash = ?;

I work in PHP using the DataStax php-driver, the code for this statement is this:

$statement = $session->prepare("UPDATE analytics SET counter = ? WHERE hash = ?;");
$session->execute($statement, new Cassandra\ExecutionOptions(array('arguments' => array($count, $hash))));

The table:

CREATE TABLE IF NOT EXISTS analytics (domain VARCHAR, page VARCHAR, ip INET, attributes MAP <VARCHAR, VARCHAR>, hash VARCHAR, counter INT, date TIMESTAMP, PRIMARY KEY(hash, domain, date));

When I execute the statement I get the error "Missing mandatory PRIMARY KEY part domain" so I added the clause ALLOW FILTERING:

UPDATE analytics SET counter = ? WHERE hash = ? ALLOW FILTERING;

But I get another error, "line 1:48 missing EOF at 'ALLOW'". What's is wrong in these queries?

Answer 1

Based on the error you have, I guess you created table with compound primary key (hash, domain). In order to update record in cassandra you have to provide the whole key, i.e. both values, so your query should look like:

UPDATE analytics SET counter = counter+1 WHERE hash = ? and domain = ?

For more information check this http://www.datastax.com/dev/blog/a-deep-look-to-the-cql-where-clause

It says: In UPDATE and DELETE statements all the primary key columns must be restricted...

Answer 2

You have to pass all primary key when you want to update column . So put all primary keys in where clause .

Answer 3

Are you really looking to update all counter values for a hash irrespective of date?

Anyhow, be careful with indexes. When you use an index, be sure to include the partition key so that your query hits exactly one partition. Otherwise, you have a cluster wide operation that'll likely time out in production.

If your data model doesn't support a query, but you need it for something analytical, a good option is to use Apache Spark, and use that for batch type jobs.