EXASOL upload and write speed issues

Hi,

I have been having some problem with the speed writing to Exasol both through the normal and in-DB output tools.

1) The normal output tool seems to struggle as I upload entire data sets and what takes minutes to upload through the Exasol client takes over an hour in Alteryx if not more. Has anyone encountered this problem and have a solution. Currently I have to exit Alteryx and write SQL to upload or wait for hours to load data.

2) Once the data is in Exasol I find a steep performance drop in the write tool. This seems to occur once the width of the data (in numberr of columns) or the workflow complexity exceed a critical point. I have already looked to minimise browse tool and field sizes, so any further advice on how to improve performance would be appreciated, as the write tool is making up 90-95% of run time at the moment (although my main concern is around the normal output tool, not in-DB)

Thank you!

Output

In Database

Database Connection

In-Database

Accepted answers

reevery

Hi Niklas

It sounds like you are hitting the limitations of Alteryx's input/output tools - they do not (yet) use Exasol's powerful IMPORT/EXPORT functionality.

One thing I'd definitely recommend first is changing the transaction size: This defines the batch size for each database commit, and in the case of Exasol can be increased dramatically - ideally so that there is only one commit.

As a longer term solution, there is a slightly more convoluted solution which I've been trying to build: Exasol's JDBC download includes an executable called exajloader - this takes a CSV file (ideally, zipped!), transfer the data in compressed form (rather than individual free-text SQL statements), then import locally into a table in the database. Within Alteryx you can call this using the command tool. Whilst this does require a temp file to be created, you'll get much better load performance if you can get it to work.

I'll try and come up with a cleaner version of my macro, but what I've attached will hopefully give you an idea of how I envisage the process running.

I should also add, we are working with Alteryx to try and improve this functionality - because this isn't the experience anyone should have with Exasol.

exa_import.yxmc

All comments

reevery

Hi Niklas

It sounds like you are hitting the limitations of Alteryx's input/output tools - they do not (yet) use Exasol's powerful IMPORT/EXPORT functionality.

I'll try and come up with a cleaner version of my macro, but what I've attached will hopefully give you an idea of how I envisage the process running.

I should also add, we are working with Alteryx to try and improve this functionality - because this isn't the experience anyone should have with Exasol.

exa_import.yxmc

steven4320555

@reevery Thanks very much for answering the question and sharing the Macro. I am trying to use the macro, but as a newbie to database (I only started to consider using a database when introduced to Exasol 2 weeks ago), I am struggling to make the macro working.

I have some questions and would be very much appreciated if I can get some advice:

1. The picture below shows my input setup, I have ziped a csv file as suggested, and launched Exasol from Virtual box, is there anything wrong with the configuration? especially the Schema.Table field?

It seems, the data schema is generated inside the macro, is it?

3. The batch macro need some input as control parameter, but I am not sure what to feed in the macro. Would you mind share more information?

Finally, I am looking forward to the updates in Alteryx to better support Exasol.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703