ElasticSearch Logstash JDBC: How to aggregate into different column names

[mpssantos (Marco Santos)]

I am new to Elasticsearch and I am trying to use Logstash to load data to an index. Following is a partial of my losgstash config:

filter {
  aggregate {
    task_id => "%{code}"
    code => "
      map['campaignId'] = event.get('CAM_ID')
      map['country'] = event.get('COUNTRY')
      map['countryName'] = event.get('COUNTRYNAME')
    # etc
    "
    push_previous_map_as_event => true
    timeout => 5
  }
}

output {
  elasticsearch {
    document_id => "%{code}"
    document_type => "company"
    index => "company_v1"
    codec => "json"
    hosts => ["127.0.0.1:9200"]
  }
}

I was expecting that the aggregation would map for instance the column 'CAM_ID' into a property in the ElasticSearch Index as 'campaignId'. Instead, is creating a property with the name 'cam_id' which is the column name as lowercase. The same with the rest of the properties.

Following is the Index Document after logstash being executed:

{
  "company_v1": {
    "aliases": {

    },
    "mappings": {
      "company": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "cam_id": {
            "type": "long"
          },
          "campaignId": {
            "type": "long"
          },
          "cam_type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "campaignType": {
            "type": "text"
          }
        }
      }
    },
    "settings": {
      "index": {
        "creation_date": "1545905435871",
        "number_of_shards": "5",
        "number_of_replicas": "1",
        "uuid": "Dz0x16ohQWWpuhtCB3Y4Vw",
        "version": {
          "created": "6050399"
        },
        "provided_name": "company_v1"
      }
    }
  }
}

'campaignId' and 'campaignType' were created by me when i created the index, but logstash created the other 2.

Can someone explain me how to configure logstash to customize the indexes documents properties names when data is being loaded?

Where can i find a place to understand better how events and map works?

Thank you very much.

Best Regards

[balumurari1]

in logstash config file, modify the query as

statement => "select campaignId as CAM_ID, country as COUNTRY , countryName as COUNTRYNAME from SAMPLE"

these alias names will replace with the names as per your requirement.

Regards

[mpssantos (Marco Santos)]

Thank you a lot.

To achieve that i had to add the following parameter:

input {
...
lowercase_column_names => false
...
}

I tried this as well and it worked. but I am not sure it is a workaround or if there is a

filter {
mutate {
rename => ["CAM_ID", "campaignId" ]
rename => ["CAM_TYPE", "campaignTypePY" ]
}
}

Does this makes sense?

[balumurari1]

yes you are on the correct path.