data.gov goes live

The US Federal Government is putting some more of its data out there, at data.gov. Their full list of raw data at the time of writing comes to 47 "catalogs" with formats including XML, CSV, KML, ESRI, and a few RSS feeds. It is not very extensive at the moment compared to existing sources, but a good start. The site is clearly separated into the raw data catalog on one side, and the tools they have for exploring the data on the other. This means that third-parties could come along and make better tools with access to the same data, benefiting everyone. Within the data commons, we're following a similar model, where the data commons "repository" holds all the data, and our own online tools and API will not be privileged in any way. If someone can come along and visualize our data better than we can, then that would be great!

SQLFairy schema translation

The SQLFairy project, which has one of the most ... unusual logos around, produces a nifty command-line tool called sqlt. This tool can map the structure of a database from one format to another, including SQL databases, Excel spreadsheets, comma-separated tables, etc. Here's the full list of formats it can read from ("Parsers") and write to ("Producers"):

Parsers: Access, DB2, DB2-Grammar, DBI, DBI-DB2, DBI-MySQL, DBI-Oracle, DBI-PostgreSQL, DBI-SQLServer, DBI-SQLite, DBI-Sybase, DBIx-Class, Excel, MySQL, Oracle, PostgreSQL, SQLServer, SQLite, Storable, Sybase, XML, XML-SQLFairy, YAML, xSV

Producers: ClassDBI, DB2, DBIx-Class-File, DiaUml, Diagram, Dumper, GraphViz, HTML, Latex, MySQL, Oracle, POD, PostgreSQL, SQLServer, SQLite, Storable, Sybase, TT-Base, TT-Table, TTSchema, XML, XML-SQLFairy, YAML

It translates the structure of data (in SQL: CREATE, ALTER) and not the data itself (in SQL: INSERT, UPDATE, DELETE). On Ubuntu/Debian systems, this tool is available in the "sqlfairy" package. Let's try a quick test on the following Excel spreadsheet (well, actually an OpenOffice spreadsheet, but saved in Excel format):


If we save this as sqlfairy.xls and run sqlt like this:

sqlt --from Excel sqlfairy.xls --to MySQL

we get:

--
-- Created by SQL::Translator::Producer::MySQL
-- Created on Sat May 2 14:30:57 2009
--
SET foreign_key_checks=0;

--
-- Table: `Accounts`
--
CREATE TABLE `Accounts` (
`Account` integer(3) NOT NULL DEFAULT '',
`First_Name` char(4) DEFAULT '',
`Last_Name` char(5) DEFAULT '',
`Balance` integer(4) DEFAULT '',
PRIMARY KEY (`Account`)
);

SET foreign_key_checks=1;

Not a bad start. If you're translating from a mysql database, you can either connect to it live using the DBI parser, or dump your database first like this:

mysqldump --user=USER --password=PASS DATABASE_NAME --lock-tables=false --no-data > dump.sql

And then convert it like this (here we convert to sqlfairy's own xml format):

sqlt --from MySQL dump.sql -t XML-SQLFairy > dump.xml

Big Data

Out of curiosity, I've been looking at how some of the "big" sources of open data out there distribute their data. Wikipedia is perhaps the most famous. All the data on Wikipedia and related sites is available for bulk download. For example, the English section of Wikipedia is available here:

• All saved versions: http://download.wikimedia.org/enwiki/
• Latest files: http://download.wikimedia.org/enwiki/latest/
• The main file is "enwiki-latest-pages-articles.xml.bz2"

In other words, with a click or two you can end up with an XML file holding the basic content of all English Wikipedia pages. There are other XML and SQL files for other bits and pieces.

The DMOZ open directory (like Yahoo's directory, but volunteer created and under a free license) is downloadable in RDF format at http://rdf.dmoz.org/.

Of course, there's lots more data out there, but this does give a sense of one way in which "Big Data" may be distributed. What I like:

• It is really easy to get the free data, just like it is easy to get free software.
• The data is in a good format to use, just like free software source code.
• Rights to the data are granted in a clear and free license, just like free software.

What I don't like:

• There's no equivalent of "patches" in software. Let me explain. If you improve a piece of code someone else wrote, you can automatically generate the "difference" between the original and your revised version, send that difference (called a "patch") to the original author, who can then evaluate it and if they like it merge it automatically with their code (even if they've made their own non-overlapping changes in the meantime). That's patching in software. Now what happens if you improve pages you downloaded from Wikipedia? I guess you go to the site and try typing them in - there's no way I see to submit something like a patch. And without a patching mechanism, there's no basis for distributed development of the data, like happens with free software.

There are distributed databases out there. CouchDB is interesting, for example. But it would also be interesting to have a procedure for patching and merging data that operated on an external representation rather than on live databases.

Update: Nat Torkington has a post called Truly Open Data asking similar questions.

Update (2): I've been doing work on storing our own data in fossil, a distributed version control system. The trick, as I see it, is to bridge the gap between git/bzr/hg/fossil/... and programs like Excel that non-programmers keep their data in.