SQL or NoSQL? … Both with MariaDB 10

Dynamic columns came to my attention a few days back. Since then I read a little bit more in the documentation (see Dynamic columns in the Knowledge Base) and played with it a little. The reason I became curious was that it brings the ‘schema less’ feature of the NoSQL world into the MySQL world. It was implemented in MariaDB v5.3, and MariaDB 10.0 introduces several enhancements. We will cover the topic in some detail in episode 181 of theMySQL Community podcast in case you’re interested. But I thought I’d showcase the feature through an hypothetical upgrade.

Differences between 5.3 and 10

In the documentation page referenced above, the new features in 10.0 are highlighted and include 2 new functions (COLUMN_CHECK andCOLUMN_JSON) and now columns can be specified by name.

When I was working with them, I used MariaDB 5.5, so I ended up with numeric column names in my test. My goal in this blog is to describe a way to use column names without updating the BLOB field directly and risking corrupting the data.

How To Update Dynamic Column Name

The table I used for my tests was a simplified catalog for music albums and it looks like this:

CREATE TABLE `album` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `artist` varchar(128) NOT NULL, `details` blob, PRIMARY KEY (`pk`) ) ENGINE=InnoDB;

I inserted a couple of rows in it, upgraded to MariaDB 10.0 and ended up with the following data (for brevity’s sake I’m not posting the INSERT statements):

MariaDB [my_test]> select pk, name, artist, COLUMN_JSON(details) from album; +—-+————————-+—————-+———————————————————-+ | pk | name | artist | COLUMN_JSON(details) | +—-+————————-+—————-+———————————————————-+ | 1 | The snow goose | Camel | {“1″:”1984″,”2″:”progressive rock”} | | 2 | Back in the U.S. Disc 1 | Paul McCartney | {“2″:”rock”,”3″:”~/art_cover/back_in_the_us_disc_1.jpg”} | +—-+————————-+—————-+———————————————————-+ 2 rows in set (0.00 sec)

Column ‘1’ is for the year the album was published, ‘2’ for the musical style and ‘3’ for the location of the cover’s art on disk. As in the NoSQL world, not all rows have all columns and you can add new ones without changing the schema.

Now that I’m on MariaDB 10.0, I’d like to replace the numbers by names to make the results more readable. We can do this with 2 UPDATE statements per dynamic column if we keep in mind the fact that COLUMN_GET returns a NULL for those rows where the dynamic column doesn’t exist. So to change ‘1’ into ‘year’ we would use the following 2 UPDATE statements:

UPDATE album SET details=COLUMN_ADD(details, ‘year’, COLUMN_GET(details, ‘1’ AS CHAR)) WHERE COLUMN_GET(details, ‘1’ AS CHAR) IS NOT NULL; UPDATE album SET details=COLUMN_DELETE(details, ‘1’) WHERE COLUMN_GET(details, ‘1’ AS CHAR) IS NOT NULL;

The resulting table contents now look like this (updated data “year” & “1984”):

MariaDB [my_test]> SELECT pk, name, artist, COLUMN_JSON(details) AS dyn_col FROM album; +—-+————————-+—————-+———————————————————-+ | pk | name | artist | dyn_col | +—-+————————-+—————-+———————————————————-+ | 1 | The snow goose | Camel | {“2″:”progressive rock”,”year”:”1984″} | | 2 | Back in the U.S. Disc 1 | Paul McCartney | {“2″:”rock”,”3″:”~/art_cover/back_in_the_us_disc_1.jpg”} | +—-+————————-+—————-+———————————————————-+ 2 rows in set (0.00 sec)

Now we can repeat this for columns ‘2’ and ‘3’. You can always verify the name of the dynamic columns in the rows using a statement similar to this:

SELECT pk, name, artist, COLUMN_LIST(details) AS dyn_col FROM album; +—-+————————-+—————-+————+ | pk | name | artist | dyn_col | +—-+————————-+—————-+————+ | 1 | The snow goose | Camel | `2`,`year` | | 2 | Back in the U.S. Disc 1 | Paul McCartney | `2`,`3` | +—-+————————-+—————-+————+ 2 rows in set (0.00 sec)

Conclusion

With the ability of storing schema less data in MariaDB it is possible to bring NoSQL and SQL solutions a lot closer that has been in the past. Dynamic columns represent schema less data in a way that resembles NoSQL solutions like MongoDB and Cassandra. So maybe you won’t need to migrate your systems after all. If you do, and decided to use Cassandra, in MariaDB 10.0 it is possible to use dynamic columns with the Cassandra storage engine, making the integration between the 2 a lot easier.