Cassandra materialized view

gistfile1.txt

cqlsh:ks1> CREATE TABLE events (
       ...     persistence_id text,
       ...     partition_nr bigint,
       ...     sequence_nr bigint,
       ...     timestamp timeuuid,
       ...     message text,
       ...     tag text,
       ...     used boolean static,
       ...     PRIMARY KEY ((persistence_id, partition_nr), sequence_nr, timestamp)
       ... ) WITH CLUSTERING ORDER BY (sequence_nr ASC);
cqlsh:ks1>
cqlsh:ks1> CREATE MATERIALIZED VIEW events_by_tag AS
       ...     SELECT tag, timestamp, persistence_id, partition_nr, sequence_nr, message
       ...     FROM events
       ...     WHERE persistence_id IS NOT NULL AND partition_nr IS NOT NULL AND sequence_nr IS NOT NULL AND tag IS NOT NULL AND timestamp IS NOT NULL
       ...     PRIMARY KEY (tag, timestamp, persistence_id, partition_nr, sequence_nr)
       ...     WITH CLUSTERING ORDER BY (timestamp ASC);
cqlsh:ks1>
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p1', 1, 1, now(), 'e1-1', 't1');
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p1', 1, 2, now(), 'e1-2', 't2');
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p1', 1, 3, now(), 'e1-3', 't1');
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p2', 1, 1, now(), 'e2-1', 't1');
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p2', 1, 2, now(), 'e2-2', 't2');
cqlsh:ks1> insert into events (persistence_id, partition_nr, sequence_nr, timestamp, message, tag) VALUES ('p1', 1, 4, now(), 'e1-4', 't1');
cqlsh:ks1>
cqlsh:ks1> select * from events_by_tag;

 tag | timestamp                            | persistence_id | partition_nr | sequence_nr | message
-----+--------------------------------------+----------------+--------------+-------------+---------
  t1 | 797e1e30-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           1 |    e1-1
  t1 | 7ef8b4b0-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           3 |    e1-3
  t1 | 8160db10-8c32-11e5-9466-5b0cd9c03871 |             p2 |            1 |           1 |    e2-1
  t1 | 85fe7f10-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           4 |    e1-4
  t2 | 7c160720-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           2 |    e1-2
  t2 | 83c0eb20-8c32-11e5-9466-5b0cd9c03871 |             p2 |            1 |           2 |    e2-2

(6 rows)
cqlsh:ks1>
cqlsh:ks1> select * from events_by_tag where tag='t1' order by timestamp asc;

 tag | timestamp                            | persistence_id | partition_nr | sequence_nr | message
-----+--------------------------------------+----------------+--------------+-------------+---------
  t1 | 797e1e30-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           1 |    e1-1
  t1 | 7ef8b4b0-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           3 |    e1-3
  t1 | 8160db10-8c32-11e5-9466-5b0cd9c03871 |             p2 |            1 |           1 |    e2-1
  t1 | 85fe7f10-8c32-11e5-9466-5b0cd9c03871 |             p1 |            1 |           4 |    e1-4

(4 rows)
cqlsh:ks1> select persistence_id, sequence_nr, timestamp, dateOf(timestamp), message, tag
       ... from events_by_tag
       ... where tag='t1'
       ... order by timestamp asc limit 3;

 persistence_id | sequence_nr | timestamp                            | system.dateof(timestamp) | message | tag
----------------+-------------+--------------------------------------+--------------------------+---------+-----
             p1 |           1 | 797e1e30-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:11+0000 |    e1-1 |  t1
             p1 |           3 | 7ef8b4b0-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:21+0000 |    e1-3 |  t1
             p2 |           1 | 8160db10-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:25+0000 |    e2-1 |  t1

(3 rows)
cqlsh:ks1> select persistence_id, sequence_nr, timestamp, dateOf(timestamp), message, tag
       ... from events_by_tag
       ... where tag='t1' and timestamp > 8160db10-8c32-11e5-9466-5b0cd9c03871
       ... order by timestamp asc limit 3;

 persistence_id | sequence_nr | timestamp                            | system.dateof(timestamp) | message | tag
----------------+-------------+--------------------------------------+--------------------------+---------+-----
             p1 |           4 | 85fe7f10-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:32+0000 |    e1-4 |  t1

(1 rows)
cqlsh:ks1> select persistence_id, sequence_nr, timestamp, dateOf(timestamp), message, tag
       ... from events_by_tag
       ... where tag='t1' and timestamp > minTimeuuid('2015-11-16 07:20:25+0000')
       ... order by timestamp asc limit 3;

 persistence_id | sequence_nr | timestamp                            | system.dateof(timestamp) | message | tag
----------------+-------------+--------------------------------------+--------------------------+---------+-----
             p2 |           1 | 8160db10-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:25+0000 |    e2-1 |  t1
             p1 |           4 | 85fe7f10-8c32-11e5-9466-5b0cd9c03871 | 2015-11-16 07:20:32+0000 |    e1-4 |  t1

(2 rows)
cqlsh:ks1>