kinesis-redshift-schema-tables.sql

-- 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
);