Querying Multiple Indexes (of different field mappings) causing Failed Shards Exception

[waleed_ali]

Hello,

I am trying to create Coordinate Map Visualization using an Index Pattern which covers two Indices of ES. One Index contains the location (lat/lon) info whereas the other contains live data from IoT devices relevant to these locations. Both these indices have different fields obviously.

The problem I am hitting is when I try to create Map, to see live data over different locations, it does not display anything rather, returns an exception "5 of 10 shards failed". I have checked the response of http://localhost:5601/elasticsearch/_msearch (which I suppose is querying ES for the required data) and getting the following object:

{
    "took": 51,
    "timed_out": false,
    "_shards": {
        "total": 10,
        "successful": 5,
        "skipped": 0,
        "failed": 5,
        "failures": [
            {
                "shard": 0,
                "index": "live-data",
                "node": "R68NHkrGSIqyk8TdlR5ZrA",
                "reason": {
                    "type": "query_shard_exception",
                    "reason": "failed to find geo_point field [location]",
                    "index_uuid": "tOogjd6PTja0DAJLKYLpDA",
                    "index": "live-data"
                }
            }
        ]
    }
}

The locations are displayed over the map as pointers when I use single index only, though.

This obviously is because ES is not being able to find location field in live-data index, but shouldn't it be able to match location field from the other index? As both the indices have a common field named "dev_id"

I guess this can be solved by setting ignore_unmapped: true while querying ES, but I have no clue how to set this parameter within Kibana so that it request ES data with it while building Visualizations.

ELK Version: 6.1.2

Any help would be much appreciated.

[timroes]

Hi,

there are basically two solutions to your issue.

1. You could create an additional index pattern, that only has the non live-data index in there. If you know, that you want to do a visualization, that will only work on the one field, since the other has no matching documents (e.g. due to the lack of the field), you can just do this single visualization on that new index pattern instead of the one matching both indexes, but still use the other index pattern for visualizations where you want to visualize data from both indexes.

2. I would need to see your _msearch request from dev tools, for that above response. But it might be, that you could add the option to the sort:options under Management > Kibana > Advanced Settings, that it would place it to the correct part of your query, but I would need to check your query for that.

Hope one of the solutions works for you.

Cheers,
Tim

[waleed_ali]

Hello,

Thank you, Tim, for taking some time out and looking into my problem. I was following the first approach that you have mentioned but our use case demands to show the live data over a single geographical map.
I understand that ES is a NoSQL datastore and doesn't support JOINS. I have managed to find a workaround however, following jureken answer in this post. Now I am appending each packet of my live data with location information.
Still, I wanted to ask you two things:

1. Is it the right approach?
2. Right now I am doing this programmatically (have written a script to append location information in each packet and then dump it in Logstash and ES). How can I do this thing
   • take ID from each packet, search ES index containing location information and fetch the document with this ID,
   • add the fetched document to the packet
     in Logstash Conf, as Jureken has mentioned?

[timroes]

Hi,

I think I misunderstood your first post, sorry for that. Yeah Elasticsearch doesn't support Joins and is not meant for that. The way you usually want to use a search engine like Elasticsearch is by denormalizing your data (instead of normalizing what you might be used from relational databases).

So if you actually have an index, that has meta information for specific dev_id and your live data has a field dev_id you should basically enrich that data live data when indexing with data from your other index. So that each document is kind of self-contained. That is def the right approach, writing everything you would want to join in a relational database into the documents when indexing them.

You should be able to do this via the Logstash Elasticsearch filter (not output), with a config similar to the following:

filter {
	elasticsearch {
		hosts => ["your-es-host"]
		index => "your_location_index"
		query => "dev_id_field_in_location_index:%{dev_id_field_in_incoming_document}"
		fields => {
			"fieldname_in_location_index_to_copy" => "new_field_name_in_live_data"
			"fieldname2_in_location_index_to_copy" => "new_field_name2_in_live_data"
		}
	}
}

You can of course make the query more complex if you cannot simply match by the dev_id field. You will have access to all fields in your incoming live data document via the %{..} placeholders.

Hope that helped you getting it work.

Cheers,
Tim