Saturday, April 21, 2012

PiQL tech talk

Big Data has been gaining a lot of press lately, and NoSQL even more. A lot of new software development is moving to using NoSQL databases to alleviate the scaling pains of traditional RDBMS systems when the data size grows very large. The NoSQL databases usually expose a simple key value based API, where you can set, retrieve, and scan values based on the value of the keys you're interested in. The API is sufficient for most applications, but sometimes you want more than the simple retrieval API; you want SQL, where you can join keys, and have more complex filtering rules.

Here is where PiQL plays a role. Last week I attended a talk about PiQL by Michael Armbrust. In the talk, Michael introduced PiQL: a query system that runs on top of NoSQL key-value stores, and allows developers to write SQL queries that execute efficiently against the simple key-value retrieval API.

The system operates in two stages: the first is the static analysis stage, and the second is the execution stage. In the analysis stage, the optimizer analyzes all the SQL queries in the application offline. The analysis phase produces one of two results. If the queries can't be executed efficiently, the system warns the developer, and gets their explicit approval to serve the results of these queries.

However for the SQL queries that return bounded results, such as select ... limit 100 type queries, the system creates views and indexes in the key-value store, and translates the SQL queries into an execution plan over these views and indexes to make the execution efficient.

At run-time, the system uses the previously created views and indexes, with an efficient execution plan to transform SQL queries to simple key-value retrieval APIs, and serve the results. Michael brushed upon the transformation patterns for different SQL query types including joins and how PiQL creates views and indexes to serve these.

PiQL works well with read intensive systems. The issue with updates is that in addition to updating the data, the system also needs to update the views and indexes, and that could take a bit of time. When I asked Michael at the end of the talk he mentioned the updates typically take 150ms, but for some queries where the created views and indexes are a cross product of two keys, the updates take longer. If a read comes in during the update time, then you might miss the updated data. The updates are still bounded though, because the key spaces are bounded--a prerequisite for efficient execution in PiQL.

You can read more about PiQL and Michael's research at: http://www.cs.berkeley.edu/~marmbrus/