Support for _routing field in SQL queries

[newUserForTesting]

Here is the relevant documentation for _routing field: https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-routing-field.html

This is extremely useful for optimizing query performance for large ElasticSearch cluster.
Requesting to please provide support for _routing field for SQL queries. We really appreciate your help!

Thanks!

[penghuo]

Thanks for raising this!
Currently, only field defined in index mapping is used as identifier in sql query. _routing is a metafield which is not defined in index mapping, so _routing could not been used as identifier in sql query.

For example, following query will throw SemanticCheckException _routing is not defined.

SELECT age, balance 
FROM accounts 
WHERE _routing = 'user1'

I think one solution is to borrow pseudo columns concept to expose metadata field like _routing.

@newUserForTesting Can you elaborate on your use case to provide us some insights?

[acarbonetto]

Proposal: couchbase offers META() as a way to retrieve metadata fields in their n1ql syntax https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/indexing-meta-info.html.

We could consider coding something similar to SQL DSL that allows one to do:

SELECT age, balance
FROM accounts
WHERE META()._routing = 'user1