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!!

About Bhabani 86 Articles
Bhabani has 10 plus years of experience in Data warehousing and Analytics projects that has span across multiple domains like Travel, Banking and Financial, Betting and Gaming Industries. Solution areas he focuses on designing the data warehouse and integrating it with cloud platforms like AWS or GCP. He is also a Elite level contributor at OTN forum more than 9 years. He loves to do experiment and POC on different integration tools and services. Some of his favorite skills are Redshift, Big Query, Python, Apache Airflow, Kafka, HDFS, Map Reduce ,HIVE, Habse, Sqoop, Drill, Impala.

Be the first to comment

Leave a Reply

Your email address will not be published.


*