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:

  1. 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.
  2. 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.

[{"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"}]
cchuangatbo

Flow Info

created 1 month ago

Node Types

Core
  • catch (x1)
  • change (x8)
  • comment (x5)
  • debug (x2)
  • function (x1)
  • inject (x4)
  • mqtt in (x1)
  • mqtt out (x1)
  • mqtt-broker (x1)
  • switch (x1)
Other
  • BigObject CSV stream (x1)
  • BigObject SQL (x4)
  • boserver (x1)
  • join (x1)
  • link in (x5)
  • link out (x3)
  • random (x1)
  • split (x2)
  • tab (x1)
  • ui_chart (x3)
  • ui_group (x1)
  • ui_tab (x1)
  • ui_template (x1)

Tags

  • bigobject
  • sliding-table
  • edge
  • database
  • iot
  • mqtt
Copy this flow JSON to your clipboard and then import into Node-RED using the Import From > Clipboard (Ctrl-I) menu option