Customer Portal

DB Join

  1. CloverDX Customer Portal
  2. Forum
  3. DB Join
Avatar
xsvov1@seznam.cz
  • Answered
Follow
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

Comments 6

Sort by Date Votes
  • Avatar
    avackova
    0
    Comment actions Permalink
    Probably you have chosen wrong key for joining. I would need more info (data, metadata, transformation) to could say something more about the solution.
  • Avatar
    xsvov1@seznam.cz
    0
    Comment actions Permalink
    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};
  • Avatar
    avackova
    0
    Comment actions Permalink
    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.
  • Avatar
    xsvov1@seznam.cz
    0
    Comment actions Permalink
    Hi,
    select employee2.account_num
    from employee2 where account_num ="87462027264","87521173504", 87521173504",
    Thanks
  • Avatar
    xsvov1@seznam.cz
    0
    Comment actions Permalink
    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
  • Avatar
    xsvov1@seznam.cz
    0
    Comment actions Permalink
    I mislead, my update cloverETL is cloverETL.rel-2-2-0.zip

Please sign in to leave a comment.

Didn't find what you were looking for?

New post