Scylla CQL and Presto SQL examples

;; CQL

CREATE KEYSPACE mykeyspace WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
use mykeyspace ;
CREATE TABLE users (user_id int PRIMARY KEY, fname text, lname text);
insert into users (user_id , fname, lname) values (1, 'tzach', 'livyatan');
insert into users (user_id , fname, lname) values (2, 'dor', 'laor');                                                               
insert into users (user_id , fname, lname) values (3, 'shlomi', 'laor');                                                            
insert into users (user_id , fname, lname) values (4, 'shlomi', 'livne');                                                           
insert into users (user_id , fname, lname) values (6, 'avi', 'kivity'); 

;; SQL
SELECT * FROM cassandra.mykeyspace.users where user_id >= 2 and user_id <= 3;
 user_id | fname  | lname 
---------+--------+-------
       2 | dor    | laor  
       3 | shlomi | laor  
(2 rows)




;; CQL
CREATE TABLE air_quality_data (sensor_id text,time timestamp,co_ppm int,PRIMARY KEY (sensor_id, time));

INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:00', 17);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:01', 18);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:02', 19);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:03', 20);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:04', 30);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:04', 31);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('my_home', '2016-08-30 07:01:10', 20);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:00', 200);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:01', 201);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:02', 201);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:03', 401);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:04', 402);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:10', 1000);
INSERT INTO air_quality_data(sensor_id, time, co_ppm) VALUES ('your_home', '2016-08-30 07:01:11', 2000);


;; PRESTO
select * from cassandra.mykeyspace.air_quality_data; // not very effictent

select sensor_id, avg(co_ppm) as AVG from cassandra.mykeyspace.air_quality_data group by sensor_id;
 sensor_id |       avg
-----------+--------------------
 your_home |  629.2857142857143 
 my_home   | 20.833333333333332 
(2 rows)


> select avg(co_ppm) as AVG from cassandra.mykeyspace.air_quality_data where sensor_id='my_home';
        AVG
--------------------
 20.833333333333332 
(1 row)