I have a graph that queries two section of data from the same table in a database, then sum of their number fields and insert the results to the same table. I tried both MERGE_JOIN, HASH_JOIN, the graph runs successfully but the results are not inserted into the target table.
The output shows me that DB_OUTPUT_TABLE0 is done before HASH_JOIN0.
INFO [WatchDog] - DB_INPUT_TABLE0 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 Out:0 32 6 0 0
INFO [WatchDog] - DB_INPUT_TABLE1 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 Out:0 32 6 24 5
INFO [WatchDog] - DB_OUTPUT_TABLE0 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 In:0 0 0 0 0
INFO [WatchDog] - FILTER0 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 In:0 32 6 0 0
INFO [WatchDog] - Out:0 16 3 32 6
INFO [WatchDog] - FILTER1 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 In:0 32 6 24 5
INFO [WatchDog] - Out:0 16 3 32 6
INFO [WatchDog] - HASH_JOIN0 FINISHED_O
INFO [WatchDog] - %CPU:-9.9 In:0 16 3 32 6
INFO [WatchDog] - In:1 16 3 32 6
INFO [WatchDog] - Out:0 0 0 0 0
INFO [WatchDog] - ---------------------------------** End of Log **--------------------------------
My last edge is defined as
Could anyone help me?
Thanks,
Wendy
Yes, it’s the problem with null value column in join key. After I removed them, the graph run successfully.
Thanks,
Wendy
Hi Wendy,
hash join isn’t producing output records. Can you check if all join key records (ORIGNATORS;TELECAST;DAYS;S_TIME;R_DUR;MEASUREMENT_PERIODS;DAYPARTS;VIEWING_SOURCES) have not null values? Try to reduce key to some single attribute joinKey=“TELECAST”.
PS: Filter is deprecated component, there is better to use Ext Filter, filter functions can be more complex.
ja
Hi Wendy !
Two records can’t be joined in any Joiner if actual values of key fields are null. It holds that NULL!=NULL. Use some default value in place of NULL to get such records matched/joined.
David.
Hi Ja,
Thank you for answering my question.
Here is my graph.
<?xml version="1.0" encoding="UTF-8"?>
SECTION==M18-34
SECTION==P2-11
${out.0.SECTION} = "M2-34";
${out.0.ORIGNATORS} = ${in.0.ORIGNATORS};
${out.0.TELECAST} = ${in.0.TELECAST};
${out.0.DAYS} = ${in.0.DAYS};
${out.0.S\_TIME} = ${in.0.S\_TIME};
${out.0.R\_DUR} = ${in.0.R\_DUR};
${out.0.MEASUREMENT\_PERIODS} = ${in.0.MEASUREMENT\_PERIODS};
${out.0.DAYPARTS} = ${in.0.DAYPARTS};
${out.0.VIEWING\_SOURCES} = ${in.0.VIEWING\_SOURCES};
${out.0.LIVE\_CVG\_HUT\_PUT} = ${in.0.LIVE\_CVG\_HUT\_PUT}+${in.1.LIVE\_CVG\_HUT\_PUT};
${out.0.LIVE\_SD\_CVG\_HUT\_PUT} = ${in.0.LIVE\_SD\_CVG\_HUT\_PUT}+${in.1.LIVE\_SD\_CVG\_HUT\_PUT};
${out.0.LIVE\_7\_CVG\_HUT\_PUT} = ${in.0.LIVE\_7\_CVG\_HUT\_PUT}+${in.1.LIVE\_7\_CVG\_HUT\_PUT};
${out.0.LIVE\_CVG\_AA} = ${in.0.LIVE\_CVG\_AA}+${in.1.LIVE\_CVG\_AA};
${out.0.LIVE\_SD\_CVG\_AA} = ${in.0.LIVE\_CVG\_SHR}+${in.1.LIVE\_CVG\_SHR};
${out.0.LIVE\_7\_CVG\_AA} = ${in.0.LIVE\_7\_CVG\_SHR}+${in.1.LIVE\_7\_CVG\_SHR};
${out.0.LIVE\_CVG\_SHR} = ${in.0.LIVE\_AA}+${in.1.LIVE\_AA};
${out.0.LIVE\_SD\_CVG\_SHR} = ${in.0.LIVE\_SD\_AA}+${in.1.LIVE\_SD\_AA};
${out.0.LIVE\_AA} = ${in.0.LIVE\_7\_AA}+${in.1.LIVE\_7\_AA};
${out.0.LIVE\_SD\_AA} = ${in.0.LIVE\_SD\_AA}+${in.1.LIVE\_SD\_AA};
${out.0.LIVE\_7\_AA} = ${in.0.LIVE\_7\_AA}+${in.1.LIVE\_7\_AA};
Output of running the graph:
Graph definition file: CustomizedNode.grf
INFO [main] - Checking graph configuration…
WARN [main] - unable to set FetchDirection & FetchSize for DB connection [DBConnection0]
WARN [main] - unable to set FetchDirection & FetchSize for DB connection [DBConnection0]
INFO [main] - Initializing DB connection:
INFO [main] - DBConnection driver[oracle.jdbc.OracleDriver]:url[jdbc:oracle:thin:@resd:1521:resd]:user[wmeng] … OK
INFO [main] - Starting WatchDog thread …
INFO [WatchDog] - Thread started.
INFO [WatchDog] - Running on 1 CPU(s) max available memory for JVM 65088 KB
INFO [WatchDog] - [Clover] Initializing phase: 0
DEBUG [WatchDog] - initializing edges:
DEBUG [WatchDog] - all edges initialized successfully…
DEBUG [WatchDog] - initializing nodes:
WARN [WatchDog] - unable to set FetchDirection & FetchSize for DB connection [DBConnection0]
DEBUG [WatchDog] - DB_INPUT_TABLE0 …OK
WARN [WatchDog] - unable to set FetchDirection & FetchSize for DB connection [DBConnection0]
DEBUG [WatchDog] - DB_INPUT_TABLE1 …OK
DEBUG [WatchDog] - DB_OUTPUT_TABLE0 …OK
WARN [WatchDog] - Graph element [FILTER0] is not checked by checkConfig() method. Please call TransformationGraph.checkConfig() first.
DEBUG [WatchDog] - FILTER0 …OK
WARN [WatchDog] - Graph element [FILTER1] is not checked by checkConfig() method. Please call TransformationGraph.checkConfig() first.
DEBUG [WatchDog] - FILTER1 …OK
INFO [WatchDog] - (compiling dynamic source)
DEBUG [WatchDog] - Compile arguments: -d C:\DOCUME~1\wmeng\LOCALS~1\Temp\ C:\DOCUME~1\wmeng\LOCALS~1\Temp\TransformTransformHASH_JOIN0.java -classpath /C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/cloveretl.engine.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/commons-cli-1.0.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/commons-logging.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/javolution.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/jms.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/jxl.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/log4j-1.2.12.zip;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/lib/poi-2.5.1.jar;/C:/Projects/eclipse_workplace/UpdateInputTable/build/;/C:/java_jdk/jdk1.5.0_06/lib/tools.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/plugins/org.jetel.component/cloveretl.component.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/plugins/org.jetel.connection/cloveretl.connection.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/plugins/org.jetel.connection/lib/jms.jar;/C:/Program Files/eclipse/plugins/cz.opentech.cloveretl.plugin_1.6.2/lib/plugins/org.jetel.lookup/cloveretl.lookup.jar
DEBUG [WatchDog] - Loading Class: TransformTransformHASH_JOIN0…
DEBUG [WatchDog] - Class: TransformTransformHASH_JOIN0 Loaded
DEBUG [WatchDog] - HASH_JOIN0 …OK
INFO [WatchDog] - [Clover] phase: 0 initialized successfully.
INFO [WatchDog] - Starting up all nodes in phase [0]
DEBUG [WatchDog] - DB_INPUT_TABLE0 … started
DEBUG [WatchDog] - DB_INPUT_TABLE1 … started
DEBUG [WatchDog] - DB_OUTPUT_TABLE0 … started
DEBUG [WatchDog] - FILTER0 … started
DEBUG [WatchDog] - FILTER1 … started
DEBUG [WatchDog] - HASH_JOIN0 … started
INFO [WatchDog] - Sucessfully started all nodes in phase!
INFO [WatchDog] - Execution of phase [0] successfully finished - elapsed time(sec): 2
INFO [WatchDog] - ---------------------** Start of tracking Log for phase [0] **-------------------
INFO [WatchDog] - Time: 12/04/07 12:23:19
INFO [WatchDog] - Node Status Port #Records #KB Rec/s KB/s
INFO [WatchDog] - ----------------------------------------------------------------------------------
INFO [WatchDog] - DB_INPUT_TABLE0 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 Out:0 32 6 0 0
INFO [WatchDog] - DB_INPUT_TABLE1 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 Out:0 32 6 0 0
INFO [WatchDog] - DB_OUTPUT_TABLE0 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 In:0 0 0 0 0
INFO [WatchDog] - FILTER0 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 In:0 32 6 0 0
INFO [WatchDog] - Out:0 16 3 30 6
INFO [WatchDog] - FILTER1 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 In:0 32 6 0 0
INFO [WatchDog] - Out:0 16 3 0 0
INFO [WatchDog] - HASH_JOIN0 FINISHED_O
INFO [WatchDog] - %CPU:-6.5 In:0 16 3 30 6
INFO [WatchDog] - In:1 16 3 0 0
INFO [WatchDog] - Out:0 0 0 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 2 5220
INFO [WatchDog] - ------------------------------** End of Summary **---------------------------
INFO [main] - WatchDog thread finished - total execution time: 3 (sec)
WARN [main] - DBConnection0 - close operation failed.
INFO [main] - Graph execution finished successfully
Execution of graph successful !
wendy
Hello,
that is strange. You have two inPort in your edge and Clover has thrown no error…? Can you change one inPort for outPort or send me the source code? Nevertheless I think if the output table gets some records from join component, there shouldn’t be problem with the database.
ja