Understanding a Federated ODBC Topology with Spider

In a federated ODBC topology, Spider has a "virtual" table on the local MariaDB Server instance, and its data is stored on a remote non-MariaDB Server instance (Oracle, Microsoft SQL Server, PostgreSQL, etc.). The client sees only the local "virtual" table, so the complexity does not affect usability. Spider integrates with the operating system's ODBC driver manager to query the remote non-MariaDB server.

This topology is only supported in MariaDB Enterprise Server 10.5. This feature has gamma maturity.

Term Definitions

Term

Definition

Spider Table

A Spider Table is a "virtual" table that uses the Spider storage engine. The table does not contain any data.

Back-end Table

A Back-end Table is a "physical" table that stores some or all of the data for a Spider Table. The table must use a non-Spider storage engine, such as InnoDB or ColumnStore.

Spider Node

The Spider Node is the MariaDB Server instance that contains the Spider table. The node uses the SPIDER plugin to access the back-end tables on the Data Nodes.

Data Node

The Data Nodes are the MariaDB Server instances that contain the back-end tables.

When to Use a Federated ODBC Topology with Spider

A federated ODBC topology allows you to:

  • Migrate a table's data from a remote non-MariaDB server to the local MariaDB server.

  • Query a table on a remote non-MariaDB server from the local MariaDB server.

  • Join a table on a remote non-MariaDB server with tables on the local MariaDB server.

Example Use Cases

Querying Remote Non-MariaDB Tables

Let's say that a business has both MariaDB and non-MariaDB databases instances, but they frequently need to query tables on multiple instances in the same query.

Spider's Federated ODBC Topology can easily be used to query remote tables on non-MariaDB servers. The MariaDB server instance that the application queries would be configured as the Spider Node, and the other non-MariaDB Server instances would be configured as the Data Nodes.

Migrating Tables to MariaDB

Let's say that a business needs to migrate a table from one non-MariaDB Server instance to a MariaDB Server instance.

Spider's Federated ODBC Topology can easily be used to migrate this table. The destination MariaDB Server instance would be configured as the Spider Node, and the source non-MariaDB Server instance would be configured as the Data Nodes.