Authenticating to Google Fusion Tables from Server

A couple of weeks ago I started a project where I needed to save big amounts of data for later statistical analysis. The first data store I looked at was Google Fusion Tabels (because it was free...). The reason I didn't go for it was that there were quota limitation that didn't allow that much data.

But before I got to the point that I discarded it wrote a working backend on Google app engine that could write to a table. Here is the code. The hard thing here was authenticating to the Fusion tables. 

Working with Google APIs from Google App Engine should be very easy thanks to Google App engines support for service account, unfortunately Fusion tables API does not support this yet. So we need to use the harder way to use Application Accounts. Here is the code for authenticating and making request to Fusion tables. Please ignore bad exception handling.

private Fusiontables fusiontables = null;
   
    public void initiateFusionTablesAPI() {
        Collection scopes =
                Collections.singleton("https://www.googleapis.com/auth/fusiontables");

        String serviceAccountId = "768188911902@developer.gserviceaccount.com";

        String p12FileName = "/privatekey.p12";
        InputStream p12Stream = context.getResourceAsStream(p12FileName);
        PrivateKey serviceAccountPK = null;

        try {
            serviceAccountPK = SecurityUtils.loadPrivateKeyFromKeyStore(
                    SecurityUtils.getPkcs12KeyStore(), p12Stream,
                    "notasecret", "privatekey", "notasecret");
        } catch (GeneralSecurityException e) {
            e.printStackTrace();
        }

        GoogleCredential credential = new GoogleCredential.Builder()
                .setTransport(new NetHttpTransport())
                .setJsonFactory(new JacksonFactory())
                .setServiceAccountId(serviceAccountId)
                .setServiceAccountScopes(scopes)
                .setServiceAccountPrivateKey(serviceAccountPK)
                .build();



        fusiontables = new Fusiontables.Builder(
                new NetHttpTransport(), new JacksonFactory(), credential)
                .setApplicationName("Bysykkel-stats")
                .build();

    }

   
    public void storeInformation(){

    String sql = "INSERT INTO ...";
   
    try {
        Sqlresponse sqlresponse = fusiontables.query().sql(sql).execute();
        if (sqlresponse.getRows().size() != 1) {
            throw new RuntimeException("Error in sql response " + sqlresponse.toPrettyString());
        }
    }  catch (Exception e) {
            e.printStackTrace();

    }

    }

Further reading

This book covers, among other things, google fusion tables