OriginalException: Elasticsearch.Net.ElasticsearchClientException: Request failed to execute when get sql result as csv type

[azadmt]

NEST/Elasticsearch.Net version:7.17.1

Elasticsearch version:8+

when I want get Result as csv format whith folowing code:

       var connectionSettings = new ConnectionSettings(connectionPool)
                .DisableDirectStreaming()
                .DefaultIndex(defaultIndex)
                .BasicAuthentication("****", "****");

        var response = _elasticClient.Sql.Query(p => p.Format("csv").Query($"SELECT * FROM INDEX_NAME"));

       OR

         var response = _elasticClient.LowLevel.Sql.Query<SearchResponse<string>>(PostData.Serializable(new
                {
                    query = "SELECT * FROM INDEX_NAME"
                }),new Elasticsearch.Net.Specification.SqlApi.QuerySqlRequestParameters { Format="csv"});

I get this exception:

_Invalid NEST response built from a unsuccessful (200) low level call on POST: /_sql?format=csv

Audit trail of this API call:

• [1] AllNodesDead: Took: 00:00:00.0000087
• [2] Resurrection: Node: http://serverAddress:9200/ Took: 00:00:00.0000006
• [3] PingSuccess: Node: http://serverAddress:9200/ Took: 00:00:00.1280097
• [4] BadResponse: Node: http://serverAddress:9200/ Took: 00:00:00.2813069

  OriginalException: Elasticsearch.Net.ElasticsearchClientException: Request failed to execute. Call: Status code 200 from: POST /_sql?format=csv

  Request:

  {"query":"SELECT * FROM candlesticks"}

  Response:

  aP,c,fAP,fTD,frDt,frTId,h,hAP,isin,l,lAP,lTD,laAP,o,postDt,tTQ,tTV,tft,tkC,toDt,toTId 1000,1100,1200,2022-05-14T08:53:00.750Z,2022-05-14T08:52:00.660Z,09dec42b-c476-44ea-ba53-93d370e776d4,2000,1800,IS001,690,1400,2022-05-14T10:51:00.751Z,1600,1400,2022-05-14T09:02:00.751Z,150000000,0,0,150000,2022-05-14T10:51:00.751Z,2baffab3-5642-4b71-b0d3-973427ab6cff 1000,1100,1200,2022-05-14T08:53:23.056Z,2022-05-14T08:52:23.044Z,374226fc-0eec-4c54-9205-d30c8d0eee15,2000,1800,IS001,690,1400,2022-05-14T10:51:23.057Z,1600,1400,2022-05-14T09:02:23.057Z,150000000,0,0,150000,2022-05-14T10:51:23.057Z,add2beed-f72b-4fd2-98de-47ae945bcbfd_

[stevejgordon]

Hi, @azadmt.

The high-level client is designed to work with JSON responses which are deserialised to strong types.

If you need to work with raw CSV data, this can be achieved but must use the low-level client. Your code above is pretty close to what you require.

var config = new RequestConfiguration 
{ 
    ContentType = "application/json",
    Accept = "text/csv; charset=utf-8;"
};

var requestParameters = new QuerySqlRequestParameters { Format = "csv", RequestConfiguration = config };

var response = client.LowLevel.Sql.Query<StringResponse>(PostData.Serializable(new
{
    query = "SELECT * FROM INDEX_NAME"
}), requestParameters);

var csvData = response.Body;

The main difference here is the response type for the generic argument. You must use either StringResponse or BytesResponse here. This will provide access to the raw body on response.Body.

Note that to use 7.17 with the 8.x server you should also enable API version headers. Due to the use of CSV as the response format, these need to be manually provided using RequestConfiguration. There are some inconsistencies with using API versioning with the SQL query API which I need to discuss with the server team, but the above should work for now to provide a valid response. This ignore API versioning for this specific request so that the client does not incorrectly determine the response is invalid due to the response Content-Type not matching the expected value.

[stevejgordon]

Closing as no action. Non-JSON formats are not a supported scenario for the client.