Adding Timestamp information from Oracle

So what seems like it should be an easy thing to do, is not always necessarily so. If you are trying to get your time information from Oracle either using DIH or the LucidWorks database crawler (which uses DIH), you may notice that you can't do that by default with either the DATE or the TIMESTAMP data types. To get it, you need to get your hands a little dirty.

First you need to edit your dataconfg.xml. For the LucidWorks crawler, this can be found in:

$LWS_HOME/data/lucid.jdbc/datasources/<datasourceid>/conf/dataconfig_<datasourceid>.xml

Now what you are going to do is add a javascript transformer to manipulate the data a little. You are going to add a javascript routine to that dataconfig and add that routine as a transformer to execute on every row. So the start of your dataconfig is going to look something like this:

<dataConfig>
  <script><![CDATA[
    function startDateTransform(row){
        // Get the timestamp and convert it to a date
        var timeVal = row.get("LAST_UPDATED")
        var array = timeVal.split(" ");

        // Put the correct date object into the original column
        row.put("LAST_UPDATED", array[0] + "T" + array[1] + "Z");
        return row;
    }]]>
  </script> 

So what you just did was get the value of the column, split it into two and added what Solr wants for to keep time information... a Zulu based time in the correct format with the T and Z in their proper places and put it all back together. You will need to make sure the time value you are getting out of Oracle is in UTC which you can do at the SQL level. Now you need to add a transformer so it will call the javascript routine you just created... so you add this to your entity:

<entity .... transformer="script:startDateTransform">

Lastly, you need to make sure you are getting timestamp information. So if you are using a TIMESTAMP type from oracle you are good. But if you are using a DATE type, then in your SQL you need to use cast to do something like this:

<entity .... query="select cast(LAST_UPDATED as TIMESTAMP) as last_updated from TEST" 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk