What is ExaBucketFS?

So what is ExaBucketFS?

ExaBucketFS is essentially just a file system or store within the cluster. Files are automatically replicated across all nodes.

There are two main use cases (that I know of) to use ExaBucketFS. Firstly, it is as a repository for our in-house and third party libraries (e.g Python, Java, R etc). Secondly, it  allows us to store binary data, such as trained statistical models; where the Exasol database cannot traditionally store binary data.

Previously, before version 6, the Exasol cluster required access to the internet to be able to get the libraries used by UDFs. For custom libraries, you would have had to setup your own repository, and again be able to access it over the internet.

ExaBucketFS does require some administration though.

To use ExaBucketFS there is an API to allow you to put/get/remove files into the cluster. You can do this using curl. In the last post, I discussed how to use Curl to upload files to the bucket. The files added to the bucket are then accessible for use within UDFs.

Buckets can be password protected for reading or writing, or left public, although this to me seems like a purely DBA type task – get a password on them!

There is also the following do’s and don’ts for ExaBucketFS:

  • Ensure that you don’t write to buckets concurrently – Buckets are non-transactional.
  • Buckets and files do not get backed up – so you need to make sure you have this backed up somewhere else (by your own means!)
  • Don’t use ExaBucket as storage, as there is 100% replication across all nodes; meaning that if you store a file once, it will be replicated on every node – consuming your disk space.

Exasol’s knowledgeable Mathias Brink describes ExaBucketFS in one of the Exasol videos here.

HOW TO: Restart the licence node

Welcome back for the fourth instalment of the AdminSeries. Catch up on the previous posts here:

For this post we’ll be restarting the licence node.

To do this, I recommend you follow all the steps in the above posts beforehand.

That said, head over to the Nodes tab in ExaOperation. Scroll down past the nodes (if you have a few!).

restartlicence

In the License Nodes section, click Reboot. If all goes well, this will take down the page you are viewing, indeed all of ExaOperation (as it primarily resides on the licence node). You should be left with your browser’s page not found or something similar.

Again, I hope you have your patience ready. For me, this step takes around 5 minutes, in which cold sweats ensue (WTF have I done!). But it’s ok.

Refresh the page periodically until ExaOperation comes back.

This concludes Restarting the licence node.

HOW TO: Shutdown the Data Nodes

Time for the third post in the AdminSeries – how to shutdown the data nodes. This post follows the previous two: How to Shutdown the Database(s) and How to Shutdown the Storage. Before you read much further, go check them out first.

With that out the way, let’s get started.

In ExaOperation, head over to the Nodes tab. It should look something like this:nodeson

In this shot, you can see the State of the nodes is Running and they are Powered On.

Next up, select the checkboxes next to the Nodes that you want to shutdown. From the Actions drop down, select Shutdown, and then the button Execute.shutdownnodes

It may take some time for the nodes to shutdown, as with all things ExaOperation, be patient and wait for it. You can refresh the page and watch for the node state to change. Sometimes the Nodes change state, one at a time, so you may be waiting for some nodes.

Eventually your page should look like this. All of the Nodes will have the state of Installed, and Power Off. Your nodes are now Shutdown.

nodesoff

That brings us to the end of How to Shutdown Data Nodes. Next up, we’ll be looking at How to Restart the licence node.

HOW TO: Shutdown the Storage

The second post in the AdminSeries is going to be about how to Shutdown the Storage. Before you read this post, or are trying to do this, go back and make sure you have Shutdown the Database(s) first.

So, assuming that you have already Shutdown the Database(s), head over to the EXAStorage tab in EXAOperation (a lot of EXA’s going on there guys!).

It should look a little something like this:

storageon

I’ve got two databases, with three nodes each and a licence node. This means I have 6 volumes listed up top, and 7 disks from my nodes at the bottom. You may have more or less depending on your setup.

Click Shutdown Storage Service.

The page should then look like this:

exastorageoff

Here you can see the 7 nodes – 6 data nodes and 1 licence server.

Seeing the EXAStorage page look like this, means that the Storage is now stopped.

Having Shutdown the Database(s) and the Storage, we’ll move onto how to Shutdown the Nodes.

HOW TO: Shutdown the database(s)

This post is the first in a series of posts about how to administer Exasol. We’re going to be covering how to Shutdown databases, Shutdown storage, shutdown the nodes, restart the licence server, install CentOS patches and restart the whole lot. For all of the HOW TO: posts regarding administration, you will need access to ExaOperation.

Firstly, a quick disclaimer: This set of posts is based around 5.0.17, and so should be used in that context. If you are unsure about what you are doing, seek professional help. Do some of these things wrong, and you could jeopardise your data.

Ok, so without further ado…

In general, you should always shutdown the database first, before other shutdowns such as the storage etc. Obviously in the case of an unplanned system shutdown, you don’t have much choice.

So first log into ExaOperation, then head over to the EXASolution tab.

In my case, I have two databases running. I can tell that they are running because of the Status, and also that they are Online and behaving well – with the green light. Databases can be Running and Stopped separately. However, if you intend to perform further maintenance, like Shutting down all nodes or Stopping the Storage, you should stop them all.Inkeddatabasesrunning_LI

To Shutdown the databases, select the tick box next to the databases and then select Shutdown.

You can then manually refresh this page. It may take up to a minute to stop the databases.

When they are stopped the page looks like this:

Inkedstartupdbs_LI

We can tell that the databases are stopped, because they have a Status of “Created”, and the light indicating that the database is Online has gone out/grey.

So that’s it, your database has stopped.

The next post is then How to Shutdown the Storage.

HOW TO: Execute multiple commands from a file in EXASOL

In Exaplus we can execute multiple commands, by hitting the Execute All button, or by pressing CTRL + SHIFT + ENTER.

But what if we have a file of multiple commands? Should we copy the contents out and then execute them? Seems a bit of a faff.

Enter the @ command. Or also the START command, but I prefer @. A bit more catchy!

To run commands from a file in Exaplus, run the following:

@C:\folder\myfile.sql;

or if your file resides on FTP:

@ftp://my:ftp@ftp.scripts/myfile.sql;

I’ve found this command to be really handy, particularly in developing my own ‘automated deployment and comparison tool’ for Exasol. But that’s for another day!

Status

2016 round-up

SiteStats

Hi everyone! Thanks for continuing to read my blog. 2016 has been full of progress and meeting new people. We had the Exasol Xperience event in the summer, and the recent release of Version 6.

The blog has grown too! This year I’ve had over 2000 views of my Exasol related content, from literally all over the world. It’s great to see the adoption of Exasol expand, as I see your interest in my content. As always, I love getting your comments, and if you’d like to get in touch, please see the About section.

My role too has started to change at work. I am no longer only a Database Architect; I’m leading the Data team at Atheon, as well as being the Information Security Officer; hence why I’ve been a bit quiet on the posts recently! In the coming posts, I’ll be posting a link to a blog written by one of my upcoming developers, which I’m excited to share with you.

I’m looking forward to what 2017 has to bring, here’s to another good year!

What happens to the Identity value when you Truncate a table in Exasol?

Today was challenging at work for many reasons, but it was a good day for learning new things!

I thought I’d share with you a nugget that I learnt today.

When I truncate a table, I expect the all of the data to be permanently deleted, essentially like it was never there. For all indexes to be emptied, and any auto-incrementing constraints to be reset.

Let’s give it a go then… *simples*…no?

CREATE TABLE ID_TESTING(
ID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
A DECIMAL(18,0),
B DECIMAL(18,0)
);

I then insert 3 rows:

insert into ID_TESTING (A,B)
SELECT 1,2;
insert into ID_TESTING (A,B)
SELECT 2,2;
insert into ID_TESTING (A,B)
SELECT 3,2;

This gives me 3 rows, with an identity value of 3, which we can check by looking in one of the sys tables.

SELECT column_identity
FROM SYS.EXA_ALL_COLUMNS
WHERE column_schema = 'DUMMY'
AND column_table = 'ID_TESTING'
AND column_name = 'ID';

Finally, I truncate the table;

truncate table ID_TESTING;

So if we check the identity value this should be zero right? Using the same query as above, we can see it remains at 3.

This is obviously not ideal, if you’re loading large amounts of data, or have not set your identity column to be DECIMAL(18,0), or you’re just plain expecting a counter to start at 1. Having got in touch with Exasol about this, as it just doesn’t make sense to me, it seems it had been raised as an issue previously, logged as IDEA 177, but has been discounted. As such, it makes this behaviour, accepted.

If, like me, this doesn’t really work for you; we need a workaround…

…I’ll cover it in my next blog post!

 

HOW TO: Find out which features are covered by your Exasol licence?

Following on from the last post about how to build an Exasol equivalent of a Recursive CTE using Graph Search, you probably need to know if you have that feature available to you.

To do this, you’ll need access to ExaOperation.

In ExaOperation, head over to Configuration>Software>Licence.

licence

Here I’ve commented out the General licence info, for obvious reasons. Down the bottom, under Extra Database Features, you can see the Graph search option. Here it says ‘Unlimited’, but also valid is a date. ‘not available’ means that you do not have that functionality as part of your licence. To get features added to your licence, contact your Account Manager.