Elastic search 6.3.0 SQL query API

[raghav1107 (Raghavendra)]

Hi,

It was great to hear that SQL query like API has been introduced into elastic search 6.3.0. I installed elastic search 6.3.0 to try this feature and was able to get the data from elastic search through SQL query API. But i am not able to fetch the data with multiple indices or left join in the query. Can you please help if there is work around here ?

[root@localhost ~]# curl -X POST http://10.102.1.190:9200/_xpack/sql?format=txt -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM sw_roles,pst_components"}'
{"error":{"root_cause":[{"type":"mapping_exception","reason":"Queries with multiple indices are not supported"}],"type":"mapping_exception","reason":"Queries with multiple indices are not supported"},"status":400}
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# curl -X POST http://10.102.1.190:9200/_xpack/sql?format=txt -H 'Content-Type: application/json' -d '{"query": "SELECT * FROM sw_roles LEFT JOIN pst_components"}'
{"error":{"root_cause":[{"type":"parsing_exception","reason":"line 1:25: Queries with JOIN are not yet supported"}],"type":"parsing_exception","reason":"line 1:25: Queries with JOIN are not yet supported"},"status":400}
[root@localhost ~]#

Thanks,
Raghav

[Andrei_Stefan]

Hi Raghav,
The error messages are pretty clear and it's not a bug: at the moment, multiple indices queries (and this includes JOINs of any kind) are not supported.

This being said though there is a scenario, involving multiple indices, that is supported: defining an alias to cover those indices and the indices having the same mappings.

[raghav1107 (Raghavendra)]

Thanks Andrei... we have a scenario where multiple tables in a MYSQL DB are being dumped to elastic search with respective indices. We have the existing SQL queries(with JOINS) which fetches the data from MYSQL DB and displays in UI. As MYSQL is slow to fetch large set of data, we thought of passing the same SQL query through elastic search API to fetch the data with minimal modification in the SQL query so that data can be displayed in UI quickly.

Will defining alias to cover those indices works in my case ? If yes, Please let me know how can achieve this.

[Andrei_Stefan]

It should work only if the indices in Elasticsearch will have the same mappings. Think about index1 and a copy of index1 - index1_copy. If the same field names and same mappings for those fields are found in these two indices then you can create an alias and use ES-SQL to query them.

But, if you are looking for a JOIN-similar query, most likely the mappings of those indices will be different. And it won't work. Not at the moment, at least.

[raghav1107 (Raghavendra)]

ok Thanks Andrie... with defining alias, we have to do lot of changes in our existing code which has hundreds of SQL queries. If JOINS was there in ES, then we could directly used our existing queries in the SQL API to retrieve the data from Elastic search.
Is there a plan to implement the important SQL functions like JOINS/GROUP BY/ORDER BY/UNOIN/SUM/COUNT in elastic search ? Please let me know and we would wait for these features to arrive in upcoming versions.

Regards,
Raghav

[Andrei_Stefan]

Raghav,

While Elasticsearch SQL can help you with some of your SQL queries, to get the most of your ES implementation, the best approach would be to design your indices the Elasticsearch way, and not the SQL-world way. Usually, just taking the SQL tables and mirroring them in Elasticsearch is not the most performant approach.

Also, note that GROUP BY, ORDER BY, SUM, COUNT are implemented. At the moment, there is no estimation for JOINs available.

[raghav1107 (Raghavendra)]

Yes Andrei.... i agree with you the best approach would be to design our indices the elastic search way, but it takes lot of manual effort to change the existing queries to map to elastic search indices. Hence we were looking for SQL way approach. If JOINS were available in ES, our purpose would have taken care easily . We will try to change atleast some of the queries to map to elastic search indices way. Thanks for all your help.