Image by William
Let’s say you’re hosting an application on Heroku. Let’s also assume that you’ve already connected the PostgreSQL add-on to your project and your DB is full of your app data. Now you want to implement a dashboard to view analytics from your application database.
In this post, I’ll walk you though how to take data from your Heroku Postgres database and load it into Google Sheets (or Microsoft Excel).
<APPNAME> - is a variable which stands for your application name. In my case, its called 'antonsoft'.
Before we move on, let’s make sure that you have the Postgres add-on configured & attached to your project. We can check this with pg:info.
anton$ heroku pg:info --app <APPNAME> === DATABASE_URL Plan: Hobby-dev Status: Available Connections: 0/20 PG Version: 9.5.4 Created: 2016-11-16 00:31 UTC Data Size: 7.2 MB Tables: 1 Rows: 4/10000 (In compliance) Fork/Follow: Unsupported Rollback: Unsupported Region: Europe Add-on: postgresql-vertical-63979
And here is the example table I’ll be using:
anton$ heroku pg:psql --app <APPNAME> -c "select * from heroku_analytics order by value_name;" ---> Connecting to DATABASE_URL value_name | value ------------+------- PARAMETER1 | 100 PARAMETER2 | 200 PARAMETER3 | 300 PARAMETER4 | 401 (4 rows)
So the basic workflow for this is:
- Export data from Postgres to a file
- Upload that file to Google Sheets
- Make visualizations
- Update the data periodically
Exporting data from Postgres
Okay, let’s get to it. We need to export our query results into a file. During export we have to choose a file format. PostgreSQL has three file formats: text (the default), binary, and CSV. Since we’re talking about spreadsheet analytics and CSV has native support in almost all spreadsheet software, we’ll use CSV for our format.
An export can be achieved using postgres COPY from within the postgres shell. The syntax is as follows:
\copy (required select query) to '/path/to/file.csv' DELIMITER ';' CSV HEADER;
In our case the command will look like this:
\copy (select * from heroku_analytics) to '/Users/anton/heroku/analytics.csv' DELIMITER ';' CSV HEADER;
Or you can execute it directly from the command line:
anton$ heroku pg:psql --app <APPNAME> -c "\copy (select * from heroku_analytics) to '/Users/anton/heroku/analytics.csv' DELIMITER ';' CSV HEADER;" ---> Connecting to DATABASE_URL COPY 4
Navigate to the directory you specified earlier and take a look at your new file. Looks like we have a SQL table in a file!
Import Into Google Sheets
Now open a new spreadsheet in Google Sheets and import the analytics.csv file into the new spreadsheet by going to
file -> import. Choose to overwrite the current sheet. Important note: during import, you’ll need to manually set the fields delimiter to “;”, otherwise the columns won’t be recognized as separate (by default CSV uses a comma).
Simple Chart Creation
Select all columns in the sheet by clicking on their headers and go to
Insert -> Chart. Then choose how you want to visualize your chart. You can arrange a series of these charts on a page to create a simple dashboard.
While I’m going to cover more robust automation of this process in a following blog post if you have access to a Linux machine you can at least do some simple automation of the csv generation. In this case, a good solution is adding the SQL COPY command into cron. Doing this quite easy. If you are on Linux you first need to make sure that the cron daemon is running:
Then, set up your scheduled task. We’ll need to create a cron configuration file:
And insert our settings (for example, we want to update our analytics.csv every Saturday at 8:05 a.m.):
# Minute Hour Day of Month Month Day of Week User(linux, not DB user) Command # (0-59) (0-23) (1-31) (1-12 or Jan-Dec) (0-6 or Sun-Sat) 5 8 * * Sat anton heroku pg:psql --app <APPNAME> -c "\copy (select * from heroku_analytics order by value_name) to '/Users/anton/heroku/analytics.csv' DELIMITER ';' CSV HEADER;"
This doesn’t import the file into your Google Sheets, but it does create a snapshotted data dump for you to use when you want to update it.
When importing if you want to overwrite your Google spreadsheet when you import analytics.csv, simply select this option during import:
You now have some basic Heroku application metrics in Google Sheets. This is a great way to piece together visual metrics in a short amount of time. Next time we’ll talk about automatically updating the spreadsheet.