Ship It: The Fastest Way to Export SQL Data for Your Analysts

This is a premium course, which you can purchase below.

Buy Now

We feel good about the data and we're ready to ship it off! But how? Well that, friends, is the best part! There are multiple ways to do this but by far the simplest way is with a simple shell command using PSQL. You can drop the file anywhere you like - on your desktop so you can email it, or, what's easiest, is to use a secure file sharing system like Dropbox, Google Drive, OneDrive - whatever. Nice and fast.

Our analysts are going to be using Excel to dig into the data so let's setup a simple way to get it to them. We can run a script for this or we can use psql:

psql cassini -c "\copy (select * from enceladus.results_per_flyby) to '//FILESHARE/results_per_flyby.csv' header csv;

Running a SQL script can be easier, and also a bit more flexible. The first command will output the results of our analysis view (rollups by flyby). The second will dump the overall result:

set search_path='enceladus';
copy (select * from enceladus.results_per_flyby)
to '//share/results_per_flyby.csv' header csv;

copy (select
  inms.source,
  chemistry.name as compound,
  chemistry.formula,
  sum(inms.high_sensitivity_count) as sum_high,
  sum(inms.low_sensitivity_count) as sum_low
from flybys
inner join inms on flyby_id = flybys.id
inner join chemistry on chemistry.molecular_weight = inms.mass
group by inms.source, chemistry.name, chemistry.formula)
to '//share/overall_results.csv' header csv;

And that's it!