Introducción a Cassandra. Ejemplos de consulta CQL

El objetivo de este articulo es gestionar información acerca los cursos y los estudiantes del máster en filología hispánica en una base de datos con Cassandra:

Cursos

IdCTítuloCréditosProfesor
101Filología románica7Sara Martínez
102Griego moderno7Daniel Pérez
103Literatura moderna10Amalia Sierra
104Literatura hispano-americana5Paloma Sánchez
105Fonética y morfología5Miguel Hernández
106Teorías literarias3Juan García
107Lingüística general3Samuel López
108Español moderno10Isabel Sanz

Estudiante

IdENombre estudiante
201Maria Mestre
202Rodrigo Calvo
203Oriol Menezes
204Carlo Berruzo
205Sofia Canyadell
206Marina Perez
207Arianna Ruiz
208Naiara Zapico

Notas

IdEIdCNota ExamFecha Examen
201101B2019-02-11
201102B2018-06-30
202106C2018-06-30
203103A2019-02-11
203108A2018-06-30
203105B2020-01-04
204101B2020-01-04
205103C2020-01-04

1. Recuperar la información de los cursos (título del curso, créditos, nombre profesor, nombre estudiante) que han sido atendidos por un estudiante concreto y ordenados por el título del curso.

IdEIdCTítuloCréditosProfesorNombre estudiante
201 101 Filología románica 7 Sara Martínez Maria Mestre
201 102 Griego moderno 7 Daniel Pérez Maria Mestre
202 106 Teorías literarias 3 Juan García Rodrigo Calvo
203 103 Literatura moderna 10 Amalia Sierra Oriol Menezes
203 105 Fonética y morfología 5 Miguel Hernández Oriol Menezes
203 108 Español moderno 10 Isabel Sanz Oriol Menezes
204 101 Filología románica 7 Sara Martínez Carlo Berruzo
205 103 Literatura hispano-americana 5 Paloma Sánchez Sofia Canyadell
205 104 Literatura hispano-americana 5 Paloma Sánchez Sofia Canyadell
205 108 Español moderno 10 Isabel Sanz Sofia Canyadell
206 106 Teorías literarias 3 Juan García Marina Perez
206 107 Lingüística general 3 Samuel López Marina Perez
207 107 Lingüística general 3 Samuel López Arianna Ruiz
208 104 Literatura hispano-americana 5 Paloma Sánchez Naiara Zapico

Como se puede comprobar, para evitar problemas con nombres duplicados, he añadido a la consulta los campos IdE y IdC no solicitados en el enunciado. Con estos campos ya puedo diferenciar correctamente dos alumnos con el mismo nombre o dos cursos con el mismo título.

Debido a las peticiones de los siguientes apartados, he decidido utilizar primary key idE_estudiante, titulo_curso y idC_curso. El clave principal, idE_estudiante me permitirá poder agrupar los datos por este valor y, así, conocer el curso con más créditos dónde se ha matriculado.

La siguiente clave utilizada ha sido titulo_curso, de esta forma cumplo el requerimiento de ordenación por este valor indicado en el enunciado.

 cqlsh
 sudo systemctl start cassandra

 CREATE KEYSPACE practica WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : '1'};
 describe keyspaces

 use practica;

 describe tables;

 CREATE TABLE ejercicio_a (
  titulo_curso text,
  idC_curso int,
  creditos_curso int,
  profesor_curso text,
  nombre_estudiante text,
  idE_estudiante int,
  PRIMARY KEY ( idE_estudiante ,  titulo_curso,idC_curso)
 ) WITH CLUSTERING ORDER BY(titulo_curso asc, idC_curso asc );


 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (201,101,'Filología románica',7,'Sara Martínez','Maria Mestre')	;
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (201,102,'Griego moderno',7,'Daniel Pérez','Maria Mestre');
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (202,106,'Teorías literarias',3,'Juan García','Rodrigo Calvo');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (203,103,'Literatura moderna',10,'Amalia Sierra','Oriol Menezes');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (203,105,'Fonética y morfología',5,'Miguel Hernández','Oriol Menezes');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (203,108,'Español moderno',10,'Isabel Sanz','Oriol Menezes');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (204,101,'Filología románica',7,'Sara Martínez','Carlo Berruzo');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (205,103,'Literatura moderna',5,'Paloma Sánchez','Sofia Canyadell');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (205,104,'Literatura hispano-americana',5,'Paloma Sánchez','Sofia Canyadell');
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (205,108,'Español moderno',10,'Isabel Sanz','Sofia Canyadell');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (206,106,'Teorías literarias',3,'Juan García','Marina Perez');
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (206,107,'Lingüística general',3,'Samuel López','Marina Perez');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (207,107,'Lingüística general',3,'Samuel López','Arianna Ruiz');	
 insert into ejercicio_a (idE_estudiante, idC_curso, titulo_curso,  creditos_curso, profesor_curso, nombre_estudiante ) values (208,104,'Literatura hispano-americana',5,'Paloma Sánchez','Naiara Zapico');	

1 Resultado del bloque ejercicio_1

2. Recuperar la información de los cursos en los que hubo estudiantes que recibieron una nota concreta. Se piden título del curso, créditos, nombre profesor, identificador del estudiantes y nota, ordenados por el número de créditos del curso.

CréditosNota ExamIdETítuloProfesor
7 B 201 Filología románica Sara Martínez
7 B 201 Griego moderno Daniel Pérez
3 C 202 Teorías literarias Juan García
10 A 203 Literatura moderna Amalia Sierra
5 B 203 Fonética y morfología Miguel Hernández
10 A 203 Español moderno Isabel Sanz
7 B 204 Filología románica Sara Martínez
10 C 205 Literatura moderna Paloma Sánchez
5 B 205 Literatura hispano-americana Paloma Sánchez
10 A 205 Español moderno Isabel Sanz
3 B 206 Teorías literarias Juan García
3 B 206 Lingüística general Samuel López
3 A 207 Lingüística general Samuel López
5 C 208 Literatura hispano-americana Paloma Sánchez

 CREATE TABLE ejercicio_b (
  titulo_curso text,
  creditos_curso int,
  profesor_curso text,
  idE_estudiante int,
  nota_exam text,
  PRIMARY KEY  ( nota_exam, creditos_curso, idE_estudiante  , titulo_curso   ),
  ) WITH CLUSTERING ORDER BY(creditos_curso desc );


 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 7,'B',201,'Filología románica','Sara Martínez');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 7,'B',201,'Griego moderno',	'Daniel Pérez');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 3,'C',202,'Teorías literarias',	'Juan García');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 10,'A',203,'Literatura moderna',	'Amalia Sierra');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 5,'B',203,'Fonética y morfología',	'Miguel Hernández');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 10,'A',203,'Español moderno',	'Isabel Sanz');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 7,'B',204,'Filología románica',	'Sara Martínez');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 10,'C',205,'Literatura moderna',	'Paloma Sánchez');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 5,'B',205,'Literatura hispano-americana',	'Paloma Sánchez');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 10,'A',205,'Español moderno',	'Isabel Sanz');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 3,'B',206,'Teorías literarias',	'Juan García');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 3,'B',206,'Lingüística general',	'Samuel López');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 3,'A',207,'Lingüística general',	'Samuel López');
 insert into ejercicio_b (creditos_curso, nota_exam, idE_estudiante, titulo_curso,   profesor_curso ) values ( 5,'C',208,'Literatura hispano-americana',	'Paloma Sánchez');

Respecto a este punto, repetimos el proceso anterior. Para evitar problemas de repetición de datos y poder incluir todos los datos, es necesario tener como clave primaria de la colección idE_estudiante y titulo_curso. En este caso, no considero importante distinguir los cursos al nivel de identificador ya que lo realmente importante es el titulo del curso y sus créditos.

El orden de las claves será: nota_exam, creditos_curso, idE_estudiante y titulo_curso. La clave principal, nota_exam me permitirá poder resolver adecuadamente el primer requerimiento del enunciado.

En este sentido, la siguiente clave utilizada ha sido creditos_curso, de esta forma resolvemos el requerimiento de ordenación indicado en el enunciado.

El resultado se puede valorar en la siguiente imagen:

2 Resultado del bloque ejercicio_b

3. A partir de las dos familias de columnas creadas en los apartados A) y B) generamos consultas en CQL y mostrar los resultados obtenidos de cada consulta:

  • Recuperar todos los cursos con estudiantes que han recibido una ‘C’ ordenados descendentemente por número de créditos.
      SELECT titulo_curso, creditos_curso FROM ejercicio_b
      WHERE nota_exam = 'C'
      order by creditos_curso desc; 
    La consulta es muy sencilla para la colección ejercicio_b. Utilizamos su clave principal para obtener la información deseada y posteriormente ordenamos los datos según el enunciado.

    3 Resultado ejercicio C apartado 1 Cassandra

  • Obtener el número de créditos máximo que ha obtenido cada estudiante de una asignatura.

     SELECT MAX(creditos_curso) as creditos_max, idE_estudiante, nombre_estudiante, titulo_curso FROM ejercicio_a group by idE_estudiante;
      

    En este punto utilizamos la colección ejercicio_a. Al estar ordenada por la clave principal idE_estudiante, se puede realizar un group by por este valor y obtener la consulta deseada.

    4 Respuesta ejercicio C apartado 2 Cassandra

    Añado el nombre del estudiante, cumpliendo los requerimientos del enunciado.

    5 Respuesta ejercicio C apartado 2 Cassandra (añadido el nombre de estudiante)

  • Recuperar la información del curso “Literatura Moderna” sin usar la cláusula ALLOW FILTERING.

    Sabiendo que “titulo_curso” no forma parte de las family keys de esta colección y que, además, forma parte de una condición del “WHERE”, entonces será necesario definir un índice sobre ese campo. De esta forma podremos evitar el comando ALLOW FILTERING indicada en el enunciado.
     CREATE INDEX i_titulo_curso ON ejercicio_a(titulo_curso);
     SELECT * FROM ejercicio_a where titulo_curso = 'Literatura moderna'; 

    6 Respuesta Ejercicio C apartado 3 Cassandra

  • Cambiar el nombre de la profesora Daniel Perez por Xesus Cortinhas.

      select * from ejercicio_a where profesor_curso='Daniel Pérez' ALLOW FILTERING;
      UPDATE ejercicio_a SET profesor_curso = 'Xesus Cortinhas'
      WHERE idc_curso in (102) and ide_estudiante in (201) and titulo_curso='Griego moderno';
      select * from ejercicio_a where profesor_curso='Daniel Pérez' ALLOW FILTERING;
      select * from ejercicio_a where profesor_curso='Xesus Cortinhas' ALLOW FILTERING;
       

    En este punto, tenemos que realizar modificaciones en ambas colecciones. Para ello realizamos el siguiente procedimiento en la tabla ejercicio_a:
    • Buscamos las líneas afectadas.
    • Obtenemos las primary key que identifican los registros mostrados ( idc_curso in (102) and ide_estudiante in (201) and titulo_curso='Griego moderno')
    • Comprobamos el éxito de la modificación.

    7 Respuesta Ejercicio 4 a en Cassandra. Tabla ejercicio_a

      select nota_exam,titulo_curso, creditos_curso,profesor_curso, ide_estudiante from ejercicio_b where profesor_curso='Daniel Pérez' ALLOW FILTERING;
      UPDATE ejercicio_b SET profesor_curso = 'Xesus Cortinhas' WHERE titulo_curso='Griego moderno' and creditos_curso=7 and ide_estudiante=201 and nota_exam='B';
      select profesor_curso,nota_exam,titulo_curso, creditos_curso, ide_estudiante from ejercicio_b where profesor_curso='Daniel Pérez' ALLOW FILTERING;
      select profesor_curso,nota_exam,titulo_curso, creditos_curso, ide_estudiante from ejercicio_b where profesor_curso='Xesus Cortinhas' ALLOW FILTERING;
     
    Para modificar la colección ejercicio_b realizamos el siguiente procedimiento:
    • Buscamos las líneas afectadas.
    • Obtenemos las primary key que identifican los registros mostrados ( titulo_curso='Griego moderno' and creditos_curso=7 and ide_estudiante=201 and nota_exam='B')
    • Comprobamos el éxito de la modificación.

    8 Respuesta Ejercicio 4 b en Cassandra. Tabla ejercicio_b

  • Eliminar la información relativa al estudiante "Rodrigo Calvo''.
      select * FROM ejercicio_a where nombre_estudiante='Rodrigo Calvo' ALLOW FILTERING;
      DELETE FROM ejercicio_a where idc_curso=106 and ide_estudiante=202 and titulo_curso='Teorías literarias';
      select * FROM ejercicio_a where nombre_estudiante='Rodrigo Calvo' ALLOW FILTERING;
     
    Al igual que el punto anterior, tenemos que realizar modificaciones en ambas colecciones. Para ello realizamos el siguiente procedimiento en la colección ejercicio_a:
    • Buscamos las líneas afectadas.
    • Obtenemos las primary key que identifican los registros mostrados ( idc_curso=106 and ide_estudiante=202 and titulo_curso='Teorías literarias' ) y ejecutamos el delete.
    • Comprobamos el éxito del borrado.

    9 Respuesta Ejercicio 5 a en Cassandra. Tabla ejercicio_a

      SELECT * FROM ejercicio_b where idE_estudiante = 202 ALLOW FILTERING;
      DELETE FROM ejercicio_b where nota_exam='C' and creditos_curso=3 and ide_estudiante=202 and titulo_curso='Teorías literarias' ;
      SELECT * FROM ejercicio_b where idE_estudiante = 202 ALLOW FILTERING;
       
    Ahora realizamos el siguiente procedimiento en la colección ejercicio_b:
    • Buscamos las líneas afectadas.
    • Obtenemos las primary key que identifican los registros mostrados ( nota_exam='C' and creditos_curso=3 and ide_estudiante=202 and titulo_curso='Teorías literarias' ) y ejecutamos el delete.
    • Comprobamos el éxito del borrado.

    10 Respuesta Ejercicio 5 b en Cassandra. Tabla ejercicio_b