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?