Table to Table Mapping in Talend Open Studio

Table to Table Mapping in 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 discussed on different panels available in talend open studio for data integration. In this post we will try create a mapping that will load data from oracle to oracle with simple transformation. To achieve this we will be using three components from the component palette. Before we start I am assuming you have an oracle instance running either in local or remote machine and it is accessible via  sql client like SQL plus or SQL Developer. In my case I have oracle 11g express edition running on my local system.

Source Table: Employee
Target Table: Employee1

CREATE TABLE "SCOTT"."EMPLOYEE"
(
"EMPNO" NUMBER(*,0) NOT NULL ENABLE,
"NAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"BOSS" NUMBER(*,0),
"HIREDATE" VARCHAR2(12 BYTE),
"SALARY" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(*,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
);
CREATE TABLE "SCOTT"."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
);

Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7839,‘KING’,‘PRESIDENT’,null,‘1981-11-17’,5000,null,10);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,null,20);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7788,‘SCOTT’,‘ANALYST’,7566,‘1982-12-09’,3000,null,20);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7876,‘ADAMSSS’,‘CLERK’,7788,‘1983-01-12’,1100,null,20);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,null,20);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,null,20);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,null,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,null,30);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,null,10);
Insert into SCOTT.EMPLOYEE (EMPNO,NAME,JOB,BOSS,HIREDATE,SALARY,COMM,DEPTNO) values (7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,null,10);

 

Open talend studio and right click on Job Designs in the repository explorer. Create a folder called demo. Then right click on the demo folder and create a job. Give any name that you like. I am giving “job_Orcl_Tab_to_Tab_LessLogging”. You might notice the suffix as less logging. Well in the subsequent posts, I will explain how to gather more statistics  related to logging mechanism.

Now in the repository explorer expand metadata. Right click on Db Connection > create connection. Give the connection name as “oracle”. Click next. Select db type as “Oracle with service name”.  Then fill all the parameters as per your db instance. This is how it looks in my studio.

1

Make sure to check you connection for a successful message. So far what we did is same like creating data server in ODI. Now we have to create data stores. Right click on oracle and then retrieve schema. If you want to put a filter then set it here or else click next. As you can see all the tables are listed under scott. Select two tables to use them as source and target and click on next.

2

After reverse engineering, both tables will be listed under oracle connection.

3

Now drag tOracleInput, tOracleoutput, tMap component to the design workspace. Either you search them in the palette or start typing directly in the design workspace, a drop down will be popped up immediately.

4

After putting all three components, the design workspace will look like this.

5

Now click on tOracleInput and go to the components tab in the properties window. Select property type as Repository. Next to click on browse button to select oracle as the connection. Now you can see all the parameters are auto populated. In the table name parameter either type the table inside double quotes or select it by clicking the browse button.

Its time to connect all the components using arrows. You can directly drag and connect to the next or else right click on tOracleInput > Row > Main and connect to tMap.

6

While doing the same from tMap to tOracleOutput you will be opted for an output name. This is required as we can have multiple outputs from a single source. So to distinguish between them we will have some name like actual output, rejected records etc. Give the name as OUTPUT for the time being. This is how it looks like in my studio.

 

7

Now click on tMap and go to properties. Click on Map Editor. If it is not already mapped you can click on auto map and it will do the mapping for you.

8

Thats  it. We are now ready for execution. Press F6. You can see below message in the run console.

9

Go to SQL developer and see if all the rows have been loaded to the target table or not. You can also see the number of rows loaded from the designer itself.

10

What about transformation? Well it is in my mind. Click on tMap and then Map editor from properties. Now highlight the mapping in which you want do some transformation. Lets apply upper case to NAME column. All the function available here are java method. If you want to use any oracle functions then that we can use in tOracleInput and tOracleOutput. Click ok and run it again.

12

You can also play around the “action on table” and “action on data” properties as shown below.

11

 

Thats it for today. Let me know if you have any questions.

Thank you!!!

 

 

0

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

1 comments

While creating the job, sometimes the metadata does not get refreshed and you will not see the mapping in input/Output in the map editor.
In a condition like this: Navigate to the Query Type label in the Component tab, select Query type as Build in and Click on Guess Query, the meta data gets refreshed.

Leave a reply

required*

Are you a human? *