ODBC MariaDB Enterprise Spider Topology

Overview

In the ODBC MariaDB Enterprise Spider topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When the Spider Table is queried in this topology, the Enterprise Spider storage engine uses an ODBC foreign data wrapper to read from and write to an ODBC Data Source.

Benefits

MariaDB Enterprise Spider:

The Spider ODBC topology:

  • Can be used to query ODBC Data Sources from the Spider Node using the ODBC foreign data wrapper.

  • Can be used to join ODBC Data Sources with tables on the Spider Node using the ODBC foreign data wrapper.

  • Can be used to migrate table data from ODBC Data Sources to the Spider Node using the ODBC foreign data wrapper.

ODBC MariaDB Enterprise Spider Topology

Spider ODBC Topology

In the Spider ODBC topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When the Spider Table is queried in this topology, the Enterprise Spider storage engine uses an ODBC foreign data wrapper to read from and write to an ODBC Data Source.

MariaDB Enterprise Spider implemented support for the ODBC foreign data wrapper in MariaDB Enterprise Server 10.5. The ODBC foreign data wrapper was backported to MariaDB Enterprise Server 10.4 in ES 10.4.25-16.

The ODBC foreign data wrapper is beta maturity. The maturity can be confirmed by querying the information_schema.SPIDER_WRAPPER_PROTOCOLS table.

The Spider ODBC topology consists of:

  • One MariaDB Enterprise Server node is a Spider Node

  • One ODBC Data Source stores data for Spider Tables

The Spider Node:

  • Contains one or more Spider Tables

  • Uses the Enterprise Spider storage engine plugin for Spider Tables

  • Uses an ODBC foreign data wrapper to query the ODBC Data Source

The ODBC Data Source:

  • Contains the Data for each Spider Table

  • Can be a non-MariaDB database server, such as Microsoft SQL Server, Oracle, or PostgreSQL

Term Definitions

Term

Definition

Data Node

A Data Node is a MariaDB Enterprise Server node that contains one or more Data Tables.

Data Table

A Data Table stores data for a Spider Table. When a Spider Table is queried, the Enterprise Spider storage engine uses the MariaDB foreign data wrapper to read from and write to the Data Table on a Data Node. The Data Table must be created on the Data Node with the same structure as the Spider Table. The Data Table must use a non-Spider storage engine, such as InnoDB or ColumnStore.

ODBC Data Source

An ODBC Data Source relies on an ODBC Driver and an ODBC Driver Manager to query an external data source.

ODBC Driver

An ODBC Driver is a library that integrates with a ODBC Driver Manager to query an external data source.

ODBC Driver Manager

An ODBC Driver Manager allows applications to use ODBC Drivers.

Spider Node

A Spider Node is a MariaDB Enterprise Server node that contains one or more Spider Tables.

Spider Table

A Spider Table is a virtual table that does not store data. When a Spider Table is queried, the Enterprise Spider storage engine uses foreign data wrappers to read from and write to Data Tables on Data Nodes or ODBC Data Sources.

Example Use Cases

Query Non-MariaDB Databases

The Spider ODBC topology can be used to query tables located on non-MariaDB databases:

  • The non-MariaDB database is configured as an ODBC Data Source in the ODBC Driver Manager.

  • The MariaDB Enterprise Server node that needs to query the ODBC Data Source is configured as a Spider Node.

  • A Spider Table is created on the Spider Node that references the ODBC Data Source.

  • On the Spider Node, the ODBC Data Source is queried by querying the Spider Table like the following:

    SELECT * FROM spider_tab;

  • Local tables can also be referenced in queries:

    SELECT *
FROM spider_tab s
JOIN local_tab l
ON s.id=l.id;

Migrate Tables from Non-MariaDB Databases

The Spider ODBC topology can be used to migrate tables from a non-MariaDB database to a MariaDB Enterprise Server node:

  • The non-MariaDB database is configured as an ODBC Data Source in the ODBC Driver Manager.

  • The MariaDB Enterprise Server node with the destination table is configured as a Spider Node.

  • A Spider Table is created on the Spider Node that references the ODBC Data Source.

  • On the Spider Node, the ODBC Data Source's data is migrated to the destination table by querying the Spider Table like the following:

    INSERT INTO destination_tab
   SELECT * FROM spider_tab;

Examples

Load Spider with Configuration File (ES 10.4+)

[mariadb]
...
plugin_load_add = "ha_spider"

Load Spider with INSTALL SONAME (ES 10.4+)

INSTALL SONAME "ha_spider";

Load Spider with Script (ES 10.3)

$ mysql --user root --password < /usr/share/mysql/install_spider.sql

View Foreign Data Wrappers (ES 10.5+)

SELECT * FROM information_schema.SPIDER_WRAPPER_PROTOCOLS;

Resources

Schema Design