Jdbc plugin: how to make a date convertion for a column?


(Lemec Cinq) #1

In my MS SQL table, i have a column with date stored as string on format "dd-mm-yyyy"
During my import, i want to convert this column's values into date type.

I saw a feature called filter which could do this type of transformation. I found examples but only for parsing logs full text lines brought by Beats with sort of regex.

Can I use it for a sql column?
If yes , how to use this feature for a sql column? For example, how adapt this conf file for making it working for real?

Sorry if it is a too easy question I an new

input {
    jdbc {
        jdbc_connection_string => "jdbc:localhost;"
          jdbc_user => "user"
          jdbc_password => "pass"
       jdbc_driver_library => "C:\Program Files (x86)\jdbc\sqljdbc_6.0\enu\sqljdbc42.jar"
        jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        statement => "SELECT  * FROM SOLD"
}
   }

filter
{
	 date {
		 match => [ "STRING_DATE", "dd-MM-YYYY" ]
	}
}

output {
    elasticsearch { 
       hosts => "localhost:9200"
       index => "indexname"
       document_type => "typename"

`    }`

}

as it is it creates this string field in ES:

"string_date": {
    "type": "text",
    "fields": {
      "keyword": {
        "type": "keyword",
        "ignore_above": 256
      }
    }
  }

(Mark Walkom) #2

Look at the date filter, apply that to whatever column name it is and you should be good.


(Lemec Cinq) #3

thank you for your answere but as you can see i fail to apply filter, it makes me a text field so i am asking what i am doing wrong and what to do


(Christian Dahlqvist) #4

If you output to stdout, you will see the structure of the record fetched from the DB. It is possible that Logstash creates fields in lower case as you do not specify the column names, so you might need to change "STRING_DATE" in the date filter to "string_date", which is what gets created in Elasticsearch.


(Lemec Cinq) #6

You were true: thank you!!

it works when i write in my conf file the lowercase version:
match => [ "string_date", "dd-MM-yyyy" ]

But i made another mistake, for making it work, i must make a mapping in es without forgetting the format attribute:

PUT /testgandalf
{
    "mappings": {
      "myindex": {
      "properties": {
        "field_name": {
            "type": "date",
            "format" : "dd-MM-yyyy" 
          }
      }
      
    }
  }
}

(system) #7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.