v8 (v7 Rails v1 Django)
Getting Started
Reference Guide
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)
Connect to a Redshift Database
1
node install node-redshift --save
Copied!
Create the database client and set up the credentials variables cf. package documentation: https://www.npmjs.com/package/node-redshift.
1
var Redshift = require('node-redshift');
2
3
var clientCredentials = {
4
host: process.env.REDSHIFT_HOST,
5
port: process.env.REDSHIFT_PORT,
6
database: process.env.REDSHIFT_DATABASE,
7
user: process.env.REDSHIFT_DB_USER,
8
password: process.env.REDSHIFT_DB_PASSWORD,
9
};
10
11
const redshiftClient = new Redshift(clientCredentials);
Copied!
Configure your database credentials in your env variables
Create the Single Value Chart
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
1
const express = require('express');
2
const router = express.Router();
3
const Liana = require('forest-express');
4
5
...
6
7
router.post('/stats/nb-users', Liana.ensureAuthenticated, async (request, response) => {
8
9
const query = `
10
SELECT count(*) as nb
11
FROM users
12
`;
13
14
const data = await redshiftClient.query(query);
15
16
let json = new Liana.StatSerializer({
17
value: data.rows[0].nb
18
}).perform();
19
20
response.send(json);
21
22
});
Copied!
Create the Leaderboard Chart
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
1
const express = require('express');
2
const router = express.Router();
3
const Liana = require('forest-express');
4
5
...
6
7
router.post('/stats/top-5-buyers', Liana.ensureAuthenticated, async (request, response) => {
8
9
const query = `
10
SELECT firstname || ' ' || lastname AS key, total_quantity AS value
11
FROM (SELECT buyerid, sum(qtysold) total_quantity
12
FROM sales
13
GROUP BY buyerid
14
ORDER BY total_quantity desc limit 5) Q, users
15
WHERE Q.buyerid = userid
16
ORDER BY Q.total_quantity desc
17
`;
18
19
const data = await redshiftClient.query(query);
20
21
let leaderboard = data.rows;
22
let json = new Liana.StatSerializer({
23
value: leaderboard
24
}).perform();
25
26
response.send(json);
27
28
});
Copied!
Result
Last modified 3mo ago