La clause WHERE de CQL à la loupe

Vous voulez tester les instructions CQL évoquées dans ce blog à travers des exercices pratiques ? Regardez la leçon Queries (Requêtes) de la série d’apprentissage Cassandra Fundamentals (Fondamentaux Cassandra).

 

---

 

Malgré leur syntaxe similaire, CQL et SQL présentent de nombreuses différences. Celles-ci proviennent essentiellement du fait que Cassandra traite des données distribuées et vise à prévenir les requêtes inefficaces. Un des points où CQL diffère beaucoup de SQL, c’est la clause WHERE. Ce post se propose de  décrire ce qui est pris en charge par la clause WHERE de CQL et d’expliquer pourquoi elle diffère du langage SQL normal.

Colonnes de clés primaires

Dans Cassandra, deux types de colonnes ont un rôle spécial : les colonnes de clés de partition et les colonnes de clustering. Ensemble, elles définissent votre ligne clé primaire. Les colonnes de clés de partition forment la première partie de la clé primaire et leur rôle consiste à répartir les données de manière homogène dans le cluster. Les lignes seront réparties dans le cluster en fonction du hachage des clés de partition. Les colonnes de clés de clustering servent à regrouper les données d’une partition, ce qui assure une extraction très efficace de lignes. En raison de ces différences de rôle, les colonnes de clés de partition, de clustering et normales prennent en charge différents ensembles de restrictions au sein de la clause WHERE. De plus, ces ensembles de restrictions diffèrent selon le type de requête : SELECT, UPDATE ou DELETE.

Restrictions de la clause WHERE pour les instructions SELECT

Restrictions des clés de partition

Les colonnes de clés de partition prennent en charge uniquement deux opérateurs : = et IN

Restriction IN

Avant 2.2, les restrictions IN ne pouvaient être appliquées qu’à la dernière colonne de la clé de partition. Par exemple, prenons la table suivante :

CREATE TABLE numberOfRequests ( cluster text, date text, time text, numberOfRequests int, PRIMARY KEY ((cluster, date), time) )

Dans 2.1, vous pouviez uniquement utiliser un opérateur IN sur la colonne date. Dans 2.2, vous pouvez utiliser l’opérateur IN sur n’importe quelle colonne de la clé de partition. Par conséquent, une requête du type :

SELECT * FROM numberOfRequests WHERE cluster IN ('cluster1', 'cluster2') AND date = '2015-05-06' AND time >= '12:00' AND time <= '14:00';

est valide depuis 2.2 mais ne l’est pas dans les versions précédentes. Cette modification rend CQL plus cohérent, mais il vous faut cependant rester prudent concernant les restrictions IN sur les colonnes de clés de partition. L’excellent post de Ryan Svihla vous explique clairement pourquoi il vaut mieux les éviter. Une autre modification apportée dans 2.2 est que les résultats ne sont plus rendus dans l’ordre dans lequel la clé de partition a été spécifiée dans la clause IN. À partir de 2.2, les résultats sont donnés dans l’ordre naturel du type de colonne et les doublons sont ignorés.

 

Colonnes de clés de partition sans restriction

Cassandra exige que vous restreignez soit toutes les colonnes de clés de partition, soit aucune, sauf si la requête peut utiliser un index secondaire. Cela signifie qu’une requête telle que :

SELECT * FROM numberOfRequests WHERE cluster='cluster1' AND time ='12:00';

sera rejetée car la colonne date n’a pas de restriction. Cela s’explique du fait que Cassandra a besoin que toutes les colonnes de clés de partition soient capables de calculer le hachage qui lui permettra de situer les nœuds contenant la partition. Si aucune restriction n’est spécifiée sur les clés de partition mais que certaines le sont sur les clés de clustering, Cassandra exigera l’ajout de ALLOW FILTERING à la requête. Pour en savoir plus sur ALLOW FILTERING, jetez un œil au post  ALLOW FILTERING expliqué.

 

Opérateurs >, >=, <= et <

Cassandra répartit la partition entre les nœuds à l’aide du partitionneur sélectionné. Comme seul le ByteOrderedPartitioner conserve une distribution ordonnée des données, Cassandra ne prend pas en charge les opérateurs >, >=, <= et < directement sur la clé de partition. En revanche, elle vous permet d’utiliser les opérateurs >, >=, <= et <; sur la clé de partition via la fonction de jeton.

SELECT * FROM numberOfRequests WHERE token(cluster, date) > token('cluster1', '2015-06-03') AND token(cluster, date) <= token('cluster1', '2015-06-05') AND time = '12:00';

Si vous utilisez un ByteOrderedPartitioner, vous pourrez effectuer des requêtes par plages sur plusieurs partitions. Vous devez cependant rester prudent. L’utilisation d’un ByteOrderedPartitioner n’est pas recommandée car elle peut entraîner un déséquilibre des clusters.

 

Restrictions des colonnes de clustering

Les colonnes de clustering prennent en charge les opérateurs =, IN, >, >=, <=, <, CONTAINS et CONTAINS KEY dans les restrictions de colonne unique et les opérateurs =, IN, >, >=, <= et < dans les restrictions de colonnes multiples.

Colonnes de clustering sans restriction

Le rôle des colonnes de clustering est de regrouper des données au sein d’une partition. Si vous avez la table suivante :

CREATE TABLE numberOfRequests ( cluster text, date text, datacenter text, hour int, minute int, numberOfRequests int, PRIMARY KEY ((cluster, date), datacenter, hour, minute))

Les données seront stockées par partition de la manière suivante :

 

{datacenter: US_WEST_COAST {hour: 0 {minute: 0 {numberOfRequests: 130}} {minute: 1 {numberOfRequests: 125}} … {minute: 59 {numberOfRequests: 97}}} {hour: 1 {minute: 0 …

Vous pouvez voir que, pour extraire les donnés de manière efficace sans index secondaire, vous devez savoir quelles colonnes de clés de clustering vous devez sélectionner. Donc, si vous exécutez :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour = 14 AND minute = 00;

Cassandra trouvera les données de manière efficace, mais si vous exécutez :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND hour = 14 AND minute = 0;

Cassandra rejettera la requête parce qu’elle devrait balayer toute la partition pour trouver les données demandées, ce qui est inefficace.

 

Restrictions IN

Avant la version 2.2, les restrictions IN sur les colonnes de clustering étaient permises uniquement sur la dernière colonne de clustering. À partir de 2.2, la restriction IN peut être utilisée sur n’importe quelle colonne et la requête suivante fonctionnera :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour IN (14, 15) AND minute = 0;

Le même ensemble de données peut être extrait avant 2.2 en utilisant une restriction IN sur colonnes multiples

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour, minute) IN ((14, 0), (15, 0));

Dans 2.2, les restrictions IN sur colonnes multiples peuvent être appliquées à n’importe quel ensemble de colonnes de clustering.

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND (datacentre, hour) IN (('US_WEST_COAST', 14), (‘US_EAST_COAST’, 17)) AND minute = 0;

Avant 2.2, les restrictions IN sur colonnes multiples ne pouvaient être appliquées qu’au dernier ensemble de colonnes de clustering avec restriction. Par conséquent, la requête précédente n’est pas valide dans 2.1. Mais la requête suivante l’est parfaitement.

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour) IN ((14), (15));

 

Restrictions >, >=, <= et <

Les restrictions de tranches de colonnes uniques sont permises uniquement sur la dernière colonne de clustering soumise à des restrictions. Les requêtes suivantes sont donc valides :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour= 12 AND minute >= 0 AND minute <= 30; SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour >= 12; SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter > 'US';

Mais la requête suivante n’est pas valide :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour >= 12 AND minute = 0;

Les restrictions de tranches de colonnes multiples sont permises sur le dernier ensemble de colonnes de clustering soumise à des restrictions.

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WESTCOAST' AND (hour, minute) >= (12, 0) AND (hour, minute) <= (14, 0)

Si les deux côtés sont spécifiés pour la tranche, les restrictions doivent commencer par la même colonne. Par conséquent, la requête :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour, minute) >= (12, 30) AND (hour) < (14)

est valide, mais la requête suivante ne l’est pas :

SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacentre = 'US_WEST_COAST' AND (hour, minute) >= (12, 0) AND (minute) <= (45)

 

Restrictions CONTAINS et CONTAINS KEY

Les restrictions CONTAINS et CONTAINS KEY ne peuvent être utilisées sur des collections que lorsque la requête utilise un index secondaire.

Restrictions de colonnes régulières

Les colonnes régulières peuvent être restreintes par =, >, >=, <= et <, ainsi que par les restrictions CONTAINS ou CONTAINS KEY si la requête repose sur un index secondaire. Les restrictions IN ne sont pas prises en charge.

Requêtes d’index secondaire

Les requêtes directes sur des index secondaires prennent uniquement en charge les restrictions =, CONTAINS ou CONTAINS KEY. La restriction CONTAINS peut uniquement être utilisée sur les types de collection. La restriction CONTAINS KEY peut uniquement être utilisée sur la map pour laquelle les clés ont été indexées. Par exemple, si vous avez la table suivante :

CREATE TABLE contacts ( id int PRIMARY KEY, firstName text, lastName text, phones map<text, text="">, emails set ); CREATE INDEX ON contacts (firstName); CREATE INDEX ON contacts (keys(phones)); // Using the keys function to index the map keys CREATE INDEX ON contacts (emails); </text,>

les requêtes suivantes seront valides :

SELECT * FROM contacts WHERE firstname = 'Benjamin'; SELECT * FROM contacts WHERE phones CONTAINS KEY 'office'; SELECT * FROM contacts WHERE emails CONTAINS 'Benjamin@oops.com';

 

Filtrage d’index secondaires

Les requêtes d’index secondaire vous permettent de restreindre les résultats obtenus avec les restrictions =, >, >=, <= et <, CONTAINS et CONTAINS KEY sur les colonnes non indexées via le filtrage. Par conséquent, les requêtes suivantes sont valides, à condition que ALLOW FILTERING soit spécifié :

SELECT * FROM contacts WHERE firstname = 'Benjamin' AND lastname = 'Lerer' ALLOW FILTERING; SELECT * FROM contacts WHERE phones CONTAINS KEY 'office' AND phones CONTAINS '0000.0000.0000' ALLOW FILTERING;

Vous devez cependant rester prudent avec le filtrage. Il peut être une opération coûteuse.

 

Restrictions des clés de partition et index secondaires

Lorsque Cassandra doit effectuer une requête d’index secondaire, elle va contacter tous les nœuds pour contrôler la partie de l’index secondaire située sur chaque nœud. Si tous les éléments de la clé de partition sont restreints, Cassandra utilisera ces informations pour interroger uniquement les nœuds qui contiennent les clés de partition spécifiées, ce qui rendra la requête plus efficace. Pour les requêtes d’index secondaire, seules les restrictions = sont prises en charge sur les colonnes de clés de partition.

Restrictions des colonnes de clustering et index secondaires

Pour chaque valeur indexée, Cassandra stocke toute la clé primaire (colonnes de clés de partition + colonnes de clustering) de chaque ligne contenant la valeur. Lorsqu’une requête d’index est effectuée, Cassandra extrait les clés primaires des lignes contenant la valeur de l’index. Elle extrait ensuite les lignes de la table et les soumet à tout filtrage nécessaire. Si les premières colonnes de clustering ont été restreintes, Cassandra effectue un filtrage précoce sur les clés primaires rendues par l’index, ce qui rend le filtrage plus efficace. Pour ce type de filtrage, Cassandra accepte les restrictions de colonnes de clustering suivantes : =, IN, >, >=, <= et <. Donc, si nous ajoutons l’index secondaire suivant à la table numberOfRequests :

CREATE INDEX ON numberOfRequests (minute);

la requête suivante est parfaitement valide :

SELECT * FROM numberOfRequests WHERE cluster = 'cluster1' AND date = '2015-06-05' AND datacenter IN ('US_WEST_COAST', 'US_EAST_COAST') AND minute = 0 ALLOW FILTERING;

 

Restrictions de la clause WHERE pour les instructions UPDATE et DELETE

Dans les instructions UPDATE et DELETE, toutes les colonnes de clés primaires doivent être restreintes et les seules restrictions permises sont :

• la restriction = de colonne unique sur toute colonne de clés de partition ou de clustering
• la restriction IN de colonne unique sur la dernière colonne de clés de partition

CASSANDRA-6237 remédiera en partie à ces limites dans 3.0 en ajoutant la prise en charge des instructions UPDATE et DELETE pour :

• les restrictions IN sur toute colonne de clés de partition
• les restrictions IN sur toute colonne de clustering
• les restrictions EQ et IN sur colonnes multiples sur les clés de clustering (mélangées ou non à des restrictions sur colonne unique)

et en ajoutant la prise en charge des instructions DELETE pour la suppression par plages de lignes entières. Les restrictions IN ne seront toujours pas prises en charge pour les mises à jour ou suppressions conditionnelles. La recherche d’index secondaire pour les instructions UPDATE et DELETE n’est pas prise en charge et ne le sera pas en raison du risque associé à la lecture avant écriture.

Conclusion

Pour tirer le maximum de Cassandra, vous devez concevoir vos tables en fonction des requêtes que vous souhaitez effectuer. Nous espérons que les explications fournies dans ce post vous y aideront.

Un dernier mot sur le mélange de restrictions sur colonne unique et colonnes multiples

Dans certaines versions de Cassandra, le mélange de restrictions sur colonne unique et colonnes multiples n’a pas fonctionné correctement. Cela a été convenablement corrigé dans les versions 2.0.15 et 2.1.5.