Introducing the Google Fusion Tables API

In this article, I'll be providing you with a quick overview of the Fusion Tables API. Google Fusion Tables is a modern data management and publishing web application that makes it easy to host, manage, collaborate on, visualize, and publish data tables online. We'll go over the API by creating a sample application that interacts with our Fusion Tables. The sample app will use the Google APIs Client Library for Java and the complete source code is available on GitHub.

The Google Fusion Tables has a web frontend, where you can create, view and share Fusion Tables.

Fustion Tables also comes with a Fusion Tables API that allows developers to interact with the Fusion Tables using different programming languages.

We'll go over the Fusion Tables API by creating a sample app (the complete source code for the app is available in the FusionTablesJ2SE GitHub repository.)
The sample app will use the Google APIs Client Library for Java. This is a java client library to access a variety of different Google APIs. Although the API doesn't officially support Google Fusion Tables, it will allow us to

• Setup the authenticated transport.
• Make actual calls to the Fusion Tables API.
• Allow us to plugin a custom CSV Parser for transforming the APIs responses

The Fusion Tables API

According to the Fusion Tables API :

"The Google Fusion Tables API is a set of statements that you can use to search for and retrieve Google Fusion Tables data, insert new data, update existing data, and delete data. The API statements are sent to the Google Fusion Tables server using HTTP GET requests (for queries) and POST requests (for inserts, updates, and deletes) from a Web client application."

So we're basically dealing with a REST API (like most Google APIs). We'll use the Google APIs Client Library for Java to interact with Fusion Tables.

The API allows us to :

• Exploring tables
• Creating a table
• Creating a view
• Querying for data
• Adding rows
• Updating a row
• Deleting rows
• Deleting a table
• Working With Geographic Data

We'll be executing all these calls from our sample application.

All API calls return a response in a CSV format. This means that when selecting records from a Fusion Table, the response will look like this:

description,name,accuracy,timestamp,geometry
the description,the name,30,1305362833553,"3.517819,50.962329,0.0"
the description2,the name2,50,1305362833553,"3.517819,50.962329,0.0"

Working with CSV in a java environment can be cumbersome,as it doesn't really map well to an object oriented structure. Most Google APIs return responses in either the JSON/Atom format, that can be easily mapped to a java object model.

At the time of writing, there is no Parser available in the Google APIs Client Library for Java that will parse the CSV response into a java based model. The API does however allow us to plugin a custom parser for handling these responses.

For this example, I've created a CsvParser that's included in the project source code. The goal of this parser is to allow you to capture the CSV responses from the Fusion Tables API, and transform that into a java based model.

The sample application

The sample JSSE based app interacts with Fusion Tables through the Fusion Tables API. It uses ClientLogin for authentication. (see the authentication section).

The sample application does the following:

• Show the Fusion Tables associated with the user account.
• Create a new Fusion Table
• Show the Fusion Tables associated with the user account. (new table is added)
• Performs a select on the new table (empty)
• Insert a record in the new table.
• Performs a select on the new table (new record is shown).
• Drops the table.

The sample application will output the following:

+++ Begin Show Tables

No tables found

 +++ Create Table

CVS Response
------------
tableid
852154

Table with ID = 852154 created

 +++ Begin Show Tables

CVS Response
------------
table id,name
852154,TEST_TABLE

Found table : TEST_TABLE(852154)

 +++ Select from Tables

CVS Response
------------
description,name,accuracy,timestamp,geometry

No records found in table 852154

 +++ Insert into Tables

CVS Response
------------
rowid
1
2

record inserted : 1
record inserted : 2

 +++ Select from Tables

CVS Response
------------
description,name,accuracy,timestamp,geometry
the description,the name,30,1305580967127,"3.517819,50.962329,0.0"
the description2,the name2,50,1305580967127,"3.517819,50.962329,0.0"

Found record : the name
Found record : the name2

 +++ Drop Tables

Setting up the project

We'll configure the project in Eclipse, and use Maven to define the dependencies. I would suggest installing m2eclipse in order to resolve the dependencies properly, and have your project setup correctly in Eclipse.

If you don't want to install m2eclipse, you'll need to execute the following command from the project folder :

mvn eclipse:clean eclipse:eclipse

The project includes the following pom.xml file, where the com.google.api.client dependencies are defined.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>com.google</groupId>
    <artifactId>google</artifactId>
    <version>5</version>
  </parent>
  <groupId>com.ecs.fusiontables.j2se</groupId>
  <artifactId>fusiontables-j2se</artifactId>
  <version>1.0-SNAPSHOT</version>
  <build>
    <plugins>
      <plugin>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>1.6</source>
          <target>1.6</target>
        </configuration>
      </plugin>
    </plugins>
  </build>
  <dependencies>
   <dependency>
      <groupId>com.google.api.client</groupId>
      <artifactId>google-api-client</artifactId>
      <version>1.4.1-beta</version>
    </dependency>
   <dependency>
      <groupId>com.google.api.client</groupId>
      <artifactId>google-api-client-googleapis</artifactId>
      <version>1.3.2-alpha</version>
    </dependency>  
    <dependency>
      <groupId>org.mortbay.jetty</groupId>
      <artifactId>jetty-embedded</artifactId>
      <version>6.1.24</version>
      <scope>compile</scope>
    </dependency>
  </dependencies>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
</project>

Your project will look like this:



The project includes the following dependencies. If you don't want to use Maven to handle the dependencies, you'll need to ensure that the sample has includes the following dependencies:

• /commons-codec/commons-codec/1.3/commons-codec-1.3.jar
• /commons-logging/commons-logging/1.1.1/commons-logging-1.1.1.jar
• /com/google/api/client/google-api-client/1.3.2-alpha/google-api-client-1.3.2-alpha.jar
• /com/google/api/client/google-api-client-googleapis/1.3.2-alpha/google-api-client-googleapis-1.3.2-alpha.jar
• /com/google/code/gson/gson/1.6/gson-1.6.jar
• /com/google/guava/guava/r08/guava-r08.jar
• /org/apache/httpcomponents/httpclient/4.0.3/httpclient-4.0.3.jar
• /org/apache/httpcomponents/httpcore/4.0.1/httpcore-4.0.1.jar
• /org/codehaus/jackson/jackson-core-asl/1.6.5/jackson-core-asl-1.6.5.jar
• /com/google/code/findbugs/jsr305/1.3.9/jsr305-1.3.9.jar
• /xpp3/xpp3/1.1.4c/xpp3-1.1.4c.jar

Setting up the transport

As all communication towards the Fusion Tables API is done through HTTP, we'll start by setting up our HTTP transport. As we're running in a J2SE environment, we'll use the ApacheHttpTransport.

private static final HttpTransport transport = new ApacheHttpTransport();

We've defined our transport on an abstract FusionTablesCommand base class that will handle the communication with the API. The Google APIs Client Library for Java v!.4.1 (that has gone into beta recently) introduces a new way of handling transports that we'll be using in the sample application.

The Google APIs Client Library for Java introduces a thread-safe light-weight HTTP request factory layer on top of the HTTP transport for initializing requests.

private static HttpTransport transport = new ApacheHttpTransport();

 public static HttpRequestFactory createRequestFactory(
   final HttpTransport transport) {
  
  return transport.createRequestFactory(new HttpRequestInitializer() {
   public void initialize(HttpRequest request) {
    GoogleHeaders headers = new GoogleHeaders();
    headers.setApplicationName("Google-FusionTables/1.0");
    request.headers=headers;
    request.addParser(new CsvParser());
    try {
     authorizeTransport(request);
    } catch (Exception e) {
     e.printStackTrace();
    }
   }
});

The HttpRequestFactory is exposed on our FusionTablesCommand base class. When our application needs to build an HTTP GET or POST request, it will use this HttpRequestFactory to obtain such a request object. The HttpRequestFactory is configured in such a way that

Proper headers are being set on the request.
• A custom CSV parser is but on the request, needed to convert the HTTP response into a java object
• Authorization headers are set on the request for authenticating

Setting up authentication

On of the first things we need to setup is the authenticated transport over HTTP to the Google Fusion Tables endpoint. Fusion Tables supports 2 authentication methods

• ClientLogin
• OAuth

In this sample, we'll be using ClientLogin. The advantage of ClientLogin is that it's very quick and easy to setup. You basically provide a username/password and your HTTP transport will be authenticated. I wouldn't go so far as saying that this is the method you would like for your production code, however keep in mind that this is a very simple sample running on a J2SE environment, and as we're focussing on the Fusion Tables API, and not so much on the various ways of authenticating against the API, ClientLogin is a good fit here.

private void authorizeTransport(HttpRequest request) throws HttpResponseException, IOException {
    // authenticate with ClientLogin
    ClientLogin authenticator = new ClientLogin();
    authenticator.authTokenType = Constants.AUTH_TOKEN_TYPE;
    authenticator.username = Constants.USERNAME;
    authenticator.password = Constants.PASSWORD;
    authenticator.transport = transport;
    try {
     Response response = authenticator.authenticate();
     request.headers.authorization=response.getAuthorizationHeaderValue();
    } catch (HttpResponseException e) {
     e.printStackTrace();
    } catch (IOException e) {
     e.printStackTrace();
    }  
   }

The FusionTablesCommand

For the sample project, we've create an abstract FusionTablesCommand that will execute the Fusion Tables API requests over HTTP. As you can see, we're using the https://www.google.com/fusiontables/api/query endpoint for all of our API requests.
In order to execute a query against the Fusion Tables API, a concrete command needs to provide a SQL statement to be executed. The abstract FusionTablesCommand takes care of handling the transport and exeucting the request.

public abstract class FusionTablesCommand {
 
 protected static final String FUSION_TABLES_API_QUERY = "https://www.google.com/fusiontables/api/query";
 protected static final String SHOW_TABLES = "SHOW TABLES";
 
 private static final HttpTransport transport = new ApacheHttpTransport();
 
 public static final HttpRequestFactory httpRequestFactory = createRequestFactory(transport);
 
 
 private String sql;

 public FusionTablesCommand(String sql) {
  this.sql = sql;

 }

 protected abstract HttpRequest getHttpRequest() throws Exception;

 public DataList execute() throws Exception {
  HttpRequest httpRequest = getHttpRequest();
  httpRequest.url.put("sql", sql);
  return httpRequest.execute().parseAs(DataList.class);
 }

As mentioned in the documentation :

• HTTP GET requests are used for executing queries
• HTTP POST requests are used for inserts, updates, and deletes

As such, we've modelled a command to perform the GET requests :

public FusionTablesGetCommand(String sql) {
  super(sql);
 }

 @Override
 protected HttpRequest getHttpRequest() throws Exception {
  return httpRequestFactory
    .buildGetRequest(new GenericUrl(FUSION_TABLES_API_QUERY));
 }

And a command to execute the POST requests :

package com.ecs.fusiontables.sample.command;

import com.google.api.client.http.GenericUrl;
import com.google.api.client.http.HttpRequest;

public class FusionTablesPostCommand extends FusionTablesCommand {

 public FusionTablesPostCommand(String sql) {
  super(sql);
 }

 @Override
 protected HttpRequest getHttpRequest() throws Exception {
  return httpRequestFactory.buildPostRequest(
    new GenericUrl(FUSION_TABLES_API_QUERY), null);
 }
}

The only difference when comparing the POST command to the GET command is that the PostRequest allows you to specify the HTTP request content. We;re not sending any content besides the URL so we're specifying null here.

Notice how our httpRequestFactory is used here in the 2 concrete classes to create a GET and a POST request. Both are using the standard fusion tables endpoint.

Showing Table information

Now that we have the basic infrastructure setup, and the ability to do POST and GET request, we can start by implementing our first Fusion Tables API call.
The first thing we'll do in our sample is display the tables that are assigned to our user account. This is done using the following code :

FusionTablesCommand showTablesCommand = new FusionTablesGetCommand(transport,SQL_SHOW_TABLES);
  TableInfoList tableList = showTablesCommand.execute(TableInfoList.class);
  List<TableInfo> records = tableList.records;
  if (records.size()==0) {
   System.out.println("No tables found.");
  } else {
   for (TableInfo table : records) {
    System.out.println("Found table : " + table.name + "(" + table.table_id + ")");
   }
  }

Notice how we're using the FusionTablesGetCommand (HTTP GET Request) and how we're passing on a SQL statement (SHOW TABLES).

The SQL statement SHOW TABLES returns all tables associated with our account. If this is the first time you're using Fusion Tables, the resultset will be empty.
If you already have tables associated with your account, the response will look like this :

table id,name
831290,TEST_TABLE
831291,TEST_TABLE2

This CSV response is handled by our Custom CSV Parser, and transformed into the java model below:

package com.ecs.fusiontables.sample.model;

import java.util.List;

import com.google.api.client.util.Key;

public class TableInfoList {

 @Key
 public List records;
 
 public static class TableInfo {

  @Key(value="table id")
  public String table_id;
  
  @Key
  public String name;
 }
}

That way, instead of dealing with the CSV string, we now have a clean datamodel to develop against. The code snippet above just loops over the TableInfo records, and prints out the table id and table name.

Creating a table

private static final String SQL_CREATE_TABLE = "CREATE TABLE 'TEST_TABLE' (description: STRING,name: STRING,accuracy: NUMBER, timestamp: NUMBER, geometry: LOCATION);";
 
  FusionTablesCommand createTableCommand = new FusionTablesPostCommand(SQL_CREATE_TABLE);
  DataList list = createTableCommand.execute();
  System.out.println("Table with ID = " + list.getRecords().get(0).tableid + " created");

The API will respond with the tableId if the table creation was succesfull.

tableid
831290

You can see in the Fusion Tables website that the table was created :

Selecting data

We'll use the following sode snippet to select data from our table. We pass on a SELECT statement, and use our tableId in the FROM clause. (keep in mind that FusionTables allows you to create different tables with the same name. The only unique key is the talbeId

public void selectFromTable(String tableId) throws Exception {
  System.out.println(" +++ Select from Tables");
  FusionTablesCommand getTableCommand = new FusionTablesGetCommand(transport,"SELECT description, name,accuracy,timestamp,geometry FROM "+ tableId);
  TableRecords tableRecords = getTableCommand.execute(TableRecords.class);
  if (tableRecords.records.size()==0) {
   System.out.println("No records found in table " + tableId);
  } else {
   for (TableRecords.TableRecord tableRecord : tableRecords.records) {
    System.out.println("Found record : " + tableRecord.name);
   }
  }
  System.out.println("");
 }

The Fusion Tables API responds to a select by returning the following CSV value (when 2 records are stored in the database). At this point you might be wondering what the geometry column is. The geometry column contains a geographic location (point) identified by a longitude latitude. Fusion Tables allows you to visualize such data on a map. But more on this in a future blog post.

description,name,accuracy,timestamp,geometry
the description,the name,30,1305362833553,"3.517819,50.962329,0.0"
the description2,the name2,50,1305362833553,"3.517819,50.962329,0.0"

Our CsvParser will transform this into a java data model. Notice how we execute the command by specifying a TalbeRecords.class.

TableRecords tableRecords = getTableCommand.execute(TableRecords.class);

For this particular table, we've create a simple java POJO like this :

package com.ecs.fusiontables.sample.model;

import java.util.List;

import com.google.api.client.util.Key;

public class TableRecords {

 @Key
 public List records;

 public static class TableRecord {

  @Key
  public String description;
  
  @Key
  public String name;
  
  @Key
  public String accuracy;
  
  @Key
  public String timestamp;
  
  @Key
  public String geometry;
 }
}

As you can see it exposes a list of TableRecord objects. Each column in our table is mapped to a field in the TableRecord class through the com.google.api.client.util.Key annotation. The custom CsvParser that we created will ensure that the CSV values (representing the coiumns in the table) get injected properly into this TableRecord.

Inserting data

The Fusion Table API allows you to insert data into a table by using a simple INSERT statement. The following code illustrates this:

public void insertIntoTable(String tableId) throws Exception {
  System.out.println(" +++ Insert into Tables");
  String point = "3.517819,50.962329,0.0";
  String sql1 = "INSERT INTO "
    + tableId
    + " (name,description, accuracy,timestamp,geometry) VALUES ('the name','the description',30,"
    + System.currentTimeMillis() + ",'" + point + "');";
  String sql2 = "INSERT INTO "
   + tableId
   + " (name,¨description, accuracy,timestamp,geometry) VALUES ('the name2','the description2',50,"
   + System.currentTimeMillis() + ",'" + point + "');";
  
  FusionTablesCommand insertTableCommand = new FusionTablesPostCommand(transport, sql1 + sql2);
  RowList rowList = insertTableCommand.execute(RowList.class);
  if (rowList.records.size()==0) {
   System.out.println("No records inserted.");
  } else {
   for (Row row : rowList.records) {
    System.out.println("record inserted : " + row.rowid);
   }
  }
  System.out.println("");

 }

When the insert was successful, the Fusion Tables API will respond with the ROWIDs of the records that got inserted. In our example, we added 2 records, so the API returns the following.

rowid
1
2

We've again modeled this response in a java object model that looks like this:

package com.ecs.fusiontables.sample.model;

import java.util.List;

import com.google.api.client.util.Key;

public class RowList {

 @Key
 public List<Row> records;
 
 public static class Row {

  @Key
  public String rowid;
 }
}

Again, you can check the Fusion Tables website

Dropping a table

We use the following code to drop a table.

public void dropTable(String tableId) throws Exception {
  System.out.println(" +++ Drop Tables");
   FusionTablesCommand dropTableCommand = new FusionTablesPostCommand(transport,"DROP TABLE " + tableId);
   dropTableCommand.execute(null);
 }

The API simply returns "OK" when the drop was successful

OK

It throws an exception when the drop couldn't take place (in case of an invalid tableId for example)

Conclusion

In this article, we went over the Fusion Tables API hands on, by writing a java based sample application that uses the Google APIs Client Library for Java. We've made various calls to the API, and used a custom parser to handle the CSV based responses we received from the API.
The API is very easy to use, and provides the basic functionality you would expect when interacting with Fusion Tables. However, some key parts are currently missing to fully integrate it in real world application. The ability to set visibility on tables, share them with specific users is currently not available in the API (although it is being logged in the issue tracker, so hopefully it will become available soon).

References

• Google Fusion Tables
• Google Fusion Tables API
• Google APIs Client Library for Java
• FusionTablesJ2SE GitHub repository
• m2eclipse