kinesis-redshift-copy.sql

-- ** MUST FIRST CHANGE your_bucket_name and cluster_permissions_role_arn **

-- sensor schema
SET search_path = sensor;

-- Copy sample data to tables from S3
TRUNCATE TABLE history;
COPY history (id, serviced, action, technician_id, notes)
    FROM 's3://your_bucket_name/history/'
    CREDENTIALS 'aws_iam_role=cluster_permissions_role_arn'
    CSV IGNOREHEADER 1;

TRUNCATE TABLE location;
COPY location (id, long, lat, description)
    FROM 's3://your_bucket_name/location/'
    CREDENTIALS 'aws_iam_role=cluster_permissions_role_arn'
    CSV IGNOREHEADER 1;

TRUNCATE TABLE sensor;
COPY sensor (id, guid, mac, sku, upc, active, notes)
    FROM 's3://your_bucket_name/sensor/'
    CREDENTIALS 'aws_iam_role=cluster_permissions_role_arn'
    CSV IGNOREHEADER 1;

TRUNCATE TABLE manufacturer;
COPY manufacturer (id, name, website, notes)
    FROM 's3://your_bucket_name/manufacturer/'
    CREDENTIALS 'aws_iam_role=cluster_permissions_role_arn'
    CSV IGNOREHEADER 1;

TRUNCATE TABLE sensors;
COPY sensors (sensor_id, manufacturer_id, location_id, history_id, message_guid)
    FROM 's3://your_bucket_name/sensors/'
    CREDENTIALS 'aws_iam_role=cluster_permissions_role_arn'
    CSV IGNOREHEADER 1;

SELECT COUNT(*) FROM history; -- 30
SELECT COUNT(*) FROM location; -- 6
SELECT COUNT(*) FROM sensor; -- 6
SELECT COUNT(*) FROM manufacturer; --1
SELECT COUNT(*) FROM sensors; -- 30