Hello, I'm new to using flat (no-sql) data models and could use some advice for trying some different ways of testing my data in ES. I'm used to doing this in relational world with different views and joins against many tables but having trouble on how I should layout/flatten my data in ES.
I'm wanting to test ES for its search power of quickly finding data based on fields of data. The two primary tables I have today combine for about 500 fields I need to search on. I want to see what this will be like in ES. So I could use modeling suggestions in ES and I'm willing to try/test multiple ideas.
table 1: 150 fields, low update rates, about 1000 items.
-> table 2: 350 fields, low update rates, for each item in table 1 about 1000 items.
->-> table 3: 400 fields, high update rates (about 2 million hourly), for each item in table 2 about 9000 items.
->-> table 4: 400 fields, medium update rates (about 2 million daily), for each item in table 2 about 5000 items.
Table 1 and 2 are reference data. For every item in table 1 I have another 1000 items in table 2, making about million rows in table 2. I could flatten everything to table 2 but this means repeating a lot of information from table 1.
Table 3 and 4 are time based data related to items in table 2. I want/plan to use some date index on this data to easily add and drop indexes over time. I still want to keep table 3 and 4 separate as I will likely be dropping index on table 3 every day and dropping index on table 4 every month.
I want to model this in way that is not parent-child so that I could later spread data around several ES servers, without restrictions?
Is there anyway I can model this so that I don't need two data queries. One to search the reference data and second to then get time data returned?
Thanks ahead for any suggestions.