Interrogation¶

Objectifs

Importer des données à partir de fichier CSV
Créer des requêtes CQL
Comprendre le Partitionnement et le Clustering
Réaliser des agrégations

Importer des données à partir de fichiers CSV¶

Télécharger l'archive contenant les données

Télécharger l'archive restaurants.zip
Décompresser le contenu
Examiner le contenu des fichiers csv (voir ci-dessous)

Il s'agit de données concernant des restaurant de New York et les résultats des inspections réalisées à ces derniers. Ces données sont respectivement dans les fichiers restaurants.csv et restaurants_inspections.csv.

Copier les fichiers CSV dans le container Cassandra

docker cp restaurants.csv cassandra:/
docker cp restaurants_inspections.csv cassandra:/

Créer le Keyspace et les tables

Se connecter à Cassandra avec cqlsh
```
docker exec -it cassandra cqlsh
```

Créer le Keyspace

CREATE KEYSPACE IF NOT EXISTS resto_NY
WITH REPLICATION = { 'class' :'SimpleStrategy', 'replication_factor': 1};

Créer les tables

USE resto_NY;
CREATE TABLE Restaurant (
  id INT,
  Name VARCHAR,
  borough VARCHAR,
  BuildingNum VARCHAR,
  Street VARCHAR,
  ZipCode INT,
  Phone text,
  CuisineType VARCHAR,
  PRIMARY KEY (id)
);
CREATE INDEX fk_Restaurant_cuisine ON Restaurant (CuisineType);
CREATE TABLE Inspection (
  idRestaurant INT,
  InspectionDate date,
  ViolationCode VARCHAR,
  ViolationDescription VARCHAR,
  CriticalFlag VARCHAR,
  Score INT,
  GRADE VARCHAR,
  PRIMARY KEY (idRestaurant, InspectionDate)
);
CREATE INDEX fk_Inspection_Restaurant ON Inspection (Grade);

Importer les fichier CSV avec la commande COPY

COPY Restaurant (
  id,
  name,
  borough,
  buildingnum,
  street,
  zipcode,
  phone,
  cuisinetype
)
FROM
'/restaurants.csv' WITH DELIMITER = ',' AND SKIPROWS = 1;

COPY Inspection (
  idrestaurant,
  inspectiondate,
  violationcode,
  violationdescription,
  criticalflag,
  score,
  grade
)
FROM
'/restaurants_inspections.csv' WITH DELIMITER = ',' AND SKIPROWS = 1;

DELIMITER est le séparateur et SKIPROWS indique le nombre de lignes à ignorer permettant ainsi d'ignorer l'entête.

Vérifier le nombre de lignes dans chaque table

SELECT count(*) FROM restaurant;
SELECT count(*) FROM inspection;

Les réponses doivent être respectivement 25624 et 149818

Format de la requête SELECT¶

select_statement::= SELECT [ JSON | DISTINCT ] ( select_clause | '*' )
    FROM `table_name`
    [ WHERE `where_clause` ]
    [ GROUP BY `group_by_clause` ]
    [ ORDER BY `ordering_clause` ]
    [ PER PARTITION LIMIT (`integer` | `bind_marker`) ]
    [ LIMIT (`integer` | `bind_marker`) ]
    [ ALLOW FILTERING ]
select_clause::= `selector` [ AS `identifier` ] ( ',' `selector` [ AS `identifier` ] )
selector::== `column_name`
    | `term`
    | CAST '(' `selector` AS `cql_type` ')'
    | `function_name` '(' [ `selector` ( ',' `selector` )_ ] ')'
    | COUNT '(' '_' ')'
where_clause::= `relation` ( AND `relation` )*
relation::= column_name operator term
    '(' column_name ( ',' column_name )* ')' operator tuple_literal
    TOKEN '(' column_name# ( ',' column_name )* ')' operator term
operator::= '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
group_by_clause::= column_name ( ',' column_name )*
ordering_clause::= column_name [ ASC | DESC ] ( ',' column_name [ ASC | DESC ] )*

Requêtes simples¶

Quel est le nom du retaurant ayant l'id 41692194

SELECT name FROM restaurant WHERE id=41692194;

Quel est le nom du retaurant ayant un id supérieur à 41692194

SELECT name FROM restaurant WHERE id>41692194;

Remarque

Cette requête provoque une erreur parce que Cassandra n'accepte que les conditions d'égalité sur la clé primaire.

Ajouter ALLOW FILTERING pour l'exécuter.

Quel est l'id du retaurant BANGKOK HOUSE

SELECT id FROM restaurant WHERE name = 'BANGKOK HOUSE';

Remarque

Cette requête provoque une erreur parce que Cassandra n'accepte que les conditions sur la clé primaire.

Ajouter ALLOW FILTERING pour l'exécuter ou ajouter un index sur la colonne name.

Quels sont les dates d'inspections du retaurant d'id = 41692194

SELECT inspectiondate FROM inspection WHERE idrestaurant=41692194;

Remarque

Remarquez que les dates sont triées. Puisque c'est la clé de clustering.

Trouver les restaurants inspectés entre le 12/10/2016 et les 3 jours qui suivent (résultat au format JSON)

SELECT JSON idrestaurant, inspectiondate FROM inspection WHERE inspectiondate>='2016-10-12' and inspectiondate<='2016-10-12'+3d ALLOW FILTERING;

Quels sont les jours de avril 2015 ayant au moins une inspection.

SELECT distinct inspectiondate FROM inspection WHERE inspectiondate>='2015-04-01' and inspectiondate<='2015-04-30' ALLOW FILTERING;

Remarque

DISTINCT ne peut être utilisé qu'avec des colonnes statique ou clé de partitionnement et le filtrage doit porter sur la clé de partitionnement.

Quel est le nom des retaurants ayant reçu un grade de A le 14/10/2016

SELECT idrestaurant FROM inspection WHERE grade='A' AND inspectiondate='2016-10-14';

Remarque

Pas de jointure dans Cassandra.

Utiliser les types utilisateur et les collections pour avoir une modélisation dénormalisée.

Quels sont les retaurants ayant été inspecté le 14/10/2016 triés selon le score décroissant

SELECT idrestaurant, score FROM inspection WHERE inspectiondate='2016-10-14' ORDER BY score;

Remarque

ORDER BY ne peut être qu'avec un filtrage sur la clé primaire avec = ou IN et sur la clé de clustering.

Agrégations¶

Quel est le nombre de restaurant de MANHATTAN

SELECT count(*) FROM restaurant WHERE borough='MANHATTAN' ALLOW FILTERING;

Quel est le nombre d'inspections par grade

SELECT grade, count(*) as total FROM inspection GROUP BY grade;

Remarque

GROUP BY ne peut être utilisé qu'avec les colonnes de la clé primaire.

Quel est le nombre d'inspections par jour

SELECT count(*) FROM inspection GROUP BY inspectiondate;

Remarque

Les colonnes dans GROUP BY ne peuvent être utilisées que dans l'ordre de définition de la clé primaire.

Quel est le nombre d'inspections par restaurant

SELECT idrestaurant, count(*) as total FROM inspection GROUP BY idrestaurant;