Join 3 tables with SQL in order to index with ES using Logstash

[malhotras (Samta Malhotra)]

I have 3 tables which I would like to index with ES using Logstash. My table structure looks like this:

Table A:
  ID  | Name
----- | ------
28254 | Abc
28234 | Cdf
5228  | ztr
4195  | Gre
5220  | tds
5224  | cbc
 Table B:
 ID    | Name   |  A_id  |  B_id  |
 ----- | -------|--------|-----------
 1     | qrl    |  28254 |  28241 |
 2     | sdf    |  5228  |  20983 |
 3     | cde    |  28254 |  27904 |
4     | vdf    |  28234 |  24522 |
5     | vfr    | 28234  |  28241 |
6     | gdf    | 4195   |  6501  |
7     | bdr    | 4195   |  5669  |
8     | yrf    | 5220   |  6501  |
9     | cbc    | 5220   |  28241 |
10    | hre    | 5224   | 27904  |
Table C:
A_ID  | C_ID
----- | ------
28254 | 1220
28234 | 1083
4195  | 404
5220  | 473
5224  | 473
5228  | 1220

So, a_id can have many b_id. And one b_id can be associated with many a_id. Similarly, a_id can be assosiated with many c_id and one c_id can be assosiated with many a_id. There is no relation between b_id and c_id.

How would I be able to define an appropriate relation with SQL statement for these 3 tables. And, use that statement in Logstash to create a nested structure with A as a parent, B as a child and C as fields in A.

[guyboertje]

You will have to do 1 JDBC input query and 1 lookup filter.

The JDBC input query is probably a left join between A and C with null for C_ID if there is no match.

Do a look up on B using the jdbc-streaming filter. As you have multiple records with the same A_ID in table B these will be added as a hash to the target field.

Your event should then look something like this:

{"id" => 5220, "name" => "tds", "c_id" => 473, "from_b" => [{"name" => "yrf"}, {"name" => "cbc"}]}

You will have add this to Logstash using bin/logstash-plugin install logstash-filter-jdbc_streaming

[malhotras (Samta Malhotra)]

@guyboertje. Thanks for the useful hint. I was really stuck with mapping these 3 tables. It works like a charm!