I wondered what temperatures in Orlando have done over this last week. You see I just happen to have a set of IoT devices which are streaming data that I persist into an archive. One of those sensors is on a covered patio in Orlando, so it would be interesting to see what kind of data there is from this last week.
In my setup, Smartthings sends data to a Spring Cloud Data Flow endpoint which drops the data into files. Those files are parsed and uploaded to Ceph host, a simple IoT data flow. Within Greenplum I have created external tables that can read the data and the table looks like this
gpadmin=# \d smartthings_tracking Table "public.smartthings_tracking" Column | Type | Modifiers ------------+-----------------------------+----------- deviceid | text | name | text | value | text | recordedat | timestamp without time zone | unit | text | Number of child tables: 170 (Use \d+ to list them.) Distributed randomly Partition by: (recordedat)
The structure is simple and I can sample of few of the data points with this query
gpadmin=# \x Expanded display is on. gpadmin=# SELECT * FROM smartthings_tracking WHERE deviceid = 'a15b41c3-8bf0-49df-8571-a5a5d138fdff' ORDER BY recordedat DESC LIMIT 2; -[ RECORD 1 ]------------------------------------ deviceid | a15b41c3-8bf0-49df-8571-a5a5d138fdff name | temperature value | 72 recordedat | 2017-09-12 01:35:47.993 unit | F -[ RECORD 2 ]------------------------------------ deviceid | a15b41c3-8bf0-49df-8571-a5a5d138fdff name | temperature value | 73 recordedat | 2017-09-12 00:20:46.677 unit | F
I know I’m going to want to have a visual representation of the data because looking at it as a list won’t easily convey the information I want. Also I know I will be doing some interactive querying to try to get this right, so I switch from the command line over to Apache Zeppelin which connects to my Greenplum server.
This is one of those cases where I can really appreciate interactive querying of IoT data. I need to do some exploring and will submit queries or attempts at a query a few dozen times. As I’m crafting the visualization I will adjust the window to varying widths of data in order to optimize what I see which will generate more queries. Getting immediate feedback greatly decreases the cycle time in order to get a result. What I’m doing here is fairly simple though and it doesn’t take long before I come up with a query and time window I like.
SELECT deviceid, recordedat::timestamp, value FROM smartthings_tracking WHERE deviceid = 'a15b41c3-8bf0-49df-8571-a5a5d138fdff' AND name = 'temperature' AND recordedat > '2017-09-05 00:00:00' ORDER BY recordedat DESC;
A little bit of messing around with the visualization buttons and I come up with this
Which is not a bad image, but I quickly realize something is wrong with it. These nice angled lines do not show how temperatures work over time. Why is that?
Then I remember, it is because these sensors kick out a temp value only when they have registered a temperature change of a full degree. So the time distance between two points could be 3 seconds or it could be 12 hours.
This will cause the time to not be reflected with spatial accuracy along my X axis. While it is showing the strange weather, reflected by the low values on the left, it isn’t giving me a good perception of how that actually happened in time over the past week.
Then I remember a post by Caleb Welton a few years ago on Time Series analysis. This has exactly what I need. A big part of making this work is setting up a few functions to that will handle and maintain the last known values.
create function last_known_t(prev float8, current float8) returns float8 as $$return current or prev$$ language plpythonu; create aggregate last_known(float8) ( stype =float8, sfunc = last_known_t, prefunc = last_known_t ); create function last_known_t(prev timestamp, current timestamp) returns timestamp as $$return current or prev$$ language plpythonu; create aggregate last_known(timestamp) ( stype = timestamp, sfunc = last_known_t, prefunc = last_known_t );
With those in place I can now create a new query that will create time slices and drop the data into those slices. Now the slices will carry forward the last known value.
WITH bounded AS ( SELECT recordedat, interval_bound(recordedat, '15 minutes') AS slice, value FROM smartthings_tracking WHERE deviceid = 'a15b41c3-8bf0-49df-8571-a5a5d138fdff' AND name = 'temperature' AND recordedat::timestamp > '2017-09-05 00:00:00' ), dense AS ( SELECT slice FROM generate_series('2017-09-05 00:00:00'::timestamp, '2017-09-13 00:00:00', '15 minutes') s(slice) ) SELECT slice, value::int, last_known(value::int) OVER (ORDER BY slice, recordedat) FROM BOUNDED RIGHT JOIN dense USING (slice) ORDER BY slice, recordedat;
Looking at a piece of the raw output it becomes easy to see how different the data appears as it is chunked into even time periods
I will potentially double up on some numbers if it jumped more than a degree in a 15 minute period, but my visualization can show the average so it is not a problem. This leads to a much better visualization of the temperatures
The temperatures sure did deviate from the normal pattern there for a little while. This isn’t a fantastic visualization, for under and hour of work though it does give me a decent visual representation of the strangeness in the weather. I feel lucky the the only impact I see is in this graph and pray that those dealing with greater impacts stay healthy and find a path to recovery.