BACK

2018-02-23

- By Nicolas Lagoutte

This tutorial is aimed at beginners. If you're new to EXASOL and want to understand how to create your first database and interact with it, this guide is for you. By the end of the tutorial you will have a local EXASOL instance running with some test data, and basic website displaying some of that data. This tutorial is using Windows 10.

Setting up your first EXASOL Database

Our first step will be to set up create a database. Running in a virtual machine. I can already hear most people recoiling from their keyboards (except you SysAdmins in the corner) - don't worry! It's a lot simpler than it sounds. Start by downloading the following:

1. Virtualisation software. There are several alternatives available (VirtualBox, VMWare, KVM...). This tutorial will use VirtualBox.

2. EXASOL Community Edition, which is a one-node EXASOL installation. It runs on a custom operating system, hence the need for a virtual machine. It is available as a virtual machine image (.ova) or a disk image (.iso) which you will run with VirtualBox. The .ova version comes with the license pre-installed, so download that one for now.

Once you've installed VirtualBox, the simplest way to import the EXASOL image is to double click the .ova file in Windows Explorer. This will automatically open VirtualBox and display the following screen:


Click on Import, and accept the License Agreement. You now have a pre-configured virtual machine ready to be launched...

... Or so you might think. It turns out there's a little more configuration we're going to have to do ourselves, but we're almost there. We need to enable a network connection to the virtual machine.

Select vm (Powered Off) in the left column of VirtualBox, and click Settings, then Network.

In the Attached To: dropdown, select NAT.

Next, click on Advanced, then Port Forwarding. EXASOL requires use of ports 8563 and 443, so we need to create 2 rules, one for each port. Your rules should look like this: 

 

Rage Against the Virtual Machine

Brace yourself, cross your fingers, select vm (Powered Off) in the left column of VirtualBox, and click Start.

If you get an error similar to this:

Failed to open a session for the virtual machine vm. VT-x is disabled in the BIOS for all CPU modes (VERR_VMX_MSR_ALL_VMX_DISABLED). Result Code: E_FAIL (0x80004005) Component: ConsoleWrap Interface: IConsole {872da645-4a9b-1727-bee2-5585105b9eed}

Then you probably need to enable Virtualisation in your BIOS. Generally speaking this involves rebooting your computer, mashing the DEL, ESC and F-keys repeatedly until your BIOS loads, then finding the Virtualisation option and enabling it. For more specific instructions, please Google your particular make of computer/motherboard :) 

If your VM's screen looks like this (no Public IP, no Netmask, no Default Gateway) and blinks every few seconds:


Then your virtual machine has no network connection. Click File, Close, then Power off the machine. With your VM still selected, click on Settings then Network and make sure they correspond to the network settings above.

 

Once you see the following screen:


Congratulations! Your database is now running locally and ready to be used.

Connecting to the Database with ExaPlus

Great! You've now got a database up and running. Very exciting stuff. But how do you do, well, anything with it? How do you start creating some tables, adding some data, and querying it?

The simplest way of doing this is using EXASOL's own user interface, ExaPlus. It is available as both a command line tool and a desktop application. Given that this is a Windows tutorial, I'm going to focus on the desktop version. The Linux tutorial will cover the command line interface.

Let's get started. Go to the EXASOL download portal, then download the latest version of ExaPlus. This tutorial is using ExaPlus 6.0.5.

Open ExaPlus, and enter the following connection details:


The default password is exasol.

ExaPlus' main UI presents you with a SQL editor. We are going to use it to create a schema with a table. Type the following code into the SQL Editor and click the Execute All Statements button.

Your screen should look like this:


We now have a table in our database! Let's add a few rows of data, again using the SQL Editor:

INSERT INTO TEST_TABLE VALUES (01, 'John A.');

INSERT INTO TEST_TABLE VALUES (02, 'Alice B.');

Feel free to run a simple SELECT query to test your table:

SELECT * FROM TEST_TABLE;

You should see the following:


Huzzah! We now have a working table. At this point we have a working instance of the EXASOL stack, and if this is enough for our business purposes that's fine. But what if we want to go a step further, and start building larger software stacks on top of this? EXASOL supports ODBC and JDBC connections, and also provides a JSON over WebSockets API. The next section will be looking at the latter.

Connecting to the Database with JavaScript WebSockets

This is where the really fun stuff happens. Lean back, do a few stretches, go refill your mug (or pint glass) with your poison of choice, and let's get cracking.

Tools, More Tools

If you already have your favourite tools installed, feel free to skip this section.

For everyone else, here is what we need to install:

1. A JavaScript-friendly IDE. I recommend Visual Studio Code.

2. A HTTP Server. The simpler the better. This tutorial will use NodeJS.

Once you have your tools setup, create a new project folder and open it in your code editor. If you are using VS Code, press CTRL + ' to also open the integrated terminal.

Create an index.html file in your project directory, and write the following code into it:

This is a minimalistic HTML template that we will use to test our development environment before actually implementing our code. Open a terminal window in your project folder (it should already be open if you're in VS Code). It's time to install a HTTP server in order to run our web application. If you have one already installed, please skip this step.

We are going to use a handy little tool called npm (Node Package Manager) in order to install our server. It comes pre-installed with NodeJS. To make sure npm is installed, type npm --version into your terminal. It should return a version number, e.g. 5.6.0. If you don't have npm installed, please make sure you've installed NodeJS as per above.

Next, let's install http-server, a npm package that does... exactly what it says on the tin. In your terminal, type

npm init -y

This will create a package.json file in your folder, which npm uses to manage your dependencies. To install http-server type:

npm install -D http-server

 

Time to test! Make sure your terminal pointing at your top-level project directory, which should contain index.html and package.json, and type:

http-server -o -c-1

This command should automatically open your default browser and serve your index.html page. Did it work? If you can see a blank webpage with "Test Paragraph." written on it, congratulations! Your basic development environment is all set.

This Is The Part Where We Write The Code

This is the part where we write the code. Well, mostly. Actually, most of the code necessary for this is already written for us by EXASOL, as the GitHub stalkers among you will already know.

Start by cloning or downloading the websocket-api repository. Place the folder inside your top-level project folder.

It's time to import this library into our web application. We will also be using a simple library called jquery. For the purposes of this project, simply importing using script tags is fine. In index.html, add the following lines at the bottom of your body tag, making sure the folder name (in my case, websocket-api-master/) is correct:

Now that everything is imported, we will need to write some JavaScript code to connect to our EXASOL instance. Let's keep it simple and just open a new script tag below the other ones:

The websocket-api library allows us to create Exasol objects that represent database connections. They take 3 arguments, a connection string, user name and password. The fourth argument is a callback function for us to use once the connection has been successfully established, and the fifth argument is a callback function that fires off if there are any errors while connecting. Let's create one such object as soon as the webpage is loaded:

Try running your webpage now (quick reminder: http-server -o -c-1). Open the web development console (in Chrome, CTRL + SHIFT + I. You should be able to spot your "Connected successfully..." log. If you get an error, make sure that your EXASOL virtual machine is up and running.

Connecting to the database was the first step. Let's build on this using a few more of the websocket-api objects which allow us to run SQL commands directly. I also think it's more satisfying to see your hard work actually affect the contents of the webpage, rather than just display console debug logs. Let's make the text on the webpage update depending on whether the connection was successful or not.

In index.html, replace <p>Test paragraph.</p> with the following line:

<p id="success-text">Ready to connect.</p>

Assigning an ID to the <p> element will allow us to select it more easily with jquery.

 

Inside the callback function that triggers on successful connection (where your console.log('Connected: ' + context); statement is), add the following code:

The first line is our old console.log command, unchanged.

$("#success-text").text("Connection to EXASOL database successful.") will replace the text on the page with text to let you know the connection was successful.

exa.com allows us to write SQL queries and receive replies over the WebSocket in JSON format. Just like the Exasol object, exa.com provides two callback functions, for success and for error. This code has two queries. First, OPEN SCHEMA TEST_SCHEMA opens the schema, and SELECT * FROM TEST_TABLE returns all the rows that we created earlier.

Run the code. If everything goes well, you should see something like this: 

Congratulations

Well done! You now have a basic web application pulling data from an EXASOL instance using JSON over WS. Here's what the final index.html code should look like:

1 comments

Posted by Nicolas Lagoutte on 2018-04-09

Feel free to post any questions in the comments section.

Please be aware that we will be processing your input data in accordance with our privacy policy.