The entire process will be handled by our React.js frontend. This means the user will enter their GridDB WebAPI credentials, upload a CSV file of their choosing, select if that container will be Once the data is in place, you can query to receive the data from your server and then finally visualize it using the To follow along, you will need a GridDB server up and running. You will also need to install the GridDB WebAPI and have that running on your server.<\/p>\n For the frontend, you will need to install react.js, along with the charts library. You can see what the The top of your file will also take all of your imports:<\/p>\n For implementation, we will walk through in the following order: user credentials, creating and send schema\/container, pushing data into the container, querying the data, and then we will finish with showing the data into a chart.<\/p>\n To begin, we will create some simple text fields to allow the user to enter their user and admin credentials. For our blog, we will simply use the GridDB default values of admin\/admin as a proof of concept.<\/p>\n Also, to keep things simple, we will be using the material ui<\/a> React.js framework.<\/p>\n Here you can see that we are handling our textfields with separate functions. We will use React’s To make the proper javascript The First, let’s allow our user to upload a Our So, once the user uploads a When parsing the user’s file, it can be difficult to reliably parse out the row’s datatype. To get around this issue, we will simply open up a modal once the file is uploaded and allow the user to set each column’s datatype. We will use this data to form our object which is used to create the schema with the Web API.<\/p>\n This is the structure that the WebAPI expects:<\/p>\n Once the user enters the column information and clicks the Create Schema button, we will prepare the proper data to send via an HTTP request.<\/p>\n The Here we are making our first Web API HTTP Request. Before we do that, we take all of data we have gathered and form the data structure the API expects. For now, we have hardcoded a The main thing we need to accomplish is to create an object which has a columns array inside with each column name and type inside. Once the data structure is set, you simply Next, once the GridDB server has accepted our HTTP request to create the container and its schema, we can now send over the full The user will be required to press the Similar to the If successful, the amount of columns will be displayed in the modal. If an error, it will also be displayed in the modal. So far, we’ve ingested our For demo purposes, we will also query the GridDB server to re-retrieve our data to visualize it. Querying with the WebAPI is very easy:<\/p>\n For this application, we will have it load after the user enters their credentials and hits the QUERY button which fires off the The empty array in the The above function not only queried the data, but it also managed to transform the data into a more usable form.<\/p>\n To get the data that For this demo, we will be using the kaggle cereals dataset<\/a>. With the data loaded in from the function above, we can place all the data onto a line chart, but I think a more interesting usecase would be showing one individual cereal’s macro and micro nutrients onto a bar chart. To do so, we will create a dropdown list with every single cereal listed. When a user picks a cereal, the app will find the corresponding data and display it on the chart<\/p>\n When the user selects a cereal, our Which will set the React state for choice with the cereal name. When the app detects that change, it will fire off the following function:<\/p>\n This function will use the javascript array find method which locate the proper data from the full dataset which is saved in our React state as For this chart, you can stack up the different micro\/macro nutrients however you like with different colors. For this demo, I did not set anything other than setting calories on its own bar so that we can see those. Here is the chart for Cinnamon Toast Crunch<\/p>\nCOLLECTION<\/code> or TIMESERIES<\/code> and then create the proper schema onto GridDB. Once the container is created, the entirety of the .csv<\/code> file will be uploaded onto the database.<\/p>\nrecharts<\/code> library<\/a>.<\/p>\nPrerequisites<\/h2>\n
package.json<\/code> file looks like here:<\/p>\n{\n \"name\": \"griddb-charts\",\n \"version\": \"0.1.0\",\n \"private\": true,\n \"dependencies\": {\n \"@emotion\/react\": \"^11.8.2\",\n \"@emotion\/styled\": \"^11.8.1\",\n \"@mui\/material\": \"^5.5.0\",\n \"@testing-library\/jest-dom\": \"^5.16.2\",\n \"@testing-library\/react\": \"^12.1.4\",\n \"@testing-library\/user-event\": \"^13.5.0\",\n \"axios\": \"^0.26.1\",\n \"react\": \"^17.0.2\",\n \"react-dom\": \"^17.0.2\",\n \"react-scripts\": \"5.0.0\",\n \"recharts\": \"^2.1.9\",\n \"web-vitals\": \"^2.1.4\"\n },\n \"scripts\": {\n \"start\": \"react-scripts start\",\n \"build\": \"react-scripts build\",\n \"test\": \"react-scripts test\",\n \"eject\": \"react-scripts eject\"\n },\n \"eslintConfig\": {\n \"extends\": [\n \"react-app\",\n \"react-app\/jest\"\n ]\n },\n \"browserslist\": {\n \"production\": [\n \">0.2%\",\n \"not dead\",\n \"not op_mini all\"\n ],\n \"development\": [\n \"last 1 chrome version\",\n \"last 1 firefox version\",\n \"last 1 safari version\"\n ]\n }\n}<\/code><\/pre>\n<\/div>\nimport React, { useState, useEffect } from 'react';\nimport '.\/App.css';\nimport { usePapaParse } from 'react-papaparse';\n\nimport {\n BarChart,\n Bar,\n XAxis,\n YAxis,\n Tooltip,\n Legend\n} from \"recharts\";\nimport Box from '@mui\/material\/Box';\nimport InputLabel from '@mui\/material\/InputLabel';\nimport MenuItem from '@mui\/material\/MenuItem';\nimport FormControl from '@mui\/material\/FormControl';\nimport Select from '@mui\/material\/Select';\nimport Container from '@mui\/material\/Container';\nimport TextField from '@mui\/material\/TextField';\nimport Button from '@mui\/material\/Button';\nimport Dialog from '@mui\/material\/Dialog';\nimport DialogActions from '@mui\/material\/DialogActions';\nimport DialogContent from '@mui\/material\/DialogContent';\nimport FormHelperText from \"@mui\/material\/FormHelperText\";\nimport Grid from '@mui\/material\/Grid'<\/code><\/pre>\n<\/div>\nETL<\/h2>\n
Credentials<\/h3>\n
<TextField\n id=\"user\"\n label=\"Username\"\n variant=\"standard\"\n onChange={handleUser}\n required={true}\n \/>\n <TextField\n id=\"standard-basic\"\n label=\"Password\"\n variant=\"standard\"\n onChange={handlePass}\n required={true}\n \/><\/code><\/pre>\n<\/div>\nuseState<\/code> to handle the changes and save the user’s input. To keep it simple, we will not have any sort of checking — we will simply save every input into the state<\/p>\n const [user, setUser] = useState('')\n const [pass, setPass] = useState('')\n\n const handleUser = (event) => {\n let val = event.target.value\n setUser(val)\n }\n\n const handlePass = (event) => {\n let val = event.target.value\n setPass(val)\n }<\/code><\/pre>\n<\/div>\nfetch<\/code> requests, we will also need to transform the user’s credentials into base64. We can do so like so:<\/p>\n const [encodedUserPass, setEncodedUserPass] = useState('')\n\n useEffect(() => {\n let encodedUserPass = btoa(user + \":\" + pass)\n setEncodedUserPass(encodedUserPass)\n }, [pass])<\/code><\/pre>\n<\/div>\n[pass]<\/code> after the function callback for the useEffect<\/code> just means we will update this specific function when the pass<\/code> state variable is updated. Not the most elegant solution, but it works for our purposes.<\/p>\nUploading a CSV File<\/h3>\n
.csv<\/code> file. This is very easily accomplished using HTML.<\/p>\n<label >Choose a CSV File to Upload:<\/label>\n\n <input type=\"file\"\n id=\"csvFile\" name=\"file\" onChange={fileHandler}\n accept=\".csv\"><\/input>\n <\/code><\/pre>\n<\/div>\nfileHandler<\/code> function will utilize the papa-parse<\/code> library to read in the file and parse the contents and set some React states for use later.<\/p>\nimport { usePapaParse } from 'react-papaparse';\n const { readString } = usePapaParse();\n\n const [fullCsvData, setFullCsvData] = useState(null);\n const [fileName, setFileName] = useState('');\n const [selectedFile, setSelectedFile] = useState(null);\n\n const fileHandler = (event) => {\n const file = event.target.files[0]\n let name = file.name\n let x = name.substring(0, name.indexOf('.')); \/\/remove the .csv file extension\n const reader = new FileReader();\n reader.addEventListener('load', (event) => {\n var data = event.target.result\n\n readString(data, {\n worker: true,\n complete: (results) => {\n setFileName(x)\n console.log(\"selected file: \", results.data[0])\n setFullCsvData(results.data)\n setSelectedFile(results.data[0]);\n setOpen(true)\n },\n });\n });\n\n reader.readAsText(file);\n\n };<\/code><\/pre>\n<\/div>\n.csv<\/code> file, the fileHandler<\/code> function will fire off, taking in the file as the event<\/code>. It will then read the file’s contents and then parse everything. Once parsed, we will set various React states: the file name, the entirety of the csv contents, and the first array of data which corresponds to the column names.<\/p>\nSetting the Column Types<\/h3>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/03\/column_types.png\")
<\/a><\/p>\n'{\n \"columns\": [\n {\n \"name\": \"timestamp\",\n \"type\": \"TIMESTAMP\"\n },\n {\n \"name\": \"name\",\n \"type\": \"STRING\"\n },\n {\n \"name\": \"value\",\n \"type\": \"FLOAT\"\n }\n ],\n \"container_name\": \"test\",\n \"container_type\": \"TIME_SERIES\",\n \"rowkey\": true\n}'<\/code><\/pre>\n<\/div>\n<Button onClick={handleSchema} variant=\"contained\">Create Schema<\/Button><\/code><\/pre>\n<\/div>\nhandleSchema<\/code> function simply calls our putSchema<\/code> function<\/p>\nGridDB WebAPI HTTP Request (Put Schema\/Container)<\/h3>\n
const handleSchema = () => {\n if (Object.keys(chartColumns).length !== 0) { \/\/chartColumns is an array of the column names from the csv\n putSchema(chartColumns)\n }\n }\n\n const putSchema = (obj) => {\n\n let data = new Object();\n data.columns = [];\n let n = Object.keys(obj).length\n let i = 0\n for (const property in obj) {\n if (i < n) {\n data.columns[i] = { \"name\": property, \"type\": (obj[property]).toUpperCase() }\n i++\n }\n }\n data[\"container_name\"] = fileName \/\/ grabbed from the react State \n data[\"container_type\"] = \"COLLECTION\" \/\/ hardcoded for now\n data[\"rowkey\"] = \"true\"\n\n let raw = JSON.stringify(data);\n\n let myHeaders = new Headers();\n myHeaders.append(\"Content-type\", \"application\/json\")\n myHeaders.append(\"Authorization\", \"Basic \" + encodedUserPass);\n\n let requestOptions = {\n method: 'POST',\n headers: myHeaders,\n body: raw,\n redirect: 'follow'\n };\n\n fetch(`http:\/\/${ADDRESS}\/griddb\/v2\/defaultCluster\/dbs\/public\/containers`, requestOptions)\n .then(response => response.text())\n .then(result => {\n setChartColumns(\"Successful. Now Push Data\") \/\/ displays where the object was being formed\n console.log(result)\n })\n .catch(error => {\n setChartColumns(error)\n console.log(\"Error: \", error)\n });\n }<\/code><\/pre>\n<\/div>\nCOLLECTION<\/code> container type, but this can be easily be fixed by adding a switch onto the modal.<\/p>\nJSON.stringify<\/code> it and send it in the body of the request, along with the requestOptions<\/code>. The last thing to note is that once the request is made, if there is an error, it will be shown in the modal. If successful, a simple message will appear, prompting the user to now push the data.<\/p>\nGridDB WebAPI Pushing CSV Data to Container<\/h3>\n
.csv<\/code> data to our server via an HTTP request.<\/p>\nPUSH DATA<\/code> button which will fire off the putData<\/code> function.<\/p>\n const handlePushingData = () => {\n if (fullCsvData !== null) {\n putData(fullCsvData)\n }\n }\n\nconst putData = (data) => {\n\n data.shift();\n let removeEmpties = data.filter(ele => ele.length > 1)\n console.log(\"data: \", removeEmpties)\n\n let raw = JSON.stringify(removeEmpties)\n console.log(raw)\n\n let myHeaders = new Headers();\n myHeaders.append(\"Content-type\", \"application\/json\")\n myHeaders.append(\"Authorization\", \"Basic \" + encodedUserPass);\n\n let requestOptions = {\n method: 'PUT',\n headers: myHeaders,\n body: raw,\n redirect: 'follow'\n };\n\n fetch(`http:\/\/${ADDRESS}\/griddb\/v2\/defaultCluster\/dbs\/public\/containers\/${fileName}\/rows`, requestOptions)\n .then(response => response.text())\n .then(result => setChartColumns(\"Successful: \", result))\n .catch(error => {\n setChartColumns(error)\n console.log(\"Error: \", error)\n });\n }<\/code><\/pre>\n<\/div>\nputSchema<\/code> function, we grab the entire contents of our data from our React’s state and then do some basic massaging to get the proper data to send. First, we get rid of the first element of the array as this one is simply the column names. We then get rid of any empty elements (if any). Everything else should be self-explanatory — the only caveat being that the HTTP Request method for this one is a PUT<\/code> (vs. a POST<\/code>).<\/p>\n.csv<\/code> data into our server. Next up is querying and displaying.<\/p>\nQuerying<\/h3>\n
curl -X POST --basic -u admin:admin -H \"Content-type:application\/json\" http:\/\/127.0.0.1:8080\/griddb\/v2\/defaultCluster\/dbs\/public\/containers\/test\/rows -d '{\"limit\":1000}' <\/code><\/pre>\n<\/div>\nhandleSubmitCreds<\/code> function. The server will respond with the full data of your query; we will then grab that data and transform it into something that recharts<\/code> can display.<\/p>\n const handleSubmitCreds = () => {\n let raw = JSON.stringify({\n \"limit\": 100\n });\n\n let myHeaders = new Headers();\n myHeaders.append(\"Content-type\", \"application\/json\")\n myHeaders.append(\"Authorization\", \"Basic \" + encodedUserPass);\n\n let requestOptions = {\n method: 'POST',\n headers: myHeaders,\n body: raw,\n redirect: 'follow'\n };\n\n fetch(`http:\/\/${ADDRESS}\/griddb\/v2\/defaultCluster\/dbs\/public\/containers\/CEREAL\/rows`, requestOptions)\n .then(response => response.text())\n .then(result => {\n let resp = JSON.parse(result)\n let rows = resp.rows\n\n let c = resp.columns\n let columns = [];\n c.forEach(val => columns.push(val.name))\n\n let map = new Map();\n let fullChartData = [];\n \/\/ transform data into more usable obj\n for (let i = 0; i < 72; i++) { \/\/hard coding the length of rows (72)\n for (let j = 0; j < 16; j++) { \/\/ hard coding length of columns (16)\n map.set(columns[j], rows[i][j])\n }\n const obj = Object.fromEntries(map);\n fullChartData.push(obj)\n }\n setData(fullChartData)\n\n })\n .catch(error => console.log('error', error));\n }<\/code><\/pre>\n<\/div>\nuseEffect<\/code> just tells our React to only run this function on page load, not on every re-render.<\/p>\nVisualizing Data<\/h2>\n
recharts<\/code> expects, we will use the Javascript map object<\/a> to set key:value data pairs and then create an object out of that map using the Object.fromEntries<\/code>.<\/p>\n <FormControl fullWidth>\n <InputLabel id=\"demo-simple-select-label\">Cereal<\/InputLabel>\n <Select labelId=\"demo-simple-select-label\" id=\"demo-simple-select\" value={choice} label=\"Cereal\" onChange={handleChange}>\n <MenuItem value={\"100% Bran\"}>100% Bran<\/MenuItem>\n <MenuItem value={\"100% Natural Bran\"}>100% Natural Bran<\/MenuItem>\n <MenuItem value={\"All-Bran\"}>All Bran<\/MenuItem>\n <MenuItem value={\"All-Bran with Extra Fiber\"}> All-Bran with Extra Fiber<\/MenuItem>\n <MenuItem value={\"Almond Delight\"}>Almond Delight<\/MenuItem><\/code><\/pre>\n<\/div>\nhandleChange<\/code> function will fire off:<\/p>\n const handleChange = (event) => {\n let val = event.target.value\n console.log(\"val: \", val)\n setChoice(val);\n };<\/code><\/pre>\n<\/div>\n useEffect(() => {\n if (data !== null) {\n let userChoice = data.find(val => val.NAME == choice)\n setDisplayedData(userChoice);\n } else console.log(\"data still null\")\n }, [choice])<\/code><\/pre>\n<\/div>\ndata<\/code>. Once it finds this data, the bar chart will display the proper data (displayedData<\/code>):<\/p>\n<BarChart\n width={1500}\n height={500}\n data={[displayedData]}\n margin={{\n top: 5,\n right: 30,\n left: 20,\n bottom: 5,\n }}\n >\n <XAxis dataKey=\"NAME\" \/>\n <YAxis \/>\n <Tooltip \/>\n <Legend \/>\n <Bar type=\"monotone\" stackId=\"a\" dataKey=\"MANUFACTURER\" fill=\"#FFF\" \/>\n <Bar type=\"monotone\" stackId=\"a\" dataKey=\"TYPE\" fill=\"#FFF\" \/>\n <Bar type=\"monotone\" stackId=\"b\" dataKey=\"CALORIES\" fill=\"red\" \/>\n <Bar type=\"monotone\" stackId=\"c\" dataKey=\"PROTEIN\" fill=\"pink\" \/>\n <Bar type=\"monotone\" stackId=\"c\" dataKey=\"FAT\" fill=\"orange\" \/>\n <Bar type=\"monotone\" stackId=\"d\" dataKey=\"SODIUM\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"d\" dataKey=\"FIBER\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"c\" dataKey=\"CARBO\" fill=\"purple\" \/>\n <Bar type=\"monotone\" stackId=\"e\" dataKey=\"SUGARS\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"e\" dataKey=\"POTASS\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"f\" dataKey=\"VITAMINS\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"f\" dataKey=\"SHELF\" fill=\"#82ca9d\" \/>\n <Bar type=\"monotone\" stackId=\"f\" dataKey=\"WEIGHT\" fill=\"#82ca9d\" \/>\n <\/BarChart> <\/code><\/pre>\n<\/div>\n
![\"\"](\"https:\/\/griddb.net\/wp-content\/uploads\/2022\/03\/basic_bar_chart.png\")
<\/a><\/p>\n