Find a word inside text file using Pentaho Kettle/Spoon/PDI

1
votes

I am creating a Data Comparison/Verification script using SQL and Spoon PDI. We're moving data between two servers, and to make sure we've got all the data we have SQL queries showing a date then the quantity of rows transferred.

Example:
Serv1: 20150522 | 100
Serv2: 20150522 | 100

The script will then try to union these values, and if it fails we'll get a fail email. However, we wish to change this setup to write the outcome to a text file, and based on that text file send either a pass or fail email.

The idea behind this is we have multiple tables we're comparing, so we wish to write all the outcomes of each comparison (eight) to a text file and based off the final text file, send the outcome - rather than spamming our email inbox if multiple steps fail.

The format of the text file we wish to have is either match -> send email or mismatch [step-name] [date] -> send email.

Usually I wouldn't ask a question if I haven't tried anything first, but I've searched everywhere on Google, tried the knowledge I currently have and nothing is going the way I wish it to. I believe this is due to the logic I am using.

I am not asking for a solution to this, or for someone to do it for me. I am simply asking for guidance along the correct path.

1
tsqlpentahoetlkettlepdi
You've said that the logic you're using is causing the problem - could you edit the question to explain what that logic is? Also, is that logic in Pentaho Kettle/Spoon? It's not too clear in your question. – Jo Douglass
I need to write eight outcomes of a comparison to a text file. Once all jobs are completed and written their outcome to text file, I then need to search the text file for keyword "mismatch". If Spoon cannot find "mismatch", then a pass email should be sent. If it can find "mismatch", then a fail email should be sent. – Paul
can you provide sample output format of your text file include match or mismatch thing also. – Venkatesh Panabaka
The output of the text file is currently: StepName Match StepName2 Match StepName3 MisMatch 20150529 I need to search through the text file and find 'MisMatch'. If there is no 'MisMatch' found, send a pass email. If there is a 'MisMatch', then send a fail email. I've got this working using a C# application that runs through a batch file after the Spoon Job, however I need a solution that uses only Spoon PDI. – Paul

1 Answers

0
votes

I would do this in a transformation where there are steps for each union where the result of each step is the comparison_name and the result. This would result in a data set at the end that looks something like this:

comparison_name | result

Union A | true

Union B | false

Union C | true

You would then be able to output those results to a text file in another step to get your result file to sent out regardless of whether the job passed or failed.

Lastly you would loop through the result row in the stream, and if all are true, you could do an email step to send out a "pass" email, and if one is false, send out a "fail" email.

EDIT:

To get the date of the pass or fail you could either get the date from each individual union query result by adding it to the query like so:

SELECT CURRENT_DATE

Or you could use the Get System Info step in spoon which has multiple ways of injecting the current date into the data stream. (system date fixed, start date range of the transformation, today 00:00:00, etc.)