Hitachi Vantara Pentaho Community Forums
Results 1 to 11 of 11

Thread: JobEntry plugin development

  1. #1

    Default JobEntry plugin development

    Hello,

    I'm using the open source RDBMS Firebird together with PDI 3.0. I have a PDI job implemented which extracts data from the database into another Firebird database. Currenty I'm using the START job entry to move data on a regular base (time interval). Firebird supports some kind of event mechanism which allows to notify interested clients in case of e.g. data changes by posting an event in a trigger. It would be really useful if I could switch my PDI job to an event-based approach, so I guess the right way to implement a custom PDI job entry which basically allows to select a job database connection and to enter the name of registered event, which gets posted by a database triggers. The Firebird JDBC driver "Jaybird" supports the event-mechanism, thus, it allows to register for incoming events by name. Now the question is, how to start implementing such a custom job entry. Such a custom job entry, I call it "FirebirdEventListener" shall, upon starting the job, connect to the database and register the interest of the given event as entered in the job entry dialog.

    Can anybody put me into the right direction? Thanks a lot!

    Thomas

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Hi Thomas,

    Such a thing would work only if the job you are running would be running all the time.
    The second problem you are facing is that if you want Firebird to be the driving force behind the trigger, you need a way to communicate the event to the plugin. This can come in many forms, from putting a file in a certain folder to putting a value in a database table. In fact, in such a situation you wouldn't need the specific job entry at all since you already have job entries that are capable of checking for this.

    Matt

  3. #3

    Default

    Hi Matt,

    thanks for your reply!

    Right, the job needs to run all the time and gets waked up when the Firebird event listener job entry receives an event. The way to communicate an Firebird event to a "listening" JDBC-based application can be easily done via the Jaybird Events API (http://jaybirdwiki.firebirdsql.org/j...implementation).

    For example, I've written a command-line tool which is Firebird event-based. Upon starting the command-line tool, it registers the interest on an event (by event name) via the Jaybird Events API. What happens here is that Jaybird opens an additional TCP connection to the database server which is used for event communication. When an event gets posted by a Firebird trigger or stored procedure via the POST_EVENT <event_name> all registered client applications gets notified that the event has been called/fired. It's up to the client application to take further actions then.

    The command-line tool I've written simply queries a table upon receiving an event and e.g. parses XML data stored in a BLOB column, thus I don't have to poll the table in question if e.g. a new record has been added.

    I simply would like to have something similar to trigger my ETL job, e.g. if a new record has been added to a table in my OLTP database, the ETL job should kick in (instead of polling the table periodically; which produces load as well) and move the data into my OLAP database.

    I now could write another command-line tool which listens on a particular event and upon receiving the event, it could start the ETL job by executing a process with is basically a kitchen call, but I thought having a proper custom job entry in place, would be smarter.

    Thanks!

    Thomas

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Thomas, it sounds like a lot of fun. Having a direct line to the database for sure is better than relying on some go-between (file/table) mechanism.

    I guess all you need to do is take a look at the source code of the Dummy job entry and/or perhaps the "Wait For File" job entry.

  5. #5

    Default

    Hi Matt,

    I hope you don't mind another question ...

    I went my first step with the tableexists job entry, because I need a connection object drop-down box ... I put the compiled JAR file, a .png graphic and the plugin.xml file into a separate sub-directory in C:\Program Files\kettle-3.0.3.0569\plugins\jobentries.

    Now, I'm not able to start Spoon anymore, so I guess, there is something wrong with my plugin. The Spoon splash screen is shown, but I won't get to the Repository dialog. If I remove my plugin, then Spoon starts without problems. So, is there a way to enable some kind of trace to possibly see what's going on during startup of Spoon?


    Thanks again! Much appreciated.


    Thomas

  6. #6
    pstoellberger Guest

    Default

    I think you can run spoon like: spoon.sh -level=DETAILED -logfile=spoon.log
    check http://wiki.pentaho.com/display/EAI/Spoon+User+Guide

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Outside of your IDE you typically edit Spoon.bat. Change the last line to

    Code:
    java %OPT% org.pentaho.di.ui.spoon.Spoon %_cmdline%
    
    pause
    That will show any possible foul-play in the form of a stack trace.

  8. #8

    Default

    Hello Matt,

    that helped a lot (differnt JDK versions in place)! Basically, the new job entry plugin works now.

    The basic setup is a START object with Repeat enabled, followed by my "Firebird Event Listener" job object. The event listener waits synchronous for an event and proceeds with the job in case of receiving an event. Once proceeded, the START object takes care of entering the event listener again ...

    In the execute method of my event listener object I have to connect via the Jaybird specific class FBEventManager to the database by using the DatabaseMeta.get... methods to read the host name, database name, ... of the underlaying PDI database connection. But, I'm not using the PDI Database object to connect the database.

    Basically the code in the execute method looks like:

    fbeventmanager.connect();
    fbeventmanager.waitforEvent;
    -- Upon here, an event has been received
    ... do something
    fbeventmanager.disconnect();

    So, with the endless loop thingy from the START object, I'm somewhat always in connected mode. The question is, is there a way to disconnect e.g. when stopping the job in the IDE safely? Some kind of callback mechanism?

    I also get the following exception in detailed log mode, when the job gets restarted due to the repeat option:

    2009/06/25 11:42:20 - i18n - org.pentaho.di.core.exception.KettleException:
    2009/06/25 11:42:20 - i18n - Message not found in the preferred and failover locale: key=[Job.Log.FinishedJobEntry], package=org.pentaho.di.job
    2009/06/25 11:42:20 - i18n -
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.i18n.LAFMessageHandler.calculateString(LAFMessageHandler.java:121)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.i18n.GlobalMessages.getString(GlobalMessages.java:424)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.i18n.BaseMessages.getString(BaseMessages.java:76)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.job.Messages.getString(Messages.java:40)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.job.Job.execute(Job.java:440)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.job.Job.execute(Job.java:258)
    2009/06/25 11:42:20 - i18n - at org.pentaho.di.job.Job.run(Job.java:209)
    2009/06/25 11:42:20 - fel_test - !Job.Log.FinishedJobEntry!


    Thanks again for your help! I'm almost done.

    Thomas

  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There is an abort job entry if that's what you mean. You can also stop a job.
    However, you can also simply kill the process if you like.

    >2009/06/25 11:42:20 - fel_test - !Job.Log.FinishedJobEntry!

    It's an untranslated key. In detailed logging mode PDI complains about it.

  10. #10

    Default

    Hi Matt,

    what I meant is to run some piece of code in case of aborting a batch file or killing the etl/java process. The reason for that is, when a connection is established to the database (again, via a particular Jaybird JDBC class and not a PDI Database object) via the event listener, and the etl job gets aborted e.g. via CTRL+C or killing it via the Task Manager, I have to somehow disconnect the event listener from the database, otherwise I always end up with such a log entry in the Firebird database log file.

    servername Thu Jun 25 16:14:45 2009
    INET/inet_error: read errno = 10054


    Altough not a big deal (except the log file might grow over time and a sysadmin may ask what is happening here), I would like to avoid if possible. In a command-line tool which using the event-based mechanism as well, I'm using the callback mechanism via

    Runtime.getRuntime().addShutdownHook(new DisconnectFromDatabaseOnExitThread(eventNotifier));

    To run some code, in particular disconnecting the event manager from the database, thus no log entry in the Firebird server log file will be added.


    Thanks,
    Thomas

  11. #11

    Default

    Btw, I've blogged about that new job entry here: http://blog.upscene.com/thomas/index...y090626-115230

    Thomas

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.