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
<Edge fromNode="MERGE_JOIN0:0" guiBendpoints="" id="Edge7" inPort="0 (DB Output Table)" metadata="Metadata1" inPort="0 (Merge Join)" toNode="DB_OUTPUT_TABLE0:0"/>
Could anyone help me?
Thanks,
Wendy
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
<Edge fromNode="MERGE_JOIN0:0" guiBendpoints="" id="Edge7" inPort="0 (DB Output Table)" metadata="Metadata1" inPort="0 (Merge Join)" toNode="DB_OUTPUT_TABLE0:0"/>
Could anyone help me?
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 Ja,
Thank you for answering my question.
Here is my graph.
<?xml version="1.0" encoding="UTF-8"?>
<Graph created="Mon Apr 09 09:40:29 EDT 2007" guiVersion="1.6" licenseType="Unlicensed clover.GUI" modified="Thu Apr 12 12:23:13 EDT 2007" name="CustomizedNode" revision="1.21">
<Global>
<Metadata id="Metadata1">
<Record name="defaultRecordName" type="fixed">
<Field name="SECTION" nullable="true" shift="0" size="0" type="string"/>
<Field name="ORIGNATORS" nullable="true" shift="0" size="0" type="string"/>
<Field name="TELECAST" nullable="true" shift="0" size="0" type="string"/>
<Field name="DAYS" nullable="true" shift="0" size="0" type="string"/>
<Field name="S_TIME" nullable="true" shift="0" size="0" type="string"/>
<Field name="R_DUR" nullable="true" shift="0" size="0" type="string"/>
<Field name="MEASUREMENT_PERIODS" nullable="true" shift="0" size="0" type="string"/>
<Field name="DAYPARTS" nullable="true" shift="0" size="0" type="string"/>
<Field name="VIEWING_SOURCES" nullable="true" shift="0" size="0" type="string"/>
<Field name="LIVE_CVG_HUT_PUT" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_SD_CVG_HUT_PUT" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_7_CVG_HUT_PUT" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_CVG_AA" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_SD_CVG_AA" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_7_CVG_AA" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_CVG_SHR" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_SD_CVG_SHR" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_7_CVG_SHR" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_AA" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_SD_AA" nullable="true" shift="0" size="0" type="numeric"/>
<Field name="LIVE_7_AA" nullable="true" shift="0" size="0" type="numeric"/>
</Record>
</Metadata>
<Connection dbDriver="oracle.jdbc.OracleDriver" dbURL="jdbc:oracle:thin:@resd:1521:resd" driverLibrary="file:/C:/bea9/weblogic91/server/lib/ojdbc14.jar" id="DBConnection0" name="Connection1" password="abc1234" type="JDBC" user="wmeng"/>
</Global>
<Phase number="0">
<Node dbConnection="DBConnection0" enabled="enabled" guiHeight="0" guiName="DB Input Table" guiWidth="0" guiX="21" guiY="24" id="DB_INPUT_TABLE0" sqlQuery="select LOAD_GLXY_DEMOG.SECTION,LOAD_GLXY_DEMOG.ORIGNATORS,LOAD_GLXY_DEMOG.TELECAST,LOAD_GLXY_DEMOG.DAYS,LOAD_GLXY_DEMOG.S_TIME,LOAD_GLXY_DEMOG.R_DUR,LOAD_GLXY_DEMOG.MEASUREMENT_PERIODS,LOAD_GLXY_DEMOG.DAYPARTS,LOAD_GLXY_DEMOG.VIEWING_SOURCES,LOAD_GLXY_DEMOG.LIVE_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_SD_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_7_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_CVG_AA,LOAD_GLXY_DEMOG.LIVE_SD_CVG_AA,LOAD_GLXY_DEMOG.LIVE_7_CVG_AA,LOAD_GLXY_DEMOG.LIVE_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_SD_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_7_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_AA,LOAD_GLXY_DEMOG.LIVE_SD_AA,LOAD_GLXY_DEMOG.LIVE_7_AA from LOAD_GLXY_DEMOG" type="DB_INPUT_TABLE"/>
<Node dbConnection="DBConnection0" enabled="enabled" guiHeight="0" guiName="DB Input Table" guiWidth="0" guiX="18" guiY="164" id="DB_INPUT_TABLE1" sqlQuery="select LOAD_GLXY_DEMOG.SECTION,LOAD_GLXY_DEMOG.ORIGNATORS,LOAD_GLXY_DEMOG.TELECAST,LOAD_GLXY_DEMOG.DAYS,LOAD_GLXY_DEMOG.S_TIME,LOAD_GLXY_DEMOG.R_DUR,LOAD_GLXY_DEMOG.MEASUREMENT_PERIODS,LOAD_GLXY_DEMOG.DAYPARTS,LOAD_GLXY_DEMOG.VIEWING_SOURCES,LOAD_GLXY_DEMOG.LIVE_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_SD_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_7_CVG_HUT_PUT,LOAD_GLXY_DEMOG.LIVE_CVG_AA,LOAD_GLXY_DEMOG.LIVE_SD_CVG_AA,LOAD_GLXY_DEMOG.LIVE_7_CVG_AA,LOAD_GLXY_DEMOG.LIVE_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_SD_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_7_CVG_SHR,LOAD_GLXY_DEMOG.LIVE_AA,LOAD_GLXY_DEMOG.LIVE_SD_AA,LOAD_GLXY_DEMOG.LIVE_7_AA from LOAD_GLXY_DEMOG" type="DB_INPUT_TABLE"/>
<Node cloverFields="SECTION;ORIGNATORS;TELECAST;DAYS;S_TIME;R_DUR;MEASUREMENT_PERIODS;DAYPARTS;VIEWING_SOURCES;LIVE_CVG_HUT_PUT;LIVE_SD_CVG_HUT_PUT;LIVE_7_CVG_HUT_PUT;LIVE_CVG_AA;LIVE_SD_CVG_AA;LIVE_7_CVG_AA;LIVE_CVG_SHR;LIVE_SD_CVG_SHR;LIVE_7_CVG_SHR;LIVE_AA;LIVE_SD_AA;LIVE_7_AA" dbConnection="DBConnection0" enabled="enabled" guiHeight="0" guiName="DB Output Table" guiWidth="0" guiX="553" guiY="74" id="DB_OUTPUT_TABLE0" passThroughInputPort="0" passThroughOutputPort="0" sqlQuery="INSERT INTO LOAD_GLXY_DEMOG (SECTION, ORIGNATORS, TELECAST, DAYS, S_TIME, R_DUR, MEASUREMENT_PERIODS, DAYPARTS, VIEWING_SOURCES, LIVE_CVG_HUT_PUT, LIVE_SD_CVG_HUT_PUT, LIVE_7_CVG_HUT_PUT, LIVE_CVG_AA, LIVE_SD_CVG_AA, LIVE_7_CVG_AA, LIVE_CVG_SHR, LIVE_SD_CVG_SHR, LIVE_7_CVG_SHR, LIVE_AA, LIVE_SD_AA, LIVE_7_AA) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" type="DB_OUTPUT_TABLE"/>
<Node enabled="enabled" guiHeight="0" guiName="Filter" guiWidth="0" guiX="183" guiY="28" id="FILTER0" type="FILTER">
<attr name="filterExpression">SECTION==M18-34</attr>
</Node>
<Node enabled="enabled" guiHeight="0" guiName="Filter" guiWidth="0" guiX="184" guiY="165" id="FILTER1" type="FILTER">
<attr name="filterExpression">SECTION==P2-11</attr>
</Node>
<Node enabled="enabled" guiHeight="0" guiName="Hash Join" guiWidth="0" guiX="375" guiY="77" id="HASH_JOIN0" joinKey="ORIGNATORS;TELECAST;DAYS;S_TIME;R_DUR;MEASUREMENT_PERIODS;DAYPARTS;VIEWING_SOURCES" type="HASH_JOIN">
<attr name="transform">${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};
</attr>
</Node>
<Edge fromNode="DB_INPUT_TABLE0:0" guiBendpoints="" id="Edge0" inPort="0 (Filter)" metadata="Metadata1" outPort="0 (DB Input Table)" toNode="FILTER0:0"/>
<Edge fromNode="DB_INPUT_TABLE1:0" guiBendpoints="" id="Edge1" inPort="0 (Filter)" metadata="Metadata1" outPort="0 (DB Input Table)" toNode="FILTER1:0"/>
<Edge fromNode="FILTER0:0" guiBendpoints="" id="Edge11" inPort="0 (Hash Join)" metadata="Metadata1" outPort="0 (Filter)" toNode="HASH_JOIN0:0"/>
<Edge fromNode="FILTER1:0" guiBendpoints="" id="Edge12" inPort="1 (Hash Join)" metadata="Metadata1" outPort="0 (Filter)" toNode="HASH_JOIN0:1"/>
<Edge fromNode="HASH_JOIN0:0" guiBendpoints="" id="Edge13" inPort="0 (DB Output Table)" metadata="Metadata1" outPort="0 (Hash Join)" toNode="DB_OUTPUT_TABLE0:0"/>
</Phase>
</Graph>
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
Please sign in to leave a comment.
Comments 5