Learning Heroku Postgres (2015)
Chapter 5. Dataclips
Dataclips are web tool provided by Heroku that allows you to share the results of your queries in a Postgres database in a simple way. You can share the URL of the results with anyone, and they can view or download the results in JSON, CSV, or Microsoft Excel formats.
Heroku dataclips also provide an API end point, which is an amazing feature that quickly creates prototyping APIs. Another use case of dataclips is when you use them for decision-making in your business. With dataclips, it is very easy for someone to share an important business query with you, and you are able to edit and work together on this query or just download the query data. It's a great feature to create dashboards in your applications.
This chapter covers the following topics:
· Creating dataclips
· Sharing dataclips
· Interacting with dataclips
· Dataclips security
· Limitations and additional settings
Creating dataclips
In order to create a dataclip, it is necessary to access https://dataclips.heroku.com; this provides a web tool that allows you to create and manage your dataclips.
The Heroku dataclips web tool
To create a new dataclip, you should follow these steps:
1. First, click on the Create Dataclip button.
2. Then, on the next screen, enter the name of your dataclip, choose the database, and type the SQL query that returns the desired results.
3. Finally, click on the Create Dataclip button.
Tip
You can use the SELECT * FROM pg_catalog.pg_tables ORDER BY tablename ASC LIMIT 5 query if you don't have data in your database. This query returns the name of the first five tables of your database in an ascending order.
The following screenshot shows the first five tables of pg_tables:
The first five tables in pg_tables
You can change your SQL query at any time. To do this, just change your query and click on the Update Query button.
Every time you change the query, a new version is automatically created, and you can easily navigate between versions by clicking on the version button and then on the View button in the previous version window.
Dataclips previous versions
Sharing dataclips
You can share your dataclips in many different ways; some of them are mentioned here:
· Using the dataclip access link
· Downloading the dataclip in XLS, CSV, or JSON format
· Sending the dataclip access link via Twitter
· Embedding the dataclip via Google Drive
Click on the Share/Export link and choose the most suitable way for you, which is shown in the following screenshot:
Share dataclips
Interacting with dataclips
The people with whom you share your dataclip can interact with it directly through the access link. This link allows your dataclip to be seen in the web format and also in the CSV, JSON, or XLS formats.
For example, you can visit a dataclip sample at https://dataclips-next.heroku.com/gygjtnncavcxgnrvalmkezjiykvh-Five-first-table-in-pg_tables. The web interface is provided to view the dataclip, and by adding the .csv, .json, or .xls file format, you can view the data in the following formats:
· To view in CSV, go to https://dataclips-next.heroku.com/gygjtnncavcxgnrvalmkezjiykvh-Five-first-table-in-pg_tables.csv
· Viewing in JSON: https://dataclips-next.heroku.com/gygjtnncavcxgnrvalmkezjiykvh-Five-first-table-in-pg_tables.json
· Viewing in XLS: https://dataclips-next.heroku.com/gygjtnncavcxgnrvalmkezjiykvh-Five-first-table-in-pg_tables.xls
If there is more than one available version, you can specify the version by adding the?version= parameter. Here is an example:
https://dataclips-next.heroku.com/gygjtnncavcxgnrvalmkezjiykvh-Five-first-table-in-pg_tables.json?version=2
Tip
The JSON endpoint is useful for prototyping APIs, but it should not be a substitute for a production API. This endpoint also supports Cross-Origin Resource Sharing (CORS) for GET requests.
Data refresh
The data visualization of dataclips is never static; it always displays the updated set of data that corresponds to the query, and you are alerted if there is a new set of data.
If your dataclip is connected with Google Drive, the data is updated on an hourly basis.
Dataclips security
All dataclips are secured through an unguessable URL. Heroku also allows only selected users to view your dataclip; this is done by adding the user e-mail in the dataclip's configuration. This functionality is only available on the Standard tier, Premium tier, or Enterprise tier plan.
In order to set the display only for selected users, you must click on the Settings link. Then, in the Security Permissions section, choose the Only to authorized users option. After that, add the e-mails that you want to allow, and finally, click on the Save Permissions button:
Dataclip available only to authorized users
When someone accesses the URL of your dataclip, they will be informed that the dataclip is protected and that the user is required to log in to view the results:
The user must log in to view the results of the dataclip
Limitations and additional settings
Dataclips have some limitations, which are as follows:
· By default, queries that take longer than 10 minutes are canceled
· The query can return a maximum of 29,999 lines
· You can only add authentication in the Standard tier, Premium tier, or Enterprise tier plan
There are some other settings that you can make in your dataclip by clicking on the Settings link; these settings are as follows:
· Rename Dataclip: This allows you to rename the dataclip.
· Change Database: This allows you to move the dataclip query to another database.
· Remove Dataclip: This allows you to delete the dataclip. This action makes the dataclip inaccessible.
Tip
There is one more feature available in the dataclip dashboard and accessible through the Fork Clip link. This feature allows you to make a copy of dataclip, and it is a useful feature when you want to work on a new version.
Self-test questions
Answer true or false:
1. Can dataclips' queries in Postgres database on Heroku be shared?
2. Can dataclips be displayed in the browser?
3. Is it possible to download a dataclip in the XLS format?
4. To create a dataclip, is it necessary to access https://dataclips.heroku.com?
5. Are dataclips recommended for API prototyping?
6. Can you see the results in the JSON format when adding .json to the dataclip URL?
7. Are dataclips that are shared on Google Drive never updated?
8. Can dataclips return an infinite set of lines?
9. Can you switch the dataclip to another Postgres database at any time?
10. Is fork a nice feature when you want to make a copy of a dataclip?
Summary
In this chapter, you learned how to use the dataclips tool provided by Heroku. This tool allows you to build SQL queries using a Postgres database hosted on Heroku. You also learned that through dataclips, you can share the results with other people, and they can see the results in many different ways, that is, on the web, in the JSON, CSV, or Microsoft Excel (XLS) formats, and you can embed or share them on Google Drive.
You also saw that dataclips are an interesting feature when working on prototyping APIs, but they should not be used in production APIs.
In the next chapter, you will learn about the rollback, followers, and forks features. Besides this, you will also study some use cases.