Error: [27002] constraint violation – primary key (PK_ on table TABLE) (Session: )

You might get this error if you enter in a duplicate value in your primary key column. Seems pretty standard and fair enough. But what about when you’re not?

This error can occur when you have an IDENTITY primary key column, that you have entered values into.

For example my table looked something like this:

CREATE TABLE DIM_TBL_STORE (
STOREID DECIMAL(18,0) IDENTITY NOT NULL ENABLE,
STORENAME VARCHAR(250) UTF8);
ALTER TABLE DIM_TBL_STORE ADD CONSTRAINT PK_DIM_TBL_STORE PRIMARY KEY(STOREID) ENABLE;

We can then check the value of the identity column; COLUMN_IDENTITY returns 0 as expected. All good.

select * from SYS.EXA_ALL_COLUMNS
where Column_schema = 'ROSE3'
AND COLUMN_TABLE = 'DIM_TBL_STORE'
AND COLUMN_NAME = 'STOREID';

Let’s then add some core stores with set IDs :

INSERT INTO DIM_TBL_STORE
SELECT 1, "StoreA"
UNION SELECT 2, "StoreB"
UNION SELECT 3, "StoreC"

SELECT * FROM DIM_TBL_STORE

This shows the 3 rows as expected.

What is unexpected is that the identity value is still zero. You can check this by running the exa_all_columns query, or looking at the info for the table in exaplus.

IdentityColumnWithZero

So next, let’s add a new store, this time not specifying a value for storeid.

INSERT INTO DIM_TBL_STORE (STORENAME)
SELECT 'STORED';

From this we get the error

Error: [27002] constraint violation - primary key (PK_ on table TABLE) (Session: )

Oops. Now we can’t enter any data, because the identity value being offered up by the table is 1. But 1 is taken! So, in fact, are 2 and 3. We need value 4.

To do this, the Identity value needs to be reset to a value after the max identity of the table.

ALTER TABLE DIM_TBL_STORE
ALTER COLUMN storeid SET IDENTITY 4;

Checking the exa_all_columns table, you can now see the value being 4.

The insert will now succeed.

In SQL server, we would have got around this by toggling IDENTITY_INSERT on or off, and SQL Server would have dealt with securing us the next unique value. With Exasol we’ve got to manage it a bit more.

HOW TO: Create an SFTP connection in Exasol

So you want to import or export files from an SFTP server? You need a connection.

The code is pretty simple:

ALTER CONNECTION MY_SFTP_CONNECTION
TO 'sftp://sftp.mysftphost.co.uk'
USER 'My_SFTP_User'
IDENTIFIED BY '********';

The user and identified by parts, are the user and password credentials that you would use to log into your SFTP.

However, to use the connection, perhaps from a script, executed by another Exasol user, you need to grant that Exasol user permission to use the connection, that you just created.

GRANT CONNECTION MY_SFTP_CONNECTION to ANOTHER_EXASOL_USER;

The Art of achieving the Impossible

ExasolMiniCluster

I am so pleased to announce that the Atheon Exasol Mini Cluster is now up and running!

Also known as the “Chinese Suitcase” and the “Atheon Holdall”, the Mini Cluster has been a few weeks in development, whilst I battled with a handful of minor issues.

The kit list for the Mini Cluster (in a 2+1 architecture) is:

The specs of the mini PC are:

  • Intel Core i7 (mobile processor)
  • 16GB RAM capacity
  • 2 gigabit NICs

The key things to note are:

  • 2 drives are required for the data nodes (only one for the Licence node)
  • 2 network cards are required for every node
  • An Exasol licence is required to operate the nodes together (talk to your Exasol Account manager, or get in touch and I’ll direct you, although this IS NOT SUPPORTED)

With costs in the cloud, looking to be a recurring £1600 per month, the total cost for the whole setup was around £2300, which will prove an invaluable saving for a small company. This cluster provides a great low-cost development environment, or could be used for a PoC, or as a “portable” (like TVs in the 90’s) Exasol implementation. More portable than a server rack in a flight case anyway!

Next up, is to get a mini rack machined out of aluminium for it, I think people might start to miss their Lego before too long!

I’d not have been able to build this without the help of the Exasol support guys, who were very patient with my constant persistence to build this, so big thanks to them.

“The difference between the impossible and the possible lies in a man’s determination.” – Tommy Lasorda

 

HOW TO: List all columns returned by a view or in a table

This week I was asked by a colleague to provide her a query that could list all columns returned by a view, along with their data types.

Exasol has a nifty in-built function called DESCRIBE, that does this, and best of all, it will list all columns in a table too.

Use it just like this:

DESCRIBE schema.OBJECT;

It will return a result set of COLUMN_NAME, SQL_TYPE, NULLABLE and DISTRIBUTION_KEY.

I love this multi-purpose function, but I’d love it even more if it had a COLUMN_POSITION column too.

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: Creating an ODBC connection string for Exasol

A prerequisite to creating an ODBC connection, is to have the Exasol Driver installed on the machine; where the connection will originate from. You can get the current (5.0.14) Driver from Exasol here

The ODBC connection string has 5 parts:

  • Driver
  • UserName
  • Password
  • ExaHost
  • Database

The 2 notable parts are the driver and ExaHost. You check the appropriate name for the Driver, by going to x64 ODBC Data sources Adminstrator >  Drivers, and then looking at the name given to EXAODBC.DLL

The EXAHost is the node IP connection string, for example 192.168.0.11..13. Using a node connection string in this manner requires the nodes to be on consecutive IP addresses.

Driver={EXASolution Driver};uid=USERNAME; pwd=PASSWORD; EXAHOST=192.168.0.11..13; database=DATABASENAME;

How To: Create and assign a role to a user in EXASOL

Having created a user, permissions can be assigned to them either directly, or by creating a role and assigning it to the user. This means that a set of permissions can be assigned to multiple users.

Having created a user, and granted them the permission to create a session, run the following:

CREATE ROLE RoleNameHere;

Next up, grant permissions to the Role.

GRANT CREATE SCHEMA
, CREATE TABLE
, CREATE VIEW
, CREATE SCRIPT
, CREATE FUNCTION
, SELECT ANY TABLE
TO RoleNameHere;

Finally, grant the role to the user:

Grant RoleNameHere to UserNameHere;

How To: Create a New User account in EXASOL

The first step to creating a user is quite simple:

CREATE USER UserNameHere identified by "pwd";

Without the double quotes the password is stored as upper case, and the user would receive an authentication error when they try to login with the lower or mixed case password.

Next, the user needs to be granted the permission to create a session. This allows them to connect to the database.

GRANT CREATE SESSION to UserNameHere;

This allows the user to connect, but as of yet does not allow them to do anything else.

Image

HOW TO: Comment and uncomment shortcuts in EXASOL

To comment in Exasol there are a couple of options. There are the manual options of — and /* */.

There a couple of shortcuts also:

To single line comment, click on or select the line in question and then type CTRL+SHIFT+MINUS

To multi line or block comment, select the block and then type CTRL+SHIFT+B.

To uncomment from a single line comment, highlight or click on the line in question and then again type CTRL+SHIFT+MINUS

To uncomment from a multi line or block comment, again , select the block and then type CTRL+SHIFT+B.

Happy Coding!