As Stewart mentioned in in post where-are-they-now-mysql-storage-engines : "Federated It’s still there… but is effectively unmaintained and dead. There’s even FederatedX in MariaDB which is an improvement, but still, the MySQL server really doesn’t lend itself kindly to this type of engine… it’s always been an oddity only suitable for very specific tasks."
The CONNECT storage engine is lifting the FEDERATED(X) limitations. It is no more a MySQL table only stuff.
It gives the MySQL access to local or remote MySQL table : in that case you can use a subset of columns, reorder them and have the limit clause correctly applied directly on the target.
But you now also have the capability to do an ODBC access to a data source.
This data source can be a local data source accessed through a DSN : Excel, Access, firebird . in that case the multiple ODBC tables concept apply. The concept of multiple table allows ODBC tables that are physically represented by multiple files. For instance to Excel or Access tables you ca views a set of monthly files as a single table.
This can also be any remote data source for which there is an ODBC driver : ORACLE, SQL Server, DB2 ...
What is nice is that these MySQL or ODBC type CONNECT tables can be mixed with the CONNECT TBL Type (Table List) table. This allow to execute queries distributed on multiple remote or local servers.
This is a very powerfull extension of the concept of MERGE table. Table can be of any type. Subset of columns can be used.