Go to cloverdx.com
← Back to Forum

Error: AggregationException: Invalid mapping

Follow
Answered
Avatar
listsos
Hello!

Running the graph via Workbench CoverETL runs successfully, however the graph errors out when running from the command line (using *.grf) during the aggregate:

ERROR [main] - [AGGREGATE0] - org.jetel.component.aggregate.AggregationException: Invalid mapping '$EID:=firstnonnull($EID)' :

The EID is mapped:
<Field delimiter="|" name="EID" nullable="true" shift="0" skipFirstLine="false" type="string"/>

<Node aggregateKey="[b]EID" enabled="enabled" guiHeight="0" guiName="Aggregate" guiWidth="0" guiX="491" guiY="265" id="AGGREGATE0" mapping="$EID:=firstnonnull($EID);$LASTNAME:=firstnonnull($LASTNAME);" sorted="false" type="AGGREGATE"/>[/b]

<Edge fromNode="AGGREGATE0:0" guiBendpoints="" id="Edge2" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="DB_OUTPUT_TABLE1:0"/>

Any ideas as to why it would error out? The graph runs file through the GUI. See attached for a visual of the graph.

Thanks in advance!!
0

Comments (6)

Sort by Date Votes
  • Avatar
    listsos
    To follow up: It appears the mapping doesn't just fail for EID, failing for all values under mapping. If I remove the EID mapping from the aggregate, the next entry fails with the same error.

    I am verifying the mapping in the below portion of the gtf file:
    <Node aggregateKey="ECID" enabled="enabled" guiHeight="0" guiName="Aggregate" guiWidth="0" guiX="491" guiY="265" id="AGGREGATE0" mapping="$EID:=firstnonnull($EID);$LASTNAME:=firstnonnull($LASTNAME);$MIDDLENAME:=firstnonnull($MIDDLENAME);" sorted="false" type="AGGREGATE"/>

    In the above example, if I remove EID from the mapping, the LASTNAME will error out with "Invalid mapping '$LASTNAME:=firstnonnull($LASTNAME)' :"

    Again, this works using the GUI. Not sure why it isn't working though the manual job since the same graph was exported.
    0
    Comment actions Permalink
  • Avatar
    Vladi
    Hello Listsos,
    I have tried to replicate the reported issue on my end by using the snippet of your graph source code but I could not manufacture a situation when the same graph would run fine in the GUI but failed when running via command line.
    Having said that, I would like to ask you for additional details:
    • The exact version of CloverETL Designer in which the graph was designed.

    • The exact version of CloverETL Designer in which you ran the graph successfully (the GUI as per your update).

    • The exact version of the open source CloverETL engine in which you ran the graph that failed with the ‘invalid mapping error’.

    • The graph in question (including any externalized metadata and sample input data. Should the graph contain any sensitive information, please delete or disguise it with dummy data).

    Kind regards,
    0
    Comment actions Permalink
  • Avatar
    listsos
    Thanks for the reply!

    I am using the CloverETL Designer bundled in with IBM MDM Workbench 10.1. The CloverETL Designer version is 2.9.7.

    Under the aggregation function, contants and fields, when using the GUI to map the input field to the output field I am getting an error:
    Invalid mapping '$LASTACTIVITYDATE:=$LASTACTIVITYDATE' :
    Input field is not the key: LASTACTIVITYDATE

    Getting this for all values except the ECID value.

    The Aggregate Key is ECID.

    I was override this error by manually adding the value under the aggregation mapping property:
    $LASTACTIVITYDATE:=firstnonnull($LASTACTIVITYDATE);$ECID:=firstnonnull($ECID);$SOURCECODE:=firstnonnull($SOURCECODE);

    The job runs fine in the GUI using this override method, though doesn't seem to like it when running via the graph.

    Please let me know if you would still like to see my graph, or if the description above sheds some lights on the error. Also including 2 screenshots of the aggregate component.
    0
    Comment actions Permalink
  • Avatar
    Vladi
    Hi Listsos,
    based on the submitted details, this engine behavior can have multiple reasons:
    • the obsolete version of CloverETL

    • the OEM partner (IBM in this case) is allowed to change many things in CloverETL during bundling some of which might affect the way CloverETL engine works

    • there might be version conflicts between the bundled CloverETL Designer and the CloverETL engine (that you are using for running the graphs via the command line)

    • the graph design that is beyond what is visible in the provided source code and screenshots

    • the engine properties, etc.

    Note: the commercial engine that is part of the bundle is not meant to be used for running graphs via the command line. Automated graph runs are meant to be orchestrated by CloverETL Server.

    Unfortunately, due to the above-mentioned, the issue is out of the scope of what CloverETL Support does. You can either request a new product from IBM that replaced the IBM MDM product that you are using now or you can request a newer version of a standalone CloverETL software. However, both options should be approached through reaching out to the IBM MDM Support.

    I am sorry for the inconvenience.

    Regards,
    0
    Comment actions Permalink
  • Avatar
    listsos
    Thanks for your assistance Vladimir!
    0
    Comment actions Permalink
  • Avatar
    the_goat
    Looking at the error message, I really am not that surprised it is not working. You can see Aggregate component as a SQL's GROUP BY statement, meaning what you configured is:

    SELECT firstnonnull(LASTACTIVITYDATE) as LASTACTIVITYDATE;firstnonnull(ECID) as ECID;firstnonnull(SOURCECODE) as SOURCECODE FROM input_stream
    GROUP BY ECID;


    Where all those functions applied are groupping functions as well as sum() is in RDBMS world.

    Which made the component a bit confused. Either lose ECID from Aggregation key property or remove that firstnonnull() function from mapping (just leave Function column in the mapping blank). That should solve the problem.
    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post