Opendistro not working with Superset

[dioptre]

I'm trying to get superset working with dbapi.

This works:

curl -w "\n" -k -H 'Content-Type: application/json'  -XPOST  "http://$CASSANDRA_HOST:9200/_opendistro/_sql/?format=csv" -d '{
  "query": "SELECT * from events_recent limit 1"
}'

This also works:

from sqlalchemy import func, select
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import MetaData, Table

engine = create_engine("odelasticsearch+http://cassandra4:9200")
rows = engine.connect().execute(
    "select eid from events_recent"
)
print([row for row in rows])

When I run select eid from events_recent it fails in superset (the SqlAlchemy URI is odelasticsearch+http://cassandra4:9200):

Sep 16 00:55:48 superset-prod bash[19699]: Query 23: Running statement 1 out of 1
Sep 16 00:55:48 superset-prod bash[19699]: INFO:superset.sql_lab:Query 23: Running statement 1 out of 1
Sep 16 00:55:48 superset-prod bash[19699]: DEBUG:urllib3.connectionpool:Starting new HTTP connection (1): cassandra4:9200
Sep 16 00:55:48 superset-prod bash[19699]: DEBUG:urllib3.connectionpool:http://cassandra4:9200 "POST /_opendistro/_sql/?format=csv HTTP/1.1" 400 
Sep 16 00:55:48 superset-prod bash[19699]: WARNING:elasticsearch:POST http://cassandra4:9200/_opendistro/_sql/?format=csv [status:400 request:0.0
Sep 16 00:55:48 superset-prod bash[19699]: DEBUG:elasticsearch:> {"query":"-- Note: Unless you save your query, these tabs will NOT persist if yo
Sep 16 00:55:48 superset-prod bash[19699]: DEBUG:elasticsearch:< {
Sep 16 00:55:48 superset-prod bash[19699]:   "error": {
Sep 16 00:55:48 superset-prod bash[19699]:     "reason": "Unsupported query: -- Note: Unless you save your query, these tabs will NOT persist if 
Sep 16 00:55:48 superset-prod bash[19699]:     "details": "java.sql.SQLFeatureNotSupportedException: Unsupported query: -- Note: Unless you save 
Sep 16 00:55:48 superset-prod bash[19699]:     "type": "SQLFeatureNotSupportedException"
Sep 16 00:55:48 superset-prod bash[19699]:   },
Sep 16 00:55:48 superset-prod bash[19699]:   "status": 400
Sep 16 00:55:48 superset-prod bash[19699]: }
Sep 16 00:55:48 superset-prod bash[19699]: Query 23: <class 'es.exceptions.ProgrammingError'>
Sep 16 00:55:48 superset-prod bash[19699]: ERROR:superset.sql_lab:Query 23: <class 'es.exceptions.ProgrammingError'>

Wonder if you might know what's going on?

Thanks heaps!
Andrew

[dpgaspar]

Opendistro is not fully supported, yet the query your mentioning should work, so superset is changing the base query on SQLLab probably setting a LIMIT on it.

But anyway has I said Opendistro is not supported yet, and you will encounter more issues around it on superset mainly related with aggregations

[dioptre]

I don't think that's the error, as noted above the limit 1 is used successfully, and its also used in superset but fails:

I'm not familiar with the codebase, but what's required to just get a select working? I'd help if you could point me in the right direction...

[dioptre]

@dpgaspar any thoughts mate?

Would love to get anything working...

[dioptre]

Anyone?

[dpgaspar]

Sorry for the delay, that's certainly weird, @anirudha can you offer some help?

btw have you tried:

from sqlalchemy import func, select
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import MetaData, Table

engine = create_engine("odelasticsearch+http://cassandra4:9200")
rows = engine.connect().execute(
    "select eid from events_recent limit 1"
)
print([row for row in rows])

[dioptre]

Hi @dpgaspar as I mentioned above, the limit 1 works, just more rows don't (in superset).

[dai-chen]

@dpgaspar I'm a developer of ODFE SQL plugin. I can help here. Could you capture and share the request sent to SQL plugin? Or show me how to reproduce the issue? It'd also be helpful if your index mapping can be shared. Thanks!