BigObject Edge Database Demonstration for Sliding Table

BigObject (BO) is a very light and efficient database with small footprint. You can even run it on Raspberry Pi or PINE64. The demonstration shows a scenario that you can use BigObject + Node-RED + Mosquitto to make a naive alerting system with a regular cell phone and a pine64. This demonstration utilizes two unique features:

Sliding Table: A sliding table is a table that keeps only the records inserted in the most recent time period T. Any records stay in the table longer than T is considered expired and removed immediately from the table. A sliding table is a long-lasting and maintenance-free table that operates very efficiently and effectively in terms of memory and disk space. People do not have to worry about deleting old data. Data kept in a sliding table is recent and so-called hot data.
Hot Data Fast Access: Users can access hot data by specifying a recent period for efficiency. BigObject is optimized for performing it efficiently with using timestamp.

Please visit bigobject-inc/edge-database-demo for details.

Note: some third-party nodes may appear with blank styling, and not as they appear in the Node-RED Editor.

[{"id":"a44f0a35.c9f958","type":"tab","label":"MQTT ACC","disabled":false,"info":""},{"id":"ce52b59f.a1c608","type":"mqtt in","z":"a44f0a35.c9f958","name":"","topic":"accelerometer","qos":"2","broker":"fe3c96dc.02e418","x":110,"y":520,"wires":[["ceaf8a1a.704328","7f05456f.598a2c"]]},{"id":"ceaf8a1a.704328","type":"ui_chart","z":"a44f0a35.c9f958","name":"","group":"3501bdf7.b1bd52","order":4,"width":0,"height":0,"label":"Raw data - Y-axis of Accelerometer","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"20","removeOlderPoints":"","removeOlderUnit":"1","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":380,"y":580,"wires":[[],[]]},{"id":"7f05456f.598a2c","type":"BigObject CSV stream","z":"a44f0a35.c9f958","name":"","boserver":"b7c7b281.1183","table":"acc1","index":"","skiprows":"","encodingconv":false,"replacequote":false,"sourceencoding":"","x":340,"y":520,"wires":[["847c340e.813968"]]},{"id":"e68e8f8c.bd362","type":"ui_chart","z":"a44f0a35.c9f958","name":"","group":"3501bdf7.b1bd52","order":3,"width":"0","height":"0","label":"BigObject Sliding Table Queries","chartType":"line","legend":"true","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"20","removeOlderPoints":"","removeOlderUnit":"1","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1230,"y":820,"wires":[[],[]]},{"id":"9ca3b476.a34528","type":"change","z":"a44f0a35.c9f958","name":"Reset","rules":[{"t":"set","p":"payload","pt":"msg","to":"[]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":400,"wires":[["fec73fb2.56b01"]]},{"id":"3e9a50f6.e2b3f","type":"inject","z":"a44f0a35.c9f958","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":400,"wires":[["9ca3b476.a34528"]]},{"id":"dba551f.ff2bab","type":"catch","z":"a44f0a35.c9f958","name":"","scope":null,"x":100,"y":40,"wires":[["79f2694.3590198"]]},{"id":"79f2694.3590198","type":"debug","z":"a44f0a35.c9f958","name":"","active":true,"console":"true","complete":"true","x":250,"y":40,"wires":[]},{"id":"484c61f.5e832a","type":"inject","z":"a44f0a35.c9f958","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":120,"wires":[["8b03e8e1.46b708"]]},{"id":"8b03e8e1.46b708","type":"BigObject SQL","z":"a44f0a35.c9f958","name":"","boserver":"b7c7b281.1183","stmt":"CREATE SLIDING TABLE acc1 (y DOUBLE, TIMEBOUND(20))","x":380,"y":120,"wires":[[]]},{"id":"f94d8712.f4e618","type":"inject","z":"a44f0a35.c9f958","name":"","topic":"","payload":"","payloadType":"date","repeat":"1","crontab":"","once":false,"x":130,"y":820,"wires":[["c9902813.9cb918"]]},{"id":"4fde383a.14d4a8","type":"BigObject SQL","z":"a44f0a35.c9f958","name":"stat in 2s","boserver":"b7c7b281.1183","stmt":"SELECT AVG(y), STDDEV_SAMP(y) FROM acc1 LAST 2s","x":460,"y":820,"wires":[["35e19aa0.2d3806"]]},{"id":"96bb5084.682ae","type":"BigObject SQL","z":"a44f0a35.c9f958","name":"stat in 10s","boserver":"b7c7b281.1183","stmt":"SELECT AVG(y), STDDEV_SAMP(y) FROM acc1 LAST 10s","x":470,"y":880,"wires":[["9aa6f409.063068"]]},{"id":"6de16a5a.5c4f84","type":"mqtt out","z":"a44f0a35.c9f958","name":"","topic":"accelerometer","qos":"0","retain":"","broker":"fe3c96dc.02e418","x":480,"y":280,"wires":[]},{"id":"e4321a2e.6fa6d8","type":"inject","z":"a44f0a35.c9f958","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":120,"y":280,"wires":[["5284e9be.98f958"]]},{"id":"5284e9be.98f958","type":"random","z":"a44f0a35.c9f958","name":"","low":"1","high":"10","inte":"false","x":300,"y":280,"wires":[["6de16a5a.5c4f84"]]},{"id":"d22154b8.da0f18","type":"comment","z":"a44f0a35.c9f958","name":"Random Number Generator","info":"","x":160,"y":220,"wires":[]},{"id":"75daf9cc.0c3168","type":"comment","z":"a44f0a35.c9f958","name":"Table Creation","info":"","x":110,"y":80,"wires":[]},{"id":"35e19aa0.2d3806","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"result","pt":"msg","to":"payload[0].Content.content[0]","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.mean","pt":"msg","to":"result[0]","tot":"msg"},{"t":"set","p":"payload.stddev","pt":"msg","to":"result[1]","tot":"msg"},{"t":"delete","p":"result","pt":"msg"},{"t":"set","p":"time_duration","pt":"msg","to":"2","tot":"num"},{"t":"set","p":"parts","pt":"msg","to":"{\"type\":\"object\", \"id\":topic, \"index\": time_duration, \"count\": 2, \"key\":time_duration}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":820,"wires":[["9e781e07.b22ab","72a13558.c00fcc"]]},{"id":"c9902813.9cb918","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":820,"wires":[["4fde383a.14d4a8","96bb5084.682ae","37e5f643.8e532a"]]},{"id":"9e781e07.b22ab","type":"split","z":"a44f0a35.c9f958","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":830,"y":820,"wires":[["ac72a62a.2440d8"]]},{"id":"ac72a62a.2440d8","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"topic & \"-\" & time_duration & \"s\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":990,"y":820,"wires":[["e68e8f8c.bd362"]]},{"id":"a93d14ea.6afc18","type":"split","z":"a44f0a35.c9f958","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"topic","x":830,"y":880,"wires":[["83d200b.feb92"]]},{"id":"83d200b.feb92","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"topic & \"-\" & time_duration & \"s\"","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":990,"y":880,"wires":[["e68e8f8c.bd362"]]},{"id":"25dd128a.300bae","type":"comment","z":"a44f0a35.c9f958","name":"Clear the charts","info":"","x":120,"y":340,"wires":[]},{"id":"fec73fb2.56b01","type":"link out","z":"a44f0a35.c9f958","name":"send reset message","links":["e4264a75.5206d8","a5e7fe55.7bf35","ff96cc4c.753a4"],"x":395,"y":400,"wires":[]},{"id":"a5e7fe55.7bf35","type":"link in","z":"a44f0a35.c9f958","name":"","links":["fec73fb2.56b01"],"x":155,"y":580,"wires":[["ceaf8a1a.704328"]]},{"id":"e4264a75.5206d8","type":"link in","z":"a44f0a35.c9f958","name":"","links":["fec73fb2.56b01"],"x":1035,"y":940,"wires":[["e68e8f8c.bd362"]]},{"id":"72a13558.c00fcc","type":"join","z":"a44f0a35.c9f958","name":"","mode":"auto","build":"merged","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","x":830,"y":940,"wires":[["75c1e795.da5df8"]]},{"id":"9aa6f409.063068","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"result","pt":"msg","to":"payload[0].Content.content[0]","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.mean","pt":"msg","to":"result[0]","tot":"msg"},{"t":"set","p":"payload.stddev","pt":"msg","to":"result[1]","tot":"msg"},{"t":"delete","p":"result","pt":"msg"},{"t":"set","p":"time_duration","pt":"msg","to":"10","tot":"num"},{"t":"set","p":"parts","pt":"msg","to":"{\"type\":\"object\", \"id\":topic, \"index\": time_duration, \"count\": 2, \"key\":time_duration}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":880,"wires":[["a93d14ea.6afc18","72a13558.c00fcc"]]},{"id":"460504fe.10f40c","type":"function","z":"a44f0a35.c9f958","name":"anomaly detection","func":"var score = Math.abs(msg.payload[\"2\"].mean - msg.payload[\"10\"].mean)/ msg.payload[\"10\"].stddev;\nmsg.payload.current_sample = msg.payload[\"2\"].mean;\nmsg.payload.score = score;\n\nif ( score > 1) {\n    msg.payload.alert = true;\n} else {\n    msg.payload.alert = false;\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":250,"y":1020,"wires":[["9057776d.918dc8","596b4626.7a9518"]]},{"id":"75c1e795.da5df8","type":"link out","z":"a44f0a35.c9f958","name":"joined-query","links":["3ff98bde.dae7b4"],"x":935,"y":940,"wires":[]},{"id":"3ff98bde.dae7b4","type":"link in","z":"a44f0a35.c9f958","name":"","links":["75c1e795.da5df8"],"x":95,"y":1020,"wires":[["460504fe.10f40c"]]},{"id":"9057776d.918dc8","type":"debug","z":"a44f0a35.c9f958","name":"","active":false,"console":"false","complete":"payload.alert","x":490,"y":1060,"wires":[]},{"id":"596b4626.7a9518","type":"ui_template","z":"a44f0a35.c9f958","group":"3501bdf7.b1bd52","name":"","order":1,"width":"16","height":"2","format":"<div layout=\"row\" layout-align=\"space-between\">\n    <h1 ng-style=\"{color: msg.payload.alert ? 'red' : 'green'}\">\n        Y-axis acceleration: {{msg.payload.current_sample | number:3}}, Z-score: {{msg.payload.score | number:3}}\n    </h1>\n</div>","storeOutMessages":true,"fwdInMessages":false,"templateScope":"local","x":460,"y":1020,"wires":[[]]},{"id":"24e188ee.f9ee28","type":"comment","z":"a44f0a35.c9f958","name":"Read data from MQTT broker","info":"","x":160,"y":460,"wires":[]},{"id":"2459f1d5.83f6de","type":"comment","z":"a44f0a35.c9f958","name":"Read statistics from BigObject periodically. And do a naive anomaly detection in the end.","info":"","x":340,"y":660,"wires":[]},{"id":"847c340e.813968","type":"switch","z":"a44f0a35.c9f958","name":"No such object","property":"payload.Status","propertyType":"msg","rules":[{"t":"eq","v":"-11","vt":"num"}],"checkall":"true","outputs":1,"x":560,"y":520,"wires":[["c081f59b.56fcc8"]]},{"id":"c081f59b.56fcc8","type":"link out","z":"a44f0a35.c9f958","name":"","links":["2a7974ed.da571c"],"x":695,"y":520,"wires":[]},{"id":"2a7974ed.da571c","type":"link in","z":"a44f0a35.c9f958","name":"create table acc1","links":["c081f59b.56fcc8"],"x":55,"y":160,"wires":[["8b03e8e1.46b708"]]},{"id":"37e5f643.8e532a","type":"BigObject SQL","z":"a44f0a35.c9f958","name":"stat in 2s","boserver":"b7c7b281.1183","stmt":"SELECT SUM(1) FROM acc1","x":460,"y":760,"wires":[["97719f1a.b8745"]]},{"id":"97719f1a.b8745","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0].Content.content[0][0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":660,"y":760,"wires":[["735c4e07.05fd9"]]},{"id":"735c4e07.05fd9","type":"change","z":"a44f0a35.c9f958","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"No. of Rows in the Sliding Table","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":850,"y":760,"wires":[["5c7debac.2551a4"]]},{"id":"5c7debac.2551a4","type":"ui_chart","z":"a44f0a35.c9f958","name":"","group":"3501bdf7.b1bd52","order":2,"width":"16","height":"3","label":"No. of Rows In the Sliding Table","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"bezier","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":"1","removeOlderPoints":"","removeOlderUnit":"60","cutout":0,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":true,"x":1090,"y":760,"wires":[[],[]]},{"id":"ff96cc4c.753a4","type":"link in","z":"a44f0a35.c9f958","name":"","links":["fec73fb2.56b01"],"x":895,"y":700,"wires":[["5c7debac.2551a4"]]},{"id":"fe3c96dc.02e418","type":"mqtt-broker","z":"","broker":"mqtt_broker","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"3501bdf7.b1bd52","type":"ui_group","z":"","name":"BigObject Edge Database Demonstration","tab":"19cc6d34.f528a3","disp":true,"width":"16"},{"id":"b7c7b281.1183","type":"boserver","z":"","host":"bigobject","port":"9090"},{"id":"19cc6d34.f528a3","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

Flow Info

Created 7 years ago

Rating: not yet rated

view on github

Owner

cchuangatbo

Actions

share flow

Node Types

Core

catch (x1)
change (x8)
comment (x5)
debug (x2)
function (x1)
inject (x4)
join (x1)
link in (x5)
link out (x3)
mqtt in (x1)
mqtt out (x1)
mqtt-broker (x1)
split (x2)
switch (x1)

Other

BigObject CSV stream (x1)
BigObject SQL (x4)
boserver (x1)
random (x1)
tab (x1)
ui_chart (x3)
ui_group (x1)
ui_tab (x1)
ui_template (x1)