Guest Author: Ioana Stama - Oracle, Sales Consultant
This article presents an overview on how to use Oracle Data Integrator Cloud Service (ODI-CS) in order to manipulate data from Oracle Cloud Infrastructure Object Storage. The scenarios here present loading the data in an object stored in Oracle Cloud Infrastructure in a table in Database Cloud Service (DBCS) and then move the object to another storage container.
We are going to showcase how ODI-CS can connect to Object Storage Classic with both RESTful Services and CURL commands.
About Object Storage by Oracle Cloud Infrastructure
Oracle Object Storage is an internet-scale storage, high performance, and durable storage platform. Developers and IT administrators can use this storage service to store an unlimited amount of data, at a very low cost.
With the Oracle Object Storage, you can safely and securely use the web-based console to store or retrieve data directly from the internet or from within the cloud platform, at any time. Oracle Object Storage is agnostic to the data content type. It enables a wide variety of use cases. You can send your backup and archive data offsite, store data for Big Data Analytics to generate business insights, or simply build a scale-out web application. The elasticity of the service enables you to start small and scale your application as needed, and you always pay for only what you use.
Oracle Object Storage provides a native REST API, along with OpenStack Swift API compatibility, and an HDFS plug-in. Oracle Object Storage also currently offers a Java SDK, as well as Console and Python CLI access for management.
We are going to see how the containers look like in the beginning.
The source container:
The target container:
The target table:
Let’s see first how we prepare the topology for the REST services, the File topology and the database topology.
We are going to start with the REST services. We are going to create a connection for the source Object Storage and one for the destination one.
Preparing the RESTful Services Topology
Go to the Topology tab in ODI Studio and right click on the RESTful Services and select New Data Server.
You have to give it a name – eg: TestRest
In the REST Service endpoint URL you have to write the endpoint URL of the cloud container. This URL can be built accordingly to Oracle documentation or take it from the cloud dashboard. It is already available there.
In order to connect you have to use the cloud account user as per the below picture.
After we save this connection we are going to create two new physical schemas. One for the source Object Storage Container and another one for the destination Object Storage Container. We are creating them under the same data server because both containers are created on the same Oracle Cloud Infrastructure.
Right click on the newly created data server and select New Physical Schema.
The first one is for the TEXTFILE object in the ODICS container, the source one. The resource URL it is also available in the Cloud Dashboard.
Now, we are going to go to the Operations Tab. Here we are going to define some operations in order to manipulate the object. As you can see in the list there are methods from where we can pick a method.
We defined operations for deleting, getting and putting the object in the container.
Let’s test the service by pressing the Test Restful Service button. A pop-up window will open. Here, by selecting the desired operation, the effective URL is built. We can see here that we can add and modify the parameters.
The save request content button opens another pop-up that will give you the chance to select the location where you want to save the content that you get from the object.
We are going to do the same for the other container. A new physical schema will be created with another resource URL.
In the operations tab we only defined operations for the GET and PUT method. The defined operations for both Objects are defined for the purpose of this demonstration.
Preparing the File Topology
In the Topology tab in ODI Studio right click on the File topology and select new data server. The host here is the name of the host where we are saving the file.
Give it a name eg: JCS File.
Please leave the JDBC connection to its default state and the save button.
Right click on the File data server created and create a schema. Here we are going to mention the path to the file where the files that we are going to use will be stored. Also we have to mention the path of the folder where ODI is going to create his log and error files.
Preparing the Database Topology
In the Topology tab in ODI Studio right click on the Oracle Technology and select new data server. Give a name to the connection and specify the database user that you want to connect with.
In this case storage is our database user.
Please go to the JDBC tab and select the Oracle JDBC driver. Please modify the URL according to your details.
The next step is to reverse engineer the database table and the file. After we are going to create a mapping to load data from the file to the table.
Go in the Designer tab and after in the Models tab.
Here, click on the arrow and select create new model.
We will choose the parameters accordingly to the used technology.
For the first model we are going to select Oracle as a technology and DB_Storage_logical as the logical schema. After we do that press save.
The next step is to reverse engineer the tables. Click on Selective Reverse-Engineering.
Select the FILE_STORAGE table and press the Reverse Engineer button.
Now we have to reverse engineer the file. Because the content for this is the same we already have a file created.
We are going to create a new model and we will select File as a technology and the logical schema created in the topology tab.
After that, press the save button.
Next, right click on the new model and select New Datastore.
Give it a name, e.g.: TEXT_DATA and in the resource name tab press on the magnifying glass. Go to the path where we saved the file (the one mentioned in the physical schema).
The next step is to go to the Files tab. Here we have to mention the type of file and the delimitators.
File format: Delimited
Header (if needed)
Record separator: e.g.: Unix
Filed Separator: e.g.: Tab.
Press save and go to the Attributes Tab. Here you have to click on the Reverse Engineer Tab.
Preparing the mapping and the packages.
Create a new project folder and go to the mappings. Right click and select new mapping. Give it a name: e.g. File_to_Oracle.
Here, in the canvas, drag and drop the reversed engineered file and the table. Then connect them with the files as a source and the table as a target.
Then press save.
The next step is to create the packages.
We are going to have two packages. One where we are going to call the RESTful services and one where we are going to call the cURL commands.
Right click on the package and select new package.
Give it a name. e.g. Flow.
Here we are going to use the OdiInvokeRESTfulService component from the tool panel.
We are going to use it three times. Once for getting data and save it in a file, then for putting the file in the second Object Storage Container, and the third one to delete the file from the source container.
The flow is simple:
The OdiInvokeRESTfulService has different parameters in the General tab.
Here we have to select the operation that we want to use. Also, in the Response File parameter we have to specify the location and the file where we want to save the content of the object.
In the command tab we can see the command that is going to be executed when we are going to run the flow.
The same applies for the other OdiInvokeRESTfulService commands.
Let’s run the workflow.
We can see that the execution was successful.
We can see that in the table.
Also we can check the containers and see that the object has been moved.
The target container:
The source container:
We are going to create a new package as we did with the previous one.
But from the toolbox we are going to select the OdiOSCommand tool.
In the command tab we are going to write the cURL commands that you can find below
GET: curl -u cloud.admin:account_password https://identity _domain.storage.oraclecloud.com/v1/Storage-identity_domain/ODICS/TEXTFILE_CURL --output /u01/app/oracle/tools/home/oracle/files_storage/test_data.txt
PUT: curl -X PUT -F 'data=@/u01/app/oracle/tools/home/oracle/files_storage/test_data.txt' -u cloud.admin:account_password https:// identity _domain.storage.oraclecloud.com/v1/Storage- identity _domain /ODICS_ARCHIVE/TEXTFILE_CURL
DELETE: curl -X DELETE -u cloud.admin:account_password https:// identity _domain.storage.oraclecloud.com/v1/Storage- identity _domain /ODICS/TEXTFILE_CURL
The steps are:
Oracle Data Integrator Cloud Service (ODI-CS) is able to manipulate objects in Oracle Cloud Infrastructure Classic. You can leverage on ODI-CS capabilities on using RESTful Services and also commands written in any language in order to integrate all your data.