OurSQL Episode 175: Interesting CONNECtions

This week, we talk about the awesome ways to use MariaDB's CONNECT storage engine that go above and beyond merely connecting to remote tables.

MariaDB CONNECT Engine
Episode 174, where we talked about setting up the CONNECT storage engine and some simple examples.

Pivot Tables
MariaDB documentation page about pivot tables with CONNECT

Wikipedia entry about pivot tables

Creating the pivot table example:
First, copy and paste the top table (with Who, Week, What and Amount) from the MariaDB example page into a file, and eliminate redundant spaces.

Then, create the CONNECT table to access the data directly from the file:

MariaDB [connect_test]> CREATE TABLE expenses (
who  varchar(20) NOT NULL,
week tinyint not null,
what varchar(15) NOT NULL,
amt decimal(5,2)
) ENGINE=CONNECT  TABLE_TYPE=CSV  FILE_NAME ='/var/tmp/expenses.txt' HEADER=1 SEP_CHAR=' ';

Now, create the pivot table:
CREATE TABLE expense_pivot ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=expenses;

MariaDB [connect_test]> select * from expense_pivot;
+-------+------+-------+-------+-------+
| who   | week | Beer  | Car   | Food  |
+-------+------+-------+-------+-------+
| Beth  |    3 | 16.00 |  0.00 |  0.00 |
| Beth  |    4 | 15.00 |  0.00 | 17.00 |
| Beth  |    5 | 20.00 |  0.00 | 12.00 |
| Janet |    3 | 18.00 | 19.00 | 18.00 |
| Janet |    4 |  0.00 | 17.00 |  0.00 |
| Janet |    5 | 33.00 | 12.00 | 12.00 |
| Joe   |    3 | 18.00 | 20.00 | 31.00 |
| Joe   |    4 | 49.00 |  0.00 | 34.00 |
| Joe   |    5 | 14.00 |  0.00 | 12.00 |
+-------+------+-------+-------+-------+

OCCUR table type which can be thought of as an un-pivot

NOTE: When we tested this, we first tried to use an OCCUR table and point it to an existing PIVOT table. This crashed MariaDB, so be careful if you are trying this in your own environment. Hopefully this will be fixed in future versions of MariaDB, since it's just a release candidate. Either the crashing behavior should be fixed, or MariaDB should not allow OCCUR tables to be defined on an existing PIVOT table.

Table list CONNECT type - similar to the MERGE storage engine, but more fully featured, including knowing which row came from which table, and multi-threading!

MERGE storage engine

Parallelism in the TBL CONNECT table type

XCOL table type for separating lists of objects. For example, starting with a table like this:

+-----------+------------------------+
| name      | childlist              |
| Sophie    | Manon, Alice, Antoine  |
| Valentine | Arthur, Sidonie, Prune |
| Betty     | Lucie, Louise          |
| Corinne   |                        |
| Claude    | Marc                   |
+-----------+------------------------+

and being able to query like this:

SELECT mother, count(child) FROM xchild GROUP BY mother;
+-----------+--------------+
| mother    | count(child) |
| Betty     | 2            |
| Claude    | 1            |
| Corinne   | 0            |
| Sophie    | 3            |
| Valentine | 3            |
+-----------+--------------+

DIR table type for putting file information like path, name, extension, size and last modified/access times into a database. Now you can compare .ibd, .MYI and .MYD file sizes to the INFORMATION_SCHEMA effortlessly, inside the database!

CATALOG table type for getting metadata from remote server - Imagine using the CONNECT storage engine not only to connect to another database, like Oracle or Postgres, but get information from the remote server like what tables exist, without having to know the exact commands on those systems!

How indexing works in the CONNECT storage engine, including index condition pushdown

Ear Candy
In this week's ear candy we'll look at how to proceed when you are upgrading or migrating from MySQL to MariaDB.

At the movies
This week in At the Movies, we present "Scaling Scalability", by Dmitriy Ryaboy of Twitter. Dmitriy runs Twitter analytics, and in this talk at the 2012 Strangeloop conference, he shares some of the lessons learned scaling Twitter’s analytics infrastructure: Data loves a schema, make data sources discoverable, and make costs visible.

Where you can see us
Sheeri will be at the March Boston MySQL User Group on Monday, March 10th, where we'll have Ivan Zoratti talking about MaxScale.

Gerry will be at the March Seattle MySQL Meetup Group on Monday, March 10th.

Gerry will also be at Percona Live, April 1st through 4th, at the Santa Clara Convention Center.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast