Streaming flat File data into Database

For my little Webserver Logfile Analysis project I had to solve the problem of how to stream data from a flat file into a relational datamodel in my target database.

Since I used NodeJS in several projects over the past years, it came to my mind at first. On a second thought and by consulting some friends another, easier, solution materialized:

A simple shell command.

Our Webservers run some Linux, so a tail -f is a very simple solution for consecutive read on one or multiple files.

All we need to do now is to send the tail-output to the database. Oracle REST Data Services (ORDS) comes to mind, providing a simple POST interface to the database.

On the shell side we simply pipe | the tail-output to a curl, which in turn POSTs to the webservice.

Since we expect many logfile entries  we don’t want to invoke the webservice for each line, instead we buffer some lines and send them grouped off to the webservice

The solution looks like that:

tail -n 1000 -f /var/foex/*/log/*custom*.log | while mapfile -t -n 150 ary && ((${#ary[@]})); do printf -v var "%s\n" "${ary[@]}"; curl -X POST https://myserver.com/ords/xxx/pxxx ush -H 'Cache-Control: no-cache' -H 'Content-Type: text/plain' -d "$var"; done

This is quite something and was possible only with a bunch of Google-zen.

Explained:

  • tail reads the end of a file, thanks to -f it keeps doing that whenever there is a new entry in the file
  • while loops until it received a total of 150 entries and builds up an array with the tail’ed file entries
  • curl finally takes the built up array and POST’s it as payload to our ORDS webservice

The webservice takes the given body, converts it to CLOB and process it accordingly:

Now all we need to do i string-parsing the input and put it in the respective tables. Easy, right?

6 thoughts on “Streaming flat File data into Database

  1. Hallo Peter
    Einfach eine geniale Idee, die ich schon immer suchte/brauchte
    ->Logfiles via ein Rest in Apex auswerten.
    Könnte man Deine Procedure awl_p_import kriegen?

    beste Grüße
    MA

  2. Vielen Dank für Deine Hilfsbereitschaft.
    An welche Adresse soll ich meine E-Mail Adresse schicken?

  3. Pingback: RESTful Deployment – micodify

  4. Pingback: Streaming flat File data into Database - SSWUG.ORG

  5. Pingback: RESTful Deployment – micodify

Leave a Reply

Your email address will not be published. Required fields are marked *