How to use the "explain" endpoint in PPL

Hello everybody, I am new in Opensearch and I didn’t know where to place this post, I put in the SQL because I haven’t seen the PPL category and this topic is quite related with SQL too. I also searched in this forum and the web but I haven’t found anything related.

I come from Splunk so PPL is much easier to me at the beginning. I know I can “translate” the queries from SQL to DSL but I don’t know if there is a way to translate from PPL to DSL.

For example. I did this request:

POST /_opensearch/_ppl/_explain
{
“query”: “source=accounts | fields firstname,lastname,age,gender”
}

I got this reply:

{
“root”: {
“name”: “ProjectOperator”,
“description”: {
“fields”: “[firstname, lastname, age, gender]”
},
“children”: [
{
“name”: “OpenSearchIndexScan”,
“description”: {
“request”: “”“OpenSearchQueryRequest(indexName=accounts, sourceBuilder={“from”:0,“size”:200,“timeout”:“1m”,”_source":{“includes”:[“firstname”,“gender”,“age”,“lastname”],“excludes”:}}, searchDone=false)"""
},
“children”:
}
]
}
}

If I use that reply in the _search endpoint it just does not work

{
“error” : {
“root_cause” : [
{
“type” : “parsing_exception”,
“reason” : “Unknown key for a START_OBJECT in [root].”,
“line” : 2,
“col” : 11
}
],
“type” : “parsing_exception”,
“reason” : “Unknown key for a START_OBJECT in [root].”,
“line” : 2,
“col” : 11
},
“status” : 400
}

With the SQL happens exactly the same if I use the “/_sql/_explain” but If I use the “_sql/_explain?format=json” I have a proper DSL query. The thing is there is nothing similar in the PPL endpoint.

Maybe this is an intentional behaviour and I am using the wrong endpoints but I don’t know how to deal with this problem. Could you help me?

@anirudha do you have any insight?

PPL has far more advanced capabilities than dsl and enables support for compiled scripted push downs.

The explain endpoint is meant for debugging the query plan. It generates a dsl which is not always compliant in the search endpoint.

1 Like

Hi and thanks for your reply. I guess I have to learn DSL or in the worst case I have to deal with SQL, which seems will solve many of my problems at the moment. Do you know if Developers are planing to support PPL or SQL in Visualizations and/or Dashboards in the future?

Sql powers visualizations are supported via integrations with superset,Tableau, powerBI, quicksight etc via jdbc and odbc drivers.

We plan to add ppl powered visualization soon to the upcoming observability plugins ( rfc will be posted soon with some demos.)

We want to add sql visualizations in Kibana at some point… For now dsl is your best bet for visualizations in Kibana.

2 Likes

Ohh. That’s very good.

Thank you for your reply. I guess I can deal with SQL and external tools like Apache superset at the moment. Thank you very much for your help :slight_smile:

Best regards.

1 Like