- Learning Apache Cassandra(Second Edition)
- Sandeep Yarabarla
- 339字
- 2021-07-03 00:19:36
Interacting only with the static columns
We've seen that static columns give us the ability to associate data with all rows that share the same partition key. However, what if we want to treat the data associated with that partition key as a discrete value rather than some extra information attached to each clustering column value?
More concretely, if we want to display an interface for alice to edit her user profile, all we really need to do is get the information associated with her partition key. We don't care if there are one, 10, or 1,000 status updates; we just need a single row with her username, e-mail address, and so on. With users as a separate table, this is trivial; we just select the single row with the partition key value alice. Let's see what happens when we do the equivalent in users_with_status_updates:
SELECT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = 'alice';
Note that we've omitted the id and body fields, which contain distinct values in each row; we only select the partition key, username, and the static columns, email and encrypted_password. Unfortunately, we don't quite get what we're looking for:
The result is two rows; the rows contain identical data because all of the selected columns are per-partition-key, but we still get duplicate results when we only want one. Fortunately, the DISTINCT keyword allows us to retrieve a result in the format that we'd like:
SELECT DISTINCT "username", "email", "encrypted_password"
FROM "users_with_status_updates"
WHERE "username" = 'alice';
Now, the result takes the form we'd like—a single row containing the information specific to the alice partition key:
By formulating our SELECT statement correctly, we can basically ignore the fact that there's a clustering column at all; the result here is exactly the same as it would have been if we had selected alice's row out of the users table. To complete this analogy, we'd also like to be able to write static columns to our table without thinking about the non-static columns.