Social media dashboard in minutes on IBM i (Db2 for i)
Social media (Twitter) dashboard - IBM Db2 for i and Postgres versions.
This example refers to an article published on IBM Developer in 2017 and available here: i-social-media-dashboard-pdf.
Materials used in this guide: https://ibm.box.com/v/ibmi-twitter-dashboard
Provided as-is, this flow might need to be updated with the latest nodes.
[{"id":"d986efa5.ac1268","type":"comment","z":"500e9ab.320cb64","name":"Twitter Simple Dashboard","info":"","x":115,"y":80,"wires":[]},{"id":"bc9924a5.08c648","type":"function","z":"500e9ab.320cb64","name":"Prepare Data - Line Chart","func":"var msg1={\n\"series\": [\"TweetsSentiment\", \"AvgSentiment\"],\n\"data\": [\n ],\n\"labels\": [\"\"]\n}\nvar data1=[];\nvar data2=[];\nmsg.payload.forEach(function(row) {\n //console.log(new Date(parseInt(row.TIME)));\n var objTweets = \n {\"x\": parseInt(row.TIME) , \"y\": parseInt(row.SENTIMENT_SCORE)};\n data1.push(objTweets);\n var objAvg = \n {\"x\": parseInt(row.TIME) , \"y\": parseInt(row.AVG_SENTIMENT)};\n data2.push(objAvg);\n })\n msg1.data.push(data1);\n msg1.data.push(data2);\n\nreturn [{payload:[msg1]}];","outputs":"1","noerr":0,"x":668,"y":188,"wires":[["3f11299f.9d7f3e"]]},{"id":"31e7c6c5.4c0702","type":"inject","z":"500e9ab.320cb64","name":"Query: Tweets Sentiment vs. time","topic":"","payload":"select a.sentiment_score , a.timestamp_ms as \"TIME\" , sum(b.sentiment_score)/count(b.id) as \"AVG_SENTIMENT\" from sentiment.tweets a, sentiment.tweets b group by a.sentiment_score, a.timestamp_ms , a.timestamp_ms order by a.timestamp_ms asc","payloadType":"str","repeat":"600","crontab":"","once":false,"x":202,"y":187,"wires":[["ce9bd5da.0ed32"]]},{"id":"c3c3ae5a.f9283","type":"ui_chart","z":"500e9ab.320cb64","name":"Twitter Sentiment Pie Chart","group":"4592b34b.790b24","order":1,"width":0,"height":0,"label":"Twitter Sentiment for #IBMi","chartType":"pie","legend":"true","xformat":"HH:mm:ss","interpolate":"linear","nodata":"Displaing Tweets Sentiment","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":"","colors":["#0080ff","#004080","#ff8040","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":893,"y":125,"wires":[[],[]]},{"id":"31d0ead7.976b5e","type":"function","z":"500e9ab.320cb64","name":"Prepare Data - Pie","func":"var m = {};\n/*m.series =[msg.payload[0].SENTIMENT, msg.payload[1].SENTIMENT,msg.payload[2].SENTIMENT];\nm.data = [ [parseInt(msg.payload[0].SCORE)], [parseInt([msg.payload[1].SCORE])],[parseInt([msg.payload[2].SCORE])]];\nm.labels = [\"Twitter Real time Sentiment\"];\n*/\nm.series =[\"Sentiment\"];\nm.data=[];\nm.labels=[];\nmsg.payload.forEach(function(row) {\nm.data.push(parseInt(row.SCORE));\nm.labels.push(row.SENTIMENT);\n});\n\nreturn {payload:[m],topic:\"sentiment\"};\n\n\n//ex for live data, message streaming input\n/*var m = [\n {topic:msg.payload.SENTIMENT, payload:parseInt(msg.payload.SCORE)},\n ];\n return [m]; \n var m = [\n {topic:msg.payload[0].SENTIMENT, payload:parseInt(msg.payload[0].SCORE)},\n {topic:msg.payload[1].SENTIMENT, payload:parseInt(msg.payload[1].SCORE)},\n {topic:msg.payload[2].SENTIMENT, payload:parseInt(msg.payload[2].SCORE)},\n ];\nreturn [m];*/","outputs":1,"noerr":0,"x":635,"y":125,"wires":[["c3c3ae5a.f9283"]]},{"id":"9f0926b.0ad8958","type":"inject","z":"500e9ab.320cb64","name":"Query:Tweets Group by sentiment","topic":"database","payload":" select sentiment_group as sentiment, count(id) as \"SCORE\" from sentiment.tweets group by sentiment_group","payloadType":"str","repeat":"600","crontab":"","once":false,"x":219,"y":124,"wires":[["832c3bd9.561188"]]},{"id":"832c3bd9.561188","type":"DB2 for i","z":"500e9ab.320cb64","mydb":"","name":"","arraymode":true,"x":463,"y":123,"wires":[["31d0ead7.976b5e"]]},{"id":"3f11299f.9d7f3e","type":"ui_chart","z":"500e9ab.320cb64","name":"Twitter Activity","group":"4592b34b.790b24","order":2,"width":"8","height":"7","label":"Twitter Activity","chartType":"line","legend":"true","xformat":"auto","interpolate":"linear","nodata":"Loading...","dot":true,"ymin":"","ymax":"","removeOlder":"12","removeOlderPoints":"30","removeOlderUnit":"3600","cutout":0,"colors":["#8080ff","#ff0080","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":907,"y":188,"wires":[[],[]]},{"id":"ce9bd5da.0ed32","type":"DB2 for i","z":"500e9ab.320cb64","mydb":"","name":"","arraymode":true,"x":456,"y":187,"wires":[["bc9924a5.08c648"]]},{"id":"a1816119.ff5a68","type":"ui_template","z":"500e9ab.320cb64","group":"e73604e8.e8d068","name":"Top Recent Tweets","order":1,"width":"20","height":"5","format":"<style>\n .mycolumn\n {\n font-family: Arial, Helvetica, sans-serif\n font-size: 25px;\n font-weight: bold;\n }\n .mydata\n {\n font-family: Arial, Helvetica, sans-serif\n font-size: 20px;\n font-weight: normal;\n color: grey;\n }\n</style>\n<div class=mycolumn layout=\"row\" layout-align=\"start center\">\n<span flex=\"35\" >Tweet</span>\n<span flex=\"15\" >Name</span>\n<span flex>Location</span>\n<span flex=\"10\">Sentiment</span>\n<span flex> TimeStamp</span>\n</div>\n<div class=mydata layout=\"row\" layout-align=\"start center\" ng-repeat=\"tweet in msg.payload\">\n<span flex=\"35\">{{tweet.tweet}}</span>\n<span flex=\"15\" >{{tweet.name}}</span>\n<span flex >{{tweet.loc}}</span>\n<span flex=\"10\" >{{tweet.score}}</span>\n<span flex >{{tweet.time}}</span>\n</div>","storeOutMessages":false,"fwdInMessages":false,"templateScope":"local","x":898,"y":250,"wires":[[]]},{"id":"dee9520e.a6c7e8","type":"inject","z":"500e9ab.320cb64","name":"Query: TOP Recent Tweets","topic":"","payload":"select SUBSTR(tweet,1,34) as \"tweet\" ,screen_name as \"name\" ,location as \"loc\",sentiment_score as \"score\" ,timestp as \"time\" from sentiment.tweets order by timestp desc FETCH FIRST 8 ROWS ONLY ","payloadType":"str","repeat":"600","crontab":"","once":false,"x":211,"y":249,"wires":[["f16b62ef.b99c7"]]},{"id":"f16b62ef.b99c7","type":"DB2 for i","z":"500e9ab.320cb64","mydb":"","name":"","arraymode":true,"x":452,"y":249,"wires":[["a1816119.ff5a68"]]},{"id":"dfc1fdc.e25dc8","type":"function","z":"500e9ab.320cb64","name":"Prepare Data - Bar","func":"var m = {};\n\nm.series =[];\nm.data=[];\nm.labels=[];\nvar datatemp=[]; \nvar datatemp2=[]; \nmsg.payload.forEach(function(row) {\n datatemp.push(parseInt(row.tweets));\n datatemp2.push(parseInt(row.score)*10);\n m.labels.push(row.loc);\n\n});\nm.data.push(datatemp);\nm.data.push(datatemp2);\nm.series.push(\"#tweets\",\"sentiment\");\n\nreturn {payload:[m],topic:\"sentiment\"};","outputs":1,"noerr":0,"x":651,"y":374,"wires":[["f269c049.8e234"]]},{"id":"d52da7a3.2669a","type":"DB2 for i","z":"500e9ab.320cb64","mydb":"","name":"","arraymode":true,"x":441,"y":374,"wires":[["dfc1fdc.e25dc8"]]},{"id":"f887a9ab.ff9e78","type":"inject","z":"500e9ab.320cb64","name":"Query:Tweets Per Location","topic":"database","payload":"select location as \"loc\",count(*) as \"tweets\" , avg(sentiment_score) as \"score\" from sentiment.tweets where location not like 'nolocation' group by location order by count(*) desc FETCH FIRST 10 ROWS ONLY ","payloadType":"str","repeat":"600","crontab":"","once":false,"x":206,"y":374,"wires":[["d52da7a3.2669a"]]},{"id":"f269c049.8e234","type":"ui_chart","z":"500e9ab.320cb64","name":"","group":"9231bf51.e7ab7","order":2,"width":"20","height":"5","label":"","chartType":"horizontalBar","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"Tweets & Sentiment per Location","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"colors":["#1f77b4","#ff8040","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"x":939,"y":374,"wires":[[],[]]},{"id":"cd95d4ce.dae488","type":"inject","z":"500e9ab.320cb64","name":"Query: Count * from Tweets","topic":"database","payload":"select count(*) as \"tweets\" from sentiment.tweets","payloadType":"str","repeat":"600","crontab":"","once":false,"x":205,"y":309,"wires":[["363bc7fd.8bccf8"]]},{"id":"363bc7fd.8bccf8","type":"DB2 for i","z":"500e9ab.320cb64","mydb":"","name":"","arraymode":true,"x":450,"y":309,"wires":[["10492fa8.b3359"]]},{"id":"10492fa8.b3359","type":"ui_text","z":"500e9ab.320cb64","group":"e73604e8.e8d068","order":0,"width":0,"height":0,"name":"","label":"Total # of Stored Tweets:","format":"{{msg.payload[0].tweets}}","layout":"row-left","x":909,"y":308,"wires":[]},{"id":"4592b34b.790b24","type":"ui_group","z":"","name":"Social Media Overview","tab":"a5888cda.11e72","order":1,"disp":true,"width":"8"},{"id":"e73604e8.e8d068","type":"ui_group","z":"","name":"TOP Recent Tweets","tab":"a5888cda.11e72","order":3,"disp":true,"width":"20"},{"id":"9231bf51.e7ab7","type":"ui_group","z":"","name":"Top 10 Tweet Locations & Sentiment","tab":"a5888cda.11e72","order":4,"disp":true,"width":"20"},{"id":"a5888cda.11e72","type":"ui_tab","z":"","name":"Social Media Dashboard with Db2 for i","icon":"dashboard","order":2}]
