Dynamic columns as a bridge between SQL and NoSQL

Last week Oleksandr "Sanja" Byelkin and I pushed Dynamic columns into MariaDB 5.3.

Dynamic columns allows you to store a different set of columns for every row in the table. This allows you to solve things that you can't normally do with a relational database, like the web store problem where you want to store attributes for different things, like a computer (memory, speed, weight) and t-shirt (size, color).

Dynamic columns works by storing the extra columns in a blob and having a small set of functions to manipulate it. The functions exist both in SQL and in the MariaDB client library to allow you to manipulate the data where it suits you best. The client code is BSD to make it easy to port the code to other languages.

You can use a dynamic column in SQL anywhere you can use a column or function:

Some examples:
SELECT name, COLUMN_GET(blob, 1 as char(10)) FROM t1 WHERE COLUMN_EXISTS(blob, 1);

SELECT COLUMN_GET(blob, 1 as char(10)) as color, count(*) FROM t1 group by COLUMN_GET(blob, 1 as char(10));

COLUMN_GET() is used above to access the dynamic column stored in the table colum 'blob'.

The full set of SQL functions are:

Create data for a dynamic column:

COLUMN_CREATE(column_nr, value [as type], [column_nr, value [as type]]...)

Add a new column or update an old one:

COLUMN_ADD(dynamic_col, column_nr, value [as type], [column_nr, value [as type]]...)

Delete a dynamic column:
COLUMN_DELETE(dynamic_column, column_nr, column_nr...);

Get the value from a dynamic column casted to a given type. (The cast is required as SQL is a strongly typed language and we need to send the result type to the client before we have executed the query)

COLUMN_GET(dynamic_column, column_nr as type)

Check if a column exists:

COLUMN_EXISTS(dynamic_column, column_nr);

Get a list of the existing dynamic columns:

COLUMN_LIST(dynamic_column);

You can find more information in the MariaDB /MySQL knowledge base.

It's very nice to finally get this work done. I got the idea for Dynamic columns 8 years ago and always wanted to do this in MySQL but never managed to find the time to do it.

The recent trend of combining NoSQL with SQL finally pushed me to get this done as this also opens doors to do new existing things with MariaDB. There are people already working on using Dynamic columns as a building block for creating connections to NoSQL databases like HBase.

At the O'Reilly MySQL conference 2011 my talk about Dynamic columns was well attended. A large portion of the attendees said this is something they need and want to start testing / using right away as it can replace some things they are already doing / planning to do. We will have another talk about dynamic columns and combining NoSQL and SQL at Open Source bridge on June 21-24 in Portland, Oregon.

Please try this out and send your feedback to the MariaDB email list or come to Open Source bridge and discuss it with us in person.

Welcome to Percona Live in New York

Percona is organizing another Percona Live event, this time a one-day MySQL summit in New York.

This is a 100% technical conference with no marketing allowed, perfect for those that are only interested in the real stuff or those that want to get answers to problems they have *right now*.

The previous summit, held in San Francisco, was very well attended and I have heard a lot of good things about it from people that were there.

In San Francisco we had one of the MariaDB optimizer gurus holding a talk about all the advanced optimization we have added to MariaDB 5.3.

In NYC we have Kurt von Finck giving a talk about What's New In MariaDB.

Unfortunately I can't be there, even if I would like to attend :(

I was in the USA last month at the MySQL Conference and expo, and I will be in the USA again in June for Open Source Bridge. Then again in July for OSCon.

Even if I like to travel to the USA, once a month is a little too often when you live in Europe. Hope that Percona will host a summit in Europe soon ...

However, don't worry; Kurt will, of course, have with him in NYC some of the black stuff everyone is expecting from a Monty Program Ab employee.

Last not but least, for all the readers of the monty-says blog, you can get a 50$ discount to the Percona Live event by using the MONTYSAYS discount code. One never knows in what kind of places this discount code may work... ;)