October 2, 2014

Dynamic Columns Tutorial – Part 1: Introduction

For certain situations, the static structure of tables in a relational database can be very limited. Each column is statically defined, has a pre-defined type and you can only enter a value of that type into the column.You can be creative and list multiple values in one column, but then those values are not generally easily accessed and manipulated with other functions. You have to use an API or contortions of a function like SUBSTRING() to pull out a value you want. Even then, you have to know what is contained in the column to be able to manipulate it properly. These methods can require too much manual intervention to assess and access the data contained in the column.

If you want to add columns as the information stored in your tables grows and your needs change you need to do fairly expensive ALTER TABLE operations. These have traditionally been very expensive in MySQL and MariaDB although the performance is a bit better starting with MariaDB 10.0 and MySQL 5.6.

The other option for having a flexible structure is to use something like Anchor Modeling . This allows you to have a very flexible schema as adding an attribute basically just means adding a table. The problem with this approach is that you'll end up with a lot of tables which means a lot of joins when looking for results which can easily become un-manageable, or at least hard to manage.

This is where dynamic columns steps into the picture. A good solution to the static structure problem is to use dynamic columns provided in MariaDB. It allows flexibility within a defined structure, within a column. A Dynamic Column is defined as a BLOB on the DDL level. However, within the BLOB column, you may set arbitrarily and dynamically defined attributes and values--for a maximum of 64k.

Dynamic columns are not in isolation: The usual functions will work fine with the values contained within them. And they can be used as join points for joining to other table as you would normally. This allows you to retaing the power of Relational SQL while still mainting a flexibility with regards to your attributes for specific tables where it makes sense

Creating Dynamic Columns

To better understand dynamic columns, let's look at some examples of a table with them. First, let's use the CREATE TABLE statement to create a table with a BLOB column which will contain some dynamic columns:

CREATE TABLE items
(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name varchar(100) NOT NULL,
 attributes BLOB);

There's nothing interesting about this CREATE TABLE statement. The table it creates is fairly normal. There isn't a dynamic column in it yet. But the column named, attributes will contain dynamic columns when we insert data into it. Incidentally, any name is fine. It doesn't have to be called, attributes. This table could be used in a webstore where we sell various items, but you would typically have more columns in the table. I've used it to list items in my laptop bag, but it could be used for anything really.

Let's now insert some items into it:

INSERT INTO items (name, attributes) VALUES
('MariaDB t-shirt', COLUMN_CREATE('colour','blue', 'size','XXL')),
('MariaDB t-shirt', COLUMN_CREATE('colour','blue', 'size','XL')),
('Samsung Galaxy S5', COLUMN_CREATE('colour','white', 'OS', 'Android',
'type', 'phone')),
('Samsung Galaxy Pro 3', COLUMN_CREATE('colour','white', 'size',8,
'OS', 'Android', 'resolution','1920x1200', 'type','tablet'));

Notice the MariaDB exclusive function, the COLUMN_CREATE( ) function. It allows you to insert structure into a BLOB column, thus creating dynamic columns. Within the parentheses, you give it a name/value pair, just like you would typically do in a JSON string. For instance, we inserted data related to two t-shirts: we gave the colour of blue for both; and the size of each, XXL and XL. The other rows provide information on electronic devices—all in the same column and all with names that make them accessible to SQL statements and functions.

Let's look at how the values look without using any special functions for extracting the dynamic column values. Here's a simple SELECT statement for retrieving the first row:

SELECT * FROM items
WHERE id = 1;

+----+-----------------+----------------------------------+
| id | name            | attributes                       |
+----+-----------------+----------------------------------+
| 1  | MariaDB t-shirt | ♦☻ ♥ ♦ C sizecolour♦XXL♦blue     |
+----+-----------------+----------------------------------+

You can see the attributes column contains some odd symbols, some binary characters. The information is pretty useless in this format. We'll need some special functions for dynamic columns. We can use the COLUMN_JSON() function to get the name/value pairs of a column which contains dynamic columns. We would execute something like this:

SELECT name AS Item,
COLUMN_JSON(attributes) AS 'Dynamic Columns'
FROM items LIMIT 1;

+-----------------+--------------------------------+
| Item            | Dynamic Columns                |
+-----------------+--------------------------------+
| MariaDB t-shirt | {"size":"XXL","colour":"blue"} |
+-----------------+--------------------------------+

This looks a lot better! You can see here that the COLUMN_JSON( ) function returns the values in a typical JSON format. However, this is not a manner that is useful within the database--especially not for use in standard functions. This format is mostly useful for exporting the data.

Dynamic Column Functions

There are a few dynamic column functions available with MariaDB. The COLUMN_LIST( ) function may be used to get a list of the attributes contained in a column. Let's try using it to get a list for the attributes column like this:

SELECT name AS Item,
COLUMN_LIST(attributes) AS 'Attribute Names'
FROM items;

+----------------------+------------------------------------------+
| Item                 | Attribute Names                          |
+----------------------+------------------------------------------+
| MariaDB t-shirt      | `size`,`colour`                          |
| MariaDB t-shirt      | `size`,`colour`                          |
| Samsung Galaxy S5    | `OS`,`type`,`colour`                     |
| Samsung Galaxy Pro 3 | `OS`,`size`,`type`,`colour`,`resolution` |
+----------------------+------------------------------------------+

That's much more agreeable. Now we clearly see which attributes each row (item) has values for.

Now let's see how to get the values from the dynamic columns. For that we can use the COLUMN_GET( ) function. It requires us to provide the name of the attribute we want. We have those names from the results of the COLUMN_LIST() in the previous example. Let's get the colours for all of the item in our table. We'll do that like this:

SELECT name AS Item,
COLUMN_GET(attributes, 'colour' AS CHAR) AS Colour
FROM items;

+----------------------+--------+
| Item                 | Colour |
+----------------------+--------+
| MariaDB t-shirt      |  blue  |
| MariaDB t-shirt      |  blue  |
| Samsung Galaxy S5    |  white |
| Samsung Galaxy Pro 3 |  white |
| The Name of the Rose |  red   |
+----------------------+--------+

You might have noticed that I slipped a book into the list while waiting! We also see that all items in the table have the attribute colour as no rows came out with NULL listed as the value.

Those are two useful functions: COLUMN_LIST( ) to list the attribute names; and COLUMN_GET( ) to get the values associated with a given attribute. There are other ways to use these functions, though, besides retrieving data from a column. We'll look at some examples of that in the next blog posting.

About Max Mether

Max Mether, co-Founder and Head of Server Product Management is currently leading the efforts on MariaDB Server development. Prior to his current role, Max built up the Professional Services team at MariaDB from scratch. Max began his career consulting and training for prominent companies including MySQL and Sun Microsystems. He continues to train as a frequent speaker at LinuxFests, LinuxCons and other prominent conferences globally. Max earned his Master of Science (Eng) in Physics and Maths from Helsinki University of Technology. A native of Finland, and by-way of Paris, France, Max now resides in Atlanta, Georgia where he is an avid skier, rugby and hockey player.

Read all posts by Max Mether