A typical case of SQL relation: aggregating results from jdbc input plugin (nested)

Elastic Stack Logstash
1

I would like to ask With reference to the following where there is a parent-child relation is pipelined
Logstash aggregate-example4
I wonder how to achieve a scenario where event elements have a hierarchal relationship like following
parent (n)
---child (n)
------ grand-child (n)
------------great-grand-child (n)

For events, I am using JDBC and a SQL query. works fine and giving me events as expected
I filter aggregate them with the following code.

filter {
    aggregate {
        task_id => "%{region_name}"
        code => "
            map['region_name'] = event.get('region_name')
            map['countryz_lst'] ||= []
            map['countryz'] ||= []

            if (event.get('country_name') != nil)
                if !( map['countryz_lst'].include? event.get('country_name') )
                map['countryz_lst'] << event.get('country_name')
                map['countryz'] << {
                    'country_name' => event.get('country_name')
                    }
                    end

                map['cityz_lst'] ||= []
                map['cityz'] ||= []
                if (event.get('city') != nil)
                                if !( map['cityz_lst'].include? event.get('city') )
                                map['cityz_lst'] << event.get('city')
                                map['cityz'] << {
                                    'city' => event.get('city'),
                                    'postal_code' => event.get('postal_code')
                                    }
                                    end
                            end
                            event.cancel()

            end


            event.cancel()
        "
        
        push_map_as_event_on_timeout => true
        timeout => 123
    }
    mutate {
        remove_field => ["countryz_lst","cityz_lst","@version","@timestamp"]
    }
}

output {
    # Output to the console.
    stdout {
        codec => json_lines
    }
}

It produces the following json

{
"region_name": "Europe",
"countryz": [
    {
        "country_name": "United Kingdom"
    },
    {
        "country_name": "Netherlands"
    },
    {
        "country_name": "Italy"
    },
    {
        "country_name": "France"
    },
    {
        "country_name": "Denmark"
    },
    {
        "country_name": "Germany"
    },
    {
        "country_name": "Switzerland"
    },
    {
        "country_name": "Belgium"
    }
],
"cityz": [
    {
        "postal_code": "09629850293",
        "city": "Stretford"
    },
    {
        "postal_code": "OX9 9ZB",
        "city": "Oxford"
    },
    {
        "postal_code": null,
        "city": "London"
    },
    {
        "postal_code": "3029SK",
        "city": "Utrecht"
    },
    {
        "postal_code": "10934",
        "city": "Venice"
    },
    {
        "postal_code": "00989",
        "city": "Roma"
    },
    {
        "postal_code": "80925",
        "city": "Munich"
    },
    {
        "postal_code": "3095",
        "city": "Bern"
    },
    {
        "postal_code": "1730",
        "city": "Geneva"
    }
]

}

REGION
         COUNTRY
         CITY

I want related cities nested into related countries.
Like

Region
           COUNTRY
                           CITY

I hope i could explain.
Thanks in advance

2

What does the input data look like?

3

Hello,
Thanks for the response,

Input is like the following dataset

REGION_NAME             COUNTRY_NAME              CITY                 STATE_PROVINCE     POSTAL_CODE  
----------------------  ------------------------  -------------------  -----------------  -----------  
Americas                Argentina                 (null)               (null)             (null)       
Americas                Brazil                    Sao Paulo            Sao Paulo          01307-002    
Americas                Canada                    Toronto              Ontario            M5V 2L7      
Americas                Canada                    Whitehorse           Yukon              YSW 9T2      
Americas                Mexico                    Mexico City          Distrito Federal,  11932        
Americas                United States of America  Seattle              Washington         98199        
Americas                United States of America  South Brunswick      New Jersey         50090        
Americas                United States of America  South San Francisco  California         99236        
Americas                United States of America  Southlake            Texas              26192        
Asia                    Australia                 Sydney               New South Wales    2901         
Asia                    China                     Beijing              (null)             190518       
Asia                    India                     Bombay               Maharashtra        490231       
Asia                    Japan                     Hiroshima            (null)             6823         
Asia                    Japan                     Tokyo                Tokyo Prefecture   1689         
Asia                    Malaysia                  (null)               (null)             (null)       
Asia                    Singapore                 Singapore            (null)             540198       
Europe                  Belgium                   (null)               (null)             (null)       
Europe                  Denmark                   (null)               (null)             (null)       
Europe                  France                    (null)               (null)             (null)       
Europe                  Germany                   Munich               Bavaria            80925        
Europe                  Italy                     Roma                 (null)             00989        
Europe                  Italy                     Venice               (null)             10934        
Europe                  Netherlands               Utrecht              Utrecht            3029SK       
Europe                  Switzerland               Bern                 BE                 3095         
Europe                  Switzerland               Geneva               Geneve             1730         
Europe                  United Kingdom            London               (null)             (null)       
Europe                  United Kingdom            Oxford               Oxford             OX9 9ZB      
Europe                  United Kingdom            Stretford            Manchester         09629850293  
Middle East and Africa  Egypt                     (null)               (null)             (null)       
Middle East and Africa  Israel                    (null)               (null)             (null)       
Middle East and Africa  Kuwait                    (null)               (null)             (null)       
Middle East and Africa  Nigeria                   (null)               (null)             (null)       
Middle East and Africa  Zambia                    (null)               (null)             (null)       
Middle East and Africa  Zimbabwe                  (null)               (null)             (null)

I hope this explains the scenario now.
Thanks

4

Does this help? If you really want countryz to be an array it will require a lot more work.

    code => "
        map['region_name'] = event.get('region_name')
        map['countryz_lst'] ||= []
        map['countryz'] ||= {}

        country_name = event.get('country_name')
        if country_name
            if !( map['countryz_lst'].include? country_name)
                map['countryz_lst'] << country_name
                map['countryz'][country_name] = {}
                map['countryz'][country_name]['cityz_lst'] ||= []
                map['countryz'][country_name]['cityz'] ||= []
            end

            city = event.get('city')
            if city
                if !( map['countryz'][country_name]['cityz_lst'].include? city )
                    map['countryz'][country_name]['cityz_lst'] << city
                    map['countryz'][country_name]['cityz'] << {
                        'city' => city,
                        'postal_code' => event.get('postal_code')
                    }
                end
            end
        end
        event.cancel()
    "

That code will produce

 "region_name" => "Europe",
"countryz_lst" => [
    [0] "Belgium",
    [1] "Denmark",
    [2] "France",
    [3] "Germany",
    [4] "Italy",
    [5] "Netherlands",
    [6] "Switzerland",
    [7] "United Kingdom"
],
    "countryz" => {
             "Italy" => {
            "cityz" => [
            [0] {
                       "city" => "Roma",
                "postal_code" => "00989"
            },
            [1] {
                       "city" => "Venice",
                "postal_code" => "10934"
            }
        ],
        "cityz_lst" => [
            [0] "Roma",
            [1] "Venice"
        ]
    },
       "Switzerland" => {
5

I appreciate your response. It can be a direction for some needed solutions. But in my case, It still needs some more effort.

Can there be Aggregate(s) based on multiple fields, can there be multiple task_ids if yes what is the use case(s)
Is there anything Like the following

aggregate {
        task_id => "%{region_name}%{country_name}"

Thanks

6

Yes, you could use that as a task_id.

7

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