[Lens] Support query languages

[stratoula]

Describe the feature:

Currently you can create a Lens chart by using a dataview and select fields from a list.
We would like to enhance the data-modelling of Lens with supporting text-based languages. (ES query dsl, SQL etc)

Text based query languages mean that the user is controlling the full query sent to ES via a text-based UI. (code editor)
The design is not yet finalized but this is highly connected with the unified search project.

The idea is to add a language switcher to the dataview picker #127244 (only where it is required, for example this doesn't make sense on a dashboard) where the user will be able to select the datasource.

Then the search bar will be replaced by a code editor.

Data fetching

Lens will use the essql expression functions. The data fetching will be part of the discover app while the unified search plugin will hold the current state.

Open questions

• Do we want to integrate them with filter pills / time range picker? (or replace with SQL box?)
• Design finalization

PoC: #118680

[elasticmachine]

Pinging @elastic/kibana-vis-editors @elastic/kibana-vis-editors-external (Team:VisEditors)

[flash1293]

Tasks

• Unified search

  • Render SQL editor using Monaco
  • Feature flag to enable
  • Overlay/inline mode
  • Language switcher (behind a feature flag) / state changes
  • Validation
  • Using the translate api for SQL, ESQL will provide specialized API

• Integration into Discover

  • Execute SQL Search using appservices infrastructure
  • Refactor doc table to work with non-documents
  • Refactor field list to work with non-documents
  • Error handling

There should be an advanced setting ”SQL in Discover” (tech preview labeled, default off)

Feature scope

• SQL integration, FROM clause bound to a data view (error if no match)
• Field statistics in field list based on table response
• No autocomplete
• No date histogram
• Saving as part of the saved search
• No switch to Lens
• No filter by cell click
• Time range integration
• Dashboard filter integration
• No async search / search sessions
• No “add field” button
• No row selection / document viewer / context viewer
• If no columns are selected, treat the first 50 as selected. If some are explicitly selected, only show them

[flash1293]

Shipped in 8.4 for Discover, #137811 will track the Lens integration