0

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  | cbd

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.

smalhot
  • 43
  • 1
  • 6
  • Are you sure you want to have parent/child docs and not simply [denormalize your data](https://stackoverflow.com/questions/36915428/how-to-setup-elasticsearch-index-structure-with-multiple-entity-bindings/36982705#36982705)? – Val Sep 01 '17 at 12:52
  • Yes what's the point to keep parent/child relations with Elasticsearch, just denormalize it. With the jdbc logstash driver, it is pretty easy – tricky Sep 01 '17 at 12:54
  • I think you have given a right suggestion. But, group_concat does not work in sql server. Can you suggest right query here. – smalhot Sep 04 '17 at 09:18
  • I am able to solve my problem without with jdbc-streaming filter [check the details here](https://discuss.elastic.co/t/join-3-tables-with-sql-in-order-to-index-with-es-using-logstash/99129/2). But, I would like to learn how to solve it with what your suggested too. For time being, i have used the suggested filter. – smalhot Sep 04 '17 at 12:44

0 Answers0