SQL api doesnt allow {preference}

[acv2]

Just leaving this around here as a question/suggestion.

why is not possible to use preference when executing queries through SQL api, I have a HOT WARM COLD architecture and im trying to reroute queries base on preference 'only_nodes:abc*' which works pretty good. then SQL happen as apparently is not possible to route the request causing all the queries to hit all the nodes even the cold ones making the architecture kinda useless.

Am I missing something? or is this a MUST be consider featur for future versions?

thanks in advance for any information you can throw in here

[Christian_Dahlqvist]

When using a hot/warm/cold architecture you generally have time-based indices spread out across the tiers based on age and each index resides in a single zone. I have never seen this use of preference in this type of architecture before, so suspect it might not be very common. If you query an index pattern that exists across all zones and limit by preference to only certain nodes, does this not lead to partial results being returned and some shards failing?

[acv2]

Hey Chris,
Thanks for your reply.

Indeed we're mostly streaching the definition here, we're using time base indices to handle a transactional table, and we're talking amolst big data. We need to be able to read realtime information up to 3 months of data, and we also need slow not priority access to the full data to create all time reports, if those query takes 40 minutes we're ok with that.

the hot-warm-cold architecture allow us to slowly rotate old data into cheaper hardware and to compress it making it more manageable, but we dont want to send by default queries to all the indices because the query will be as slow as the slowest node in this case the "cold" nodes, we want to route the query ONLY to the hot , warm nodes. we could add distinct group of aliases for such indices but i dont think there's a parameter in the ILM to allow this to be created automatically when rotating nodes. is there any?

thanks