CRATE.io demo queries

demo.sql

-- count all steps records
SELECT count(*) FROM steps;

-- count all steps
SELECT sum(num_steps) FROM steps;

-- count all steps for a specific user
SELECT sum(num_steps) AS steps_for_user FROM steps WHERE username = 'gosinski';

-- count all steps for a specific user on a specific month
SELECT sum(num_steps) AS steps_per_month FROM steps WHERE username = 'gosinski' AND month_partition = '201409';

-- histogram of daily steps for a specific user in a month
SELECT date_trunc('day', ts), sum(num_steps) FROM steps
WHERE username = 'gosinski' AND month_partition = '201409'
GROUP BY 1;

-- count all steps of all users on a specific day
SELECT date_trunc('day', ts), sum(num_steps) as num_steps, count(*) as num_records
FROM steps
WHERE month_partition = '201409'
GROUP BY 1 ORDER BY 1 DESC;


-- count all steps for a specific user for each day
SELECT format('%tc', date_trunc('day', ts)) AS day, sum(num_steps) AS num_steps, count(*) AS num_records
FROM steps
WHERE username = 'gosinski'
GROUP BY 1;

-------------------------------
-- ADMINISTRATIVE STUFF VIA SQL
-------------------------------

--
-- ! API WILL CHANGE IN THE FUTURE !
--

-- size of your tables
select 
  format('%s.%s', schema_name, table_name) as "table", 
  cast(sum(num_docs) as long) as num_docs, 
  sum(size)/1024/1024 as size_mb 
from sys.shards 
group by 1 limit 10;

-- distribution of allocated disk space
select sum(size)/1024/1024 as size_mb, sys.nodes.id as node, table_name 
from sys.shards 
group by sys.nodes.id, table_name;

-- distribution of shards, docs, replicas
select sum(num_docs) as docs, count(*) as shards, sys.nodes.id as node, table_name 
from sys.shards 
group by sys.nodes.id, table_name;

-- network traffic per host
select max(network['tcp']['packets']['received']+network['tcp']['packets']['sent']) as max, id 
from sys.nodes 
group by id 
order by 1 desc;

-- diff between avg and max traffic
select 
  max(network['tcp']['packets']['received']+network['tcp']['packets']['sent']) as max, 
  max(network['tcp']['packets']['received']+network['tcp']['packets']['sent'])- avg(network['tcp']['packets']['received']+network['tcp']['packets']['sent']) as "max-avg" 
from sys.nodes;