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)
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.
@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.
@reevery Thank you for this. I'm happy to see that you are working on this and the macro is a nice way around it for now.
@steven4320555 Steven, you are meant to enter both table and schema into the first input e.g. ALL_SALES.SALES_DATA_20170929 if your schema is called ALL_SALES and your data is called SALES_DATA_20170929
@Niklas Hi Niklas. Thanks very much for the reply.
I have to admit that, currently, I am lack of the fundamental knowledge of operating a database. It seems I don't need to create a schema and a table inside Exasol before I can use the macro to import data into the database, do I?
And the batch macro need some control input, what sort of data does it need to connect to the macro? Currently, I just typed 1 in the text input, so the macro just run 1 iteration.
The file inside the zip file is just a column of postcode. I put a new schema and table name, but I got the following error:
Do you may have any idea on what is wrong in my setup?