Vega: use elasticsearch's _query endpoint for queries

[jpcarey]

Describe the feature: Allow usage of the elasticsearch /_sql endpoint with the Vega visualization / plugin.

Describe a specific use case for the feature: Allow graphing using vega, while using standard SQL syntax to query for the data in elasticsearch.

It is a challenge to build the elasticsearch query, then have to fully transform the return data from elasticsearch's aggregation to something that Vega can use. The SQL endpoint seems like it would be easier to get back something closer to a flattened table.

From Vega's documentation, most examples have a simple array of single level objects, where each object contains what needs to be plotted:

"values": [
  {"x": 0, "y": 28, "c": 0}, {"x": 0, "y": 55, "c": 1},
  {"x": 1, "y": 43, "c": 0}, {"x": 1, "y": 91, "c": 1},
  {"x": 2, "y": 81, "c": 0}, {"x": 2, "y": 53, "c": 1},
  {"x": 3, "y": 19, "c": 0}, {"x": 3, "y": 87, "c": 1},
]

The sql endpoint will return the data shaped like

{
  "columns" : [
    {
      "name" : "tier_c",
      "type" : "keyword"
    },
    {
      "name" : "week_of_year",
      "type" : "integer"
    },
    {
      "name" : "resources",
      "type" : "integer"
    },
    {
      "name" : "count",
      "type" : "long"
    },
    {
      "name" : "effort",
      "type" : "double"
    }
  ],
  "rows" : [
    [
      null,
      1,
      2,
      59,
      118
    ],
    [
      "fixme",
      1,
      0,
      3,
      6
    ]
  ]
}

This would still need to be transformed to combine the column names to create each row as an object (might be an enhancement for elasticsearch to return something similar to the Vega example structure above).

cc: @flash1293

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[flash1293]

A little more context:

• Vega offers a lot of ways to transform data, so the particular format it gets returned in doesn't matter too much - it's working ok for highly nested aggregation responses, so it should be possible to do it for this format as well.
• As vega is an OSS feature and sql is not, we have to introduce an extension point for vega data sources. It's probably not too hard to do this, one option would be to tie it to the key in data, e.g.

  data: {
    url: { // handled by the default search datasource

  data: {
    sql: { // handled by the default essql datasource

Another option would be to leverage the expression framework here and provide a separate dedicated SQL input for the sql query and a different expression (essql query='...' | vega useContext=true) - this would be more work initially but could help us integrating vega with canvas and other datasources (like EQL) later on.

[wylieconlon]

This is worth revisiting now that Vega is in GA, as I'd really like to see Vega support SQL and EQL. Vega already supports querying using:

• Search source
• Maps service
• External URL fetching

The SQL and EQL endpoints are very simple, but they are not exposed directly in the browser so that increases the complexity of the integration. If we could have SQL and EQL support in the search source it would make this easier, but I don't think that's likely to happen soon.

What if we add a Vega server which exposes a SQL and EQL querying endpoint?

[wylieconlon]

After discussion with @ppisljar and @lizozom we have realized that a SQL search strategy is the missing piece needed here, but that it can be done. There is currently no issue tracking the SQL search strategy.

[stratoula]

Possibly we need to rethink it and instead of supporting _sql support ES|QL instead (_query api)

[ethanjonez]

Assuming _sql can be used to make pipelined queries i.e. first result forms basis for second query +1. If this is simple to add, this is incredibly powerful for combining queries across non-time series indices with time-series indices.