0

I'm developing an ETL and must do some routines for monitoring it.

At the begining, I must make in INSERT on DB to create a record informing the filename and starting process datetime. This query will return the record's PK and it must be stored. When the ETL of that file finishes, I must update that record informing the ETL finished with success and its ending process datetime.

I use Text File Input to look for files that match its regex, and add its "Additional output fields" to stream. But I can't find a component that will run only for first record and will execute a SQL command for the INSERT.

Hikari
  • 3,797
  • 12
  • 47
  • 77

3 Answers3

1

You can use "Identify last row" and "Filter rows" together, so you will keep only one line from your input (filtering just the last one). You INSERT will be right after the Filter Rows step.

enter image description here

As you will need to split your flow, you'll need to join your ID column with the original text input rows.

jfneis
  • 2,139
  • 18
  • 31
  • I think I understand your idea, but couldn't implement it based on the image only. What I did was check "Rownum in output" option on Text File Input. Then I use Filter Rows to split the stream moving the first row to a stream and all other rows to another stream. On the stream that receives the first row I use a Database Lookup to query a table for file name. If the lookup brings the table PK I know that file was already processed, if it comes as null I know that file is new to be processed. – Hikari Aug 30 '17 at 14:34
  • @Hikari I understood that you wanted to 1) insert a row and b) update this row in the end of your transformation. Right? After you have only one row, do your db insert and JOIN the ID field with your main stream. – jfneis Aug 30 '17 at 15:12
1

You also have a Unique row. If you do not specify on which field to filter a unique value, it will output one and exactly one row.

Now, unless I misunderstood your specs, I'd rather use Kettle's logging system. Click anywhere, select properties on the popup, then Logging tab. It will give you the status (Started/End/Stop/...) and plenty of additional info, like the number of errors, the line read and written (just tell the PDI on which step it has to look for these numbers).

You can even read almost real-time in the DB the same information as you see on the bottom panel of the PDI. Just click the fields you want and press the SQL button to create the file.

Just note that, for historical reasons, the start date is not really the start dte (it's the date of the previous successful run). The start date is called Replay date.

And also if you need this system to monitor the load and know if the run has to start or nor not, take care that on abrupt ending the system does sometimes not have the time to write "End" to the log. Therefore a logdate<now-10minutes is more reliable.

enter image description here

AlainD
  • 6,187
  • 3
  • 17
  • 31
0

To do something for only the first row of a stream, use an 'Add sequence' step (start at 1) followed by a 'Filter rows' step with condition 'seq = 1'.

matthiash
  • 3,105
  • 3
  • 23
  • 34