[search] align sql search strategy with ese

[ppisljar]

currently SQL search stragegy behaves a bit differently from the other search stragegies and should be aligned.

[Dosant]

Gap analysis:

• I think this strategy is generic enough and can be moved from canvas x-pack/plugins/canvas/server/lib/essql_strategy.ts to data plugin
• We need to decide if we want this to be sync or async or both (2 strategies).
  • Depending on sync or async we would align this strategy with:
    • Sync: ES_SEARCH_STRATEGY
    • Async: ESE_SEARCH_STRATEGY
  • Sync is useful to have for REST API use-case when we need to get the full response
  • Async will be needed to support search sessions in cavas
    • elasticsearch suppots async SQL:
    • Currently, canvas's sql strategy scrolls through results and prepares final response server-side. This is recommended by es approach. This is basically a "complex" query (similar to other bucket use-case) and will be blocked by support for background quries
      • Alternatively, maybe elasticsearch could remove a limitation of index.max_result_window on SQL search results when an async search is used?
• Similar to default stratgies we should:
  • Align Request payload:
    • move query specific staff into params. Keep id and params on root
    • introduce options support for abortSignal, searchSession params, executionContext, etc ...
  • Align Response payload:
    • searchSession params, warning, total etc...
  • Add search usage tracking
  • For async implement cancel and extend methods

[Dosant]

Next steps for SQL strategy in data plugin:

• Work with [unified search] team on higher-level building blocks for their needs (replacement of SearchSource, expression, etc...)
• Work with [canvas] team to see if we can migrate to using this sql strategy as a low-level primitive for their strategy: is async SQL stable enough to switch canvas to use it? Also figure out how to avoid having second sql expression function for this strategy
• See what's next for search session before finishing this strategy to support sessions

[elasticmachine]

Pinging @elastic/kibana-data-discovery (Team:DataDiscovery)