Logstash JDBC index field with multiple values

Elastic Stack Logstash
1

Newbie here, I am using Logstash to index data on our PostgreSQL database, the thing is, we have a very complex query for just one record,e.g 1 table has many relations.

Is it possible to format the index like this:

{
    "id": 1,
    "customer_type": "design",
    "number": "00010231",
    "country": "US",
    "date": "2019-02-12",
    "address": "Street City State",
    "orders": { // table
        "order_id": 1,
        "order_desc": null,
        ...
    },
    "products_ordered": [ // other table
        {
            "id": 1,
            "sequence": 0,
            "product_name": "sample product",
            ...,
            ...,
        }
    ],

.... // other info

I am indexing the data by writing super complicated and very long query but I don't think that's the right way to do.

is there any way or technique on how to do it?

Any help will be greatly appreciated.

2

if I understood right, you are using logstash-input-jdbc to read data from PostgreSQL and send to ElasticSearch (that's what you mean with 'index data', right ?)

you could have this single SQL query that captures it all, and do the transformation using some logstash-filter-*

I'd recommend making 1 logstash-input-jdbc and some logstash-filter-jdbc_streaming to enrich your data using some fields' values from the original message to make the linking, and other filters for normalisation, renaming, aggregation ...

see :

3

Hi @endersonmaia, Thank you for your response, I read about the links that you gave me and did some reasearch, I think this is the right answer to what I want to do, but I don't know where to start, Sorry, I'm still confused because I don't understand how am I going to add the information from one table to another. I'm not sure but I think the parameter option is the key, I am hoping you can provide me at least the very basic setup/configuration for it.

I really appreciate you helping me on this. :slight_smile:

4

considering you have two tables Sales and SalesItems, and you want a JSON message with the sales details and itens details

input {
  jdbc {
    ...
    tracking_column => "id"
    statement => "SELECT * FORM Sales WHERE id > :sql_last_value"
    ...
  }
}
...
jdbc_streaming {
  ...
  statement => "SELECT * FROM SalesItems WHERE sales_id = :sales_id"
  parameters => {
    "sales_id"  => "[id]"
  }
  target => "[itens]"
  ...
}

It would generate a message like

{
  "id": 1
  "total_ammount": 10.50
  "items" : [
    { "id" : 1, "product_name": "computer", "value": 10.0 },
    { "id" : 2, "product_name": "mouse", "value": 0.50 }
  ]
}
1 Like
6

Hi @endersonmaia, I've got a lot of errors at first but I got it all working, esp. on date thing.

THANK YOU VERY MUCH!

7

@Kevin_Juliano, I suggest you to share your solution, maybe others with a similar problem could use this information.

8

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