Example of a music service

The social music service example uses a UUID as a primary key.

This example of a social music service requires a songs table having a title, album, and artist column, plus a column (called data) for the actual audio file itself. The table uses a UUID as a primary key.

CREATE TABLE songs (
  id uuid PRIMARY KEY,
  title text,
  album text,
  artist text,
  data blob
 );

In a relational database, you would create a playlists table with a foreign key to the songs, but in Apache Cassandra™, you denormalize the data because joins are not performant in a distributed system. Later, this document covers how to use a collection to accomplish the same goal as joining the tables to tag songs. To represent the playlist data, you can create a table like this:

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.

Note: The UUID is handy for sequencing the data or automatically incrementing synchronization across multiple machines. For simplicity, an int song_order is used in this example.

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: