Using Google Fusion Tables as a web application database

Databases are a great way to store data that’s used for visualizations, web apps, or any web-based program where you a) need to update the data on a quasi-regular basis and/or b) you need to be able to show only a subset of all the data you have. The trouble is, setting up an entire database system costs money and requires security, both at levels that may be overkill for individuals and small publications. Google Fusion Tables (through its API provides many of the features of a database, but without the setup and security costs. Even better, the main interface to your data is essentially a spreadsheet, meaning that it can be edited and updated by anyone, not just people who feel comfortable with coding and data formats.

This post is a basic walk-through of how to use Google Fusion Tables as a database for your visualization/webapp. For step-by-step videos of essentially this process, you can check out these videos.

Start with the documentation

FusionTablesAPISearch

Click on “Getting Started” and then choose “Sending directly from the browser” on the landing page. If this is the first time you’ve used the Fusion Tables API, you’ll need to generate an API key.

To locate/generate your API key, first make sure you are logged in to your Google account, then click here to visit the APIs Console. If this is your first project, start by accepting the Terms of Service.
In the “Services” tab, scroll down and click the switch next to the Fusion Tables API into the “On” position:
GoogleAPIConsoleServicesMenu

Screen shot 2013-04-07 at 11.41.01 AM

Finally, choose “API Access” at the left and you’ll find your key in the “Simple API Access” box. Keep this tab open or copy and save your API key string to a text file (TextEdit or NotePad, not any version of Word &c.):
SimpleAPIKeyScreengrab
Note: Although Simple API Access only allows your key to be used to view information from tables that you’ve chosen to make publicly visible, it’s a good idea to specify the domains from which your API key is allowed to be used. You can do this by clicking “Edit allowed referrers…” at right, and adding the domains you wish to include.

Create your Fusion Table

Fusion Tables supports a range of functions, but at its most basic it is a great way to store, sort, query and visualize tabluar (e.g. spreadsheet) data. Upload a .csv or Excel file, set the table’s visibility to “Anyone with the link” and you can easily retrieve some or all of the information in your spreadsheet in a hand JSON format.

Again making sure you’re logged in to your Google account, go to Drive and click on the “Create” button. If the “Fusion Tables” option doesn’t appear, choose “Connect more apps…” and then search for Fusion Tables.

Screen shot 2013-04-07 at 12.25.35 PM

Upload or link to your spreadsheet and click “Next” using the preview screen to confirm that your data looks as you expect (if doesn’t, click “Back” and fiddle around with the delimiters, checking the preview pane as needed).

On the next pane, Fusion Tables will ask you for all kinds of metadata about your table: what you want to name it, who to attribute it to, even a place to link to the source and give it a longer description. Take 5 minutes and FILL ALL THIS OUT!!! It is definitely in your interest.

Why bother with metadata?
In the world of journalism, data without metadata is useless. If you can’t verify the source, any information is no good to you. But you need to be able to verify your info not just when you write the story/create the visualization/newsapp, you need to be able to verify it later as well. Why? Worst case scenario, your work is called into question by someone, and your editor wants a fact-check on what you published. Best case scenario, your work is wildly successful and your editor wants you to do the same project again, with the most recent data.

Once you click “Finish” you’ll see the default “Rows” view of your data. Note that it gives you a row count at the top, and if you have more than 100, you can use the small arrow buttons to view the additional pages. I won’t cover the Fusion Tables interface in detail here, but will be writing additional posts on particular features/functions of Fusion Tables. For our purposes here, the only other thing we need to do is to update our table’s visibility (assuming that you ultimately want to publish the results somewhere on the internet and have anyone be able to see them), and make a note of our table’s ID.

To update the visibility
, click on the “Share” button in the top right corner and under “Who has access” click the “Change” link to the right of Private (all tables are private by default):Screen shot 2013-04-07 at 12.59.47 PM
Select either the “Anyone with a link” or the “Public” radio button, and then “Save”.

To get your table ID, go to File >> About this table, and then copy and paste the string of letters and numbers that appears next to “Id”. Save this into the same text file where you put your API key earlier.

Retrieve your data

The Google documentation on this is pretty good, but here’s a quick breakdown of what the URL is doing:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&key=AIzaSyAm9yWCV7JPCTHCJut8whOjARd7pwROFDQ

Part 1: The API location (sometimes called an “endpoint”):
https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&key=AIzaSyAm9yWCV7JPCTHCJut8whOjARd7pwROFDQ

The first part of the url basically says “go to the part of the Fusion Tables API that accepts queries.” The question mark at this point denotes the start of the so-called “query string” (get it? Query/question/question mark).

Part 2: The query string
The query string in any url is just the part that comes after the question mark (if there is one). The query string is made up of so-called key/value pairs in the format key=value, with each pair separated by an ampersand (&). In the example here, there are two key/value pairs:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&key=AIzaSyAm9yWCV7JPCTHCJut8whOjARd7pwROFDQ

The first is sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4 and the second is key=AIzaSyAm9yWCV7JPCTHCJut8whOjARd7pwROFDQ . The SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4 is a SQL statement that says “give me all rows and columns from the table whose id is..”. The second says “this is my API key.”

SQL (Simply Query Language) is an incredibly powerful tool for selecting, filtering and manipulating data, the scope of which is well beyond this post. However, I will link back with other resources that touch on it as they’re added here.

To try the example above on your own table, copy and paste the URL into your browser address bar, but first replace the table id and API key with your own. If all goes well, you should see some resulting JSON that looks vaguely like this (though what appears in “rows” will differ depending on your data):


{
"kind": "fusiontables#sqlresponse",
"columns": [
"Product",
"Inventory"
],
"rows": [
[
"Amber Bead",
"1251500558"
],
...

I like feedback! Submit questions and comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>