Oracle to Amazon RDS using Talend Open Studio

Oracle to Amazon RDS using Talend Open Studio:

Environment:
Talend Open Studio for Data Integration Version: 6.3.1
Java Compiler: 1.7
OS: Windows 8

In the previous post we saw how to implement SCD Type 2 using tOracleSCD component. In this post we will discuss about Amazon RDS ( relational database services) and how to load data from local oracle instance to the oracle instance running on Amazon. If you do not have knowledge  on Amazon RDS then no need to worry at this point.

What is Amzon RDS: Amazon Relational Database Service (Amazon RDS) is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. It provides cost-efficient, resizeable capacity for an industry-standard relational database and manages common database administration tasks. So that means its same like local instance but running on someone else’s machine with some additional features.

If you dont have an account click here to register. During registration you will be asked to provide your credit card details. However you will be charged only when you go beyond the free tier usage. Once the registration is done you can login to amazon console using this link. Go to services and click on RDS.

1

 

Now click on instance and you will be see the instance running at the moment. If you logging first time then create one Oracle instance. In another post I will show you how to do that.

3

4

As you can see the instance details running on amazon cloud. We will be using these parameters to configure in talend open studio. Before going to the studio lets connect this cloud instance from SQL Developer and create a table call EMPLOYEE1. Refer this post to get the script.

CREATE TABLE “EMPLOYEE1”
( “SG_KEY” NUMBER,
“EMPNO” NUMBER(5,0) NOT NULL ENABLE,
“NAME” VARCHAR2(10 BYTE),
“JOB” VARCHAR2(9 BYTE),
“BOSS” NUMBER(5,0),
“HIREDATE” VARCHAR2(12 BYTE),
“SALARY” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(5,0),
“IS_ACTIVE” NUMBER,
“START_DATE” DATE,
“END_DATE” DATE
)

5

Go to metadata under repository explorer in Talend Studio. Create a new connection under Db Connection and name it as OracleAmazonRDS. Select Oracle with SID as the DB type and fill required parameters as per your amazon instance.

6

Now create a new job called job_LocalORCL_to_AmazonRDS. Drag tOrcleInput, tMap, tOracleOutput in to the designer. Either you search them in the palette or start typing directly in the design workspace, a drop down will be popped up immediately. Configure in the similar fashion like we did the post oracle to oracle mapping. This is how it looks like in my studio.

7

You can see I have kept two outputs. One points to local instance and the other points to Amazon RDS. If you want to do any transformation then do that in the tMap Editor.

8

Now we are done with the setup. Press F6 to build and execute the job. As you can see we have 14 rows loaded to each output instances.

9

Thats it for today. Let me know if you are stuck at any point. I will be happy to assist you.

Thank you!!

1 Thank you for your recommendation.

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

Leave a reply

required*

Are you a human? *