Ways to set up nested keys in Cassandra

NestedKeysCassandra.sql

-- Different ways to handle nested keys
-- Data can be from an Android device installed as part of the 
-- Open Auto Alliance. Hierarchy is Maker: Model:
-- Using bigint casting of 64-bit HEX android_id 

CREATE KEYSPACE auto_data_space WITH replication = {
  'class': 'SimpleStrategy',
  'replication_factor': '3'
};

USE auto_data_space

-- Option 1: Fastest but least Flexible
--Essentially forces maker and model combo, but no INDEX required
create table car_data (
  maker text,
  model text,
  android_id bigint,
  read_ts timestamp,
  speed float,
  engine_temp float,
  fuel_level float,
  mileage float
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY ((maker, model), android_id, read_ts) );


-- Option 2: Second fastest but limited flexibility
-- Can select: ALL, All for Maker, all for Maker and Model
-- or all for Maker, Model in time range
create table car_data (
  maker text,
  model text,
  android_id bigint,
  read_ts timestamp,
  speed float,
  engine_temp float,
  fuel_level float,
  mileage float
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY (maker, model, android_id, read_ts) );

-- Option 3: Most Flexible, byt slowest
-- Needs lots of indices. Not recommended for C*
-- Very similar to models auto-generated by ORM packages
create table car_data (
  read_id timeuuid,
  maker text,
  model text,
  android_id bigint,
  read_ts timestamp,
  speed float,
  engine_temp float,
  fuel_level float,
  mileage float
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY (read_id) );

CREATE INDEX ON car_data(maker);
CREATE INDEX ON car_data(model);
CREATE INDEX ON car_data(android_id);
CREATE INDEX ON car_data(read_ts);


-- Option 4: Optimizes on Android IDs
-- Some indxes needed
-- However, this will not capture repeat transmission of same read,
-- something common in sensor world
create table car_data (
  android_id bigint,
  read_ts timestamp,
  maker text,
  model text,
  speed float,
  engine_temp float,
  fuel_level float,
  mileage float
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY (android_id, read_ts);

CREATE INDEX ON car_data(maker);
CREATE INDEX ON car_data(model);

-- Option 5 Optimizes on Android IDs
-- Allows duplicate read data transfer and capture at price of extra index
-- timeuuid recommended as can be auto-generated quickly with low collision
create table car_data (
  android_id bigint,
  event_uid timeuuid,
  read_ts timestamp,
  maker text,
  model text,
  speed float,
  engine_temp float,
  fuel_level float,
  mileage float
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY (android_id, event_uid);

CREATE INDEX ON car_data(maker);
CREATE INDEX ON car_data(model);
CREATE INDEX ON car_data(read_ts);

-- Continuiing this to aggregate pressure at given date and weather for geocode
-- Using Worldl Aeronautical Codes (WACs) to simplify zoning
-- Aggregates all reads of pressure by make and model for WAC and date
create table pressure_reads_by_local_and_date (
  android_id bigint,
  wac int,
  date text,
  read_ts timestamp,
  maker text,
  model text,
  tire_pressure_fl float,
  tire_pressure_fr float,
  tire_pressure_bl float,
  tire_pressure_br float,
  PRIMARY KEY ((android_id, wac, date), read_ts) );

-- Now add all weather readings for WAC and date
create table wx_event_by_local_and_date (
  wac int,
  date text,
  wx_read_ts timestamp,
  temp float,
  dew_point float,
  PRIMARY KEY ((wac, date), wx_read_ts) );

--However, it may simply be easier to have a WAC + DATE + Hour agg
CREATE TABLE hourly_wx_by_local_and_datw (
  wac int,
  date text,
  hour int,
  temp_mean float,
  temp_max float,
  temp_min float,
  dew_point_mean float,
  dew_point_max float,
  dew_point_min float,
  PRIMARY KEY (wac, date, hour) );