This post demonstrates how to load data into a Snowflake database from an Oracle OCI source Autonomous Database using ODI Web Edition provisioned from OCI Cloud Markerplace.
Connect to the OCI console in your cloud tenancy and select the Marketplace menu.
Then type the search string “data integrator” and from the search results returned select the Data Integrator: Web Edition.
Enter the details required to create the compute instance like name, compartment name, image and shape and the required VCN details.
Once the status of the instance shows as RUNNING, we launch a web browser and connect using the URL http://private IP address or host name:9999/oracle-data-transforms.
Select the option Create a new Data Transforms Repository.
Provide the details of the Autonomous Database where the ODI repository will be created – including the ADMIN account password.
Make a note of the SUPERVISOR password as this is the password we will be using to login to the Web ODI console later.
We can now login to the ODI Web console by providing the SUPERVISOR password.
Create connections to the Oracle Autonomous Database source as well as the Snowflake database target.
Click on the Connections menu.
In the JDBC URL we need to provide details of the Snowflake account name, the database, the schema, the warehouse and the role in the form below.
jdbc:weblogic:snowflake://snowflakecomputing.com;AccountName=account_name;User=user_name;Password=password;[property=value[;…]];
For the Oracle Autonomous Database we need to upload the ADB or ADW database wallet file, select the appropriate service to connect and also provide the ADMIN password.
After we have created the Snowflake and Oracle Autonomous Database connections, we now have to create a project.
From the Projects menu, select Create Project.
After the project has been created, we next need to import Data Entities of the source Oracle Autonomous Database.
Select the Data Entities menu and click on Import Data Entities.
Select the connection to the Autonomous Database which we just created and the appropriate schema.
We are using the DEMO schema which consists of two tables – MYOBJECTS and MYOBJECTS_NEW.
A job will be created to import the data entities and by clicking on the job name link we can track the progress and view the status of the job.
Access the Projects menu select the project we just created – LOAD_DATA_TO_SNOWFLAKE.
Select the Data Loads menu and click on Create Data Load button.
Provide a name for the data load and for the source select the connection to the Oracle Autonomous Database and the DEMO schema.
Click on Create button.
We can see the two table in the DEMO schema now showing.
Under Target Action, select the Recreate option.
For the Target Schema, select the connection to the Snowflake database which we earlier had created and the schema name.
Click on the Save icon.
Click the green arrow icon to start the Data Load job.
Click on the Data Load job link to view the progress as well as status of the data load job.
Once the Data Load job has completed, we can connect to our Snowflake console.
We can see that the Snowflake database tables MYOBJECTS and MYOBJECTS_NEW have been created and we can also view the data in the Snowflake table to validate the data load has been successfully completed.
