Create tables
✅ Create table networks:
DROP TABLE IF EXISTS networks;
CREATE TABLE IF NOT EXISTS networks (
bucket TEXT,
name TEXT,
description TEXT,
region TEXT,
num_sensors INT,
PRIMARY KEY ((bucket),name)
);
✅ Create table temperatures_by_network:
DROP TABLE IF EXISTS temperatures_by_network;
CREATE TABLE IF NOT EXISTS 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:
DROP TABLE IF EXISTS sensors_by_network;
CREATE TABLE IF NOT EXISTS sensors_by_network (
network TEXT,
sensor TEXT,
latitude DECIMAL,
longitude DECIMAL,
characteristics MAP<TEXT,TEXT>,
PRIMARY KEY ((network),sensor)
);
✅ Create table temperatures_by_sensor:
DROP TABLE IF EXISTS temperatures_by_sensor;
CREATE TABLE IF NOT EXISTS temperatures_by_sensor (
sensor TEXT,
date DATE,
timestamp TIMESTAMP,
value FLOAT,
PRIMARY KEY ((sensor,date),timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);
✅ Verify that the four tables have been created:
DESCRIBE TABLES;