Virtual Schemas are an abstraction layer that makes external data sources accessible in our data analytics platform through regular SQL commands. The contents of the external data sources are mapped to virtual tables which look like and can be queried as any regular Exasol table.
After creating a virtual schema, you can use its tables in SQL queries and combine them with persistent tables stored in Exasol, or with any other virtual table from a different virtual schema. The SQL optimizer translates the virtual objects into connections to the underlying systems and implicitly transfers the required data. SQL conditions are pushed to the data sources to ensure minimum data transfer and optimum performance.
This concept creates a type of logical view on top of several data sources that could be databases or other data services. Using the virtual schema, you can implement a consistent access layer for your reporting tools. You can also use it for agile and flexible ETL processing because you don't need to change anything in Exasol if you change or extend the objects in the underlying system.
The Virtual Schema translates the Exasol SQL into the SQL of the remote database. Your query is rewritten into a new query that uses the IMPORT statement. You can also load data directly using these generated statements. To see the generated query ,use the EXPLAIN VIRTUAL statement.
Exasol provides an open and extensible framework where the connectivity logic is open sourced. In this framework, you can use the available adapters or optimize them according to your requirements without waiting for new release from Exasol.
Exasol provides you with different data sources for Virtual Schema JDBC adapters. To know more about them, see Supported Data Sources.
The following sections cover the procedures and information that you need to use virtual schemas:
You can also use the IMPORT statement to load data into the database