› › ›

运行异步 SQL 搜索

默认情况下，SQL 搜索是同步的。它们会等待返回完整的结果才会返回响应。但是，对于跨大数据集或冻结数据的搜索，可能需要更长的时间才能返回结果。

为了避免长时间等待，可以运行异步 SQL 搜索。将 wait_for_completion_timeout 设置为您希望同步结果等待的时间。

resp = client.sql.query(
    format="json",
    wait_for_completion_timeout="2s",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

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

const response = await client.sql.query({
  format: "json",
  wait_for_completion_timeout: "2s",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(response);

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

如果搜索在此期间未完成，则搜索将变为异步。API 返回

搜索的 id。
is_partial 值为 true，表示搜索结果不完整。
is_running 值为 true，表示搜索仍在后台运行。

对于 CSV、TSV 和 TXT 响应，API 将这些值分别返回在 Async-ID、Async-partial 和 Async-running HTTP 标头中。

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

要检查异步搜索的进度，请将搜索 ID 与获取异步 SQL 搜索状态 API 一起使用。

resp = client.sql.get_async_status(
    id="FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
)
print(resp)

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

const response = await client.sql.getAsyncStatus({
  id: "FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
});
console.log(response);

GET _sql/async/status/FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=

如果 is_running 和 is_partial 都为 false，则表示异步搜索已完成并返回完整的结果。

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

要获取结果，请将搜索 ID 与获取异步 SQL 搜索 API 一起使用。如果搜索仍在运行，请使用 wait_for_completion_timeout 指定您希望等待的时间。您还可以指定响应的 format。

resp = client.sql.get_async(
    id="FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
    wait_for_completion_timeout="2s",
    format="json",
)
print(resp)

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

const response = await client.sql.getAsync({
  id: "FnR0TDhyWUVmUmVtWXRWZER4MXZiNFEad2F5UDk2ZVdTVHV1S0xDUy00SklUdzozMTU=",
  wait_for_completion_timeout: "2s",
  format: "json",
});
console.log(response);

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

更改搜索保留期限

编辑

默认情况下，Elasticsearch 会存储异步 SQL 搜索五天。在此期限之后，即使搜索仍在运行，Elasticsearch 也会删除搜索及其结果。要更改此保留期限，请使用 keep_alive 参数。

resp = client.sql.query(
    format="json",
    keep_alive="2d",
    wait_for_completion_timeout="2s",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

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

const response = await client.sql.query({
  format: "json",
  keep_alive: "2d",
  wait_for_completion_timeout: "2s",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(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
}

您可以使用获取异步 SQL 搜索 API 的 keep_alive 参数稍后更改保留期限。新的期限在请求运行后开始。

resp = client.sql.get_async(
    id="FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=",
    keep_alive="5d",
    wait_for_completion_timeout="2s",
    format="json",
)
print(resp)

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

const response = await client.sql.getAsync({
  id: "FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=",
  keep_alive: "5d",
  wait_for_completion_timeout: "2s",
  format: "json",
});
console.log(response);

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

在 keep_alive 期限结束之前，可以使用删除异步 SQL 搜索 API 来删除异步搜索。如果搜索仍在运行，Elasticsearch 将取消它。

resp = client.sql.delete_async(
    id="FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=",
)
print(resp)

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

const response = await client.sql.deleteAsync({
  id: "FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=",
});
console.log(response);

DELETE _sql/async/delete/FmdMX2pIang3UWhLRU5QS0lqdlppYncaMUpYQ05oSkpTc3kwZ21EdC1tbFJXQToxOTI=

存储同步 SQL 搜索

编辑

默认情况下，Elasticsearch 仅存储异步 SQL 搜索。要保存同步搜索，请指定 wait_for_completion_timeout 并将 keep_on_completion 设置为 true。

resp = client.sql.query(
    format="json",
    keep_on_completion=True,
    wait_for_completion_timeout="2s",
    query="SELECT * FROM library ORDER BY page_count DESC",
    fetch_size=5,
)
print(resp)

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

const response = await client.sql.query({
  format: "json",
  keep_on_completion: true,
  wait_for_completion_timeout: "2s",
  query: "SELECT * FROM library ORDER BY page_count DESC",
  fetch_size: 5,
});
console.log(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
}

如果 is_partial 和 is_running 都为 false，则表示搜索是同步的，并返回了完整的结果。

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

您可以使用搜索 ID 和获取异步 SQL 搜索 API 来稍后获取相同的结果。

保存的同步搜索仍然受 keep_alive 保留期限的约束。当此期限结束时，Elasticsearch 将删除搜索结果。您还可以使用删除异步 SQL 搜索 API 来删除已保存的搜索。

« 使用运行时字段 SQL Translate API »