column meta information on essql (and other) responses

[ppisljar]

Would it be possible to provide additional meta data on columns inside essql response (we will also have same needs for eql) ?

metadata we are looking for:

• field name and index behind the data in this column
• type (string, date, ...)

we need to know field name (and index) in order to construct the filter in kibana. Currently filtering is not possible if your visualization is backed by essql data as we have no way to figure out the field behind the column in the response.

type information would be used to provide better formatting. For example if we know a field is an ip or a date we can properly format them in the final visualization. Currently we would require user to provide all this formatting information which is an extra step which makes user experience less than ideal.

[elasticmachine]

Pinging @elastic/es-ql (:Query Languages/EQL)

[elasticmachine]

Pinging @elastic/es-ql (:Query Languages/SQL)

[costin]

@ppisljar could you please take a look at https://github.com/elastic/elasticsearch/issues/57036 as well? The ticket is about the EQL response and the format returned.

[bpintea]

On the three pieces of info mentioned about:

• the SQL type currently provided in SQL responses should current be sufficient (as established);
• the index/indices info can be provided, but in the form it shows in the SQL query; this would preserve any expression provided by the user (as detailed in https://github.com/elastic/elasticsearch/issues/60987#issuecomment-673624680). Note however that this might be problematic with non-plain queries, such as subqueries, PIVOT or potentially future ones like UNION, JOIN.
• the field name will be provided, but only where available; for instance, not in expressions.

@ppisljar Would this be an acceptable compromise? If not, we'd benefit from a clearer illustration of how filtering in Kibana would work in some example cases; to start with:

• expressions (like SELECT abs(col_a) + col_b FROM ...) where Kibana might not be able to simply construct and pass a DSL through the filtering SQL API without understanding the SQL query and replicating its effect (in the lines of abs(col_a) + col_b == [desired filer values]);
• PIVOT, where some of the columns would not map to an index field (or even fields expression), but would correspond to a particular value in a field, while the column values to a an aggregation of the values of a different field (see last example in PIVOT).

Would an alternative to this be to impose an appropriate maximum result set size, load everything in memory and do the filtering there?

Laterally, this feature request would otherwise map well on the xDBC interfaces: index name on JDBC's ResultSetMetaData#getTableName and field name on getColumnLabel/getColumnName (and ODBC's SQL_DESC_BASE_TABLE_NAME and SQL_DESC_BASE_COLUMN_NAME/SQL_DESC_LABEL respectively).

[ppisljar]

Thanks @bpintea, I think the proposed would work for us, as mentioned providing index in exact manner the user provided them is what will work best for us as well and we only need field information where its available because as you mentioned with expressions, pivots and pretty much anything where the final column in the result is not represented by a single field directly we won't be able to construct a filter anyway.

[bpintea]

@ppisljar, do you have an expectation on the timing for this enhancement? There's a bit of progress, an opened PR draft that implements the API extension (and that could be tested), but the implementation is still in flux, so I wanted to ask.

[ppisljar]

we were hoping it would still be in 7.x

[wylieconlon]

We are beginning a new round of discussions about SQL in Kibana, and this issue is the main blocker for us. Is there anything we can do to increase its priority?

[bpintea]

We are beginning a new round of discussions about SQL in Kibana

Sounds great. Are there any new developments over the integration efforts? new approaches we should consider? Are the limitations/requirements in https://github.com/elastic/elasticsearch/issues/60956#issuecomment-674790499 still applicable?

Is there anything we can do to increase its priority?

I think that fact that the extra column details only work for mostly simple queries is something that we were trying to improve. Anyways, trying out the open PR would help, since that's been developed as a PoC. But that needs some refreshing now. (I think I could assemble a build if that'd help.)

[wylieconlon]

@bpintea No new developments on our side. It's still important for Kibana users that simple queries work "automatically", but we can probably work around those more complex cases in the Kibana UI. We can either ask the user to do it manually or throw an error, but even simple SQL queries are a big improvement for users.

I just tested out the linked PR locally and left some feedback.

[ppisljar]

any progress on this ?

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

Superceded by ES|QL