Create Charts with AWS Redshift
This example shows you how to create a graph based on AWS Redshift.
This could be useful if you want to avoid making graphs directly from your production database.
We'll create 2 charts:
- 1.Number of users (single value chart)
- 2.Top 5 buyers (leaderboard chart)
node install node-redshift --save
Create the database client and set up the credentials variables cf. package documentation: https://www.npmjs.com/package/node-redshift.
var Redshift = require('node-redshift');
var clientCredentials = {
host: process.env.REDSHIFT_HOST,
port: process.env.REDSHIFT_PORT,
database: process.env.REDSHIFT_DATABASE,
user: process.env.REDSHIFT_DB_USER,
password: process.env.REDSHIFT_DB_PASSWORD,
};
const redshiftClient = new Redshift(clientCredentials);
Configure your database credentials in your env variables
Step 1 - Create a Single Value Smart Chart in the Forest Admin Project Dashboard.
Step 2 - Create the route to handle the Smart Chart
routes/dashboard.js
const express = require('express');
const router = express.Router();
const Liana = require('forest-express');
...
router.post('/stats/nb-users', Liana.ensureAuthenticated, async (request, response) => {
const query = `
SELECT count(*) as nb
FROM users
`;
const data = await redshiftClient.query(query);
let json = new Liana.StatSerializer({
value: data.rows[0].nb
}).perform();
response.send(json);
});
Step 1 - Create a Leaderboard Smart Chart in the Forest Admin Project Dashboard.
Step 2 - Create the route to handle the Smart Chart
routes/dashboard.js
const express = require('express');
const router = express.Router();
const Liana = require('forest-express');
...
router.post('/stats/top-5-buyers', Liana.ensureAuthenticated, async (request, response) => {
const query = `
SELECT firstname || ' ' || lastname AS key, total_quantity AS value
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 5) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc
`;
const data = await redshiftClient.query(query);
let leaderboard = data.rows;
let json = new Liana.StatSerializer({
value: leaderboard
}).perform();
response.send(json);
});
Last modified 4mo ago