Perl for Fusion Tables

September 17th, 2011 robin Posted in google, Internet, Ironman, Perl | 9 Comments »

The last couple of weeks I’ve been getting accustomed to Google Fusion Tables.

For those who haven’t yet noticed it – Fusion Tables is a new (still in Beta) Google product for data storage: a kind of database.

Some major cool things about Fusion Tables:

  1. it’s really accessible (use the usual OAuth2 authentication as for all other Google services to script interaction), or use the web interface to manually edit your tables.
  2. If you include geo data in your tables, you can easily (and I mean really easily!) display your data on a map.
  3. They’re really clever about geo data… a text address, longitude/latitude, polygons, shapes, … dump it into a “Location” type field, and it will probably be understood.
The one annoying thing: being in Beta, there aren’t many tools available to work with it yet…  But as usual: if someone else hasn’t done it, you’d better do it yourself:
  • Google::Fusion – an object oriented (perl) interface to Google::Fusion tables
  • Net::OAuth2 – the authentication module

Net::OAuth2 isn’t actually a new Perl module, but in its previous state it wasn’t really usable for command line applications, so I reworked it a bit.  Both of these modules are very raw/alpha – they are lacking in documentation, error handling, unit tests…. all the things one would expect in productive code… but I want to get them out there now so other people can start using and abusing them.

Here’s a wee screencast of pfusion in action:

And a short instruction to install (I hope it’s complete…):

git clone git://github.com/robin13/Net-OAuth2.git
git clone git://github.com/robin13/Google-Fusion.git
vi ~/.fusion

Edit to look like this, with the id/secret of your application.  If you haven’t got an id/secret yet, you’ll have to register an application for this purpose on the Google API Console and be sure to make it an “installed application” (not “web application”)

---
client_id: 372296649547.apps.googleusercontent.com
client_secret: XDy8Y8xTuwN90F3h7ljCuw4i

# And now run fusion:

perl -I ./Google-Fusion/lib -I ./Net-OAuth2/lib ./Google-Fusion/bin/pfusion

If authentication is successful, you should be able to interact with Fusion Tables to your hearts content, and should find a .fusion.auth file in your home directory with your current tokens (you only have to enter the authentication code the first time).

I hope you have fun with it, and give me some nice pull requests on those repositories!


You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

9 Responses to “Perl for Fusion Tables”

  1. Matt Says:
    September 19th, 2011 at 01:38

    Just tried out your directions to get started and they worked great! One thing that wasn’t obvious to me, totally new to the Google API stuff, was how to register for the client_id and _secret. I eventually figured out it was the “API Access” choice in the console. 🙂

  2. Ron Says:
    September 20th, 2011 at 06:13

    I’m not sure what happened to my previous post but I have made more progress. I’m new to oAuth so I used the wrong code. I’ve fixed this and my FT sql command complete but with no results. For example,

    C:\Users\Ron\Dropbox\aa map\perl>perl -I Google-Fusion/lib -I Net-OAuth2/lib Google-Fusion/bin/pfusion

    fusion> show tables
    Digest: be021a0b3956334563aa6949abdacb23a012818b1d159432132f69c9e5a5f914

    0 line in 0.0363s (auth: 0.0185s)

    fusion> create table test (col1:NUMBER)
    Digest: 1437cf52ab2e257e8239e76aa74e0246a9fe671b25a1a2d6d52c7a5596d62bb1

    0 line in 1.8301s (auth: 0.0000s)

    fusion>

    The create table didn’t create a table as shown in the FT UI. I’ll keep trying. Any ideas would be appreciated.

  3. Ron Says:
    September 20th, 2011 at 06:22

    Okay, looks like I got it. There is something funny happening w/ the query_cache. If I mod the dir from ‘/tmp/query_cache’ to ‘query_cache’ it works. I’m sure it is a windows thing although I tried it in cygwin and it had the same behavior.

    Anyway, thanks for your efforts on this. I look fwd to using it and if you are in need of a windows based tester let me know.

  4. robin Says:
    September 21st, 2011 at 08:12

    Hi Ron,

    Thanks for testing on Windows!

    The query_cache was a variable I implemented for testing so that it would cache big query results, to optimise tabular display, without waiting for slow network every query. I have removed it as a default now (can be put in your .fusion config file if desired), so should not cause any problems any more, and have removed the two annoying debugging outputs which were still cluttering the output. Just pull for latest!

    Please keep giving me any feedback!

  5. Ron Says:
    September 26th, 2011 at 06:03

    Something was going on with my permissions and I could select from a table but not much else (describe, show, create returned nothing). To figure out what was going on I added an else block to the “if ($response->is_success){” which simply printed the error in Fusion.pm.

    if( $response->is_success ){
    …
    } else {
    print “Error: “.$response->status_line.”\n”;
    }

    This showed me my authorization was bad. I reset it and it worked fine.

    C:\Users\Ron\Dropbox\aa map\perl>perl -I Net-OAuth2/lib -I Google-Fusion/lib Google-Fusion/bin/pfusion

    fusion> show tables
    Error: 401 Unauthorized
    0 line in 1.3926s (auth: 0.0200s)

    Maybe you can add something similar to your next iteration?

  6. Ron Says:
    September 26th, 2011 at 08:35

    I now see the HTTP response is included in the result returned by the query. So I can check for it within my program instead of modifying Fusion.pm.

    I’d suggest updating the example script to check for success. FT has some issues with internal errors with insert/update/delete statements and error checking is important (see http://code.google.com/p/fusion-tables/issues/detail?id=433)

  7. robin Says:
    September 27th, 2011 at 01:43

    Thanks for your input – try the latest version from github. If the error is a 401/Unauthorized (usually the Auth token is invalid), it will now try to refresh the token and re-submit the request.
    There is also a local function: .refresh_token to force a token refresh.

  8. David Gross Says:
    August 18th, 2012 at 12:47

    I’m at the “fusion>” command line and have input the command “show tables”.

    The command line responds with “Please authorize your application with this URL”, gives me a long URL, and then asks for a “Code”.

    Does anyone know what I need to do?

  9. Seqbench: A useful meta-resource of E. coli sequence reads Says:
    October 9th, 2012 at 12:05

    […] this via a SQL interface using the Google Fusion Table API, e.g. with a command-line interface like Pfusion. The dataset is under active curation by Lex and myself. When it is stable I plan to deposit it […]

«
»