Database federation
Querying several heterogeneous databases as one without copying their data: global schema, mediator, adapters and foreign tables, query splitting, filters pushed to the source, and what sets federation apart from a warehouse.
- 29 cards
- 6 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- On the diagram
- Scenarios
- Understand
A few cards from the deck
Federated database: what is it?
Show the answer
A system that presents several autonomous, heterogeneous databases as one, and answers each query by asking the sources at the moment it arrives.
The data stays with its owners: nothing is copied. The flip side is that the answer depends on each source's availability and speed.
In PostgreSQL, what does the last command of this script do?
```sql
CREATE EXTENSION postgres_fdw;
CREATE SERVER stock_srv FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'stock.example.org', dbname 'stock');
CREATE USER MAPPING FOR CURRENT_USER SERVER stock_srv
OPTIONS (user 'lecteur', password 'secret');
IMPORT FOREIGN SCHEMA public LIMIT TO (articles)
FROM SERVER stock_srv INTO public;`- It declares the articles table locally as a foreign table, without copying its rows
- It copies the articles table and all its rows into the local database to query them faster
- It creates the server and the stock database on the remote machine, then sends the articles table to it
- It continuously synchronises the remote database's public schema
Show the answer
It declares the articles table locally as a foreign table, without copying its rows
IMPORT FOREIGN SCHEMA creates foreign table definitions from the remote catalogue. No row is copied: each query on articles goes to read them at the source.
An … translates the mediator's sub-queries into its source's dialect and returns the results in the format of the ….
Show the answer
An adapter translates the mediator's sub-queries into its source's dialect and returns the results in the format of the global schema.
There is one adapter per type of source (PostgreSQL, MySQL, file…), which lets the mediator talk to engines that do not share the same SQL.
Mediator: what is it?
Show the answer
The component that receives the single query, splits it into sub-queries sent to the sources, then assembles the partial results.
It acts as conductor: the sources do not know each other, so it performs the join of the results when it cannot be done at the source.