Tech Blog

Use Virtual Schemas to give you access to data wherever it sits

In today’s data architecture landscape many organizations have disparate data sources located across organizations and technologies. Often you run into on-premise legacy applications, a data lake, cloud data sources and even local files that aren’t maintained in one environment.

So how can we help you solve this issue?

With Exasol, you can utilize Virtual Schemas that create a read-only link to external sources. This allows you to create an abstraction layer that makes these external data sources accessible to you in Exasol without actually physically storing the data in Exasol.

With Virtual Schemas you establish a connection to a remote source, we retrieve metadata from the source and build the schema in Exasol. Your queries are pushed down to the source to execute as much SQL on the remote system as possible. This gives you online access to the most up-to-date data in your source systems and doesn’t require an ETL process to get insight from your data.

Hybrid cloud use case for Virtual Schemas

A use case that we often have for Virtual schemas is in a hybrid cloud setup. For example we could have an Exasol on-premises solution and an Exasol instance in the cloud where the on-premise solution may contain sensitive information whereas the cloud instance does not. A user connecting to the on-premises database can see all the data utilizing a virtual schema link to the cloud instance. The data is stored in the cloud but they are able to retrieve it for analysis through their on-premise system.  A user who connects to the cloud database would only see the cloud data.

In addition this doesn’t have to be just an Exasol to Exasol connection or an on-premise to cloud deployment. You could create virtual schemas that link up to other relational sources such as Oracle, or even Loading...Hadoop to your Exasol instance – we have a list of all available virtual schema connections on our public GitHub page.

How Virtual Schemas deliver a Loading...logical data warehouse

Because of the ability to use virtual schemas out of the box with Exasol you can utilize the database as a logical data warehouse that sits on top of all your data sources and gives you a holistic view of your data for analytics.

In the below use case we are establishing virtual schemas to Loading...relational databases and Hadoop where we access the data in Exasol. In the case that you have a slow data source, such as the D schema in Hadoop below, you can even replicate data with virtual schemas into Exasol. It can serve as an ad hoc light weight ETL tool where you can bring in data on the fly.

With this logical data warehouse all data is visible and can be combined in queries and used for analytics even though not all data is physically stored in Exasol.

These are just some of the use cases that Virtual Schemas can be used in to give you access to data wherever it sits.

What’s next?

Do you want to see how to spin up an Exasol cluster in the cloud and how to connect to an on-premise Exasol solution and Oracle database using Virtual Schemas? View a step-by step demo by signing up to our webinar here.