Google has deprecated the fusion table SQL API and will be shutdown on December 26, 2012. Like me, many app developers are preparing for migration work.
Google already give a guideline docs to the developer for smooth up-gradation. It is fine but not sufficient for advance work. New api’s basic operation like select, update and delete is very much similar to older api. The main challenging part is the authentication to fusion table.
The fusion API v1 support below to authentication/authorization,
a. API Key: Allow only view public, unlisted(exportable) fusion table. Unfortunately not allow any update/insert/delete operation.
b. OAuth 2: Allow all kind of operation like fusion table CRUD, view of public/private table.
Authenticate using API Key:
It is very simple implementation. First, we need to generate API key using google console and then have to pass the generated key with fusion table sql. We have to follow below steps,
- login https://code.google.com/apis/console
- Click Services from left top menu
- “ON” fusion table api.
- Now click “API Access” menu link
- Create a browser/Server Key. For test purpose you keep blank referrer box. But must specify the production site referrer after deployment.
- Use the generated key(Simple API Access) in fusion table HTTP sql request . Below is a sample
https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&key=[your API key]
The API key is limited to select public/non listed(exportable) fusion table. Your Google account must have Edit/View permission of the targeted fusion table. To change add/modify fusion table access permission, click share button of the fusion table.
Authentication using OAuth 2
Google strongly recommended OAuth 2 authentication for fusion table api. If you read fusion table developer reference documentation, you will get Oauth 2 authentication step for different use-case. Unfortunately all of those need human interaction to allow app access. It is totally useless when we need to do crud operation by script. Interestingly fusion table support “service account” thought it not listed in documentation.
- Click “API” access and click new auth 2 account.
- Check service account and click “Create client ID”
- keys will be created for service account and google prompt pop for download binary key file. Download this and save your code directory.
- Now most important part is that both the gmail and “Service account” email have EDIT permission of the targeted fusion table. So allow permission both “xxxxxxxx@developer.gserviceaccount.com” and gmail account by click share button of the fusion table. Interestingly i did not find this information any where in fusion table developer api documentation except mighty Stack overflow.
Now we can authenticate fusion table by service account. I have been using google api client php version to play with fusion table API. Sample code for fusion table authentication using OAuth 2 can be found from here. This sample code used google api client php library.
Yes, hopefully we get the OAuth 2 token. Now we use this token in fusion table HTTP sql request like we have used in simple API KEY. We will always store generated token in session for repeated use.
OAuth has taken some of my night’s sleep. Now “all is well” 🙂