Loading Data into GridDB and then fetching data from GridDB<\/h2>\n\nvar griddb = require('griddb_node');\n\nconst createCsvWriter = require('csv-writer').createObjectCsvWriter;\nconst csvWriter = createCsvWriter({\n path: 'out.csv',\n header: [\n {id: \"DATE\", title:\"DATE\"}, \n {id: \"WIND\", title:\"WIND\"}, \n {id: \"IND\", title:\"IND\"}, \n {id: \"RAIN\", title:\"RAIN\"}, \n {id: \"IND.1\", title:\"IND.1\"}, \n {id: \"T.MAX\", title:\"T.MAX\"}, \n {id: \"IND.2\" , title:\"IND.2\"}, \n {id: \"T.MIN\", title:\"T.MIN\"}, \n {id: \"T.MIN.G\", title:\"T.MIN.G\"}, \n ]\n});\n\nconst factory = griddb.StoreFactory.getInstance();\nconst store = factory.getStore({\n \"host\": '239.0.0.1',\n \"port\": 31999,\n \"clusterName\": \"defaultCluster\",\n \"username\": \"admin\",\n \"password\": \"admin\"\n});\n\/\/ For connecting to the GridDB Server we have to make containers and specify the schema.\nconst conInfo = new griddb.ContainerInfo({\n 'name': \"windspeedanalysis\",\n 'columnInfoList': [\n [\"name\", griddb.Type.STRING],\n [\"DATE\", griddb.Type.STRING],\n [\"WIND\", griddb.Type.STRING],\n [\"IND\", griddb.Type.STRING],\n [\"RAIN\", griddb.Type.STRING],\n [\"IND.1\", griddb.Type.STRING],\n [\"T.MAX\", griddb.Type.STRING],\n [\"IND.2\", griddb.Type.STRING],\n [\"T.MIN\", griddb.Type.STRING],\n [\"T.MIN.G\", griddb.Type.STRING]\n ],\n 'type': griddb.ContainerType.COLLECTION, 'rowKey': true\n});\n\nconst csv = require('csv-parser');\n\nconst fs = require('fs');\nvar lst = []\nvar lst2 = []\nvar i =0;\nfs.createReadStream('.\/dataset\/windspeed.csv')\n .pipe(csv())\n .on('data', (row) => {\n lst.push(row);\n console.log(lst);\n\n })\n .on('end', () => {\n\n var container;\n var idx = 0;\n \n for(let i=0;i<lst.length;i++){\n\n \/\/ lst[i]['DATE'] = String(lst[i][\"DATE\"])\n \/\/ lst[i]['WIND'] = parseFloat(lst[i][\"WIND\"])\n \/\/ lst[i]['IND'] = parseInt(lst[i][\"IND\"])\n \/\/ lst[i]['RAIN'] = parseFloat(lst[i][\"RAIN\"])\n \/\/ lst[i]['IND.1'] = parseInt(lst[i][\"IND.1\"])\n \/\/ lst[i]['T.MAX'] = parseFloat(lst[i][\"T.MAX\"])\n \/\/ lst[i]['IND.2'] = parseInt(lst[i][\"IND.2\"])\n \/\/ lst[i]['T.MIN'] = parseFloat(lst[i][\"T.MIN\"])\n \/\/ lst[i]['T.MIN.G'] = parseFloat(lst[i][\"T.MIN.G\"])\n\n store.putContainer(conInfo, false)\n .then(cont => {\n container = cont;\n return container.createIndex({ 'columnName': 'name', 'indexType': griddb.IndexType.DEFAULT });\n })\n .then(() => {\n idx++;\n container.setAutoCommit(false);\n return container.put([String(idx), lst[i]['DATE'],lst[i][\"WIND\"],lst[i][\"IND\"],lst[i][\"RAIN\"],lst[i][\"IND.1\"],lst[i][\"T.MAX\"],lst[i][\"IND.2\"],lst[i][\"T.MIN\"],lst[i][\"T.MIN.G\"]]);\n })\n .then(() => {\n return container.commit();\n })\n \n .catch(err => {\n if (err.constructor.name == \"GSException\") {\n for (var i = 0; i < err.getErrorStackSize(); i++) {\n console.log(\"[\", i, \"]\");\n console.log(err.getErrorCode(i));\n console.log(err.getMessage(i));\n }\n } else {\n console.log(err);\n }\n });\n \n }\n\n store.getContainer(\"windspeedanalysis\")\n .then(ts => {\n container = ts;\n query = container.query(\"select *\")\n return query.fetch();\n })\n .then(rs => {\n while (rs.hasNext()) {\n let rsNext = rs.next()\n lst2.push(\n \n {\n 'DATE': rsNext[1],\n \"WIND\": rsNext[2],\n \"IND\": rsNext[3],\n \"RAIN\": rsNext[4],\n \"IND.1\": rsNext[5],\n \"T.MAX\": rsNext[6],\n \"IND.2\": rsNext[7],\n \"T.MIN\": rsNext[8],\n \"T.MIN.G\": rsNext[9],\n }\n );\n }\n csvWriter\n .writeRecords(lst2)\n .then(()=> console.log('The CSV file was written successfully'));\n return \n }).catch(err => {\n if (err.constructor.name == \"GSException\") {\n for (var i = 0; i < err.getErrorStackSize(); i++) {\n console.log(\"[\", i, \"]\");\n console.log(err.getErrorCode(i));\n console.log(err.getMessage(i));\n }\n } else {\n console.log(err);\n }\n }); \n});\n <\/code><\/pre>\n<\/div>\nNote: For running the d3.js in the html file an httpserver should be run first to read the output csv file containing the data fetched from GridDB.<\/p>\n
Data Visualization:<\/h2>\n
For the purpose of plotting and drawing tables, we will use D3.js, a library that allows manipulation of documents based on data. HTML DOM manipulation and visualization is possible with this library.<\/p>\n
We will first start with printing the first few rows of the dataset to get a feel for the data. For this purpose we are going to have to write the code for creating and inserting data into an html table. This is going to be helper code and will be included in the source files, and is beyond the scope of this article.<\/p>\n
Once this is done, we can write the code for printing the table.<\/p>\n
\nlet result = data.flatMap(Object.keys);\nresult = [...new Set(result)];\n\ntables(\"data_table\",data,result,true)<\/code><\/pre>\n<\/div>\nIn the code above, result is the array of columns of our data, and “data_table” is the HTML Element ID for the table we are going to insert data into. The last argument is a flag for showing the top 5 rows, which we set to true.<\/p>\n
Here we see the result of our code:<\/p>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/Head.png\")
<\/a><\/p>\nNow that we see the kind of data that we have, we would now want to look at a summary of different statistics related to all the numeric columns of our data. We can use the same helper function for the drawing the table to the HTML document, but we will need to write the code for getting all the summarized quantities. We will get the Minimum, Maximum, Deviation, Mean, Median and a Count of rows that are not null in the data. Fortunately D3.js provides functions for most of these, but we will have to create a helper function for the count of not null rows. We can simply write a for loop and increment a value if the row for that column is not null and return the value at the end.<\/p>\n
\nconst countNotNull = (array) => {\n let count = 0\n for(let i = 0; i < array.length; i++){\n if(isNaN(parseInt(array[i]))==false && array[i]!==null){ count++ }}\n return count\n};<\/code><\/pre>\n<\/div>\nWith that out of the way, we can write the for loop for getting the summary data of our dataset. We are going to use an HTML element with the ID “data_table2” to draw this table.<\/p>\n
\nlet obj = {}\n\nlet summary_data = []\nfor(let i = 0; i < result.length; i++){\n \n obj[result[i]] = data.map(function(elem){return elem[result[i]]});\n let count = 0\n let summarize = {}\n\n if(result[i]!='DATE'){\n obj[result[i]] = obj[result[i]].map(Number)\n }\n summarize['Field'] = result[i]\n summarize['Max'] = d3.max(obj[result[i]])\n summarize['Min'] = d3.min(obj[result[i]])\n summarize['Deviation'] = d3.deviation(obj[result[i]])\n summarize['Mean'] = d3.mean(obj[result[i]])\n summarize['Median'] = d3.median(obj[result[i]])\n \n summarize['Count Not Null'] = countNotNull(obj[result[i]])\n \n summary_data.push(summarize)\n}\nlet result2 = summary_data.flatMap(Object.keys)\nresult2 = [...new Set(result2)];\ntables(\"data_table2\",summary_data,result2,false)<\/code><\/pre>\n<\/div>\nThe result should be as follows:<\/p>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/Summary.png\")
<\/a><\/p>\nNow, we would want to see the actual distributions for each of the columns in the data in the form of histogram and a box plot. Again for both plots we will need to write helper functions, which are provided in the source files. After writing those helper functions, we can call them directly in our main javascript file.<\/p>\n
\n## WindSpeed\nhistogram(\"my_dataviz\",obj['WIND'],'Average Wind Speed')\nboxplot(\"my_dataviz\",obj['WIND'],\"Wind Speed\")\n\n## Precipitation\nhistogram(\"my_dataviz2\",obj['RAIN'],'Precipitation (Rain)',\"orange\")\nboxplot(\"my_dataviz2\",obj['RAIN'],\"Precipitation (Rain)\",'brown')\n\n## Max Temperature\nhistogram(\"my_dataviz3\",obj['T.MAX'],'Max Temperature',\"green\")\nboxplot(\"my_dataviz3\",obj['T.MAX'],\"Max Temperature\",'yellow')\n\n## Min Temperature\nhistogram(\"my_dataviz4\",obj['T.MIN'],'Min Temperature',\"purple\")\nboxplot(\"my_dataviz4\",obj['T.MIN'],\"Min Temperature\",'pink')\n\n## Minimum Grass Temperature\nhistogram(\"my_dataviz5\",obj['T.MIN.G'],'Minimum Grass Temperature',\"cyan\")\nboxplot(\"my_dataviz5\",obj['T.MIN.G'],\"Minimum Grass Temperature\",'orange')\n\n## Indicator Variable\nhistogram(\"my_dataviz6\",obj['IND'],'Indicator')\n\n## Indicator Variable 1\nhistogram(\"my_dataviz7\",obj['IND.1'],'Indicator 1',\"orange\")\n\n## Indicator Variable 2\nhistogram(\"my_dataviz8\",obj['IND.2'],'Indicator 2',\"green\")<\/code><\/pre>\n<\/div>\nIn each of these plotting functions, the first argument is the ID of the html element in which we want to draw the plot, then the array of the column of the data we want to plot, then a title and a CSS-compatible color code. We can visualize the results by opening the html file from the web server.<\/p>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/Hist1.png\")
<\/a><\/p>\n![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/Hist2.png\")
<\/a><\/p>\nWe’ve had a look at plots for distributions, but since the data is a timeseries, we should also plot its columns against time to visualize the trends and patterns in the data. So now we are going to make a line plot of each of the variables in the dataset. The lineplot()<\/code> function was written by us and is included in the source files.<\/p>\n\nlineplot(\"my_dataviz9\",data,obj,'DATE','WIND','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','RAIN','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MAX','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MIN','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MIN.G','#4169e1')<\/code><\/pre>\n<\/div>\nAnd here are the results.<\/p>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/LinePlots.png\")
<\/a><\/p>\nWe can see from the graphs that all variables have periodic pattern as expected from weather data and have averages just as shown by the histograms.<\/p>\n
Lastly, we can plot a correlation matrix to see which columns are correlated with each other, an important thing to look for when making predictions, as we would want to include variables in our model that are correlated with the target variable and exclude variables that are correlated to each other. A simple call to correllogram() function with the data as an argument will give us the correlation matrix. correllogram() comes from correl.js file which is provided in the source file.<\/p>\n
\ncorrelogram(data)<\/code><\/pre>\n<\/div>\nAnd here is what the correlation matrix looks like:<\/p>\n
var griddb = require('griddb_node');\n\nconst createCsvWriter = require('csv-writer').createObjectCsvWriter;\nconst csvWriter = createCsvWriter({\n path: 'out.csv',\n header: [\n {id: \"DATE\", title:\"DATE\"}, \n {id: \"WIND\", title:\"WIND\"}, \n {id: \"IND\", title:\"IND\"}, \n {id: \"RAIN\", title:\"RAIN\"}, \n {id: \"IND.1\", title:\"IND.1\"}, \n {id: \"T.MAX\", title:\"T.MAX\"}, \n {id: \"IND.2\" , title:\"IND.2\"}, \n {id: \"T.MIN\", title:\"T.MIN\"}, \n {id: \"T.MIN.G\", title:\"T.MIN.G\"}, \n ]\n});\n\nconst factory = griddb.StoreFactory.getInstance();\nconst store = factory.getStore({\n \"host\": '239.0.0.1',\n \"port\": 31999,\n \"clusterName\": \"defaultCluster\",\n \"username\": \"admin\",\n \"password\": \"admin\"\n});\n\/\/ For connecting to the GridDB Server we have to make containers and specify the schema.\nconst conInfo = new griddb.ContainerInfo({\n 'name': \"windspeedanalysis\",\n 'columnInfoList': [\n [\"name\", griddb.Type.STRING],\n [\"DATE\", griddb.Type.STRING],\n [\"WIND\", griddb.Type.STRING],\n [\"IND\", griddb.Type.STRING],\n [\"RAIN\", griddb.Type.STRING],\n [\"IND.1\", griddb.Type.STRING],\n [\"T.MAX\", griddb.Type.STRING],\n [\"IND.2\", griddb.Type.STRING],\n [\"T.MIN\", griddb.Type.STRING],\n [\"T.MIN.G\", griddb.Type.STRING]\n ],\n 'type': griddb.ContainerType.COLLECTION, 'rowKey': true\n});\n\nconst csv = require('csv-parser');\n\nconst fs = require('fs');\nvar lst = []\nvar lst2 = []\nvar i =0;\nfs.createReadStream('.\/dataset\/windspeed.csv')\n .pipe(csv())\n .on('data', (row) => {\n lst.push(row);\n console.log(lst);\n\n })\n .on('end', () => {\n\n var container;\n var idx = 0;\n \n for(let i=0;i<lst.length;i++){\n\n \/\/ lst[i]['DATE'] = String(lst[i][\"DATE\"])\n \/\/ lst[i]['WIND'] = parseFloat(lst[i][\"WIND\"])\n \/\/ lst[i]['IND'] = parseInt(lst[i][\"IND\"])\n \/\/ lst[i]['RAIN'] = parseFloat(lst[i][\"RAIN\"])\n \/\/ lst[i]['IND.1'] = parseInt(lst[i][\"IND.1\"])\n \/\/ lst[i]['T.MAX'] = parseFloat(lst[i][\"T.MAX\"])\n \/\/ lst[i]['IND.2'] = parseInt(lst[i][\"IND.2\"])\n \/\/ lst[i]['T.MIN'] = parseFloat(lst[i][\"T.MIN\"])\n \/\/ lst[i]['T.MIN.G'] = parseFloat(lst[i][\"T.MIN.G\"])\n\n store.putContainer(conInfo, false)\n .then(cont => {\n container = cont;\n return container.createIndex({ 'columnName': 'name', 'indexType': griddb.IndexType.DEFAULT });\n })\n .then(() => {\n idx++;\n container.setAutoCommit(false);\n return container.put([String(idx), lst[i]['DATE'],lst[i][\"WIND\"],lst[i][\"IND\"],lst[i][\"RAIN\"],lst[i][\"IND.1\"],lst[i][\"T.MAX\"],lst[i][\"IND.2\"],lst[i][\"T.MIN\"],lst[i][\"T.MIN.G\"]]);\n })\n .then(() => {\n return container.commit();\n })\n \n .catch(err => {\n if (err.constructor.name == \"GSException\") {\n for (var i = 0; i < err.getErrorStackSize(); i++) {\n console.log(\"[\", i, \"]\");\n console.log(err.getErrorCode(i));\n console.log(err.getMessage(i));\n }\n } else {\n console.log(err);\n }\n });\n \n }\n\n store.getContainer(\"windspeedanalysis\")\n .then(ts => {\n container = ts;\n query = container.query(\"select *\")\n return query.fetch();\n })\n .then(rs => {\n while (rs.hasNext()) {\n let rsNext = rs.next()\n lst2.push(\n \n {\n 'DATE': rsNext[1],\n \"WIND\": rsNext[2],\n \"IND\": rsNext[3],\n \"RAIN\": rsNext[4],\n \"IND.1\": rsNext[5],\n \"T.MAX\": rsNext[6],\n \"IND.2\": rsNext[7],\n \"T.MIN\": rsNext[8],\n \"T.MIN.G\": rsNext[9],\n }\n );\n }\n csvWriter\n .writeRecords(lst2)\n .then(()=> console.log('The CSV file was written successfully'));\n return \n }).catch(err => {\n if (err.constructor.name == \"GSException\") {\n for (var i = 0; i < err.getErrorStackSize(); i++) {\n console.log(\"[\", i, \"]\");\n console.log(err.getErrorCode(i));\n console.log(err.getMessage(i));\n }\n } else {\n console.log(err);\n }\n }); \n});\n <\/code><\/pre>\n<\/div>\nNote: For running the d3.js in the html file an httpserver should be run first to read the output csv file containing the data fetched from GridDB.<\/p>\n
Data Visualization:<\/h2>\n
For the purpose of plotting and drawing tables, we will use D3.js, a library that allows manipulation of documents based on data. HTML DOM manipulation and visualization is possible with this library.<\/p>\n
We will first start with printing the first few rows of the dataset to get a feel for the data. For this purpose we are going to have to write the code for creating and inserting data into an html table. This is going to be helper code and will be included in the source files, and is beyond the scope of this article.<\/p>\n
Once this is done, we can write the code for printing the table.<\/p>\n
\nlet result = data.flatMap(Object.keys);\nresult = [...new Set(result)];\n\ntables(\"data_table\",data,result,true)<\/code><\/pre>\n<\/div>\nIn the code above, result is the array of columns of our data, and “data_table” is the HTML Element ID for the table we are going to insert data into. The last argument is a flag for showing the top 5 rows, which we set to true.<\/p>\n
Here we see the result of our code:<\/p>\n
<\/a><\/p>\nNow that we see the kind of data that we have, we would now want to look at a summary of different statistics related to all the numeric columns of our data. We can use the same helper function for the drawing the table to the HTML document, but we will need to write the code for getting all the summarized quantities. We will get the Minimum, Maximum, Deviation, Mean, Median and a Count of rows that are not null in the data. Fortunately D3.js provides functions for most of these, but we will have to create a helper function for the count of not null rows. We can simply write a for loop and increment a value if the row for that column is not null and return the value at the end.<\/p>\n
\nconst countNotNull = (array) => {\n let count = 0\n for(let i = 0; i < array.length; i++){\n if(isNaN(parseInt(array[i]))==false && array[i]!==null){ count++ }}\n return count\n};<\/code><\/pre>\n<\/div>\nWith that out of the way, we can write the for loop for getting the summary data of our dataset. We are going to use an HTML element with the ID “data_table2” to draw this table.<\/p>\n
\nlet obj = {}\n\nlet summary_data = []\nfor(let i = 0; i < result.length; i++){\n \n obj[result[i]] = data.map(function(elem){return elem[result[i]]});\n let count = 0\n let summarize = {}\n\n if(result[i]!='DATE'){\n obj[result[i]] = obj[result[i]].map(Number)\n }\n summarize['Field'] = result[i]\n summarize['Max'] = d3.max(obj[result[i]])\n summarize['Min'] = d3.min(obj[result[i]])\n summarize['Deviation'] = d3.deviation(obj[result[i]])\n summarize['Mean'] = d3.mean(obj[result[i]])\n summarize['Median'] = d3.median(obj[result[i]])\n \n summarize['Count Not Null'] = countNotNull(obj[result[i]])\n \n summary_data.push(summarize)\n}\nlet result2 = summary_data.flatMap(Object.keys)\nresult2 = [...new Set(result2)];\ntables(\"data_table2\",summary_data,result2,false)<\/code><\/pre>\n<\/div>\nThe result should be as follows:<\/p>\n
<\/a><\/p>\nNow, we would want to see the actual distributions for each of the columns in the data in the form of histogram and a box plot. Again for both plots we will need to write helper functions, which are provided in the source files. After writing those helper functions, we can call them directly in our main javascript file.<\/p>\n
\n## WindSpeed\nhistogram(\"my_dataviz\",obj['WIND'],'Average Wind Speed')\nboxplot(\"my_dataviz\",obj['WIND'],\"Wind Speed\")\n\n## Precipitation\nhistogram(\"my_dataviz2\",obj['RAIN'],'Precipitation (Rain)',\"orange\")\nboxplot(\"my_dataviz2\",obj['RAIN'],\"Precipitation (Rain)\",'brown')\n\n## Max Temperature\nhistogram(\"my_dataviz3\",obj['T.MAX'],'Max Temperature',\"green\")\nboxplot(\"my_dataviz3\",obj['T.MAX'],\"Max Temperature\",'yellow')\n\n## Min Temperature\nhistogram(\"my_dataviz4\",obj['T.MIN'],'Min Temperature',\"purple\")\nboxplot(\"my_dataviz4\",obj['T.MIN'],\"Min Temperature\",'pink')\n\n## Minimum Grass Temperature\nhistogram(\"my_dataviz5\",obj['T.MIN.G'],'Minimum Grass Temperature',\"cyan\")\nboxplot(\"my_dataviz5\",obj['T.MIN.G'],\"Minimum Grass Temperature\",'orange')\n\n## Indicator Variable\nhistogram(\"my_dataviz6\",obj['IND'],'Indicator')\n\n## Indicator Variable 1\nhistogram(\"my_dataviz7\",obj['IND.1'],'Indicator 1',\"orange\")\n\n## Indicator Variable 2\nhistogram(\"my_dataviz8\",obj['IND.2'],'Indicator 2',\"green\")<\/code><\/pre>\n<\/div>\nIn each of these plotting functions, the first argument is the ID of the html element in which we want to draw the plot, then the array of the column of the data we want to plot, then a title and a CSS-compatible color code. We can visualize the results by opening the html file from the web server.<\/p>\n
<\/a><\/p>\n<\/a><\/p>\nWe’ve had a look at plots for distributions, but since the data is a timeseries, we should also plot its columns against time to visualize the trends and patterns in the data. So now we are going to make a line plot of each of the variables in the dataset. The lineplot()<\/code> function was written by us and is included in the source files.<\/p>\n\nlineplot(\"my_dataviz9\",data,obj,'DATE','WIND','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','RAIN','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MAX','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MIN','#4169e1')\nlineplot(\"my_dataviz9\",data,obj,'DATE','T.MIN.G','#4169e1')<\/code><\/pre>\n<\/div>\nAnd here are the results.<\/p>\n
<\/a><\/p>\nWe can see from the graphs that all variables have periodic pattern as expected from weather data and have averages just as shown by the histograms.<\/p>\n
Lastly, we can plot a correlation matrix to see which columns are correlated with each other, an important thing to look for when making predictions, as we would want to include variables in our model that are correlated with the target variable and exclude variables that are correlated to each other. A simple call to correllogram() function with the data as an argument will give us the correlation matrix. correllogram() comes from correl.js file which is provided in the source file.<\/p>\n
\ncorrelogram(data)<\/code><\/pre>\n<\/div>\nAnd here is what the correlation matrix looks like:<\/p>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/05\/correl.png\")
<\/a><\/p>\n