Apache Cassandra and CQL: data modeling and query examples

Introduction to Cassandra. CQL query examples

The aim of this article is to manage information about courses and students of the master's degree in Hispanic philology in a Cassandra database:

Courses

IdC	Title	Credits	Professor
101	Romance philology	7	Sara Martinez
102	Modern Greek	7	Daniel Perez
103	Modern literature	10	Amalia Sierra
104	Hispanic-American literature	5	Paloma Sanchez
105	Phonetics and morphology	5	Miguel Hernandez
106	Literary theories	3	Juan Garcia
107	General linguistics	3	Samuel Lopez
108	Modern Spanish	10	Isabel Sanz

Ver video

2. Retrieve the information of the courses in which there were students who obtained a specific grade. The statement asks for course title, credits, professor name, student identifier and grade, ordered by the number of course credits.

Credits	Exam grade	IdE	Title	Professor
7	B	201	Romance philology	Sara Martinez
7	B	201	Modern Greek	Daniel Perez
3	C	202	Literary theories	Juan Garcia
10	A	203	Modern literature	Amalia Sierra
5	B	203	Phonetics and morphology	Miguel Hernandez
10	A	203	Modern Spanish	Isabel Sanz
7	B	204	Romance philology	Sara Martinez
10	C	205	Modern literature	Paloma Sanchez
5	B	205	Hispanic-American literature	Paloma Sanchez
10	A	205	Modern Spanish	Isabel Sanz
3	B	206	Literary theories	Juan Garcia
3	B	206	General linguistics	Samuel Lopez
3	A	207	General linguistics	Samuel Lopez
5	C	208	Hispanic-American literature	Paloma Sanchez

In this point we repeat the previous process. To avoid data duplication issues and still include all the information, it is necessary to use `idE_estudiante` and `titulo_curso` as the primary key of the collection. In this case, I do not consider it important to distinguish courses by identifier because what really matters is the course title and its credits.

The order of the keys will be: `nota_exam`, `creditos_curso`, `idE_estudiante` and `titulo_curso`. The main key, `nota_exam`, allows me to solve the first requirement of the statement appropriately.

In that sense, the next key used was `creditos_curso`, which solves the ordering requirement indicated in the statement.

The result can be evaluated in the following image:

3. Starting from the two column families created in sections A) and B), generate CQL queries and show the results obtained in each one:

• Retrieve all courses with students who received a 'C', ordered descending by number of credits.

  The query is very simple for the `ejercicio_b` collection. We use its primary key to obtain the desired information and then sort the data as required by the statement.

  

• Obtain the maximum number of credits obtained by each student from a subject.
  At this point we use the `ejercicio_a` collection. Since it is ordered by the primary key `idE_estudiante`, it is possible to perform a `group by` on that value and obtain the desired query.

  

  I add the student's name, thereby satisfying the requirements of the statement.

  

• Retrieve the information for the course "Modern Literature" without using the ALLOW FILTERING clause.

  Knowing that `titulo_curso` is not part of the family keys of this collection and that it is part of a `WHERE` condition, it is necessary to define an index on that field. In that way we can avoid using the `ALLOW FILTERING` command mentioned in the statement.

  

• Change the professor name Daniel Perez to Xesus Cortinhas.
  At this point we need to make modifications in both collections. To do so, we perform the following procedure in table `ejercicio_a`:
  • Search for the affected rows.
  • Obtain the primary keys that identify the displayed records: `idc_curso in (102) and ide_estudiante in (201) and titulo_curso='Griego moderno'`.
  • Verify that the modification was successful.

  

  To modify collection `ejercicio_b`, we follow this procedure:
  • Search for the affected rows.
  • Obtain the primary keys that identify the displayed records: `titulo_curso='Griego moderno' and creditos_curso=7 and ide_estudiante=201 and nota_exam='B'`.
  • Verify that the modification was successful.

  

• Delete the information related to the student "Rodrigo Calvo".

  As in the previous point, we have to make changes in both collections. For that reason, we perform the following procedure in collection `ejercicio_a`:

  • Search for the affected rows.
  • Obtain the primary keys that identify the displayed records: `idc_curso=106 and ide_estudiante=202 and titulo_curso='Teorias literarias'`, and execute the delete.
  • Verify that the deletion was successful.

  

  We now carry out the following procedure in collection `ejercicio_b`:

  • Search for the affected rows.
  • Obtain the primary keys that identify the displayed records: `nota_exam='C' and creditos_curso=3 and ide_estudiante=202 and titulo_curso='Teorias literarias'`, and execute the delete.
  • Verify that the deletion was successful.