Exasol

This is the second part of the mini-series Exasol on AWS. Here’s the first part.

Cloud UI is an extension to EXAoperation that makes it easy for you to

• Scale up & down
• Increase storage capacity
• Scale out by adding nodes to the cluster

Cloud UI can be reached by adding the port number 8835 to the URL of your License Server and uses the same credentials as EXAoperation.

Scale down to m5.large with Cloud UI

Depending on the load you get on your Exasol cluster, you can scale up your data nodes to more powerful EC2 instances if load is high and scale down to less expensive EC2 instances with lower user demands.

I started my little cluster with r5.large instances. Now I want to scale down to m5.large. Enter Cloud UI:

It’s amazingly easy to run an Exasol Cluster on Amazon Web Services (AWS).

Subscribe Exasol in AWS marketplace

After having registered and having logged in to your AWS account, go to the AWS marketplace and search for Exasol:

Adding a cluster node will not only increase the available storage capacity but also the total compute power of your cluster. This scale-out is a quite common operation for Exasol customers to do.

My example shows how to change an existing 2+1 cluster into a 3+0 cluster. Before you can enlarge the database with an active node, this node has to be a reserve node first. See here how to add a reserve to a 2+0 cluster. Of course you can add another reserve node to change from 3+0 to 3+1 afterwards. See here if you wonder why you may want to have a reserve node at all.

Initial state – reserve node is present

I start with a 2+1 cluster – 2 active nodes and 1 reserve node:

After having installed a 2+0 Cluster in Hyper-V, now let’s expand that to a 2+1 Cluster.

Add the node as a VM first

Add another VM in Hyper-V with the same attributes as the existing two data nodes n11 and n12 and name it n13:

• 2500 MB memory, not dynamically extending
• Legacy network adapter, connected to edu-cluster-interconnect
• Network adapter, connected to edu-public-network
• Boot order with legacy network adapter first
• Two hard disk of type VHDX with max. size 100 GB

Give it a static MAC for the Legacy Network Adapter 08:00:27:58:03:21 and a static MAC for normal Network Adapter 08:00:27:71:27:26 and make sure they do not conflict with other existing MAC addresses in your environment.

After having installed Hyper-V, an ISO file with the Exasol software needs to be downloaded. The 2+0 cluster consisting of two data nodes and one license server needs a host machine with at least 8 GB memory and 60 GB free disk space. I do it on my Windows 10 notebook with 16 GB memory. This is for educational purposes of course and not suitable for production use.

2+0 means two active nodes and no reserve node. A reserve node can be added later, expanding the environment to a 2+1 cluster.

We’ll start adding two virtual switches to be able to connect the network adapter of the VMs to them later.

Create Hyper-V Switches for Private and Public Network of the cluster

Click the Virtual Switch Manager in Hyper-V Manager:

Oops, we did it again

• Exasol
• TOI

As a Database Developer or Database Administrator, it becomes increasingly unlikely that you will work with only one platform.

It’s quite useful to have one single tool to handle multiple different database platforms. And that’s exactly the ambition of DbVisualizer.

As a hypothetical scenario, let’s assume you are a database admin who works on a project to migrate from Oracle to EDB Postgres and Exasol.

The goal might be to replace the corporate Oracle database landscape, moving the OLTP part to EDB Postgres and the DWH / Analytics part to Exasol.

Instead of having to switch constantly between say SQL Developer, psql and EXAplus, a more efficient approach would be using DbVisualizer for all three.

Many of our customers are using Oracle together with SQL Developer, so this question comes up regularly: Can we use SQL Developer also for Exasol?

Short answer is: Unfortunately not.

I tried myself to make that work with no success. Then I found this on Stackoverflow:

Jeff Smith: “No, that’s not supported. SQL Developer’s 3rd party JDBC connectivity is provided for one use case – migrations to Oracle Database.
There’s no support on that for Exasol DB, so there’s no connectivity support provided.
If you want a generic jdbc db client, that’s not Oracle SQL Developer.” [Highlighted by me]

Why should you bother? Because Exasol is the fastest analytical database in the world, outperforming any competitor. Therefore, expertise about Exasol might soon be very valuable also in your company.
Free training helps us to spread the knowledge in a scalable way, empowering customers across the globe to get the best out of Exasol and supporting our rapid growth.

You can register here. The free online courses are branded as “Exacademy”:

An Exasol database will automatically create, maintain and drop indexes, following the core idea to deliver great performance without requiring much administrative efforts. Like our tables, our indexes are always compressed and you don’t need to configure anything for that.

Joins between two or more tables are processed like this in Exasol: One table is full scanned (this is called the root table) and the other tables are joined using an index on their join columns.

If these indexes on the join columns are not already existing, they are automatically created during the join operation. Taking two tables t1 and t2 as an example, and a statement like