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!

Link

Another GUI, another blog

HNY2017

First off, Happy New Year from me in France! I’m out in Les Arcs, skiing for the week (best way to start the year back at work if you ask me!!).

So, in my round up of 2016, I promised you a look at a blog written by one of the guys on my team at Atheon. Vinit has written in his blog about another GUI which can interface with Exasol. An alternative to Exaplus if you will. And, best of all it comes with intellisense! I’ll let Vinit tell you the rest over at his blog optimumretrieval.

 

HOW TO: Recursive Queries Using Common Table Expressions (CTE)

CTEs are pretty standard. Recursive CTEs are pretty mainstream too. They’re great for calculations amongst a hierarchy, think manger-employee type relationship; both are employees and as such sit in the same table. To analyse this hierarchy, you would need a recurring self join. This same technique can be used to analyse non-hierarchical problems too. Microsoft have got a pretty comprehensive guide to Recursive CTEs here, if you would like to understand that first.

So, the problem: Charity shops have tins, for donations on the counters. Every day that donations are made, the total donation is emptied from the tin, and the total donation recorded against a store and a day. Looking back, we now want to know how many days it is until the next donation is made, and from then, the next and so on in each store. Then we could do some trend analysis on that data about how often stores are receiving donations.

Let’s have a look at the sort of thing we might have wanted to do before in SQL Server, and then we’ll get into how we’ll go about doing it in Exasol.

This is the setup code for SQL:

CREATE TABLE TBL_STOREDONATIONS (
    STOREID       DECIMAL(18,0),
    DONATIONDATE  DATE,
    DONATIONVALUE DECIMAL(18,2)
);

INSERT INTO CTE_EXAMPLE.DBO.TBL_STOREDONATIONS
SELECT 1, '2016-01-12', '3.00'
UNION ALL SELECT 1, '2016-01-20', '5.00'
UNION ALL SELECT 1, '2016-01-20', '4.20'
UNION ALL SELECT 2, '2016-01-14', '1.00'
UNION ALL SELECT 2, '2016-01-15', '3.40'
UNION ALL SELECT 1, '2016-01-29', '5.50'
UNION ALL SELECT 1, '2016-01-30', '2.10'
UNION ALL SELECT 3, '2016-01-17', '7.80'
UNION ALL SELECT 3, '2016-01-27', '1.00'
UNION ALL SELECT 3, '2016-01-28', '1.20'
UNION ALL SELECT 4, '2016-01-30', '5.44'
UNION ALL SELECT 1, '2016-02-03', '1.23';

Here’s the code for the Recursive CTE:

WITH Donations (StoreID, DonationDate, DonationOrder, DaysToNext)
AS
(
-- Anchor member definition
    SELECT  e.StoreID
		, e.DonationDate 
		, rnk
		, null
    FROM tbl_storedonations AS e
	INNER JOIN(
		SELECT Storeid
		, donationdate
		, DENSE_RANK() OVER (PARTITION BY storeid ORDER BY donationdate DESC) rnk
		FROM TBL_STOREDONATIONS
		)rnkd
	ON e.storeid = rnkd.storeid
	AND e.donationdate = rnkd.donationdate
	WHERE rnk =1 --get last donation    
    UNION ALL
-- Recursive member definition
    SELECT e.StoreID
		, e.DonationDate
		,rnk
		,DATEDIFF(dd, e.donationdate,d.DonationDate) 
    FROM dbo.TBL_STOREDONATIONS AS e
	INNER JOIN(
		SELECT Storeid
		, donationdate
		, DENSE_RANK() OVER (PARTITION BY storeid ORDER BY donationdate DESC) rnk
		FROM TBL_STOREDONATIONS
		)rnkd
	ON e.storeid = rnkd.storeid
	AND e.donationdate = rnkd.donationdate
    INNER JOIN Donations AS d
    ON e.StoreId = d.StoreID
	AND rnk = donationorder + 1
)
-- Statement that executes the CTE
SELECT storeid
, donationdate
, daystonext
FROM Donations
ORDER BY storeid ASC, donationdate DESC
GO

Here’s the output, exactly what we wanted.

SQL_CTE_OUTPUT

Unfortunately, this is not natively supported in Exasol, using recursive CTEs.

There is a light at the end of the tunnel though. We can build something we the same output, by using the Exasol feature, Graph search.

Here’s the Exasol setup code to this demo:

CREATE SCHEMA CTE_EXAMPLE;

CREATE TABLE TBL_STOREDONATIONS (
    STOREID       DECIMAL(18,0),
    DONATIONDATE  DATE,
    DONATIONVALUE DECIMAL(18,2)
);

INSERT INTO CTE_EXAMPLE.TBL_STOREDONATIONS
SELECT 1, '2016-01-12', '3.00'
UNION ALL SELECT 1, '2016-01-20', '5.00'
UNION ALL SELECT 1, '2016-01-20', '4.20'
UNION ALL SELECT 2, '2016-01-14', '1.00'
UNION ALL SELECT 2, '2016-01-15', '3.40'
UNION ALL SELECT 1, '2016-01-29', '5.50'
UNION ALL SELECT 1, '2016-01-30', '2.10'
UNION ALL SELECT 3, '2016-01-17', '7.80'
UNION ALL SELECT 3, '2016-01-27', '1.00'
UNION ALL SELECT 3, '2016-01-28', '1.20'
UNION ALL SELECT 4, '2016-01-30', '5.44'
UNION ALL SELECT 1, '2016-02-03', '1.23';

Here, we’re going to use the Graph Search keyword of CONNECT BY. We’re telling Exasol to join the single result set, back on itself, on the StoreId attribute and the ranking r_instore, by looking for the next instance of a donation in store. r_instore is also used to start the calculation at the last instance of a donation in a store.
This code is undoubtedly tidier and much more readable than the SQL Server equivalent.

SELECT * FROM(
SELECT 
d.StoreId
, d.DonationDate
, d.r_instore
, DAYS_BETWEEN(PRIOR donationdate, donationdate) AS DaysToNext
FROM (
SELECT distinct
StoreId
, DonationDate
, DENSE_RANK() OVER (PARTITION BY StoreId ORDER BY DonationDate desc) r_instore
FROM tbl_StoreDonations
) d

CONNECT BY
StoreId = PRIOR StoreId and
r_instore = PRIOR r_instore + 1

START WITH
r_instore = 1)DTN
ORDER BY STOREID ASC
, DONATIONDATE ASC;

Here’s the output from Exasol, again just what we needed.EXA_CTE_OUTPUT

It is important to note, that to use this functionality, you will need Graph Search as part of your Exasol licence.

Error “Can not find the Java Runtime Environment needed by EXAplus GUI. Trying to use system wide virtual machine (from registry) instead. No 64 bit java virtual machine found in registry.”

Don’t you just love that new machine setup process…said nobody ever. Today I got hold of my new machine at work (which is great, thanks guys!). First job is to get all of the programs on that I need.

After the essentials, I got round to installing Exaplus. You can get it from here

I ran the install and got this error on completion:

Can not find the Java Runtime Environment needed by EXAplus GUI. Trying to use system wide virtual machine (from registry) instead. No 64 bit java virtual machine found in registry.

This rang a bell, and was quite similar to this error I’ve had before.

This time round, it was because I didn’t have the 64 bit Java. To fix it, I got the 64 bit download for Java from here.

The install did not fail, it was just the opening of Exaplus that was causing the error. With 64 bit Java installed, Exaplus loaded fine.,

 

 

HOW TO: Make sure EXAPlus aborts execution on Error

ErrorHandling

It surprised me to learn that by default, on executing multiple commands in EXAPlus, the execution does not stop on error.

I was happily executing batches of code and thinking that everything was fine, as it all executed so quickly, and the last message displaying in the log panel was successful, so therefore, everything worked out…right? WRONG!

My code had a bug in one of the middle scripts, but despite the execution encountering the error, the execution as a whole continued.

Luckily, this was highlighted to me at exaxp16, by one of the support team.

So, here’s how to set the flag, to abort execution on error in EXAPlus:

In EXAPlus, go to SQL > Error Handling > All Errors > Select Abort

HOW TO: Create a table in EXASOL

Using EXAplus,  select the New connection icon as shown below, or alternatively go to EXAplus in the ribbon, then select Connect from the list.

connecticon

Next up, select Templates from the left hand menus. This then provides you with most basic scripts to get started. We’re going to select CREATE TABLE(…)

createtable

This then populates the SQL Editor window with some preconfigured SQL.

In this case, EXAplus has added the template to the end of some code I already had.

OPEN SCHEMA x; is the SQL Server equivalent of a USE statement.

EXASOL has the nifty conception of CREATE or REPLACE, kind of like an IF EXISTS DROP CREATE, in one word.

Right now, we’re going to create a table, in the schema schema_test, called tbl_Clients. Each client will have an ID and Name.

Here’s the code snippet:

open schema schema_test;

CREATE OR REPLACE TABLE tbl_Clients
(
ID int identity primary key
, Name nvarchar(50)
);

Then click the Execute All Play button.

Here’s what it looks like in EXAplus.

 

createtable2

The new table is now visible in the Table list in the Database Browser, as is the execution history, to the bottom of the screen.

Exaplus

EXASOL has it’s own application for development. It’s called EXAplus. You can download the latest version of EXAplus from the EXASOL site here.

exaplus

EXAplus is available for both Linux and Windows. EXAplus also has a console application to supplement the graphical application.

EXASOL have a brief tutorial here on the basic features of EXAplus.

In the next few posts I’ll be looking at how to really get started with EXASOL and EXAplus.

HOW TO: Inserting data into EXASOL (Part 1)

There are quite a few options to get data into EXASOL. The three main ones being, inserting directly via Exaplus, Importing from a text file and Importing from an existing database external to EXASOL.

EXASOL comes with drivers to connect to most existing databases, for example, there are pre-configured JBDC, ODBC and .NET, with the ability via EXAoperation to extend that.

To start with I’m going to look at how to insert data into EXASOL via EXAplus. If you haven’t already please go back and look at How to Create a table. Assuming you’ve got a table in place, let’s get started.

Syntax 1) INSERT INTO tablename (cols) VALUES (vals);

insert1

Syntax 2) INSERT INTO tablename VALUES (vals);insert2

Syntax 3) INSERT INTO tablename SELECT vals;insert3

If you know of any more please let me know and I’ll tag them in. Next up, I’ll be covering inserting data into EXASOL from a text file or CSV.