2

In my use case, ElasticSearch is already configured and has data that can be queried via REST API. I'm wondering if there is a way to write SQL statements that can query this data that ElasticSearch is already configured on.

Example, configure an adapter to ElasticSearch in MS SQLServer and use linkedserver to connect and run normal SQL statements.

I have read about the "river", but it seems to do the opposite of what I'm looking for. Any pointers will really help.

Backs
  • 24,430
  • 5
  • 58
  • 85
maverick
  • 35
  • 1
  • 8

2 Answers2

3

SQL Server is a relational database. It operates with tables in common. Posting requests to some URI is very unusual work for SQL Server. And there is no standard mechanism to do this.

What can you do:

  1. Write a CLR-function to send post-requests
  2. Map result json to some table (it can be difficult, because Elastic Search is document-oriented and SQL Server is not)

So, as for me, this way is very complicated. I advice to use some hand-written service to operate with DB and Elastic Search, and don't try to put all logic to SQL Server.

Community
  • 1
  • 1
Backs
  • 24,430
  • 5
  • 58
  • 85
2

Something like Elasticsearch-SQL plugin might be of interest to you. This won't allow you to use it as a linked server in MSSQL, but it will allow whatever application you have, to send SQL queries to the sql API on your ElasticSearch server and get results.

Example: http://localhost:9200/_sql?sql=select * from indexName limit 10

Silas Hansen
  • 1,669
  • 2
  • 17
  • 23