Database Reference
In-Depth Information
Updating a counter column
The
daily_status_update_views
table introduces a new type of column: the
counter column. Counter columns store integer values, just like
int
and
bigint
columns; however, unlike a normal data column, counter columns are always incremented
or decremented, rather than having a value set directly.
We've currently got two tables to store usage data:
status_update_views
to store raw
view observations, and
daily_status_update_views
to store views by day. We'd
like to record that one of
alice
's status updates was viewed on the web on October 5,
2014 at 3:12 P.M. EDT:
INSERT INTO "status_update_views" (
"status_update_username", "status_update_id",
"observed_at", "client_type"
) VALUES (
'alice', 76e7a4d0-e796-11e3-90ce-5f98e903bf02,
85a53d10-4cc3-11e4-a7ff-5f98e903bf02,
'web'
);
UPDATE "daily_status_update_views"
SET "total_views" = "total_views" + 1,
"web_views" = "web_views" + 1
WHERE "year" = 2014
AND "date" = '2014-10-05';
The first
INSERT
statement should look familiar: we're adding a new row whose partition
key columns contain the full primary key of the status update that was viewed, and whose
clustering column is a timestamp UUID encoding the exact time at which the status update
was viewed. We also record what kind of client was used to view the status update; in this
case, a web browser.
The second statement,
UPDATE
, introduces the manipulation of counter columns. Syn-
tactically, we've seen this before; specifically, when adding an item to a list or set column,
we used the same syntax of
column_name = column_name + incremental_value
. In this
case, however, the incremental value is an integer, rather than one or more items we want
to add.
