The technique to recover only certain objects from an ordinary backup is called Virtual Access Restore. Means you create a database from backup that contains only the minimum elements needed to access the objects you request. This database is then removed afterwards.
Let’s see an example. This is my initial setup:
One database in a 2+1 cluster. Yes it’s tiny because it lives on my notebook in VirtualBox. See here how you can get that too.
It uses the data volume v0000 and I took a backup into the archive volume v0002 already.
I have a schema named RETAIL there with the table SALES:
By mistake, that table gets dropped:
And I’m on AUTOCOMMIT, otherwise this could be rolled back in Exasol. Virtual Access Restore to the rescue!
First I need another data volume:
Notice the size of the new volume: It is smaller than the overall size of the backup respectively the size of the “production database”! I did that to prove that space is not much of a concern here.
Then I add a second database to the cluster that uses that volume. The connection port (8564) must be different from the port used by the first database and the DB RAM in total must not exceed the licensed size, which is limited to 4 GB RAM in my case:
I did not start that database because for the restore procedure it has to be down anyway. Clicking on the DB Name and then on the Backups button gets me here:
No backup shown yet because I didn’t take any backups with exa_db2. Clicking on Show foreign database backups:
The Expiration date must be empty for a Virtual Access Restore, so I just remove it and click Apply. Then I select the Restore Type as Virtual Access and click Restore:
This will automatically start the second database:
I connect to exa_db2 with EXAplus, where the Schema Browser gives me the DDL for the table SALES:
I take that to exa_db1 and run it there, which gives me the table back but empty. Next I create a connection from exa_db1 to exa_db2 and import the table
create connection exa_db2 to '192.168.43.11..13:8564' user 'sys' identified by 'exasol'; import into retail.sales from exa at exa_db2 table retail.sales;
This took about 2 Minutes:
The second database and then the second data volume can now be dropped. Problem solved!
Exasol introduced Partitioning in version 6.1. This feature helps to improve the performance of statements accessing large tables. As an example, let’s take these two tables:
Say t2 is too large to fit in memory and may get partitioned therefore.
In contrast to distribution, partitioning should be done on columns that are used for filtering:
ALTER TABLE t2 PARTITION BY WhereCol;
Now without taking distribution into account (on a one-node cluster), the table t2 looks like this:
Notice that partitioning changes the way the table is physically ordered on disk.
A statement like
SELECT * FROM t2 WHERE WhereCol=’A’;
would have to load only the red part of the table into memory. This may show benefits on a one-node cluster as well as on multi-node clusters. On a multi-node cluster, a large table like t2 is distributed across the active nodes. It can additionally be partitioned also. Should the two tables reside on a three-node cluster with distribution on the JoinCol columns and the table t2 partitioned on the WhereCol column, they look like this:
That way, each node has to load a smaller portion of the table into memory if statements are executed that filter on the WhereCol column while joins on the JoinCol column are still local joins.
EXA_(USER|ALL|DBA)_TABLES shows both the distribution key and the partition key if any.
Notice that Exasol will automatically create an appropriate number of partitions – you don’t have to specify that.
Your BI users complain about slow performance of their analytical queries? Is this your Status Quo?
tableau was taken as a popular example for AdHoc analytics but it might be any of the others like MicroStrategy, Looker, you name it. The good news is that this problem can be solved quite easily and without having to spend a fortune trying to speed up your legacy DWH to keep up with the BI demands:
Using Exasol as a High Performance Sidecar to take away the pain from your BI users is the easy and fast cure for your problem! This is actually the most common way how Exasol arrives at companies. More often than not this may lead to a complete replacement of the legacy DWH by Exasol:
That’s what adidas, Otto and Zalando did, to name a few of our customers.
Don’t take our word for it, we are more than happy to do a PoC!