gFTC

Fusion Tables Console

For a quick start with Google fusion tables sql: syntax highlighting, syntactic and semantic error markers, content assist, refer to tables by name or ID. Delete, rename and copy tables by sql, export csv. Can be used with graphical UI or command line mode, also available as Eclipse plugin.

Get started

You need some fusion tables you own or some external table IDs, a google developers project with activated fusion tables api and an OAuth2 client ID (with application type "other") for the project.

Download the packaged JAR archive, extract it, run ftc.bat (Windows), ftc.sh (make sure it is executable) for unix-like systems or "java -jar ftcClientJava-0.1.jar" otherwise. Requires Java 7.

Enter client id and secret, then press

. The authentication workflow will try to open your default browser and take you through further steps. If it doesen't show up, you can find the required URL in the log pane, proceed manually in this case. If anything goes wrong with the authentication, the client waits for the defined timeout period and then cancels. In case of success the client stores the credentials and authenticates automatically in future sessions.

Once authenticated create a new .sql or .fts file and open it with the Fusion Tables editor. Press ctrl+space in the empty editor window for a list of top level Sql statements.

Covers

SELECT, CREATE VIEW, INSERT, UPDATE, DELETE, DESCRIBE and SHOW TABLES.

For tables owned by yourself you can use table names instead of table IDs. Make sure, there are no naming collisions. Alt+F5 outputs the query text with table name replaced by table Id, given there is any Id for that name.

The client accepts some usual Sql terms not covered by Fusion Tables Sql and translates them to api calls. UPDATE and DELETE are augmented, they accept the same <filter_condition> as SELECT ({ WHERE <filter_condition> | <spatial_condition> { AND <filter_condition> }* }).

Extended commands

Command	Syntax	Comment
Rename table	ALTER TABLE [table name or ID] RENAME TO [new name]	Atomic
Delete table	DROP TABLE [table name or ID]	Atomic
Copy table	CREATE TABLE [table name] AS SELECT * FROM [source table name or ID]	Composite: copy, rename. If rename fails, the name of the new table defaults to "Copy of [original name]"
UPDATE	UPDATE TABLE [table name or ID] <filter_condition>	Composite: select ROWIDs for <filter_condition>, 1 update per ROWID.
DELETE	DELETE FROM [table name or ID] { <filter_condition> }	Composite: select ROWIDs for <filter_condition>, 1 delete per ROWID.

Commands and Shortcuts

F5	Execute query under cursor
Ctrl+F5	Cancel query execution
Alt+F5	View preprocessed query. Table name replaced by table Id and in case of SELECT added OFFSET and LIMIT clauses, if not defined anyway.
F7	Execute all queries sequentially
F12	Focus editor text field
Ctrl+space	Trigger code completion. Switch between proposed Sql snippets and table or column names
Alt+left	Browse command history backwards
Alt+right	Browse command history forward
Alt+M	Memorize editor text ... means, append it to the command history without executing the query
Ctrl+T	Show tables
Ctrl+E	Export to csv file
Ctrl+S	Save editor text
Ctrl+O	Open file

Command line mode

You can pass query source- and optionally result destination file names as command line parameters using ftc.sh (didn't bother myself with ftc.bat yet in this regard). Execution goes on without gui then. Command line execution relies on previously stored authentication credentials. Looks like this:

ftc.sh [<query source file> [<result data destination file>]]

Limitations

Some augmented operations are composites and not atomic . E.g. "create table as select" in fact is "copy table" followed by "rename". Say copy succeed, still rename may fail for whatever reason. This leaves a copy with the default name: "Copy of [original name]". Issue another rename in this case. UPDATE and DELETE for multiple ROWIDs may fail anytime in between leaving the operation incomplete, but potentially repeatable. Another issue here is limited possibility for query parallelization. Only 2 threads do this now and give you occasional "503 - service not available" errors, which become more frequent with more threads. Covered by a retry mechanism, until success or manual cancellation.
Column ids can be used in queries, but they are not covered by semantic highlighting, so you get a blue wiggle line for them.
Keyword "AS" is mandatory in definitions of table aliases.
Handling huge amounts of data isn't the focus of the implementation nor of the Sql interface. That said, it may take you as far as the network, the computer's memory and the fusion tables service allows it to.
Snippets aren't supported for each and every corner, just for the major cases: top level statements (SELECT, UPDATE...), <column_spec> including aggregate operations, GROUP BY, ORDER BY and WHERE clauses. Completion may behave a bit surprising in certain situations. If you're editing around the end of a statement, just adding a terminal ";" at the end of it in order to enable the parser to produce something reasonable may be of help.
Single snippets can't be nested. E.g. if you use the SELECT snippet, parameter replacement only offers table and field names. You can not use another aggregate statement in the <column_specs>s or add a WHERE clause. Exit the parameter completion and toggle another snippet in order to do so.
I guess the snippet aproach to Sql ran into its limits with the create view statement involving joins.
The plugin uses the Java preference storage mechanism rather than the one of Eclipse.

Google throttling

When you start to see thinkgs like "403 Forbidden - Rate Limit Exceeded" that are access limitations imposed by Google. It's really obnoxious, though understandable, rendered parallel query execution almost useless and made a retry mechanism mandatory.

Table API	200 queries per day
SQL API	5 queries per second for read requests per user account or per IP, whichever comes first
	30 queries per minute for more successful write requests

Can look like this then:

08:55:51 running: 'UPDATE classifiedAds SET statusPredicted = 0 WHERE statusPredicted = '';'
08:55:52 Composite queries being processed.
08:55:52 Queued UPDATE 310 times
09:06:21 Executed query in 629.443 seconds. 1 records returned
09:06:21 310 of 310 composite queries processed

Open Issues

Find the list at GitHub.

Credits

Silk icon set by Mark James.
RSyntaxTextArea Editor and auto complete components by Robert Futrell
Google Fusion Tables™ service is a trademark of Google Inc.