Sensor Data Modeling Example

Create a keyspace

Create the sensor_data keyspace:

CREATE KEYSPACE sensor_data
WITH replication = {
  'class': 'NetworkTopologyStrategy', 
  'DC-Houston': 1 };

Set the current working keyspace:

USE sensor_data;

Create tables

Create table networks:

CREATE TABLE networks (
  bucket TEXT,
  name TEXT,
  description TEXT,
  region TEXT,
  num_sensors INT,
  PRIMARY KEY ((bucket),name)
);

Create table temperatures_by_network:

CREATE TABLE temperatures_by_network (
  network TEXT,
  week DATE,
  date_hour TIMESTAMP,
  sensor TEXT,
  avg_temperature FLOAT,
  latitude DECIMAL,
  longitude DECIMAL,
  PRIMARY KEY ((network,week),date_hour,sensor)
) WITH CLUSTERING ORDER BY (date_hour DESC, sensor ASC);

Create table sensors_by_network:

CREATE TABLE sensors_by_network (
  network TEXT,
  sensor TEXT,
  latitude DECIMAL,
  longitude DECIMAL,
  characteristics MAP<TEXT,TEXT>,
  PRIMARY KEY ((network),sensor)
);

Create table temperatures_by_sensor:

CREATE TABLE temperatures_by_sensor (
  sensor TEXT,
  date DATE,
  timestamp TIMESTAMP,
  value FLOAT,
  PRIMARY KEY ((sensor,date),timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);

Populate tables

Execute the CQL script to insert sample data:

SOURCE '~/sensor_data.cql'

Retrieve all rows from table networks:

SELECT * FROM networks;

Retrieve all rows from table temperatures_by_network:

SELECT network, week, date_hour, 
       sensor, avg_temperature 
FROM temperatures_by_network;

Retrieve all rows from table sensors_by_network:

SELECT * FROM sensors_by_network;

Retrieve all rows from table temperatures_by_sensor:

SELECT * FROM temperatures_by_sensor;

Design query Q1

Find information about all networks; order by name (asc):

Solution

SELECT name, description,
       region, num_sensors
FROM networks
WHERE bucket = 'all';

Design query Q2

Find hourly average temperatures for every sensor in network forest-net and date range [2020-07-05,2020-07-06] within the week of 2020-07-05; order by date (desc) and hour (desc):

Solution

SELECT date_hour, avg_temperature, 
       latitude, longitude, sensor 
FROM temperatures_by_network
WHERE network    = 'forest-net'
  AND week       = '2020-07-05'
  AND date_hour >= '2020-07-05'
  AND date_hour  < '2020-07-07';

Find hourly average temperatures for every sensor in network forest-net and date range [2020-07-04,2020-07-06] within the weeks of 2020-06-28 and 2020-07-05; order by date (desc) and hour (desc):

Solution 1

SELECT date_hour, avg_temperature, 
       latitude, longitude, sensor 
FROM temperatures_by_network
WHERE network    = 'forest-net'
  AND week       = '2020-07-05'
  AND date_hour >= '2020-07-04'
  AND date_hour  < '2020-07-07';

SELECT date_hour, avg_temperature, 
       latitude, longitude, sensor 
FROM temperatures_by_network
WHERE network    = 'forest-net'
  AND week       = '2020-06-28'
  AND date_hour >= '2020-07-04'
  AND date_hour  < '2020-07-07';

Solution 2

SELECT date_hour, avg_temperature, 
       latitude, longitude, sensor 
FROM temperatures_by_network
WHERE network    = 'forest-net'
  AND week      IN ('2020-07-05','2020-06-28')
  AND date_hour >= '2020-07-04'
  AND date_hour  < '2020-07-07';

Design query Q3

Find information about all sensors in network forest-net:

Solution

SELECT * 
FROM sensors_by_network
WHERE network = 'forest-net';

Design query Q4

Find raw measurements for sensor s1003 on 2020-07-06; order by timestamp (desc):

Solution

SELECT timestamp, value 
FROM temperatures_by_sensor
WHERE sensor = 's1003'
  AND date   = '2020-07-06';

cd <directory>	Change directory
ls	List directory
echo 'contents' > <file>	Write contents to a file
cat <file>	Output contents of file

i	In Command Mode	Change into Insert Mode, you can now insert and edit text in the file
esc	In Insert Mode	Change into Command Mode, you can now execute commands
:wq	In Command Mode	Save and Exit

Welcome!

Congratulations!

You've completed the scenario!

Scenario Rating

Steps