Explore MariaDB Enterprise Spider topologies with MaxScale. This section details how it integrates with Spider to manage & route traffic efficiently across sharded & distributed database environments.
Query, join, or migrate tables on a remote MariaDB Enterprise Server node from a Spider Node using virtual Spider Tables and the MariaDB foreign data wrapper.
Read from and write to external ODBC data sources from a Spider Node using virtual Spider Tables and the ODBC foreign data wrapper (Enterprise Server 10.5 and later).
Partition a large table across multiple MariaDB Enterprise Server Data Nodes using virtual Spider Tables and standard partitioning syntax for horizontal scalability.
Query, join, or migrate tables on a remote MariaDB Enterprise Server node from a Spider Node using virtual Spider Tables and the MariaDB foreign data wrapper.
Federated MariaDB Enterprise Spider Topology
Overview
In the Federated MariaDB Enterprise Spider topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to a Data Table on a Data Node.
Benefits
MariaDB Enterprise Spider:
Supports a MariaDB foreign data wrapper. The MariaDB foreign data wrapper can be used to replace the older Federated and FederatedX storage engines.
Supports an ODBC foreign data wrapper in MariaDB Enterprise Server 10.5 and later. The ODBC foreign data wrapper is beta maturity. The maturity can be confirmed by querying the information_schema.SPIDER_WRAPPER_PROTOCOLS table.
The Spider Federated topology:
Can be used to query tables located on a different MariaDB Enterprise Server node from the Spider Node using the MariaDB foreign data wrapper.
Can be used to join tables located on a different MariaDB Enterprise Server node with tables on the Spider Node using the MariaDB foreign data wrapper.
Can be used to migrate tables located on a different MariaDB Enterprise Server node to the Spider Node using the MariaDB foreign data wrapper.
Federated MariaDB Enterprise Spider Topology
In the Spider Federated topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to a Data Table on a Data Node.
The Spider Federated topology consists of:
One MariaDB Enterprise Server node is a Spider Node
Uses a MariaDB foreign data wrapper to query the Data Table on the Data Node
The Data Node:
Contains a Data Table for each Spider Table
Uses a non-Spider storage engine for each Data Table, such as InnoDB or ColumnStore
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 uses foreign data wrappers to read from and write to Data Tables on Data Nodes or ODBC Data Sources.
Example Use Cases
Query MariaDB Enterprise Server Nodes
The Spider Federated topology can be used to query tables located on another MariaDB Enterprise Server node:
The MariaDB Enterprise Server node with the desired table is configured as a Data Node.
The MariaDB Enterprise Server node that needs to query the table is configured as a Spider Node.
The Data Table is the desired table on the Data Node.
A Spider Table is created on the Spider Node that references the Data Table on the Data Node.
On the Spider Node, the Data Table is queried by querying the Spider Table like the following:
SELECT * FROM spider_tab;
Non-Spider tables can also be referenced in queries with the Spider Table:
SELECT *
FROM spider_tab s
JOIN innodb_tab i
ON s.id=i.id;
Migrate Tables from MariaDB Enterprise Server Nodes
The Spider Federated topology can be used to migrate tables from one MariaDB Enterprise Server node to another MariaDB Enterprise Server node:
The MariaDB Enterprise Server node with the source table is configured as a Data Node.
The MariaDB Enterprise Server node with the destination table is configured as a Spider Node.
The Data Table is the source table on the Data Node.
A Spider Table is created on the Spider Node that references the Data Table on the Data Node.
On the Spider node, the Data Table's data is migrated to the destination table by querying the Spider Table like the following:
INSERT INTO innodb_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";
View Foreign Data Wrappers (ES 10.5+)
SELECT * FROM information_schema.SPIDER_WRAPPER_PROTOCOLS;
Create Federated Spider Table
CREATE SERVER hq_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.2",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "hq_sales"
);
CREATE DATABASE spider_hq_sales;
CREATE TABLE spider_hq_sales.invoices (
branch_id INT NOT NULL,
invoice_id INT NOT NULL,
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2),
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=Spider
COMMENT='server "hq_server", table "invoices"';
Read from and write to external ODBC data sources from a Spider Node using virtual Spider Tables and the ODBC foreign data wrapper (Enterprise Server 10.5 and later).
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:
Supports a MariaDB foreign data wrapper. The MariaDB foreign data wrapper can be used to replace the older Federated and FederatedX storage engines.
Supports an ODBC foreign data wrapper in MariaDB Enterprise Server 10.5 and later. The maturity can be confirmed by querying the information_schema.SPIDER_WRAPPER_PROTOCOLS table.
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
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.
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 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";
View Foreign Data Wrappers (ES 10.5+)
SELECT * FROM information_schema.SPIDER_WRAPPER_PROTOCOLS;
Create ODBC Spider Table (with an Oracle remote server)
Follow the link under Operations for further information on the setup.
INSTALL SONAME 'ha_spider';
CREATE DATABASE spider_test;
USE spider_test;
CREATE OR REPLACE TABLE contacts (
contact_id BIGINT NOT NULL PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20),
customer_id BIGINT
) ENGINE=SPIDER COMMENT='wrapper "odbc", dsn "ORARDS", table "CONTACTS"';
Partition a large table across multiple MariaDB Enterprise Server Data Nodes using virtual Spider Tables and standard partitioning syntax for horizontal scalability.
Sharded MariaDB Enterprise Spider Topology
Overview
In the Sharded MariaDB Enterprise Spider topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried in this topology, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to Data Tables on Data Nodes. The data for the Spider Table is partitioned among the Data Nodes using the regular partitioning syntax.
Benefits
MariaDB Enterprise Spider:
Supports a MariaDB foreign data wrapper. The MariaDB foreign data wrapper can be used to replace the older Federated and FederatedX storage engines.
Supports an ODBC foreign data wrapper in MariaDB Enterprise Server 10.5 and later. The ODBC foreign data wrapper was backported to MariaDB Enterprise Server in a previous version. The ODBC foreign data wrapper is beta maturity. The maturity can be confirmed by querying the information_schema.SPIDER_WRAPPER_PROTOCOLS table.
The Spider Sharded topology:
Can be used to consolidate multiple tables on multiple MariaDB Enterprise Server nodes into a single "virtual" table on the Spider Node using the MariaDB foreign data wrapper.
Can be used to partition a large table across multiple MariaDB Enterprise Server nodes for horizontal scalability using the MariaDB foreign data wrapper.
In the Spider Sharded topology, a Spider Node contains one or more "virtual" Spider Tables. A Spider Table does not store data. When a Spider Table is queried in this topology, the Enterprise Spider storage engine uses a MariaDB foreign data wrapper to read from and write to Data Tables on Data Nodes. The data for the Spider Table is partitioned among the Data Nodes using the regular partitioning syntax.
The Spider Sharded topology consists of:
One MariaDB Enterprise Server node is a Spider Node
One or more MariaDB Enterprise Server nodes are Data Nodes
The Spider Node:
Contains one or more partitioned Spider Tables
Uses the Enterprise Spider storage engine plugin for Spider Tables
Uses a MariaDB foreign data wrapper to query the Data Tables on the Data Nodes
The Data Nodes:
Contain Data Tables for one or more partitions of the Spider Table
Use a non-Spider storage engine for each Data Table, such as InnoDB or ColumnStore
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 or .
ODBC Data Source
An ODBC Data Source relies on an ODBC Driver and an ODBC Driver Manager to query an external data source.
Example Use Cases
Shard Big Tables
The Spider Sharded topology can be used to split table data into multiple shards stored on remote MariaDB Enterprise Server nodes for horizontal scalability:
One MariaDB Enterprise Server node is configured as a Spider Node and accepts application queries.
One or more MariaDB Enterprise Server nodes are configured as Data Nodes and store shards.
Consolidate Data for Multi-Location Businesses
The Spider Sharded topology can be used to implement a consolidated view of multiple remote databases:
One MariaDB Enterprise Server is configured as a Spider Node and provides a consolidated view using Spider Tables.
One or more MariaDB Enterprise Server nodes are configured as Data Nodes and contain the local data.
Examples
Load Spider with Configuration File
[mariadb]
...
plugin_load_add = "ha_spider"
Load Spider with INSTALL SONAME
INSTALL SONAME "ha_spider";
View Foreign Data Wrappers (ES 10.5+)
SELECT * FROM information_schema.SPIDER_WRAPPER_PROTOCOLS;
Create Sharded Spider Table
CREATE SERVER hq_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.2",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "hq_sales"
);
CREATE SERVER eastern_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.3",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "eastern_sales"
);
CREATE SERVER western_server
FOREIGN DATA WRAPPER mariadb
OPTIONS (
HOST "192.0.2.4",
PORT 5801,
USER "spider_user",
PASSWORD "password",
DATABASE "western_sales"
);
CREATE DATABASE spider_sharded_sales;
CREATE TABLE spider_sharded_sales.invoices (
branch_id INT NOT NULL,
invoice_id INT NOT NULL,
customer_id INT,
invoice_date DATETIME(6),
invoice_total DECIMAL(13, 2)
payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
PRIMARY KEY(branch_id, invoice_id)
) ENGINE=Spider
PARTITION BY LIST(branch_id) (
PARTITION hq_partition VALUES IN (1) COMMENT = 'server "hq_server", table "invoices"',
PARTITION eastern_partition VALUES IN (2) COMMENT = 'server "eastern_server", table "invoices"',
PARTITION western_partition VALUES IN (3) COMMENT = 'server "western_server", table "invoices"'
);
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 uses foreign data wrappers to read from and write to Data Tables on Data Nodes or ODBC Data Sources.
