Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Custom Logging New Field or Sepereate Table

  1. #1
    Join Date
    Oct 2013
    Posts
    18

    Default Custom Logging New Field or Sepereate Table

    Hello All

    I know there's a good amount out there on this topic but I couldn't find what I was looking for so hoping someone can help here.

    Basically, when a job is called I need to be able to query the appropriate row in my DB that pertains to that job. I need to be able to set the ID to a value I define myself, which I found how I can do by setting a named parameter and use that value within my job, but I need to be able to relate that unique value to a job's log field. I thought of two different ways to do this, but ran into obstacles with each.

    1st approach: add a field to the log that has this this unique ID so I can then select it in a query.
    Problem: I can't find a way to add a custom column to the log DB table. If there's anyway to do this I'd greatly appreciate the insight.

    2nd approach: Explicitly write to my own custom DB table on each step instead of the pentaho log table. This custom table would have the fields job_id, date_time, step_name, status (success or fail), and log_detail.
    Problem: I don't know if there's some function or transformation I can use to get the log_detail per each step.I need this so if I do have an error I can figure out why.

    Any ideas? Or if you have a different suggestion I'm all ears.

    Thanks!

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Can you add a column in your DB for the Job ID (Batch ID)? It's easy to retrieve using the Get System Info step.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Oct 2013
    Posts
    18

    Default

    The problem with using the Batch ID is every job is returning -1 for the batch ID. Since this is not unique this isn't helping. Is there any way to set the batch ID to a specific value so I can then use that instead?

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    The Batch ID should be a sequential/unique number. What is the general relationship of your jobs/transformations? How are you configuring your log settings?
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Oct 2013
    Posts
    18

    Default

    I have both jobs and transformations writing to a DB called ETL_LOG. The jobs are writing to a table called JOB_LOG and the transformations are writing to a table called TRANSFORMATION_LOG. I just assumed the batch_id was supposed to be -1. How can I set it up so the batch ID is unique?

    If there's an article that I've overlooked please link me it so I can get this figured out.

    Thanks!

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    In your job, on the "Job Settings" --> "Settings" tab, do you have the "Pass batch ID?" box checked?
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Oct 2013
    Posts
    18

    Default

    I didnt, but after I checked it the jobs log still came up with a batch_id of -1. Is there a common reason why this would happen? I havent found anything when I searched for it

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Unhappy

    I tried a quick example with a database table for both job logging and transformation logging. I am not getting exactly what you are getting but it is not working as I was expecting. Here are my observations:
    1. Using the Get System Info step for the "transformation batch ID" type, I always get a unique transformation batch ID, but it is just a counter for the number of times the transformation has run - not the ID of the parent job. I verified this with a job that calls another job and then calls a transformation The ID's in the two database tables get out of sync (both jobs write to the same logging table) and there is no correlation between the batch ID in the job logging table and the batch ID in the transformation logging table.
    2. Using the Get System Info step for the "parent job batch ID" type, I always get a parent job batch ID of 0. It is not being passed down to the transformation. Contrary to what I said before, it doesn't seem to matter what the state of the "Pass batch ID?" check box in the job settings is. I always get 0. Makes me wonder what that check box is for..


    The main issue I see is that #2 above doesn't return the parent job batch ID. This seems like a bug to me. Unless someone else can shed some light on this, you should fill out a JIRA.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

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.