Run an async SQL search

By default, SQL searches are synchronous. They wait for complete results before returning a response. However, results can take longer for searches across large data sets or frozen data.

To avoid long waits, run an async SQL search. Set wait_for_completion_timeout to a duration you’d like to wait for synchronous results.

response = client.sql.query(
  format: 'json',
  body: {
    wait_for_completion_timeout: '2s',
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

POST _sql?format=json
{
  "wait_for_completion_timeout": "2s",
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 5
}

If the search doesn’t finish within this period, the search becomes async. The API returns:

For CSV, TSV, and TXT responses, the API returns these values in the respective Async-ID, Async-partial, and Async-running HTTP headers instead.

{
  "id": "FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
  "is_partial": true,
  "is_running": true,
  "rows": [ ]
}

To check the progress of an async search, use the search ID with the get async SQL search status API.

response = client.sql.get_async_status(
  id: 'FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU='
)
puts response

GET _sql/async/status/FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=

If is_running and is_partial are false, the async search has finished with complete results.

{
  "id": "FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
  "is_running": false,
  "is_partial": false,
  "expiration_time_in_millis": 1611690295000,
  "completion_status": 200
}

To get the results, use the search ID with the get async SQL search API. If the search is still running, specify how long you’d like to wait using wait_for_completion_timeout. You can also specify the response format.

response = client.sql.get_async(
  id: 'FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=',
  wait_for_completion_timeout: '2s',
  format: 'json'
)
puts response

GET _sql/async/FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=?wait_for_completion_timeout=2s&format=json

By default, Elasticsearch stores async SQL searches for five days. After this period, Elasticsearch deletes the search and its results, even if the search is still running. To change this retention period, use the keep_alive parameter.

response = client.sql.query(
  format: 'json',
  body: {
    keep_alive: '2d',
    wait_for_completion_timeout: '2s',
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

POST _sql?format=json
{
  "keep_alive": "2d",
  "wait_for_completion_timeout": "2s",
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 5
}

You can use the get async SQL search API’s keep_alive parameter to later change the retention period. The new period starts after the request runs.

response = client.sql.get_async(
  id: 'FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=',
  keep_alive: '5d',
  wait_for_completion_timeout: '2s',
  format: 'json'
)
puts response

GET _sql/async/FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=?keep_alive=5d&wait_for_completion_timeout=2s&format=json

Use the delete async SQL search API to delete an async search before the keep_alive period ends. If the search is still running, Elasticsearch cancels it.

response = client.sql.delete_async(
  id: 'FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI='
)
puts response

DELETE _sql/async/delete/FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=

By default, Elasticsearch only stores async SQL searches. To save a synchronous search, specify wait_for_completion_timeout and set keep_on_completion to true.

response = client.sql.query(
  format: 'json',
  body: {
    keep_on_completion: true,
    wait_for_completion_timeout: '2s',
    query: 'SELECT * FROM library ORDER BY page_count DESC',
    fetch_size: 5
  }
)
puts response

POST _sql?format=json
{
  "keep_on_completion": true,
  "wait_for_completion_timeout": "2s",
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 5
}

If is_partial and is_running are false, the search was synchronous and returned complete results.

{
  "id": "Fnc5UllQdUVWU0NxRFNMbWxNYXplaFEaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQTo0NzA=",
  "is_partial": false,
  "is_running": false,
  "rows": ...,
  "columns": ...,
  "cursor": ...
}

You can get the same results later using the search ID with the get async SQL search API.

Saved synchronous searches are still subject to the keep_alive retention period. When this period ends, Elasticsearch deletes the search results. You can also delete saved searches using the delete async SQL search API.