TechnologyJune 8, 2015

A deep look at the CQL WHERE clause

Benjamin Lerer
Benjamin Lerer
A deep look at the CQL WHERE clause
CREATE TABLE numberOfRequests ( cluster text, date text, time text, numberOfRequests int, PRIMARY KEY ((cluster, date), time) )
SELECT * FROM numberOfRequests WHERE cluster IN ('cluster1', 'cluster2') AND date = '2015-05-06' AND time >= '12:00' AND time <= '14:00';
SELECT * FROM numberOfRequests WHERE cluster='cluster1' AND time ='12:00';
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';
CREATE TABLE numberOfRequests ( cluster text, date text, datacenter text, hour int, minute int, numberOfRequests int, PRIMARY KEY ((cluster, date), datacenter, hour, minute))
{datacenter: US_WEST_COAST {hour: 0 {minute: 0 {numberOfRequests: 130}} {minute: 1 {numberOfRequests: 125}} … {minute: 59 {numberOfRequests: 97}}} {hour: 1 {minute: 0 …
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour = 14 AND minute = 00;
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND hour = 14 AND minute = 0;
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour IN (14, 15) AND minute = 0;
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour, minute) IN ((14, 0), (15, 0));
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;
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour) IN ((14), (15));
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';
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND hour >= 12 AND minute = 0;
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)
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacenter = 'US_WEST_COAST' AND (hour, minute) >= (12, 30) AND (hour) < (14)
SELECT * FROM numberOfRequests WHERE cluster = ‘cluster1’ AND date = ‘2015-06-05’ AND datacentre = 'US_WEST_COAST' AND (hour, minute) >= (12, 0) AND (minute) <= (45)
CREATE TABLE contacts ( id int PRIMARY KEY, firstName text, lastName text, phones map<text, text="">, emails set<text> ); 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></text,>
SELECT * FROM contacts WHERE firstname = 'Benjamin'; SELECT * FROM contacts WHERE phones CONTAINS KEY 'office'; SELECT * FROM contacts WHERE emails CONTAINS 'Benjamin@oops.com';
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;
CREATE INDEX ON numberOfRequests (minute);
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;
Share

One-stop Data API for Production GenAI

Astra DB gives JavaScript developers a complete data API and out-of-the-box integrations that make it easier to build production RAG apps with high relevancy and low latency.