Sending data to Amazon s3 buckets using ODI - s3 to ODI

Recently I've come across with a task to upload data from Oracle Data Integrator to amazon s3 buckets.

This demand comes for many reasons, but the main one is the trend of using cloud storage for data.

ODI is a very powerful middleware and can be used to integrate data with amazon s3. Although cards should be open, this tool is not native with amazon, and I have found a workaround to implement this.

Sending data to s3 buckets via ODI can be achieved in two main ways.

• Using Amazon CLI commands in ODI
• Using RESTful API to send data to from ODI to s3 bucket.  

This blog post will cover the first one because it is much faster and easier to implement. For the second one, videos within youtube can be found how to invoke RESTful API in ODI.

Firstly Amazon CLI should be installed in the desired environment where ODI is being used.

To install Amazon CLI, head over to the following link:

• https://aws.amazon.com/cli/

Having downloaded the installer, click on it and continue with the simple installation within the system.

I am using windows, so to test whether Amazon CLI has been properly installed, run this command within command prompt of windows:

• aws --version

Testing Amazon CLI deployment

If the above text displays, it means that Amazon CLI has been successfully installed within the environment.

Now that Amazon CLI is installed, we should configure the s3 bucket connection with amazon CLI.

An existing s3 bucket is needed, for the sake of this post, I will create a new one in aws console:

Bucket name: "bucketname"

Region: eu-central-1

After creating this bucket, you should generate the AWS Access Key ID and AWS Secret Access key, to do that please watch this tutorial since I wont be covering that:

• https://youtu.be/m5nCqLPwSsk

Once the keys are generated, head over to CMD to configure aws connection with Amazon CLI

To do that, firstly type the following command in cmd:

• aws configure

And continue with keys, region inputs:

aws configure

Having configured aws connection, amazon CLI commands have to be used in order to achieve successful upload to s3 from ODI.

The following command copies testfile.txt from my environment to the bucket desired

• aws s3 cp C:\Users\lori\Desktop\testfile.txt s3://bucketname

If you want to copy a whole folder, and create that folder within s3 bucket dynamically, use the following command:

• aws s3 cp C:\Users\lori\Desktop\testfolder s3://bucketname/testfolder --recursive

The same can be achieve with commands to download files from s3 to an environment using ODI with amazon CLI commands, but I won't cover that in this blog post.

Now head over to ODI to configure the package for amazon CLI command invocation.

Create a package, and drag an OdiOSCommand object, this object can be used to call Operating system commands from ODI middleware:

• On the command to execute section, copy the amazon CLI command desired.

• Error file: use a random name, it will create a file in case of an error. **It is used for error tracing.

• Working directory: specify the path where Amazon CLI is installed in your system.

Execute the command and head over to s3 bucket, you can observe the file uploaded in s3:

Uploaded file

As you can imagine, this can be extended to far wider application for automatic BI purposes, also ODI variables can be used.

I hope it helps!