Simple data sharing with Data Clips
February 14, 2012 by Matthew Soldo
Data clips are available today in beta as a standard feature on all Heroku Postgres databases.
When we share information on the Internet, we do so by sharing URLs. We send URLs for locations, books, videos, and even source code. Until now there hasn't been a convenient way to share data inside a database. That's why we're introducing Data Clips. They are a fast and easy way to unlock the data in your database in the form of a secure URL.
Data Clips allow the results of SQL queries on a Heroku Postgres database to be easily shared. Simply create a query on postgres.heroku.com, and then share the resulting URL with co-workers, colleagues, or the world. Data clips can be shared through e-mail, Twitter, irc, or any other medium - they are just URLs. The recipients of a data clip are able to view the data in their browser or download it in JSON, CSV, XML, or Microsoft Excel formats.
Data changes rapidly in databases. We've created Data Clips with this in mind. They can either be locked to a point in time or set to refresh with live data. When locked to a point in time, data clips are guaranteed to show an unchanging snapshot of data, even if they are viewed hours, days, or years after the clip was created. Alternatively, when data clips are set to "Now", they provide a live view into the database.
To illustrate how data clips work, we created a database containing all of the people with Wikipedia articles (english articles only). This data comes via dbpedia.org (an excellent project which captures the "structured" information from Wikipedia). It is a modest, 4.6 GB database. The
People table contains 800,000 rows:
=> \d people Table "public.people" Column | Type | Modifiers -------------+-------------------+----------- id | integer | not null url | character varying | name | character varying | given_name | character varying | sur_name | character varying | birth_date | date | death_date | date | description | character varying |
Here are data clips of all of the people whose description contains the words scientist, and artist. We can easily use the power of SQL to summarize this data, grouping and counting each by the century that they were born. We could also create a data clip to show how the average lifespan of wikipedia personalities has changed over time (this clip also exposes some errors in the source data). As you can see, data clips are as expressive as SQL itself.
- Data Clips are immediately available in beta on all Heroku Postgres dedicated databases.
- Data Clips utilize the excellent Ace Code Editor for SQL entry.
- To reduce the risk of altering your data, Data Clips query databases within a read-only transaction.
- Results are limited to 10,000 rows.
- To limit the load on your database, Data Clips refresh a maximum of once every 60 seconds.