I've setup an ODBC 64-bit driver on a server that runs SQL Server 2017. I picked the ODBC version to match the ElasticSearch installation (6.0.6). The installation of the ODBC driver and creation of a Linked Server went fine and I am able to connect from the ODBC applet. When I expand the Linked Servers node in SQL Server Management Studio, I get the following:
So it sees the es-oc2-q, which is the name of the cluster. However, it doesn't list anything under Tables or Views.
I've tried running the following commands, but they failed with Invalid object name 'ESSTAGE.es-oc2-q'.:
-- addresses is an alias
SELECT *
FROM ESSTAGE.[es-oc2-q].addresses
I then tried using the 2 dots:
SELECT *
FROM ESSTAGE.[es-oc2-q]..addresses
This did not return an error, but the query never comes back.
The next stack release - 7.7.0, due very soon - should bring backwards support for the most important gaps. I'll update the thread once the release is out.
Note however that the driver and server are, up to this release, in version lock-step; i.e. you'll need a server updated to 7.7.
@bogdan.pintea A release of the ODBC driver that is not version locked would be appreciated. We are on ES 6.6 and upgrading because the ODBC driver isn't working right will not get any traction with the powers that be in any organization. And the upgrade itself is not exactly a trivial matter either.
Yes, but it might be a mixed news update for your needs.
The Linked Server will now connect to Elasticsearch with a 7.7 driver (I've also updated the previously linked thread).
Is it still locked to the ES version?
It is no longer bound in lock-step to the server starting with this release; but: the general compatibility policy that Elasticsearch has towards the clients is still applicable; i.e. the server will support any client that's older than itself with up to one major release.
Elasticsearch clients do not generally have a backwards compatibility policy themselves (i.e. support older servers), although it might sometimes work, depending on the APIs the client accesses.
This is not specific to the SQL clients, but the SQL clients do require stricter integration to provide correctness and will complain if server's version is too old. And they do so because even if the version check would be edited out, connecting would still fail due to the changes that have occurred in previous releases.
That's why I was suggesting in my first answer that a server upgrade would be needed too. I do understand that server upgrades are unfortunately much more delicate than a client update and generally not done just for one client support, but these are the given limitations, I'm afraid.
So I have server 6.4.2. I tried installing 7.7 ODBC and it errored out. I installed 6.5 ODBC and it didn't get an error but now I can't do any direct queries over the linked server of the elasticsearch database. What version of the ODBC driver can I install that will work for both the linked server query and the installation compatibility.
thanks for the response. Additionally, how can I get an example of using an openquery command against the elasticsearch linked server query?
Currently trying to use select * from linkedservername.clustername.indexname. Is this the correct format? I have also tried.
select * from openquery(linkedservername, 'select * from clustername.indexname') and this doesn't work either.
I am disappointed that for a version of ES that's still active, a bug is not fixed, but rather the customers are required to upgrade to the latest version, which is not a trivial thing.
As this is new functionality/capabilities, I believe it always goes into the latest version, which in this case was 7.7. Version 6.8 is still supported but not under active development. It is receiving fixes for security issues and serios bugs, but new functionality is not backported. Version 6.6 does not receive any updates or fixes at all.
Afaik, version 6.6 is an active and supported version. The ODBC driver was supported in that version. Seeing how I paid for the entire product, I think ES should fix the driver.
I am not asking ES to make the 7.7 version of the ODBC driver to be backwards compatible to 6.6 (though if they did, that would be fine). I am saying fix v6.6 of the ODBC driver that people paid for.
Currently trying to use select * from linkedservername.clustername.indexname. Is this the correct format? I have also tried.
select * from openquery(linkedservername, 'select * from clustername.indexname') and this doesn't work either.
Try SELECT field FROM openquery(linkedservername, 'SELECT field FROM indexname') and expand from there. The catalog name is not required, but if you'd like to provided it, you'd need to use the ES/SQL catalog separator, which is :, like in SELECT field FROM clustername:indexname.
Very sorry you're having troubles with the upgrade required in this case.
I am saying fix v6.6 of the ODBC driver that people paid for.
Supporting LinkedServer however required changes that applied both to the driver and the server and that are considered features, such as the artificial varchar limitation required by LinkedServer or adapting to its incapacity to work with BASE TABLEs.
No recent product Microsoft has rolled out requires these workarounds, LinkedServers is however not based on new code. I'd happily open a support request with Microsoft around these issues, but I think I know how it would end up.
In any case, we're happy to meet our users where they are and doing efforts to accommodate their applications. And we are making fixing backports back into the supported versions.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.
