Elastic Search NEST (.net) SQL

I am trying to use https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html with the .net NEST client. Any ideas how? I do not really see a guide anywhere.

Solution

There's a couple of ways that you could write SQL and return results from Elasticsearch

Use the ODBC Driver

Install the ODBC driver and use System.Data.Odbc.OdbcConnection to get records. For example

using var connection = new OdbcConnection("DSN=Local Elasticsearch");
connection.Open();

using var command = connection.CreateCommand();
command.CommandText = "SELECT * FROM my_index";

using var adapter = new OdbcDataAdapter(command);
var table = new DataTable();
adapter.Fill(table);
connection.Close();

// do something with data in table

Note that the ODBC driver is a platinum feature which requires a platinum or enterprise license.

Use the SQL API

Use the Elasticsearch SQL API, which is exposed on NEST, the .NET client. An example

var client = new ElasticClient();

var sqlResponse = client.Sql.Query(q => q
    .Query("SELECT * from my_index")
);

foreach (var c in sqlResponse.Columns)
{
    // do something with columns 
}
foreach (var r in sqlResponse.Rows)
{
    // do something with rows
}

The SQL API is part of the features of the default distribution.