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.

Invoke OCI Functions from Autonomous DB and send mails.

This is a 2 part blog.

Part1 will discuss how we can configure OCI Functions to send Gmail.

The Part2 will discuss how you can invoke the OCI function from Oracle Autonomous Database.

OCI Functions is a serverless platform that allows developers to build and run code without having to worry about managing the infrastructure. In this blog we will call the OCI function and send email from ADB.

we will use the new DBMS_CLOUD functionality in ADB to invoke the function we just deployed. Open up SQL Developer Web from ADB instance Service Console and run the following scripts as directed. If you don't yet have an ADB instance and need a guide on how to set one up click here.

Create a user credential that is required for authentication to call OCI APIs by filling in your user_ocid, tenancy_ocid, private_key and fingerprint. Click here if you are unsure where to find this information. BEGIN DBMS_CLOUD.CREATE_CREDENTIAL ( credential_name => 'OCI_KEY_CRED', user_ocid => 'ocid1.user.oc1..aaaaaaaam2...', tenancy_ocid => 'ocid1.tenancy.oc1..aaaaaaaakc...', private_key => 'MIIEogIBAAKCAQEAtU...', fingerprint => 'f2:db:d9:18:a4:aa:fc:83:f4:f6..'); END; / And finally, we use the SEND_REQUEST procedure to invoke the deployed function using the function endpoint. You may identify your function's invoke endpoint using this CLI command or simply copying it from your Oracle Cloud UI under Developer Services Replace the uri parameter below with your Function's invoke endpoint and, if you like, your own custom name in the body parameter. SET SERVEROUTPUT ON DECLARE resp DBMS_CLOUD_TYPES.resp; payload clob; BEGIN --HTTP POST Request resp := DBMS_CLOUD.send_request( credential_name => 'OCI_FUNCTIONS', uri => 'https://fuukiqbujgq.us-ashburn-function.. ..actions/invoke', method => DBMS_CLOUD.METHOD_POST, body => UTL_RAW.cast_to_raw('{ "sender-email":"manly.hogan@gmail.com", "sender-name":"Test", "recipient":"vijay.balebail@oracle.com","subject":"Hello subject", "body":"This is a test email" }') ); -- Response Body in TEXT format DBMS_OUTPUT.put_line('Body: ' || '------------' || CHR(10) || DBMS_CLOUD.get_response_text(resp) || CHR(10)); -- Response Headers in JSON format DBMS_OUTPUT.put_line('Headers: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_headers(resp).to_clob || CHR(10)); -- Response Status Code DBMS_OUTPUT.put_line('Status Code: ' || CHR(10) || '------------' || CHR(10) || DBMS_CLOUD.get_response_status_code(resp)); END; /

If all went according to plan, you should see a 200 OK Response Code and the response text that the email was sent. Note: While invoking Oracle Functions, use the troubleshooting guide to help resolve issues. In addition to that, here are two places I stumbled so you won't have to: If you are still seeing access errors after already uploading your public key to your User, or creating the policies to give Functions access to your user, you may want to wait a few minutes. It sometimes may take a little while for the changes to propagate. If you are seeing a 502 error and are using a public subnet in your VCN, you may need to create an internet gateway and set up its routing table (you can use CIDR 0.0.0.0/0 as default) to give public internet access to your gateway. Click here for more information about networking or to use the simplified Virtual Networking Wizard. While this is a simple example to walk you through the necessary steps, Oracle Functions is an extremely powerful service and the ability to call Functions from the database expands Autonomous Database functionality to essentially any custom functionality you desire. You may of course also use the SEND_REQUEST procedure to call any other Cloud Service REST API, such as object storage operations, instance scaling operations, Oracle Streams, and many more!