Moving data from Heroku Postgres to Google Sheets

By Matthew Rathbone on December 06, 2016

postgres analytics database heroku google sheets psql

This article was co-authored by Anton Antonenko

abstract city lights 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).

Set Up

<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:

  1. Export data from Postgres to a file
  2. Upload that file to Google Sheets
  3. Make visualizations
  4. 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!

SQL table

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.

Charts

Hacky Automation

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:

/etc/init.d/crond start

Then, set up your scheduled task. We’ll need to create a cron configuration file:

vim /etc/cron.d/analytics

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:

Import options

Wrap Up

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.

Still Writing SQL in a Terminal?

Beekeeper is a free and full featured SQL IDE with query autocomplete, error detection, dataset previews, and result visualization. We support SparkSQL, Hive, Postgres and more!

Create your account »

Matthew Rathbone bio photo

Matthew Rathbone

CEO of Beekeeper Data. British. Data nerd.

Big Data Blog Email Twitter Github Stackoverflow