ETL tool (altryx) integration to BULK LOAD using AutoRest API in ADB.

Autonomous Database is a cloud-based database service that offers high availability, security, and scalability. AutoREST is a feature of Autonomous Database that enables users to generate REST APIs automatically for their database tables, views, and procedures. AutoREST also supports OAuth2 authentication, which allows users to secure their API endpoints and access control. In this blog, we will discuss how to enable AutoREST in Autonomous Database with OAuth2 and call the BULK LOAD API.

Step 1: Create a Table in the Autonomous Database

From the Autonomous database console, click on "Database Actions" and then "SQL"

In the sql worksheet , you can create a sample table where we need to load data through CSV files/

Eg. create table customers as select * from ssb.customer  where 1=2;

Step 2: Create a Table in the Autonomous Database

The first step is to enable AutoREST in Autonomous Database. This can be done by logging in to the Oracle Cloud Console, selecting the Autonomous Database instance, and clicking on the AutoREST tab. From here, you can enable AutoREST and select the database objects that you want to expose through REST APIs.

In this example, i have enable "OAuth2 authorization as well "

Step 2: Set up OAuth2 authentication .

For OAuth2 to work, we have to create a DB specific OAuth Client_id and associate the auto generated role and privilege to this Client_id to access the api.

 Do this by navigating to the Database Actions menu in the upper left of the page. Choose REST in the Development list.

 select the Security Tab on the top of the page and then select OAuth Clients.

OAuth2 is an industry-standard protocol for secure API authentication. To set up OAuth2 authentication in Autonomous Database, you need to create an OAuth2 client in the Autonomous Database instance. This can be done by following the steps below:

Enter client name, and support URL and support Email info. Note that these values are mandatory, but the info is not validated.

Next Click on the Privilege tab and select the privilege for the table created..

Accessess the Autorest API, we need to obtain a token to pass to the secured REST service once its enabled. To obtain this token, we can click the pop out menu icon  on our OAuth tile and select Get Bearer Token

The OAuth Token modal will provide the token text in the Current Token field. You can use the copy icon  to copy this token text. Save it to a text document or notes application as you'll need it when calling the secured REST service. The modal will also provide us with a cURL command to obtain a token should we need to include it in our applications.

Take note of the client_id, client_password and the token URL.

Next, get the URL to bulk load data through AutoRest. You can click on the Database Actiontions --> SQL and rigth clith the table where you enabled the Autorest and click on the Curl

.

Here note the URL for BLUKLOAD.

Now, you have all the components to put a CSV file through a POST REST call.

I configured the information cathered in the python program  which coluld push data in multiple parallel threads into ADB.

Download the python program and replace the client_id, password and tokan url and table url in the python program.

https://github.com/vijaybalebail/AutoRestBulkLOAD/blob/main/bulkloadapi.py

you can run the perl script   and pass the file name you need to load as a parameter.

Enabling AutoREST in Autonomous Database with OAuth2 authentication and calling the BULK LOAD API is a simple process that can be done in a few easy steps. This allows developers to easily integrate their applications with the database, making it easier to manage and analyze data. With these steps, you can take full advantage of the capabilities of Autonomous Database and make the most of your data.