DB Join

CloverDX Platform
1

Hi,

I can ask question how I can merge records from database and data from txt. I can not in this component DB Join specify primary Join key = Slave key.

DB Join make only that records from database merge this method

customer_id;lname;fname;account_num;employee_id;first_name;last_name;Id
1;Nowmer;Sheri;87462027264;1;Petr;Janda;1
1;Nowmer;Sheri;87462027264;2;Jana;Reznickova;1
1;Nowmer;Sheri;87462027264;3;Jakub;Prochazka;1
1;Nowmer;Sheri;87462027264;4;Iveta;Novakova;1
2;Whelply;Derrick;87470587904;1;Petr;Janda;2
2;Whelply;Derrick;87470587904;2;Jana;Reznickova;2
2;Whelply;Derrick;87470587904;3;Jakub;Prochazka;2
2;Whelply;Derrick;87470587904;4;Iveta;Novakova;2

How can I do in this component
1;Nowmer;Sheri;87462027264;1;Petr;Janda;1
2;Whelply;Derrick;87470587904;2;Jana;Reznickova;2
3;Derry;Jeanne;87475757056;3;Jakub;Prochazka;3
4;Spence;Michael;87500480512;4;Iveta;Novakova;4

Thanks for answer

Vl

2

Probably you have chosen wrong key for joining. I would need more info (data, metadata, transformation) to could say something more about the solution.

3

Thanks, I think that have I do not chosen wrong key for joining.

I send input data form source data txt.
customer_ID,account_num,lname,fname,ID
1;87462027264;Nowmer;Sheri;1
2;87470587904;Whelply;Derrick;2
3;87475757056;Derry;Jeanne;3
4;87500480512;Spence;Michael;4
5;87514054656;Gutierrez;Maya;5
6;87517782016;Damstra;Robert;6
7;87521173504;Kanagaki;Rebecca;7
8;87539744768;Brunner;Kim;8
9;87544799232;Blumberg;Brenda;9
10;87568711680;Stanz;Darren;10
11;87572824064;Murraiin;Jonathan;12
12;87579238400;Creek;Jewel;13
13;87587119104;Medina;Peggy;14
14;87592624128;Rutledge;Bryan;15
15;87597752320;Cavestany;Walter;16

Source data from DB
Employee_ID,account_num,first_name,last_name, department_Id,management_role
1, 87462027264,Petr,Janda,2,Sales Manager
2,87521173504,Jana,Reznickova,5,Sales Manager
3,87470587904,Jakub,Prochazka,3,Sales Manager
4,87587119104,Iveta,Novakova,3,Sales Manager

Join key is account_num

Join metadata are
customer_ID, lname,fname,account_num,employee_id,first_name,last_name,Id

Thansformation look like this

${out.0.customer_id} = ${in.0.customer_id};
${out.0.lname} = ${in.0.lname};
${out.0.fname} = ${in.0.fname};
${out.0.Id} = ${in.0.Id};
${out.0.employee_id} = ${in.1.employee_id};
${out.0.first_name} = ${in.1.first_name};
${out.0.last_name} = ${in.1.last_name};
${out.0.account_num} = ${in.0.account_num};

4

I created graph with your data. DBJoin component parameters:
Transform:
${out.0.customer_ID} = ${in.0.customer_ID};
${out.0.lname} = ${in.0.lname};
${out.0.fname} = ${in.0.fname};
${out.0.Id} = ${in.0.ID};
${out.0.employee_id} = ${in.1.Employee_ID};
${out.0.first_name} = ${in.1.first_name};
${out.0.last_name} = ${in.1.last_name};
${out.0.account_num} = ${in.0.account_num};
SQL query:
select * from tmp_employee where account_num=?
Join key:
account_num

My output:
1;Nowmer;Sheri;87462027264;1;Janda;Petr;1
2;Whelply;Derrick;87470587904;3;Prochazka;Jakub;2
7;Kanagaki;Rebecca;87521173504;2;Reznickova;Jana;7
13;Medina;Peggy;87587119104;4;Novakova;Iveta;14
what is exactly somebody could expect.
Pls try to download last version of cloverETL and run your graph again.

5

Hi,
select employee2.account_num
from employee2 where account_num =“87462027264”,“87521173504”, 87521173504",
Thanks

6

Hi,

Thanks for answer and advice but the problem continue.
I update cloverETL and eclipse on version cloverETL.rel-2-1-2.zip but the probleme continue and do it still this.

Can you help me? I send you my graph on email. May you me send where I have error.

Thanks V.

Graph definition file: graph\DBJoin2.grf
INFO [main] - Checking graph configuration…
DEBUG [main] - Opening input file C:/Documents and Settings/vsvoboda/Desktop/Data/DBJoin/customers01.txt
DEBUG [main] - Reading input file C:/Documents and Settings/vsvoboda/Desktop/Data/DBJoin/customers01.txt
INFO [main] - Initializing DB connection:
INFO [main] - DBConnection driver[com.mysql.jdbc.Driver]:url[jdbc:mysql://localhost:3306/test]:user[root] … OK
INFO [main] - Starting WatchDog thread …
INFO [WatchDog] - Thread started.
INFO [WatchDog] - Running on 2 CPU(s) max available memory for JVM 65088 KB
INFO [WatchDog] - [Clover] Initializing phase: 0
DEBUG [WatchDog] - initializing edges:
DEBUG [WatchDog] - Edge ‘Edge1’ is running in debug mode. (C:\DOCUME~1\vsvoboda\LOCALS~1\Temp\Edge1.dbg)
DEBUG [WatchDog] - Edge ‘Edge0’ is running in debug mode. (C:\DOCUME~1\vsvoboda\LOCALS~1\Temp\Edge0.dbg)
DEBUG [WatchDog] - all edges initialized successfully…
DEBUG [WatchDog] - initializing nodes:
INFO [WatchDog] - (compiling dynamic source)
DEBUG [WatchDog] - Compile arguments: -d C:\DOCUME~1\vsvoboda\LOCALS~1\Temp\ C:\DOCUME~1\vsvoboda\LOCALS~1\Temp\TransformTransformDBJOIN0.java -classpath /C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/cloveretl.engine.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/commons-cli-1.0.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/commons-logging.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/javolution.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/jms.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/jxl.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/log4j-1.2.12.zip;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/poi-2.5.1.jar;/C:/Documents and Settings/vsvoboda/workspace/example/;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/cloveretl-commons.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/cloveretl.engine.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/commons-logging.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/lib/log4j-1.2.12.zip;/C:/Program Files/Java/jdk1.5.0_11/lib/tools.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/plugins/org.jetel.component/cloveretl.component.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/plugins/org.jetel.connection/cloveretl.connection.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/plugins/org.jetel.connection/lib/jms.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.4/lib/plugins/org.jetel.lookup/cloveretl.lookup.jar
DEBUG [WatchDog] - Loading Class: TransformTransformDBJOIN0…
DEBUG [WatchDog] - Class: TransformTransformDBJOIN0 Loaded
DEBUG [WatchDog] - DBJOIN0 …OK
DEBUG [WatchDog] - Opening input file C:/Documents and Settings/vsvoboda/Desktop/Data/DBJoin/customers01.txt
DEBUG [WatchDog] - Reading input file C:/Documents and Settings/vsvoboda/Desktop/Data/DBJoin/customers01.txt
DEBUG [WatchDog] - DELIMITED_DATA_READER0 …OK
DEBUG [WatchDog] - DELIMITED_DATA_WRITER0 …OK
INFO [WatchDog] - [Clover] phase: 0 initialized successfully.
INFO [WatchDog] - Starting up all nodes in phase [0]
DEBUG [WatchDog] - DBJOIN0 … started
DEBUG [WatchDog] - DELIMITED_DATA_READER0 … started
DEBUG [WatchDog] - DELIMITED_DATA_WRITER0 … started
INFO [WatchDog] - Sucessfully started all nodes in phase!
INFO [WatchDog] - Execution of phase [0] successfully finished - elapsed time(sec): 0
INFO [WatchDog] - ---------------------** Start of tracking Log for phase [0] **-------------------
INFO [WatchDog] - Time: 13/08/07 11:31:48
INFO [WatchDog] - Node Status Port #Records #KB Rec/s KB/s
INFO [WatchDog] - ----------------------------------------------------------------------------------
INFO [WatchDog] - DBJOIN0 FINISHED_O
INFO [WatchDog] - %CPU:-1.9 In:0 15 0 0 0
INFO [WatchDog] - Out:0 60 5 0 0
INFO [WatchDog] - DELIMITED_DATA_READER0 FINISHED_O
INFO [WatchDog] - %CPU:-1.9 Out:0 15 0 0 0
INFO [WatchDog] - DELIMITED_DATA_WRITER0 FINISHED_O
INFO [WatchDog] - %CPU:-1.9 In:0 60 5 0 0
INFO [WatchDog] - ---------------------------------** End of Log **--------------------------------
INFO [WatchDog] - Forcing garbage collection …
INFO [WatchDog] - -----------------------** Summary of Phases execution **---------------------
INFO [WatchDog] - Phase# Finished Status RunTime(sec) MemoryAllocation(KB)
INFO [WatchDog] - 0 0 0 5403
INFO [WatchDog] - ------------------------------** End of Summary **---------------------------
INFO [main] - WatchDog thread finished - total execution time: 2 (sec)
INFO [main] - Graph execution finished successfully
Execution of graph successful !

1;Nowmer;Sheri;87462027264;1;Petr;Janda;1
1;Nowmer;Sheri;87462027264;2;Jana;Reznickova;1
1;Nowmer;Sheri;87462027264;3;Jakub;Prochazka;1
1;Nowmer;Sheri;87462027264;4;Iveta;Novakova;1
2;Whelply;Derrick;87470587904;1;Petr;Janda;2
2;Whelply;Derrick;87470587904;2;Jana;Reznickova;2
2;Whelply;Derrick;87470587904;3;Jakub;Prochazka;2
2;Whelply;Derrick;87470587904;4;Iveta;Novakova;2
3;Derry;Jeanne;87475757056;1;Petr;Janda;3
3;Derry;Jeanne;87475757056;2;Jana;Reznickova;3
3;Derry;Jeanne;87475757056;3;Jakub;Prochazka;3
3;Derry;Jeanne;87475757056;4;Iveta;Novakova;3
4;Spence;Michael;87500480512;1;Petr;Janda;4
4;Spence;Michael;87500480512;2;Jana;Reznickova;4
4;Spence;Michael;87500480512;3;Jakub;Prochazka;4
4;Spence;Michael;87500480512;4;Iveta;Novakova;4
5;Gutierrez;Maya;87514054656;1;Petr;Janda;5
5;Gutierrez;Maya;87514054656;2;Jana;Reznickova;5
5;Gutierrez;Maya;87514054656;3;Jakub;Prochazka;5
5;Gutierrez;Maya;87514054656;4;Iveta;Novakova;5
6;Damstra;Robert;87517782016;1;Petr;Janda;6
6;Damstra;Robert;87517782016;2;Jana;Reznickova;6
6;Damstra;Robert;87517782016;3;Jakub;Prochazka;6
6;Damstra;Robert;87517782016;4;Iveta;Novakova;6
7;Kanagaki;Rebecca;87521173504;1;Petr;Janda;7
7;Kanagaki;Rebecca;87521173504;2;Jana;Reznickova;7
7;Kanagaki;Rebecca;87521173504;3;Jakub;Prochazka;7
7;Kanagaki;Rebecca;87521173504;4;Iveta;Novakova;7

7

I mislead, my update cloverETL is cloverETL.rel-2-2-0.zip