CREATE DATABASE my_example;
USE DATABASE my_example;
CREATE SERVER pgsql
VERSION 'one' FOREIGN DATA WRAPPER postgresql
OPTIONS (
"resource-name" 'java:/postgres-ds'
);
CREATE SCHEMA test SERVER pgsql;
IMPORT FOREIGN SCHEMA public FROM SERVER pgsql INTO test
OPTIONS(
importer.useFullSchemaName false,
importer.tableTypes 'TABLE,VIEW'
);Virtual databases
A virtual database (VDB) is a metadata container for components used to integrate data from multiple data sources, so that they can be accessed in an integrated manner through a single, uniform API.
A virtual database typically contains multiple schema components (also called as models), and each schema contains the metadata (tables, procedures, functions). There are two different types of schemas:
- Foreign schema
-
Also called a source or physical schema, a foreign schema represents external or remote data sources, such as a relational database, such as Oracle, Db2, or MySQL; files, such as CSV or Microsoft Excel; or web services, such as SOAP or REST.
- Virtual schema
-
A view layer, or logical schema layer that is defined using schema objects from foreign schemas. For example, when you create a view table that aggregates multiple foreign tables from different sources, the resulting view shields users from the complexities of the data sources that define the view.
One important thing to note is, a virtual database contains only metadata. Any use case involving Teiid Spring Boot must have a virtual database model to begin with. So, it is important to learn how to design and develop a VDB.
The following example of a virtual database model, defines a single foreign schema component that makes a connection to a PostgreSQL database.
The SQL DDL commands in the example implement the SQL/MED specification.
The following sections describe in greater detail how the statements in the preceding example are used to define a virtual database. Before that we need to learn about the different elements of the source schema component.
External data sources
As shown in preceding example, the "source schema" component of a virtual database is a collection of schema objects,
tables, procedures and functions, that represent an external data source’s metadata locally.
In the example, schema objects are not defined directly, but are imported from the server.
Details of the connection to the external data source are provided through a resource-name, which is a named connection reference to a external data source.
For the purposes of Teiid Spring Boot, connecting and issuing queries to fetch the metadata from these external data sources, Teiid Spring Boot defines/provides two types of resources.
Translator
A translator, also known as a DATA WRAPPER, is a component that provides an abstraction layer between the Teiid Spring Boot query engine
and a physical data source.
The translator knows how to convert query commands from Teiid Spring Boot into source-specific commands and execute them.
The translator also has the intelligence to convert data that the physical source returns into a form that
the Teiid Spring Boot query engine can process.
For example, when working with a web service translator, the translator converts SQL procedures from the Teiid Spring Boot
layer into HTTP calls, and JSON responses are converted to tabular results.
Teiid Spring Boot provides various translators as part of the system, or one can be developed by using the provided java libraries. For information about the available translators, see Translators.