ELT tool integration ( Altryx) with ADB to load CSV files using AutoREST.

 

Necessity is the mother of all inventions. I was involved in a POC, where I had to load data to Oracle Autonomous database (ADB) after some data transformation in Altryx Desktop.

The customer complained loading data into Oracle was about four times slower than Snowflake.

Altryx does not have parallel threads enabled to load data to Oracle database. This explains why the performance was slower than most ETL tools to Load into Oracle.

In addition to this,  Altryx had a Bulk option in Snowflake which created a file on local disk or S3 and loaded that data in parallel using some custom code written by Altryx. So, it was not a fair caparison, although from the customer point of view, a ETL tool should just Load and perform.

To get similar performance in Oracle, I realized that we have all the pieces of the puzzle solved by ADB. Oracle ADB has a new feature called AutoRest APIs. These rest APIs can be created over tables, views, procedures, and functions by simply right clicking on GUI to enable it. One of the AutoRest APIs is a BULK load API.

Instead of Altryx loading data directly into Oracle DB as the target, if we can have the Output written to a CSV file, we can pick the file from a python program as part of "After Run" event of Designer canvas. This program will read the CSV files and run the python or curl script that, in turn, calls the BULK load APIs. of AutoRest.

  If the data to be loaded is large,  Altryx Designer  "Output"  can be split into multiple files, and each CSV file generated can be loaded in parallel through the python program.

To split the CSV file into multiple files, all we had to do was set "Max Records Per File. -- This splits the output file and adds a record at the file name.

To call the Python program from Altryx , Click on the canvas and "Events" and  "Add"  the program as a "After Run" event.

The second part of this blog is to see how to enable AutoRest on the table we need to load.