Scripts Tutorial Cassandra

cassandra.cql

-- Mostrar información sobre Cassandra
SHOW VERSION;

-- Mostrar información sobre el cluster
DESCRIBE CLUSTER;

-- Mostrar información sobre los keyspaces
DESCRIBE KEYSPACES;

-- Crear un keyspace llamado music con una replicación de 1 nodo
CREATE KEYSPACE IF NOT EXISTS music
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 1
};

-- Mostrar información sobre el keyspace music
DESCRIBE KEYSPACE music;

-- Modificar el keyspace music para tener una replicación de 3 nodos
ALTER KEYSPACE music
WITH replication = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
};

-- Poner en uso el keyspace music
USE music;

-- USUARIOS --

-- Crear una tabla llamada users con las columnas id, name y email
CREATE TABLE IF NOT EXISTS music.users (
    id UUID PRIMARY KEY,
    name TEXT,
    email TEXT
);

-- Mostrar información sobre la tabla users
DESCRIBE TABLE music.users;

-- Insertar un usuario en la tabla users
INSERT INTO music.users (id, name, email) VALUES  (123e4567-e89b-12d3-a456-426614174000, 'John', 'john@acme.com');

-- Mostrar información sobre la tabla users
SELECT * FROM music.users;

-- Actualizar el usuario con id 123e4567-e89b-12d3-a456-426614174000
UPDATE music.users
SET name = 'John Doe', email = 'johndoe@acme.com'
WHERE id = 123e4567-e89b-12d3-a456-426614174000;

-- Mostrar información sobre la tabla users
SELECT * FROM music.users;

-- Actualizar el usuario con id 123e4567-e89b-12d3-a456-426614174000 con INSERT
INSERT INTO music.users (id, name, email) VALUES  (123e4567-e89b-12d3-a456-426614174000, 'Johnnie Doe', 'johnniedoe@acme.com');

-- Mostrar información sobre la tabla users
SELECT * FROM music.users;

-- Eliminar el usuario con id 123e4567-e89b-12d3-a456-426614174000
DELETE FROM music.users
WHERE id = 123e4567-e89b-12d3-a456-426614174000;

-- Mostrar información sobre la tabla users
SELECT * FROM music.users;

-- Volver a insertar al usuario en la tabla users
INSERT INTO music.users (id, name, email) VALUES  (123e4567-e89b-12d3-a456-426614174000, 'John Doe', 'johndoe@acme.com');

-- Insertar otro usuario en la tabla users
INSERT INTO music.users (id, name, email) VALUES  (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 'janesmith@acme.com'); 

-- Mostrar información sobre la tabla users
SELECT * FROM music.users;

-- CANCIONES ESCUCHADAS POR USUARIO --

-- Crear una tabla music.last_played_songs_by_user con las columnas user_id, song_id y played_at
-- La columna user_id es la clave de partición y la columna played_at es la clave de clustering
-- La columna user_name es una columna estática
CREATE TABLE IF NOT EXISTS music.last_played_songs_by_user (
    user_id UUID,
    user_name TEXT STATIC,
    song_id UUID,
    song_name TEXT,
    artist_name TEXT,
    cover_url TEXT,
    played_at TIMESTAMP,
    PRIMARY KEY ((user_id), played_at)
);

-- Mostrar información sobre la tabla music.last_played_songs_by_user
DESCRIBE TABLE music.last_played_songs_by_user;


-- Insertar canciones escuchadas por dos usuarios
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614174000, 'John Doe', 123e4567-e89b-12d3-a456-426614174001, 'This is my song', 'The good singers', 'https://covers.com/this-is-my-song', toTimestamp(now()));
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614174000, 'John Doe', 123e4567-e89b-12d3-a456-426614174002, 'Holiday song', 'The Summers', 'https://covers.com/holiday-song', toTimestamp(now()) + 3m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614174000, 'John Doe', 123e4567-e89b-12d3-a456-426614174002, 'Holiday song', 'The Summers', 'https://covers.com/holiday-song', toTimestamp(now()) + 4m);

INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174001, 'This is my song', 'The good singers', 'https://covers.com/this-is-my-song', toTimestamp(now()) + 5m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174002, 'Holiday song',  'The Summers', 'https://covers.com/holiday-song', toTimestamp(now()) + 10m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174003, 'Cat meaow', 'The Cats', 'https://covers.com/cat-meaow', toTimestamp(now()) + 15m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174003, 'Cat meaow', 'The Cats', 'https://covers.com/cat-meaow', toTimestamp(now()) + 16m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174004, 'Dog bark', 'The Dogs', 'https://covers.com/dog-bark', toTimestamp(now()) + 20m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174004, 'Dog bark', 'The Dogs', 'https://covers.com/dog-bark', toTimestamp(now()) + 21m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174004, 'Dog bark',  'The Dogs', 'https://covers.com/dog-bark', toTimestamp(now()) + 22m);
INSERT INTO music.last_played_songs_by_user (user_id, user_name, song_id, song_name, artist_name, cover_url, played_at) VALUES (123e4567-e89b-12d3-a456-426614178000, 'Jane Smith', 123e4567-e89b-12d3-a456-426614174005, 'Bird tweet', 'The Birds', 'https://covers.com/bird-tweet', toTimestamp(now()) + 25m);

-- Mostrar información sobre la tabla music.last_played_songs_by_user
SELECT * FROM music.last_played_songs_by_user;

-- Consultar las filas correspondiente a la canción con id 123e4567-e89b-12d3-a456-426614174003
SELECT * FROM music.last_played_songs_by_user WHERE song_id = 123e4567-e89b-12d3-a456-426614174003;

-- Añadir la opción ALLOW FILTERING a la consulta anterior para poder filtrar por una columna no clave y no indexada
SELECT * FROM music.last_played_songs_by_user WHERE song_id = 123e4567-e89b-12d3-a456-426614174003 ALLOW FILTERING;

-- Crear un índice en la columna song_id de la tabla music.last_played_songs_by_user
CREATE INDEX IF NOT EXISTS song_id_index
ON music.last_played_songs_by_user (song_id);

-- Mostrar información sobre la tabla music.last_played_songs_by_user
DESCRIBE TABLE music.last_played_songs_by_user;

-- Activar el trazado de consultas para ver si se utiliza el índice
TRACING ON;

-- Volver a ejecutar la consulta anterior para saber si se ha utilizado el índice en la consulta
SELECT * FROM music.last_played_songs_by_user WHERE song_id = 123e4567-e89b-12d3-a456-426614174003;

-- En la salida de la traza se puede ver que se ha utilizado el índice
-- Executing read on music.last_played_songs_by_user using index song_id_index

-- Desactivar la traza
TRACING OFF;

-- Eliminar el índice song_id_index
DROP INDEX IF EXISTS song_id_index;

-- Eliminar la tabla music.last_played_songs_by_user
DROP TABLE IF EXISTS music.last_played_songs_by_user;

-- Eliminar la tabla music.users
DROP TABLE IF EXISTS music.users;

-- Eliminar el keyspace music
DROP KEYSPACE IF EXISTS music;

-- Mostrar información sobre el keyspace music
DESCRIBE KEYSPACE music;