Unable to use the ODBC driver as a SQL Server Linked Server

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:

image

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.

What am I missing?

Hi @rgelb,

There are a few compatibility issues of this application with latest ODBC spec drivers (some details to be found here: Elasticsearch ODBC driver with SQL Linked Server).

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.

@bogdan.pintea I see ES 7.7 is released. Anything to report regarding the ODBC driver? Is it still locked to the ES version?

Hello @rgelb,

Anything to report regarding the ODBC driver?

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.

@bogdan.pintea I am not sure that is true. This is what happened when i tried to connect:

image

Am I missing something simple? Like maybe a flag somewhere?

@rgelb,
Let me give some concrete examples to explain how this works:

  • the 7.7 ODBC driver will work with a 7.7 server, 7.8 server, 7.x...; then also 8.0 server etc.
  • a 7.7 server will work with a 7.7 ODBC driver;
  • a 7.8 server will work with a 7.7 and 7.8 ODBC driver;
  • a 8.0 server will work with a 7.7, 7.8, 7.x... and 8.0 driver;
    a.s.o.

Hopefully the compatibility policy of Elasticsearch I was trying to explain before makes more sense now, but let me know if I can clarify more.

1 Like

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.

Based on the discussion here I believe you need to upgrade to Elasticsearch 7.7 and then use version 7.7 of the ODBC driver.

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.

1 Like

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.

Especially since the company has a fix.

I would recommend you contact Elastic Support in order to get an official response.

Hi @mattm,

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.

The query provided in OPENQUERY "is the query string executed in the linked server" so it'll have to be formatted according to Elasticsearch/SQL synthax.

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.

Hello @rgelb,

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.