Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
-- Create new schema in Redshift DB
DROP SCHEMA IF EXISTS sensor CASCADE;
CREATE SCHEMA sensor;
SET search_path = sensor;
-- Create (6) tables in Redshift DB
CREATE TABLE message -- streaming data table
(
id BIGINT IDENTITY (1, 1), -- message id
guid VARCHAR(36) NOT NULL, -- device guid
ts BIGINT NOT NULL DISTKEY SORTKEY, -- epoch in seconds
temp NUMERIC(5, 2) NOT NULL, -- temperature reading
created TIMESTAMP DEFAULT ('now'::text)::timestamp with time zone -- row created at
);
CREATE TABLE location -- dimension table
(
id INTEGER NOT NULL DISTKEY SORTKEY, -- location id
long NUMERIC(10, 7) NOT NULL, -- longitude
lat NUMERIC(10, 7) NOT NULL, -- latitude
description VARCHAR(256) -- location description
);
CREATE TABLE history -- dimension table
(
id INTEGER NOT NULL DISTKEY SORTKEY, -- history id
serviced BIGINT NOT NULL, -- service date
action VARCHAR(20) NOT NULL, -- INSTALLED, CALIBRATED, FIRMWARE UPGRADED, DECOMMISSIONED, OTHER
technician_id INTEGER NOT NULL, -- technician id
notes VARCHAR(256) -- notes
);
CREATE TABLE sensor -- dimension table
(
id INTEGER NOT NULL DISTKEY SORTKEY, -- sensor id
guid VARCHAR(36) NOT NULL, -- device guid
mac VARCHAR(18) NOT NULL, -- mac address
sku VARCHAR(18) NOT NULL, -- product sku
upc VARCHAR(12) NOT NULL, -- product upc
active BOOLEAN DEFAULT TRUE, --active status
notes VARCHAR(256) -- notes
);
CREATE TABLE manufacturer -- dimension table
(
id INTEGER NOT NULL DISTKEY SORTKEY, -- manufacturer id
name VARCHAR(100) NOT NULL, -- company name
website VARCHAR(100) NOT NULL, -- company website
notes VARCHAR(256) -- notes
);
CREATE TABLE sensors -- fact table
(
id BIGINT IDENTITY (1, 1) DISTKEY SORTKEY, -- fact id
sensor_id INTEGER NOT NULL, -- sensor id
manufacturer_id INTEGER NOT NULL, -- manufacturer id
location_id INTEGER NOT NULL, -- location id
history_id BIGINT NOT NULL, -- history id
message_guid VARCHAR(36) NOT NULL -- sensor guid
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment