Example of a music service
The social music service example uses a UUID as a primary key.
CREATE TABLE songs ( id uuid PRIMARY KEY, title text, album text, artist text, data blob );
CREATE TABLE playlists ( id uuid, song_order int, song_id uuid, title text, album text, artist text, PRIMARY KEY (id, song_order ) );
The combination of the id and song_order in the playlists table uniquely identifies a row in the playlists table. You can have more than one row with the same id as long as the rows contain different song_order values.
Here's an example of inserting a single record into the playlist: inserting the example data into playlists
INSERT INTO playlists (id, song_order, song_id, title, artist, album) VALUES (62c36092-82a1-3a00-93d1-46196ee77204, 4, 7db1a490-5878-11e2-bcfd-0800200c9a66, 'Ojo Rojo', 'Fu Manchu', 'No One Rides for Free');
After inserting the remaining records, use the following SELECT query to display the table's data:
SELECT * FROM playlists;
The example below illustrates how to create a query that uses the artist as a filter:
SELECT album, title FROM playlists WHERE artist = 'Fu Manchu';
Cassandra will reject this query because the query requires a sequential scan across the entire playlists dataset, because artist is not a partition key or clustering column. By creating an index on artist, Cassandra can now pull out the records.
CREATE INDEX ON playlists( artist );
Now, you can query the playlists for songs by Fu Manchu. The output looks like this: