Is SQL and PPL fully supported?

Question

I tried a few queries and found that some of the SQL queries are working while some are not.

I uploaded test data using dev tools. I have uploaded a few hundred documents (crwaler) as well.

PUT /library/book/_bulk?refresh&pretty
{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}

This works correctly and shows the indices.

SHOW tables LIKE %;

.kibana_1
cwl-2021.01.05
library

This works as well. Shows 42 records.

select * from .kibana_1;

This works as expected:

select * from library where page_count > 500 and name = 'Dune'

This does not work:

select * from cwl-2021.01.05

Getting an error "no response"

This works but shows only 200 records out of 400:

select * from cwl*

This works only if the ID is within the first 200 records:

select * from cwl* where requestID = '3FB4131539FD4403'

The pipe query language works as expected only with library index.

search source=library page_count > 500 and name = 'Dune'

It does not work with other indices like .kibana_1 or cwl-2021.01.05

I am not sure how SQL and PPL is supported in AWS elasticsearch instance.

score 1 · Answer 1 · answered Jan 07 '21 at 16:08

Please refer to this reference for delimiting identifiers for using special chars.

https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/experiment/ppl/general/identifiers.rst#delimited-identifiers

More on documentation:

Sql : https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/index.rst

Ppl : https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/experiment/ppl/index.rst

Contributions: https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/developing.rst

Forums: https://discuss.opendistrocommunity.dev/c/sql/8

Sql/ppl : are both developed in the open source and used as-is in the aws service.

showing a "no response" message for any (or every) error is not an intuitive way of programming, by the way :) — shantanuo, Jan 08 '21 at 03:35

score 1 · Accepted Answer · answered Jan 07 '21 at 17:15

1

You can always use backtick for this user case. e.g.

POST /_opendistro/_ppl
{
  "query": """
    source=`filebeat-7.10.0-2020.11.28`
  """
}

answered Jan 07 '21 at 17:15

penghuo

62
1

backticks work with PPL but not with SQL. https://github.com/opendistro-for-elasticsearch/sql/issues/976 – shantanuo Jan 08 '21 at 05:33
You could enable the new engine on ODFE 1.12 which support the backticks. [opendistro.sql.engine.new.enabled](https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/user/admin/settings.rst#opendistro-sql-engine-new-enabled) – penghuo Jan 08 '21 at 19:27

score 0 · Answer 3 · answered Jan 07 '21 at 04:40

This is because SQL and PPL does not like special characters like dot or dash in table name.

PUT /cwl-2021.01.06/_alias/cwl20210106

PUT /cwl-2021.01.07/_alias/cwl20210107

I created an alias and now everything seems to be working as expected.

select * from cwl20210107 where eventID = 'xxx' limit 100

All the beats, crawlers (by default) have the special chars in their index names. It is not easy to change the index name. :)

Is SQL and PPL fully supported?

3 Answers3