Let's say we have a
users table. Then after a while, a new use case emerges, as we notice we often need to lookup users by their birthday. We then create a new table to fit that query:
CREATE TABLE users_by_birthday ( birthday DATE, userid UUID, name TEXT, PRIMARY KEY ((birthday))
The content of this new
users_by_birthday table could be derived from:
SELECT birthday, userid, name FROM users;
It would be convenient to do an INSERT SELECT ala old-fashion-SQL:
INSERT INTO users_by_birthday (birthday, userid, name) FROM SELECT birthday, userid, name FROM users;
Granted this SELECT is an anti-pattern because of the full table scan. Doubled by a massive flood of INSERTs let's say of 1 million rows. However, we are willing to take the penalty for this one-time sin.
How to achieve this kind of INSERT ... SELECT the Cassandra way?