Return cursor id before row or send cursor as header in any format of sql response

[a-a-davydov]

In current version _xpack sql rest api send cursor field in response after rows, but in some cases, it is useful to know if this response is last batch of data. For example, if I want to store result in other index using bulk api, i woud like to set refresh=wait_for only for the last batch.

Please, send cursor before rows, or send boolean header if cursor is over for all response formats

[elasticmachine]

Pinging @elastic/es-search-aggs

[costin]

Could you explain why you'd prefer having the cursor before the columns? Can you just add refresh=wait to the URL once the request has been fully parsed?

The header option isn't viable since the cursor can grow in size and pass the maximum header limit (see #16993).
The cursor could be moved up front to be consistent with the scroll API (potentially by adding a _ prefix as well).

[a-a-davydov]

Could you explain why you'd prefer having the cursor before the columns? Can you just add refresh=wait to the URL once the request has been fully parsed?

I can open index request in separate thread and stream data from from response http entity to request. It helps to avoid unnecessery memory allocation (needs to have only some reusable buffers to store entries in queue between response and request)

The header option isn't viable since the cursor can grow in size and pass the maximum header limit

It can be just boolean header indicates if this response is the last for current cursor. (i.e. if it will be cursor field at the end)

[nik9000]

It can be just boolean header indicates if this response is the last for current cursor. (i.e. if it will be cursor field at the end)

I don't think we can do this consistently. It is possible in some queries for us to know that there won't be other batches but for others we can't know until we pull that batch. Since we don't store state in SQL and we don't want to store state in SQL we'd only ever be able to tell you when we're sure that there isn't another row. But if we tell you there is another row there might not be one.

For example, if I want to store result in other index using bulk api, i woud like to set refresh=wait_for only for the last batch.

I don't think this'll work in all cases. refresh=wait_for only waits for refreshes on the shards that received documents.

You might be better off making all of the bulk writes to Elasticsearch asynchronously and adding refresh=wait_for to all of them and then waiting for them all to succeed.

[a-a-davydov]

I don't think this'll work in all cases. refresh=wait_for only waits for refreshes on the shards that received documents.

thanks, it is important. It will be good to update ?refresh and _bulk documentation

I don't think we can do this consistently. It is possible in some queries for us to know that there won't be other batches but for others we can't know until we pull that batch. Since we don't store state in SQL and we don't want to store state in SQL we'd only ever be able to tell you when we're sure that there isn't another row. But if we tell you there is another row there might not be one.

I don't ask to change any system behaviour. I meet But if we tell you there is another row there might not be one behaviour, because if you say there is no another row, there is no another row. And if I get this information before fully parse 1000 records I may use this fact for some optimisations.

Additional header is better than change JSON field order, because JSON semantic ignores field order and it is bad practice to use json field order in application logic.