Sharing Query Results with Dataclips
Last updated 09 July 2019
Table of Contents
Heroku Dataclips enable you to create SQL queries for your Heroku Postgres databases and share the results with colleagues, third-party tools, and the public. Recipients of a dataclip can view the data in their browser and also download it in JSON and CSV formats.
Dataclips cannot connect to Shield databases.
Visit data.heroku.com/dataclips and click New Dataclip:
Specify a title for your dataclip and a Heroku Postgres database to run it against. Type your SQL query into the main text area like so:
Click Save & Run. The query’s results appear in a table below the query.
The Schema Explorer
The Schema Explorer displays table and column information for your dataclip’s associated database, including the data type for each column:
For larger schemas, you can filter the information displayed by typing in the text box at the top of the Schema Explorer.
The Schema Explorer automatically updates to reflect the schema of whichever database is currently selected for the dataclip.
The query editor offers autocompletion suggestions as you type. These suggestions are based on the schema of the selected database, as well as the syntax and functions of the Postgres SQL dialect:
Whenever you save a dataclip with a valid query, the query is executed and its results are displayed in a table below the query:
If your dataclip’s results include both a column of date or timestamp data and a column of numeric data, it might be possible to display the results as a chart. The toggle above the results table activates when this is possible:
Chart display is possible when all of the following are true about the dataclip’s results:
- There are two or more columns.
- For timeseries data: One of the columns is a date or timestamp and another is numeric.
- For categorical data: One of the columns is a category (string) and the another is numeric.
- There are between 10 and 1,000 rows.
Dataclip access and visibility
By default, a dataclip’s status is
published dataclip can be accessed and edited by all Heroku users who are collaborators on whichever app owns the dataclip’s associated database. You can view an app’s collaborators via either the Heroku CLI or the Heroku Dashboard.
In all cases, users must have access to the default credential for the database.
A Heroku Postgres database can be attached to a Heroku app that doesn’t own it. To view dataclips for a particular database, a Heroku user must have necessary permissions for the app that owns the database. It is insufficient to have permissions for an app that the database is only attached to.
The draft status
If you don’t want collaborators to have access to one of your dataclips, you can change its status from
draft. This is useful if you aren’t ready to share the dataclip’s results with colleagues, or if you’re creating a dataclip only for your own personal reference.
Sharing dataclip results
You can share a dataclip’s results with specific people, teams, or the general public. Sharing results this way does not expose the dataclip’s query, or any other information about the underlying application (such as the app name or database details).
After you create a dataclip you want to share, click the Share button:
The sharing flyout appears:
Sharing with the public
To share a dataclip with the public, click Create Public URL to generate a unique URL:
An authenticated Heroku user session is not required to access this URL.
The public sharing URL differs from the URL you use to access the dataclip as a collaborator. Make sure to copy the URL from the text box in the sharing flyout, not from your browser’s address bar.
To revoke an existing public URL, click Revoke Public URL. This revokes access for all visitors, including anyone who’s visited the URL in the past.
To replace an existing public URL, simply revoke it and recreate it from the sharing flyout. A new URL is generated each time.
Sharing with individuals and teams
You can share dataclip results with individual Heroku users, along with Heroku Teams that you belong to. Click the Add button in the sharing flyout to display the following form:
To share with an individual, specify their email address and click Add.
You can share dataclip results with any email address. However, that email address must be associated with a Heroku account for the recipient to be able to access the dataclip’s results.
To share with a Heroku Team that you belong to, begin typing the team’s name and select it from the dropdown options that appear:
All members of the selected team can view the dataclip’s results.
To revoke sharing access, click the Delete button next to whichever individual or team you want to revoke access for:
Downloading and exporting dataclip results
Dataclip results can be downloaded in CSV and JSON formats. They can also be embedded in Google Sheets with a supplied macro.
Start by clicking Download/Export above the dataclip’s results table:
A dialog appears that displays the various options for downloading and embedding the results:
To download the results to your local machine as a CSV or JSON file, click the corresponding button in the dialog’s Download Results section.
To enable a third-party application to consume the results, copy either the the CSV or JSON URL (whichever format the third-party application expects). These URLs contain an access token that permits access to the dataclip results without needing to authenticate.
Keep these URLs secret. Anyone with the access token they contain can view your dataclip’s results without authenticating.
If you think that your dataclip’s access token might have been compromised, you can click Refresh Access Token to invalidate the existing token and generate a new one. Note that all URLs with the old access token will immediately stop working.
Google Sheets integration
Importing the results of a Dataclip into a Google Sheets spreadsheet is an excellent approach to further refinement of data for presentation, or to synthesize the output of multiple Dataclips into dashboards and other artifacts.
To embed a dataclip’s results into a Google Sheets spreadsheet, paste the
=IMPORTDATA(...) text from the Download/Export dialog into cell
A1 of the spreadsheet. The
=IMPORTDATA(...) string contains the same access token as the dataclip’s CSV and JSON URLs.
Google Sheets does not hide the contents of the
=IMPORTDATA(...) function from viewers. Anyone who can see the spreadsheet is able to copy and paste the CSV URL from the
=IMPORTDATA(...) string, including the secret access token. You must restrict access to the Google Sheet to ensure that only trusted parties can open it.
Dataclip execution and data freshness
There is an important distinction between dataclip execution — the moment when a query is run against your database — and viewing results.
Whenever a dataclip is viewed or downloaded, Heroku executes its query if it hasn’t done so in the last sixty seconds. However, the dataclip does not wait for the new query execution to complete. Instead, it immediately returns the results of its most recently completed execution.
As long as a dataclip has been viewed or downloaded at least once in the past seven days, Heroku automatically executes its query and updates its results about once per hour whether or not the results are viewed or downloaded. After seven days of inactivity, Heroku doesn’t execute the query at all until the dataclip is accessed again.
If you keep a dataclip open in your browser, its query is executed every one to two minutes. When a new execution completes, a banner appears on the page to indicate that updated results are available.
When a database is migrated to a different instance of Heroku Postgres (for example, as a result of upgrading), the dataclips associated with the old database become orphaned and require recovery. These dataclips are shown with the following notice in red in your dataclip list view:
To attach an orphaned dataclip to a new database, visit the dataclip’s detail page:
Select a new database from the dropdown menu that says
Select a datastore, then click Save & Run. The dataclip will attach to the specified database and execute your query.
If you need to recover a large number of dataclips, please open a support ticket via help.heroku.com.
Limits and restrictions
- Dataclips can be created for any Heroku Postgres databases except Shield Tier plans.
- A dataclip can return up to 100,000 rows.
- A dataclip’s results can be at most 104,857,600 bytes in size.
- Dataclip results that are larger than 26,214,400 bytes in size are downloadable, but you cannot explore the results in your browser.
- Dataclip queries time out after ten minutes.
- You can’t share dataclip results with Heroku Teams that you don’t belong to.
- Unauthenticated users retrieving results from the
.jsonendpoints are limited to 30 requests per minute per IP.