I have multiple indices in elastic and I need to generate reports using all of them. Description of indices:
Ticket:
Fields: Ticket_id, campaign_id, customer_id, <other_ticket_attributes>
Campaign
Fields: campaign_id, campaign_name
Customer:
Fields: customer_id, customer_name, customer_email, etc
A campaign can have billions of tickets in it, also millions of tickets can be associated with a single customer.
The report ( say ticket detail report) that needs to be generated should have the following information:
Ticket_id, campaign_id, campaign_name, customer_name, customer_email, etc
While going through the documentation, I realized that this was not feasible either through elastic DQL, Kibana reporting module or ElasticSearch SQL. One of the ways to achieve this was through maintaining a denormalized index containing all the above fields.
In my application’s domain, campaign name and customer details like name etc are editable. This means editing any of these fields will result in the updating of millions of docs. Is this the correct way of implementing such reporting use-cases or should I switch to SQL DBs ( for joins and nested queries)? Any suggestions are welcome.